将几百万条数据导入到数据库中,怎么样高效率的导入?
下面我就介绍一个高效率的方法:
1、将数据库文件(DB.csv)导入到DataTable中:
/// <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、将数据库保存到数据库:
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、在数据库创建表值参数类型:
create type dbo.BulkUdt(col1 bigint,col2 nvarchar(10));
4、开始导入数据:
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));
打个广告:需要iPhone 5s / 5c / 5 钢化玻璃膜、保护套/保护壳的朋友,上http://kener.taobao.com,联系客服给同行的程序员们一个优惠的价格哦!
转自:http://www.dengyukeji.com/thread-564-1-1.html