Ensure unique username

Question

Asp.net membership 2.0.

I want to update the username in table aspnet_user, the best way is to update the table directly.

UPDATE aspnet_Users SET UserName = @UserName,LoweredUserName = LOWER( @UserName) 
WHERE UserName = @CurrentUname

How to ensure it is unique?

Thanks,

UPDATE: If not exist (select UserName from aspnet_Users) Begin UPDATE aspnet_Users SET UserName = @UserName,LoweredUserName = LOWER( @UserName) WHERE UserName = @CurrentUname End

HOW TO MODIFY THE CODE ABOVE?

Second Update:

If not exist (select UserName from aspnet_Users WHERE UserName = @CurrentUname)
Begin 
  UPDATE aspnet_Users SET UserName = @UserName,LoweredUserName = LOWER( @UserName)
WHERE UserName = @CurrentUname
End
SELECT 1;
else
select 0;

A ... ["unique constraint"](http://msdn.microsoft.com/en-us/library/ms191166%28v=sql.105%29.aspx)? — , Apr 17 '12 at 17:51
Is that really the best way to update it? Don't you just have to query the table to see if there already exists a user with that name, though - if you can write the query for the update, can't you write a SELECT query for that? — Rup, Apr 17 '12 at 17:52
If the new username is already existing, I want to prevent it. — , Apr 17 '12 at 17:53
@pst This is probably the ASP.NET SqlMembershipProvider's table - I'd be nervous about doing that because the default code might not handle the uniqueness exception gracefully — Rup, Apr 17 '12 at 17:54
You need `WHERE UserName = @UserName` in the `if not exist()` too. But you also need to return success / failure from this snippet if you're doing the check in SQL. — Rup, Apr 17 '12 at 17:59
There's a whole lot of context missing here. What happens if the user exists? Do you care about atomicity? Is an application doing this? If so, what makes you think side stepping the provider and running raw sql statements is the best way to get this done, given your fear of tampering with the table constraints? Something smells funny... — Pete M, Apr 17 '12 at 18:08
Why not just do it through .NET? You update SQL direclty and you risk breaking membership. — paparazzo, Apr 17 '12 at 18:23
I assume something cares about that scalar recordset returned. I would go with a return value or output variable. Nothing technically wrong with what you have there, it's just a best practices/style thing. It makes it obvious that the value kicked back is not a record set at all but rather a status. The rest of my questions still stand. Looks like it should do what you've asked so far. Is there something else you want from it? Still curious why you're not using the provider itself to do this. It would be trivial to synchronize a check and update in .NET. — Pete M, Apr 17 '12 at 18:25
Blam, please post your code. There is one at http://stackoverflow.com/questions/1001491/is-it-possible-to-change-the-username-with-the-membership-api. But it still needs sql. — , Apr 17 '12 at 18:27
The SQL isn't the hard part nor the focus of the questions you're being asked. You are talking about stepping outside of the provider entirely to do one specific action. Extend the provider. That's what it's there for. You're still left with the question of why you're doing wacky things in a stored procedure instead of keeping it in the provider with all the rest of the business logic. I tried dragging details out of you and you're resisting, so I'm confused as to what you want from us. — Pete M, Apr 17 '12 at 19:00
Then I apologize. I did not realize the .NET API did not support that. — paparazzo, Apr 17 '12 at 22:09

score 4 · Answer 1 · answered Apr 17 '12 at 17:53

4

Create a unique index on the username column.

Something like:

CREATE UNIQUE INDEX index_username
ON aspnet_Users (UserName)

answered Apr 17 '12 at 17:53

Icarus

63,293
14
100
115

score 3 · Answer 2 · answered Apr 17 '12 at 17:53

3

Place a unique constraint on the column in the database. Then either check for an existing username before attempting the update, or catch the exception and handle it accordingly.

answered Apr 17 '12 at 17:53

Pete M

2,008
11
17

If doing the pre-check, it is important to use the appropriate transaction. – Apr 17 '12 at 17:54
No, it is ASP.NET SqlMembershipProvider's table. I don't want do it in this way. – Apr 17 '12 at 17:57
Good point. There is no guarantee the desired name won't be taken by a second user in between checking for a pre-existing name and attempting the update for the first user. A table constraint will still force the issue and yell at you, but that's a lot messier than doing the whole thing as an atomic transaction. – Pete M Apr 17 '12 at 17:57
I don't understand why you don't want to add a constraint because it's a SqlMembershipProvider table. If you want to ABSOLUTELY GUARANTEE uniqueness, constraints are the only sane way to do it. There is always the trigger route. It makes me feel dirty just mentioning it... What is the whole story here? Why are you doing this? Maybe you should be looking at rolling your own provider? – Pete M Apr 17 '12 at 17:59

Pankaj · Accepted Answer · 2012-04-17T18:58:46.983

1

In addition to @Icarus and @Pete M Replies. These are already Good/Trusted suggestion.

Alternative is, Can you use TransactionScope ?

Pseudo Code

using (TransactionScope scope = new TransactionScope())
{
    UPDATE aspnet_Users 
    SET    UserName = @UserName,
           LoweredUserName = LOWER( @UserName) 
    WHERE  UserName = @CurrentUname

    if((select count(userName) 
        From   aspnet_Users
        Where  userName = @CurrentUname) = 1)
    Begin
         scope.Complete
    End
}

The above approach keeps the independent session of the request under Transaction and keep it isolated from other request made by other users.

edited Apr 17 '12 at 18:58

answered Apr 17 '12 at 18:25

Pankaj

9,749
32
139
283

It seems to be different and inspired. Could you please add the entire code? – Apr 17 '12 at 18:40
I have added Pseudo code. it's merely my Point of View. I hope you can convert it into actual code. – Pankaj Apr 17 '12 at 18:47
I guess that it should be scope.Complete() instead of Trans.Complete(); – Apr 17 '12 at 18:52
Please note that, This is the situation when you do not want to use the `Unique Constraint`. – Pankaj Apr 17 '12 at 18:53
@Love, major advantage of using this technique is a `TransactionScope` can span `multiple resources (i.e. multiple databases, or a database and an MSMQ server, etc).` – Pankaj Apr 17 '12 at 19:00

Ensure unique username

3 Answers3