批量数据处理有事务处理的方式。而本文使用的是DataTable数据集方式来更新。本文实现的是将选中的用户功能listNum[]插入到用户名为userId的userFunction表。
1 数据库userFunction表:
字段名 变量类型 字长
UserId varchar 15
FunctionId int 4
sn int 4
其中sn是主键 自增
2 相关代码:
/**//// <summary>
/// 批量插入数据库
/// </summary>
/// <param name="userId"></param>
/// <param name="listNum"></param>
private void InsertUserRights(String userId, String[] listNum)
...{
DeleteUserRights(userId);
// 新建一个DataTable的实例dt
DataTable dt = new DataTable();
// 声明一个DataRow实例dr
DataRow dr;
// 说明DataTable表里的包含哪些字段
dt.Columns.Add(new DataColumn("UserId", typeof(string)));
dt.Columns.Add(new DataColumn("FunctionId", typeof(Int32)));
foreach (string listValue in listNum)
...{
if (listValue.Length != 0)
...{
//i = Convert.ToInt16(listValue);
// 用DataTable的NewRow方法新建一个DataRow对象的实例dr
dr = dt.NewRow();
dr[0] = userId;
dr[1] = listValue;
dt.Rows.Add(dr);
}
}
SqlConnection conn = DbConn.GetConn();
SqlCommand cmd = new SqlCommand("Insert Into userFunction(Userid,FunctionId) values(@UserId,@FunctionId)");
SqlDataAdapter adapt = new SqlDataAdapter("select * from userFunction where 1=0", conn);
SqlCommandBuilder sqlCmdBud = new SqlCommandBuilder(adapt);
SqlParameter UserIdParam = new SqlParameter("@UserId",SqlDbType.VarChar,15);
SqlParameter FunctionIdParam = new SqlParameter("@FunctionId",SqlDbType.Int,4);
adapt.Fill(dt);
adapt.SelectCommand.Parameters.Add(UserIdParam);
adapt.SelectCommand.Parameters.Add(FunctionIdParam);
adapt.SelectCommand.Parameters["@UserId"].SourceColumn = "UserId";
adapt.SelectCommand.Parameters["@FunctionId"].SourceColumn = "FunctionId";
adapt.UpdateCommand = sqlCmdBud.GetUpdateCommand();
if (dt != null)
...{
adapt.Update(dt);
}
}
/// 批量插入数据库
/// </summary>
/// <param name="userId"></param>
/// <param name="listNum"></param>
private void InsertUserRights(String userId, String[] listNum)
...{
DeleteUserRights(userId);
// 新建一个DataTable的实例dt
DataTable dt = new DataTable();
// 声明一个DataRow实例dr
DataRow dr;
// 说明DataTable表里的包含哪些字段
dt.Columns.Add(new DataColumn("UserId", typeof(string)));
dt.Columns.Add(new DataColumn("FunctionId", typeof(Int32)));
foreach (string listValue in listNum)
...{
if (listValue.Length != 0)
...{
//i = Convert.ToInt16(listValue);
// 用DataTable的NewRow方法新建一个DataRow对象的实例dr
dr = dt.NewRow();
dr[0] = userId;
dr[1] = listValue;
dt.Rows.Add(dr);
}
}
SqlConnection conn = DbConn.GetConn();
SqlCommand cmd = new SqlCommand("Insert Into userFunction(Userid,FunctionId) values(@UserId,@FunctionId)");
SqlDataAdapter adapt = new SqlDataAdapter("select * from userFunction where 1=0", conn);
SqlCommandBuilder sqlCmdBud = new SqlCommandBuilder(adapt);
SqlParameter UserIdParam = new SqlParameter("@UserId",SqlDbType.VarChar,15);
SqlParameter FunctionIdParam = new SqlParameter("@FunctionId",SqlDbType.Int,4);
adapt.Fill(dt);
adapt.SelectCommand.Parameters.Add(UserIdParam);
adapt.SelectCommand.Parameters.Add(FunctionIdParam);
adapt.SelectCommand.Parameters["@UserId"].SourceColumn = "UserId";
adapt.SelectCommand.Parameters["@FunctionId"].SourceColumn = "FunctionId";
adapt.UpdateCommand = sqlCmdBud.GetUpdateCommand();
if (dt != null)
...{
adapt.Update(dt);
}
}
测试通过