SQL Connection message event handler use

Introduction

This article will describe how to backup a database of SQL Server and how to receive status messages send by SQL Server while backup process is running. BackgroundWorker functionality are also described in this article.

Using the code

Backup is an essential object while developing a program for client. Data may be corrupt in many cases, so we have to develop a program by which we can preserve user data on external media or computer.

This article describes about how to backup an SQL Server database. You will also learn about asynchronous functionality of BackgroundWroker class in this article.

I also want to introduce about SqlConnection class property FireInfoMessageEventOnUsersErrors working in this article. When SQL Server executes an command it sends status to application which can be caught by application.

In this article when Backup processes Status of backup in percentage will be received by application which is send by SQL Server to show progress bar value and percentage on label control.

Finally, this article doesn’t use any third party library/control to perform backup action. All classes used in this code are built-in .NET Framework. You can run this code without any problem and can incorporate in any application that uses SQL Server as backend to store data. 

Source code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.Sql;
using System.Data.SqlClient;
using System.IO;
using System.Data.Common;

namespace BackupDatabase
{
    public partial class Form1 : Form
    {
        SqlConnection sCon = null;
        SqlCommand sCmd = null;
        SqlDataAdapter sAdpt = null;

        BackgroundWorker bWorker = null;
        bool bDone = true;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cboAuth.SelectedIndex = 0;
            this.Height = 235;

            setWindowLocation();
        }

        private void bttnCancel_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void bttnBrowse_Click(object sender, EventArgs e)
        {
            folderDialog.Description = "Please select path";
            folderDialog.RootFolder = Environment.SpecialFolder.Desktop;

            if (folderDialog.ShowDialog() != DialogResult.Cancel)
                txtBackupLocation.Text = folderDialog.SelectedPath;
        }

        private void bttnConnect_Click(object sender, EventArgs e)
        {
            if (txtServer.Text.Trim() == string.Empty)
            {
                MessageBox.Show("Please select Server Name", 
                  "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            else if (cboAuth.SelectedIndex==1)
            {
                if (txtUserName.Text.Trim() == string.Empty)
                {
                    MessageBox.Show("Please enter User Name", 
                      "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                else if (txtPassword.Text.Trim() == string.Empty)
                {
                    MessageBox.Show("Please enter Password", 
                      "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
            }

            string sConnectionString = string.Empty;

            if (cboAuth.SelectedIndex == 1)
                sConnectionString = "Data Source=" + txtServer.Text + 
                  "; User Id=" + txtUserName.Text + "; Password=" + 
                  txtPassword.Text + "; Database=Master";
            else
                sConnectionString = "Data Source=" + txtServer.Text + 
                  "; Integrated Security=SSPI; Database=Master";

            try
            {
                sCon = new SqlConnection(sConnectionString);
                sCon.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Connect to Server", 
                      MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            this.Height = 423;

            setWindowLocation();

            loadDatabaseList();
        }

        void setWindowLocation()
        {
            this.Top = (Screen.PrimaryScreen.Bounds.Height - this.Height) / 2;
            this.Left = (Screen.PrimaryScreen.Bounds.Width - this.Width) / 2;
        }

        void loadDatabaseList()
        {
            sCmd = new SqlCommand("select name from sys.databases order by name", sCon);
            sAdpt = new SqlDataAdapter(sCmd);

            DataSet ds = new DataSet();
            sAdpt.Fill(ds, "tblList");

            cboDatabaseList.DisplayMember = "name";
            cboDatabaseList.DataSource = ds.Tables[0];
        }

        private void bttnStart_Click(object sender, EventArgs e)
        {
            if (cboDatabaseList.SelectedIndex == -1)
            {
                MessageBox.Show("No database name found to backup", 
                  "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            else if (txtBackupLocation.Text.Trim()==string.Empty)
            {
                MessageBox.Show("No backup location found to backup", 
                  "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            bWorker = new BackgroundWorker();

            bWorker.WorkerReportsProgress = true;
            bWorker.WorkerSupportsCancellation = true;

            bWorker.DoWork += new DoWorkEventHandler(bWorker_DoWork);
            bWorker.ProgressChanged += new ProgressChangedEventHandler(bWorker_ProgressChanged);
            bWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(bWorker_RunWorkerCompleted);

            BackupHelper bI = new BackupHelper(sCon,cboDatabaseList.Text, txtBackupLocation.Text);
            bWorker.RunWorkerAsync(bI);
        }

        void bWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            BackupHelper bI = (BackupHelper)e.Argument;

            bI.StartBackup(bWorker);
        }

        void bWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            pBar.Value = 0;
            lblPercent.Text = "0 %";

            if (bDone)
            {
                MessageBox.Show("Backup Complete !", 
                  "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        void bWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (e.ProgressPercentage == -1)
            {
                bDone = false;
                bWorker.CancelAsync();
                MessageBox.Show(e.UserState.ToString(), 
                  "Connect to Server", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                pBar.Value = e.ProgressPercentage;
                lblPercent.Text = pBar.Value + " %";
            }
        }
    }

    public class BackupHelper
    {
        private SqlConnection sBackupConnection = null;
        private string sDatabaseName = null;
        private string sBackupLocation = null;

        BackgroundWorker bW = null;

        private SqlConnection BackupConnection
        {
            get
            {
                return sBackupConnection;
            }
            set
            {
                sBackupConnection = value;
            }
        }

        private string DatabaseName
        {
            get
            {
                return sDatabaseName;
            }
            set
            {
                sDatabaseName = value;
            }
        }

        private string BackupLocation
        {
            get
            {
                return sBackupLocation;
            }
            set
            {
                sBackupLocation = value;
            }
        }

        public BackupHelper()
        {

        }

        public BackupHelper(SqlConnection sBackupConnection, 
                string sDatabaseName, string sBackupLocation)
        {
            this.BackupConnection = new SqlConnection(sBackupConnection.ConnectionString);
            this.DatabaseName = sDatabaseName;
            this.BackupLocation = sBackupLocation;
        }

        public void StartBackup(BackgroundWorker bW)
        {
            this.bW = bW;

            sBackupConnection.FireInfoMessageEventOnUserErrors = true;
            sBackupConnection.InfoMessage += OnInfoMessage;
            sBackupConnection.Open();

            StringBuilder sb = new StringBuilder();

            sb.AppendFormat(@"BACKUP DATABASE [{0}] TO  DISK = N'{1}' WITH " + 
                @"DESCRIPTION = N'{2}', NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD,  STATS = 1",
                this.DatabaseName, Path.Combine(this.BackupLocation, 
                this.DatabaseName + ".bak"), this.DatabaseName + " full backup");

            SqlCommand sqlCmd = new SqlCommand(sb.ToString(), sBackupConnection);
            sqlCmd.CommandTimeout = 10000;
            sqlCmd.ExecuteNonQuery();

            sBackupConnection.Close();
        }

        protected void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            if (e.Message.Contains("percent processed."))
            {
                int iPercent = 0;
                int.TryParse(e.Message.Replace("percent processed.", ""), out iPercent);

                bW.ReportProgress(iPercent, null);
            }
            else
            {
                #region COMMENT

                string CommandStatus = string.Empty;

                foreach (SqlError info in e.Errors)
                    if (info.Class > 10)
                        CommandStatus += e.Message + Environment.NewLine;

                if (CommandStatus != string.Empty)
                {
                    bW.ReportProgress(-1, CommandStatus);
                }

                #endregion
            }
        }
    }
}

This statement will confirm that the child thread can send data to it's parent thread.

bWorker.WorkerReportsProgress = true;

This statement specifies that the child thread can be interrupt while running.

bWorker.WorkerSupportsCancellation = true;

This statement specifies that method bWorker_DoWork will be execute when RunWorkerAsync method will be call.

bWorker.DoWork += new DoWorkEventHandler(bWorker_DoWork);

This statement specifies that method bWorker_ProgressChanged will be execute when child thread call ReportProgress method.

bWorker.ProgressChanged += new ProgressChangedEventHandler(bWorker_ProgressChanged);

Finally... These following lines of code will specifies the SqlConnection class can send message when any SqlCommand fires on it, and which method will be call Specifies by InfoMessage property.

sBackupConnection.FireInfoMessageEventOnUserErrors = true;
sBackupConnection.InfoMessage += OnInfoMessage;

Finally, I think this article will help to improve knowledge of beginners and developers also. Thanks...

Next Update

In the next version of this article I will introduce the restore functionality.