Displaying PRINTed SQL Output in ASP.Net

The SqlConnection, SqlCommand and SqlDataReader classes are mainly used to query resultsets from the database. But they also support retrieving the output of SQL Server’s PRINT statements.

The printed output can be processed in the SqlConnection event InfoMessage:

SqlConnection cn = OpenMyConnection();
cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);

So if you have to display the result of PRINT statements in an ASP.Net multiline TextBox, try this code:

sbLog = new System.Text.StringBuilder();
SqlCommand cmd = new SqlCommand("PRINT 'Hello World'", cn);
cmd.ExecuteNonQuery();
TextBox1.Text = sbLog.ToString();

and additionally

void cn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    sbLog.AppendLine(e.Message);
}

You can also let .Net execute the SQL statements automatically by calling them from the SelectCommand of a SqlDataSource which is assigned to a GridView:

ds.Selecting += new 
    SqlDataSourceSelectingEventHandler(ds_Selecting);

void ds_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
    SqlConnection cn = (SqlConnection)e.Command.Connection;
    cn.InfoMessage += new SqlInfoMessageEventHandler(cn_InfoMessage);
}

In this solution, we can access the SqlConnection object in the Selecting event to add our InfoMessage event handler, which captures PRINT output.

The Connection value in the event arguments has to be cast to SqlConnection to support InfoMessage.

If your SQL statement never returns a recordset, set GridView1.Visible = false after binding.