C# 用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;
}

最后

以上就是朴实流沙为你收集整理的C# 用DataTable批量插入数据库的全部内容，希望文章能够帮你解决C# 用DataTable批量插入数据库所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。