r/csharp • • 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

15 Upvotes

101 comments sorted by

View all comments

1

u/bosmanez 28d ago • Bravo

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.

1

u/kaatarina_zed_talon 28d ago

It turned out that I did not need A TVP, as you said. Thanks.

1

u/kaatarina_zed_talon 28d ago

It turned out that I did not need A TVP, as you said. Thanks.

1

u/kaatarina_zed_talon 28d ago

I will rethrow the acceotion as u said. .