Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Commerce forum / Using SqlCommand inste...
Commerce forum
Answered

Using SqlCommand instead of using DatabaseContext in CRT

Posted on by 103

Hi am facing error when trying to use transaction with Databasecontext in CRT. Is it okay to use sqlcommand and sqlconnection in our custom processes

Categories:
  • Verified answer
    RE: Using SqlCommand instead of using DatabaseContext in CRT

    We are investigating options to support DatabaseContext.BeginTransactionAsync() without persisted connection mode.

    In a meanwhile try using TransactionScope class with TransactionScopeAsyncFlowOption enabled.

  • Suhail Ismail Profile Picture
    Suhail Ismail 103 on at
    RE: Using SqlCommand instead of using DatabaseContext in CRT

    Hi Sam,

    Basically in the DatabaseContext there is no member called database or anything similar. I have used similar code to the link you shared in entity framework. In CRT it seems to be a different case.

  • Suggested answer
    Sam.pruett Profile Picture
    Sam.pruett 10 on at
    RE: Using SqlCommand instead of using DatabaseContext in CRT

    Hey Suhail, this is because of the BeginTransactionAsync() method is opening a transaction against the same db context. Here are some ideas on how to get around that.

    stackoverflow.com/.../using-begintransactionasync-in-different-functions-that-also-implement-it

  • Suhail Ismail Profile Picture
    Suhail Ismail 103 on at
    RE: Using SqlCommand instead of using DatabaseContext in CRT

    Hi,

    So as first step i am trying to create Database transaction as shown in the below code

    try {
                    using (var dbContext = new DatabaseContext(context))
                    {
                        using (var dbTransaction = await dbContext.BeginTransactionAsync())
                        {
    
                            var sqlQuery = new SqlQuery
                            {
                                QueryString = sql
                            };
                           //setting parameters here
                            await dbContext.ExecuteNonQueryAsync(sqlQuery);
                            dbTransaction.Commit();
    
    
                        }
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }

    the above code throws error "This API is not supported in non-persisted connection mode.".

    Now i have changed the code as below to allow persisted connection.

    try {
                    using (var dbContext = new DatabaseContext(context, DatabaseConnectionMode.IsPersisted))
                    {
                        using (var dbTransaction = await dbContext.BeginTransactionAsync())
                        {
    
                            var sqlQuery = new SqlQuery
                            {
                                QueryString = sql
                            };
                            
                            //parameters
                            
                            await dbContext.ExecuteNonQueryAsync(sqlQuery);
                            dbTransaction.Commit();
    
    
                        }
                    }
                }
                catch(Exception ex)
                {
                    throw ex;
                }

    with the above code it throws error "BeginExecuteReader 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."

  • ToddB Profile Picture
    ToddB on at
    RE: Using SqlCommand instead of using DatabaseContext in CRT

    Hi Suhail,

    I looked at all of our samples and it looks like Databasecontext is the way to go.

    If you are unable to determine why things are not working using Databasecontext you could try posting in this forum for assistance.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans