• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

最短时间(几秒内)利用C#往SQLserver数据库一次性插入10万条数据 ...

原作者: [db:作者] 来自: [db:来源] 收藏 邀请

 用途说明:

公司要求做一个数据导入程序,要求将Excel数据,大批量的导入到数据库中,尽量少的访问数据库,高性能的对数据库进行存储。于是在网上进行查找,发现了一个比较好的解决方案,就是采用SqlBulkCopy来处理存储数据。SqlBulkCopy存储大批量的数据非常的高效,就像这个方法的名字一样,可以将内存中的数据表直接的一次性的存储到数据库中,而不需要一次一次的向数据库Insert数据。初次实验,百万级别的数据表,也只需几秒时间内就可以完全的存入数据库中,其速度,比传统的Insert方法不止快百倍千倍

      #region 批量添加实名认证信息(万级)
        /// <summary>
        /// 批量添加实名认证信息(万级)
        /// </summary>
        /// <returns></returns>
        [Route("Test/BatchAddNciicUserInfo")]
        [HttpGet]
        public async Task<string> BatchAddNciicUserInfo()
        {
            DateTime regtime = DateTime.Parse("2019-06-24");
            int i = 0;
            using (var db = new GPAppEntities())
            {
                using (SqlConnection conn = db.Database.Connection as SqlConnection)
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    using (SqlTransaction tran = conn.BeginTransaction())
                    {
                        db.Database.UseTransaction(tran);
                        try
                        {
                            var userlist = db.AppUser.Where(a => a.RegisterTime > regtime).OrderBy(a=>a.RegisterTime).Select(s => new { s.ID,s.MobilePhone}).Take(51154).ToList();
                            StreamReader sr = new StreamReader(@"D:\Deploy\GPApp.Api\Content\Test\APP用户数据.txt", Encoding.Default);
                            String line;
                            List<IDCardNo> list = new List<IDCardNo>();
                            while ((line = sr.ReadLine()) != null)
                            {
                                var model = new IDCardNo();
                                model.Number = line;
                                list.Add(model);
                            }

                            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                            {

                                DataTable dtSource = new DataTable();
                                dtSource.Columns.Add("ID", typeof(Guid));
                                dtSource.Columns.Add("UserId", typeof(Guid));
                                dtSource.Columns.Add("RealName", typeof(string));
                                dtSource.Columns.Add("Gender", typeof(string));
                                dtSource.Columns.Add("IDCardNo", typeof(string));
                                dtSource.Columns.Add("IDCardNoEncrypt", typeof(string));
                                dtSource.Columns.Add("IDCardAgency", typeof(string));
                                dtSource.Columns.Add("IDCardStartDate", typeof(DateTime));
                                dtSource.Columns.Add("IDCardEndDate", typeof(DateTime));
                                dtSource.Columns.Add("Portrait", typeof(string));
                                dtSource.Columns.Add("FIDCard", typeof(string));
                                dtSource.Columns.Add("BIDCard", typeof(string));
                                dtSource.Columns.Add("Status", typeof(string));
                                dtSource.Columns.Add("AppSystem", typeof(string));
                                dtSource.Columns.Add("AppDevice", typeof(string));
                                dtSource.Columns.Add("AppIP", typeof(string));
                                dtSource.Columns.Add("CreateDate", typeof(DateTime));
                                dtSource.Columns.Add("ModifyDate", typeof(DateTime));
                                dtSource.Columns.Add("Birthday", typeof(DateTime));
                                foreach (var item in userlist)
                                {
                                    DataRow newdr = dtSource.NewRow();
                                    newdr["ID"] = Guid.NewGuid();
                                    newdr["UserId"] = item.ID;
                                    newdr["RealName"] = "叶长种";
                                    newdr["Gender"] = "";
                                    newdr["IDCardNo"] = list[i].Number;
                                    newdr["IDCardNoEncrypt"] = SHA256Help.sha256(list[i].Number).ToLower();
                                    newdr["IDCardAgency"] = "上海市公安局黄浦分局";
                                    newdr["IDCardStartDate"] = DateTime.Parse("2010-01-01 00:00:00");
                                    newdr["IDCardEndDate"] = DateTime.Parse("2020-01-01 00:00:00");
                                    newdr["Portrait"] = "";
                                    newdr["FIDCard"] = "";
                                    newdr["BIDCard"] = "";
                                    newdr["Status"] = "一致";
                                    newdr["AppSystem"] = "";
                                    newdr["AppDevice"] ="";
                                    newdr["AppIP"] = "";
                                    newdr["CreateDate"] = DateTime.Now;
                                    newdr["ModifyDate"] = DateTime.Now;
                                    newdr["Birthday"] = DateTime.Parse("1989-01-28 00:00:00");
                                    dtSource.Rows.Add(newdr);
                                    i = i + 1;
                                }

                                sqlBC.BatchSize = 10000;
                                sqlBC.BulkCopyTimeout = 60;
                                sqlBC.DestinationTableName = string.Format("dbo.NciicUserInfo");
                                sqlBC.ColumnMappings.Add("ID", "ID");
                                sqlBC.ColumnMappings.Add("UserId", "UserId");
                                sqlBC.ColumnMappings.Add("RealName", "RealName");
                                sqlBC.ColumnMappings.Add("Gender", "Gender");
                                sqlBC.ColumnMappings.Add("IDCardNo", "IDCardNo");
                                sqlBC.ColumnMappings.Add("IDCardNoEncrypt", "IDCardNoEncrypt");
                                sqlBC.ColumnMappings.Add("IDCardAgency", "IDCardAgency");
                                sqlBC.ColumnMappings.Add("IDCardStartDate", "IDCardStartDate");
                                sqlBC.ColumnMappings.Add("IDCardEndDate", "IDCardEndDate");
                                sqlBC.ColumnMappings.Add("Portrait", "Portrait");
                                sqlBC.ColumnMappings.Add("FIDCard", "FIDCard");
                                sqlBC.ColumnMappings.Add("BIDCard", "BIDCard");
                                sqlBC.ColumnMappings.Add("Status", "Status");
                                sqlBC.ColumnMappings.Add("AppSystem", "AppSystem");
                                sqlBC.ColumnMappings.Add("AppDevice", "AppDevice");
                                sqlBC.ColumnMappings.Add("AppIP", "AppIP");
                                sqlBC.ColumnMappings.Add("CreateDate", "CreateDate");
                                sqlBC.ColumnMappings.Add("ModifyDate", "ModifyDate");
                                sqlBC.ColumnMappings.Add("Birthday", "Birthday");
                                sqlBC.WriteToServer(dtSource);
                            }
                            tran.Commit();
                        }
                        catch (Exception ex)
                        {
                            tran.Rollback();
                            throw ex;
                        }
                    }

                }
            }
            return "添加成功";
        }
        public class IDCardNo
        {
            public string Number { get; set; }
        }
        #endregion

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
热门推荐
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap