如何通过不提交当前会话事务将DataTable作为参数传递给存储过程？ [英] How to pass DataTable as a parameter to Stored Procedure by not commiting the current session transaction?

问题描述

我的代码如下所示：

  public void InsertSampleData（DataTable tempTable）
 {
 session .Transaction.Commit（）; 
 var connection = session.GetSessionImplementation（）。 
 using（var sqlConnection =（SqlConnection）connection）
 {
 using（var cmd = sqlConnection.CreateCommand（））
 {
 cmd.CommandType = CommandType.StoredProcedure ; 
 cmd.CommandText =dbo.insertData; 
 cmd.Parameters.Add（@ sItems，SqlDbType.Structured）; 
 var sqlParam = cmd.Parameters [@ Items]; 
 sqlParam.Direction = ParameterDirection.Input; 
 sqlParam.TypeName =[dbo]。[DataItemType]; 
 sqlParam.Value = tempTable; 
 cmd.ExecuteNonQuery（）; 
 
 
 
 
 
 
 $ b 
代码正常工作，但如果我不提交事务，它会抛出一个异常，如图所示
 
 
 System.InvalidOperationException：ExecuteNonQuery需要命令在分配连接时有一个事务到
命令是在一个未决的本地事务。该命令的事务属性
尚未初始化。
 
但我不希望事务被提交在完成整个操作之前。我怎样才能达到这个目标？    解决方案

问题是交易本应该使用已经提交并已经处理，因此要么实例化一个新的SqlConnection，要么获得一个新的会话，然后运行该查询作为新会话的一部分。

  public void InsertSampleData（DataTable tempTable）
 {
 session.Transaction.Commit（）; 
 
使用（var cmd = sqlConnection.CreateCommand（））使用（var sqlConnection = new SqlConnection（session.GetSessionImplementation（）
 .Connection.ConnectionString））
 ）
 {
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.CommandText =dbo.insertData; 
 cmd.Parameters.Add（@ sItems，SqlDbType.Structured）; 
 var sqlParam = cmd.Parameters [@ Items]; 
 sqlParam.Direction = ParameterDirection.Input; 
 sqlParam.TypeName =[dbo]。[DataItemType]; 
 sqlParam.Value = tempTable; 
 cmd.ExecuteNonQuery（）; 
 
 
 $ b code $ 
 $ b $或$ $ 
 
 
  public void InsertSampleData（DataTable tempTable）
 {
 session.Transaction.Commit（）; 
 var connection = session.SessionFactory.OpenSession（）。GetSessionImplementation（）。Connection; 
 using（var sqlConnection =（SqlConnection）connection）
 {
 using（var cmd = sqlConnection.CreateCommand（））
 {
 cmd.CommandType = CommandType.StoredProcedure ; 
 cmd.CommandText =dbo.insertData; 
 cmd.Parameters.Add（@ sItems，SqlDbType.Structured）; 
 var sqlParam = cmd.Parameters [@ Items]; 
 sqlParam.Direction = ParameterDirection.Input; 
 sqlParam.TypeName =[dbo]。[DataItemType]; 
 sqlParam.Value = tempTable; 
 cmd.ExecuteNonQuery（）; 
 
 
 
 
 
 
 
 
为什么使用SQLConnection你为什么不用Nhibernate Session来执行查询呢？你可以使用Nhibernate Session来完成你正在执行的任何操作。
 
 
 pre $  session.CreateSQLQuery（< your sql>）
 .SetParameters（）//和其他各种API来设置参数
 .ExecuteUpdate（）; 
  
 
My code looks like this:
public void InsertSampleData(DataTable tempTable)
    {
       session.Transaction.Commit();
        var connection = session.GetSessionImplementation().Connection;
        using (var sqlConnection = (SqlConnection)connection)
        {
            using (var cmd = sqlConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.insertData";
                cmd.Parameters.Add("@sItems", SqlDbType.Structured);
                var sqlParam = cmd.Parameters["@Items"];
                sqlParam.Direction = ParameterDirection.Input;
                sqlParam.TypeName = "[dbo].[DataItemType]";
                sqlParam.Value = tempTable;
                cmd.ExecuteNonQuery();
            }
        }
    }
The code works fine but if I do not commit the transaction it throws an exception as shown

  
System.InvalidOperationException: ExecuteNonQuery requires the command to have a transaction when the connection assigned to the
  command is in a pending local transaction.  The Transaction property
  of the command has not been initialized.
But I don't want the transaction to be commited yet before completing the whole operation. How can I achieve this??
解决方案
the issue is that the transaction was supposed to use has been committed and already has been disposed, thus either instantiate a new SqlConnection or get a new session and then run the query as part of the new session.
public void InsertSampleData(DataTable tempTable)
{
    session.Transaction.Commit();

    using (var sqlConnection = new SqlConnection(session.GetSessionImplementation()
                                       .Connection.ConnectionString))
    {
        using (var cmd = sqlConnection.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "dbo.insertData";
            cmd.Parameters.Add("@sItems", SqlDbType.Structured);
            var sqlParam = cmd.Parameters["@Items"];
            sqlParam.Direction = ParameterDirection.Input;
            sqlParam.TypeName = "[dbo].[DataItemType]";
            sqlParam.Value = tempTable;
            cmd.ExecuteNonQuery();
        }
    }
}
or
public void InsertSampleData(DataTable tempTable)
    {
       session.Transaction.Commit();
        var connection = session.SessionFactory.OpenSession().GetSessionImplementation().Connection;
        using (var sqlConnection = (SqlConnection)connection)
        {
            using (var cmd = sqlConnection.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.insertData";
                cmd.Parameters.Add("@sItems", SqlDbType.Structured);
                var sqlParam = cmd.Parameters["@Items"];
                sqlParam.Direction = ParameterDirection.Input;
                sqlParam.TypeName = "[dbo].[DataItemType]";
                sqlParam.Value = tempTable;
                cmd.ExecuteNonQuery();
            }
        }
    }
BTW, why are using SQLConnection why arent you exexurting the query using the Nhibernate Session, you could do the everything that you are doing right now using the Nhibernate Session.
session.CreateSQLQuery(<your sql>)
.SetParameters()   //and various other API to set your parameters
.ExecuteUpdate();


                        
这篇关于如何通过不提交当前会话事务将DataTable作为参数传递给存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！