A Few Questions On Locking In Sql Server 2005

Hi pardon my ignorance but I wonder if someone could answer a few questions for me.

I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.

A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.

So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.

Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.

The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.

How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.

I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.

I suspect the problem is the way my program is going about the checking.

Should I use an SQL insert statement like??

If exists(SELECT username from users where username=@username)
BEGIN
RAISEERROR("Username already exists")
END
ELSE
BEGIN
INSERT etc

If so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?

Also some more general questions.

1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.

Dim lclRowRet As SqlDataReader

lclRowRet = LoadUserCommand.ExecuteReader(CommandBehavior.SingleRow)

lclRowRet.Read()

2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()

As a rule I close all my connections as soon as I'm done with them.

Many thanks for your help in advance.

nik