批量导入数据的性能问题

杨恩锋

本帖最后由吴春晟于 2011-11-17 12:02 编辑

/// <summary>
/// 进行批量往数据库插入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpdate_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLSERVER_WCS;AttachDbFilename=|DataDirectory|\DBPhoneNO.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
foreach (string file in filelists)//遍历文件列表中文件
{
FileInfo fileinfo = new FileInfo(file);
string str = fileinfo.Name.Remove(fileinfo.Name.LastIndexOf("."));
using (FileStream filestream = File.OpenRead(file))
{
using (StreamReader streamreader = new StreamReader(filestream, System.Text.Encoding.Default))
{
string line = null;
while (((line = streamreader.ReadLine()) != null))
{
string[] strs = line.Split('-');
string phoneNOStart = strs[0];
string phoneNOEnd = strs[1];
string city = strs[2];
cmd.CommandText = "insert into T_PhoneNO(PhoneNOStart,PhoneNOEnd,City,PromaryAndNet) values(@PhoneNOStart,@PhoneNOEnd,@City,@PromaryAndNet)";
cmd.Parameters.Clear();//清除Parameters中参数
cmd.Parameters.Add(new SqlParameter("PhoneNOStart", phoneNOStart));
cmd.Parameters.Add(new SqlParameter("PhoneNOEnd", phoneNOEnd));
cmd.Parameters.Add(new SqlParameter("City", city));
cmd.Parameters.Add(new SqlParameter("PromaryAndNet", str));
cmd.ExecuteNonQuery();
}
}
}
}
}
}
MessageBox.Show("数据导入成功");
}

复制代码

在做习题手机号码归属地查询时,我第一次写的批量更新数据库代码如上:

考虑到这里多次执行cmd.ExecuteNonQuery();应该会影响效率,那么对代码做如下改动;

/// <summary>
/// 进行批量往数据库插入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnUpdate_Click(object sender, EventArgs e)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLSERVER_WCS;AttachDbFilename=|DataDirectory|\DBPhoneNO.mdf;Integrated Security=True;User Instance=True"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
foreach (string file in filelists)//遍历文件列表中文件
{
FileInfo fileinfo = new FileInfo(file);
string str = fileinfo.Name.Remove(fileinfo.Name.LastIndexOf("."));
using (FileStream filestream = File.OpenRead(file))
{
using (StreamReader streamreader = new StreamReader(filestream, System.Text.Encoding.Default))
{
string line = null;
//int count = 0;
while (((line = streamreader.ReadLine()) != null))
{
//count = count + 1;
string[] strs = line.Split('-');
string phoneNOStart = strs[0];
string phoneNOEnd = strs[1];
string city = strs[2];
//放弃cmd.Parameters,防止对cmd.Parameters最后一次ADD的参数进行反复添加
//cmd.CommandText += "insert into T_PhoneNO(PhoneNOStart,PhoneNOEnd,City,PromaryAndNet) values(@PhoneNOStart,@PhoneNOEnd,@City,@PromaryAndNet)";
//cmd.Parameters.Clear();//清除Parameters中参数
//cmd.Parameters.Add(new SqlParameter("PhoneNOStart", phoneNOStart));
//cmd.Parameters.Add(new SqlParameter("PhoneNOEnd", phoneNOEnd));
//cmd.Parameters.Add(new SqlParameter("City", city));
//cmd.Parameters.Add(new SqlParameter("PromaryAndNet", str));
cmd.CommandText += "insert into T_PhoneNO(PhoneNOstart,PhoneNOEnd,City,PromaryAndNet) values ('" + phoneNOStart + "','" + phoneNOEnd + "','" + city + "','" + str + "')";
//cmd.ExecuteNonQuery();
}
}
}
}
cmd.ExecuteNonQuery();
}
}
MessageBox.Show("数据导入成功");
}

复制代码

同样能实现批量更新数据,而且只用执行一次cmd.ExecuteNonQuery(); 但无法验证效率上是否有提升?

谁帮忙验证下,或者提供下更有效率的方法?

杨楠 · 杨楠

...偶咋觉得你酱紫插只插入了一条数据啊...每次cmd的comandtext不都会被下一条冲掉么??

吴春晟 · 吴春晟

小色猫发表于 2011-11-19 16:10
...偶咋觉得你酱紫插只插入了一条数据啊...每次cmd的comandtext不都会被下一条冲掉么?? ...

不会,我给Command.CommandText 赋值时用的是"+="

帐号		自动登录	找回密码
密码			加入黑马

批量导入数据的性能问题

评分

2 个回复

评分

评分