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

表结构：

文本文件太大就不上传了。内容格式如下：

"号段" "所属地区" "号码类型" "区号"
1300000 "北京市" "联通" "010"
1300001 "江苏省常州市" "联通" "0519"
1300002 "安徽省巢湖市" "联通" "0565"
1300006 "江苏省南京市" "联通" "025"
1300008 "湖北省武汉市" "联通" "027"

使用两种方法实现插入：

一、Insert方法：

代码如下：

//定义数据连接字符串          private  string  connStr = ConfigurationManager.ConnectionStrings[ "connstr" ].ConnectionString;            /// <summary>          /// 用insert方法从文本文件中批量导入到数据库表中          /// </summary>          /// <param name="sender"></param>          /// <param name="e"></param>          private  void  btnInsert_Click( object  sender, RoutedEventArgs e)          {              OpenFileDialog ofd = new  OpenFileDialog();              ofd.Filter = "文本文档|*.txt" ;              //如果用户没有选择文件并确定则直接返回              if  (ofd.ShowDialog() != true )              {                  return ;              }              //获得文件名包括路径              string  fileName = ofd.FileName;                try              {                  //定义一个开始时间                  DateTime startTime = DateTime.Now;                  //因为文件比较大，所有使用StreamReader的效率要比使用File.ReadLines高                  using  (StreamReader sr = new  StreamReader(fileName, Encoding.Default))                  {                      using  (SqlConnection conn = new  SqlConnection(connStr))                      {                          conn.Open();                          using  (SqlCommand cmd = conn.CreateCommand())                          {                              while  (!sr.EndOfStream)                              {                                                                    string  readStr = sr.ReadLine(); //读取一行数据                                  if  (readStr.StartsWith( "1" )) //去掉标题行                                  {                                      string [] strs = readStr.Split( new  char [] { '\t' , '"'  }, StringSplitOptions.RemoveEmptyEntries); //将读取的字符串按"制表符/t“和””“分割成数组                                        string  startNum = strs[0];                                      string  numArea = strs[1];                                      string  numType = strs[2];                                        cmd.CommandText = "insert into T_PhoneInfo(FStartNum,FNumArea,FNumType) values(@startNum,@numArea,@numType)" ;                                      cmd.Parameters.Clear(); //清除上一次的参数                                      cmd.Parameters.Add( new  SqlParameter( "@startNum" , startNum));                                      cmd.Parameters.Add( new  SqlParameter( "@numArea" , numArea));                                      cmd.Parameters.Add( new  SqlParameter( "@numType" , numType));                                      cmd.ExecuteNonQuery();                                  }                              }                          }                      }                    }                    //结束时间-开始时间=总共花费的时间                  TimeSpan ts = DateTime.Now - startTime;                  MessageBox.Show( "导入数据成功！共花费时间:"  + ts.ToString());                }              catch  (IOException ex)              {                  MessageBox.Show(ex.Message);              }              catch  (Exception ex)              {                  MessageBox.Show(ex.Message);              }          }

 此方法经过测试共花费时间：大约25分钟。效率太低。

二、使用sqlBulkCopy方法：

代码如下：

private  void  btnSqlBulkCopy_Click( object  sender, RoutedEventArgs e)          {              OpenFileDialog ofd = new  OpenFileDialog();              ofd.Filter = "文本文档|*.txt" ;              //如果用户没有选择文件并确定则直接返回              if  (ofd.ShowDialog() != true )              {                  return ;              }              //获得文件名包括路径              string  fileName = ofd.FileName;                try              {                  //定义一个开始时间                  DateTime startTime = DateTime.Now;                                    //创建内存临时数据表来存储从文本文件中读取出来的数据                  using  (DataTable table = new  DataTable())                  {                      //为数据表创建相对应的数据列                      table.Columns.Add( "startNum" );                      table.Columns.Add( "numArea" );                      table.Columns.Add( "numType" );                        //因为文件比较大，所有使用StreamReader的效率要比使用File.ReadLines高                      using  (StreamReader sr = new  StreamReader(fileName, Encoding.Default))                      {                          while  (!sr.EndOfStream)                          {                              DataRow dr = table.NewRow(); //创建数据行                                string  readStr = sr.ReadLine(); //读取一行数据                              if  (readStr.StartsWith( "1" )) //去掉标题行                              {                                  string [] strs = readStr.Split( new  char [] { '\t' , '"'  }, StringSplitOptions.RemoveEmptyEntries); //将读取的字符串按"制表符/t“和””“分割成数组                                    string  startNum = strs[0];                                  string  numArea = strs[1];                                  string  numType = strs[2];                                    //往对应的 行中添加数据                                  dr[ "startNum" ] = startNum;                                  dr[ "numArea" ] = numArea;                                  dr[ "numType" ] = numType;                                    table.Rows.Add(dr); //将创建的数据行添加到table中                              }                          }                      }                        SqlBulkCopy bulkCopy = new  SqlBulkCopy(connStr);                        bulkCopy.DestinationTableName = "T_PhoneInfo" ; //设置数据库中对象的表名                        //设置数据表table和数据库中表的列对应关系                      bulkCopy.ColumnMappings.Add( "startNum" , "FStartNum" );                      bulkCopy.ColumnMappings.Add( "numArea" , "FNumArea" );                      bulkCopy.ColumnMappings.Add( "numType" , "FNumType" );                      bulkCopy.WriteToServer(table); //将数据表table复制到数据库中                        TimeSpan ts = DateTime.Now - startTime;                      MessageBox.Show( "共导入"  +table.Rows.Count+ "条数据，总共花费时间:"  + ts.ToString());                  }                                }              catch  (IOException ex)              {                  MessageBox.Show(ex.Message);              }              catch  (Exception ex)              {                  MessageBox.Show(ex.Message);              }            }

 此方法共花费时间

差距啊，SqlBulkCopy在批量数据导入的时候效率好高。