自学.net(5)SqlBulkCopy批量数据插入

插个1万条的数据用了40多秒,我感觉我这个代码还是有问题

using Microsoft.Win32;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
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;

namespace 导入数据优化
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

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

            OpenFileDialog ofg = new OpenFileDialog();
            ofg.Filter = "文本|*.txt";
            if (ofg.ShowDialog() == false)
            {
                return;
            }
            IEnumerable<string> lines = File.ReadLines(ofg.FileName, Encoding.Default);
            DateTime startTime=DateTime.Now;

            DataTable table = new DataTable();
            table.Columns.Add("haoduan");
            table.Columns.Add("diqu");
            table.Columns.Add("leixing");
            table.Columns.Add("quhao");
            for (int i = 1; i < lines.Count(); i++)
            {
                 string line = lines.ElementAt(i);
                 string[] str = line.Split(\t);
                 string haoduan = str[0];
                 string diqu = str[1];
                 diqu.Trim(");
                 string leixing = str[2];
                 leixing.Trim(");
                 string quhao = str[3];
                 quhao.Trim(");

                  DataRow row=table.NewRow();
                  row["Haoduan"] = haoduan;
                  row["Diqu"] = diqu;
                  row["Leixing"] = leixing;
                  row["Quhao"] = quhao;
                  table.Rows.Add(row);

            }

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
            {
                bulkCopy.DestinationTableName = "T_Tel";
                bulkCopy.ColumnMappings.Add("haoduan", "haoduan");
                bulkCopy.ColumnMappings.Add("diqu", "diqu");
                bulkCopy.ColumnMappings.Add("leixing", "leixing");
                bulkCopy.ColumnMappings.Add("quhao", "quhao");
                bulkCopy.WriteToServer(table);
            }
            TimeSpan ts = DateTime.Now - startTime;
            MessageBox.Show(ts.ToString());

//            using (SqlConnection conn = new SqlConnection(connStr))
//           { 
//                conn.Open();
               
//                for (int i = 1; i < lines.Count(); i++)
//                {
//                    string line = lines.ElementAt(i);
//                    string[] str = line.Split(‘\t‘);
//                    string haoduan = str[0];
//                    string diqu = str[1];
//                    diqu.Trim(‘"‘);
//                    string leixing = str[2];
//                    leixing.Trim(‘"‘);
//                    string quhao = str[3];
//                    quhao.Trim(‘"‘);
//                    using (SqlCommand cmd = conn.CreateCommand())
//                    {
//                        cmd.CommandText = @"insert into T_Tel(haoduan,diqu,leixing,quhao)
//                        values (@Haoduan,@Diqu,@Leixing,@Quhao)";
//                        cmd.Parameters.Add(new SqlParameter("@Haoduan", haoduan));
//                        cmd.Parameters.Add(new SqlParameter("@Diqu", diqu));
//                        cmd.Parameters.Add(new SqlParameter("@Leixing", leixing));
//                        cmd.Parameters.Add(new SqlParameter("@Quhao", quhao));
//                        cmd.ExecuteNonQuery();
//                    }
//                }   
//            }
           
        }
    }
}

 

自学.net(5)SqlBulkCopy批量数据插入

上一篇:Docker 部署Spring Boot 项目并连接mysql、redis容器(记录过程)


下一篇:Oracle X$ Tables介绍