NullReferenceException при открытии C# SqlConnection ⇐ C#

[Anonymous]

Время от времени в моей системной фоновой программе Windows 10 на C# я получаю исключение NullReferenceException в this.connection.Open().
Мой класс:

Код: Выделить всё

private SqlConnection connection;
private string connectionString;
private StringBuilder trace = new StringBuilder();
private int counter = 0;

public void InitConnectionIfNeeded()
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [0]");

// Check if connection is already established but connection string changed, purge it.
if (this.connection != null && this.connection.ConnectionString != this.connectionString)
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [1]");

if (this.connection.State == ConnectionState.Open)
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [2]");
this.connection.Close();
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [2+]");
}

this.connection = null;
}

// Create instance of SqlConnection object by connection string
if (this.connection == null)
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [3]");
this.connection = new SqlConnection(this.connectionString);
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [3+]\nConnectionString: {this.connectionString}");
}

// If ConnectionState != Open then open it.
if (this.connection.State != ConnectionState.Open)
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [4]");

this.connection.Open();

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [4+]");
}

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} InitConnectionIfNeeded [5]");
}

Мой код в функции:

Код: Выделить всё

message = "";
errorStackTrace = "";
success = false;
resultsCount = 0;
results = new DataTable();

try
{
bool connectionEstablished = false;
Exception lastException = null;

for (int i = 0; i < 3;  i++)
{
try
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Try InitConnectionIfNeeded Attempt: {i+1}");

InitConnectionIfNeeded();
connectionEstablished = true;
break;
}
catch(Exception ex)
{
System.Threading.Thread.Sleep(1000);
lastException = ex;
}
}

if(!connectionEstablished)
{
throw lastException;
}

using (SqlCommand cmd = new SqlCommand(query, connection))
{
trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [1]");

cmd.CommandTimeout = (int)queryTimeout;

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [2]");

SqlDataReader reader = cmd.ExecuteReader();

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [3]");

results.Load(reader);

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [4]");

resultsCount = results.Rows.Count;

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [5]");

ChangeDate(results);

trace.AppendLine($"{counter++}\t{DateTime.Now.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")} Get Collection [6]");

success = true;
}
}
catch (SqlException ex) when (ex.Number == -2)
{
message = "The command has timed out.";
}
catch (Exception ex)
{
message = ex.Message;
StringBuilder sb = new StringBuilder();
sb.AppendLine($"-----------------------\nError at: {DateTime.Now.ToLocalTime()}");
sb.Append(this.trace + "\n::\n");
sb.Append($"ex.Source: {ex.Source}\n");
sb.Append($"ex.HResult: {ex.HResult}\n");

if (ex.InnerException != null)
{
sb.Append($"ex.InnerExceptionMessage: {ex.InnerException.Message}\n");
sb.Append($"ex.InnerExceptionGetTypeFullName: {ex.InnerException.GetType().FullName}\n");
sb.Append($"ex.InnerExceptionStackTrace: {ex.InnerException.StackTrace}\n");
}

sb.Append($"ex.GetType: {ex.GetType().FullName}\n");

if (ex.Data != null && ex.Data.Count > 0)
{
sb.Append("ex.Data\n");

foreach (DictionaryEntry dictionaryEntry in ex.Data)
{
sb.Append($"{dictionaryEntry.Key}\t{dictionaryEntry.Value}\n");
}
}

sb.Append($"ex.StackTrace: {ex.StackTrace}\n.\n.\n");
sb.AppendLine("--------------------------------------");
errorStackTrace = sb.ToString();
}

В 99% случаев это работает, но иногда я получаю такой результат:

Код: Выделить всё

Error at: 2024-07-11 14:27:32
0   2024-07-11 14:27:29.555 Try InitConnectionIfNeeded Attempt: 1
1   2024-07-11 14:27:29.555 InitConnectionIfNeeded [0]
2   2024-07-11 14:27:29.555 InitConnectionIfNeeded [3]
3   2024-07-11 14:27:29.555 InitConnectionIfNeeded [3+]
ConnectionString: Data Source=MYSQLSERVER;Database=DB123;Trusted_Connection=Yes;Timeout=30
4   2024-07-11 14:27:29.555 InitConnectionIfNeeded [4]
5   2024-07-11 14:27:30.558 Try InitConnectionIfNeeded Attempt: 2
6   2024-07-11 14:27:30.558 InitConnectionIfNeeded [0]
7   2024-07-11 14:27:30.558 InitConnectionIfNeeded [4]
8   2024-07-11 14:27:31.560 Try InitConnectionIfNeeded Attempt: 3
9   2024-07-11 14:27:31.560 InitConnectionIfNeeded [0]
10  2024-07-11 14:27:31.560 InitConnectionIfNeeded [4]

::
ex.Source: do3wemmp
ex.HResult: -2147467261
ex.GetType: System.NullReferenceException
ex.StackTrace:    w SQLServerC_.Get_Collection(String query, Decimal queryTimeout, String& message, Boolean& success, Decimal& resultsCount, DataTable& results, String&  errorStackTrace)
.
.
--------------------------------------

Полная трассировка стека:

Код: Выделить всё

   w System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
w System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
w System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
w System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
w System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
w System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
w System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
w System.Data.SqlClient.SqlConnection.Open()
w SQLServerC_.InitConnectionIfNeeded()
w SQLServerC_.Get_Collection(String query, Decimal queryTimeout, String& message, Boolean& success, Decimal& resultsCount, DataTable& results, String& errorStackTrace)

Как вы видите в трассировке построителя строк, мой код заполняет поле класса this.connection и пытается открыть соединение. К сожалению, операция Open завершилась неудачно трижды с задержкой в ​​1 секунду между ними.
Это происходит только в < 1% случаев.
Do у вас есть идеи, как с этим справиться?
Я попробовал:

• добавить цикл в InitConnectionIfNeeded метод (лучшая ситуация, но все равно иногда выдает исключение NullReferenceException)
• добавить трассировку

Я хочу чтобы устранить эти ошибки и открыть соединение с успешным результатом.

Подробнее здесь: https://stackoverflow.com/questions/787 ... ction-open