C# SQL Server - Passing a list to a stored procedure

sql

tvp

parameterized-queries

sql-injection

byAnton Shumikhin·Sep 12, 2024

Pass a list to SQL Server stored procedure using Table-Valued Parameters (TVPs). The first step is to define a SQL user-defined table type:

CREATE TYPE dbo.ListType AS TABLE ( Item INT );

Next, create a procedure that accepts this TVP:

CREATE PROCEDURE InsertItems @List dbo.ListType READONLY AS BEGIN
    -- Insert command, beware the cat may steal your list 🐱
    INSERT INTO TargetTable (ItemColumn)
    SELECT Item FROM @List
END;

Now in C#, populate a DataTable with your list and pass it to the procedure:

// Create DataTable and give it a column type
DataTable table = new DataTable();
table.Columns.Add("Item", typeof(int));

// It's a loop party! Everybody in the list is invited 🎉
myList.ForEach(i => table.Rows.Add(i));  

using(SqlCommand cmd = new SqlCommand("InsertItems", conn) {
    CommandType = CommandType.StoredProcedure
}) {
    // Add parameter magic 🪄
    cmd.Parameters.Add(new SqlParameter("@List", table) {
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.ListType"
    });
    conn.Open(); // Time to open the door to the party 🚪
    cmd.ExecuteNonQuery();
    conn.Close(); // Always clean up after the party ends 🧹
}

You can effectively pass your list wrapped up like a little present in a DataTable, with the TVP working like magic to provide seamless data integration between C# and SQL Server.

Elevating your SQL game

Tackling large data

For big lists, you should look into splitting your data into smaller batches. Instead of sending a huge list all at once, why not send them in bits like how mom used to cut up your food when you were a kid. This method aids in memory handling and helps you avoid those dreaded timeouts:

int batchSize = 1000; // Size does matter 😏
for(int i = 0; i < myList.Count; i += batchSize) {
    // Create and populate smaller tables  
    DataTable batchTable = new DataTable();
    batchTable.Columns.Add("Item", typeof(int));
    myList.GetRange(i, Math.Min(batchSize, myList.Count - i)).ForEach(item => batchTable.Rows.Add(item));
    
    // Execute as above with 'batchTable'
}

Security concerns

Being secure is cool. Don't be the person who leaves their front door open. Avoid building SQL commands via string concatenation, it's like leaving your keys under the doormat. The use of TVPs inherently wards off SQL injection, like garlic to a vampire, but always use parameterized queries, better safe than sorry.

Weighing your options

TVPs are great, but so are XML, JSON, or even comma-delimited strings. Make sure to pick the right tool for the job, it's like choosing between pizza, tacos, and sushi - they're all great but serve different purposes.

Parsing XML in SQL Server could look something like this:

CREATE PROCEDURE InsertXMLItems @ListXML XML AS BEGIN
    -- SQL Server doing its best XML parser cosplay
    INSERT INTO TargetTable (ItemColumn)
    SELECT Tbl.Col.value('.', 'INT')
    FROM @ListXML.nodes('//item') Tbl(Col)
END;

However be warned, like pineapple on pizza, what suits one doesn't always suit all. Typically, TVPs deliver the best balance of flavor and nutrition for handling collections, particularly in terms of scalability and efficiency.

Beefing up the procedure

Access control in C#

Implement appropriate try-catch-finally blocks or using statements to handle resources including connections and commands. This ensures they are properly disposed of, preventing leaks as efficient as your kitchen sink.

Outside the TVP comfort zone

Alternatives to TVPs include Table-Valued Functions (TVFs) or split functions for cases where passing a list might not be the best approach. Like when you need your friend (join with other tables) to help move the couch (data).

Handle with care

If you're handling sensitive data types, ensure the user-defined table type corresponds accurately, or use nvarchar(MAX) for flexibility when your data feels cramped.

explain-codes / Sql / C# SQL Server - Passing a list to a stored procedure

Linked

Maximum size for a SQL Server Query? IN clause? Is there a Better Approach



Fastest Way of Inserting in Entity Framework



Passing List<> to SQL Stored Procedure



Increasing the Command Timeout for SQL command



How do I loop through a set of records in SQL Server?

