权限管理之添加用户同时设置角色

前情提要：

　　我们在做权限管理的时候，会用到为用户分配角色。有些做法是，添加完用户再设置角色，这种就没啥了。还有的时候，我们是在添加用户的时候，就为其分配角色。现在就牵扯到一个问题，数据库中的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);
                }
            }
        }