方法1：DataTable方式
/// <summary>
/// 批量新增数据（限Excel使用）
/// </summary>
/// <param name="dt">DataTable（其中的列名要与数据库表列名一致）</param>
public int BatchAdd(DataTable dt)
{
int rs = 1;
SqlConnection sqlConn =
new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]);
try
{
sqlConn.Open();
SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(sqlConn);
sqlbulkcopy.DestinationTableName = "PhoneToQRCode";//数据库中的表名
sqlbulkcopy.WriteToServer(dt);
}
catch (Exception ex)
{
rs = 0;
}
finally
{
sqlConn.Close();
}
return rs;
}
方法2：使用List方式
/// <summary>
/// 批量插入
/// </summary>
/// <param name="conn"></param>
/// <param name="list">源数据</param>
internal static void BulkCopy<T>(IDbConnection conn, IEnumerable<T> list)
{
var dt = list.ToDataTable();
using (conn)
{
if (conn.State == ConnectionState.Closed)
conn.Open();
using (var sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn))
{
sqlbulkcopy.DestinationTableName = dt.TableName;
for (var i = 0; i < dt.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dt);
}
}
}
/// <summary>
/// List转DataTable
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list">集合</param>
/// <returns></returns>
public static DataTable ToDataTable<T>(this IEnumerable<T> list)
{
var type = typeof(T);
var properties = type.GetProperties().ToList();
var newDt = new DataTable(type.Name);
properties.ForEach(propertie =>
{
Type columnType;
if (propertie.PropertyType.IsGenericType && propertie.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
columnType = propertie.PropertyType.GetGenericArguments()[0];
}
else
{
columnType = propertie.PropertyType;
}
newDt.Columns.Add(propertie.Name, columnType);
});
foreach (var item in list)
{
var newRow = newDt.NewRow();
properties.ForEach(propertie =>
{
newRow[propertie.Name] = propertie.GetValue(item, null) ?? DBNull.Value;
});
newDt.Rows.Add(newRow);
}
return newDt;
————————————————
版权声明：本文为CSDN博主「Weber_t」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weirtang/article/details/89368114
原文：https://www.cnblogs.com/chinasoft/p/14789259.html
如果您也喜欢它,动动您的小指点个赞吧
602392714
清零编程群