C# check username if already exists from database

Article
2014-12-11

Question

_{Thursday, December 11, 2014 10:33 AM}

Hi, I want to validate the username from the database. Followin are my codes, but whereever i call UsernameCheck() it gives the error message Already Exists but inserts the username which are already in database.

Code for Procedure sp_ManageUser

    ALTER PROCEDURE [dbo].[sp_ManageUser]
   -- Add the parameters for the stored procedure here
   @StaffID int,
   @Name nvarchar(100),
   @Address nvarchar(500),
   @Phone nvarchar(100),
   @Email nvarchar(100),
   @JoinedDate date,
   @Username nvarchar(50),
   @Password nvarchar(max),
   @CreatedDate date,
   @Mode varchar(1)
        AS
    BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT OFF;

        -- Insert statements for procedure here
    if(@Mode='I')
    insert into tbl_Staff (Name,Address,Phone,Email,JoinedDate,Username,Password,CreatedDate) values(@Name,@Address,@Phone,@Email,@JoinedDate,@Username,@Password,@CreatedDate)
    if(@Mode='U')
    Update tbl_Staff set Name=@Name,Address=@Address,Phone=@Phone,Email=@Email,JoinedDate=@JoinedDate,Username=@Username,Password=@Password,CreatedDate=@CreatedDate where Username=@Username
    if(@Mode='D')
    Delete from tbl_Staff where Username=@Username
    end

Code for UsernameCheck()

    private void UsernameCheck()
        {
            SqlConnection con = new SqlConnection("Data Source=MY-PC;Initial Catalog=db_ProjectStatusManager;Integrated Security=True;");
            SqlCommand cmd = new SqlCommand("Select * from tbl_Staff where Username= @Username", con);
            cmd.Parameters.AddWithValue("@Username", this.txtUserame.Text);
            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (dr.HasRows == true)
                {
                    MessageBox.Show("Username = " + dr[6].ToString() + " Already exist");
                    txtUserame.Clear();
                    break;
                }
            }

}

Code for btn_SaveUser

    private void btnSaveUser_Click(object sender, EventArgs e)
        {



            try
            {

                int result = uc.ManageUser(txtFullName.Text, txtAddress.Text, txtPhone.Text, txtEmail.Text, Convert.ToDateTime(dateTimePickerJoinedDate.Text), txtUserame.Text, txtPassword.Text, Convert.ToDateTime(dateTimePickerCreatedDate.Text), "I");
                if (result == 1)
                {

                    MessageBox.Show("New User Saved");
                    dgvUserDetails.DataSource = uc.SelectAllUsers();
                    //MakeFieldsBlank();
                }
                else
                {
                    MessageBox.Show("SOME ERRORS OCCURED WHILE PROCESSING THE REQUEST");
                }

}

catch (Exception ex)
{

                MessageBox.Show(ex.Message);
            }
        }

Code For ManageUser

    public int ManageUser(String Name, String Address, String Phone, String Email, DateTime JoinedDate, String Username, String Password, DateTime CreatedDate, String Mode)
        {
            try
            {
                int result = 0;
                SqlCommand cmd = new SqlCommand("sp_ManageUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@StaffID",DBNull.Value);
                cmd.Parameters.AddWithValue("@Name", Name);
                cmd.Parameters.AddWithValue("@Address", Address);
                cmd.Parameters.AddWithValue("@Phone", Phone);
                cmd.Parameters.AddWithValue("@Email", Email);
                cmd.Parameters.AddWithValue("@JoinedDate", JoinedDate);
                cmd.Parameters.AddWithValue("@Username", Username);
                cmd.Parameters.AddWithValue("@Password", Password);
                cmd.Parameters.AddWithValue("@CreatedDate", CreatedDate);
                cmd.Parameters.AddWithValue("@Mode", Mode);

                conn.Open();
                result = cmd.ExecuteNonQuery();
                conn.Close();
                return result;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

Ero_Jiraiya

All replies (8)

_{Thursday, December 11, 2014 4:51 PM ✅Answered | 1 vote}

Hi ,

Declare your Method UsernameCheck() as below. In a below Method I am adding return type to the Method and returning the value finally.

public bool UsernameCheck()
{
isUserExisted=false;
SqlConnection con = new SqlConnection("Data Source=MY-PC;Initial Catalog=db_ProjectStatusManager;Integrated Security=True;");
SqlCommand cmd = new SqlCommand("Select * from tbl_Staff where Username= @Username", con);
cmd.Parameters.AddWithValue("@Username", this.txtUserame.Text);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.HasRows == true)
{
// MessageBox.Show("Username = " + dr[6].ToString() + " Already exist");
//  txtUserame.Clear();
isUserExisted=true;
 break;
}
}
return isUserExisted;
}

Don't forget to Mark it as an answer and Vote me if it useful.

Mahesh

_{Thursday, December 11, 2014 11:35 AM | 1 vote}

The database should always have a fallback check in there to protect it's integrity.

If you don;'t want a constraint on UserName then check in the stored proc which inserts.

BUT you should really just have a constraint.

if(@Mode='I')
     BEGIN
     If not exists (Select Username from tbl_Staff where Username=@Username)
         insert into tbl_Staff  .....
     END
     if(@Mode='U')
     Update tbl_Staff set ....

From your code, I don't see how UsernameCheck() is inserting anything at all. All it does is a select.

Could you explain how it inserts something?

Hope that helps
Please don't forget to upvote posts which you like and mark those which answer your question.

_{Thursday, December 11, 2014 11:44 AM | 1 vote}

HI,

Where you are calling UsernameCheck() Method.I didn't see in that in above Code.

You can do like this.

btnSaveUser_Click()

{
bool isUserExisted= UsernameCheck();

if(isUserExisted)
{
//Show alert Message.
}
else
{
//Create New User by calling Your Method ManageUser().
}
}

Please Mark it as an Answer and helpful, if it resolves your query.

Mahesh

_{Thursday, December 11, 2014 3:02 PM | 1 vote}

In your sproc you are relying on the caller to specify the operation to perform. This is dangerous. You should probably create one method to add/update users and a separate method to delete them. Inside your add/update script do an insert if the user doesn't exist (IF NOT EXIST (SELECT ...)) otherwise do an update. This allows the caller to simply add/update without having to worry about whether the user is already there or not. A separate delete sproc makes it clear that they are deleting when called.

On the client side where you're calling the sproc you are always passing "I" as the mode and hence you'll always be inserting. Since you've moved the add/update logic into the sproc that parameter goes away and your client code will work correctly.

As Andy mentioned you should also add a unique constraint on your users table if you want to ensure uniqueness. Nothing prevents someone from inserting/updating directly into the table so any constraints like this should be enforced at the database level.

If you have further questions around SQL then please post them in the SQL forums. For C# code you can post them here.

Michael Taylor
http://blogs.msmvps.com/p3net

_{Thursday, December 11, 2014 3:30 PM}

Hello Mahesh, Thank you for your suggestion. Using your suggestion gave error "cannot implicitly convert type void to bool" on UsernameCheck(). Could you please suggest how to solve this convert type?

Ero_Jiraiya

_{Thursday, December 11, 2014 3:57 PM}

Hello Andy, as you mentioned I used the constraints and made unique key username and changed the procedure as you suggested but it didn't inserted instead it gave error message of else statement .("SOME ERRORS OCCURED WHILE PROCESSING THE REQUEST");
BEGIN
if not exists (select Username from tbl_Staff where Username= @Username)
insert into tbl_Staff (Name,Address,Phone,Email,JoinedDate,Username,Password,CreatedDate) values(@Name,@Address,@Phone,@Email,@JoinedDate,@Username,@Password,@CreatedDate)
END

Ero_Jiraiya

_{Thursday, December 11, 2014 6:02 PM}

Thank You Mahesh , this solved the issue. But i added bool isUserExisted; on top of the method and it worked but i don't know if its right or not.

Ero_Jiraiya

_{Friday, December 12, 2014 5:05 AM | 1 vote}

Hi ,

It is correct only, I forgot to add datatype while declaring isUserExisted variable in the above Method.

Please mark it as an answer and vote me if it is resolved your problem.

Mahesh

Share via

C# check username if already exists from database

Question

All replies (8)

Additional resources