Why I'm retrieving null values and new line characters from SQLite database?

264 views Asked by At

I imported a dataset from a CSV file into a SQLite database 3.1.1. I'm managing that database through SQLiteStudio. I'm suppose to retrieve 7,332 values at runtime in my C# code; not the 7,340 values I'm retrieving.

Problem:

I'm checking every row to see if the value being return is null. However, there are no null values in the dataset or any empty rows. I'm retrieving more rows than expected 7,340 rows versus 7,332 from the dataset. I'm also retrieving a row that in the column 'NAME' contains this value: "\n"..

The code works fine; however where does this "\n" value comes from? Why it is messing up my data with null values and with "\n" values?

Solution Expected

The original code didn't have to check for nulls with the old dataset, as again; the data does not contain null values. Now that I imported a new dataset I'm having this problem. How can I get rid of the null values when retrieving and reading the data through the ExecuteReader()?

Code:

   using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
        {
            conn.Open();
            using (SQLiteCommand cmd = conn.CreateCommand())
            {
                cmd.CommandText = "SELECT NAME, STATE_FIPS, COUNTY_FIPS, BEDS, ICU_BEDS FROM Hospitals";
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        string name = Convert.ToString(reader.GetString(0));
                        int? stateFIPS = (Convert.IsDBNull(reader.GetValue(1)) ? 0 : Convert.ToInt32(reader.GetValue(1)));
                        int? countyFIPS = (Convert.IsDBNull(reader.GetValue(2)) ? 0 : Convert.ToInt32(reader.GetValue(2)));
                        int? numBeds = (Convert.IsDBNull(reader.GetValue(3)) ? 0 : Convert.ToInt32(reader.GetValue(3)));
                        int? numICUBeds = (Convert.IsDBNull(reader.GetValue(4)) ? 0 : Convert.ToInt32(reader.GetValue(4)));

                        hospitals.Add(new Hospital()
                        {
                            Name = reader.GetString(0),
                            StateFIPS = Convert.ToInt32(stateFIPS),
                            CountyFIPS = Convert.ToInt32(countyFIPS),
                            NumBeds = Convert.ToInt32(numBeds),
                            NumICUBeds = Convert.ToInt32(numICUBeds),

                        });

                    }
                    reader.Close();
                }
            }
            conn.Close();

How I'm managing the import from SQLite

enter image description here

0

There are 0 answers