how to pass DataTable with less columns to stored procedure when user defined table type in SQL server has default values of all columns set to null

Question

I have a stored proc which accepts user defined table type and default values for all the columns in the user defined data type is set to null. Now i am passing a dataTable with less columns to stored procedure from c# code expecting that the values for remaining columns will be set to null.

But i am getting this error: Trying to pass a table-valued parameter with 21 column(s) where the corresponding user-defined table type requires 77 column(s). This is the code

  SqlConnection conn = new SqlConnection("server=****; database=***;integrated security=SSPI");
  DataSet dataset=new DataSet();
  conn.Open();
  SqlCommand cmd = new SqlCommand("Insert");      
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Connection = conn;
  SqlParameter para = new SqlParameter();
  para.ParameterName = "@TableVar";
  para.SqlDbType = SqlDbType.Structured;
  //SqlParameter para=cmd.Parameters.AddWithValue("@TableVar",table);
  para.Value = table;
  cmd.Parameters.Add(para);
  cmd.ExecuteNonQuery();

Answer 1

You can use a SqlDataAdapter to create a DataTable matching the table type schema:

DataTable table = new DataTable();

// Declare a variable of the desired table type to produce a result set with it's schema.
SqlDataAdapter adapter = new SqlDataAdapter("DECLARE @tableType dbo.UserDefindTableType 
                                             SELECT * FROM @tableType", ConnectionString);

// Sets the DataTable schema to match dbo.UserDefindTableType .
adapter.FillSchema(table, SchemaType.Source);

You can then create DataRows with the all the default column values and just set the columns you know about:

DataRow row = table.NewRow();

// Set know columns...
row["ColumnName"] = new object();

// or check column exists, is expected type etc first
if (table.Columns.Contains("ColumnName") 
    && table.Columns["ColumnName"].DataType == typeof(string)) {
    row["ColumnName"] = "String";
}

table.Rows.Add(row);