C＃SqlCommand有多个语句 - 怎么样？ [英] C# SqlCommand with multiple statements - how to?

问题描述

大家好，我在C＃上有一段代码，以便在SQL Server上运行一些查询，最后返回哪些数据受到影响。

基本上它是一个带有OUTPUT子句的删除命令，用于报告它删除的项目。

查询：

 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 

C＃方法：

 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（例外）
 {
 if（transaction！= null）
 transaction.Rollback（transactionName）; 
 
 deletedItems.Clear（）; 
 
 throw; 
} 
最后
 {
尝试
 {
 if（connection！= null）
 connection.Close（）; 
} 
 catch {} 
} 
 
 return deletedItems; 
} 

这会引发错误" 已经有一个与此命令关联的打开DataReader，必须先关闭。 "。

我不能安装存储过程，因为这是一个产品数据库，并且有一些冲突不允许我这样做。

如何使用C＃中的查询文本实现此目的？

谢谢。

解决方案

那么为什么不在Trans.Commit（）中关闭阅读器和Dispose（）？

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

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.

解决方案

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

这篇关于C＃SqlCommand有多个语句 - 怎么样？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！