From time to time at my Windows 10 system background program in C# I receive NullReferenceException at this.connection.Open().
My class:
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]");
}
My code in function:
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.Datan");
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();
}
At 99% it works but sometimes I receive that result:
-----------------------
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)
.
.
--------------------------------------
Full stackTrace:
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)
As you see at string builder trace, my code fill this.connection class field and try to Open connection. Unfortunately Open operation failed three times with 1 second delay between.
This happen only at <1% cases. Have you got any idea how to deal with it?
I tried:
- add loop on InitConnectionIfNeeded method (better situation but still sometimes nullreferenceexception)
- add trace
I want to resolve these errors and Open connection with success result.
New contributor