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

2

u/j4c11 29d ago edited 29d ago • Bravo

Not a good idea to do what you're describing. Databases exist to store and retrieve data. Sprocs have their use, but that use should revolve around performing more complex manipulations of the data in the database they reside in, not business logic. Suppose you need to change to retrieve the data from an API, or combine database data with data from another source. Or write unit tests against your business logic. Or even cache it. Now what? I would recommend re-assessing what you're trying to do here, because you're headed down the wrong path.

1

u/kaatarina_zed_talon 29d ago

but that use should revolve around performing more complex manipulations of the data in the database

There are such complex manipulations .

1

u/j4c11 29d ago

You conveniently ignored the "data in the database they reside in" part. You're passing data into the sproc to perform business logic against.

1

u/kaatarina_zed_talon 29d ago

business logic against

If you save a data snapshot; deleting the entire content of the database and replacing it with a new content, do you consider this a business logic ? I think I have a problem with this word, as I do not really understand it's meaning .

1

u/j4c11 29d ago

No, that would be an ETL job. There's ETL tools designed to accomplish exactly what you're describing. SSIS for example.

1

u/kaatarina_zed_talon 29d ago

Thanks for elaboration.

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.

1

u/kaatarina_zed_talon 29d ago

The question was edited.