How to create a progress bar for a stored procedure while importing a XML file into SQL Server Management Studio

Question

I am stuck: here is my sample code

namespace WindowsFormsApplicationXml
{
    public partial class Form1 : Form
    {
        string ConString, CmdString;
        int TotalRecords;
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader reader;

        public Form1()
        {
            InitializeComponent();
            ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;

        }
        public class MyWorkerClass
        {
                    public int UserID;
        }

        private void btnSaveXML_Click(object sender, EventArgs e)
        {
            MyWorkerClass obj = new MyWorkerClass();
            if (!backgroundWorker1.IsBusy)
            {
                TotalRecords = GetTotalRecords();
                progressBar1.Maximum = TotalRecords;
                backgroundWorker1.RunWorkerAsync(obj);
            }
        }

        private int GetTotalRecords()
        {
            try
            {
                using (con = new SqlConnection(ConString))
                {
                    cmd = new SqlCommand("SELECT COUNT(UserID) FROM UserMaster", con);
                    con.Open();
                    TotalRecords = int.Parse(cmd.ExecuteScalar().ToString());
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return TotalRecords;
        }


        public void LoadData(MyWorkerClass obj)
        {
            try
            {
                lblMessage.Text = "Please, Wait importing data from XML is in progress....";
                lblMessage.Visible = true;
                btnSaveXML.Enabled = false;
                DataSet ds = new DataSet();
                XmlTextReader xmlreader = new XmlTextReader(txtXMLFilePath.Text.ToString());
                xmlreader.MoveToContent();
                xmlreader.Read();

                ds.ReadXml(xmlreader);
                xmlreader.Close();
                if (ds.Tables.Count != 0)
                {
                    using (SqlConnection con = new SqlConnection("Data Source= YOUNGTECH-PC\\SQLEXPRESSNEW;Integrated Security=true;Initial Catalog=CADirect_CT; uid=reshav; Password=kanak; "))
                    {
                        string xml = ds.GetXml();//for verification code
                        con.Open();
                        SqlCommand cmd = new SqlCommand("sp_ct_importUserXml", con);
                        cmd.CommandType = CommandType.StoredProcedure;
                        int i = 0;
                        cmd.Parameters.Add("@XMLdata", SqlDbType.Xml).Value = ds.GetXml();

                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds1 = new DataSet();
                        da.Fill(ds1);
                        lblResulttext.Visible = true;
                        dgviewXMLData.Visible = true;
                        dgviewXMLData.DataSource = ds1.Tables[0].DefaultView;
                        AutoNumberRowsForGridView(dgviewXMLData);
                        //i++;
                        //Thread.Sleep(10);
                        con.Close();
                        lblMessage.Text = "Successfully import XML data with below result.";
                        btnSaveXML.Enabled = true;
                    }
                }
            }
            catch (Exception ex)
            {
                lblMessage.Visible = true;
                lblMessage.Text = ex.Message;
            }

        }

        public void AutoNumberRowsForGridView(DataGridView dataGridView)
        {

            if (dataGridView != null)
            {

                for (int count = 0; (count <= (dataGridView.Rows.Count - 1)); count++)
                {

                    dataGridView.Rows[count].HeaderCell.Value = string.Format((count + 1).ToString(), "0");

                }

            }

        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog = new OpenFileDialog();
            // To list only xml files, we need to add this filter
            openFileDialog.Filter = "|*.xml";
            DialogResult result = openFileDialog.ShowDialog();

            if (result == DialogResult.OK)
            {
                txtXMLFilePath.Text = openFileDialog.FileName;
            }
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            MyWorkerClass obj = (MyWorkerClass)e.Argument;
            LoadData(obj);
        }

        private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            if (!backgroundWorker1.CancellationPending)
            {
                MyWorkerClass obj = (MyWorkerClass)e.UserState;

            }
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

        }

    }
}

Answer 1

You can define a controller child action which can return the value of the bar (0-100) according to how much data is imported.

Use an ajax call to render the progress bar, every 500 ms:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Title</title>
  <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
  <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
  <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.min.js"></script>
</head>
<body>

<div id="progressbar"></div>
 <script >
    //Calls function every 3 seconds
    $(function() 
    {
        $("#progressbar").progressbar(
        {
          value: 0
        });
    });
    window.setInterval(function()
    {
         window.progressBarValue = 0;
         if(window.progressBarValue < 100)
         {       
             $.ajax(
                     {
                         //I'm using this to get a random int, you should create a controller action which should return the data status
                         url: "http://www.random.org/integers/?num=1&min=1&max=100&col=1&base=10&format=plain&rnd=new", //"@Url.Action("YourGetProgressBarAction","YourControllerName")",
                         success:function(data)
                         {
                            //data should be a value like 37, 53, 87
                            $(function() 
                            {
                                window.progressBarValue = parseInt(data);
                                $("#progressbar").progressbar(
                                {
                                  value: parseInt(data)
                                });
                            });
                         }}
                    );
        }
    },500);
 </script>
</body>
</html>

Regarding on how to obtain how much of the XML is imported, it depends on how you do it. The easieast way would be to count how many objects you have in your XML and update the progress bar after each successful database insert.

In this way, you can call the method which estimates how much you imported in your controller action (memorize the total in a variable and make it available to the controller via a property or a method). By knowing how many objects you have in your XML and how many you imported you can apply the rule of three to determine the progressbar status.