title: StoredProcedure custom sink for Serilog
body: <p>I want to call a stored procedure using Serilog. I am aware that there is no such sink so I am creating a custom Sink. I have my logic to call the stored procedure inside the Emit method of the StoredProcedureSink that implements ILogEventSink. Now, this stored procedure returns a value. How can I get this value when I use Log.Information();</p>
  
  <pre><code>class StoredProcedureSink : ILogEventSink
      {
          private string _connectionString;
          public StoredProcedureSink(string connectionString)
          {
              _connectionString = connectionString;
          }
  
          public void Emit(LogEvent logEvent)
          {
              var conn = new SqlConnection(_connectionString);
              conn.Open();
              SqlCommand cmd = new SqlCommand(logEvent.MessageTemplate.ToString().Substring(0, logEvent.MessageTemplate.ToString().IndexOf('{')), conn);
              cmd.CommandType = CommandType.StoredProcedure;
              var properties = logEvent.Properties.GetValueOrDefault("SqlParams");
              var json = JObject.Parse(properties.ToString().Substring(properties.ToString().IndexOf('{') - 1));
              foreach(var kvp in json)
              {
                  cmd.Parameters.Add(new SqlParameter(kvp.Key, ((JValue)kvp.Value).Value));
              }
              cmd.ExecuteNonQuery();
  //I would like to read the value returned by the stored proc.
          }
      }
  
  //I have a wrapper DBLogger in which I configure the serilog. I have published DBLogger as a nuget package so I can use it in all my apps.
  
  public class DBLogger()
  {
  
  public DBLogger()
  {
  Log.Logger = new LoggerConfiguration()
              .MinimumLevel.Information()
              .MinimumLevel.Override("Microsoft", LogEventLevel.Warning)
              .Enrich.FromLogContext()
              .WriteTo.StoredProcedureSink(
                  "connectionString")
              .CreateLogger();
  }
  
  public void Information(string storedProcedureName, T parameters)
          {
              try
              {
                 Log.Information(storedProcedureName, parameters);
              }
              catch (Exception ex)
              {
                  Log.Fatal(ex, "Host terminated unexpectedly");
              }
          }
  }
  
  public class Program
  {
     static void Main()
     {
       var logger = new DBLogger();
       logger.Information("storedProcedureName", params); //need the Id returned by the stored proc here.
      }
  }
  </code></pre>