CODEWITHSUNDEEP
c#table-valued-parameters
Rash
Rash

Reputation: 330

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

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

Upvotes: 0

Views: 891

Answers (1)

nvoigt
nvoigt

Reputation: 77364

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>();
}

Upvotes: 1

Related Questions