Loading data from a sql database table based on a variable value

[andrewmanuja]

Hi All,

I am new to visual C# and currently working on developing an application.

I got a data base table which got a integer field named "orderID" and I want to access the data relating to a given integer variable which matches with the respective orderID value.
For example, I have declared a integer variable named "a" and has assign a value as 2 ( int a =2.
I later want to pass variable values based on the user input and assign it to "a".
I wrote the below C# code to select the data from the table; "SqlDataAdapter sda = new SqlDataAdapter("SELECT orderID, costcentre,drugID,drugname,requestquantity FROM tbl_order WHERE orderID=2", con);"

con refers to the SqlConnection variable.

However, I do not get the desired output with the above code but, do get the records if I statically assign a value as " WHERE orderID =2".

I may have miss something in regards to data conversion and not able to rectify the issue (Please refer the attached screenshot of the code below).

Appreciate a lot in advance if someone in the community could help me with the said issue.

Kind regards,

Andrew

 

[Skydiver]

Unfortunately, SQL knows nothing about C#. Furthermore, SQL is executed on the SQL server, while your code is running on your machine, making it even harder for a to be deduced by the SQL server.

So the quick and dirty solution that opens you up to SQL injections is to replace that a in your SQL query with the actual value:

:
string query = $"SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID={a}";
// the above uses string interpolation for a cleaner version of
// string query = String.Format("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID={0}", a);

var sda = SqlDataAdapter(query, con);
:

Obligatory SQL injection warning cartoon:

The correct and safe way is to use parameterized queries:

var command = new SqlCommand("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID=@id", con);
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters["@id"].Value = a;

var sda = SqlDataAdapter(command);

 

[andrewmanuja]

Unfortunately, SQL knows nothing about C#. Furthermore, SQL is executed on the SQL server, while your code is running on your machine, making it even harder for a to be deduced by the SQL server.

So the quick and dirty solution that opens you up to SQL injections is to replace that a in your SQL query with the actual value:

:
string query = $"SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID={a}";
// the above uses string interpolation for a cleaner version of
// string query = String.Format("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID={0}", a);

var sda = SqlDataAdapter(query, con);
:

Obligatory SQL injection warning cartoon:

The correct and safe way is to use parameterized queries:

var command = new SqlCommand("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID=@id", con);
command.Parameters.Add("@id", SqlDbType.Int);
command.Parameters["@id"].Value = a;

var sda = SqlDataAdapter(command);

Hi,

Thank you very much for your prompt reply.
However, I'm getting an error at " var sda = SqlDataAdapter(command);" stating that Non-invocable member 'SqlDAtAdapter' cannot be used like a method.

Please refer the code after adding your piece.

SqlConnection con = new SqlConnection(@"Data Source=ABC\SQLEXPRESS;Initial Catalog=Order_DB;Integrated Security=True");
            
            con.Open();
            int a = 2;
           
            var command = new SqlCommand("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID=@id", con);
           
            command.Parameters.Add("@id", SqlDbType.Int);
            command.Parameters["@id"].Value = a;

            var sda = SqlDataAdapter(command);

            dataGridView1.Rows.Clear();
            DataTable dt = new DataTable();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;
            
            con.Close();

Appreciate your valuable feedback.

Thank you very much in advance.

Kind regards,

Andrew

 

[jmcilhinney]

That should have been:

var sda = new SqlDataAdapter(command);

 

[andrewmanuja]

Hi,

Thank you very much. It's working.

Kind regards,

Andrew

 

[jmcilhinney]

There's nothing specifically wrong with the way it was shown earlier but, for the record, I would have done it like this:

var sda = new SqlDataAdapter("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID=@id", con);

sda.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = a;

It's basically the same thing but with slightly more succinct code.

 

[andrewmanuja]

There's nothing specifically wrong with the way it was shown earlier but, for the record, I would have done it like this:

var sda = new SqlDataAdapter("SELECT orderID, costcentre, drugID, drugname, requestquantity FROM tbl_order WHERE orderID=@id", con);

sda.SelectCommand.Parameters.Add("@id", SqlDbType.Int).Value = a;

It's basically the same thing but with slightly more succinct code.

Thanks a lot.

This is a great forum to learn C#

Kind regards,

Andrew