批量数据处理有事务处理的方式。而本文使用的是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); } }测试通过
