将DataTable中的数据批量插入数据库

///

/// 初始化一个DataTable类型的数据源
/// Author : AnDequan
/// Date : 2011-3-14
///

/// Source
private DataTable InitSource()
{
DataTable dtUserAdd = new DataTable();
dtUserAdd.Columns.Add(new DataColumn(“ID”, typeof(int)));
dtUserAdd.Columns.Add(new DataColumn(“UserName”, typeof(string)));
dtUserAdd.Columns.Add(new DataColumn(“UserPwd”, typeof(string)));
DataRow drTemp = null;
for (int i = 0; i &lt; 100; i++) { drTemp = dtUserAdd.NewRow(); drTemp["ID"] = 0; drTemp["UserName"] = "测试" + (i + 1); drTemp["UserPwd"] = "密码" + (i + 1); dtUserAdd.Rows.Add(drTemp); } return dtUserAdd; } ///

/// 一次性把DataTable中的数据插入数据库
/// Author : AnDequan
/// Date : 2011-3-14
///

/// DataTable数据源 /// true – 成功，false – 失败
public bool AddDataTableToDB(DataTable source)
{
SqlTransaction tran = null;//声明一个事务对象
try
{
using (SqlConnection conn = new SqlConnection(“server=.;uid=sa;pwd=sa;database=Test;”))
{
conn.Open();//打开链接
using (tran = conn.BeginTransaction())
{
using (SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
{
copy.DestinationTableName = “AnDequan.dbo.[User]”; //指定服务器上目标表的名称
copy.WriteToServer(InitSource()); //执行把DataTable中的数据写入DB
tran.Commit(); //提交事务
return true; //返回True 执行成功！
}
}
}
}
catch (Exception ex)
{
if (null != tran)
tran.Rollback();
//LogHelper.Add(ex);
return false;//返回False 执行失败！
}
}