#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

 

 

 

 

#region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中

/// <summary>

/// 使用SqlBulkCopy将DataTable中的数据批量插入数据库中

/// </summary>

/// <param name="strTableName">数据库中对应的表名</param>

/// <param name="dtData">数据集</param>

public void SqlBulkCopyInsert(string strTableName, DataTable dtData)

{

string ConStr = connectionString;// 数据库连接字符串

try

{

using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(ConStr))//引用SqlBulkCopy

{

sqlRevdBulkCopy.DestinationTableName = strTableName;//数据库中对应的表名

sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;//有几行数据

sqlRevdBulkCopy.WriteToServer(dtData);//数据导入数据库

sqlRevdBulkCopy.Close();//关闭连接

}

}

catch (Exception ex)

{

WriteErrorLog(ex.Message + "数据库处理出错654行。SqlBulkCopyInsert");

throw (ex);

}

}

#endregion

 

 

 

private static void DataTableToSQLServer( DataTable dt)

{

string connectionString = GetConnectionString();

using (SqlConnection destinationConnection =new SqlConnection(connectionString))

{

destinationConnection.Open();

using (SqlBulkCopy bulkCopy =new SqlBulkCopy(destinationConnection))

{

try

{

bulkCopy.DestinationTableName = "T_EIInformation";//要插入的表的表明

bulkCopy.ColumnMappings.Add("Email", "Email");//映射字段名 DataTable列名 ,数据库 对应的列名

bulkCopy.ColumnMappings.Add("author", "author");

bulkCopy.ColumnMappings.Add("Title", "Title");

bulkCopy.ColumnMappings.Add("Type", "Type");

bulkCopy.ColumnMappings.Add("confName", "confName");

bulkCopy.ColumnMappings.Add("Language", "Language");

bulkCopy.ColumnMappings.Add("Publicationyear", "Publicationyear");

bulkCopy.ColumnMappings.Add("Conferencelocation", "Conferencelocation");

bulkCopy.WriteToServer(dt);

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

finally

{

// Close the SqlDataReader. The SqlBulkCopy

// object is automatically closed at the end

// of the using block.

}

}

}

}