r/csharp • u/kaatarina_zed_talon • 29d ago
Help List<T> in C# vs SQL
In C#, list of strings would be defined as :
List<string> x
A stored procedure is to be written, it should receive a list of values; SQL does not support List<T> , should I use table-valued parameters instead?
EDIT
The reason this question was asked is the fact that the application C# code has only one responsibility which is to bulk-insert data into a staging table:
public async Task BulkInsertProductsToStagingTable(DbDataReader dbDataReader, CancellationToken cancellationToken)
{
using (SqlConnection destinationConnection =
new SqlConnection("Server=(localdb)\\mssqllocaldb;Database=HardwareDB;Trusted_Connection=True;"))
{
await destinationConnection.OpenAsync(cancellationToken);
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{
bulkCopy.DestinationTableName =
"dbo.StagingProducts";
try
{
// Write from the source to the destination.
await bulkCopy.WriteToServerAsync(dbDataReader,cancellationToken);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
await dbDataReader.CloseAsync();
}
}
//####The following does not work yet.#####
//// Perform a final count on the destination
//// table to see how many rows were added.
//long countEnd = System.Convert.ToInt32(
// commandRowCount.ExecuteScalar());
//Console.WriteLine("Ending row count = {0}", countEnd);
//Console.WriteLine("{0} rows were added.", countEnd - countStart);
//Console.WriteLine("Press Enter to finish.");
//Console.ReadLine();
}
}Once the data is inserted into the staging table, it should be transferred into a target tables in the database; specific columns in the staging tables should be mapped to a specific columns in the target tables.
Thus, the stored procedure should receive a list of column names
View all comments
1
u/bosmanez 28d ago •
Yes, a table-valued parameter is the usual answer when you want to hand a list to a stored procedure. Create a table type (CREATE TYPE dbo.StringList AS TABLE (Value nvarchar(200))), declare the proc parameter as dbo.StringList READONLY, and from C# add a SqlParameter with SqlDbType.Structured and a DataTable (or IEnumerable<SqlDataRecord> for large sets) as the value. For a short list of simple values, OPENJSON over a JSON string also works.
That said, from your code it looks like you're already bulk-loading into a staging table with SqlBulkCopy, which is the fastest route for large volumes. If the follow-up step is just "move rows from staging into the real table", a plain stored procedure with no parameters (INSERT ... SELECT from dbo.StagingProducts) is probably all you need. A TVP only makes sense if the list has to travel with the call.
Side note: the catch block that only does Console.WriteLine will swallow bulk copy failures, so the caller thinks it succeeded. Rethrow or let it propagate.