将数据从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
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow