直到看了保哥的文章，才發現原來 .NET 有內建大量資料寫入資料庫的函式，看完之後覺得可以動手把保哥寫的範例改成泛型，以下是改完後的程式碼：

public class SqlBulkCopyHelper
{
    public static void SqlBulkCopy(SqlConnection sqlConnection, DataTable dt, string tableName)
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection))
        {
            bulkCopy.DestinationTableName = tableName;

            bulkCopy.WriteToServer(dt);
        }
    }

    public static DataTable CreateDataTable<T>(List<T> list)
    {
        DataTable dt = new DataTable();
        List<PropertyInfo> properties = new List<PropertyInfo>();

        foreach (PropertyInfo propertyInfo in typeof(T).GetProperties()) 
        {
            dt.Columns.Add(propertyInfo.Name, propertyInfo.PropertyType);
            properties.Add(propertyInfo);
        }

        foreach (T item in list)
        {
            DataRow dr = dt.NewRow();
            foreach (PropertyInfo property in properties) 
            {
                dr[property.Name] = property.GetValue(item, null);
            }             
            dt.Rows.Add(dr);
        }

        return dt;
    }
}

這中間有用到 reflection 的方式，很建議學 C# 的朋友熟練這門技術。接下來是使用方式：

string connectionStr = "<connection string>";
SqlConnection sqlConnection = new SqlConnection(connectionStr);
sqlConnection.Open();

DataTable dt = new DataTable();
List<BuckTestClass> list = new List<BuckTestClass>();

for (int i = 0; i < 100_000; i++)
{
    BuckTestClass buckTestClass = new BuckTestClass
    {
        col_1 = "demo",
        col_2 = new Random().Next(500, 5000),
        col_3 = DateTime.Now,
    };
    list.Add(buckTestClass);
}

dt = SqlBulkCopyHelper.CreateDataTable<BuckTestClass>(list);
SqlBulkCopyHelper.SqlBulkCopy(sqlConnection, dt, "[dbo].[Table_1]");

class BuckTestClass
{
    public string col_1 { get; set; } = string.Empty;
    public int col_2 { get; set; } 
    public DateTime col_3 { get; set; }
}

參考資料