View print statements in your stored procedures in C# via ADO.NET

You may have "print" statements in your stored procedures or function in SQL Server for informational purposes.

You can retrieve warnings and informational messages from a SQL Server data source using the InfoMessage event of the SqlConnection object. Errors returned from the data source with a severity level of 11 through 16 cause an exception to be thrown. However, the InfoMessage event can be used to obtain messages from the data source that are not associated with an error. In the case of Microsoft SQL Server, any error with a severity of 10 or less is considered to be an informational message, and can be captured by using the InfoMessage event.

The InfoMessage event receives a SqlInfoMessageEventArgs object containing, in its Errors property, a collection of the messages from the data source. You can query the Error objects in this collection for the error number and message text, as well as the source of the error. The .NET Framework Data Provider for SQL Server also includes detail about the database, stored procedure, and line number that the message came from.

Example:

// Assumes that connection represents a SqlConnection object.
  connection.InfoMessage +=
    new SqlInfoMessageEventHandler(OnInfoMessage);

protected static void OnInfoMessage(
  object sender, SqlInfoMessageEventArgs args)
{
  foreach (SqlError err in args.Errors)
  {
   Console.WriteLine(
  "The {0} has received a severity {1}, state {2} error number {3}\n" +
  "on line {4} of procedure {5} on server {6}:\n{7}",
   err.Source, err.Class, err.State, err.Number, err.LineNumber,
   err.Procedure, err.Server, err.Message);
  }
}

The InfoMessage event will normally fire only for informational and warning messages that are sent from the server. However, when an actual error occurs, the execution of the ExecuteNonQuery or ExecuteReader method that initiated the server operation is halted and an exception is thrown.

If you want to continue processing the rest of the statements in a command regardless of any errors produced by the server, set the FireInfoMessageEventOnUserErrors property of the SqlConnection to true. Doing this causes the connection to fire the InfoMessage event for errors instead of throwing an exception and interrupting processing. The client application can then handle this event and respond to error conditions.

These two "tricks" can be very useful for debugging, especially for production code.