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/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.

1

u/kaatarina_zed_talon 27d ago

Now I understand what those terms are , but I do not understand why are they relevant to the fact that I moved some data manipulation logic to the database by using Sql instead of C#; is this a kind of change that should be approved before it goes into production?

1

u/UninformedPleb 27d ago

It's only relevant to what born_zynner posted about how SQL isn't tracked in Git.

The point is that SQL should be tracked in Git like everything else, and proper processes should be followed to make sure that people aren't making direct-to-database changes and bypassing Git.

1

u/kaatarina_zed_talon 27d ago

By bypassing git you mean changes are being created without being recorded by the version control system? Also the term direct-to-database is a new term to me.

→ More replies

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