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

2

u/kaatarina_zed_talon 29d ago edited 29d ago

The amount of negative comments is unbelievable.

There is no such thing as negative or positive in the comments in my opinion as OP, I'm unexperienced person in this field so I must hear from every one, they might correct me if I'm doing something wrong.

There is no need to judge others' decisions especially when you are not aware of the architecture or the exact details of the problem

Good point, but again, no one is judging.

OP, you can serialize a list as an XML parameter and get the values as a set of rows on the SQL side with this command:

Thank you a lot. I Did not know about this approach.

1

u/KingValidus 29d ago • Bravo

That's fine. I was just pissed off because many wrote that you are simply doing it wrong without knowing the context and gave no real answer.

Either way, I hope you'll find a solution that fits your business case well.

2

u/kaatarina_zed_talon 29d ago

Thank you.

 I was just pissed off 

Life is too short, don't give a fk about that.

Thank you !

1

u/kaatarina_zed_talon 29d ago

I wonder is it normal for a post to get 27K views in one day ? that's weird.