C# 用DataTable批量插入数据库

最新推荐文章于 2023-12-13 16:17:38 发布

原创最新推荐文章于 2023-12-13 16:17:38 发布 · 2.2k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#c# #数据库

C# 专栏收录该内容

30 篇文章

订阅专栏

本文介绍了如何使用DataTable进行批量插入数据库的操作，强调了DataTable的列顺序必须与数据库字段顺序一致，包括自增字段。同时，提供了将实体类转换为DataTable的辅助方法，以及创建DataTable的函数，方便数据处理。

用DataTable批量插入时，需要注意：DataTable的列顺序要和数据库字段顺序一致！！自增长的字段（如ID）也要在DataTable内。

/// <summary>
/// 批量导入DataTable
/// </summary>
/// <param name="dt">导入的数据表</param>
/// <param name="TbName">表名称</param> 
/// <param name="msg">返回信息</param> 
public static void InserBulk(DataTable dt, string TbName, out string msg, string connectionString)
{
    try
    {
        msg = "success";
        System.Diagnostics.Stopwatch sw = new Stopwatch();
        using (System.Data.SqlClient.SqlConnection conn = new SqlConnection(connectionString))
        {
            System.Data.SqlClient.SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
            bulkCopy.DestinationTableName = TbName;
            bulkCopy.BatchSize = dt.Rows.Count;
            conn.Open();
            sw.Start();
            if (dt != null && dt.Rows.Count != 0)
            {
                bulkCopy.WriteToServer(dt);
                sw.Stop();
            }
        }
    }
    catch (Exception ex)
    {
        msg = ex.Message.ToString();
    }

}



/// <summary>
/// 实体类转换成DataTable 辅助方法
/// </summary>
/// <param name="modelList">实体类列表</param>
/// <returns></returns>
public DataTable FillDataTable(List<T> modelList)
{
    if (modelList == null || modelList.Count == 0)
    {
        return null;
    }
    DataTable dt = CreateData(new T());
    foreach (T model in modelList)
    {
        DataRow dataRow = dt.NewRow();
        foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
        {
            dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null) ?? DBNull.Value;
        }
        dt.Rows.Add(dataRow);
    }
    return dt;
}

/// <summary>
/// 根据实体类得到表结构
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
public DataTable CreateData(T model)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    var t = typeof(T).GetProperties();
    foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
    {
        Type colType = propertyInfo.PropertyType;
        if (colType.IsGenericType && colType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            colType = colType.GetGenericArguments()[0];
        }
        dataTable.Columns.Add(new DataColumn(propertyInfo.Name, colType));
    }
    return dataTable;
}