C#从文本文件中读取数据大批量导入数据库

2023年6月21日发(作者：)

C#从⽂本⽂件中读取数据⼤批量导⼊数据库表结构：⽂本⽂件太⼤就不上传了。内容格式如下："号段" "所属地区" "号码类型" "区号"1300000 "北京市" "联通" "010"1300001 "江苏省常州市" "联通" "0519"1300002 "安徽省巢湖市" "联通" "0565"1300006 "江苏省南京市" "联通" "025"1300008 "湖北省武汉市" "联通" "027"使⽤两种⽅法实现插⼊：⼀、Insert⽅法：代码如下：//定义数据连接字符串 private string connStr = tionStrings["connstr"].ConnectionString; ///

/// ⽤insert⽅法从⽂本⽂件中批量导⼊到数据库表中 ///

/// /// private void btnInsert_Click(object sender, RoutedEventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); = "⽂本⽂档|*.txt"; //如果⽤户没有选择⽂件并确定则直接返回 if (alog() != true) { return; } //获得⽂件名包括路径 string fileName = me; try { //定义⼀个开始时间 DateTime startTime = ; //因为⽂件⽐较⼤，所有使⽤StreamReader的效率要⽐使⽤nes⾼ using (StreamReader sr = new StreamReader(fileName, t)) { using (SqlConnection conn = new SqlConnection(connStr)) { (); using (SqlCommand cmd = Command()) { while (!tream) {

string readStr = ne();//读取⼀⾏数据 if (With("1"))//去掉标题⾏ { string[] strs = (new char[] { 't', '"' }, EmptyEntries);//将读取的字符串按"制表符/t“和””“分割成数组 string startNum = strs[0]; string numArea = strs[1]; string numType = strs[2]; dText = "insert into T_PhoneInfo(FStartNum,FNumArea,FNumType) values(@startNum,@numArea,@numType)"; ();//清除上⼀次的参数 (new SqlParameter("@startNum", startNum)); (new SqlParameter("@numArea", numArea)); (new SqlParameter("@numType", numType)); eNonQuery(); } } } } } //结束时间-开始时间=总共花费的时间 TimeSpan ts = - startTime; ("导⼊数据成功！共花费时间:" + ng()); } catch (IOException ex) { (e); } catch (Exception ex) { (e); } } 此⽅法经过测试共花费时间：⼤约25分钟。效率太低。⼆、使⽤sqlBulkCopy⽅法：代码如下：private void btnSqlBulkCopy_Click(object sender, RoutedEventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); = "⽂本⽂档|*.txt"; //如果⽤户没有选择⽂件并确定则直接返回 if (alog() != true) { return; } //获得⽂件名包括路径 string fileName = me; try { //定义⼀个开始时间 DateTime startTime = ;

//创建内存临时数据表来存储从⽂本⽂件中读取出来的数据 using (DataTable table = new DataTable()) { //为数据表创建相对应的数据列 ("startNum"); ("numArea"); ("numType"); //因为⽂件⽐较⼤，所有使⽤StreamReader的效率要⽐使⽤nes⾼ using (StreamReader sr = new StreamReader(fileName, t)) { while (!tream) { DataRow dr = ();//创建数据⾏ string readStr = ne();//读取⼀⾏数据 if (With("1"))//去掉标题⾏ { string[] strs = (new char[] { 't', '"' }, EmptyEntries);//将读取的字符串按"制表符/t“和””“分割成数组 string startNum = strs[0]; string numArea = strs[1]; string numType = strs[2]; //往对应的 ⾏中添加数据 dr["startNum"] = startNum; dr["numArea"] = numArea; dr["numType"] = numType; (dr);//将创建的数据⾏添加到table中 } } } SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr); ationTableName = "T_PhoneInfo";//设置数据库中对象的表名 //设置数据表table和数据库中表的列对应关系 ("startNum", "FStartNum"); ("numArea", "FNumArea"); ("numType", "FNumType"); oServer(table);//将数据表table复制到数据库中 TimeSpan ts = - startTime; ("共导⼊" ++ "条数据，总共花费时间:" + ng()); }

} catch (IOException ex) { (e); } catch (Exception ex) { (e); } } 此⽅法共花费时间差距啊，SqlBulkCopy在批量数据导⼊的时候效率好⾼。

2023年6月21日发(作者：)

C#从⽂本⽂件中读取数据⼤批量导⼊数据库表结构：⽂本⽂件太⼤就不上传了。内容格式如下："号段" "所属地区" "号码类型" "区号"1300000 "北京市" "联通" "010"1300001 "江苏省常州市" "联通" "0519"1300002 "安徽省巢湖市" "联通" "0565"1300006 "江苏省南京市" "联通" "025"1300008 "湖北省武汉市" "联通" "027"使⽤两种⽅法实现插⼊：⼀、Insert⽅法：代码如下：//定义数据连接字符串 private string connStr = tionStrings["connstr"].ConnectionString; ///

/// ⽤insert⽅法从⽂本⽂件中批量导⼊到数据库表中 ///

/// /// private void btnInsert_Click(object sender, RoutedEventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); = "⽂本⽂档|*.txt"; //如果⽤户没有选择⽂件并确定则直接返回 if (alog() != true) { return; } //获得⽂件名包括路径 string fileName = me; try { //定义⼀个开始时间 DateTime startTime = ; //因为⽂件⽐较⼤，所有使⽤StreamReader的效率要⽐使⽤nes⾼ using (StreamReader sr = new StreamReader(fileName, t)) { using (SqlConnection conn = new SqlConnection(connStr)) { (); using (SqlCommand cmd = Command()) { while (!tream) {

string readStr = ne();//读取⼀⾏数据 if (With("1"))//去掉标题⾏ { string[] strs = (new char[] { 't', '"' }, EmptyEntries);//将读取的字符串按"制表符/t“和””“分割成数组 string startNum = strs[0]; string numArea = strs[1]; string numType = strs[2]; dText = "insert into T_PhoneInfo(FStartNum,FNumArea,FNumType) values(@startNum,@numArea,@numType)"; ();//清除上⼀次的参数 (new SqlParameter("@startNum", startNum)); (new SqlParameter("@numArea", numArea)); (new SqlParameter("@numType", numType)); eNonQuery(); } } } } } //结束时间-开始时间=总共花费的时间 TimeSpan ts = - startTime; ("导⼊数据成功！共花费时间:" + ng()); } catch (IOException ex) { (e); } catch (Exception ex) { (e); } } 此⽅法经过测试共花费时间：⼤约25分钟。效率太低。⼆、使⽤sqlBulkCopy⽅法：代码如下：private void btnSqlBulkCopy_Click(object sender, RoutedEventArgs e) { OpenFileDialog ofd = new OpenFileDialog(); = "⽂本⽂档|*.txt"; //如果⽤户没有选择⽂件并确定则直接返回 if (alog() != true) { return; } //获得⽂件名包括路径 string fileName = me; try { //定义⼀个开始时间 DateTime startTime = ;

//创建内存临时数据表来存储从⽂本⽂件中读取出来的数据 using (DataTable table = new DataTable()) { //为数据表创建相对应的数据列 ("startNum"); ("numArea"); ("numType"); //因为⽂件⽐较⼤，所有使⽤StreamReader的效率要⽐使⽤nes⾼ using (StreamReader sr = new StreamReader(fileName, t)) { while (!tream) { DataRow dr = ();//创建数据⾏ string readStr = ne();//读取⼀⾏数据 if (With("1"))//去掉标题⾏ { string[] strs = (new char[] { 't', '"' }, EmptyEntries);//将读取的字符串按"制表符/t“和””“分割成数组 string startNum = strs[0]; string numArea = strs[1]; string numType = strs[2]; //往对应的 ⾏中添加数据 dr["startNum"] = startNum; dr["numArea"] = numArea; dr["numType"] = numType; (dr);//将创建的数据⾏添加到table中 } } } SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr); ationTableName = "T_PhoneInfo";//设置数据库中对象的表名 //设置数据表table和数据库中表的列对应关系 ("startNum", "FStartNum"); ("numArea", "FNumArea"); ("numType", "FNumType"); oServer(table);//将数据表table复制到数据库中 TimeSpan ts = - startTime; ("共导⼊" ++ "条数据，总共花费时间:" + ng()); }

} catch (IOException ex) { (e); } catch (Exception ex) { (e); } } 此⽅法共花费时间差距啊，SqlBulkCopy在批量数据导⼊的时候效率好⾼。