C#完成SQL批量拔出數據到表的辦法。本站提示廣大學習愛好者:(C#完成SQL批量拔出數據到表的辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是C#完成SQL批量拔出數據到表的辦法正文
本文實例講述了C#完成SQL批量拔出數據到表的辦法。分享給年夜家供年夜家參考,詳細以下:
#region 贊助實例:SQL 批量拔出數據 多種辦法 /// <summary> /// SqlBulkCopy往數據庫中批量拔出數據 /// </summary> /// <param name="sourceDataTable">數據源表</param> /// <param name="targetTableName">辦事器上目的表</param> /// <param name="mapping">創立新的列映照,並應用列序號援用源列和目的列的列稱號。</param> 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(SQLHelper.ConnectionString); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的數據有用批量加載sql server表中 bulkCopy.DestinationTableName = targetTableName; //辦事器上目的表的稱號 bulkCopy.BatchSize = sourceDataTable.Rows.Count; //每批次中的行數 try { conn.Open(); if (sourceDataTable != null && sourceDataTable.Rows.Count != 0) { for (int i = 0; i < mapping.Length; i++) bulkCopy.ColumnMappings.Add(mapping[i]); //將供給的數據源中的一切行復制到目的表中 bulkCopy.WriteToServer(sourceDataTable ); } } catch (Exception ex) { //throw ex; Common.WriteTextLog("BulkToDB", ex.Message); } finally { conn.Close(); if (bulkCopy != null) bulkCopy.Close(); } } /// <summary> /// SQL2008以上方可支撐自界說表類型 :挪用存儲進程游標-往數據庫中批量拔出數據 ,留意 /// </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 ) --創立用戶自界說表類型,在可編程性->類型性->用戶自界說表類型 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
願望本文所述對年夜家C#法式設計有所贊助。