C#, WPF, SQL Server database

Question

I am working on a C# application where the main part of my program is about stock and items available on stock. What I want to do is when I choose an item from stock the quantity should deduct, I know how to deduct the quantity.

Problem is this: when the quantity reaches the value 0, the deduct should stop and application should return a message that notice me about that.

My code that I tried is this:

private void btn_Click(object sender, RoutedEventArgs e)
{
    SqlConnection con = new SqlConnection("server = localhost; database= test;Integrated Security = true");

    con.Open();

    try
    {
        SqlCommand cmd = new SqlCommand("Update so set quantity where quantity = 0  And name Like  '%" + txt.Text + "%'", con);
    }
    catch
    {
        MessageBox.Show("There isn't any part left that you are looking for");
    }
    finally
    {
        con.Close();
    }
}

You don't seem to be setting quantity to anything in your update statement. — ManoDestra, May 09 '16 at 15:55
this code is very open to a [SQL injection attack](https://www.owasp.org/index.php/SQL_Injection). You should use parameters — Liam, May 09 '16 at 15:57
Using a catch to stop is also not good. Exceptions are expensive processes and shouldn't be used for flow control — Liam, May 09 '16 at 15:58
Quite simply, there are too many problems here to even begin to help... — Liam, May 09 '16 at 15:59

score 1 · Answer 1 · edited May 23 '17 at 10:30

There are 2 ways of doing it correctly:

with Stored Procedure (SP) that returns some indication if it was able to deduct stock item.
or by opening Transaction in Client (your application is Client from SQL Server point of view), checking what number of items available, deciding if you can deduct, and then either deducting followed by Commit Transaction, or not deducting with Rollback Transaction.

SP will require Transactions internally any way, as you would like to protect integrity of your data where concurrent access takes place.

In some workplaces SP is the only way of doing such things, as the other way of accessing SQL server is not allowed. In some other less strict workplaces you'll be allowed to do all of this in the client.

You can get some idea for second approach from the following question: How can I lock a table on read, using Entity Framework?

Or this one which is similar to what you use with SqlCommand: https://codereview.stackexchange.com/questions/59863/transaction-handling-for-multiple-sql-statements

Kramb · Answer 2 · 2016-05-09T16:25:12.293

Maybe something like this will help you get started?

private void btn_Click(object sender, RoutedEventArgs e)
{
    string connectionString = "server = localhost; database= test;Integrated Security = true";
    using(SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using(SqlCommand cmd = new SqlCommand("SELECT * FROM table WHERE name LIKE '%' + @name + '%'")
        {
            SqlDataReader reader = cmd.ExecuteReader();
            if(reader.HasRows)
            {
                while(reader.Read())
                {
                    if(Convert.ToInt32(reader["quantity").ToString()) > 0)
                    {
                        cmd.CommandText = "Update so set quantity where quantity = 0  And name Like '%' + @name + '%'";
                        cmd.Parameters.AddWithValue("@name", txt.Text);
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("There isn't any part left that you are looking for");
                    }
                }
                reader.Close();
            }
        }
    }
}

C#, WPF, SQL Server database

2 Answers2