SQL C#中与数据库交互的介绍|极客笔记

SQL C#中与数据库交互的介绍

在本文中，我们将介绍如何使用C#语言与数据库进行交互。我们将学习如何连接数据库、执行SQL查询和更新操作，并且通过示例代码进行说明。

阅读更多：SQL 教程

连接数据库

在C#中，我们可以使用ADO.NET来连接数据库。ADO.NET是Microsoft.NET Framework中用于访问数据库的一种技术。下面是一个连接到SQL Server数据库的示例：

using System;
using System.Data.SqlClient;

public class DatabaseConnection
{
    private string connectionString;
    private SqlConnection connection;

    public DatabaseConnection()
    {
        connectionString = "Data Source=serverName;Initial Catalog=databaseName;User ID=username;Password=password";
        connection = new SqlConnection(connectionString);
    }

    public void OpenConnection()
    {
        connection.Open();
        Console.WriteLine("成功连接到数据库");
    }

    public void CloseConnection()
    {
        connection.Close();
        Console.WriteLine("已关闭与数据库的连接");
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        DatabaseConnection dbConnection = new DatabaseConnection();
        dbConnection.OpenConnection();
        dbConnection.CloseConnection();
    }
}

上面的示例代码创建了一个DatabaseConnection类，其中包含了连接字符串和连接对象。通过调用OpenConnection()和CloseConnection()方法，我们可以打开和关闭与数据库的连接。

执行SQL查询

一旦与数据库建立了连接，我们可以使用C#来执行SQL查询。下面是一个查询数据库中的学生表的示例：

using System;
using System.Data.SqlClient;

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

public class DatabaseConnection
{
    private string connectionString;
    private SqlConnection connection;

    public DatabaseConnection()
    {
        connectionString = "Data Source=serverName;Initial Catalog=databaseName;User ID=username;Password=password";
        connection = new SqlConnection(connectionString);
    }

    public void OpenConnection()
    {
        connection.Open();
        Console.WriteLine("成功连接到数据库");
    }

    public void CloseConnection()
    {
        connection.Close();
        Console.WriteLine("已关闭与数据库的连接");
    }

    public void SelectStudents()
    {
        string query = "SELECT * FROM Students";
        SqlCommand command = new SqlCommand(query, connection);
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Student student = new Student();
            student.Id = Convert.ToInt32(reader["Id"]);
            student.Name = reader["Name"].ToString();
            student.Age = Convert.ToInt32(reader["Age"]);
            Console.WriteLine("学生ID: {0}, 姓名: {1}, 年龄: {2}", student.Id, student.Name, student.Age);
        }

        reader.Close();
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        DatabaseConnection dbConnection = new DatabaseConnection();
        dbConnection.OpenConnection();
        dbConnection.SelectStudents();
        dbConnection.CloseConnection();
    }
}

上面的示例代码创建了一个Student类，用于存储从数据库中检索到的学生信息。通过调用SelectStudents()方法，我们可以执行SQL查询并将结果打印到控制台。

执行SQL更新操作

除了查询外，我们还可以使用C#执行SQL更新操作，例如插入、更新和删除数据。下面是一个向学生表中插入数据的示例：

using System;
using System.Data.SqlClient;

public class DatabaseConnection
{
    private string connectionString;
    private SqlConnection connection;

    public DatabaseConnection()
    {
        connectionString = "Data Source=serverName;Initial Catalog=databaseName;User ID=username;Password=password";
        connection = new SqlConnection(connectionString);
    }

    public void OpenConnection()
    {
        connection.Open();
        Console.WriteLine("成功连接到数据库");
    }

    public void CloseConnection()
    {
        connection.Close();
        Console.WriteLine("已关闭与数据库的连接");
    }

    public void InsertStudent(string name, int age)
    {
        string query = "INSERT INTO Students (Name, Age) VALUES (@Name, @Age)";
        SqlCommand command = new SqlCommand(query, connection);
        command.Parameters.AddWithValue("@Name", name);
        command.Parameters.AddWithValue("@Age", age);
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("已插入 {0} 条记录", rowsAffected);
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        DatabaseConnection dbConnection = new DatabaseConnection();
        dbConnection.OpenConnection();
        dbConnection.InsertStudent("张三", 18);
        dbConnection.CloseConnection();
    }
}

上面的示例代码通过调用InsertStudent()方法，向学生表中插入了一条记录。