C # SQL Server Output Processing

Question

When you run scripts in SQL Server Management Studio, the messages that appear in the message box are often generated. For example, when starting a database backup:

processed 10%.

processed 20%.

Etc ...

1722608 pages were processed for the Sample database, the Sampe file in file 1.

100% processed.

Processed 1 page for the Sample database, file "Sample_Log" in file 1.

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

I would like to be able to display this message in a C # application that runs SQL scripts in a database. However, I cannot figure out how to get a descriptor for outputting a message from SQL as it is created. Does anyone know how to do this? It doesn't matter to me which communications infrastructure I should use. I am relatively comfortable with LINQ, NHibernate, Entity Framework, ADO.Net, the corporate library, and I am happy to learn new ones.

Answer 1

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

Answer 2

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

Please note that you really need the code:

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

This e.Message message returns the message back to txtMessages (you can replace it as a TextBox or Label).

You can also refer to this article: Backing up a SQL Server database

An example of my code is 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 make the desktop work, you need to implement this with a background worker, which your application does not freeze and will not be 100% done unexpectedly.