C# Handle on SQL Server Message Output

Question

When executing scripts in SQL Server Management Studio, messages are often generated that display in the message window. For example when running a backup of a database:

10 percent processed.

20 percent processed.

Etc...

Processed 1722608 pages for database 'Sample', file 'Sampe' on file 1.

100 percent processed.

Processed 1 pages for database 'Sample', file 'Sample_Log' on file 1.

BACKUP DATABASE successfully processed 1722609 pages in 202.985 seconds (66.299 MB/sec).

I would like to be able to display these message in a C# application that is running SQL scripts against a database. However, I cannot figure out how to get a handle on the message output from SQL as it is generated. Does anybody know how to do this? It doesn't matter to me which connection framework I have to use. I'm relatively comfortable with LINQ, NHibernate, Entity Framework, ADO.Net, Enterprise Library, and am happy to learn new ones.

Solution 1

The SqlConnection.InfoMessage event occurs when SQL Servers returns a warning or informational message. This website shows a possible implementation.

Solution 2

Here is the example code I tried and it works for me. http://www.dotnetcurry.com/ShowArticle.aspx?ID=344

Note the code you need is actually this part :

cn.Open();
cn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
{                                    
         txtMessages.Text += "\n" + e.Message;                                   
};

It's the e.Message keeps returning the message back to txtMessages (You can replace as TextBox or Label).

You may also refer to this article: Backup SQL Server Database with progress

An example of my code is in the following:

//The idea of the following code is to display the progress on a progressbar using the value returning from the SQL Server message. 
//When done, it will show the final message on the textbox. 
String connectionString = "Data Source=server;Integrated Security=SSPI;";
SqlConnection sqlConnection = new SqlConnection(connectionString);

public void DatabaseWork(SqlConnection con)
{
    con.FireInfoMessageEventOnUserErrors = true;
    //con.InfoMessage += OnInfoMessage;
    con.Open();
    con.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
    {
        //Use textBox due to textBox has Invoke function. You can also utilize other way. 
        this.textBox.Invoke(
            (MethodInvoker)delegate()
            {
                int num1;
                //Get the message from e.Message index 0 to the length of first ' '
                bool res = int.TryParse(e.Message.Substring(0, e.Message.IndexOf(' ')), out num1);

                //If the substring can convert to integer
                if (res)
                {
                    //keep updating progressbar
                    this.progressBar.Value = int.Parse(e.Message.Substring(0, e.Message.IndexOf(' ')));
                }
                else
                {
                    //Check status from message 
                    int succ;
                    succ = textBox.Text.IndexOf("successfully");
                    //or succ = e.Message.IndexOf("successfully");  //get result from e.Message directly
                    if (succ != -1) //If IndexOf find nothing, it will return -1
                    {
                        progressBar.Value = 100;
                        MessageBox.Show("Done!");
                    }
                    else
                    {
                        progressBar.Value = 0;
                        MessageBox.Show("Error, backup failed!");
                    } 
                }
            }
        );
    };
    using (var cmd = new SqlCommand(string.Format(
        "Your SQL Script"//,
        //QuoteIdentifier(databaseName),
        //QuoteString(Filename)//,
        //QuoteString(backupDescription),
        //QuoteString(backupName)
        ), con))
    {
        //Set timeout = 1200 seconds (equal 20 minutes, you can set smaller value for shoter time out. 
        cmd.CommandTimeout = 1200;
        cmd.ExecuteNonQuery();
    }
    con.Close();
    //con.InfoMessage -= OnInfoMessage;
    con.FireInfoMessageEventOnUserErrors = false;
}

In order to get the progressbar working, you need to implement this with a backgroundworker, which your application won't freeze and get 100% done suddenly.