CODEWITHSUNDEEP
asp.netsqlsql-serverdatabase
developer82
developer82

Reputation: 13713

What is the most effective way to check if user exists in database?

I'm building a user registration for a .NET site that works with SQL Server.

I've written a Stored Procedure to insert a new user. Before I insert I want to make sure that the user isn't already registered.

The username is his email address and before inserting I'm doing the following check:

IF EXISTS (SELECT 1 FROM dbo.Members WHERE _Email = @Email)
    BEGIN
         SELECT @UsernameAlreadyInUse
         RETURN @UsernameAlreadyInUse
    END

is this a good way? if I have a lot of users wouldn't that make a full scan of my table and actually be slow? what is the best practice for that scenario?

Thanks

Upvotes: 0

Views: 662

Answers (2)

granadaCoder
granadaCoder

Reputation: 27842

Here is an alternative if you only want to hit the database one time.

You attempt the INSERT, and track the @@ROWCOUNT.

Your decision should be based on whether you think a "collision" will be commonplace, or a rarity.

I like the below, because I feel a collision will occur less often than not. And I get the "check" and the (probable?) INSERT to work.........with one db hit.

Having said that.......if you are using the MembershipProvider, I would strongly recommend to use the methods in the library to do what you need to do. And not to deal directly with the database. It is a MembershipProvider model, and exists so you can swap out the "concrete" implemenation (SqlMembershipProvider (url below)) with another one with minimal changes.

http://msdn.microsoft.com/en-us/library/system.web.security.sqlmembershipprovider%28v=vs.110%29.aspx

/* START TSQL */

if exists ( SELECT * FROM information_schema.tables WHERE table_schema = 'dbo' and table_name = 'UserInfo' )
      BEGIN
            print 'About to DROP TABLE [dbo].[UserInfo]'
            DROP TABLE [dbo].[UserInfo]
            print 'TABLE [dbo].[UserInfo] dropped'
            print ''
      END
GO



CREATE TABLE [dbo].[UserInfo] (
      UserInfoUUID [uniqueidentifier] not null default NEWSEQUENTIALID() ,
      EmailAddress varchar(64) not null
)    
GO


ALTER TABLE dbo.UserInfo ADD CONSTRAINT PK_UserInfo_UserInfoUUID
PRIMARY KEY CLUSTERED (UserInfoUUID)
GO


ALTER TABLE dbo.UserInfo ADD CONSTRAINT CK_UserInfo_UserInfoName_UNIQUE
UNIQUE (EmailAddress)
GO


SET NOCOUNT ON 


declare @MyRowCount bigint
declare @UsernameAlreadyInUse bit
declare @EmailAddress varchar(64)
select @EmailAddress = '[email protected]'


INSERT INTO [dbo].[UserInfo] ( EmailAddress ) 
Select @EmailAddress  where not exists (select null from dbo.UserInfo innerUI where innerUI.EmailAddress = @EmailAddress)
select @MyRowCount = @@ROWCOUNT

Select @UsernameAlreadyInUse = 0
if (@MyRowCount = 0)
BEGIN
    select @UsernameAlreadyInUse = 1
END

select * from [dbo].[UserInfo]

print '1st @UsernameAlreadyInUse'
print @UsernameAlreadyInUse
print ''


INSERT INTO [dbo].[UserInfo] ( EmailAddress ) 
Select @EmailAddress  where not exists (select null from dbo.UserInfo innerUI where innerUI.EmailAddress = @EmailAddress)
select @MyRowCount = @@ROWCOUNT

Select @UsernameAlreadyInUse = 0
if (@MyRowCount = 0)
BEGIN
    select @UsernameAlreadyInUse = 1
END

select * from [dbo].[UserInfo]

print '2nd @UsernameAlreadyInUse'
print @UsernameAlreadyInUse
print ''



SET NOCOUNT OFF

Upvotes: 1

Danish Junaid
Danish Junaid

Reputation: 1

IF EXISTS (SELECT Name FROM dbo.Members WHERE _Email = @Email)
    BEGIN
         RAISERROR('User Already Exist',16,1)         
    END

Upvotes: 0

Related Questions