Miranda's Musings

Sometimes there is a null value in a column for a database table.   When this is not a string it will cause a null exception error in the model if you do not account for that null.   Even when the data type is nullable. 

The cure for that is to alter the assigment to the variable.   like so

GenreId = (int?)(rdr.IsDBNull(0) ? null : rdr[0])

The above code says if the very first field returned is null, then to pass the value null into the assignment, otherwise to assign the value being returned.

Here is an example where I  display two more optional items  for the game when viewing the information of the game.  The first is all Genres the game may belong to.  The next one is photos that are listed for the game. Both of these are optional items meaning they may not exist so we need to account for that possibility in our method.  

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel.DataAnnotations;

namespace MyLibrary.Models
{
    public class Game
    {
        public string GameId { get; set; }
        [Required, Display(Name = "Game Name"), StringLength(200)]
        public string GameName { get; set; }
        [Required, Display(Name = "Recommended Age"), StringLength(25)]
        public string RecommendedAges { get; set; }
        [Required]
        public string Synopsis { get; set; }

        [StringLength(100)]
        public string Link { get; set; }
        [Required, Display(Name = "Time to Play"), StringLength(20)]
        public string Playtime { get; set; }
        public bool CheckedOut { get; set; }
        [Required, Display(Name = "Minimum Players")]
        public int MinPlayers { get; set; }
        [Required, Display(Name = "Maximum Players")]
        public int MaxPlayers { get; set; }
        [Required, Display(Name = "Minimum time to play")]
        public int MinPlaytime { get; set; }
 
        public Game()
        {
            GamePhoto = new HashSet<GamePhoto>();
            GameGenre = new HashSet<Genre>();
        }

        public string Email { get; set; }
        public DateTime DateChecked { get; set; }

        public virtual ICollection<GamePhoto> GamePhoto { get; set; }
        public virtual ICollection<Genre> GameGenre { get; set; }

		public Game GetGame(string GameId)
		{
			List<GamePhoto> myList = null;
			List<Genre> gList = null;
			using (SqlConnection oConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
			{
				using (SqlCommand oCmd = new SqlCommand())
				{
					try
					{
						oCmd.CommandText = "GetGameInfo";
						oCmd.CommandType = CommandType.StoredProcedure;
						oCmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
						oCmd.Connection = oConn;
						oConn.Open();
						using (SqlDataReader reader = oCmd.ExecuteReader())
						{
							Game game = new Game();
							while (reader.Read())
							{
								game.GameId = GameId;
								game.GameName = reader[0].ToString();
								game.RecommendedAges = reader[1].ToString();
								game.Synopsis = reader[2].ToString();
								game.Link = reader[3].ToString();
								game.CheckedOut = (bool)reader[4];
								game.MinPlayers = (int)reader[5];
								game.MaxPlayers = (int)reader[6];
								game.Playtime = reader[7].ToString();
								game.MinPlaytime = (int)reader[8];
							}

							using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
							{
								using (SqlCommand Cmd = new SqlCommand())
								{
									try
									{
										Cmd.CommandText = "ListGamePhotos";
										Cmd.CommandType = CommandType.StoredProcedure;
										Cmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
										Cmd.Connection = Conn;
										Conn.Open();
										using (SqlDataReader rdr = Cmd.ExecuteReader())
										{
											myList = new List<GamePhoto>();
											while (rdr.Read())
											{
												GamePhoto photo = new GamePhoto
												{
													Id = (int?)(rdr.IsDBNull(0) ? null : rdr[0]),
													PhotoName = rdr[1].ToString()
												};
												myList.Add(photo);
											}
										}
									}
									catch (Exception ex)
									{
										Errors.ErrorOccured(ex, "Inner Query - GameId= " + GameId.ToString());
										return null;
									}
									finally
									{
										Conn.Close();
										Cmd.Dispose();
									}
								}
							}
							using (SqlConnection aConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
							{
								using (SqlCommand aCmd = new SqlCommand())
								{
									try
									{
										aCmd.CommandText = "ListGameGenres";
										aCmd.CommandType = CommandType.StoredProcedure;
										aCmd.Parameters.Add("@GameId", SqlDbType.NVarChar).Value = GameId;
										aCmd.Connection = aConn;
										aConn.Open();
										using (SqlDataReader rdr = aCmd.ExecuteReader())
										{
											gList = new List<Genre>();
											while (rdr.Read())
											{
												Genre g = new Genre
												{
													GenreId = (int?)(rdr.IsDBNull(0) ? null : rdr[0]),
													GenreName = rdr[1].ToString()
												};
												gList.Add(g);
											}
										}
									}
									catch (Exception ex)
									{
										Errors.ErrorOccured(ex, "Inner Query - GameId= " + GameId.ToString());
										return null;
									}
									finally
									{
										aConn.Close();
										aCmd.Dispose();
									}
								}
							}
							game.GamePhoto = myList;
							game.GameGenre = gList;
							return game;
						}
					}
					catch (Exception ex)
					{
						Errors.ErrorOccured(ex,"GameId = " + GameId);
						return null;
					}
					finally
					{
						oCmd.Dispose();
						oConn.Close();
					}
				}
			}
		}
	}
}

To wrap it up, when your class allows for a null value, then you need to account for that in your asignment of values coming in from your SQLDataReader.