Table-Valued Parameters: The Secret Weapon for High-Performance Data Transfer in SQL Server and C#

Leveraging IEnumerable<SqlDataRecord> for Efficient Bulk Data Inserts and Updates Using TVPs in .NET

Introduction

When handling large data transfers between your C# application and SQL Server, performance becomes a critical concern. A common solution involves passing individual records one at a time, which can lead to slow performance due to repeated database calls. Instead, you can leverage Table-Valued Parameters (TVP) to pass multiple records in a single query.

In this blog, we’ll reveal how to use Table-Valued Parameters (TVP) in SQL Server with C#, focusing on the use of IEnumerable<SqlDataRecord>, which is extra green than DataTable for high-overall performance facts transfer.

1. What is a Table-Valued Parameter (TVP)?

Table-Valued Parameters (TVPs), which were added in SQL Server 2008. It’s allows you to pass multiple rows of data to a stored procedure or query in a single database call. This significantly cuts down the number of times the database is accessed, which can boost the speed of handling large amounts of data.

2. Creating a Table-Valued Parameter in SQL Server

To use TVP, you first need to define a user-defined table type and a stored procedure in SQL Server.

Step 1: Define a User-Defined Table Type

CREATE TYPE dbo.MyTableType AS TABLE
(
    Id INT,
    Name NVARCHAR(50),
    Quantity INT
);

This type will allow us to pass structured data (i.e., rows and columns) as a parameter to our stored procedures.

Step 2: Create a Stored Procedure to Use the TVP

CREATE PROCEDURE dbo.InsertMyTable
    @MyTable MyTableType READONLY
AS
BEGIN
    INSERT INTO MyTable (Id, Name, Quantity)
    SELECT Id, Name, Quantity FROM @MyTable;
END

This stored procedure takes the TVP as an input parameter (@MyTable) and inserts the records into the actual table within the database.

3. Using IEnumerable<SqlDataRecord> in C#

Now, let’s enforce the C# code on the way to send statistics to the saved system the use of IEnumerable<SqlDataRecord>, that is a greater reminiscence-efficient way to handle massive data units than DataTable.

Step 1: Creating the Data Source as IEnumerable<SqlDataRecord>
We can represent every row of the TVP as a SqlDataRecord. Below is an instance of how to create a set of SqlDataRecord gadgets that represent the statistics to be inserted.

public static IEnumerable<SqlDataRecord> GetSqlDataRecords(List<Product> products)
{
    // Define the schema for the SqlDataRecord (same as MyTableType)
    SqlMetaData[] metaData = new SqlMetaData[]
    {
        new SqlMetaData("Id", SqlDbType.Int),
        new SqlMetaData("Name", SqlDbType.NVarChar, 50),
        new SqlMetaData("Quantity", SqlDbType.Int)
    };

    // Populate the records
    foreach (var product in products)
    {
        SqlDataRecord record = new SqlDataRecord(metaData);
        record.SetInt32(0, product.Id);   // Set Id
        record.SetString(1, product.Name); // Set Name
        record.SetInt32(2, product.Quantity); // Set Quantity

        yield return record;
    }
}

Here, Product is a simple class:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
}

This approach creates an IEnumerable<SqlDataRecord> object, which may be used to send data to SQL Server.

Step 2: Passing IEnumerable<SqlDataRecord> to SQL Server
Once we’ve got the records within the shape of IEnumerable<SqlDataRecord>, we are able to bypass it to the saved technique using a SqlParameter of type SqlDbType.Structured.

public void InsertDataUsingTVP(IEnumerable<SqlDataRecord> dataRecords)
{
    string connectionString = "Your SQL Server connection string here";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("dbo.InsertMyTable", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            // Create the TVP parameter
            SqlParameter tvpParam = new SqlParameter();
            tvpParam.ParameterName = "@MyTable";
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.MyTableType"; // Specify the table type
            tvpParam.Value = dataRecords; // Pass the IEnumerable<SqlDataRecord>

            // Add parameter to the command
            command.Parameters.Add(tvpParam);

            // Execute the command
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

In this case, we create a SqlParameter with a based type (SqlDbType.Structured), assign it to the TVP kind (dbo.MyTableType), and skip the IEnumerable<SqlDataRecord> as the cost.

4. Handling Bulk Updates with IEnumerable<SqlDataRecord>

TVP may be used not handiest for bulk inserts however also for bulk updates. Below is an instance of a stored system for updating statistics.

SQL Server Stored Procedure for Update:

CREATE PROCEDURE dbo.UpdateMyTable
    @MyTable MyTableType READONLY
AS
BEGIN
    UPDATE MyTable
    SET Name = src.Name, Quantity = src.Quantity
    FROM MyTable tgt
    INNER JOIN @MyTable src ON tgt.Id = src.Id;
END

This stored procedure updates current records based totally on the Id column.

C# Code for Bulk Update:
The C# code to handle updates is sort of equal to the insert instance, however you will call the UpdateMyTable system alternatively.

public void UpdateDataUsingTVP(IEnumerable<SqlDataRecord> dataRecords)
{
    string connectionString = "Your SQL Server connection string here";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("dbo.UpdateMyTable", connection))
        {
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter tvpParam = new SqlParameter();
            tvpParam.ParameterName = "@MyTable";
            tvpParam.SqlDbType = SqlDbType.Structured;
            tvpParam.TypeName = "dbo.MyTableType";
            tvpParam.Value = dataRecords;

            command.Parameters.Add(tvpParam);

            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

This process effectively updates more than one statistics in a single name, making it a great answer for bulk operations.

5. Performance Comparison

Here’s a short look at how this technique compares to standard row-by using-row insertion.

Traditional Row-through-Row Insertion:

public void InsertDataRowByRow(List<Product> products)
{
    string connectionString = "Your SQL Server connection string here";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        foreach (var product in products)
        {
            using (SqlCommand command = new SqlCommand("INSERT INTO MyTable (Id, Name, Quantity) VALUES (@Id, @Name, @Quantity)", connection))
            {
                command.Parameters.AddWithValue("@Id", product.Id);
                command.Parameters.AddWithValue("@Name", product.Name);
                command.Parameters.AddWithValue("@Quantity", product.Quantity);

                command.ExecuteNonQuery();
            }
        }
    }
}

TVP Insertion with IEnumerable<SqlDataRecord>:

public void InsertDataUsingTVP(IEnumerable<SqlDataRecord> dataRecords)
{
    // Code as shown above for TVP insertion
}

For massive datasets, using TVP with IEnumerable<SqlDataRecord> drastically reduces the quantity of database calls, making it some distance more performant than row-by-row insertion.

Conclusion

Table-Valued Parameters (TVP), combined with IEnumerable<SqlDataRecord>, are a notably green way to carry out bulk facts operations among a C# utility and SQL Server. This approach minimizes database round journeys and may substantially decorate performance in information-intensive applications.