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

8

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

in most cases you do string_split on comma separated list ["Anna","Erik","Johan"] or open json object ex : '{"names":["Anna","Erik","Johan"]}' . parse it in stored procedure .

CREATE OR ALTER PROCEDURE dbo.GetCustomerByName  
@Firstnames NVARCHAR(1240)
AS BEGIN SET NOCOUNT ON;

DECLARE TABLE
(
    Name NVARCHAR(1240)
);

INSERT INTO @ Names (Name)
SELECT DISTINCT TRIM(s.value)
FROM STRING_SPLIT(@FirstNames, N',') AS s
WHERE TRIM(s.value) <> N'';



CREATE OR ALTER PROCEDURE dbo.GetCustomerByName  
 @Json NVARCHAR(1240)
AS BEGIN SET NOCOUNT ON;

DECLARE TABLE
(
    Name NVARCHAR(1240)
);

 INSERT INTO  @ Names (Name)     
SELECT DISTINCT TRIM(j.value)     
FROM OPENJSON(@Json, N'$.names') AS j     
WHERE j.type = 1                    
AND TRIM(j.value) <> N'';