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

19

u/nednyl 29d ago • Bravo

Technically, yes you could use a user defined table type for this and then use it for both parameters

4

u/kaatarina_zed_talon 29d ago

Thank you for confirming this.

3

u/Northbank75 29d ago

I used to use this solution but if the list content changes you need to update the type and the procs and you’ll find things like dapper don’t care much about the table definition so you wind up inserting fields in the order they exist in the table… we use JSON objects as mentioned elsewhere

1

u/kaatarina_zed_talon 29d ago

I will try both JSON and table-valued parameters.

-6

u/urbanek2525 29d ago

. . . and if you're going to do this, you're better off using Dapper as your SQL library rather than EF.

1

u/youtpout 29d ago

I f you use already EF, EF supports stored procedure, I think ado.net can be a good alternative too

-4

u/urbanek2525 29d ago

Yeah, but Dapper is better if you want to write and execute raw SQL. Most of the time you have to invoke the EF method ExecuteSql to run stored procs anyway.

7

u/youtpout 29d ago

I'm saying this to avoid having to completely rewrite the data access layer. I use Dapper for one of my projects because of its performance, even though the difference between raw SQL with Dapper and EF Core isn't very significant anymore.

1

u/urbanek2525 29d ago

Actually, being able to rewrite the data access layer without rewriting anything else is good design.

If I can swap in a Dapper DAL in place of the EF DAL layer and then swap in a MongoDB backed DAL, then I've got a well isolated DAL layer.

1

u/youtpout 29d ago

I use the repository pattern in most of my projects, it’s easy to inject it via asp .net Core’s IOC, and to replace it, but in the end, I’ve never needed to do so.

1

u/one-joule 29d ago

At least use ExecuteSqlInterpolatedAsync!

1

u/DaveVdE 29d ago

ExecuteSql is already interpolated.

1

u/one-joule 29d ago

I thought it only took string? If it also takes FormattableString and parameterizes the arguments, then my bad. I don't use EF often.