Executing SqlCommand

[wocar]

Hello Halil,

I have an Issue, I have a method that executes a SqlCommand in a remote server (different connection string than the DbContext)

 using (var con = new SqlConnection(ConnectionString))
                {
                    con.Open();

                    using (var command = new SqlCommand("select * from ... ", con))
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                return reader.GetString(0);
                            }
                        }
                    }
                }

But it throws an exception when openning the connection...

System.Data.SqlClient.SqlException: There is already a datareader associated with this command which must be closed first.

I already tried adding to both connection strings (sqlcommand & dbContext)

MultipleActiveResultSets=true

Another thing to add is that in the unit test the code does work, but when I run the server, it doesnt.

Thank you for your valuable time and answering this question,
William Obando

[hikalkan]

That maybe related to transaction. Can you disable the transaction (probably by adding UnitOfWork(false) to the calling app service) and try again. You can also try to inject IUnitOfWorkManager and run all your codes by suppressing the transaction like that:

using (var uow = uowManager.Begin(TransactionScopeOption.Suppress))
{
    using (var con = new SqlConnection(ConnectionString))
    {
        con.Open();

        using (var command = new SqlCommand("select * from ... ", con))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    return reader.GetString(0);
                }
            }
        }
    }

    uow2.Complete();
}

You need to update v0.6.6 in order to use TransactionScopeOption.

[wocar]

Done, it worked. Is this just a work arround or the final fix? Maybe we should document this, just in case someone wants to execute sqlcommands/stored procedures within their application.

Thanks for your extremely fast answer. .

[natiki]

Hi,

I tried disabling the transaction management using UnitOfWork(false) and keeping my code as per #584 and the error still persisted.

If I spin up a brand new SqlConnection then my code works as well. I would prefer that this was fixed in ABP as I have a large number of stored procedure calls (95% of my data access are SP's). As such this bug means that ABP is largely a hindrance to me as I would need to do connection management for all these calls.

Furthermore I think the use of:

using (var uow = uowManager.Begin(TransactionScopeOption.Suppress))
{
}

is redundant as TransactionScopeOption.Suppress means you don't want to take part in a transaction which seems counter intuitive. So for instance when I implemented my code I ended up with:

public static UspGetBookingSummaryByCruiseCabinCodeReturnDto UspGetBookingSummaryByCruiseCabinCodeEx(DbContext aDbContext, string aCruiseCode, string aCabinCode, string aCabinRate,
                                                                                                        out int aProcResult)
{
    UspGetBookingSummaryByCruiseCabinCodeReturnDto result = new UspGetBookingSummaryByCruiseCabinCodeReturnDto();
    SqlParameter cruiseCodeParam = new SqlParameter { ParameterName = "@CruiseCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCruiseCode, Size = 30 };
    SqlParameter cabinCodeParam = new SqlParameter { ParameterName = "@CabinCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCabinCode, Size = 10 };
    SqlParameter cabinRateParam = new SqlParameter { ParameterName = "@CabinRate", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCabinRate, Size = 10 };
    SqlParameter procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

    //Testing without ABP - So make a new connection
    using (var con = new SqlConnection("ConnString"))
    {
        con.Open();

        using (var dbCommand = con.CreateCommand())
        {
            dbCommand.Parameters.Add(cruiseCodeParam);
            dbCommand.Parameters.Add(cabinCodeParam);
            dbCommand.Parameters.Add(cabinRateParam);
            dbCommand.Parameters.Add(procResultParam);
            dbCommand.CommandText = "EXEC @procResult = [dbo].[usp_GetBookingSummaryByCruiseCabinCode] @CruiseCode, @CabinCode, @CabinRate";
            //dbCommand.Transaction = aDbContext.Database.CurrentTransaction.UnderlyingTransaction;
            DbDataReader reader = dbCommand.ExecuteReader();
            try
            {
                aProcResult = 0;

                //Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
                ObjectContext objectContext = ((IObjectContextAdapter)aDbContext).ObjectContext;

                result.CruiseSummary = objectContext.Translate<UspGetBookingSummaryByCruiseCabinCodeDetailReturnDto>(reader).ToList();

                reader.NextResult();
                result.SeatingRequest = objectContext.Translate<UspGetBookingSummarySeatingReturnDto>(reader).ToList();

                reader.NextResult();
                result.TableSize = objectContext.Translate<UspGetBookingSummaryTableSizeReturnDto>(reader).ToList();

                reader.NextResult();
                result.SupplierPromos = objectContext.Translate<UspGetCruiseSupplierPromosReturnDto>(reader).ToList();

            }
            catch (Exception)
            {
                aProcResult = -1;

                throw;
            }
        }
    }

    return result;
}

This left my AppService unaltered and if the exception was raised things would still be rolled back.

So.... Can we get an ABP fix? Having to reimplement my code with its own connection for over a hundred methods is not something I am looking forward to ;-)

[wocar]

For me

using (var uow = uowManager.Begin(TransactionScopeOption.Suppress))
{
}
Worked when executing the SqlCommand... does it still throw the same
exception?

El jue., ago. 27, 2015 7:38 PM, Donovan Edye notifications@github.com
escribió:

Hi,

I tried disabling the transaction management using UnitOfWork(false) and
keeping my code as per #584
#584 and
the error still persisted.

If I spin up a brand new SqlConnection then my code works as well. I would
prefer that this was fixed in ABP as I have a large number of stored
procedure calls (95% of my data access are SP's). As such this bug means
that ABP is largely a hindrance to me as I would need to do connection
management for all these calls.

Furthermore I think the use of:

using (var uow = uowManager.Begin(TransactionScopeOption.Suppress))
{
}

is redundant as TransactionScopeOption.Suppress means you don't want to
take part in a transaction which seems counter intuitive. So for instance
when I implemented my code I ended up with:

public static UspGetBookingSummaryByCruiseCabinCodeReturnDto UspGetBookingSummaryByCruiseCabinCodeEx(DbContext aDbContext, string aCruiseCode, string aCabinCode, string aCabinRate,
out int aProcResult)
{
UspGetBookingSummaryByCruiseCabinCodeReturnDto result = new UspGetBookingSummaryByCruiseCabinCodeReturnDto();
SqlParameter cruiseCodeParam = new SqlParameter { ParameterName = "@CruiseCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCruiseCode, Size = 30 };
SqlParameter cabinCodeParam = new SqlParameter { ParameterName = "@cabincode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCabinCode, Size = 10 };
SqlParameter cabinRateParam = new SqlParameter { ParameterName = "@CabinRate", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, Value = aCabinRate, Size = 10 };
SqlParameter procResultParam = new SqlParameter { ParameterName = "@procResult", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output };

//Testing without ABP - So make a new connection
using (var con = new SqlConnection("ConnString"))
{
    con.Open();

    using (var dbCommand = con.CreateCommand())
    {
        dbCommand.Parameters.Add(cruiseCodeParam);
        dbCommand.Parameters.Add(cabinCodeParam);
        dbCommand.Parameters.Add(cabinRateParam);
        dbCommand.Parameters.Add(procResultParam);
        dbCommand.CommandText = "EXEC @procResult = [dbo].[usp_GetBookingSummaryByCruiseCabinCode] @CruiseCode, @CabinCode, @CabinRate";
        //dbCommand.Transaction = aDbContext.Database.CurrentTransaction.UnderlyingTransaction;
        DbDataReader reader = dbCommand.ExecuteReader();
        try
        {
            aProcResult = 0;

            //Drop down to the wrapped `ObjectContext` to get access to the `Translate` method
            ObjectContext objectContext = ((IObjectContextAdapter)aDbContext).ObjectContext;

            result.CruiseSummary = objectContext.Translate<UspGetBookingSummaryByCruiseCabinCodeDetailReturnDto>(reader).ToList();

            reader.NextResult();
            result.SeatingRequest = objectContext.Translate<UspGetBookingSummarySeatingReturnDto>(reader).ToList();

            reader.NextResult();
            result.TableSize = objectContext.Translate<UspGetBookingSummaryTableSizeReturnDto>(reader).ToList();

            reader.NextResult();
            result.SupplierPromos = objectContext.Translate<UspGetCruiseSupplierPromosReturnDto>(reader).ToList();

        }
        catch (Exception)
        {
            aProcResult = -1;

            throw;
        }
    }
}

return result;

}

This left my AppService unaltered and if the exception was raised things
would still be rolled back.

So.... Can we get an ABP fix? Having to reimplement my code with its own
connection for over a hundred methods is not something I am looking forward
to ;-)

—
Reply to this email directly or view it on GitHub
#583 (comment)
.

[hikalkan]

Hi @natiki
I don't think this ia a bug of ABP. It just uses standard TransactionScope (System.Transactions) and I think there is a problem on creating a different connection & call SP inside that TransactionScope. There is no special thing of ABP. Also, you can search it on the web because I think that's not unique to ABP.
So, what a solution could be...? For now, I don't know, I should research and try it.

[natiki]

@wocar no it did not throw an exception.... It just seemed pointless to pass in a UOW and then suppress the transaction. Unless I am missing something really obvious here?

[natiki]

@hikalkan The "bug" I am referring to is that if I use a regular DBContext without the notion of ABP my code works without issue. So somewhere in the mix with ABP "something has changed". That's why I think it is a "bug". Or have you seen some references on the web that indicate otherwise?

[tiandao-dongguan]

@natiki the same problem to me,in my application ,there are many oracle stored procedure and multiple database ,in order to use abp and ensure the consistency of transaction,i use msdtc and OracleMTSRecoveryService.i want to know How do you solve the problem? and How do you use stored procedure with repository pattern like abp.thank you....

[natiki]

@tiandao-dongguan Sorry I missed your post.... Do you still need some code?