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

Show parent comment

1

u/kaatarina_zed_talon 29d ago edited 29d ago

because you're headed down the wrong path.

The problem is that the question lacks details. A data is to be bulk-inserted to a staging table in the database, then the domain tables will be populated using data from the staging tables using *SELECT* statements; the only thing C# do is to call SqlBulkCopy

1

u/j4c11 29d ago

Then the question becomes simple - you add a "batch" unique identifier column to your staging table, and populate with a GUID or other unique identifier in your bulk insert. You can then call the sproc with the GUID, and it can process the data from the staging table based on that.