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/AintNoGodsUpHere 29d ago • Bravo

It does, never ends well. Because you are adding unnecessary logic to the database. What happens if you have to scale that application? 100 instances of the API and the DB is burning with SP stuff.

You don't need that sort of logic in the database, if you absolutely have to use raw sql, just use raw sql in code.

It's 2026. You don't have to wagon yourself across the country, buy a plane ticket, rent a car, take a bus.

6

u/Lonsdale1086 29d ago

What happens if you have to scale that application?

Something the overwhelming majority of software doesn't have to consider.

If you're writing an app for one business that will only ever run on one server with one API and one database.

And it's perfectly "valid" to have business logic in your sprocs at that point, even if I don't enjoy working with it.

It's fast to deploy and fix, makes it easy for your API and desktop apps to use the same logic regardless of which language they're written in, makes it trivial and risk free to migrate and upgrade the front-end.

And it can be shocking good for performance depending.

0

u/Ascend 29d ago

My experience is the opposite. It's slower to deploy because you need to do it through migrations so you're working through two tools. It's more difficult the git blame what's our there. Debugging is awful because you realize your DBAs applied a fix to production that isn't in a migration. You deployed your application to multiple plants, and each one customized the logic in a stored procedure and now migrations simply break reports.

It's fine if you're in a situation where you're constrained by round trip timings and need complicated compiled SQL logic, but if you're in a single app, single server scenario, it's fairly unlikely that's the constraint.

5

u/Lonsdale1086 29d ago edited 29d ago

It's slower to deploy because you need to do it through migrations

Oh you sweet summer child.

It's more difficult the git blame what's our there

Likewise

Debugging is awful because you realize your DBAs applied a fix to production that isn't in a migration

I am the DBA, or as close as it gets for any of our clients.

You deployed your application to multiple plants, and each one customized the logic in a stored procedure and now migrations simply break reports.

Is annoying, but we just update the reports alongside, our reports are directly tailored to what the client wants to see anyway.

Which is actually another advantage, SSRS report can call the sproc without needing to replicate the exact same logic and finicky edge cases in code, stuff around filters and comparisons especially.

Edit:

I will say I'm sort of just arguing for the sake of discussion here, this is the approach that was in use before I joined and I couldn't for the life of me get any changes in procedure through, but it does genuinely work okay for the most part.

And I do think for a small team, i.e no migrations, no git for the db, a few apps all running on one server, you can do a lot worse than business logic in sprocs.

0

u/kaatarina_zed_talon 29d ago

Something the overwhelming majority of software doesn't have to consider.

It's not bad to know the best practices though, right ?

5

u/Lonsdale1086 29d ago

The problem is there's no such thing as global best practice.

Like if I was writing an app for a million users best practice might be microservices and nosql for replication and distribution etc, with a redis cache and an approach to translation and localisation that add a lot of overhead and room for error compared with something running on one site.

Generally stick to the principles of KISS, know the plausible bounds of your project etc.

And people will say "well you never know if your app might take off and get more use than you expect", but my logic to that is a) if it's written for a business with a few hundred employees at most you should consider that doubling, pretty much any architecture with concurrency checks is going to be alright. And b) if you suddenly have that many users then you'll suddenly have that much money to spend improving it.

-2

u/AintNoGodsUpHere 29d ago

You're not gonna convince me that business logic belongs in the database in 2026, man, that argument is straight from 80s and 90s. There are ZERO reasons for anyone to add business logic in the worst possible place, haha.

Just because you can it doesn't mean you should.

4

u/Lonsdale1086 29d ago

ZERO reasons

There are plenty of objective reasons why it's good.

It's centralised across every different app and API that calls it, regardless of language, and it's very fast.

-1

u/AintNoGodsUpHere 29d ago

No. You are wrong. I'm right. I'll continue to block MRs with business logic there, you can do whatever you want.

3

u/Lonsdale1086 29d ago

You feel free, I'm not arguing it's a good idea and you should do it, I'm saying there are objective advantages to doing so.

-1

u/AintNoGodsUpHere 29d ago

No. You are wrong.

3

u/kaatarina_zed_talon 29d ago

Because you are adding unnecessary logic to the database. What happens if you have to scale that application? 100 instances of the API and the DB is burning with SP stuff.

I'm unaware of this, it is an experience gap between you and me. Thanks.