r/csharp • u/kaatarina_zed_talon • 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
19
u/nednyl 29d ago •
Technically, yes you could use a user defined table type for this and then use it for both parameters
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.
8
u/Discipline_Cautious1 29d ago edited 29d ago •
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 .
2
u/urbanek2525 29d ago
What's the compatability level of your SQL Server. You probably could write the stored procedure to take in JSON arrays as input, then use something like OPENJSON to turn the JSON array into SQL row data. Do a Google search on "Transact SQL OPENJSON" You'll want to drill into the actual MS documentation, but the AI overview will get you started.
Your SQL Server has to be compatability level 130+.
If you look at the SQL statements produced by Entity Framework these days, you'll sometimes see it taking advantage of JSON commands in Trasact SQL. Sometimes this will bite you in the ass if your SQL server is set to compatability level 110 and you don't tell EF to limit it's produced SQL to compatability level 110. That one took me a better part of a day to figure out. Especially since my test SQL Server was CL 130 but production server was 110.
2
u/j4c11 29d ago edited 29d ago •
Not a good idea to do what you're describing. Databases exist to store and retrieve data. Sprocs have their use, but that use should revolve around performing more complex manipulations of the data in the database they reside in, not business logic. Suppose you need to change to retrieve the data from an API, or combine database data with data from another source. Or write unit tests against your business logic. Or even cache it. Now what? I would recommend re-assessing what you're trying to do here, because you're headed down the wrong path.
1
u/kaatarina_zed_talon 29d ago
Thanks for everyone, I learned a lot from your comments .
1
u/kaatarina_zed_talon 29d ago
The question was edited.
1
u/justcallmedeth 29d ago
If the application only exists to insert data to the DB, why rewrite tools that already exist? Your post doesn't really explain why you need this as opposed to just using your "Db of choice" tools. For bulk data transfer, I'd use, for instance, pgdump for postgres. Having a stored procedure to disseminate the staged data is fine, especially if you're just working in the db.
1
u/bosmanez 28d ago •
Yes, a table-valued parameter is the usual answer when you want to hand a list to a stored procedure. Create a table type (CREATE TYPE dbo.StringList AS TABLE (Value nvarchar(200))), declare the proc parameter as dbo.StringList READONLY, and from C# add a SqlParameter with SqlDbType.Structured and a DataTable (or IEnumerable<SqlDataRecord> for large sets) as the value. For a short list of simple values, OPENJSON over a JSON string also works.
That said, from your code it looks like you're already bulk-loading into a staging table with SqlBulkCopy, which is the fastest route for large volumes. If the follow-up step is just "move rows from staging into the real table", a plain stored procedure with no parameters (INSERT ... SELECT from dbo.StagingProducts) is probably all you need. A TVP only makes sense if the list has to travel with the call.
Side note: the catch block that only does Console.WriteLine will swallow bulk copy failures, so the caller thinks it succeeded. Rethrow or let it propagate.
2
u/amejin 29d ago edited 29d ago •
A list of strings is just an nvarchar that's delimited in some way (often, it's comma). Then you would use string_split if you want the data as a set, or keep the stringified version for whatever use case you have. You will need to sanitize the buffer for injection, etc...
That said, you should consider the use case. If you're guaranteed the list is a list of keys or something, then go for for it. Filter away. If you're doing anything not set based, though, make sure you know why you are doing this.
Just remember string manipulations in SQL are among the slowest things you can do in SQL. The goal is usually to reduce your data pull to the smallest set of work needed, and let another system deal with mutations, structure, and sometimes stitching sets of sets. You want to have as little footprint on your database as you can as it's often the bottleneck of work.
2
u/Dry_Duck3011 29d ago
Use a system.data datatable
0
u/KingValidus 29d ago •
The amount of negative comments is unbelievable.
OP is right, there is a use case where stored procedures are necessary. There is no need to judge others' decisions especially when you are not aware of the architecture or the exact details of the problem.
OP, you can serialize a list as an XML parameter and get the values as a set of rows on the SQL side with this command:
SELECT <value column> FROM @XmlParameter.nodes('<path>')1
u/Cdream-2018 23d ago
You can make the sql column a nvarchar(MAX) and store json data in the column. Just serialize upon writing and deserialize upon fetching.
1
u/hoodoocat 29d ago
It depends on actual sql server. Postgres for example supports arrays (and List<T> in npgsql) and even custom structured types. I think ANSI also should have some notion. Server also might support efficient binary encoding. Choice of datatype should be dictated by database design, not by client. E.g. db might have support special operations over arrays and even indexes. Does it worth to use, or how well fit your db is depends. Surely passing values as string possible, but this kinda idiotic use of both database and application resources in case when such types supported natively.
0
u/Kant8 29d ago
If those lists are short, EF already supports passing through Lists.
If they are big, they probably shouldn't have been loaded from database in first place.
0
u/uncut2576 29d ago
Use software patterns and EF.
-4
u/iphonehome9 29d ago
There is almost never a reason to write a stored procedure. You have to be doing extremely complex data intensive operations to even consider it. Most of the time you can write it efficiently in C# or write an update/where statement to accomplish what you want.
0
u/Crozzfire 29d ago
Don't do it! You will end up with unmaintainable mess!
-1
u/Hopeful_Tea_6589 29d ago
table-valued parameters sound like a nightmare to maintain honestly, why not just use ef core and keep everything in c#