0

I am having an issue saving a select query result to a model class.

I am trying to match values taken from an excel sheet with values from a local database. I keep getting the error "Data is Null. This method or property cannot be called on Null values " or something similar. I don't think I can be passing in null as I ran a SQL query to replace all nulls with 0. I also don't care if null is passed in to these properties, so either making the fields nullable or removing the nulls would work...I think?

The following is my code:

 //initialize spreadsheet package,
        //load excel file, create dictionary of
        //naic/industry key/value pairs
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
        ExcelFile ef = ExcelFile.Load("C:/Data Sources/NAICS sheets/3-Digit-NAICS-Codes.xls");
        Dictionary<object, object> naics = new Dictionary<object, object>();

        //Connection string for local MSSQL database
        SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=gov_world;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");


        //Create array of client objects returned from query
        Client[] allRecords = null;
        string sql = "SELECT v_company, client_pk, v_naic_1, v_naic_2, v_naic_3, v_naic_4, v_naic_5 FROM  dbo._clients";

        //Iterate through elements of excel sheet, eventually inputting first column value
        //as key and second column value as value in dictionary
        foreach (ExcelWorksheet sheet in ef.Worksheets)
        {


            foreach (ExcelRow row in sheet.Rows)
            {
                object[] industry = new object[2];
                var count = 0;

                //populate industry array with all values of row
                foreach (ExcelCell cell in row.AllocatedCells)
                {
                    if (cell.Value != null)
                    {
                        industry[count] = cell.Value;
                    }
                    count++;
                }
                count = 0;

                //add values of first 2 cells to naics dictionary object
                naics.Add(industry[0], industry[1]);
                //Console.WriteLine(industry[0] + " | " + industry[1]);

                //reinitialize for every record
                industry[0] = 0;
                industry[1] = 0;

            }
        }


        //Set values from query to client model object, uses
        //to populate allRecords Array
        using (var command = new SqlCommand(sql, con))
        {
            con.Open();
            using (var reader = command.ExecuteReader())
            {
                var list = new List<Client>();
                while (reader.Read())
                    list.Add(new Client {
                        //Solution: write as v_company = reader[0] as string;
                        v_company = reader.GetString(0),
                        client_pk = reader.GetInt32(1),
                        v_naic_1 = reader.GetString(2),
                        v_naic_2 = reader.GetString(3),
                        v_naic_3 = reader.GetString(4),
                        v_naic_4 = reader.GetString(5),
                        v_naic_5 = reader.GetString(6)

                    });
                allRecords = list.ToArray();

            }
        }


        Console.WriteLine(allRecords[0].v_naic_1);
        Console.ReadLine();

I can figure the match out, at this point I just want the "list.Add(new Client..." to work so that the while loop completes.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
Ryan Gedwill
  • 31
  • 6
  • You should be using EF with MVC, but that is not the issue, do you know which is the line which throws error? Or stack trace or anything further? Also is all your column types string/varchar? – Rohit Vipin Mathews Jan 19 '17 at 23:45
  • yes they are string/varchar except for the id. Wanted to use EF but just copy pasted boilerplate code because I'm in a bit of a rush and not to proficient in either. The line that throws the error is the "while(reader.read())list.Add....etc." Looked through stack trace, not sure how to post relevant stuff here. It's coming up with an empty enumerable set after about 150,000 entries. – Ryan Gedwill Jan 19 '17 at 23:46
  • "or something similar" - why not quote the exact error message to us? If you don't have it up right now run your program again and transcribe it. A full accurate error message along with what line its on (and perhaps a full stack trace) would make debugging this a lot easier. I'm guessing this is probably a duplicate of http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it but can't be sure at this stage without the correct error message. – Chris Jan 19 '17 at 23:49
  • @Chris Sorry should have been more clear. What I quoted is exactly the error message. What I meant is that I troubleshooted a lot and got similar errors but cannot repeat them exactly. I looked through stack trace but not sure how to post here? Going to check out link you posted now – Ryan Gedwill Jan 19 '17 at 23:50
  • I'm sure nullreferenceexception has something to do with it, but what is most confusing is that the list gets about 150,000 entries added before error is thrown – Ryan Gedwill Jan 19 '17 at 23:53

1 Answers1

1

You need to check for IsDBNull for all your values:

if(!reader.IsDBNull(0))
{
  employee.FirstName = sqlreader.GetString(0);
}

Or you can use it as 

reader[0] as string

Also it would be better to use column names instead of index values, you can get the column index from column name as follows :

int colIndex = reader.GetOrdinal(fieldname);

You can find more information on how to read data from a reader and better approaches by reading through all the answers of this question.

Community
  • 1
  • 1
Rohit Vipin Mathews
  • 11,629
  • 15
  • 57
  • 112