none
C# SqlCommand with multiple statements - how to? RRS feed

  • Question

  • Hi all, I have a piece of code on C# in order to run a few queries on SQL Server and at the end return which data was affected.

    Basically it is a delete command with OUTPUT clause in order to report which items it deleted.

    The query:

    DECLARE @DeletedItems TABLE (ItemId NVARCHAR(128))
            
    DELETE FROM tb_Items
    OUTPUT DELETED.ItemId
    INTO @DeletedItems
    WHERE ItemId = '{0}'
            
    DELETE FROM tb_Main WHERE ItemId = 'stuff'
    	    
    SELECT ItemId FROM @DeletedItems

    The C# method:

    public List<string> DeleteItemsFromDatabase(string id)
            {
                List<string> deletedItems = new List<string>();
    
                string deletePattern = @"DECLARE @DeletedItems TABLE (ItemId NVARCHAR(128))
            
    		DELETE FROM tb_Items
            OUTPUT DELETED.ItemId
            INTO @DeletedItems
            WHERE ItemId = '{0}'
            
    		DELETE FROM tb_Main WHERE ItemId = '{0}'
    	    
            SELECT ItemId FROM @DeletedItems";
    
                string query = string.Format(deletePattern, id);
    
                string transactionName = "TRN";
    
                SqlConnection connection = null;
                SqlTransaction transaction = null;
    
                try
                {
                    connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyCS"].ConnectionString);
    
                    connection.Open();
    
                    transaction = connection.BeginTransaction(transactionName);
                    SqlCommand command = new SqlCommand(query, connection, transaction);
                    command.CommandType = CommandType.Text;
    
                    SqlDataReader reader = command.ExecuteReader();
    
                    if (reader.HasRows)
                        while (reader.Read())
                        {
                            string itemId = reader["ItemId"].ToString();
    
                            if (!deletedItems.Contains(itemId))
                                deletedItems.Add(itemId);
                        }
    
                    transaction.Commit();
                }
                catch (Exception)
                {
                    if (transaction != null)
                        transaction.Rollback(transactionName);
    
                    deletedItems.Clear();
    
                    throw;
                }
                finally
                {
                    try
                    {
                        if (connection != null)
                            connection.Close();
                    }
                    catch { }
                }
    
                return deletedItems;
            }


    This throws the error "There is already an open DataReader associated with this Command which must be closed first.".

    I can't install a stored procedure since this is a product database and there are a few conflicts that does not allow me to do that.

    How can I achieve this using query text in C#?

    Thank you.

    Wednesday, July 11, 2018 4:56 PM

All replies

  • So why didn't you close the reader and Dispose() of it at the Trans.Commit()?

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

    Wednesday, July 11, 2018 5:57 PM
  • "There is already an open DataReader associated with this Command which must be closed first.".

    When you open a DataReader on a SqlConnection, the DataReader maintains the connection "busy" and nothing else can be sent to the server over that connection until the DataReader is closed (unless you are using MARS, but that is a different topic).

    This means that you need to remember to Close the DataReader once you are done using it, i.e., after exitng the "while" loop. Otherwise, the "commit" cannot be sent through the connection, and you get the error that you are seeing.

    In order to guarantee that the datareader is closed, it is recommended that you enclose it in a "using" statement. Upon exiting the "using" block, the Dispose method will be implicitly called, and the Dispose calls Close internally.

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            string itemId = reader["ItemId"].ToString();
    
            if (!deletedItems.Contains(itemId))
                deletedItems.Add(itemId);
        }
    }
    I removed the superfluous "if (reader.Hasrows)" -- the while loop will be skipped if there are no rows, no need to use the "if").



    Wednesday, July 11, 2018 9:30 PM
    Moderator
  • Hello,

    Is there are reason for not simply calling the DELETE statement and execute via ExecuteNonQuery which returns a int with the results of rows affected? You can run this in a transaction if the result is not what was expected.

    On a side note, having two SELECT statements (not a DELETE and SELECT with a semicolon splitting the two statements) you could run them with ExecuteReader then get results then once done use reader.NextResults but that is not the case here.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 11, 2018 10:44 PM
    Moderator