CODEWITHSUNDEEP
c#sql-serversql-server-2008
Sora
Sora

Reputation: 2551

Showing a progress bar for a database insertion

I have an application in which the user uploads an Excel file containing several hundreds rows and when he hits upload the file is converted to a DataTable and sent to the database to be inserted by a stored procedure. The upload is taking too much time and I want to display progress for the number of rows that have been inserted. Is there anyway to do that?

List<DataTable> tb = Helper.SplitTableToManyTables(FileTb, 50); // this is a function that split the Main dataTable to several Dt each have 50 rows
        int importedRowsCount = 0;
        for (int KLoop = 0; KLoop < tb.Count; KLoop++)
        {
            string[] ContctInfoCols = { "First Name", "Last Name", "Nickname", "Job Title", "Birthday", "Gender", "E-mail Address", "E-mail Address 2", "E-mail Address 3", "Web Page", "Mobile", "Mobile 2", "Mobile 3", "Home Phone", "Home Phone 2", "Business Phone", "Business Phone 2", "Other Phone", "Business Fax", "Home Fax", "Home Street", "Business Street", "Notes", "Company" };//the order of fields is very important
            string contact = "";
            DataTable resTb = new DataTable("ContactsTb");
            resTb.Columns.Add("ContactInfo");
            for (int iLoop = 0; iLoop < tb[KLoop].Rows.Count; iLoop++)
            {
                contact = ";";
                DataRow dr = resTb.NewRow();
                for (int jLoop = 0; jLoop < ContctInfoCols.Length; jLoop++)
                {
                    if (ContctInfoCols[jLoop] != "" && tb[KLoop].Rows[iLoop][ContctInfoCols[jLoop]].ToString() != "")
                        contact += (jLoop + 1).ToString() + "~" + tb[KLoop].Rows[iLoop][ContctInfoCols[jLoop]].ToString().Replace("/", "") + ";";
                }
                dr["ContactInfo"] = contact;
                resTb.Rows.Add(dr);
            }
            if (QueriesDataHelper.ImportContacts(resTb, int.Parse(TxtHiddenGroupId.Value), Session)) // here is the stored procedure call 
            {
                importedRowsCount += resTb.Rows.Count;
                var script = "DisplayProgressMsg(" + importedRowsCount + ")";
                ScriptManager.RegisterStartupScript(this, GetType(), "MyScript", script, true); // here i am trying to display the currently inserted rows but it's not working
                if (KLoop == tb.Count - 1)
                    MessageBox.Show(importedRowsCount.ToString()+" Contacts imported successfully");
            }
            //else
            // MessageBox.Show("Sorry,an error occured during contacts upload.");
        } 

     function DisplayProgressMsg(msg) {
        alert(msg);
     }

Upvotes: 1

Views: 1111

Answers (1)

ta.speot.is
ta.speot.is

Reputation: 27214

The upload is taking too much time and I want to display progress for the number of rows that have been inserted. Is there anyway to do that?

It depends on how you're inserting the data.

sent to the database to be inserted by a stored procedure.

If you're using SqlDbType.Structured parameters you might take note of how far along enumerating the parameter's IEnumerable<SqlDataRecord> value you are.

If you're calling a stored procedure once per row then count how many stored procedure calls you've made so far.

If you're using SqlBulkCopy you can use the SqlRowsCopied event to receive feedback on the number of rows inserted.

If you're using SqlDataAdapter you can look at the RowUpdated and RowUpdating events.

Using something else? Read the fine manual for what feedback options are available to you.

Although you might be missing the real problem:

several hundreds rows ... taking too much time

Review how you're inserting the rows, you should be able to insert thousands of rows per second which could render your need to provide feedback moot.

Upvotes: 1

Related Questions