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对象 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中即可