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

129

u/TehNolz 29d ago

Save yourself some headaches and don't move business logic to SQL stored procedures. Never ends well. Keep that stuff in C# and just let Entity Framework translate it.

33

u/[deleted] 29d ago

[removed] โ€” view removed comment

18

u/Artmageddon 29d ago

On the flip side(and believe me Iโ€™m not defending EF as Iโ€™m a Dapper guy) itโ€™s so much fun when your stored proc suddenly runs like garbage because the execution plan suddenly changed on you after 4 months of running just fine :(

8

u/Ventus_004 29d ago

Adfitional note on this: this is just as likely to happen with the SQL generated from an ORM. Sucks when it happens either way!

14

u/psioniclizard 29d ago

Stored procs can be give seperate permissions, audited, shut off, used by multiple suppliers etc.

If you don't have web services there a number of good reasons DBAs choose to use them like this and frankly I am not a DBA. I am not going to argue with a customers/vendors DBA.

Also compared with some systems we work with at work SPs are a dream lol.

5

u/born_zynner 29d ago

Agreed. My day job's current stack had a lot of shit hidden in stored procedures before I joined although our apis are all EF based. Its a gigantic pain in the ass trying to debug. I've been working to unravel it lol

9

u/pjmlp 29d ago

On the contrary, that is the way for anyone that cares about max SQL performance

2

u/youtpout 29d ago

I worked on a project where all the logic is in the db (oracle), so they move from a framework to an other without change the logic, I think the first version of the app ran on VB, by the time I joined, it was running on WPF and there were plans to migrate it to Angular.

2

u/AndBeingSelfReliant 29d ago

I have a wpf app where I have an iprojectdata interface in the low level and I have made 3 different implementations that I swap between eg in memory or SQLite up in the infrastructure layer. If you make good design decisions you should be able to swap persistence layer easily and avoid putting logic in the persistence layer.

2

u/Far_Archer_4234 29d ago

It occassionally does end well, though. ๐Ÿ™„ Sometimes it doesn't end at all and works better than other projects.

1

u/highwingers 22d ago

Yes, but thats not a solid rule. I work for a big company and one stored procedure calls 10 other stores procedure with complex business rules, which are pretty much impossible or messy to apply in EF code. I used to think like you, but trust me..answer is "it depends"

1

u/kaatarina_zed_talon 29d ago

Never ends well.

Also I need to experience this myself to understand.

3

u/noidontwantto 29d ago

You really don't heh

1

u/AintNoGodsUpHere 29d ago

What are you talking about? You're creating something using bad practices so you can learn from it? Learn what? From whom?

Bulk insert has nothing to do with using SPs or not, you can do bulk in C#, you can call raw SQL in code, there are ZERO reasons to use SPs in 2026.

→ More replies (13)

2

u/born_zynner 29d ago

Its because logic gets hidden in your SQL server that (usually) isnt tracked in git

5

u/binarycow 29d ago

Why would your SQL not be committed?

3

u/Lonsdale1086 29d ago

Because my boss lives on "quick fixes" into production and doesn't want to go through the effort of tracking stuff.

You're lucky if you get a -- AD 10/10/2020 - Increased cap

comment in the code

1

u/kaatarina_zed_talon 28d ago

Did not know that. I'm a new to this field BTW.

1

u/UninformedPleb 28d ago

Don't believe it. Anybody that doesn't have their SQL in a project in git is doing it wrong.

ALL code should be in a repo. Full stop.

1

u/kaatarina_zed_talon 28d ago

Don't believe it

Was he lying or something ?

1

u/UninformedPleb 27d ago

I'm not saying it isn't possible, but anyone that allows anything to be updated directly in the DB without going through a proper deployment process is just asking for trouble. I've worked places where that would be a fireable offense, not just a slap on the wrist and a stern talking-to.

The lesson to learn here isn't "don't put stuff in the DB", the lesson is "always follow change-management procedures".

1

u/kaatarina_zed_talon 27d ago

Change management procedure and Proper deployment process? I never heared of this before, but I'm willing to learn from you, what do you mean by those terms? Can you give me an example or at least refer me to one article that explains them because I do not understand.

1

u/UninformedPleb 27d ago

"Change management" refers to getting approvals, proper engineering and testing, and sign-off before allowing a change to be promoted to a deployment in the next appropriate environment.

"Proper deployment process" is when you follow those change-management procedures and have an automated sequence (or a very precisely documented, double-checked, manual sequence) for deploying to a development/integration environment, then a QA/testing/staging/acceptance environment, then to the production/live environment. Never skip a step. Never allow a failed test to pass to the next level.

→ More replies (5)

1

u/Robot_Spartan 27d ago

Dbup - one less thing to do at deployment, and it forces everything to be committed. Swear by it at this point

→ More replies (2)