Using variables vs SQL Server in using statement

Question

Can someone help me understand what's happening? I'm trying to get the

SAQA ID
NQF Level
Credits

of a course but there's something I don't understand.

If I create a variable with my query such as

public Int32 T_Course_Id = 0, T_Company_Id = 0, T_Nqf = 0, T_Credit = 0;

string queryTaskId = "SELECT [course_saqa_id] FROM"+
                     "[sta].[dbo].[Courses]"+
                     "WHERE course_name = '" + _Coursename + "'";

string queryNqf = "SELECT [course_nqf]"+
                  "FROM [sta].[dbo].[Courses]"+
                  "WHERE course_saqa_id = '" + T_Course_Id + "'";

using (SqlConnection Conn = new SqlConnection(ConnString))
{
    Conn.Open();

    using (SqlCommand command = new SqlCommand(queryTaskId, Conn))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                reader.Read();
                // Call Read before accessing data. 
                T_Course_Id = reader.GetInt32(0);
            }

            // Call Close when done reading.
            reader.Close();
        }
    }

    using (SqlCommand command = new SqlCommand(queryCredit, Conn))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                reader.Read();

               // Call Read before accessing data. 
                T_Credit = reader.GetInt32(0);
            }

            // Call Close when done reading.
            reader.Close();
        }
    }                    

    Conn.Close();
}

If I do it this way I get a 0 value for the T_Credit variable, but if I do it like this (this is only the last part)

using (SqlCommand command = new SqlCommand("SELECT [course_nqf] FROM [sta].[dbo].[Courses] WHERE course_saqa_id = '" + T_Course_Id + "'", Conn))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            reader.Read();
            // Call Read before accessing data. 
            T_Credit = reader.GetInt32(0);
        }

        // Call Close when done reading.
        reader.Close();
    }
 }

then I get the correct value, as you can see I'm passing the SQL command directly instead of a variable

using (SqlCommand command = new SqlCommand("SELECT [course_nqf] FROM [sta].[dbo].[Courses] WHERE course_saqa_id = '" + T_Course_Id + "'", Conn))

Why won't a variable work here ?

Have you tried using template strings? C# 6 Moreover, why are you calling close when you're already using the using() { ... } style? — codejockie
– codejockie, Commented Aug 29, 2017 at 11:06
It looks it's just a typo, you are using the wrong variable in your first case. You should use queryNqf instead of queryCredit. — Alisson Reinaldo Silva
– Alisson Reinaldo Silva, Commented Aug 29, 2017 at 11:09
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Aug 29, 2017 at 11:31
It would also be helpful to have some spaces between your keyword, if you concatenate together your SQL statement like that..... e.g. use "SELECT [course_saqa_id] FROM " + "[sta].[dbo].[Courses]" + " WHERE course_name = @CourseName"; - note the SPACE after the FROM and before the WHERE keywords .... — marc_s
– marc_s, Commented Aug 29, 2017 at 11:32

AJ X. · Accepted Answer · 2017-08-29 11:20:28Z

Order of operations matters.

In your first example: You are setting the value of your T_Course_ID = 0, then creating a query string immediately after. At that time, the T_Course_ID value is evaluated to 0 and forgotten. The string concatenation IS NOT evaluated each time it is used. ONLY at assignment.

In your second example: T_Course_ID is assigned a new value by your initial lookup. Since the SQLCommand is executed after the variable reassignment, it is evaluated using the new value every time.

Here is an example using javascript, the same applies there was well:

var T_Course_Id = 0;

var queryNqf =
  "SELECT [course_nqf]" +
  "FROM [sta].[dbo].[Courses]" +
  "WHERE course_saqa_id = '" + T_Course_Id + "'";

// Query displays initialized value
console.log(queryNqf);

// Variable is changed
T_Course_Id = 'I Will NOT CHANGE';

// Try to log the updated value but it doesn't work because queryNqf was assigned too early
console.log(queryNqf);

// Reassign with the new value
queryNqf =
  "SELECT [course_nqf]" +
  "FROM [sta].[dbo].[Courses]" +
  "WHERE course_saqa_id = '" + T_Course_Id + "'";

// Retrieve the expect result.
console.log(queryNqf);

Krishna · Accepted Answer · 2017-08-29 11:12:24Z

0

It seems you are not using the correct command string, queryNqf in your using statement.

Yours: using (SqlCommand command = new SqlCommand(queryCredit, Conn))

Could Be: using (SqlCommand command = new SqlCommand(queryNqf , Conn))

edited Aug 29, 2017 at 11:12

answered Aug 29, 2017 at 11:06

Krishna

6801 gold badge7 silver badges10 bronze badges

Collectives™ on Stack Overflow

Using variables vs SQL Server in using statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related