none
SQL Connection Exception errors RRS feed

  • Question

  • Hallo

    Hope all are well.

    We running a program that continually pole a record in a database for enable flag. We creating numerous instances for the db connections class. Ever so often we get the error below from the exception handler, is there a way to resolve this or a better way to handle the Connection?

    namespace WCS_Shared.Databases.SQL
    {
        public class SqlDbConnect
        {
            private SqlConnection _con;
            public SqlCommand Cmd;
            private SqlDataAdapter _da;
            private DataTable _dt;
    
            public SqlDbConnect()
            {
                _con = new SqlConnection(WCS_Shared.Databases.SQL.ConnectionString.GetConnectionString());
    
                try
                {
                    if (_con.State == ConnectionState.Open)
                    {
                        _con.Close();                   
                    }
                    _con.Open();         //This is line 25...   
                }
                catch (Exception e)
                {
                    LogFiles.ExceptionLogger.WriteException(e);
                }
            }
    
            public void SqlQuery(string queryText)
            {
                Cmd = new SqlCommand(queryText, _con);
            }
    
           public DataTable QueryEx()
            {
                _da = new SqlDataAdapter(Cmd);
                _dt = new DataTable();
    
                try
                {
                    _da.Fill(_dt);
                }
                catch (Exception e)
                {
                    LogFiles.ExceptionLogger.WriteException(e);
                }           
                return _dt;
            }
    
            public void NonQueryEx()
            {
                try
                {
                    Cmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    LogFiles.ExceptionLogger.WriteException(e);
                }
                
            }
        }
    }


    labjac

    Wednesday, July 8, 2020 10:44 AM

Answers

  • You can do a retry as per

    https://stackoverflow.com/questions/24041062/know-when-to-retry-or-fail-when-calling-sql-server-from-c

    using System.Data.SqlClient;
    using System.Linq;
    
    namespace WindowsFormsApp1
    {
        public class SqlDbConnect
        {
            private static readonly int[] RetriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 };
            private static SqlConnection _connection;
            private const int MaximumNumberOfRetries = 4;
    
            private static bool CanRetry(SqlError error)
            {
                // Use this switch if you want to handle only well-known errors,
                // remove it if you want to always retry. A "blacklist" approach may
                // also work: return false when you're sure you can't recover from one
                // error and rely on Class for anything else.
                switch (error.Number)
                {
                    // Handle well-known error codes, 
                }
    
                // Handle unknown errors with severity 21 or less. 22 or more
                // indicates a serious error that need to be manually fixed.
                // 24 indicates media errors. They're serious errors (that should
                // be also notified) but we may retry...
                return RetriableClasses.Contains(error.Class); // LINQ...
            }
            public static void CreateConnection()
            {
                bool rebuildConnection = true; // First try connection must be open
    
                for (int index = 0; index < MaximumNumberOfRetries; ++index)
                {
                    try
                    {
                        // (Re)Create connection to SQL Server
                        if (rebuildConnection)
                        {
                            if (_connection != null)
                                _connection.Dispose();
    
                            // Create connection and open it...
                        }
    
                        // Perform your task
    
                        // No exceptions, task has been completed
                        break;
                    }
                    catch (SqlException e)
                    {
                        if (e.Errors.Cast<SqlError>().All(x => CanRetry(x)))
                        {
                            // What to do? Handle that here, also checking Number property.
                            // For Class < 20 you may simply Thread.Sleep(DelayOnError);
    
                            rebuildConnection = e.Errors
                                .Cast<SqlError>()
                                .Any(x => x.Class >= 20);
    
                            continue;
                        }
    
                        throw;
                    }
                }
            }
        }
    }
    
    You could use connection.OpenAsync in a task also, another option is to not use one single connection but instead use a new local connection.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by labjac Saturday, July 11, 2020 9:10 PM
    Wednesday, July 8, 2020 12:00 PM
    Moderator
  • Hi labjac,
    First, the main reason for the error is that you just opened the connection but did not release it.
    So you can release all the resources including the connection string by calling the dispose method.
    And using statement will get translated into try/finally block after compilation and dispose method will be called in the finally block.

    using(SqlConnection connection = new SqlConnection())
    {
          connection.Open(); 
      //Perform your operations here
    
    }

    To avoid useless database connections, I suggest you can open db  before performing certain operations instead of opening the database in the constructor.
    Here is a simple code you can refer to.

     private static string connStr = "connectString";
            public static int NonQueryEx(string sql)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
    
                    }
                }
            }

    Best Regards
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 9, 2020 6:20 AM

All replies

  • You can do a retry as per

    https://stackoverflow.com/questions/24041062/know-when-to-retry-or-fail-when-calling-sql-server-from-c

    using System.Data.SqlClient;
    using System.Linq;
    
    namespace WindowsFormsApp1
    {
        public class SqlDbConnect
        {
            private static readonly int[] RetriableClasses = { 13, 16, 17, 18, 19, 20, 21, 22, 24 };
            private static SqlConnection _connection;
            private const int MaximumNumberOfRetries = 4;
    
            private static bool CanRetry(SqlError error)
            {
                // Use this switch if you want to handle only well-known errors,
                // remove it if you want to always retry. A "blacklist" approach may
                // also work: return false when you're sure you can't recover from one
                // error and rely on Class for anything else.
                switch (error.Number)
                {
                    // Handle well-known error codes, 
                }
    
                // Handle unknown errors with severity 21 or less. 22 or more
                // indicates a serious error that need to be manually fixed.
                // 24 indicates media errors. They're serious errors (that should
                // be also notified) but we may retry...
                return RetriableClasses.Contains(error.Class); // LINQ...
            }
            public static void CreateConnection()
            {
                bool rebuildConnection = true; // First try connection must be open
    
                for (int index = 0; index < MaximumNumberOfRetries; ++index)
                {
                    try
                    {
                        // (Re)Create connection to SQL Server
                        if (rebuildConnection)
                        {
                            if (_connection != null)
                                _connection.Dispose();
    
                            // Create connection and open it...
                        }
    
                        // Perform your task
    
                        // No exceptions, task has been completed
                        break;
                    }
                    catch (SqlException e)
                    {
                        if (e.Errors.Cast<SqlError>().All(x => CanRetry(x)))
                        {
                            // What to do? Handle that here, also checking Number property.
                            // For Class < 20 you may simply Thread.Sleep(DelayOnError);
    
                            rebuildConnection = e.Errors
                                .Cast<SqlError>()
                                .Any(x => x.Class >= 20);
    
                            continue;
                        }
    
                        throw;
                    }
                }
            }
        }
    }
    
    You could use connection.OpenAsync in a task also, another option is to not use one single connection but instead use a new local connection.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by labjac Saturday, July 11, 2020 9:10 PM
    Wednesday, July 8, 2020 12:00 PM
    Moderator
  • I cannot read your error image at all. It is too small and zooming in just makes it blurry.

    I strongly recommend against your code as written. You are guaranteed to leak SQL connections which means you can run out of connections to the DB which will cause problems. Never open a shared resource like a DB without guaranteeing it will be closed. In your case it depends on how you use your SqlDbConnect class but the following will leak 5 connections.

    for (var index = 0; index < 5; ++index)
    {
       new SqlDbConnect();
    }

    At a minimum, if you need to keep the DB connection open then make sure the containing class implements IDisposable and cleans up the DB connection. Otherwise there is nothing anybody using your code can do to make this work.

    The second issue, in my opinion, is that you're opening the DB in the constructor. I should be able to create any # of instances of your type. The DB shouldn't open until either I explicitly want it to or you need it (trying to execute something). Simply creating an object should have no side effects in a perfect world.

    The third thing I see isn't wrong but it is wasteful. You are checking to see if the connection is open before opening it. You just newed up the connection so there is never a case it'll be open already.

    From a design standpoint I don't understand your methods. You have a method whose sole purpose is to set the query text yet it doesn't execute it. So calling code would look like this.

    //Connection leak...
    var conn = new SqlDbConnect();
    conn.SqlQuery("SELECT ...");
    var dt = conn.QueryEx();
    
    //Run another
    conn.SqlQuery("INSERT...");
    conn.NonQueryEx();

    At a minimum this is pretty much what DbCommand already does so you're wrapping it for no reason. Secondly you're requiring calling code to make 2 calls to run 1 query. Since it is highly unlikely that a single query text could be used with both query methods it doesn't make sense to break this up. The query text should be part of the method itself. While this may sound like a design critique it actually plays into the fact that you're also leaking a SqlCommand (just like the SqlConnection) of earlier. The moment you start putting disposable objects into fields you really need to ask yourself if this is the correct design. In this case I would argue it isn't (ignoring the fact that SqlCommand doesn't actually do anything in its dispose method). If you want to stick with this design then store the query text in a simple string property then create the command, run it and dispose of it inside your query methods to at least eliminate that problem. But soon you'll probably realize you don't need the query text field at all and then it becomes a parameter of the query method. 

    Since I cannot see the error message you're getting I'm going to theorize that once you fix the disposable problems your connection issues may go away. 


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, July 8, 2020 1:29 PM
    Moderator
  • Consider an alternative implementation, where _con, Cmd, _da and _dt represent local variables inside QueryEx and NonQueryEx. Also make a helper private function that creates and opens the connection, to be used by QueryEx and NonQueryEx. Close and dispose the connection and command at the end of QueryEx and NonQueryEx.

    Your current code does not seem to free the connections, which probably affects the system connection pool and causes the issue.

    Wednesday, July 8, 2020 1:38 PM
  • hi

    In connection string is there size defined ? if yes follow the link

    https://stackoverflow.com/questions/46818440/how-to-set-max-pool-size-of-sqlconnection-string-in-c-sharp-on-asmx-web-service

    Thanks and regards

    Wednesday, July 8, 2020 5:11 PM
  • Hi labjac,
    First, the main reason for the error is that you just opened the connection but did not release it.
    So you can release all the resources including the connection string by calling the dispose method.
    And using statement will get translated into try/finally block after compilation and dispose method will be called in the finally block.

    using(SqlConnection connection = new SqlConnection())
    {
          connection.Open(); 
      //Perform your operations here
    
    }

    To avoid useless database connections, I suggest you can open db  before performing certain operations instead of opening the database in the constructor.
    Here is a simple code you can refer to.

     private static string connStr = "connectString";
            public static int NonQueryEx(string sql)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
    
                    }
                }
            }

    Best Regards
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 9, 2020 6:20 AM
  • Hallo

    Thank for all the answers, we open multiple connections on various part of the program, I realized we never close the connections, so probably where the leaks is coming from, will look at the code again and see dispose once we got the information from the database.

    Will give feedback during the week, thanks for all the assistance.


    labjac

    Saturday, July 11, 2020 9:15 PM