0

I'm using C#.Net and the latest version of Dapper. I just started getting a null reference exception when Dapper reads a record from the database, but the field that it's trying to retrieve is null. What can I do about it?

What follows is what I think is the pertinent code:

        using (DapperInit.conn)
        {
            try
            {
                ProjectRollup projectRollup = new ProjectRollup();

                // get the Jobs in this Project
                string sql = "select JobID, JobName, TaxRate, ScheduledStart from [Jobs] where ProjectID = @ProjectID";
                IEnumerable<JobForRollup> jobs = DapperInit.conn.Query<JobForRollup>(sql, new { ProjectID = projID });

                // get the Discount rate for the project
                sql = "select Discount from [Projects] where ProjectID = @ProjectID";
                projectRollup.Discount = DapperInit.conn.Query<int>(sql, new { ProjectID = projID }).FirstOrDefault();

Here's what the ProjectRollup class looks like (if it matters):

public class WorkItemForRollup
{
    public Guid TaskID { get; set; }
    public string Description { get; set; }
    public bool Taxable { get; set; }
    public decimal Price { get; set; }
    public DateTime ScheduledDate { get; set; }
}

public class JobForRollup
{
    public Guid JobID { get; set; }
    public string JobName { get; set; }
    public decimal TaxRate { get; set; }
    public decimal Price { get; set; }
    public DateTime ScheduledStart { get; set; }
    public List<WorkItemForRollup> WorkItems { get; set; } = new List<WorkItemForRollup>();
}

public class ProjectRollup
{
    public decimal Total { get; set; }
    public int Discount { get; set; }
    public List<JobForRollup> Jobs { get; set; } = new List<JobForRollup>();
}

I'm successfully getting the jobs data from Dapper, and there is a record such that my ProjectID = projID, but the Discount field happens to be null. What can I do to prevent getting the null reference exception, because Discount is a nullable field? (Btw, it doesn't matter if I declare Discount to be int? - I still get the exception.)

Barry Dysert
  • 665
  • 1
  • 9
  • 21

1 Answers1

3

I just got it! I need to declare Discount to be an int? and I also need to tell Dapper that the datatype is int?. The offending line of code, therefore, must be:

                int? discount = DapperInit.conn.Query<int?>(sql, new { ProjectID = projID }).FirstOrDefault();

It works!

Barry Dysert
  • 665
  • 1
  • 9
  • 21
  • It's worth noting that your `discount` variable will be null in two different cases: 1) the query returns a value that happens to be null (i.e., the first item in the returned list is null), or 2) your query doesn't return anything (so `FirstOrDefault` returns the default of null). What is the type of `Discount` in your database? Is it nullable? – Flydog57 Nov 16 '18 at 20:49
  • Yes, in the database, Discount is a nullable int. I have found a better solution, though, and it's to use IsNull(Discount, 0) in my Select statement. That way, I don't get the exception, and my Discount variable gets set to 0 if it's' null (which is what I want anyway). – Barry Dysert Nov 17 '18 at 11:52