3

I'm writing a program for viewing, archiving and restoring SQL-Data. The problem occurs only in restoring and while using target framework .NET 3.5. .NET 4.0 and higher seem to have solved the issue, but I have to write the program with 3.5 to guarantee the full compatibility with the computers where it should be used on.

The following method is called a few times (5-10) in order to get all names of a databases tables:

public List<string> GetAllTables(string sDatabase)
{
    List<string> result = new List<string>();

    try
    {
        using (SqlConnection con = new SqlConnection(dbConnection.conString + sDatabase))
        {
            con.Open();

            using (SqlCommand command = new SqlCommand("SELECT name FROM sys.Tables", con))
            {
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        result.Add(reader["name"].ToString());
                    }
                }
            }
        }

        return result;
    }
    catch (SqlException sqlEx)
    {
        string methodName = (new System.Diagnostics.StackTrace()).GetFrame(0).GetMethod().Name;
        string className = (new System.Diagnostics.StackTrace()).GetFrame(0).GetMethod().DeclaringType.Name;
        Debug.Print("Error in class {0} - method: {1}: {2}", className, methodName, sqlEx.Message);
        return result;
    }
}

The error occurs at var reader = command.ExecuteReader(). The weird thing is that it doesn't happen every time. Every second restoring is successful and doesn't throw the error.

The exception stack trace is like this:

Error in class DatabaseWorker - method: GetAllTables: Transmission-level error when sending the request to the server. (provider: TCP-Provider, error:0 - An existing connection was closed by the remote host.)
at System.Data.SqlClient.SqlConnection.onError(SQLException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
for sql_data archiving.fashion model.DatabaseWorker.GetAllTables(String sDatabase)
Stefan Wuebbe
  • 2,109
  • 5
  • 17
  • 28
aaronwe
  • 33
  • 4

1 Answers1

3

Essentially, the SQL server is closing the connection on you.

I've found that Transport level errors when taking to SQL is generally authentication related, especially when integrated security is involved. It could be something Windows Domain related (like how SQL assesses the identity of the connection). Sometimes I found these issues were resolved with a SQL Server restart, but those were for persistent connection issues (not what you have). If you are using integrated security, try a SQL account (username/password).

As to why it's a sometimes problem, that's not easy considering you want to run on .NET 3.5 Framework (likely an underlying issue has been fixed in later runtimes).

I can suggest a few approaches

  • Try make the connection using a SQL account
  • If you have to remain on .NET 3.5, implement a retry strategy in your code (say 3 times, delay 1 second, before failing).
  • Consider building the app with .NET Core as a self-contained release (--self-contained). This packages all dependencies, including the runtime. That is, you don't need to install the runtime when deploying the app.

For performing administrative work with SQL servers, there's a nuget package called Microsoft.SqlServer.SqlManagementObjects which includes rich functionality like defining backup sets etc. It's not available for .NET 3.5 but with a self-contained deployment in .NET Core, it might be a useful option for future work.

MoSlo
  • 535
  • 5
  • 11