如何计算表中从excel文件中受影响的行数?

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?

我希望跟踪上传和导入Excel文件到SQL数据库中保存了多少记录。请有人修改我的代码，以便它能显示存储在我的表中的记录的数量，以及“成功”或“失败”的信息?

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();
}

3 个解决方案

#1

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

您可以使用DataTable而不是DataReader，这样您就可以预先确定将要写入的行数。例如:

    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);
    }

#2

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.

或者，也可以使用目标数据库表执行相同的操作。

#3

The following hack (using reflection) is an option:

下面的hack(使用反射)是一个选项:

    /// <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://*.com/a/4474394/1727357

来自:https://*.com/a/4474394/1727357

#1