权限管理之添加用户同时设置角色
前情提要：
我们在做权限管理的时候，会用到为用户分配角色。有些做法是，添加完用户再设置角色，这种就没啥了。还有的时候，我们是在添加用户的时候，就为其分配角色。现在就牵扯到一个问题，数据库中的User表的ID是UerRole表的外键，当insert的时候问题就来了。①、可以先插入用户，获得刚插入的UserID，再插入角色，这种方法觉得比较麻烦，而且问题是如果插入用户后，插入角色时失败，用户体验会不好(当然看要求了)。个人比较喜欢下面这种方法：
使用事务的方式，这样在2个表插入的时候，有一个失败就会回滚，整个都失败。但是，这时刚插入的UserID怎么获得呢?我们知道在插入数据的时候，可以使用@@IDENTITY这个全局变量得到最新插入的主键值。因此，再插入角色时，直接在sql语句中为UserID赋值@@IDENTITY就可以了。
string sql="insert into UserRole(UserId,RoleId.....) values(@@IDENTITY,@roleid.....)"
但是设置多个角色时，问题又来了因为@@IDENTITY获得的是最新的插入数据的主键。解决办法是：设置变量存放@@IDENTITY(set @ReturnValue=@@IDENTITY)。
上代码：使用sql参数化，用@ReturnValue来接收@@IDENTITY，最重要的是要设置@ReturnValue参数为Output。角色表中，@UserId参数设置为ParameterDirection.InputOutput。
public int AddSetRole(User user, List<UserRole> list) { //用户表 StringBuilder userSql = new StringBuilder(); userSql.Append("insert into Common_Auth_User("); userSql.Append("LoginName,LoginPwd,FullName,Email,Phone,Enabled,PwdErrorCount,LoginCount,RegisterTime,LastLoginTime,CreateId,CreateBy,CreateTime,ModifyId,ModifyBy,ModifyTime,IsDeleted)"); userSql.Append(" values ("); userSql.Append("@LoginName,@LoginPwd,@FullName,@Email,@Phone,@Enabled,@PwdErrorCount,@LoginCount,@RegisterTime,@LastLoginTime,@CreateId,@CreateBy,@CreateTime,@ModifyId,@ModifyBy,@ModifyTime,@IsDeleted)"); userSql.Append("; set @ReturnValue=@@IDENTITY "); SqlParameter[] parameters = { new SqlParameter("@LoginName", SqlDbType.NVarChar,50), new SqlParameter("@LoginPwd", SqlDbType.NVarChar,50), new SqlParameter("@FullName", SqlDbType.NVarChar,50), new SqlParameter("@Email", SqlDbType.NVarChar,100), new SqlParameter("@Phone", SqlDbType.NVarChar,20), new SqlParameter("@Enabled", SqlDbType.Bit,1), new SqlParameter("@PwdErrorCount", SqlDbType.Int,4), new SqlParameter("@LoginCount", SqlDbType.Int,4), new SqlParameter("@RegisterTime", SqlDbType.DateTime), new SqlParameter("@LastLoginTime", SqlDbType.DateTime), new SqlParameter("@CreateId", SqlDbType.Int,4), new SqlParameter("@CreateBy", SqlDbType.NVarChar,50), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@ModifyId", SqlDbType.Int,4), new SqlParameter("@ModifyBy", SqlDbType.NVarChar,50), new SqlParameter("@ModifyTime", SqlDbType.DateTime), new SqlParameter("@IsDeleted", SqlDbType.Bit,1), new SqlParameter("@ReturnValue",SqlDbType.Int) }; parameters[0].Value = user.LoginName; parameters[1].Value = user.LoginPwd; parameters[2].Value = user.FullName; parameters[3].Value = user.Email; parameters[4].Value = user.Phone; parameters[5].Value = user.Enabled; parameters[6].Value = user.PwdErrorCount; parameters[7].Value = user.LoginCount; parameters[8].Value = user.RegisterTime; parameters[9].Value = user.LastLoginTime; parameters[10].Value = user.CreateId; parameters[11].Value = user.CreateBy; parameters[12].Value = user.CreateTime; parameters[13].Value = user.ModifyId; parameters[14].Value = user.ModifyBy; parameters[15].Value = user.ModifyTime; parameters[16].Value = user.IsDeleted; parameters[17].Direction = ParameterDirection.Output; List<CommandInfo> lists = new List<CommandInfo>(); CommandInfo commandInfo = new CommandInfo(userSql.ToString(), parameters); lists.Add(commandInfo); //用户角色表 添加 int count = list.Count; for (int i = 0; i < count; i++) { StringBuilder userRoleSql = new StringBuilder(); userRoleSql.Append("insert into Common_Auth_UserRole("); userRoleSql.Append("UserId,RoleId,CreateId,CreateBy,CreateTime,ModifyId,ModifyBy,ModifyTime,IsDeleted)"); userRoleSql.Append(" values ("); userRoleSql.Append("@UserId,@RoleId,@CreateId,@CreateBy,@CreateTime,@ModifyId,@ModifyBy,@ModifyTime,@IsDeleted)"); SqlParameter[] userRoleParameters = { new SqlParameter("@UserId", SqlDbType.Int), new SqlParameter("@RoleId", SqlDbType.Int,4), new SqlParameter("@CreateId", SqlDbType.Int,4), new SqlParameter("@CreateBy", SqlDbType.NVarChar,50), new SqlParameter("@CreateTime", SqlDbType.DateTime), new SqlParameter("@ModifyId", SqlDbType.Int,4), new SqlParameter("@ModifyBy", SqlDbType.NVarChar,50), new SqlParameter("@ModifyTime", SqlDbType.DateTime), new SqlParameter("@IsDeleted", SqlDbType.Bit,1) }; userRoleParameters[0].Direction=ParameterDirection.InputOutput; userRoleParameters[1].Value = list[i].RoleId; userRoleParameters[2].Value = list[i].CreateId; userRoleParameters[3].Value = list[i].CreateBy; userRoleParameters[4].Value = list[i].CreateTime; userRoleParameters[5].Value = list[i].ModifyId; userRoleParameters[6].Value = list[i].ModifyBy; userRoleParameters[7].Value = list[i].ModifyTime; userRoleParameters[8].Value = list[i].IsDeleted; commandInfo=new CommandInfo(userRoleSql.ToString(),userRoleParameters); lists.Add(commandInfo); } DbHelperSQL.ExecuteSqlTranWithIndentity(lists); return (int)parameters[17].Value; }
SqlHelper对应代码：对应sql代码，如果参数为Output，使用变量indentity存储下来，参数为InputOutput时就把先前得到的值赋值。注意这时，ExecuteSqlTranWithIndentity方法的参数为List类型，不能使用HashTable类型(因为无序)。
public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { SqlCommand cmd = new SqlCommand(); try { int indentity = 0; //循环 foreach (CommandInfo myDE in SQLStringList) { string cmdText = myDE.CommandText; SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters; foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.InputOutput) { q.Value = indentity; } } PrepareCommand(cmd, conn, trans, cmdText, cmdParms); int val = cmd.ExecuteNonQuery(); foreach (SqlParameter q in cmdParms) { if (q.Direction == ParameterDirection.Output) { indentity = Convert.ToInt32(q.Value); } } cmd.Parameters.Clear(); } trans.Commit(); } catch { trans.Rollback(); throw; } } } }
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (SqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } }
