首页 > 代码库 > [已解决]C#批量高效率导入大数据到数据库[百万级以上]

[已解决]C#批量高效率导入大数据到数据库[百万级以上]

将几百万条数据导入到数据库中,怎么样高效率的导入?
下面我就介绍一个高效率的方法:
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