用于 Connection.Query 的 Trans.commit-解网

问：

我在下面有一个函数，它将通过存储过程将值插入到SQL DB中。我正在尝试实现trans.commit以使其更好，但是使用sp_insertTransaction2的存储过程在第一次插入时一直遇到错误：

当分配给命令的连接位于挂起的本地事务中时，ExecuteReader 要求命令具有事务。命令的 Transaction 属性尚未初始化

我在网上看到了很多教程，但是使用SQLCommand来初始化trans而不是使用连接。像我的代码一样查询。需要帮助来解决问题。

public bool insertTransactions(string receiver, string deliverer, string module, DateTime date, bool isChkout, int timeLapse, List<Lot> Lots)
{

    using (var connection = new SqlConnection(ConnString))
    {
        connection.Open();
        var trans = connection.BeginTransaction();
        try
        {
            var data = connection.Query<TransactionsModel>("[sp_insertTransaction2]",
            new
            {
                receiver,
                deliverer,
                module,
                date,
                isChkout,
                timeLapse
            },
            commandTimeout: 120, commandType: CommandType.StoredProcedure);

            **trans.Commit();**

            foreach (var lot in Lots)
            {
                var data2 = connection.Query<TransactionsModel>("[sp_insertLot]",
                 new
                {
                    lot.LotVPO,
                    lot.Bin,
                    lot.Location,
                    lot.Qty,
                },
                 commandTimeout: 120, commandType: CommandType.StoredProcedure);
            trans.Commit();
       }
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine("insertTransactions API error : " + ex.ToString());
            trans.Rollback();
            return false;
        }
    }

C# SQL Dapper

public bool insertTransactions(string receiver, string deliverer, string module, DateTime date, bool isChkout, int timeLapse, List<Lot> Lots)
{
    try
        {
            using (var connection = new SqlConnection(ConnString))
            {
                connection.Open();
                using (var scope = new TransactionScope()) // use scope instead, its better
                {   //use Execute when you insert/update/delete, Query is only for read
                    var data = connection.Execute<TransactionsModel>("[sp_insertTransaction2]",
                    new {
                    receiver,
                    deliverer,
                    module,
                    date,
                    isChkout,
                    timeLapse
                    }, commandTimeout: 120, commandType: CommandType.StoredProcedure);

                    foreach (var lot in Lots)
                    {
                        //use Execute when you insert/update/delete, Query is only for read
                        var data2 = connection.Execute<TransactionsModel>("[sp_insertLot]",
                        new
                        {
                        lot.LotVPO,
                        lot.Bin,
                        lot.Location,
                        lot.Qty,
                        }, commandTimeout: 120, commandType: CommandType.StoredProcedure);
                
                    }
                    scope.Complete(); //Or it will automatically rollback, call it only once! 
                }
                
            }   
        }
        catch (Exception ex)
        {
            Console.WriteLine("insertTransactions API error : " + ex.ToString());
            trans.Rollback();
            return false;
        }
}

上一个：cmd.ExecuteNonQuery（）;不工作 - C#（VS Code Windows 窗体）和 MS-Access 数据库

下一个：如何突出显示重复的列？

用于 Connection.Query 的 Trans.commit

Trans.commit for Connection.Query

评论

评论