/// <summary>
/// 一、构建模拟数据存放于DataTable
/// </summary>
/// <returns>DataTable</returns>
public DataTable GetData()
{
DataTable dt = new DataTable(); dt.Columns.Add("MemberID", typeof(int));//为新的Datatable添加一个新的列名
dt.Columns.Add("Body", typeof(string));//为新的Datatable添加一个新的列名
dt.Columns.Add("IsSecret", typeof(int));//为新的Datatable添加一个新的列名
dt.Columns.Add("AdminReply", typeof(string));//为新的Datatable添加一个新的列名
dt.Columns.Add("AdminReplyTime", typeof(DateTime));
dt.Columns.Add("CreateTime", typeof(DateTime)); for (int i = ; i < ; i++) //开始循环赋值
{
DataRow row = dt.NewRow(); //创建一个行
row["MemberID"] = ; //从总的Datatable中读取行数据赋值给新的Datatable
row["Body"] = "留言了:" + (i + ).ToString();
row["IsSecret"] = ;
row["AdminReply"] = "回复了:" + (i + ).ToString();
row["AdminReplyTime"] = DateTime.Now;
row["CreateTime"] = DateTime.Now.AddMonths(-);
dt.Rows.Add(row);//添加次行
}
return dt;
}
/// <summary>
/// 二、实例的数据源中的列与该实例的目标表中的列之间的映射
/// </summary>
/// <returns></returns>
public SqlBulkCopyColumnMapping[] GetMapping()
{
SqlBulkCopyColumnMapping[] mapping = new SqlBulkCopyColumnMapping[];
mapping[] = new SqlBulkCopyColumnMapping("MemberID", "MemberID");
mapping[] = new SqlBulkCopyColumnMapping("Body", "Body");
mapping[] = new SqlBulkCopyColumnMapping("IsSecret", "IsSecret");
mapping[] = new SqlBulkCopyColumnMapping("AdminReply", "AdminReply");
mapping[] = new SqlBulkCopyColumnMapping("AdminReplyTime", "AdminReplyTime");
mapping[] = new SqlBulkCopyColumnMapping("CreateTime", "CreateTime");
return mapping;
} /// <summary>
/// DataTable批量添加(有事务)
/// </summary>
/// <param name="Table">数据源DataTable</param>
/// <param name="DestinationTableName">目标表即需要插入数据的数据表名称如"Message"</param>
public bool MySqlBulkCopy(DataTable Table, string DestinationTableName)
{
bool Bool = true;
using (SqlConnection con = new SqlConnection(DbHelperSQL.connectionString))
{
con.Open();
using (SqlTransaction Tran = con.BeginTransaction())//应用事物
{
using (SqlBulkCopy Copy = new SqlBulkCopy(con, SqlBulkCopyOptions.KeepIdentity, Tran))
{
Copy.DestinationTableName = DestinationTableName;//指定目标表
SqlBulkCopyColumnMapping[] Mapping = GetMapping();//获取映射关系
if (Mapping != null)
{
//如果有数据
foreach (SqlBulkCopyColumnMapping Map in Mapping)
{
Copy.ColumnMappings.Add(Map);
}
}
try
{
Copy.WriteToServer(Table);//批量添加
Tran.Commit();//提交事务
}
catch
{
Tran.Rollback();//回滚事务
Bool = false;
}
}
}
}
return Bool;
} public ActionResult InsertMoreData()
{
MySqlBulkCopy(GetData(), "Message");
return RedirectToAction("Index");
}