Make condition right using SqlDataReader?

Question

var a = "1";
var b = "2";
var c = "3";

var name = authResult.ExtraData["email"];

string connectionString = null;

SqlConnection cnn;
SqlCommand cmd;
string sql = null;
SqlDataReader reader;

connectionString = "Data Source = dj0043\\sqlexpress; Initial Catalog = XXXX; Integrated Security = True";
sql = "Select EmployeeRoles.RoleId From EmployeeList Inner Join EmployeeRoles on EmployeeList.EmployeeId = EmployeeRoles.EmployeeId Where EmailId = name";

cnn = new SqlConnection(connectionString);

try
{
    cnn.Open();
    cmd = new SqlCommand(sql, cnn);
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        if (reader == a)
        {
            return Redirect(Url.Action("Employee", "Home"));
        }
        else if (sql == b)
        {
            return Redirect(Url.Action("Index", "Home"));
        }
        else if (sql == c)
        {
            return Redirect(Url.Action("Index", "Home"));
        }
    }

    reader.Close();
    cmd.Dispose();
    cnn.Close();
}

In this if any user have the 1 id will be redirected to that page and same as other vice versa.

Can anyone tell me how to make SqlDataReader work with this condition?

Answer 1

First of all, your SQL query is not correct. You should send a @name parameter into the query.

I prefer to do it this way as it's much more cleaner

var a = "1";
var b = "2";
var c = "3";

var name = authResult.ExtraData["email"];
var connectionString = "Data Source = dj0043\\sqlexpress; Initial Catalog = XXXX; Integrated Security = True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
           connection.Open();
           using (SqlCommand cmd = connection.CreateCommand())
           {
               cmd.CommandText = "Select EmployeeRoles.RoleId From EmployeeList Inner Join EmployeeRoles on EmployeeList.EmployeeId = EmployeeRoles.EmployeeId Where EmailId = @name";
               cmd.CommandType = CommandType.Text;
               cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = name;


               using (SqlDataReader reader = cmd.ExecuteReader())
               {
                    while (reader.Read())
                    {
                       if (reader["RoleId"].ToString() == a)
                       {
                          return Redirect(Url.Action("Employee", "Home"));
                       }
                       else if (reader["RoleId"].ToString() == b)
                       {
                          return Redirect(Url.Action("Index", "Home"));
                       }
                       else if (reader["RoleId"].ToString() == c)
                       {
                          return Redirect(Url.Action("Index", "Home"));
                       }
                    }
               }
          }
}