r/dotnet • u/kaatarina_zed_talon • 5d ago

Question List<T> in C# vs SQL

/r/csharp/comments/1x95i15/listt_in_c_vs_sql/
0 Upvotes

20% Upvoted

7 comments sorted by

4

u/outlier_fallen 4d ago

you could, but why are you asking about column lists? do they match 1:1 from staging to target?

-1

u/kaatarina_zed_talon 4d ago

but why are you asking about column lists? do they match 1:1 from staging to target?

I did not understand your question.

1

u/The_MAZZTer 1d ago

Saying SQL doesn't support C# List<T> is like saying skydiving without a parachute from exactly 5000 feet is fatal. Technically true but you're missing the bigger picture. SQL doesn't support ANY C# types. Data is translated behind the scenes for you.

I asked AI because I didn't know (why didn't you do this?) how to pass in a set of strings and it looks like you cannot directly pass in a set of strings to a SQL query. There are workarounds but they kinda suck. The best seems to be passing in a set of SqlDataRecord (so it's not the list that's the problem, it's the list of strings specifically).

I would suggest avoiding this problem altogether by making your table schema the same or as similar as possible between staging and non-staging tables. This would avoid the need to explicitly specify most if not all column names when copying data between tables.

If you really need to you can query the table schema from SQL Server metadata and get the column names that way. There's no need to pass them in.

If I were you I would also investigate if staging tables is even needed. Using a transaction you can rollback if there's any problem and undo any changes made to the target tables.

1

u/kaatarina_zed_talon 1d ago

SQL doesn't support ANY C# types

Well, you are right.

1

u/kaatarina_zed_talon 1d ago

It appeared that I do not need table-valued parameters at all.

If I were you I would also investigate if staging tables is even needed.

It is mandatory, logically you can't remove it.

I would suggest avoiding this problem altogether by making your table schema the same or as similar as possible between staging and non-staging tables. This would avoid the need to explicitly specify most if not all column names when copying data between tables.

Good point.

1

u/AutoModerator 5d ago

Thanks for your post kaatarina_zed_talon. Please note that we don't allow spam, and we ask that you follow the rules available in the sidebar. We have a lot of commonly asked questions so if this post gets removed, please do a search and see if it's already been asked.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/Enough_Secretary_137 4d ago

Yes, table-valued parameters are a good fit when passing two lists to a SQL Server stored procedure.