I am using C#, Entity Framework. SQL Server for the development. I have a method which has two parameters (int ID, List<String> _listobj
). For each ID, there is a list of obj. All of these IDs and lists will be inserted into different tables in database. So the question is: what's the best practice for this? I have couple of options:
-
In C#, split the
list<obj>
as single value, then create a stored procedure which takes two parameters (ID, str
), then the C# will call the stored procedure many times depending on the size of the list -
Pass the
ID
andlist<obj>
as two parameters to the stored procedure and let the stored procedure split thelist<obj>
. This way C# will only call the stored procedure once
Is the second strategy doable? If yes, which is better in terms of performance?
Best Answer
You don't want either of those two options, though #2 is quite close. Option #1 is slow due to being row-by-row and each call being its own separate transaction. Even if you wrap all of the Stored Procedure calls into a single explicit Transaction, it will still be slower than a set-based approach.
Option #2 is flawed mainly for the additional work being done in both layers: joining the collection into a single string in the app layer, and splitting the string back out into elements in the data layer. However, the general structure of this idea — passing in just two params: ID and the collection of strings — is the way to go. You just need to use a Table-Valued Parameter (TVP) so that you can iterate through the collection as is, and receive it as a pre-populated Table Variable in SQL Server.
You do this by creating a User-Defined Table Type (which here could be a single
NVARCHAR
column) and using that as the parameter for the collection (hence the "Table"-Valued "Parameter").The other piece to this puzzle is, on the app code side, creating a method that accepts
int ID, List<String> _listobj
and returnsIEnumerable<SqlDataRecord>
, and then use that method as the "Value" for theSqlParameter
that is mapped to the TVP. Meaning, something like:Then, you would just pass in
SendRows(_listobj)
as the value for the TVP parameter. When you execute theSqlCommand
, it will call that method and stream that collection into SQL Server.The technique shown above requires no additional memory or processing, it just sends the collection as a collection. No need for
DataTable
s orString.Join
ing or splitting :-).To see a complete example code setup for this, please see my answer to the following Stack Overflow question: Pass Dictionary to Stored Procedure T-SQL.