数据为空。这个方法或属性不能被称为空值。

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.

原因(当然)是sproc返回null，因为电影没有任何类型，但是我只是想不出如何防止这个异常被抛出。就像我说的，在不存储任何类型的信息的情况下存储电影是可能的。

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:

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

3 个解决方案

#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:

您不应该尝试将proc中的null值转换为ints——因此，在创建MovieGenre实例之前，您需要使用SqlDataReader检查可空字段。IsDBNull方法:

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:

假设GenreID和MovieGenreID是空的ints，你可以这样做:

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

#2

Edit your select statement as follows to handle null issue.

编辑您的select语句，以处理null问题。

SELECT ISNULL(m.MovieID,0) AS MovieID, 
       ISNULL(g.GenreID,0) AS GenreID, 
       ISNULL(mg.MovieGenreID,0) AS MovieGenreID,
       ISNULL(g.Genre,'') AS Genre
FROM --rest of your query...

#3

The simplest answer is to replace the nulls with non-null values. Try:

最简单的答案是用非空值替换null。试一试:

ALTER PROCEDURE usp_GetMovieGenreByMovieID
@MovieID int
AS
BEGIN
    BEGIN TRY
        SELECT m.MovieID, 
               coalesce(g.GenreID,0) GenreID, 
               coalesce(mg.MovieGenreID,0) MovieGenreID, 
               coalesce(g.Genre, 'Not Applicable') 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

#1