How to Save 2 different Cell values into 2 different variables from database C#

Question

I am stuck on collecting 2 column values from a database row. this method is only working to retrieve one value, not for 2. I need to save values from cells to Different variables then I will use these variables to populate another database.

string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True";
using (var con2 = new SqlConnection(connectionString))
{
    try
    {
        con2.Open();             
        SqlCommand command = new SqlCommand();
        command.Connection = con2;
        command.CommandText = string.Format("update Inventory set Quantity= Quantity - {0} WHERE id='"+tbItemid.Text+"'", Convert.ToInt32(tbQuantity.Text));
        command.ExecuteNonQuery();
        con2.Close();
        Data();
        DData();
        con2.Open();
        int x = int.Parse(tbQuantity.Text);
        SqlCommand cmd1 = new SqlCommand("SELECT Model from Inventory WHERE id='" + tbItemid.Text + "'", con2);
        SqlDataReader modelRdr = null;
        modelRdr = cmd1.ExecuteReader();
        modelRdr.Read();
        modelRdr = cmd1.ExecuteReader();
        string model = modelRdr["model"].ToString();
        con2.Close();
        con.Open();
        int y = int.Parse(tbQuantity.Text);
        SqlCommand cmd2 = new SqlCommand("SELECT Price from Inventory WHERE id='" + tbItemid.Text + "'", con2);
        SqlDataReader pricerdr = null;
        pricerdr = cmd2.ExecuteReader();
        pricerdr.Read();
        int price = int.Parse(pricerdr["Price"].ToString());
        SqlCommand cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Bill values (" + tbItemid.Text + ",'"  +model.ToString()+ "',"+price.ToString()+",'"+tbQuantity.Text+"')";
        cmd.ExecuteNonQuery();
        con.Close();
        Data();
    }
    catch
    {
        MessageBox.Show("Enter Catagory and Product ID");
    }
}

Answer 1

First thing first you should use Parameterized Queries instead of Concatenations. These kind of queries are prone to SQL Injection. You can read both the columns in one command

SqlCommand cmd1 = new SqlCommand("SELECT Model, Price from Inventory WHERE id='" + tbItemid.Text + "'", con2);
SqlDataReader modelRdr = null;
modelRdr = cmd1.ExecuteReader();
modelRdr.Read();
modelRdr = cmd1.ExecuteReader();
string model = modelRdr["model"].ToString();
int price = int.Parse(modelRdr["Price"].ToString());

The complete code with Parameters would look like

string model=String.Empty;
int price = 0;
string connectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True";
using (SqlConnection con2 = new SqlConnection(connectionString))
{
    try
    {
        con2.Open();             
        using(SqlCommand command = new SqlCommand())
        {
            command.Connection = con2;
            command.CommandText = string.Format("update Inventory set Quantity = Quantity - @qty WHERE id=@id";
            command.Parameters.AddWithValue("@id", tbItemid.Text);
            command.Parameters.AddWithValue("@qty", Convert.ToInt32(tbQuantity.Text)));
            command.ExecuteNonQuery();

            Data();
            DData();

            int x = int.Parse(tbQuantity.Text);
            using(SqlCommand cmd1 = new SqlCommand("SELECT Model, Price from Inventory WHERE id=@id"))
            {
                cmd1.Parameters.AddWithValue("@id", tbItemid.Text);
                SqlDataReader modelRdr = null;
                modelRdr = cmd1.ExecuteReader();
                modelRdr.Read();
                model = modelRdr["model"].ToString();
                price = int.Parse(modelRdr["Price"].ToString());    
            }
            using(SqlCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "insert into Bill values (@id,@model,@price,@qty)";.
                cmd.Parameters.AddWithValue("@id", tbItemid.Text);
                cmd.Parameters.AddWithValue("@model", model);
                cmd.Parameters.AddWithValue("@price", price);
                cmd.Parameters.AddWithValue("@qty", tbQuantity.Text);
                cmd.ExecuteNonQuery();
            }
            Data();
        }
        catch
        {
            MessageBox.Show("Enter Catagory and Product ID");
        }
    }
}