SQL:C#批量插入表

2023年6月21日发(作者：)

SQL：C#批量插⼊表 #region 帮助实例：SQL 批量插⼊数据 多种⽅法 ///

/// SqlBulkCopy往数据库中批量插⼊数据 ///

/// 数据源表 /// 服务器上⽬标表 /// 创建新的列映射，并使⽤列序号引⽤源列和⽬标列的列名称。 public static void BulkToDB(DataTable sourceDataTable, string targetTableName, SqlBulkCopyColumnMapping[] mapping) { /* 调⽤⽅法 -2012年11⽉16⽇编写 //DataTable dt = Get_All_RoomState_ByHID(); //SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[4]; //mapping[0] = new SqlBulkCopyColumnMapping("Xing_H_ID", "Xing_H_ID"); //mapping[1] = new SqlBulkCopyColumnMapping("H_Name", "H_Name"); //mapping[2] = new SqlBulkCopyColumnMapping("H_sName", "H_sName"); //mapping[3] = new SqlBulkCopyColumnMapping("H_eName", "H_eName"); //BulkToDB(dt, "Bak_Tts_Hotel_Name", mapping); */ SqlConnection conn = new SqlConnection(tionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //⽤其它源的数据有效批量加载sql server表中 ationTableName = targetTableName; //服务器上⽬标表的名称 ize = ; //每⼀批次中的⾏数 try { (); if (sourceDataTable != null && != 0) { for (int i = 0; i < ; i++) (mapping[i]); //将提供的数据源中的所有⾏复制到⽬标表中 oServer(sourceDataTable );

} } catch (Exception ex) { //throw ex; extLog("BulkToDB", e); } finally { (); if (bulkCopy != null) (); } } ///

/// SQL2008以上⽅可⽀持⾃定义表类型 :调⽤存储过程游标-往数据库中批量插⼊数据 ，注意 ///

/// public void DataTableToHotelDB(DataTable sourceDataTable) { /* -2012年11⽉15⽇编写 ALTER PROCEDURE [dbo].[P_InsertSubject] @tempStudentID int AS DECLARE rs CURSOR LOCAL SCROLL FOR select H_ID from Tts_Hotel_Name

OPEN rs FETCH NEXT FROM rs INTO @tempStudentID WHILE @@FETCH_STATUS = 0 BEGIN Insert student (tempStudentID) values (@tempStudentID) FETCH NEXT FROM rs INTO @tempStudentID END CLOSE rs * *************************************************************** * create table Orders ( Orders_ID int identity(1,1) primary key, ItemCode nvarchar(50) not null, UM nvarchar(20) not null, Quantity decimal(18,6) not null, UnitPrice decimal(18,6) not null ) --创建⽤户⾃定义表类型，在可编程性->类型性->⽤户⾃定义表类型 create type OrdersTableType as table ( ItemCode nvarchar(50) not null, UM nvarchar(20) not null, Quantity decimal(18,6) not null, UnitPrice decimal(18,6) not null ) go create procedure Pro_Orders ( @OrdersCollection OrdersTableType readonly ) as insert into Orders([ItemCode],[UM],[Quantity],[UnitPrice]) SELECT oc.[ItemCode],oc.[UM],[Quantity],oc.[UnitPrice] FROM @OrdersCollection AS oc;

go *

*/ SqlParameter[] parameters = {new SqlParameter("@OrdersCollection", ured)}; parameters[0].Value = sourceDataTable; new SQLHelper().ExecuteScalar("P_DataTable_ToHotelDB", parameters, true); } #endregion

2023年6月21日发(作者：)

SQL：C#批量插⼊表 #region 帮助实例：SQL 批量插⼊数据 多种⽅法 ///

/// SqlBulkCopy往数据库中批量插⼊数据 ///

/// 数据源表 /// 服务器上⽬标表 /// 创建新的列映射，并使⽤列序号引⽤源列和⽬标列的列名称。 public static void BulkToDB(DataTable sourceDataTable, string targetTableName, SqlBulkCopyColumnMapping[] mapping) { /* 调⽤⽅法 -2012年11⽉16⽇编写 //DataTable dt = Get_All_RoomState_ByHID(); //SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[4]; //mapping[0] = new SqlBulkCopyColumnMapping("Xing_H_ID", "Xing_H_ID"); //mapping[1] = new SqlBulkCopyColumnMapping("H_Name", "H_Name"); //mapping[2] = new SqlBulkCopyColumnMapping("H_sName", "H_sName"); //mapping[3] = new SqlBulkCopyColumnMapping("H_eName", "H_eName"); //BulkToDB(dt, "Bak_Tts_Hotel_Name", mapping); */ SqlConnection conn = new SqlConnection(tionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //⽤其它源的数据有效批量加载sql server表中 ationTableName = targetTableName; //服务器上⽬标表的名称 ize = ; //每⼀批次中的⾏数 try { (); if (sourceDataTable != null && != 0) { for (int i = 0; i < ; i++) (mapping[i]); //将提供的数据源中的所有⾏复制到⽬标表中 oServer(sourceDataTable );

} } catch (Exception ex) { //throw ex; extLog("BulkToDB", e); } finally { (); if (bulkCopy != null) (); } } ///

/// SQL2008以上⽅可⽀持⾃定义表类型 :调⽤存储过程游标-往数据库中批量插⼊数据 ，注意 ///

/// public void DataTableToHotelDB(DataTable sourceDataTable) { /* -2012年11⽉15⽇编写 ALTER PROCEDURE [dbo].[P_InsertSubject] @tempStudentID int AS DECLARE rs CURSOR LOCAL SCROLL FOR select H_ID from Tts_Hotel_Name

OPEN rs FETCH NEXT FROM rs INTO @tempStudentID WHILE @@FETCH_STATUS = 0 BEGIN Insert student (tempStudentID) values (@tempStudentID) FETCH NEXT FROM rs INTO @tempStudentID END CLOSE rs * *************************************************************** * create table Orders ( Orders_ID int identity(1,1) primary key, ItemCode nvarchar(50) not null, UM nvarchar(20) not null, Quantity decimal(18,6) not null, UnitPrice decimal(18,6) not null ) --创建⽤户⾃定义表类型，在可编程性->类型性->⽤户⾃定义表类型 create type OrdersTableType as table ( ItemCode nvarchar(50) not null, UM nvarchar(20) not null, Quantity decimal(18,6) not null, UnitPrice decimal(18,6) not null ) go create procedure Pro_Orders ( @OrdersCollection OrdersTableType readonly ) as insert into Orders([ItemCode],[UM],[Quantity],[UnitPrice]) SELECT oc.[ItemCode],oc.[UM],[Quantity],oc.[UnitPrice] FROM @OrdersCollection AS oc;

go *

*/ SqlParameter[] parameters = {new SqlParameter("@OrdersCollection", ured)}; parameters[0].Value = sourceDataTable; new SQLHelper().ExecuteScalar("P_DataTable_ToHotelDB", parameters, true); } #endregion