CODEWITHSUNDEEP
azureazure-sql-database
Jérôme Tarere
Jérôme Tarere

Reputation: 427

Transient Fault Handling - Does an opened ReliableSqlConnection automatically takes care of SQL commands retry under its management?

Using the Transient Fault Handling Application Block with SQL Azure. In this sample is a specific retry logic for oCmd.ExecuteReader() is mandatory or does the ReliableSqlConnection takes care of it ?

    Using oCmd As New SqlCommand()
        strSQL = "SELECT xxxxxx.* FROM xxxxxx"
        oCmd.CommandText = strSQL
        Using oConn As New ReliableSqlConnection(Cs, retryPolicy)
            oConn.Open()
            oCmd.Connection = oConn
            Using oDR As SqlDataReader = oCmd.ExecuteReader()
                If oDR.Read() Then
                    sb1.Append(oDR("xxxxxx").ToString)
                End If
            End Using
        End Using
    End Using

* UPDATE *

From response below, If I create the SqlCommand object from the context of the ReliableSqlConnect object i can expect to have the retry behaviour extended to the command also, as stated in this page http://geekswithblogs.net/ScottKlein/archive/2012/01/27/understanding-sql-azure-throttling-and-implementing-retry-logic.aspx

"This next code example below illustrates creating a retry policy using the RetryPolicy class, specifying the retry attempts and the fixed time between retries. This policy is then applied to the ReliablSqlConnection as both a policy to the connection as well as the policy to the command."

RetryPolicy myretrypolicy = new RetryPolicy<SqlAzureTransientErrorDetectionStrategy>(3, TimeSpan.FromSeconds(30));

using (ReliableSqlConnection cnn = new ReliableSqlConnection(connString, myretrypolicy, myretrypolicy))
{
    try
    {
    cnn.Open();

    using (var cmd = cnn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM HumanResources.Employee";

        using (var rdr = cnn.ExecuteCommand<IDataReader>(cmd))
        {
        //
        }
    }
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message.ToString());
    }
}

Upvotes: 0

Views: 1100

Answers (1)

QFDev
QFDev

Reputation: 9008

ReliableSqlConnection applies retry logic only to the process of establishing a connection. Try using something like this:

Using oDR As SqlDataReader = oCmd.ExecuteReaderWithRetry(retryPolicy)
          If oDR.Read() Then
                    sb1.Append(oDR("xxxxxx").ToString)
           End If
End Using

For further information on usage your can refer to MSDN or there are some additional examples here.

Upvotes: 0

Related Questions