Note: This is a problem with Microsoft.Data.SqlClient and it's implementation of SqlConnection. If I create an instance (_sqlConnection = new SqlConnection(); ) then check state ( _sqlConnection.State) I get a NullReferenceException. If I assign a valid Connection string, still Null exception.
The solution (to save you time) was to revert back to using System.Data.SqlClient and all is good. I am leaving the rest here in case it helps track down the cause - or if someone else is having a similar issue.
I have a WPF .net 4.7.2 app that runs when I build and run from VS (or double click the .exe in debug or release.) If I generate a .msi file and install from there, I get a null exception error when writing a log to the MS SQL server (but not to the local file)
I am using:
The .msi file is created using: Microsoft Visual Studio Installer Projects (available from https://marketplace.visualstudio.com/items?itemName=VisualStudioClient.MicrosoftVisualStudio2017InstallerProjects&ssr=false#overview)
The following code has been modified to remove all distractions as well as any proprietary information. Keep in mind that this works fine EXCEPT when installed as a .msi file.
When my app starts, I call the following: Serilog.Debugging.SelfLog.Enable(msg => Debug.WriteLine(msg));
Example of call to cause problem: LogDebug("This is a developer test of LogDebug by {user}", );
Code:
// Primary method
public void LogDebug(string msgtemplate, params object[] parms){
var LogCfg = GetStdLogConfig();
Log.Logger = LogCfg.CreateLogger();
Log.Warning(msgtemplate, parms);
Log.CloseAndFlush(); // error happens on this line
}
protected virtual LoggerConfiguration GetStdLogConfig()
{
LoggerConfiguration logcfg = null;
logcfg = new LoggerConfiguration()
.MinimumLevel.ControlledBy(StandardLevelSwitch)
.Enrich.WithProperty("Fid1", AppValues.VersionNo)
.Enrich.WithProperty("Fid2", AppValues.UserId)
.Enrich.WithProperty("Fid3", getUserRightsString())
.Enrich.WithProperty("Fid4", AppValues.AppCompileMode);
AddLocalFile(logcfg, "std"); // this writes to local file just fine.
AddSql(logcfg, "std");
return logcfg;
}
protected virtual LoggerConfiguration AddSql(LoggerConfiguration logcfg, string mode = "")
{
try
{
var logTable = "App_Logs"; // Renamed for sample
var sinkOptions = new MSSqlServerSinkOptions { TableName = logTable };
var columnOptionVals = new ColumnOptions
{
AdditionalColumns = new System.Collections.ObjectModel.Collection<SqlColumn>()
};
columnOptionVals.AdditionalColumns.Add(new SqlColumn("Fid1", System.Data.SqlDbType.NVarChar, true, 50));
columnOptionVals.AdditionalColumns.Add(new SqlColumn("Fid2", System.Data.SqlDbType.NVarChar, true, 15));
columnOptionVals.AdditionalColumns.Add(new SqlColumn("Fid3", System.Data.SqlDbType.NVarChar, true, 100));
columnOptionVals.AdditionalColumns.Add(new SqlColumn("Fid4", System.Data.SqlDbType.NVarChar, true, 20));
columnOptionVals.Store.Remove(StandardColumn.MessageTemplate);
columnOptionVals.LogEvent.DataLength = 2048;
columnOptionVals.PrimaryKey = columnOptionVals.TimeStamp;
columnOptionVals.TimeStamp.NonClusteredIndex = true;
// <Valid ConnectionString> is the same as used to do other updates without issue
logcfg.WriteTo.MSSqlServer(
connectionString: <Valid ConnectionString>,
sinkOptions: sinkOptions,
columnOptions: columnOptionVals
);
return logcfg;
}
The error happens in Log.CloseAndFlush():.
System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=Microsoft.Data.SqlClient
StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)
If I continue, this error shows:
System.NullReferenceException
HResult=0x80004003
Message=Object reference not set to an instance of an object.
Source=Microsoft.Data.SqlClient
StackTrace:
at Microsoft.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken)
This exception was originally thrown at this call stack:
Microsoft.Data.SqlClient.SqlConnection.OpenAsync(System.Threading.CancellationToken)
System.Data.Common.DbConnection.OpenAsync()
Serilog.Sinks.MSSqlServer.Platform.SqlClient.SqlConnectionWrapper.OpenAsync()
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in exceptionservicescommon.cs
If I view this exception, in the StackTrace, I notice additional info:
at Microsoft.Data.SqlClient.SqlConnection.OpenAsync(CancellationToken cancellationToken) at System.Data.Common.DbConnection.OpenAsync() at Serilog.Sinks.MSSqlServer.Platform.SqlClient.SqlConnectionWrapper.d__6.MoveNext() at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() in f:\dd\ndp\clr\src\BCL\system\runtime\exceptionservices\exceptionservicescommon.cs:line 133
It then continues (but nothing is logged to the SQL server)
In the output window I see (due to SelfLog call above):
Exception thrown: 'System.NullReferenceException' in mscorlib.dll
Object reference not set to an instance of an object.
When I debug the .exe, after the SqlConnection is instantiated, many of the properties are NullExceptions, not NULL. Trying to assign a value to them does nothing and they are still NullExceptions. But it only happens on this call, all other instantiations of SqlConnection work as expected.
All the other projects in the solution use System.Data.SqlClient - which is what all that code worked and only this sink did not.
I edited the library per the opening info in the email. What finally fixed it (with or without the help of those steps) was to replace the Github MS SQL libraries with the default system sql libraries.
//using Microsoft.Data.SqlClient;
using System.Data.SqlClient;