C# Microsoft Sql Server 資料庫完整 CRUD 示範 (Microsoft.Data.SqlClient)

Microsoft.Data.SqlClient 是什麼？

以前在 .NET Framework 時代要連接資料庫都是使用 System.Data.SqlClient ，並且有內建在 .NET Framework 中，現在 .NET (.NET Core) 和 .NET Framework 的原始程式碼不一樣，並且也不好快速更新 System.Data.SqlClient ，所以就出了一個新的 Microsoft.Data.SqlClient，未來會主要開發 Microsoft.Data.SqlClient ，但也不會馬上棄用 System.Data.SqlClient。雖然兩者都可以使用，不過在 .NET 中最好優先使用新的 Microsoft.Data.SqlClient，目前已經發展到 5.0.1，只支援 .NET Standard 2.0+, .NET Core 3.1+, .NET Framework 4.6.2+
延伸閱讀：.NET .NET Core .NET Framework 這三個是什麼？差別在哪裡？快速釐清懶人包

阿舊的不是還能用？幹嘛要學新的？

下載次數比較

2022-12-26	System.Data.SqlClient	Microsoft.Data.SqlClient
總共	462.6M	255.1M
當前版本	1.4M	2.2M
每天平均	155.9K	191.8K

雖然目前舊的 System.Data.SqlClient 下載數將近新的 Microsoft.Data.SqlClient 的兩倍，不過依照下載次數來看新的日平均高於舊的，總有一天新的會超越舊的，所以現在很適合學習新的👍
註：其實大部分語法都相同，詳細差別可以查看 Github 上的這份清單

安裝套件

先使用 NuGet 安裝 Microsoft.Data.SqlClient 套件，或是使用 .NET CLI 執行以下指令安裝

	
dotnet add package Microsoft.Data.SqlClient
    

完整程式碼範例

下面提供包含建立資料表、新增、修改(更新)、刪除、查詢(單筆、全部) 的程式碼範例，並使用參數執行 sql 語法，能夠有效防止資料隱碼(SQL Injection)攻擊

    
using Microsoft.Data.SqlClient;

namespace ConsoleAppSqlServerTest;

public class Program
{
    public static void Main(string[] args)
    {
        CreateTable();
        int id1 = Insert("Ruyut", "a@ruyut.com");
        var user = FindById(id1);
        Console.WriteLine(user); // Id: 1, Name: Ruyut, Email: a@ruyut.com

        int id2 = Insert("小明", "test@ruyut.com");
        Update(id2, "小明123", "123@ruyut.com");

        List<User> findAll = FindAll();
        findAll.ForEach(Console.WriteLine);
        // Id: 1, Name: Ruyut, Email: a@ruyut.com
        // Id: 2, Name: 小明123, Email: 123@ruyut.com

        Delete(id2);
    }

    private const string ConnectionString =
        "Server=localhost;Database=mydb;User=ruyut;Password=ruyut;TrustServerCertificate=true";

    /// <summary>
    /// 建立資料表
    /// </summary>
    public static void CreateTable()
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string createTable = @"
                create table users
                (
                    id   INT PRIMARY KEY IDENTITY (1,1),
                    name VARCHAR(50) NOT NULL UNIQUE,
                    email VARCHAR(100) NOT NULL UNIQUE
                )
            ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = createTable;
        command.ExecuteNonQuery();
        command.Connection.Close();
    }

    /// <summary>
    /// 新增
    /// </summary>
    public static int Insert(string name, string email)
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string insertData = @"
                insert into users (name, email) values (@name, @email)
            ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = insertData;
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@email", email);

        // 取得新增資料後自動產生的 id
        command.CommandText += "select @@identity";
        int id = Convert.ToInt32(command.ExecuteScalar());
        command.Connection.Close();

        Console.WriteLine($"id: {id}");
        return id;
    }

    /// <summary>
    /// 更新
    /// </summary>
    public static void Update(int id, string name, string email)
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string updateData = @"
                update users set name = @name, email = @email where id = @id
            ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = updateData;
        command.Parameters.AddWithValue("@id", id);
        command.Parameters.AddWithValue("@name", name);
        command.Parameters.AddWithValue("@email", email);
        command.ExecuteNonQuery();
        command.Connection.Close();
    }

    /// <summary>
    /// 使用 id 尋找
    /// </summary>
    public static User FindById(int id)
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string getData = @"
                select name, email from users where id = @id
            ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = getData;
        command.Parameters.AddWithValue("@id", id);
        using SqlDataReader reader = command.ExecuteReader();
        reader.Read();

        return new()
        {
            Id = id,
            Name = reader.GetString(0),
            Email = reader.GetString(1),
        };
    }

    /// <summary>
    /// 尋找所有
    /// </summary>
    public static List<User> FindAll()
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string sql = @" select id, name, email from users ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = sql;
        using SqlDataReader reader = command.ExecuteReader();
        List<User> users = new();
        while (reader.Read())
        {
            users.Add(new()
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Email = reader.GetString(2),
            });
        }

        reader.Close();
        return users;
    }

    /// <summary>
    /// 刪除
    /// </summary>
    public static void Delete(int id)
    {
        using SqlConnection connection = new SqlConnection(ConnectionString);

        string sql = @" delete from users where id = @id ";

        using SqlCommand command = connection.CreateCommand();
        command.Connection.Open();
        command.CommandText = sql;
        command.Parameters.AddWithValue("@id", id);
        command.ExecuteNonQuery();
        command.Connection.Close();
    }

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public string Email { get; set; } = string.Empty;

        public override string ToString() => $"Id: {Id}, Name: {Name}, Email: {Email}";
    }
}
    

註： 若在大型的資料庫系統上，使用 Entity Framework Core 或許能夠有效提升開發速度

參考資料：
Microsoft.Learn - Introduction to Microsoft.Data.SqlClient namespace
Microsoft.Learn - SqlConnection Class
iThome - 微軟釋出新的SQL Server資料連接程式