Null Reference Exception for Table Value Parameter while executing the stored procedure in C#

Question

I am trying to execute a stored procedure with table valued parameters. These parameters can be null, because of which my code is throwing null reference exception. Is there a way I can pass nullable table valued parameters. Please advise.

public IEnumerable<ReportFilter> GetReportFilter(ReportInputDefinition criteria, Guid tenantId)
    {
        IEnumerable<ReportFilter> ReportFilterData;
        IEnumerable<DomainTableType> domainTableTypes=null;
        if (criteria.Domains != null && criteria.Domains.Any())
        {
            domainTableTypes = criteria.Domains.Select(d => new DomainTableType(d));
        }
        using (var connector = GetConnector(ConnectionNames.DefaultConnection))
        {
            reportFilterData = connector.StoredProcedure("SpName")
                .TableValuedParameter("@TableValuedParameter1", domainTableTypes)
                .Parameter("@TenantID", tenantId)
                .As<ReportFilter>()
                .GetRows();
        }
        return reportFilterData;
    }

In the above code, I am getting null reference exception for domainTableTypes because sometime it can have value and sometimes not. This question isn't duplicate as I have issue with table valued parameter. I am aware how to deal with regular parameters. That solution didn't work for me :(

Can you post the full stack trace? `domainTableTypes` being null should not be a problem in _your_ code. — D Stanley, May 11 '16 at 14:56
@Stanely: sorry for security reasons , I can't provide stack trace here. But If I provide some value, the code works, so it is the null table type parameter which is causing issue. — Rash, May 11 '16 at 15:54
It's not clear to me whether you are getting a Sql error or a .Net exception. What does the `TableValuedParameter` method do? — Chris Dunaway, May 11 '16 at 16:08
@Harry, Chris: In DB, it columns are nullable, I was getting a .Net exception --exceptionMessage": "Object reference not set to an instance of an object." at domainTableTypes. But the below solution worked for me. — Rash, May 11 '16 at 16:28

score 1 · Accepted Answer · answered May 11 '16 at 15:01

As far as I can remember, you cannot pass NULL to a table type. You have to pass an empty table. I apologize in advance if this is database system specific, but maybe you are using the same DBMS.

if (criteria.Domains != null && criteria.Domains.Any())
{
    domainTableTypes = criteria.Domains.Select(d => new DomainTableType(d));
}
else 
{
    domainTableTypes = Enumerable.Empty<DomainTableType>();
}

Null Reference Exception for Table Value Parameter while executing the stored procedure in C#

1 Answers1