C# & WPF: connection with SQL Server database

Question

I am working with a project in C# using WPF, connected to a SQL Server database. I can insert data into a table, delete, update and select.

For example I have a table with a column Parts and another Quantity.

I have these sample values: Parts: Tires and Quantity: 3.

What I want to do is when I once choose the tires, the quantity would become 2; i.e, Quantity(2). Thanks to everyone!

The code-behind is:

{
    SqlConnection conn = new SqlConnection("Server = localhost;Database = autoser; Integrated Security = true");

    conn.Open();
    SqlCommand cmd = new SqlCommand("SELECT Part FROM autoparts WHERE Part LIKE '%" + txtpart.Text + "%'", conn);

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable("dtkerkimi");                          
    da.Fill(dt);

    datag.ItemsSource = dt.DefaultView;

    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adapt.Fill(ds);
    conn.Close();

    int count = ds.Tables[0].Rows.Count;

    if (count == 0)
    {
        MessageBox.Show("There isn't a part that you are looking for!");
    }
    else
    {
        MessageBox.Show("Congrats");
    }
}

Are you asking how to issue a SQL update statement? https://msdn.microsoft.com/en-us/library/ms177523.aspx Your example is also an excellent example of how to create a SQL Injection vulnerability (http://stackoverflow.com/questions/601300/what-is-sql-injection). I would strongly recommend researching parameterized SQL. — DVK, May 06 '16 at 13:51
No, what i want to do when i choose tires in the part field, the quantity should be deducted from three to two. And after some time when i choose again tires the quantity should be deducted from two to one..... — Leo Mujaj, May 06 '16 at 13:55
Can you provide code for how people choose a part or select a quantity? Is there an event handler attached to some event on a control? — DVK, May 06 '16 at 13:59
DVK, i'm a bit confused about that part, for moment i don't have code to do that, i have in sql part and quantity field. The quantity field should be decreased automatically, after i choose the part — Leo Mujaj, May 06 '16 at 14:14

score 1 · Answer 1 · answered May 06 '16 at 14:30

1

This is the SQL you are looking for to Update and Return Result

UPDATE autoparts
SET    autoparts.Quantity = autoparts.Quantity-1 
OUTPUT DELETED.Part
WHERE   autoparts.Part like '%Tires%'

answered May 06 '16 at 14:30

Dmitriy

638
1
6
12

mark_h · Accepted Answer · 2016-05-07T10:44:19.957

Your select statement will only get data, it cannot update it. For that you will need an update statement similar to the one below

SqlCommand upDateCmd = new SqlCommand("UPDATE autoparts SET Quantity = Quantity - 1 WHERE Part Like  '%" + txtpart.Text + "%'", conn);

This should find the part you want and then reduce the quantity by 1 (if it's 0 it will reduce it to -1, if you don't want that to happen then put the code that uses the update command in the else block of your code sample).

However this risks a sql injection attack. A safer approach would be to use parameters which give protection against these forms of attack.

        var upDateCommand = new SqlCommand
        {
            CommandText = "UPDATE autoparts SET Quantity = Quantity - 1 WHERE Part = @partName"
        };
        upDateCommand.Parameters.AddWithValue("@partName", txtpart.Text);

C# & WPF: connection with SQL Server database

2 Answers2