SqlBulkCopy php,C# SqlBulkCopy sqlserver 批量插入和更新数据

///

///SqlBulkCopy 帮助类///

public static classSqlBulkCopyHelper

{///

///本地认证评估表建表SQL///

private const string CreateTemplateSql= @"[Id] [int] NOT NULL,[DisabilityCardId] [nvarchar](50) NOT NULL,[PartId] [nvarchar](32) NULL,[ProvinceCode] [nvarchar](4) NULL,[DisabilityLevel] [int] NULL,[DisabilityTypes] [nvarchar](16) NULL,[VisualDisabilityLevel] [int] NULL";///

///本地认证评估更新SQL 这里采用的merge语言更新语句 你也可以使用 sql update 语句///

private const string UpdateSql= @"Merge into DisabilityAssessmentInfo AS T

Using #TmpTable AS S

ON T.Id = S.Id

WHEN MATCHED

THEN UPDATE SET T.[DisabilityCardId]=S.[DisabilityCardId],T.[PartId]=S.[PartId],T.[ProvinceCode]=S.[ProvinceCode],T.[DisabilityLevel]=S.[DisabilityLevel],T.[DisabilityTypes]=S.[DisabilityTypes],T.[VisualDisabilityLevel]=S.[VisualDisabilityLevel];";///

///SqlBulkCopy 批量更新数据///

///

///

///

///

public static void BulkUpdateData(List list, string crateTemplateSql,stringupdateSql)

{var dataTable =ConvertToDataTable(list);

ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlServerContext"].ConnectionString))

{using (var command = new SqlCommand("", conn))

{try{

conn.Open();//数据库并创建一个临时表来保存数据表的数据

command.CommandText =$"CREATE TABLE #TmpTable ({crateTemplateSql})";

command.ExecuteNonQuery();//使用SqlBulkCopy 加载数据到临时表中

using (var bulkCopy = newSqlBulkCopy(conn))

{foreach (DataColumn dcPrepped indataTable.Columns)

{

bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);

}

bulkCopy.BulkCopyTimeout= 660;

bulkCopy.DestinationTableName= "#TmpTable";

bulkCopy.WriteToServer(dataTable);

bulkCopy.Close();

}//执行Command命令 使用临时表的数据去更新目标表中的数据 然后删除临时表

command.CommandTimeout = 300;

command.CommandText=updateSql;

command.ExecuteNonQuery();

}finally{

conn.Close();

}

}

}

}///

///SqlBulkCopy 批量插入数据///

///

///

///

public static void BulkInsertData(List list, stringtableName)

{var dataTable =ConvertToDataTable(list);

ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal);using (var bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["ServiceDataContext"].ConnectionString))

{foreach (DataColumn dcPrepped indataTable.Columns)

{

bulkCopy.ColumnMappings.Add(dcPrepped.ColumnName, dcPrepped.ColumnName);

}

bulkCopy.BulkCopyTimeout= 660;

bulkCopy.DestinationTableName=tableName;

bulkCopy.WriteToServer(dataTable);

}

}public static DataTable ConvertToDataTable(IListdata)

{var properties = TypeDescriptor.GetProperties(typeof(T));var table = newDataTable();foreach (PropertyDescriptor prop inproperties)

table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType)??prop.PropertyType);foreach (T item indata)

{var row =table.NewRow();foreach (PropertyDescriptor prop inproperties)

{

row[prop.Name]= prop.GetValue(item) ??DBNull.Value;

}

table.Rows.Add(row);

}returntable;

}

}