Data is Null. This method or property cannot be called on Null values

Question

I'm working on an application where one can get information on movies from a database as well as add, update and delete the movies. In the database I have three tables (Movie, Genre and MovieGenre <- stores the movies and their genre/s). Everything works fine besides one thing, and that's when a movie hasn't got any genres (which should be possible).

The problem occur in the method below, and the following exception is thrown: Data is Null. This method or property cannot be called on Null values.

The reason (of course) is that the sproc returns null because the movie hasn't got any genres, but I just can't figure out how to prevent this exception being thrown. As I said, it should be possible to store a movie without storing any information of genre/s.

Thanks in advance!

The method:

public List<MovieGenre> GetMovieGenrebyMovieID(int movieID) {      using (SqlConnection conn = CreateConnection()) {         try {              SqlCommand cmd = new SqlCommand("dbo.usp_GetMovieGenreByMovieID", conn);             cmd.CommandType = CommandType.StoredProcedure;              cmd.Parameters.AddWithValue("@MovieID", movieID);              List<MovieGenre> movieGenre = new List<MovieGenre>(10);              conn.Open();              using (SqlDataReader reader = cmd.ExecuteReader()) {                  int movieGenreIDIndex = reader.GetOrdinal("MovieGenreID");                 int movieIDIndex = reader.GetOrdinal("MovieID");                 int genreIDIndex = reader.GetOrdinal("GenreID");                  while (reader.Read()) {                      movieGenre.Add(new MovieGenre {                         MovieID = reader.GetInt32(movieIDIndex),                         MovieGenreID = reader.GetInt32(movieGenreIDIndex),                         GenreID = reader.GetInt32(genreIDIndex)                     });                 }             }              movieGenre.TrimExcess();              return movieGenre;         }         catch {             throw new ApplicationException();         }     } } 

The sproc:

ALTER PROCEDURE usp_GetMovieGenreByMovieID @MovieID int AS BEGIN     BEGIN TRY         SELECT m.MovieID, g.GenreID, mg.MovieGenreID, g.Genre         FROM Movie AS m         LEFT JOIN MovieGenre AS mg             ON m.MovieId = mg.MovieID         LEFT JOIN Genre AS g             ON mg.GenreID = g.GenreID         WHERE m.MovieID = @MovieID     END TRY     BEGIN CATCH         RAISERROR ('Error while trying to receive genre(s).',16,1)     END CATCH END 

Answer 1

You shouldn't be trying to convert the null values from the proc into ints - so before you create the MovieGenre instance you need to check the nullable fields using the SqlDataReader.IsDBNull method:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.isdbnull.aspx

Assuming that the GenreID and MovieGenreID are nullable ints you could do something like:

movieGenre.Add(new MovieGenre {   MovieID = reader.GetInt32(movieIDIndex),   MovieGenreID = reader.IsDBNull(movieGenreIDIndex) ? null : reader.GetInt32(movieGenreIDIndex),   GenreID = reader.IsDBNull(genreIDIndex) ? null : reader.GetInt32(genreIDIndex) });