Sending Null values to User Defined Data table from C#

Question

I have two user defined data tables in stored Procedure for inserting the list of projectid's and AccountId's.

From C# I am sending the list of values but only one list will go at a time and another one is null.

Example: working on Projectid details then ProjectId's list will go at that time accountid's list have null.

Same as working with AccountID details then AccountId's list will go at that time ProjectId's list have null.

public string SubmitGroupInfo(GroupSettingsInputs finalinput)
{
    string result = string.empty;

    List<ProjectIDinputList> ProjectIdDetails = new List<ProjectIDinputList>(finalinput.ProjectIds); 

    // Here I am getting finalinput.ProjectIds is null.
    List<AccountIDinputList> AccountIdDetails = new List<AccountIDinputList>(finalinput.AccountIds);

    DataTable projectlist = ToDataTable(ProjectIdDetails);
    DataTable accountlist = ToDataTable(AccountIdDetails);

    dbcmd = this.sqlConn.GetStoredProcCommand("SPName");
    dbcmd.CommandType = CommandType.StoredProcedure;
    dbcmd.CommandTimeout = 240;

    this.sqlConn.AddParameter(dbcmd,"@ProjectIds",SqlDbType.Structured,projectlist);
    this.sqlConn.AddParameter(dbcmd,"@AccountIds",SqlDbType.Structured,accountlist);

    using (IDataReader datareader = this.sqlConn.ExecuteReader(dbcmd))
    {
        while (datareader.Read())
        {
            result = Convert.ToString(datareader["result"]);
        }
    }

    return result;
}

This code I added sample one only.

Finally I am getting DbNull value from parameter '@ProjectIds' is not supported. Table-valued parameters cannot be DBNull.

Can anyone please let me know how to handle null values while sending to the stored procedure for user defined tables.

score 1 · Answer 1 · answered Sep 20 '22 at 18:15

1

You can’t pass null value as table values parameter. You could pass the parameter without any rows and then check in store procedure is rows are present or not.

answered Sep 20 '22 at 18:15

Vivek Nuna

25,472
25
109
197

score 1 · Answer 2 · answered Sep 20 '22 at 18:16

1

Avoid adding the parameter at all if it is null. This way it will default to an empty table.

I believe this has already been answered here: Binding empty list or null value to table valued parameter on a stored procedure (.NET)

answered Sep 20 '22 at 18:16

Cristian Rusanu

452
5
15

Sending Null values to User Defined Data table from C#

2 Answers2