將數據從Excel移動到SQL Server時C# - NullReferenceException

c# excel model-view-controller sqlbulkcopy

我正在開發一個Web應用程序,我必須使用C#和ASP.NET MVC從給定的Excel文件導入SQL Server中的數據。為此,我遵循了這篇文章。所以我使用ExcelDataReader來讀取Excel文件。此外,我在我的代碼中使用了SqlBulkCopy將數據插入數據庫。以下是我的代碼:

Create方法

var bData = getBillData();

var connString = ConfigurationManager.ConnectionStrings["WASABill"].ConnectionString;
DataTable table = new DataTable();

using (var reader = ObjectReader.Create(bData))
{
    table.Load(reader);
}

using (SqlBulkCopy bcp = new SqlBulkCopy(connString))
{
                bcp.ColumnMappings.Add("AccountNo", "AccountNo");
                bcp.ColumnMappings.Add("BillNo", "BillNo");
                bcp.ColumnMappings.Add("Category", "Category");
                bcp.ColumnMappings.Add("Billing_Period", "Billing_Period");
                bcp.ColumnMappings.Add("Name", "Name");
                bcp.ColumnMappings.Add("Address", "Address");
                bcp.ColumnMappings.Add("Issue_Date", "Issue_Date");
                bcp.ColumnMappings.Add("Due_Date", "Due_Date");
                bcp.ColumnMappings.Add("Water_Bill", "Water_Bill");
                bcp.ColumnMappings.Add("Sewerage_Bill", "Sewerage_Bill");
                bcp.ColumnMappings.Add("Aquifer_Charges", "Aquifer_Charges");
                bcp.ColumnMappings.Add("Current_Amount", "Current_Amount");
                bcp.ColumnMappings.Add("Arrears", "Arrears");
                bcp.ColumnMappings.Add("Service_Charges", "Service_Charges");
                bcp.ColumnMappings.Add("Payable_within_DueDate", "Payable_within_DueDate");
                bcp.ColumnMappings.Add("Surcharge", "Surcharge");
                bcp.ColumnMappings.Add("Payable_after_DueDate", "Payable_after_DueDate");
                bcp.ColumnMappings.Add("Payment_History_1", "Payment_History_1");
                bcp.ColumnMappings.Add("Paid_1", "Paid_1");
                bcp.ColumnMappings.Add("Payment_History_2", "Payment_History_2");
                bcp.ColumnMappings.Add("Paid_2", "Paid_2");
                bcp.ColumnMappings.Add("Payment_History_3", "Payment_History_3");
                bcp.ColumnMappings.Add("Paid_3", "Paid_3");
                bcp.ColumnMappings.Add("Area", "Area");
                bcp.ColumnMappings.Add("Water_Rate", "Water_Rate");
                bcp.ColumnMappings.Add("Sewerage_Rate", "Sewerage_Rate");
                bcp.ColumnMappings.Add("Discharge_Basis", "Discharge_Basis");
                bcp.ColumnMappings.Add("Pump_Size", "Pump_Size");
                bcp.ColumnMappings.Add("Ferrule_Size", "Ferrule_Size");
                bcp.ColumnMappings.Add("Meter_Type", "Meter_Type");
                bcp.ColumnMappings.Add("Meter_Status", "Meter_Status");
                bcp.ColumnMappings.Add("Last_Readin", "Last_Readin");
                bcp.ColumnMappings.Add("Current_Reading", "Current_Reading");
                bcp.ColumnMappings.Add("Water_Aquiffer_Charges", "Water_Aquiffer_Charges");
                bcp.DestinationTableName = "WASA_Bill_Detail";
                bcp.WriteToServer(table);                
}

var rowCount = table.Rows.Count; //Number of rows in data table
//if (ModelState.IsValid)
//{
//    db.WASA_Bill_Detail.Add(wASA_Bill_Detail);
//    db.SaveChanges();
//    return RedirectToAction("Index");
//}
TempData["RowCount"] = rowCount;
return RedirectToAction("Index");

讀取Excel文件並將數據作為列表返回的方法

public IEnumerable<WASA_Bill_Detail> getBillData()
{
        List<WASA_Bill_Detail> billDetaileList = new List<WASA_Bill_Detail>();
        //string path = @TempData["FilePath"].ToString();//@"E:\W317.xlsx";
        string path = TempData["FilePath"].ToString();
        string excelpath = Server.MapPath(path);
        if(path!=null)
        {
            var excelData = new ExcelData(excelpath);
            var billRecords = excelData.getData("Sheet1");
            foreach (var row in billRecords)
            {
                var billDetail = new WASA_Bill_Detail()
                {
                    AccountNo = row["ACCOUNT#"].ToString(),
                    BillNo = row["BILLNO"].ToString(),
                    Category = row["CATEGORY"].ToString(),
                    Billing_Period = row["BILLING_PERIOD"].ToString(),
                    Name = row["NAME"].ToString(),
                    Address = row["ADDRESS"].ToString(),
                    Issue_Date = row["ISSUE_DATE"].ToString(),
                    Due_Date = row["DUE_DATE"].ToString(),
                    Water_Bill = row["WATER_BILL"].ToString(),
                    Sewerage_Bill = row["SEWERAGE BILL"].ToString(),
                    Aquifer_Charges = row["AQUIFER"].ToString(),
                    Current_Amount = row["CURRENT AMOUNT"].ToString(),
                    Arrears = row["ARREARS"].ToString(),
                    Service_Charges = row["SERVICE CHARGES"].ToString(),
                    Payable_within_DueDate = row["PAYABLE WITHIN DUEDATE"].ToString(),
                    Surcharge = row["SURCHARGE"].ToString(),
                    Payable_after_DueDate = row["AFTER DUE DATE"].ToString(),
                    Payment_History_1 = row["PAY HISTORY 1"].ToString(),
                    Paid_1 = row["PAID 1"].ToString(),
                    Payment_History_2 = row["PAY HISOTRY 2"].ToString(),
                    Paid_2 = row["PAID 2"].ToString(),
                    Payment_History_3 = row["PAY HISOTRY 3"].ToString(),
                    Paid_3 = row["PAID 3"].ToString(),
                    Area = row["AREA"].ToString(),
                    Water_Rate = row["WATER RATE"].ToString(),
                    Sewerage_Rate = row["SEWER RATE"].ToString(),
                    Discharge_Basis = row["DISCHAGE"].ToString(),
                    Pump_Size = row["PUMP SIZE"].ToString(),
                    Ferrule_Size = row["FERRULE SIZE"].ToString(),
                    Meter_Type = row["METER TYPE"].ToString(),
                    Meter_Status = row["METER STATUS"].ToString(),
                    Last_Readin = row["LAST READING"].ToString(),
                    Current_Reading = row["CURRENT READING"].ToString(),
                    Water_Aquiffer_Charges = row["AQUIFER CHARGES"].ToString(),
                };
                billDetaileList.Add(billDetail);
            }
        }            
        return billDetaileList;
}

在我的開發機器上一切正常。文件正確上傳,然後使用bcp插入數據庫。

但是當我將其發佈到託管服務器時,發生了NullReferenceException

WASAWeb.Controllers.AdminControllers.WASA_Bill_DetailController.getBillData() +128 

我無法理解,因為它在我的開發機器中100%正常工作。我已檢查該文件是否已正確上傳到服務器。

對此有何幫助?

熱門答案

你可以用這個:

private string GetStringValue(object obj)
{
    string str = null;
    if(obj != null)
        str = obj.ToString().Trim();
    return str;
}

呼叫

......
AccountNo = GetStringValue(row["ACCOUNT#"])
......


許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因