5

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:

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

  2. Pass the ID and list<obj> as two parameters to the stored procedure and let the stored procedure split the list<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?

3

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 returns IEnumerable<SqlDataRecord>, and then use that method as the "Value" for the SqlParameter that is mapped to the TVP. Meaning, something like:

private static IEnumerable<SqlDataRecord> SendRows(List<String> RowData)
{
   SqlMetaData[] _TvpSchema = new SqlMetaData[] {
      new SqlMetaData("SomeSomething", SqlDbType.NVarChar, 4000)
   };
   SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);

   foreach (string _CurrentRow in RowData)
   {
      _DataRecord.SetString(0, _CurrentRow);

      yield return _DataRecord;
   }
}

Then, you would just pass in SendRows(_listobj) as the value for the TVP parameter. When you execute the SqlCommand, 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 DataTables or String.Joining 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.

0

2nd way is doable. You can either pass a comma separated string to the SP and within the SP use a custom sql table value function like this:

CREATE FUNCTION [dbo].[fn_Split](@text VARCHAR(MAX), @delimiter VARCHAR(5) = ',')

RETURNS @Strings TABLE
(    
    position int IDENTITY PRIMARY KEY,
    value VARCHAR(8000)  
)

AS
BEGIN
    DECLARE @index int 
    SET @index = -1 

    WHILE (LEN(@text) > 0) 
        BEGIN  
            SET @index = CHARINDEX(@delimiter , @text)  

            IF (@index = 0) AND (LEN(@text) > 0)  
                BEGIN   
                    INSERT INTO @Strings VALUES (@text)
                    BREAK  
                END  

            IF (@index > 1)
                BEGIN
                    INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
                END

            SET @text = RIGHT(@text, (LEN(@text) - (@index+LEN(@delimiter)-1))) 
        END
    RETURN
END

Or you can define a custom table type like foll and call your SP from C# passing a table variable as 2nd param using SqlDbType.Structured

CREATE TYPE [dbo].[MyCustomType] AS TABLE(
Id int not null
)
  • 1
    You should mention that string manipulation (splits, joins, searches) is slow. Depending on how often this must be done, I would go with the custom type option you mentioned as my first choice. Let the application do the splitting. – Jonathan Fite May 24 '17 at 19:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.