1.封装批量插入数据SQL Server数据的方法
public static void DataTableToSQLServer(DataTable dt, string connectString, string tableName)
{
string connectionString = connectString;
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
{
try
{
bulkCopy.DestinationTableName = tableName;//要插入的表的表名
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.ColumnMappings.Add("ID", "ID");//映射字段名 DataTable列名 ,数据库 对应的列名
bulkCopy.ColumnMappings.Add("primaryDataID", "primaryDataID");
bulkCopy.ColumnMappings.Add("SysKeywordID", "SysKeywordID");
bulkCopy.ColumnMappings.Add("value", "value");
bulkCopy.ColumnMappings.Add("UserID", "UserID");
bulkCopy.ColumnMappings.Add("IsTitle", "IsTitle");
bulkCopy.ColumnMappings.Add("IsContent", "IsContent");
bulkCopy.ColumnMappings.Add("ContentCount", "ContentCount");
bulkCopy.ColumnMappings.Add("IsMatching", "IsMatching");
bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate");
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
}
}
}
}
2.调用DataTableToSQLServer方法
public ApiResult test()
{
try
{
//清空表格数据
List<primaryDataKeyword> pdk = dbcontext.primaryDataKeywords.ToList();
dbcontext.primaryDataKeywords.RemoveRange(pdk);
var value = from dc in dbcontext.DBColumns.Where(t => t.En_Name == "keyword" && t.ColumnType == ColumnTypeEnums.TagsInput)
join cd in dbcontext.CustomDataList.Where(t => !string.IsNullOrEmpty(t.value)) on dc.ID equals cd.DBColumnID
join pd in dbcontext.PrimaryDataList on cd.PrimaryDataID equals pd.ID
where (pd.SystemType == SystemTypeEnums.People && dc.SystemDataType == SystemDataTypeEnums.People) || (pd.SystemType != SystemTypeEnums.People && dc.SystemDataType != SystemDataTypeEnums.People)
select new keywordData()
{
primaryDataID = cd.PrimaryDataID,
UserID = dc.UserID,
value = cd.value,
dbid = dc.DBID
};
if (value == null || value.Count() == 0)
{
return ApiResult.NewErrorJson("无关键词");
}
DataTable dtUserAdd = new DataTable();
dtUserAdd.Columns.Add(new DataColumn("ID", typeof(string)));
dtUserAdd.Columns.Add(new DataColumn("primaryDataID", typeof(string)));
dtUserAdd.Columns.Add(new DataColumn("SysKeywordID", typeof(string)));
dtUserAdd.Columns.Add(new DataColumn("value", typeof(string)));
dtUserAdd.Columns.Add(new DataColumn("UserID", typeof(string)));
dtUserAdd.Columns.Add(new DataColumn("IsTitle", typeof(bool)));
dtUserAdd.Columns.Add(new DataColumn("IsContent", typeof(bool)));
dtUserAdd.Columns.Add(new DataColumn("ContentCount", typeof(int)));
dtUserAdd.Columns.Add(new DataColumn("IsMatching", typeof(bool)));
dtUserAdd.Columns.Add(new DataColumn("CreateDate", typeof(DateTime)));
DataRow drTemp = null;
foreach (keywordData val in value.ToList())
{
List<string> condition = val.value.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).ToList();
foreach (string c in condition)
{
SysKeyword sk = dbcontext.SysKeywords.FirstOrDefault(t => t.DBID == val.dbid && t.UserID == val.UserID && t.Title == c);
if (sk == null)
{
continue;
}
drTemp = dtUserAdd.NewRow();
drTemp["ID"] = Guid.NewGuid().ToString();
drTemp["primaryDataID"] = val.primaryDataID;
drTemp["SysKeywordID"] = sk.ID;
drTemp["value"] = c;
drTemp["UserID"] = val.UserID;
drTemp["IsTitle"] = false;
drTemp["IsContent"] = false;
drTemp["ContentCount"] = 0;
drTemp["IsMatching"] = false;
drTemp["CreateDate"] = DateTime.Now;
dtUserAdd.Rows.Add(drTemp);
}
}
DataTableToSQLServer(dtUserAdd, connectString, "EKU_Test");
return ApiResult.NewSuccessJson("关键词提取成功");
}
catch (Exception ex)
{
return ApiResult.NewErrorJson(ex.Message.ToString());
}
}