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

用途说明：

公司要求做一个数据导入程序，要求将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 = ;

            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().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 + ;

                                }

                                sqlBC.BatchSize = ;

                                sqlBC.BulkCopyTimeout = ;

                                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

巴特西

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

最新文章

热门文章