checking user name or user email already exists

Question

I am working in a simple registration page where the user can't enter the same user name or email, I made a code that prevent the user from entering the username and it worked but when I tried to prevent the user from entring the same username or email it didn't work.

and my question is, "How can I add another condition where the user can't enter email that already exists?"

I tried to do it in this code, but it did't work:

protected void Button_Click(object sender, EventArgs e)
{
  SqlConnection con = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString );
  SqlCommand cmd1 = new SqlCommand("select 1 from Table where Name =@UserName", con);
  SqlCommand cmd2 = new SqlCommand("select 1 from Table where Email=@UserEmail", con);

  con.Open();
  cmd1.Parameters.AddWithValue("@UserName", Name_id.Text);
  cmd2.Parameters.AddWithValue("@UserEmail", Email_id.Text); 

  using (var dr1 = cmd1.ExecuteReader())
  {
    if (dr1.HasRows)
    {
      Label1.Text = "user name already exists";
    }
    using (var dr2 = cmd2.ExecuteReader())
    {
      if (dr2.HasRows)
      {
        Label1.Text = "email already exists";
      }
      else
      {
        dr1.Close();
        dr2.Close();
        //add new users
        con.Close();
      }
    }
  }  
}

but i get this error:

There is already an open DataReader associated with this Command which must be closed first.

Answer 1

You have opened another DataReader inside the First and thats causing the problem. Here I have re-arranged your code a bit

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd1 = new SqlCommand("select 1 from Table where Name =@UserName", con),
cmd2 = new SqlCommand("select 1 from Table where Email=@UserEmail", con);

con.Open();
cmd1.Parameters.AddWithValue("@UserName", Name_id.Text);
cmd2.Parameters.AddWithValue("@UserEmail", Email_id.Text);

bool userExists = false, mailExists = false;

using (var dr1 = cmd1.ExecuteReader())
     if (userExists = dr1.HasRows) Label1.Text = "user name already exists";

using (var dr2 = cmd2.ExecuteReader())
     if (mailExists = dr2.HasRows) Label1.Text = "email already exists";

if (!(userExists || mailExists)) {
     // can add User
}

Answer 2

Like I said in my comment your design is bad !

First you should have Data Access Layer. This should be project in big solutions but in your case you can put it like new directory. In this directory you create SqlManager class here is the code:

public class SqlManager
{

    public static string ConnectionString
    {
        get
        {
            return ConfigurationManager.ConnectionStrings["DevConnString"].ConnectionString;
        }
    }

    public static SqlConnection GetSqlConnection(SqlCommand cmd)
    {
        if (cmd.Connection == null)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);

            conn.Open();

            cmd.Connection = conn;

            return conn;
        }

        return cmd.Connection; 
    }

    public static int ExecuteNonQuery(SqlCommand cmd)
    {
        SqlConnection conn = GetSqlConnection(cmd);

        try
        {
            return cmd.ExecuteNonQuery();
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
        }
    }

    public static object ExecuteScalar(SqlCommand cmd)
    {

        SqlConnection conn = GetSqlConnection(cmd);

        try
        {
            return cmd.ExecuteScalar();
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
        }
    }

    public static DataSet GetDataSet(SqlCommand cmd)
    {
        return GetDataSet(cmd, "Table");
    }

    public static DataSet GetDataSet(SqlCommand cmd, string defaultTable)
    {
        SqlConnection conn = GetSqlConnection(cmd);

        try
        {
            DataSet resultDst = new DataSet();

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(resultDst, defaultTable);
            }

            return resultDst;
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
        }
    }


    public static DataRow GetDataRow(SqlCommand cmd)
    {
        return GetDataRow(cmd, "Table");
    }

    public static DataRow GetDataRow(SqlCommand cmd, string defaultTable)
    {
        SqlConnection conn = GetSqlConnection(cmd);

        try
        {
            DataSet resultDst = new DataSet();

            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(resultDst, defaultTable);
            }

            if (resultDst.Tables.Count > 0 && resultDst.Tables[0].Rows.Count > 0)
            {
                return resultDst.Tables[0].Rows[0];
            }
            else
            {
                return null;
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            conn.Close();
        }
    }
}

After that you should have Business Object Layer. In bigger solution is project in your case directory. If you are in the page TaxesEdit.aspx, you should add Tax.cs class in the BO(business object).

Example of methods for the class, for your first button:

public DataSet GetTaxesByUserName(string userName)
{
     SqlCommand cmd = new SqlCommand(@"

        select 1 from Table where Name =@UserName");

      cmd.Parameters.AddWithValue("@UserName", userName);

      return DA.SqlManager.GetDataSet(cmd);
}

You fetch all the needed data in datasets. After that you make checks like taxesDst.Tables[0].Rows.Count > 0 (or == 0)

For Insert you can have method like this:

    public virtual void Insert(params object[] colValues)
    {
        if (colValues == null || colValues.Length % 2 != 0)
            throw new ArgumentException("Invalid column values passed in. Expects pairs (ColumnName, ColumnValue).");

        SqlCommand cmd = new SqlCommand("INSERT INTO " + TableName + " ( {0} ) VALUES ( {1} )");

        string insertCols = string.Empty;
        string insertParams = string.Empty;

        for (int i = 0; i < colValues.Length; i += 2)
        {
            string separator = ", ";
            if (i == colValues.Length - 2)
                separator = "";

            string param = "@P" + i;

            insertCols += colValues[i] + separator;
            insertParams += param + separator;

            cmd.Parameters.AddWithValue(param, colValues[i + 1]);
        }

        cmd.CommandText = string.Format(cmd.CommandText, insertCols, insertParams);

        DA.SqlManager.ExecuteNonQuery(cmd);
    }

For this you need to have property TableName in the current BO class.

In this case this methods can be used everywhere and you need only one line of code to invoke them and no problems like yours will happen.

Answer 3

Why don't you do something like this:

[Flags]
public enum ValidationStatus
{
  Valid         = 0 ,
  UserNameInUse = 1 ,
  EmailInUse    = 2 ,
}
public ValidationStatus ValidateUser( string userName , string emailAddr )
{
  ValidationStatus status ;
  string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString ;

  using ( SqlConnection con = new SqlConnection( connectionString ) )
  using ( SqlCommand    cmd = con.CreateCommand() )
  {
    cmd.CommandText + @"
      select status = coalesce( ( select 1 from dbo.myTable t where t.UserName  = @UserName  ) , 0 )
                    + coalesce( ( select 2 from dbo.myTable t where t.UserEmail = @UserEmail ) , 0 )
       " ;
            cmd.Parameters.AddWithValue( "@UserName"  , userName  ) ;
            cmd.Parameters.AddWithValue( "@emailAddr" , emailAddr ) ;

    int value = (int) cmd.ExecuteScalar() ;
    status = (ValidationStatus) value ;

  }

  return status ;
}

Aside from anything else, hitting the DB twice for something like this is silly. And this more clearly expresses intent.

Then you can use it in your button click handler, something like this:

protected void Button_Click( object sender , EventArgs e )
{
  string           userName  = Name_id.Text ;
  string           emailAddr = Email_id.Text ;
  ValidationStatus status    = ValidateUser( userName , emailAddr ) ;

  switch ( status )
  {
  case ValidationStatus.Valid         :
    Label1.Text = "" ;
    break ;
  case ValidationStatus.EmailInUse    :
    Label1.Text = "Email address in use" ;
    break ;
  case ValidationStatus.UserNameInUse :
    Label1.Text = "User name in use" ;
    break ;
  case ValidationStatus.EmailInUse|ValidationStatus.UserNameInUse:
    Label1.Text = "Both user name and email address in use." ;
    break ;
  default :
    throw new InvalidOperationException() ;
  }

  if ( status == ValidationStatus.Valid )
  {
    CreateNewUser() ;
  }
}

Answer 4

This may work, although there are a few things I would do differently...

protected void Button_Click(object sender, EventArgs e)
{
    bool inputIsValid = true;
    var con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    var userNameCmd = new SqlCommand("SELECT 1 FROM Table WHERE Name = @UserName", con);
    var emailCmd = new SqlCommand("SELECT 1 FROM Table WHERE Email = @UserEmail", con);

    con.Open();
    userNameCmd.Parameters.AddWithValue("@UserName", Name_id.Text);
    emailCmd.Parameters.AddWithValue("@UserEmail", Email_id.Text);

    using (var userNameReader = userNameCmd.ExecuteReader())
    {
        if (userNameReader.HasRows)
        {
            inputIsValid = false;
            Label1.Text = "User name already exists";
        }
    }
    using (var emailReader = emailCmd.ExecuteReader())
    {
        if (emailReader.HasRows)
        {
            inputIsValid = false;
            Label1.Text = "Email address already exists";
        }
    }

    if (inputIsValid)
    {
        // Insert code goes here
    }
    con.Close();
}

Answer 5

You need to close one datareader before opening the other one. Although it's not how I'd do it, but you can deal with the runtime error by closing the datareader after each IF:

using (var dr1 = cmd1.ExecuteReader())
{
    if (dr1.HasRows)
    {
        string Text = "user name already exists";
    }
    dr1.Close();
}

using (var dr2 = cmd2.ExecuteReader())
{
    if (dr2.HasRows)
    {
        string ext = "email already exists";
    }

    else
    {
        //add new users
    }
    dr2.Close();
}
con.Close();