本文實(shí)例講述了C#實(shí)現(xiàn)SQL批量插入數(shù)據(jù)到表的方法。分享給大家供大家參考，具體如下：
#region 幫助實(shí)例：SQL 批量插入數(shù)據(jù) 多種方法/// <summary>/// SqlBulkCopy往數(shù)據(jù)庫中批量插入數(shù)據(jù)/// </summary>/// <param name="sourceDataTable">數(shù)據(jù)源表</param>/// <param name="targetTableName">服務(wù)器上目標(biāo)表</param>/// <param name="mapping">創(chuàng)建新的列映射，并使用列序號(hào)引用源列和目標(biāo)列的列名稱。</param>public static void BulkToDB(DataTable sourceDataTable, string targetTableName, SqlBulkCopyColumnMapping[] mapping){ /* 調(diào)用方法 -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(SQLHelper.ConnectionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的數(shù)據(jù)有效批量加載sql server表中 bulkCopy.DestinationTableName = targetTableName; //服務(wù)器上目標(biāo)表的名稱 bulkCopy.BatchSize = sourceDataTable.Rows.Count; //每一批次中的行數(shù) try { conn.Open(); if (sourceDataTable != null && sourceDataTable.Rows.Count != 0) { for (int i = 0; i < mapping.Length; i++) bulkCopy.ColumnMappings.Add(mapping[i]); //將提供的數(shù)據(jù)源中的所有行復(fù)制到目標(biāo)表中 bulkCopy.WriteToServer(sourceDataTable ); } } catch (Exception ex) { //throw ex; Common.WriteTextLog("BulkToDB", ex.Message); } finally { conn.Close(); if (bulkCopy != null) bulkCopy.Close(); }}/// <summary>/// SQL2008以上方可支持自定義表類型 :調(diào)用存儲(chǔ)過程游標(biāo)-往數(shù)據(jù)庫中批量插入數(shù)據(jù) ，注意/// </summary>/// <param name="sourceDataTable"></param>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 ) --創(chuàng)建用戶自定義表類型，在可編程性->類型性->用戶自定義表類型 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", SqlDbType.Structured)}; parameters[0].Value = sourceDataTable; new SQLHelper().ExecuteScalar("P_DataTable_ToHotelDB", parameters, true);}#endregion 
