C# - NullReferenceException while moving data from Excel to SQL Server

Question

I am developing a web application in which i have to import the data in SQL Server from given Excel files using C# and ASP.NET MVC. For this purpose I followed this article. So I used ExcelDataReader to read the Excel files. Furthermore I have used SqlBulkCopy in my code to insert the data into the database. following is my code:

The Create method

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

The method which reads the Excel file and returns the data as a list

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

Everything is working fine on my development machine. File uploaded properly and then inserted into the database using bcp.

But when I publish this to the hosting server the NullReferenceException occurred at

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

I could not understand as it is working 100% fine in my development machine. I have checked that file is properly uploaded to the server.

Any help with this?

possible duplicate of [avoiding null reference exceptions](http://stackoverflow.com/questions/1943465/avoiding-null-reference-exceptions) — Mathemats, Apr 23 '15 at 05:40
i think one from you rows is null, so when you call .ToString() it return nullreference — Bondaryuk Vladimir, Apr 23 '15 at 05:44
.ToString() does not handle null values so if you need to handle this try to make Convert.ToString(value) . — Jinesh Jain, Apr 23 '15 at 08:02
Sorry for late reply. i tried this Convert.ToString(value). but no success — Mustafa Sattar, Apr 30 '15 at 12:50

score 0 · Answer 1 · answered Apr 23 '15 at 05:57

0

you can use this:

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

call

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

answered Apr 23 '15 at 05:57

Bondaryuk Vladimir

509
3
10

many thanks Bondaryuk Vladimir. I tried this also and no success yet – Mustafa Sattar Apr 30 '15 at 12:50

C# - NullReferenceException while moving data from Excel to SQL Server

1 Answers1