Excel 大数据导入方法之一

将几百万条数据导入到数据库中，怎么样高效率的导入？
下面我就介绍一个高效率的方法：
1、将数据库文件(DB.csv)导入到DataTable中：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

/// <summary>         /// 将CSV文件的数据读取到DataTable中         /// </summary>         /// <param name="fileName">CSV文件路径</param>         /// <returns>返回读取了CSV数据的DataTable</returns>         public static DataTable OpenCSV(string filePath)         {             Encoding encoding = Encoding.GetEncoding("utf-8"); //Encoding.ASCII;//             DataTable dt = new DataTable();             FileStream fs = new FileStream(filePath, System.IO.FileMode.Open, System.IO.FileAccess.Read);               //StreamReader sr = new StreamReader(fs, Encoding.UTF8);             StreamReader sr = new StreamReader(fs, encoding);             //string fileContent = sr.ReadToEnd();             //encoding = sr.CurrentEncoding;             //记录每次读取的一行记录             string strLine = "";             //记录每行记录中的各字段内容             string[] aryLine = null;             string[] tableHead = null;             //标示列数             int columnCount = 0;             //标示是否是读取的第一行             bool IsFirst = true;             //逐行读取CSV中的数据             while ((strLine = sr.ReadLine()) != null)             {                 //strLine = Common.ConvertStringUTF8(strLine, encoding);                 //strLine = Common.ConvertStringUTF8(strLine);                   if (IsFirst == true)                 {                     tableHead = strLine.Split(',');                     IsFirst = false;                     columnCount = tableHead.Length;                     //创建列                     for (int i = 0; i < columnCount; i++)                     {                         DataColumn dc = new DataColumn(tableHead[i]);                         dt.Columns.Add(dc);                     }                 }                 else                 {                     if (!String.IsNullOrEmpty(strLine))                     {                         aryLine = strLine.Split(',');                         DataRow dr = dt.NewRow();                         for (int j = 0; j < columnCount; j++)                         {                             dr[j] = aryLine[j];                         }                         dt.Rows.Add(dr);                     }                 }             }             if (aryLine != null && aryLine.Length > 0)             {                 dt.DefaultView.Sort = tableHead[0] + " " + "asc";             }               sr.Close();             fs.Close();             return dt;         }     }

2、将数据库保存到数据库：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

public static void TableValuedToDB(DataTable dt)         {             SqlConnection sqlConn = new SqlConnection(               ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);             const string TSqlStatement =              "insert into table (col1,col2)" +              " SELECT nc.col1,nc.col2" +              " FROM @NewBulkTestTvp AS nc";             SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);             SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);             catParam.SqlDbType = SqlDbType.Structured;             //表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。             catParam.TypeName = "dbo.BulkUdt";             try             {                 sqlConn.Open();                 if (dt != null && dt.Rows.Count != 0)                 {                     cmd.ExecuteNonQuery();                 }             }             catch (Exception ex)             {                 throw ex;             }             finally             {                 sqlConn.Close();             }         }

3、在数据库创建表值参数类型:

?

1	create type dbo.BulkUdt(col1 bigint,col2 nvarchar(10));

4、开始导入数据：

?

1 2 3 4 5 6 7

Stopwatch sw = new Stopwatch(); string filePath = @"C:\DB.csv"; DataTable dt = CSVFileHelper.OpenCSV(filePath); sw.Start(); TableValuedToDB(dt); sw.Stop(); Trace.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));