To effectively handle massive volumes of data in a single call, a Table-Valued Parameter (TVP) in SQL Server allows an entire table to be passed as a parameter to a stored procedure or function. When sending many rows of data from an application to SQL Server, TVPs are commonly used.
Advantages of TVPs
- Increased effectiveness: Decreases the requirement for looping and multiple calls when passing numerous rows.
- Streamlined Code: Enhances code clarity by combining multiple inserts/updates into a single batch operation.
- 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;
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
Explanation of the above Code
- Data Binding: UserDataGridView.ItemsSource = userCollection; binds the ObservableCollection<UserDetailsModel> to the DataGrid, making it easy to visualize added entries.
- 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.
- 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.
- Stored Procedure in SQL Server: The SQL Server stored procedure prcInsertDataIntoTVPTableData should be configured to accept a TVP with the structure of UserDetailsModel.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
