Post
Ask Question

Efficient Data Handling in SQL Server with Table-Valued Parameters

In SQL Server, a Table-Valued Parameter (TVP) enables the passing of an entire table as a parameter to a stored procedure or function, making it possible to efficiently handle large amounts of data in a single call. TVPs are frequently utilized for transmitting multiple rows of data from an application to SQL Server.

Advantages of TVPs

  1. Increased effectiveness: Decreases the requirement for looping and multiple calls when passing numerous rows.
  2. Streamlined Code: Enhances code clarity by combining multiple inserts/updates into a single batch operation.
  3. Enhanced Performance: Reduces trips to the server, leading to substantial performance improvements with large datasets.

To achieve this Functionality Follow the Following Steps

Step 1. In SQL Server, a User-Defined Table Type (UDTT) needs to be created to define the structure (columns and data types) of the table parameter before using a TVP.

CREATE TYPE TVPUsersDetail AS TABLE (
    UserID INT,
    UserName NVARCHAR(50) NOT NULL,
    UserAge INT NOT NULL,
    UserAddress NVARCHAR(255)
);

Step 2. Create a physical table to store information like the one below.

CREATE TABLE UsersPrimaryTable (
    UserID INT,
    UserName NVARCHAR(50) NOT NULL,
    UserAge INT NOT NULL,
    UserAddress NVARCHAR(255)
);

Step 3. After defining the UDTT, create a stored procedure that takes a parameter of this table type.

CREATE PROCEDURE prcInsertDataIntoTVPTableData
    @MTVPUsersDetailsParam TVPUsersDetail READONLY
AS
BEGIN
    INSERT INTO TVPUsersDetail (UserID, UserName, UserAge, UserAddress)
    SELECT UserID, UserName, UserAge, UserAddress 
    FROM @MTVPUsersDetailsParam;
END;

UDTT

Step 4. Develop a sample application similar to the one provided below. I am utilizing WPF, but you may choose your own framework based on your specific needs.

UI View

<Window x:Class="TVPExample.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:TVPExample"
        mc:Ignorable="d"
        Title="MainWindow" Height="450" Width="800">

    <StackPanel Margin="10">
        <!-- User ID Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User ID:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserIDTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Name Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Name:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserNameTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Age Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Age:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserAgeTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- User Address Field -->
        <StackPanel Orientation="Horizontal" Margin="5">
            <TextBlock Text="User Address:" Width="100" VerticalAlignment="Center"/>
            <TextBox Name="UserAddressTextBox" Width="200" Margin="5,0,0,0"/>
        </StackPanel>

        <!-- Buttons -->
        <Button Content="Add to List" Click="AddToList_Click" Margin="5"/>
        <Button Content="Submit to Database" Click="SubmitToDatabase_Click" Margin="5"/>

        <!-- DataGrid to Display Entries -->
        <DataGrid Name="UserDataGridView" Margin="5" Height="200"/>
    </StackPanel>
</Window>

Code behind Implementation

using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Windows;

namespace TVPExample
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        private ObservableCollection<UserDetailsModel> userCollection = new ObservableCollection<UserDetailsModel>();

        public MainWindow()
        {
            InitializeComponent();
            UserDataGridView.ItemsSource = userCollection;
        }

        string connectionString = @"Server=DESKTOP-JNM9BF1\SANJAYSERVER;Database=Demo;User Id=sa;Password=1234;"; // Change this connection string with your own

        private void AddToList_Click(object sender, RoutedEventArgs e)
        {
            if (int.TryParse(UserIDTextBox.Text, out int userId) &&
                int.TryParse(UserAgeTextBox.Text, out int userAge))
            {
                userCollection.Add(new UserDetailsModel
                {
                    UserID = userId,
                    UserName = UserNameTextBox.Text,
                    UserAge = userAge,
                    UserAddress = UserAddressTextBox.Text
                });

                // Clear input fields
                UserIDTextBox.Clear();
                UserNameTextBox.Clear();
                UserAgeTextBox.Clear();
                UserAddressTextBox.Clear();
            }
        }

        private void SubmitToDatabase_Click(object sender, RoutedEventArgs e)
        {
            var dataTable = new DataTable();
            dataTable.Columns.Add("UserID", typeof(int));
            dataTable.Columns.Add("UserName", typeof(string));
            dataTable.Columns.Add("UserAge", typeof(int));
            dataTable.Columns.Add("UserAddress", typeof(string));

            foreach (var user in userCollection)
            {
                dataTable.Rows.Add(user.UserID, user.UserName, user.UserAge, user.UserAddress);
            }

            InsertDataToDatabase(dataTable);
        }

        private void InsertDataToDatabase(DataTable dataTable)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                using (var command = new SqlCommand("prcInsertDataIntoTVPTableData", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    var parameter = command.Parameters.AddWithValue("@MTVPUsersDetailsParam", dataTable);
                    parameter.SqlDbType = SqlDbType.Structured;

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

Model class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TVPExample
{
    internal class UserDetailsModel
    {
        public int UserID { get; set; }
        public string UserName { get; set; }
        public int UserAge { get; set; }
        public string UserAddress { get; set; }
    }
}

Application View

Application View

Explanation of the above Code

  1. Data Binding: UserDataGridView.ItemsSource = userCollection; binds the ObservableCollection<UserDetailsModel> to the DataGrid, making it easy to visualize added entries.
  2. Add to List: The AddToList_Click event reads values from TextBox inputs, converts them to the appropriate types, and adds a new UserDetailsModel instance to userCollection.After adding, it clears the TextBox fields.
  3. Submit to Database: The SubmitToDatabase_Click event creates a DataTable that matches the structure of the UserDetailsModel.It populates the DataTable from userCollection and sends it to SQL Server using a Table-Valued Parameter.
  4. Stored Procedure in SQL Server: The SQL Server stored procedure prcInsertDataIntoTVPTableData should be configured to accept a TVP with the structure of UserDetailsModel.


Similar Articles