之前做公众号,需要用到批量操作。最开始是一条一条操作,效率太低了,需要频繁打开关闭数据库链接。最后在网上搜到了三种方法:DataAdapter,SqlBulkCopy,表值参数。
DataAdapter用作DataSet 和数据源之间的桥接器以便检索和保存数据,可以批量插入,更新,删除数据。
SqlBulkCopy原理是采用了SQL Server的BCP协议进行数据的批量复制,所以只能批量插入数据,大数据性能上要高于DataAdapter。
表值参数是 SQL Server 2008 中的新参数类型,批量插入的性能和SqlBulkCopy差不多,在此就不介绍了。
DataAdapter方法:
public static bool BatchUpdate(string cmdText, DataTable dt, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); using (SqlConnection conn = new SqlConnection(connectionString)) { try { PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; adapter.UpdateBatchSize = 800; SqlCommandBuilder builder = new SqlCommandBuilder(adapter); adapter.Update(dt); //清空SqlCommand中的参数列表 cmd.Parameters.Clear(); return true; } catch (Exception ex) { throw ex; } } } private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) { //判断数据库连接状态 if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; //cmd.CommandTimeout //判断是否需要事物处理 if (trans != null) cmd.Transaction = trans; cmd.CommandType = cmdType; if (cmdParms != null) { foreach (SqlParameter parm in cmdParms) cmd.Parameters.Add(parm); } }
SqlBulkCopy方法:
public static bool BulkCopy(DataTable dt, string tableName, SqlBulkCopyColumnMapping[] columnMap) { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn)) { try { conn.Open(); sqlBulkCopy.DestinationTableName = tableName; //一次处理大小,视数据而定(普通800左右最好) sqlBulkCopy.BatchSize = 800; foreach (SqlBulkCopyColumnMapping map in columnMap) sqlBulkCopy.ColumnMappings.Add(map); sqlBulkCopy.WriteToServer(dt); return true; } catch (Exception ex) { throw ex; } } } }