Monday, April 14, 2008

Using the SqlConnection InfoMessage to return variable amounts of data

A little used thingie on the SqlConnection object called the InfoMessage event fires whenever there were info messages (duh!) from the last query or stored procedure execution. That means errors, of course, but it also means warnings and simple prints! You get where I am going with this?

Instead of changing stored procedures, datalayers and code whenever I need to get some new information from SQL, I can just add some nicely formatted PRINT commands and get all the information I need! Here is some code:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SqlPrintCommands
public partial class Form2 : Form
public Dictionary<string, string> values;

public Form2()
values = new Dictionary<string, string>();

private void button1_Click(object sender, EventArgs e)
SqlConnection connection =
new SqlConnection("[connectionString]");
connection.InfoMessage += sc_InfoMessage;
SqlCommand comm =
new SqlCommand("pr_Test", connection);
string s = "";
foreach (KeyValuePair<string, string> pair in values)
s += string.Format("{0} : {1}\r\n",
pair.Key, pair.Value);
label1.Text = s;

private void sc_InfoMessage(object sender,
SqlInfoMessageEventArgs e)
string commandPrefix = "Eval: ";
foreach (SqlError err in e.Errors)
if ((err.Message ?? "").StartsWith(commandPrefix))
string command =
string[] cmd = command.Trim().Split('=');
string commandArgument = cmd[0];
string commandValue = cmd[1];
values[commandArgument] = commandValue;

In this scenario I have a simple form with a button and a label. I execute a pr_Test stored procedure and then I parse the messages it returns. If the messages are of the format
Eval: Name=Value
I store the keys and values in a Dictionary. Not the nicest code, but it's for demo purposes.

So, you want to know the count of whatever operation you executed? Add
PRINT 'Eval: RowCount='+cast(@@rowcount as varchar)
in your stored procedure. Pretty cool huh?

Unfortunately I haven't been able to send messages asynchronously, even if the connection was async and the running was async and the messages were generated with
. BTW, who is the idiot that spelled RAISERROR with only one E? What's a Rror and why would I raise it?