sqlQuery with if else

Question

I'm automating student information system in C # programming language and visual studio. I am using MSSQL as a database. At first I had created a lot of different tables such as students, teachers, parents, lectures and admin. But then I decided to collect them in one table. That's my question.

I've created a variable named userType in the sql query. When the user in the table with UserType 1 logs into the system, for example, go to the admin panel. I want the user to go to the student table when the person who has 2 has logged in. I'm a student at first, if you're a teacher, if you're admin, I would open three label labels separately and process each label individually. But now I want the user to go to the appropriate form based on the type when the user logs in. So I said the performance would be better.

The Sql query is here;

 declare @userType SMALLINT
    if (@userType = 1)

    "Select * from singleTable where UserType = 1 and UserName = '" + userName.Text + "'"
    else if (@userType = 2) Select * from singleTable where UserType = 2 and UserName = '" + userName.Text + "'
    else if (@userType = 3) select * from singleTable where UserType = 3 and UserName = '" + userName.Text + "'

It's okay up here. But I couldn't figure out how to send the form to the appropriate form.

query = new SqlCommand ("declare @userType SMALLINT if @userType = 1" +
        "Select * from singleTable where UserType = 1 and UserName = '" + userName.Text + "'" +
    "else if @userType = 2 Select * from singleTable where UserType = 2 and UserName = '" + userName.Text + "'" +
    "else if @userType = 3 select * from singleTable where UserType = 3 and UserName = '" + userName.Text + "'", conn);
                dr = query.ExecuteReader ();
                if (dr.Read ())
                {
                    MessageBox.Show ("Login is successful. Welcome" "+ userName.Text +" '");
                    studentPanel form = new studentPanel ();
                    form.userName = userName.Text;
                    Form.ShowDialog ();
                   this.Hide ();
                }

The above is the code you provide if you're seeing access to a single form. I want to connect with the @userType variable above.

So I want to open new if blocks in if (dr.Read).

if (@userType = 1) {
adminPanel form = new adminPanel ();
}
else if (@userType = 2) {
teacherPanel form = new teacherPanel ();
}

else if (@userType = 3) {
studentPanel form = new studentPanel ();
}

Like ... Waiting for your help. Respects.

Hey. SqlConnection is have null value.

private void loginButton_Click(object sender, EventArgs e)
    {
        string connection = @"Data Source=DESKTOP-AG9TT68;Initial Catalog=studentInformation;Integrated Security=True";
        SqlConnection conn = new SqlConnection(connection);
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
            var query = new SqlCommand(@"IF @UserType = 'admin' 
            BEGIN
            Select * from singleTable where UserType = 'admin' and UserName = @Username;
            END
            IF @UserType = 'teacher' 
            BEGIN 
            Select * from singleTable where UserType = 'teacher' and UserName = @Username;
            END
            IF @UserType = 'student' 
            BEGIN 
            select * from singleTable where UserType = 'student' and UserName = @Username;
            END", conn);
            //You should pass parameters to avoid SQL injection
            var userType = "@UserType";
            query.Parameters.AddWithValue("@UserType", userType);
            query.Parameters.AddWithValue("@Username", userName.Text);

            var dr = query.ExecuteReader();
            if (dr.Read())
            {

                if (userType == "'admin'")
                {
                    MessageBox.Show("Login is successful. Welcome '" + userName.Text + "'");
                    adminPanel form = new adminPanel();
                    form.ShowDialog();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("Your username or password is wrong!");
                }

                if (userType == "'teacher'")
                {
                    MessageBox.Show("Login is successful. Welcome '" + userName.Text + "'");
                    teacherPanel form = new teacherPanel();
                    form.ShowDialog();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("Your username or password is wrong!");
                }

                if (userType == "'student'")
                {
                    MessageBox.Show("Login is successful. Welcome '" + userName.Text + "'");
                    studentPanel form = new studentPanel();
                    form.ShowDialog();
                    this.Hide();
                }
                else
                {
                    MessageBox.Show("Your username or password is wrong!");
                }



            }

        }
    }

Unrelated tips: avoid string concatenation to create the query or your code is vulnerable to SQL injection attacks: use SQL parameters instead. Also, SqlCommand and SqlDataReader and your form are all IDisposable so each should be in a `using` block. Consider doing `where UserType = @userType` in a single query rather than 3 alternatives. — Richardissimo, Oct 28 '18 at 10:12
Consider using SQL parameters. Output parameters may help you. — Morteza, Oct 28 '18 at 10:18
Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) — mjwills, Oct 28 '18 at 10:22
You are confusing things with your approach and you description. There are 2 different things you need to do. First is to validate access. It appears that this is based only on a name entered by a person. No password? That is a terrible idea. Anyone can type in any name and access the system with that identity. No, no, no, and no. And if your query finds no matching row, what happens? AFTER you validate access, you then need to retrieve the user's type from the table and then decide what to do. (continued) — SMor, Oct 28 '18 at 12:29
When you retrieve that value, you store it. You store it in some session-state like area where you store other information about the session. Your app then refers to it to decide what to show, what navigation to allow, etc. Your last code idea confuses tsql with csharp. Do you define variables in your app with names like @UserType? That is tsql. Get the access validation working FIRST with parameters. Then worry about refining that by adding the navigation to the appropriate form/page. — SMor, Oct 28 '18 at 12:33
The idea that you add a parameter for UserType is just wrong IMO. Esperanto comes closer to a possible solution - but that code does not retrieve the user type from the table and the switch statement I can't understand. — SMor, Oct 28 '18 at 12:37

score 0 · Answer 1 · edited Oct 28 '18 at 11:47

Pass @userType as a parameter to your query:

var query = new SqlCommand(@"IF @UserType = 1 
BEGIN
    Select * from singleTable where UserType = 1 and UserName = @Username;
END
IF @UserType = 2 
BEGIN 
    Select * from singleTable where UserType = 2 and UserName = @Username;
END
IF @UserType = 3 
BEGIN 
select * from singleTable where UserType = 3 and UserName = @Username;
END", conn);

//You should pass parameters to avoid SQL injection
        query.Parameters.AddWithValue("@UserType", userType);
        query.Parameters.AddWithValue("@Username", username.Text);

    var dr = query.ExecuteReader();

Or as @mjwills suggested, you can do it without if conditions:

var query = "Select * from singleTable where UserType = @UserType and UserName = @Username and UserType IN (1,2,3)";

query.Parameters.AddWithValue("@UserType", userType);
query.Parameters.AddWithValue("@Username", username.Text);

var dr = query.ExecuteReader();

Could you instead use `Select * from singleTable where UserType = @UserType and UserName = @Username and UserType IN (1,2,3)`? What is the benefit of the `IF` clauses? — mjwills, Oct 28 '18 at 10:30
**DO NOT** use [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). Parameterize correctly. — SMor, Oct 28 '18 at 12:23

score 0 · Answer 2 · answered Oct 28 '18 at 11:17

try Something like this

private void button1_Click(object sender, EventArgs e)
        {

            SqlCommand cmd = null;
            SqlDataAdapter da = null;
            DataTable dt = null;
            Form form = null;
            int UserID = -1;

            try
            {
                string Query = "select UserID from tblName where UserName = @Username ";
                cmd = new SqlCommand(Query, con);
                da = new SqlDataAdapter();
                dt = new DataTable();
                cmd.Parameters.AddWithValue("@Username", username.Text);

                con.Open();
                da.Fill(dt);

                if (dt.Rows.Count==0)
                {
                    throw new Exception(string.Format("User '{0}' not founded", username.Text));
                }

                if (dt.Rows.Count>1)
                {
                    throw new Exception(string.Format("User '{0}' founded but multiple", username.Text));
                }

                UserID = (int)dt.Rows[0]["UserID"];

                switch (UserID)
                {
                    case 1:
                        form = new adminPanel();
                        break;

                    case 2:
                        form = new teacherPanel();
                        break;

                    case 3:
                        form = new studentPanel();
                        break;

                    default:
                        throw new Exception(string.Format("User ID '{0}' not implemented", UserID));
                }

                this.Hide();
                form.ShowDialog();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
                if (cmd != null) cmd.Dispose();
                if (da != null) da.Dispose();
                if (dt != null) dt.Dispose();
            }
        }

Do not use [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). Parameterize correctly. I can't see the logic behind the switch. And you do not want to "help" someone hack the system by providing error messages that are too helpful. — SMor, Oct 28 '18 at 12:38
AddWithValue work correctly, log behin switch are equal to question asked and errors are intercepted to the same place — Esperento57, Oct 28 '18 at 17:10

score 0 · Answer 3 · answered Oct 28 '18 at 15:16

wirte like like

 query = new SqlCommand ("Select UserType from singleTable where UserName = @userName", conn);
    query.Parameters.AddWithValue("@userName", userName.Text);                    
    dr = query.ExecuteReader ();
    if (dr.Read ())
      {
          MessageBox.Show ("Login is successful. Welcome '"+ userName.Text +"'");
          if(Convert.ToInt32(dr["UserType"]) == 1) {
            adminPanel form = new adminPanel ();
           }
          else if (Convert.ToInt32(dr["UserType"]) == 2) {
            teacherPanel form = new teacherPanel ();
           }

           else if (Convert.ToInt32(dr["UserType"])== 3) {
        studentPanel form = new studentPanel ();
         }

       }

sqlQuery with if else

3 Answers3