using System.Data.SqlClient;
private bool TransferData2DB(DataTable dt)
{
bool bResult = false;
SqlConnection conn = new SqlConnection(SqlHelper.ConnString);
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);//创建SqlBulkCopy对象
// bulkCopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.KeepNulls, tran);
try
{
sqlBulkCopy.DestinationTableName = cboTableName.Text;//目标数据库表名
sqlBulkCopy.ColumnMappings.Clear();
for (int j = 0; j < dt.Columns.Count; j++)
{
sqlBulkCopy.ColumnMappings.Add((string)dt.Columns[j].ColumnName, (string)dt.Columns[j].ColumnName);//匹配列名
}
sqlBulkCopy.WriteToServer(dt);//将源表中的数据写入数据库中目标表中
tran.Commit();
bResult = true;
}
catch (Exception ex)
{
tran.Rollback();
Common.DisplayMsg(this.Text, ex.Message);
bResult= false;
}
finally
{
sqlBulkCopy.Close();
GC.Collect();
}
return bResult;
}
提示:
1,要指定数据库连接字符串 SqlHelper.ConnString
关于数据库连接字符串,一般有2种格式:
(1),Windows验证
Common.ConnString = @"Initial Catalog=数据库名;Data Source=电脑名或IP地址;Integrated Security=SSPI";
(2), Sql混合验证
Common.ConnString = @"server=电脑名或IP地址;database=数据库名;uid=用户名;password=密码";
2,要指定目标数据库表名 cboTableName.Text (这2个换成你自己对应的内容就好了)
3,事先把需要批量导入的数据放到dataTable中即可