C# SqlCommand with multiple statements - how to?

[Pakojones]

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.

Continue reading...