A transport-level error has occurred (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

azure-sql-database c# enterprise-library entity-framework-6

Question

I have read both these SO questions and the MS Docs:

And have the same error. I did not have any of these in my ConnectionString: ConnectRetryCount, ConnectRetryInterval or Connection Timeout.

This is a method of my DB class:

public DataTable ExecuteSqlCommand(SqlCommand com)
{
    var retryStrategy = new Incremental(5, TimeSpan.FromSeconds(1), TimeSpan.FromSeconds(2));
    var retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DataContext"].ToString());

    com.Connection = con;
    SqlDataAdapter da = new SqlDataAdapter(com);
    DataTable dt = new DataTable();
    try
    {
        retryPolicy.ExecuteAction(() =>
        {
            con.Open();
            da.Fill(dt);
        });
    }
    catch (Exception e)
    {
        var telemetry = new TelemetryClient(); // app insights (azure)
        telemetry.TrackException(e);
    }
    finally
    {
        con.Close();
    }
    return dt;
}

So what would be better? Remove the retry stuff from my code and use the attributes in my connection string? Let the framework do the work? Or is my current retry code sufficient? I have the feeling that the enterprise lib and retry stuff is obsolete, but cannot find a good source to confirm my thoughts.

I am using 4.7 and also have EF 6.2, but most queries are just SqlCommands using the code from above.

enter image description here

1
1
12/5/2017 9:57:40 AM

Popular Answer

Make sure the user/login you use on your connection string has access to the master database on your Azure SQL Database server also, not only to the user database. That will provide faster connections and timeouts may disappear.

Using SQL Azure Execution Estrategy may help you with this issue.

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy()); 
    } 
}

public class MyConfiguration : DbConfiguration 
{ 
    public MyConfiguration() 
    { 
        SetExecutionStrategy( 
            "System.Data.SqlClient", 
            () => new SqlAzureExecutionStrategy(1, TimeSpan.FromSeconds(30))); 
    } 
}

For more information about SQL Azure Execution Strategy please visit this URL.

0
12/5/2017 4:04:17 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow