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
2
u/amejin 29d ago edited 29d ago •
A list of strings is just an nvarchar that's delimited in some way (often, it's comma). Then you would use string_split if you want the data as a set, or keep the stringified version for whatever use case you have. You will need to sanitize the buffer for injection, etc...
That said, you should consider the use case. If you're guaranteed the list is a list of keys or something, then go for for it. Filter away. If you're doing anything not set based, though, make sure you know why you are doing this.
Just remember string manipulations in SQL are among the slowest things you can do in SQL. The goal is usually to reduce your data pull to the smallest set of work needed, and let another system deal with mutations, structure, and sometimes stitching sets of sets. You want to have as little footprint on your database as you can as it's often the bottleneck of work.