客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
用途说明： 公司要求做一个数据导入程序，要求将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
|
请发表评论