用途说明：
公司要求做一个数据导入程序，要求将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