0 Comments

Lazy productive I as a developer am, I did not want to write a stored procedure. So I ended up with a lot of SQL statements which needed to be combined and executed only if the previous one succeeds.

However there was a timeout during the execution. It was strange because when I copy pasted the SQL statement in SMSS it worked perfectly and fast too. My mistake was to use a new SqlCommand object which was not part of the transaction. I needed to re-use the same sqlcommand. Here is some working code:

using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConString"].ToString()))
{
    con.Open();

    SqlCommand cmd = con.CreateCommand();
    SqlTransaction transaction = con.BeginTransaction();

    cmd.Connection = con;
    cmd.Transaction = transaction;

    try{
    
        cmd.CommandText = "SELECT productcode FROM products WHERE productid = " + productid;
        string code = cmd.ExecuteScalar().ToString();
    
        cmd.CommandText = "SELECT ProductID, ProductWeight, ProductEuroPrice, OrderID FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        SqlDataReader sdr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(sdr);
    
        cmd.CommandText = "DELETE FROM ORDERPICKINGPRODUCTS WHERE orderid = " + orderid + " AND productid = " + productid;
        cmd.ExecuteNonQuery();
        transaction.Commit();
    }
    catch(Exception e)
    {
        transaction.Rollback();
    }
}
I know that it is better to use parameters https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters%28v=vs.110%29.aspx

And the AddWithValue method… But this is just pseudo code and not in production. So don’t worry!

This is just a small sample to help people with a strange timeout which you can encounter if you use an sqlcommand outside the transaction.

 

Good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn