C# 海量数据瞬间插入到数据库的方法
当我们在数据库中进行大量的数据追加时，是不是经常因为数据量过大而苦恼呢？
而所谓的海量数据，一般也是上万级的数据，比如我们要添加一百万条数据，应该如何提高它的效率呢？
Oracle数据库：
普通肉垫式
什么叫批量插入呢，就是一次性插入一批数据，我们可以把这批数据理解为一个大的数组，而这些全部只通过一个SQL来实现，而在传统方式下，需要调用很多次的SQL才可以完成，这就是著名的“数组绑定”的功能。我们先来看一下传统方式下，插入多行记录的操作方式：
//设置一个数据库的连接串， string connectStr = "User Id=scott;Password=tiger;Data Source="; OracleConnection conn = new OracleConnection(connectStr); OracleCommand command = new OracleCommand(); command.Connection = conn; conn.Open(); //通过循环写入大量的数据，这种方法显然是肉垫 for (int i = 0; i < recc; i++) { string sql = "insert into dept values(" + i.ToString() + "," + i.ToString() + "," + i.ToString() + ")"; command.CommandText = sql; command.ExecuteNonQuery(); }
带事务的栗子：
string result; //创建连接 var conn = new OracleConnection(_connectStr); conn.Open(); var tran = conn.BeginTransaction(); //事务 try { CreateTable(jiFenZhuiSuGuanXis.Select(info => info.TableGroup).Distinct().ToList()); //创建数据库表 带有分表要求 //创建Command 并循环插入数据 var command = conn.CreateCommand(); foreach (var guanXi in jiFenZhuiSuGuanXis) { //插入 var insertStr = string.Format( "insert into {5} values(‘{0}‘,‘{1}‘,‘{2}‘,‘{3}‘,‘{4}‘)", "","","","",""); command.CommandText = insertStr; command.ExecuteNonQuery(); } tran.Commit(); result = "成功"; } catch (OracleException ex) { tran.Rollback(); result = "出现错误。\n"+ex.Message; LogHelper.WriteLog("UpLoad.OracleException捕获异常。\n", ex); } catch (Exception ex) { result = "出现错误。\n" + ex.Message; LogHelper.WriteLog("UpLoad.Exception捕获异常。\n", ex); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } return result;
使用ODP特性
//设置一个数据库的连接串 string connectStr = "User Id=scott;Password=tiger;Data Source="; OracleConnection conn = new OracleConnection(connectStr); OracleCommand command = new OracleCommand(); command.Connection = conn; //到此为止，还都是我们熟悉的代码，下面就要开始喽 //这个参数需要指定每次批插入的记录数 command.ArrayBindCount = recc; //在这个命令行中,用到了参数,参数我们很熟悉,但是这个参数在传值的时候 //用到的是数组,而不是单个的值,这就是它独特的地方 command.CommandText = "insert into dept values(:deptno, :deptname, :loc)"; conn.Open(); //下面定义几个数组,分别表示三个字段,数组的长度由参数直接给出 int[] deptNo = new int[recc]; string[] dname = new string[recc]; string[] loc = new string[recc]; // 为了传递参数,不可避免的要使用参数,下面会连续定义三个 // 从名称可以直接看出每个参数的含义,不在每个解释了 OracleParameter deptNoParam = new OracleParameter("deptno", OracleDbType.Int32); deptNoParam.Direction = ParameterDirection.Input; deptNoParam.Value = deptNo; command.Parameters.Add(deptNoParam); OracleParameter deptNameParam = new OracleParameter("deptname", OracleDbType.Varchar2); deptNameParam.Direction = ParameterDirection.Input; deptNameParam.Value = dname; command.Parameters.Add(deptNameParam); OracleParameter deptLocParam = new OracleParameter("loc", OracleDbType.Varchar2); deptLocParam.Direction = ParameterDirection.Input; deptLocParam.Value = loc; command.Parameters.Add(deptLocParam); //在下面的循环中,先把数组定义好,而不是像上面那样直接生成SQL for (int i = 0; i < recc; i++) { deptNo[i] = i; dname[i] = i.ToString(); loc[i] = i.ToString(); } //这个调用将把参数数组传进SQL,同时写入数据库 command.ExecuteNonQuery();
如果插入多张表格的数据 Command 需要重新new
string result; //创建连接 var conn = new OracleConnection(_connectStr); conn.Open(); var tran = conn.BeginTransaction(); //事务 try { //创建数据库表 分表要求 CreateTable(jiFenZhuiSuGuanXis.Select(info => info.TableGroup).Distinct().ToList()); //根据分表名称 将数据分组 var dataTableGroup = jiFenZhuiSuGuanXis.GroupBy(j => j.TableGroup); foreach (var group in dataTableGroup) { var command = conn.CreateCommand(); //创建Command command.ArrayBindCount = group.Count(); //插入数量 //插入语句 command.CommandText = string.Format( "insert into {0} values(:jiid,:productcode,:productspec,:zhuisucode,:jifencode)", group.Key.ToUpper()); #region 定义传递参数 var idParam = new string[group.Count()]; var productCodeParam = new string[group.Count()]; var productSpecParam = new string[group.Count()]; var zhuisucodeParam = new string[group.Count()]; var jifencodeParam = new string[group.Count()]; //定义传递参数 command.Parameters.AddRange(new[] { new OracleParameter("jiid", OracleDbType.NVarchar2) { Direction = ParameterDirection.Input, Value = idParam }, new OracleParameter("productcode", OracleDbType.NVarchar2) { Direction = ParameterDirection.Input, Value = productCodeParam }, new OracleParameter("productspec", OracleDbType.NVarchar2) { Direction = ParameterDirection.Input, Value = productSpecParam }, new OracleParameter("zhuisucode", OracleDbType.NVarchar2) { Direction = ParameterDirection.Input, Value = zhuisucodeParam }, new OracleParameter("jifencode", OracleDbType.NVarchar2) { Direction = ParameterDirection.Input, Value = jifencodeParam } } ); #endregion #region 参数赋值 var i = 0; foreach (var xi in group) { idParam[i] = xi.Id; //ID参数 productCodeParam[i] = xi.ProductCode; //productcode参数 productSpecParam[i] = xi.ProductSpec; //productspec参数 zhuisucodeParam[i] = xi.ZhuiSuCode; //zhuisucode参数 jifencodeParam[i] = xi.JiFenCode; //JiFenCode参数 i++; } #endregion command.ExecuteNonQuery(); //执行 } tran.Commit(); result = "成功"; } catch (OracleException ex) { tran.Rollback(); result = "出现错误。\n" + ex.Message; LogHelper.WriteLog("UpLoadOdp.OracleException捕获异常。\n", ex); } catch (Exception ex) { result = "出现错误。\n" + ex.Message; LogHelper.WriteLog("UpLoadOdp.Exception捕获异常。\n", ex); } finally { if (conn.State == ConnectionState.Open) conn.Close(); } return result;