上一篇文章提到了ODP,目前项目全都换成了ODP,本篇着重说一下。
ODP是技术总监当时建议我用的,效率没的说,从半小时直接提到1分钟不到。当时他只发给我了一个链接,让我自己看,具体的代码如下:
- //设置一个数据库的连接串
- 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);
- Stopwatch sw = new Stopwatch();
- sw.Start();
- //在下面的循环中,先把数组定义好,而不是像上面那样直接生成SQL
- for (int i = 0; i < recc; i++)
- {
- deptNo[i] = i;
- dname[i] = i.ToString();
- loc[i] = i.ToString();
- }
- //这个调用将把参数数组传进SQL,同时写入数据库
- command.ExecuteNonQuery();
- sw.Stop();
- System.Diagnostics.Debug.WriteLine("批量插入:" + recc.ToString()
- + "所占时间:" +sw.ElapsedMilliseconds.ToString());
原文链接:http://blog.csdn.net/yiaccount/article/details/5880139,还是建议去看原文,上面的代码我主要用来做记录用。
下面是项目中的代码,全是根据上面代码写的,不恰当的地方还望多加指正。
//二、开始导入号码
DelData();
int recc = ds.Tables[0].Rows.Count - err;
command.ArrayBindCount = recc; //这个参数需要指定每次批插入的记录数
command.CommandText = "insert into KA_BlackList (telnum,telname,inserttime,listtype,departid,operid) values(:tlenum1,:telname1,:inserttime1,:listtype1,:departid1,:operid1)";
Conn.Open();
string[] telnum2 = new string[recc];
string[] telname2 = new string[recc];
DateTime[] inserttime2 = new DateTime[recc];
decimal[] listtype2 = new decimal[recc];
//decimal[] companyid2 = new decimal[recc];
decimal[] departid2 = new decimal[recc];
decimal[] operid2 = new decimal[recc];
OracleParameter telnumPar = new OracleParameter("tlenum1", OracleDbType.Varchar2);
telnumPar.Direction = ParameterDirection.Input;
telnumPar.Value = telnum2;
command.Parameters.Add(telnumPar);
OracleParameter telnamePar = new OracleParameter("telname1", OracleDbType.Varchar2);
telnamePar.Direction = ParameterDirection.Input;
telnamePar.Value = telname2;
command.Parameters.Add(telnamePar);
OracleParameter inserttimePar = new OracleParameter("inserttime1", OracleDbType.Date);
inserttimePar.Direction = ParameterDirection.Input;
inserttimePar.Value = inserttime2;
command.Parameters.Add(inserttimePar);
OracleParameter listtypePar = new OracleParameter("listtype1", OracleDbType.Decimal);
listtypePar.Direction = ParameterDirection.Input;
listtypePar.Value = listtype2;
command.Parameters.Add(listtypePar);
//OracleParameter companyidPar = new OracleParameter("companyid1", OracleDbType.Decimal);
//companyidPar.Direction = ParameterDirection.Input;
//companyidPar.Value = companyid2;
//command.Parameters.Add(companyidPar);
OracleParameter departidPar = new OracleParameter("departid1", OracleDbType.Decimal);
departidPar.Direction = ParameterDirection.Input;
departidPar.Value = departid2;
command.Parameters.Add(departidPar);
OracleParameter operidPar = new OracleParameter("operid1", OracleDbType.Decimal);
operidPar.Direction = ParameterDirection.Input;
operidPar.Value = operid2;
command.Parameters.Add(operidPar);
int userid = int.Parse(Session["userid"].ToString());
if (CheckBox1.Checked == false)//不选姓名
{
int j = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (flag[i] == false)//正确号码才能插入
{
telnum2[j] = ds.Tables[0].Rows[i][selectnum].ToString();
telname2[j] = null;
inserttime2[j] = DateTime.Now;
listtype2[j] = 1;
//companyid2[j] = null;
departid2[j] = 1;
operid2[j] = userid;
j++;
}
}
}
else
{
int j = 0;
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
if (flag[i] == false)
{
telnum2[j] = ds.Tables[0].Rows[i][selectnum].ToString();
telname2[j] = ds.Tables[0].Rows[i][selectname].ToString();
inserttime2[j] = DateTime.Now;
listtype2[j] = 1;
//companyid2[j] = null;
departid2[j] = 1;
operid2[j] =userid;
j++;
}
}
}
int aa = command.ExecuteNonQuery();
command.Dispose();
if (Conn.State == ConnectionState.Open)
{
Conn.Close();
}
此篇完。