Problems with role provider

[ralphiooo]

Hi, i've downloaded a membership and roles provider but i am very new to ado.net. I have come from asp background. Here's my code:

public override void AddUsersToRoles(string[] usernames, string[] rolenames) {
    // Validate arguments
    foreach (string rolename in rolenames) if (!this.RoleExists(rolename)) throw new ProviderException("Role name not found");
    foreach (string username in usernames) {
        if (username.IndexOf(',') > 0) throw new ArgumentException("User names cannot contain commas.");
        foreach (string rolename in rolenames) {
            if (IsUserInRole(username, rolename)) throw new ProviderException("User is already in role.");
        }
    }

    SqlConnection db = this.OpenDatabase();
    SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@UserName, @RoleName)", db);
    cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 100);
    cmd.Parameters.Add("@RoleName", SqlDbType.VarChar, 100);
    SqlTransaction tran = null;

    try {
        tran = db.BeginTransaction();
        cmd.Transaction = tran;
        foreach (string username in usernames) {
            foreach (string rolename in rolenames) {
                cmd.Parameters["@UserName"].Value = username;
                cmd.Parameters["@RoleName"].Value = rolename;
                cmd.ExecuteNonQuery();
            }
        }
        tran.Commit();
    }
    catch {
        tran.Rollback();
        throw;
    }
    finally {
        db.Close();
    }
}

private SqlConnection OpenDatabase() {
    SqlConnection DB = new SqlConnection(this.connectionString);
    DB.Open();
    return DB;
}

The problem i have is that the table structure they provide is to have:

UsersInRoles
- UserName (foreign key to users table)
- RoleName (foreign key to roles table)

but the table structure i have is:

UsersInRoles
- UserID (foreign key to users table)
- RoleID (foreign key to roles table)

So what i need to do is lookup the UserID and RoleID from the appropriate tables before doing the insert. However i am not familiar with the new syntax. I'm sure i could bodge something together but i assume this new syntax is something to do with running all the insert statements in one (transaction) so it doesn't have to keep taking round trips back to sql server.

Appreciate if someone could help. Thanks

 

[ralphiooo]

Hi i come up with:

SqlConnection db = this.OpenDatabase();
SqlCommand cmd = new SqlCommand("INSERT INTO UserRoles (UserName, RoleName) VALUES (@UserId, @RoleId)", db);
cmd.Parameters.Add("@UserId", SqlDbType.Int);
cmd.Parameters.Add("@RoleId", SqlDbType.Int);
SqlTransaction tran = null;

try {
    tran = db.BeginTransaction();
    cmd.Transaction = tran;
    foreach (string userName in userNames) {
        SqlCommand cmd2 = new SqlCommand("SELECT UserId FROM Users WHERE UserName = @UserName", db);
        cmd2.Parameters.Add("@UserName", SqlDbType.VarChar, 100).Value = userName;
        int userId = (int)cmd2.ExecuteScalar();

        foreach (string roleName in roleNames) {
            SqlCommand cmd3 = new SqlCommand("SELECT RoleId FROM Roles WHERE RoleName = @RoleName", db);
            cmd3.Parameters.Add("@RoleName", SqlDbType.VarChar, 100).Value = roleName;
            int roleId = (int)cmd3.ExecuteScalar();

            cmd.Parameters["@UserId"].Value = userId;
            cmd.Parameters["@RoleId"].Value = roleId;
            cmd.ExecuteNonQuery();
        }
    }
    tran.Commit();
}
catch {
    tran.Rollback();
    throw;
}
finally {
    db.Close();
}

but it doesn't work. Any ideas what i am doing wrong. Cheers