C# Check if email already exists-CodePudding

How do I modify this registration code so it checks if email entered already exists in the database? I already have a query written for it, but I don't know how to implement it

[HttpPost("Register")]
        public async Task<ActionResult<User>> Register(UserDto request, Guid guid)
        {
            string query = @"
                           insert into dbo.Users(UserID,Name,Email,PasswordHash,PasswordSalt)
                           values (@UserID,@Name,@Email,@PasswordHash,@PasswordSalt)
                            ";
            string emailValidationQuery = @"SELECT * FROM dbo.Users WHERE Email = @Email";

            CreatePasswordHash(request.Password, out byte[] passwordHash, out byte[] passwordSalt);
            string psw = PasswordHash(request.Password);

            Guid guid1 = Guid.NewGuid();
            guid = guid1;

            user.UserID = guid;
            user.Username = request.Username;
            user.Email = request.Email;
            user.PasswordHash = Encoding.UTF8.GetBytes(psw);
            user.PasswordSalt = passwordSalt;

            DataTable table = new DataTable();
            string sqlDataSource = _configuration.GetConnectionString("ContactAppCon");
            SqlDataReader myReader;
            using (SqlConnection myCon = new SqlConnection(sqlDataSource))
            {
                myCon.Open();
                using (SqlCommand myCommand = new SqlCommand(query, myCon))
                {
                    myCommand.Parameters.AddWithValue("@UserID", Guid.NewGuid());
                    myCommand.Parameters.AddWithValue("@Name", request.Username);
                    myCommand.Parameters.AddWithValue("@Email", request.Email);
                    myCommand.Parameters.AddWithValue("@PasswordHash", psw);
                    myCommand.Parameters.AddWithValue("@PasswordSalt", passwordSalt);
                    myReader = myCommand.ExecuteReader();
                    table.Load(myReader);
                    myReader.Close();
                    myCon.Close();
                }
            }
            return Ok(user);
        }

CodePudding user response：

Try something like below (open and dispose the connections properly)

string emailValidationQuery = @"SELECT Count(*) FROM dbo.Users WHERE Email = @Email";
....

using SqlCommand command = new SqlCommand(emailValidationQuery, myCon);
int count = (Int32) command.ExecuteScalar();

if(count > 0)
    return new User() // or whatever you required

CodePudding user response：

Why not a single statement:

INSER INTO dbo.Users (UserID, Name, Email, ...)
  VALUES (@UserID, @Name, @Email, ...)
WHERE NOT EXISTS
(
  SELECT 0 
    FROM dbo.Users WITH (SERIALIZABLE, UPDLOCK)
    WHERE Email = @Email
);

If this affects 0 rows (you can check with @@ROWCOUNT), then the e-mail already existed (and maybe you should run an update instead in that case, but it's not clear from the question) or the insert failed for some other reason (which you can check with simple try/catch patterns).

And you can prevent race conditions and avoid costly exceptions by doing it a little differently:

BEGIN TRANSACTION;

IF NOT EXISTS 
(
  SELECT 0 FROM dbo.Users WITH (SERIALIZABLE, UPDLOCK)
  WHERE Email = @Email
)
BEGIN
  INSERT ...
END
ELSE
BEGIN
  -- UPDATE? RAISERROR? Again requirements aren't clear.
END

COMMIT TRANSACTION;

Don't go for simple or expensive when correct and more efficient are better.