批量导入数据的性能问题 - 黑马程序员技术交流社区

/// <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("数据导入成功");
}

复制代码

/// <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("数据导入成功");
}

复制代码