Delete from database only works when debugging

Question

I am reading rows from a database which contains BLOB's. I take each of these blobs and saves them as a file to my disk. Afterwards I want to delete the rows I have just read. Now the problem: When I debug, all works like a charm. When I run without having a debug breakpoint, it doesn't delete anything! And it doesn't come up with an error.

I use C# and MS-SQL server.

Here is my code:

class Program
{
    static void Main(string[] args)
    {

        if (args[0] == "/?" || args.Length != 1)
        {
            Console.WriteLine("BlobReader");
            Console.WriteLine("Will read blob from database and write is as a file to destination specified in database.");
            Console.WriteLine();
            Console.WriteLine("BlobReader <AppName>");
            Console.WriteLine();
            Console.WriteLine("<AppName>: Application name which identifies which files to extract and save.");

            EndProgram();
        }

        string now = DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss.fff");

        Console.WriteLine("writing files to disk");
        bool success = SqlBlob2File(args[0], now);
        if (success)
        {
            Console.WriteLine("Deleting db");
            DeleteRows(args[0], now);
            Console.WriteLine("Db deleted");
        }

        EndProgram();
    }

    static void EndProgram()
    {
        Console.WriteLine();
        Console.WriteLine("Press any key to end.");
        Console.ReadLine();
    }

    static private void DeleteRows(string app, string now)
    {
        try
        {
            string connectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
            SqlConnection connection = new SqlConnection(connectionString);
            string sql = string.Format("DELETE FROM Blobs WHERE Application = '{0}' AND CreatedDate < '{1}'", app,
                                       now);
            SqlCommand cmd = new SqlCommand(sql, connection);
            connection.Open();
            cmd.BeginExecuteNonQuery();
            connection.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }

    }

    static private bool SqlBlob2File(string app, string now)
    {
        bool success;


        string connectionString = ConfigurationManager.ConnectionStrings["dbConn"].ToString();
        SqlConnection connection = new SqlConnection(connectionString);
        try
        {
            int blobCol = 0; // the column # of the BLOB field

            string sql =
                string.Format(
                    "SELECT Blob, Drive, Folder, FileName FROM Blobs WHERE Application='{0}' AND CreatedDate < '{1}'",
                    app, now);
            SqlCommand cmd = new SqlCommand(sql, connection);
            connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                string destFilePath = string.Format("{0}{1}{2}", dr["Drive"], dr["Folder"], dr["FileName"]);

                Byte[] b = new Byte[(dr.GetBytes(blobCol, 0, null, 0, int.MaxValue))];
                dr.GetBytes(blobCol, 0, b, 0, b.Length);

                System.IO.FileStream fs = new System.IO.FileStream(destFilePath, System.IO.FileMode.Create,
                                                                   System.IO.FileAccess.Write);

                fs.Write(b, 0, b.Length);
                fs.Close();
                Console.WriteLine("Blob written to file successfully");
            }
            dr.Close();


            success = true;
        }
        catch (SqlException ex)
        {
            success = false;
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connection.Close();
        }

        return success;
    }

}

If I set up a breakpoint in method DeleteRows it does delete from the database. If I don't, nothing is deleted.

Answer 1

If you use this cmd.BeginExecuteNonQuery(),you have use EndExecuteNonquery().

Otherwise,it wont be deleted and it may create memory leak problem.

Best way is to use cmd.ExecuteNonQuery();

Answer 2

Does this also happen when you remove the Connection.Close? It might be that since you are removing async that the connection is closed before the operation can complete unlike when you have a breakpoint and the code execution waits.

Also instead of using SqlConnection the way you do, you might want to use:

using (sqlConnection con = new SqlConnection()) 
{
// your code
 }

This way the connection is automatically closed when it is out of scope or when something goes wrong.

I agree with MahaSwetha that cmd.ExecuteNonQuery() would make your life easier. Also cmd.ExecuteNonQuery() returns an int telling you how much rows have changed. Can come in handy sometimes.