C# setting String variables as argument for SQL string statement

Question

I'm coming from Java programming and I recently just tried to study C# for web (.aspx). I'm new and still getting myself familiar with the components and how C# components are bound to the SQL Server database.

I'm having trouble setting values from TextBoxes which are assigned to String variables into my SQL statement.

Here's my code.

 protected void Btn_additem_Click(object sender, EventArgs e)
 {
     String category = "";
     String itemName = Tb_itemname.Text;
     String code = Tb_itemcode.Text;
     String brand = Tb_brand.Text;
     String serial = Tb_serial.Text;
     String capacity = Tb_capacity.Text;
     String version = Tb_version.Text;

     if (Rbl_hardsoft.SelectedValue.Equals("Hardware")) 
     {
         category = "Hardware";
     }
     else if(Rbl_hardsoft.SelectedValue.Equals("Software"))
     {
         category = "Software";
     }

     String SQL = "INSERT INTO ItemMasterData(item_code,item_category, item_name, item_brand,item_serialnumber, item_capacity, item_version) " +
            "VALUES(?,?,?,?,?,?,?)";
}

My goal is to be able to make the string variables category, itemName, code ...and so on to be arguments for the ?

In Java we usually make use of PreparedStatement wherein we use ? for arguments then we set its value thru setters. For instance,

ps.setString(1,"stringvalue or string variable"); // 1 for the first question mark
ps.setInt(2,intvalueOrintVariable); // 2 for the second question mark

How do I do that in C#? I'm not very familiar with DataSource yet and I would like to be able to assign parameters by code rather than by using the C# properties window.

I'd appreciate any help or practical example.

Thanks.

Search is a good friend to talk to, especially since he/she is the oracle of many such questions. You are not the first person to use "parameterized queries" in C#. You will not be the last. — user2864740, Aug 27 '17 at 06:30
Also, include the actual DataSource usage in the question for better responses.. — user2864740, Aug 27 '17 at 06:32
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, Aug 27 '17 at 08:36

score 2 · Accepted Answer · answered Aug 27 '17 at 06:05

Use parameters https://msdn.microsoft.com/library/bb738521(v=vs.100).aspx

 protected void Btn_additem_Click(object sender, EventArgs e)
 {
     String category = "";
     String itemName = Tb_itemname.Text;
     String code = Tb_itemcode.Text;
     String brand = Tb_brand.Text;
     String serial = Tb_serial.Text;
     String capacity = Tb_capacity.Text;
     String version = Tb_version.Text;

     if (Rbl_hardsoft.SelectedValue.Equals("Hardware")) 
     {
         category = "Hardware";
     }
     else if(Rbl_hardsoft.SelectedValue.Equals("Software"))
     {
         category = "Software";
     }

     using (SqlConnection connection = new SqlConnection(connectionString))
     using (SqlCommand command = connection.CreateCommand())
     command.CommandText = "INSERT INTO Contacts ItemMasterData(item_code,item_category, item_name, item_brand,item_serialnumber, item_capacity, item_version) VALUES (@item_code,@item_category, @item_name, @item_brand,@item_serialnumber, @item_capacity, @item_version)";
     command.Parameters.AddWithValue("@item_code", code );
     command.Parameters.AddWithValue("@item_category", category );
     command.Parameters.AddWithValue("@item_name", itemName );
     command.Parameters.AddWithValue("@item_brand", brand );
     .. the rest of parameters...

    connection.Open();
    command.ExecuteNonQuery();
}

@user2864740: Usually a DataSource (any of them) just wraps a number of SQL Commands (add, delete) in whatever your native code is. So that is not exactly a argument this code being valid. But there are a lot of classe named "DataSource", each working differently. I think the biggest difference to consider between Parametized Query and Prepared Statement, is that with Parametized Queries the statement is prepared on the client side. There is no need for that "prepare" step. I found Prepared Statements difficulty to learn and implement because of that difference to Parametized Querries. — Christopher, Sep 02 '17 at 21:21

score -1 · Answer 2 · edited Aug 27 '17 at 20:32

-1

It is entirely possible to build a SQL string via basic string concatenation and replacement. But for anything beyond the most simple test or if the string comes 100% for your code, do not.

In C# there is this whole concept of a parametrized query. It actually exists independent of a prepared statement. Which one to use depends on which database you use as this does directly work with the driver: https://msdn.microsoft.com/en-us/library/bb738521(v=vs.90).aspx

edited Aug 27 '17 at 20:32

marc_s

732,580
175
1,330
1,459

answered Aug 27 '17 at 06:02

Christopher

9,634
2
17
31

The OP acknowledges this. In fact, he/she asks how to use the equivalent of a Java "PreparedStatement" in C#. Stating this again is then not answering the question. Also, the OP is using a DataSource. – user2864740 Aug 27 '17 at 06:33

C# setting String variables as argument for SQL string statement

2 Answers2