asp.net 批量添加和批量修改数据实例SqlBulkCopyColumnMapping

开发中经常遇到批量添加，，批量修改问题。微软自带提供了一个函数（SqlBulkCopyColumnMapping），可以方便大家使用。

1.批量添加

//可以直接传datatable

public void Update(List<实体> list) {using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)){if (conn.State == ConnectionState.Closed){conn.Open();}using (var cmd = conn.CreateCommand()){//添加到一张零时表中cmd.CommandText = "SELECT id,name INTO [#Tmp] FROM 表 Where 1=2";cmd.ExecuteNonQuery();}using (var bcp = new SqlBulkCopy(conn)){var mapid =new SqlBulkCopyColumnMapping("id", "id");bcp.ColumnMappings.Add(mapid);var mapname =new SqlBulkCopyColumnMapping("name", "name");bcp.ColumnMappings.Add(mapname);bcp.DestinationTableName = "#Tmp";try{bcp.WriteToServer(Collection.ToDataTable(list));}catch{throw;}}using (var cmd = conn.CreateCommand()){cmd.CommandText =@"Update A Set A.name=B.name From #Tmp B Inner Join 表 A ON A.id=B.id;Drop Table #Tmp;";cmd.ExecuteNonQuery();}}}

2.批量修改。

public void Update(List<实体> list) {using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)){if (conn.State == ConnectionState.Closed){conn.Open();}using (var cmd = conn.CreateCommand()){//添加到一张零时表中cmd.CommandText = "SELECT id,name INTO [#Tmp] FROM 表 Where 1=2";cmd.ExecuteNonQuery();}using (var bcp = new SqlBulkCopy(conn)){var mapid =new SqlBulkCopyColumnMapping("id", "id");bcp.ColumnMappings.Add(mapid);var mapname =new SqlBulkCopyColumnMapping("name", "name");bcp.ColumnMappings.Add(mapname);bcp.DestinationTableName = "#Tmp";try{bcp.WriteToServer(Collection.ToDataTable(list));}catch{throw;}}using (var cmd = conn.CreateCommand()){cmd.CommandText =@"Update A Set A.name=B.name From #Tmp B Inner Join 表 A ON A.id=B.id;Drop Table #Tmp;";cmd.ExecuteNonQuery();}}}

转载注明：美丽元素返利网

版权声明：本文为博主原创文章，未经博主允许不得转载。

与其在那里苦苦挣扎，碍于面子硬撑，倒不如微笑着面对，