批量插入数据到数据库的方式

出于性能的考虑。用Insert语句一条一条的插入大量数据到数据库肯定不是最好的选择。指不定还会把数据库搞死了。

前几天，用户提了需求，要求写Job实现，每天清空一个Table,然后将新发过来的数据Insert到数据库中。想着一条条Insert有点逊。于是就去查了批量插入数据的方法。看到可以用OracleBuckCopy进行批量操作（一脸惊喜），但是我想要清空表成功之后，就执行批量插入数据，如果都成功就commit,否则就rollback. 然后我就死在了这，因为OracleBuckCopy中的writeToServer不能和清空数据库共享一个Transaction. 必须是单独的Transaction. 那要是我清空了数据库，然后还插入失败了，空空如也的数据库岂不是会让我死的很难看。。

So,我就想，反正数据量不是那么大。那我就Low一点算了，一条一条Insert得了，反正没人看得见（手上的系统不止一个，事情还一起来，在忙忙人海中迷茫）。哒哒哒，屁颠颠的去改了代码，三下五除二，搞定（简直不要太高兴），帅不过三秒。发现当数据超过296条，就会死翘翘啦，一直说达到了游标最大数（ORA-01000?什么鬼？游标？）。我没用游标啊，就一个Delete和N个Insert语句啊？(一脸狗带像）。。网上一搜，说什么数据库默认游标300，可能系统占几个，所以就只能插入296条数据。不知是个啥么子原理。要去改游标数,改成多少呢？数据量不定啊，万一以后外部系统的数据过大，我设置的游标岂不是不够用?

What?

who am I?

where am I?

what is the purpose?

发现自己越走越远，于是准备洗心革面，从头来过。

OK，在一个事务中，实现清空表，然后批量插入数据。

鉴于自己不能徒手写1000行代码（活该单身），于是在网上搜搜搜，终于，让我看到了一个让我心动的代码。借鉴部分代码，测试后，性能帮帮哒。自己的功能也实现了。

So, 小小记录一下，万一以后就又有用了呢。

一，使用参数数组的方式，批量Insert数据 ,速度快，但是代码中定义参数稍显麻烦。同时，要么全部成功Insert数据库，如果失败全部都Insert失败。

我在代码中使用这种方法，是因为要做到，清空表和Insert成功都成功，则Commit，否则，回滚所有的操作。

A part of my code, just as a conference.

            //设置一个连接字符串，最好放到Config文件中，以便在不懂环境中测试的时候好修改；
            //<add name="ConnectionString" connectionString="Password=；User ID=;Data Source=“>
            //Assembly:System.Data.OracleClient.dll
            private string connectStr = ConfigurationSettings.AppSettings["connectionString"];
            OracleConnection conn = new OraceConnection(connectStr);
            OracleCommand commad = new OracleCommand();

            //这个参数需要指定每次批量Insert的记录数
            command.ArraryBindCount = recordsCount;
            //在这个SQL语句中，用到了参数，参数我们很熟悉，但是这个参数在传值的时候，用到的是数组，而不是单个的值。
            command.CommandText = "insert into student values(:stuId,:stuName,:sex)";
            conn.Open();
            //下面定义几个数组，分别是三个字段，数组的长度由参数决定
            int[] sutId = new int[recordsCount];
            string[] stuName = new string[recordsCount];
            string[] sex = new string[recordsCount];
            //为了传递参数，不可避免的要使用参数，下面会连续定义三个参数
            OracleParameter stuIdParam = new OracleParameter("stuId",OracleType.Int32);
            stuIdParam.Direction = ParameterDirection.Input;
            stuIdParam.Value = stuId;
            command.Parameters.Add(stuIdParam);
            OracleParameter stuNameParam = new OracleParameter("stuName",OracleType.VarChar2);
            stuNameParam.Direction = ParameterDirection.Input;
            stuNameParam.Value = stuName;
            command.Parameters.Add(stuNameParam);
            OracleParameter sexParam = new OracleParameter("sex",OracleType.VarChar2);
            sexParam.Direction = ParameterDirection.Input;
            sexParam.Value = sex;
            command.Parameters.Add(sexParam);
            //在下面的循环中，先把数组定义好，而不是如往常那样直接生成SQL
            for（int i = 0; int<records.count;int++)
            {
                stuId[i] = if;
                stuName[i] = i.ToString();
                sex[i] = i.ToString();
            }
            //这个调用将把参数数组传入SQL，同时写入数据库
            command.ExecuteNonQuery();

View Code

二, 用OracleBuckCopy,批量插入数据到数据库。但是要注意，只能在版本为11及以上数据库才可以用。如果代码中和服务器中的Oracle.DataAccess.dll版本不同，可以在配置文件中设置即可。

       public void BulkToDB(DataTable dt)
        {
            using (OracleBulkCopy bcp = new OracleBulkCopy(con,OracleBulkCopyOptions.UseInternalTransaction))
            {
                bcp.BatchSize = 400;
                bcp.BulkCopyTimeout = 1000;
                bcp.DestinationTableName = "";

                try
                {
                   bcp.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                   throw new Exception(ex.ToString());
                }
                finally
                {
                    bcp.Close();
                    con.Close();
                }
            }
        }

View Code

三。用SQL Loder批量插入数据，感兴趣的自己去研究，看到很多人写过这个，自己动手用用就OK了，可以点击这里看别人总结的。