how to count number of rows affected in Table from excel file

Question

I want to track how many records are saved after uploading and importing an Excel file into my SQL database. Please can someone modify my code so that it can show the number of records stored in my table as well as a 'success' or 'failure' message?

Here is my code:

protected void btnSend_Click(object sender, EventArgs e)  {
    //file upload path
    string path = fileuploadExcel.PostedFile.FileName;
    //Create connection string to Excel work book
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\xxxxxx\Desktop\1-8-13-ct.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False";
    //Create Connection to Excel work book
    OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel
    OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]",excelConnection);
    excelConnection.Open();
    OleDbDataReader dReader;
    dReader = cmd.ExecuteReader();
    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    //Give your Destination table name
    sqlBulk.DestinationTableName = "contact";
    sqlBulk.WriteToServer(dReader);
    excelConnection.Close();
}

John Kim · Accepted Answer · 2013-08-01 12:38:10Z

1

You can use a DataTable instead of a DataReader so you can predetermine the number of rows that will be written. For example:

    protected void btnSend_Click(object sender, EventArgs e)
    {
    //file upload path
    string path = fileuploadExcel.PostedFile.FileName;
    //Create connection string to Excel work book
    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\xxxxxx\Desktop\1-8-13-ct.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False";
    //Create Connection to Excel work book
    OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
    //Create OleDbCommand to fetch data from Excel
    OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]",excelConnection);
    excelConnection.Open();
    OleDbDataReader dReader;
   // Datatable table = new DataTable();
     DataTable table = new DataTable();
    dReader = cmd.ExecuteReader();
    table.Load(dReader);
    SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
    //Give your Destination table name
    sqlBulk.DestinationTableName = "contact";
    sqlBulk.WriteToServer(table);
    excelConnection.Close();

    int numberOfRowsInserted= table.Rows.Count;// <-- this is what was written.

    string message=string.Format("<script>alert({0});</script>",numberOfRowsInserted);
    ScriptManager.RegisterStartupScript(this, this.GetType(), "scr",message , false);
    }

edited Aug 1, 2013 at 12:38

John Kim

432 gold badges4 silver badges12 bronze badges

answered Aug 1, 2013 at 11:28

Icarus

64.1k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

John Kim Over a year ago

so i am going to use your code, hope that shows something good.. :P

Icarus Over a year ago

@johnkim I just showed you how to get the number of rows but my code doesn't show anything on the page. You will have to do something useful with the numberOfRowsInserted value.

John Kim Over a year ago

yea that is what i am asking , that how to show the result or some useful info that kind i need.. :(

John Kim Over a year ago

getting these error: Error 1 The type or namespace name 'Datatable' could not be found (are you missing a using directive or an assembly reference?)

John Kim Over a year ago

and: Error 2 The name 'string' does not exist in the current context.

|

Martin · Accepted Answer · 2013-08-01 11:30:17Z

1

You could achieve this by creating a new command to read the number of rows from the sheet as so:

OleDbCommand cmd1 = new OleDbCommand("select count(*) from [Sheet1$]", excelConnection);
int rows = (int)cmd1.ExecuteScalar();

Or alternatively, do the same using the target database table.

answered Aug 1, 2013 at 11:30

Martin

16.5k1 gold badge33 silver badges51 bronze badges

1 Comment

John Kim Over a year ago

a bit more detail will be helpful, can you merge that with my code.

Community · Accepted Answer · 2017-05-23 11:56:36Z

The following hack (using reflection) is an option:

    /// <summary>
    /// Helper class to process the SqlBulkCopy class
    /// </summary>
    static class SqlBulkCopyHelper
    {
        static FieldInfo rowsCopiedField = null;

        /// <summary>
        /// Gets the rows copied from the specified SqlBulkCopy object
        /// </summary>
        /// <param name="bulkCopy">The bulk copy.</param>
        /// <returns></returns>
        public static int GetRowsCopied(SqlBulkCopy bulkCopy)
        {
            if (rowsCopiedField == null)
            {
                rowsCopiedField = typeof(SqlBulkCopy).GetField("_rowsCopied", BindingFlags.NonPublic | BindingFlags.GetField | BindingFlags.Instance);
            }

            return (int)rowsCopiedField.GetValue(bulkCopy);
        }
    }

And then use the class as follows:

int rowsCopied = SqlBulkCopyHelper.GetRowsCopied(bulkCopyObjectInYourCode);

Hope this helps.

from: https://stackoverflow.com/a/4474394/1727357

Collectives™ on Stack Overflow

how to count number of rows affected in Table from excel file

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related