User defined table type with dynamic columns in SQL Server

Question

In my .NET (C#) win application, I import an Excel file that contains multiple columns of varying numbers. So, I store the data in my datatable in code and I need to pass that datatable to a SQL Server stored procedure for further processing.

Is there any proper way to pass my datatable to a SQL Server stored procedure and can user defined table type have a dynamic number of columns?

Answer 1

if it's possible to create a table type with a dynamic number of columns.

No, its not possible.

My suggestion would be you can update the data from datatable directly to sql server table like this, then use the table in your procedure.

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

Note, datatable column name should be same as your sql table column name to get it mapped properly.

Answer 2

First of all, you can not create a table with a dynamic number of columns.

You can use JSON to store all your borrowers in a single column. You really don't need to create multiple columns for borrowers. You can simply create a column in a table that will be of size NVARCHAR(MAX) and you have to create a JSON of all borrowers and store it in a table.

While retrieving data from the table deserialize it to get in original form.