Problems grasping ado.net - coming from asp background

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

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 based on the UserName and RoleName 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 sqlserver.

Appreciate if someone could show me how this could be done. Thanks

If I understand you correctly, this issue does not differ from ASP to ASP.NET. You need to get UserName(RoleName) based on UserID(RoleID) and then insert the values into UsersInRoles table, right? If so, you can create a stored procedure like this:

CREATE PROCEDURE sp_InsUsersInRoles @.RoleID INT
AS
INSERT INTO UsersInRoles (UserName, RoleName)
SELECT UserName, RoleName
FROM Users join Roles ON Users.RoleID=Roles.RoleID
WHERERoles.RoleID=@.RoleID

Then you can easily call the stored procedure, just remember to set the SqlCommand.CommandType to StoredProcedure, and add parameter for it.

|||

Change:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserName, RoleName) VALUES (@.UserName, @.RoleName)", db);

to:

SqlCommand cmd = new SqlCommand("INSERT INTO UsersInRoles (UserID, RoleID)SELECT (SELECT UserIDFROM UsersWHERE Username=@.UserName),RoleIDFROM RolesWHERE RoleName=@.RoleName", db);

|||

Hi Motley worked a treat. However i also tried doing:

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 didn't work.

Any ideas what i did wrong. Cheers

|||

Is RoleName unique within Roles? If you are using the default asp.net membership database structure, then it is not. RoleName is only unique within an Application, and hence may appear in Roles multiple times for different applications. If you have multiple application support, then you will need to supply the application name or application id to your roleid lookup.

My code in this case will add the user to the RoleName of all applications in which the role appears. The same problem is true for UserName as well if you are using multiple applications, but my code will fail in that case as well.

Otherwise I see nothing wrong with your code.

|||

On the other hand, it could be transaction related. I don't normally use the transaction support in ASP.NET. I prefer to use SQL transactions for all my work since I tend to create large batches within my SQL, and wrap that rather than wrapping many SqlCommands. In any case, you might try adding the cmd2 and cmd3 to the transaction as well.

It would also help if you posted what error you are getting rather than just "It didn't work".

|||

Hi, i can't see how to track the error, i'm using visual studio web developer edition. I tried using the built in asp.net config tool to manage my users but when i put a break point in my code it does nothing. However to test if it was reaching this part of the code i did a dummy insert statement and it worked.

I also tried putting an exception on the catch part and doing console.writeline(ex.Message) but that didn't do anything either.

Appreciate your help once more. Thanks