c# - keep trying to connect to Server through Func - but SqlConnection parameter stays null

Question

I am trying to write a method that "tries again" when some Database (SQL-Server) related stuff fails the first time. After the job is done (or failed), the given connection should be closed.

But in the finally block the connection-object stays NULL, shouldn't there be a reference to the callers Connection-object? I tried a non-static aproach aswell with the same results.

Why is connection (in KeepTrying::Run) always null?

Here is simplified code:

using System;
using System.Data.SqlClient;
class Program
{
    static void Main(string[] args)
    {
        DBManager.CreateConnection();
        Console.ReadKey();
    }
}

public static class DBManager
{
    public static SqlConnection Connection;
    public static bool CreateConnection()
    {
        String error = String.Empty;
        bool result = KeepTrying.Run(() => _CreateConntection(), Connection, out error);
        return result;
    }
    private static bool _CreateConntection()
    {
        Connection = new SqlConnection("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;user=USER;password=PASSWORD;");
        Connection.Open();
        return true;
    }
}

public static class KeepTrying
{
    public static T Run<T>(Func<T> method, SqlConnection connection, out String ErrorMessage)
    {
        ErrorMessage = String.Empty;
        int maxAttempts = 3;
        int time = 435;
        int attempts = 0;
        bool error = true;
        while (error && attempts < maxAttempts)
        {
            attempts++;
            try
            {
                T result = method();
                return result;
            }
            catch (Exception ee)
            {
                ErrorMessage = ee.Message;
                error = true;
                if (attempts < maxAttempts)
                    System.Threading.Thread.Sleep(time);
                else
                    return default(T);
            }
            finally
            {
                if (connection != null)  //connection is still null here
                    connection.Close();
            }
        }
        return default(T);
    }
}

The local scoped variable connection is never set. – Patrick Hofman Oct 09 '18 at 11:48 — Patrick Hofman, Oct 09 '18 at 11:48

score 1 · Accepted Answer · answered Oct 09 '18 at 11:57

1

The connection will not be set when you call run, the static null instance of Connection will be passed into the method and then replaced when the _CreateConnection is invoked, the instance passed into the method will remain null. Although I am not convinced by this pattern, if you omit passing the connection into the method and reference the static Connection at all times it'll probably work. Also consider looking up the use of using when working with disposable objects.

Considering you have a timer running before retries i would also consider opening the connection each time you retry rather than holding onto an open connection while you sleep the thread.

answered Oct 09 '18 at 11:57

Tom John

783
5
14

I want to implement a Transaction class the same way, thats why I dont use try-finally in the _CreateConnection. Which always worked, and now there is another todo with the sleep, yes. But I still dont get why this objects is never set. If I pass some "User" object to another Method and change its properties all those changes will be visible by the caller without returning the User. Why is this different? – Jan Oct 09 '18 at 12:03
The connection is null when you pass it to the method. The static instance is then constructed within the other method you pass in as a parameter. Try putting some break points in and you will see. – Tom John Oct 09 '18 at 12:06
1

I had to write a User-Class example to get in in my head, thanks for staying with me. So in my words: The keyword "new" messes up the obj-reference held by the caller. But it works now when passing the connection with the ref keyword to the Run() Method. =) – Jan Oct 09 '18 at 13:09

c# - keep trying to connect to Server through Func - but SqlConnection parameter stays null

1 Answers1