ADO.NET基础学习SqlBulkCopy

<!-- lang: c# -->
using System;

using System.Collections.Generic; using System.Linq; using System.Text; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; using System.Configuration; using Microsoft.Win32; using System.IO; using System.Data.SqlClient; using System.Data;

namespace 手机号码导入查询 { /// <summary> /// MainWindow.xaml 的交互逻辑 /// </summary> public partial class MainWindow : Window { public MainWindow() { InitializeComponent(); }

    private void Import_btn_Click(object sender, RoutedEventArgs e)
    {
        string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
        //MessageBox.Show(connStr);

        OpenFileDialog ofd = new OpenFileDialog();
        ofd.Filter = "文件文件|*.txt";
        if (ofd.ShowDialog() != true)
        {
            return;
        }
        //大数据插入

        DateTime startTime = DateTime.Now;

        string[] lines = File.ReadLines(ofd.FileName, Encoding.Default).ToArray();

        DataTable table = new DataTable();
        table.Columns.Add("StartTelNum");   //创建本地表的列
        table.Columns.Add("Num_Area");
        table.Columns.Add("Num_Type");
        table.Columns.Add("Num_Code");

        for (int i = 1; i < lines.Count(); i++)
        {
            string line = lines[i];
            string[] strs = line.Split('\t'); ;
            string startTelNum = strs[0];  //开始号码
            string city = strs[1];   //城市
            city = city.Trim('"'); //去掉两边的双引号:"北京市"
            string telType = strs[2];  //号码类型
            telType = telType.Trim('"');
            string code_num = strs[3];  //区号
            code_num = code_num.Trim('"');

            DataRow row = table.NewRow();   //创建一个DataRow对象
            row["StartTelNum"] = startTelNum;  //给列赋值  一定要在一开始创建table.Clumns添加列
            row["Num_Area"] = city;
            row["Num_Type"] = telType;
            row["Num_Code"] = code_num;
            table.Rows.Add(row);  //插入DataRow对象
            
        }
         
        
        
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))  //爆发bulkCopy
        {
            bulkCopy.DestinationTableName = "T_TelNum";  //把这个对象copy到T_TelNum这个表
            bulkCopy.ColumnMappings.Add("StartTelNum","StartTelNum");  //DataTable row字段和数据库中的对应关系
            bulkCopy.ColumnMappings.Add("Num_Area","Num_Area");
            bulkCopy.ColumnMappings.Add("Num_Type","Num_Type");
            bulkCopy.ColumnMappings.Add("Num_Code","Num_Code");
            //添加DataTable中列名和数据库表汇总列名的映射
            bulkCopy.WriteToServer(table);  //写入到数表中
            
        }
        TimeSpan ts = DateTime.Now - startTime;
        MessageBox.Show("总共用了:"+ts.ToString());
       
        



        //传统插入方法

        //DateTime startTime = DateTime.Now;

        跳过第一行表头
        //for (int i = 1; i < lines.Count(); i++)
        //{
        //    string line = lines[i];
        //    string[] strs = line.Split('\t'); ;
        //    string startTelNum = strs[0];  //开始号码
        //    string city = strs[1];   //城市
        //    city = city.Trim('"'); //去掉两边的双引号:"北京市"
        //    string telType = strs[2];  //号码类型
        //    telType = telType.Trim('"');
        //    string code_num = strs[3];  //区号
        //    code_num = code_num.Trim('"');

        //    //SqlHelper.ExecuteNonQuery("insert into T_TelNum (StartTelNum,Num_Area,Num_Type,Num_Code) values('1300000','北京市','联通',010)");

        //    //插入到数据库

        //    //数据库连接池:ADO.net会尽可能的复用连接池中的连接

        //    SqlHelper.ExecuteNonQuery(@"insert into T_TelNum (StartTelNum,Num_Area,Num_Type,Num_Code) values(@StartTelNum,@Num_Area,@Num_Type,@Num_Code)",
        //        new SqlParameter("@StartTelNum",startTelNum),
        //        new SqlParameter("@Num_Area",city),
        //        new SqlParameter("@Num_Type",telType),
        //        new SqlParameter("@Num_Code",code_num));

        //    TimeSpan ts = DateTime.Now - startTime;  //DateTime 相减得到TimeSpan类型
        //    double ellapsedSec = ts.TotalSeconds;   //已经耗用的秒数
        //   // double totalSec = (ellapsedSec / (i + 1)) * lines.Length;  //先乘后除精确
        //    double totalSec = ellapsedSec * lines.Length /(i+1) /60;  //估计需要的时间

        //    MessageBox.Show("大概需要"+totalSec.ToString());


        //}

        

        MessageBox.Show("导入完成！");

    }
}

}

转载于:https://my.oschina.net/u/1156135/blog/181232