在 SQL Server 中使用表值参数进行高效数据处理
阅读：18
点赞：0
一、引言
在SQL Server中，表值参数（TVP）允许将整个表格作为参数传递给存储过程或函数，从而在一个调用中高效地处理大量数据。TVP常用于从应用程序向SQL Server传输多行数据。
二、TVP的优势
1. 提升效率
减少在传递多行数据时的循环和多次调用的需求。
2. 简化代码
通过将多个插入/更新操作合并为一个批次操作，提高了代码的清晰度。
3. 提高性能
减少了服务器往返次数，对于大数据集来说，显著提升了性能。
三、实现步骤
步骤1：创建用户定义的表类型（UDTT）
在使用TVP之前，需要在SQL Server中创建一个用户定义的表类型（UDTT），以定义表参数的结构（列和数据类型）。
CREATE TYPE TVPUsersDetail AS TABLE (
UserID INT,
UserName NVARCHAR(50) NOT NULL,
UserAge INT NOT NULL,
UserAddress NVARCHAR(255)
);
每行注释：
-
第1行：定义了一个名为 TVPUsersDetail的用户定义表类型。
步骤2：创建物理表
创建一个物理表来存储类似的信息。
CREATE TABLE UsersPrimaryTable (
UserID INT,
UserName NVARCHAR(50) NOT NULL,
UserAge INT NOT NULL,
UserAddress NVARCHAR(255)
);
每行注释：
-
第1行：创建一个名为 UsersPrimaryTable的物理表。
步骤3：创建存储过程
定义好UDTT之后，创建一个接受该表类型的存储过程。
CREATE PROCEDURE prcInsertDataIntoTVPTableData
@MTVPUsersDetailsParam TVPUsersDetail READONLY
AS
BEGIN
INSERT INTO UsersPrimaryTable (UserID, UserName, UserAge, UserAddress)
SELECT UserID, UserName, UserAge, UserAddress
FROM @MTVPUsersDetailsParam;
END;
每行注释：
-
第1行：创建一个名为 prcInsertDataIntoTVPTableData的存储过程。 -
第2行：声明一个只读的表值参数。 -
第3行至第6行：将表值参数中的数据插入到物理表中。
步骤4：开发示例应用程序
开发一个类似的应用程序，这里使用的是WPF框架，但你可以根据自己的需求选择不同的框架。
UI视图
<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">
<!-- 用户ID字段 -->
<StackPanel Orientation="Horizontal" Margin="5">
<TextBlock Text="用户ID:" Width="100" VerticalAlignment="Center"/>
<TextBox Name="UserIDTextBox" Width="200" Margin="5,0,0,0"/>
</StackPanel>
<!-- 用户名字段 -->
<StackPanel Orientation="Horizontal" Margin="5">
<TextBlock Text="用户名:" Width="100" VerticalAlignment="Center"/>
<TextBox Name="UserNameTextBox" Width="200" Margin="5,0,0,0"/>
</StackPanel>
<!-- 用户年龄字段 -->
<StackPanel Orientation="Horizontal" Margin="5">
<TextBlock Text="用户年龄:" Width="100" VerticalAlignment="Center"/>
<TextBox Name="UserAgeTextBox" Width="200" Margin="5,0,0,0"/>
</StackPanel>
<!-- 用户地址字段 -->
<StackPanel Orientation="Horizontal" Margin="5">
<TextBlock Text="用户地址:" Width="100" VerticalAlignment="Center"/>
<TextBox Name="UserAddressTextBox" Width="200" Margin="5,0,0,0"/>
</StackPanel>
<!-- 按钮 -->
<Button Content="添加到列表" Click="AddToList_Click" Margin="5"/>
<Button Content="提交到数据库" Click="SubmitToDatabase_Click" Margin="5"/>
<!-- 数据网格显示条目 -->
<DataGrid Name="UserDataGridView" Margin="5" Height="200"/>
</StackPanel>
</Window>
每行注释：
-
第1行至第7行：定义了窗口的基本属性。 -
第9行至第26行：定义了输入控件和按钮。
代码背后的实现
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;"; // 更改此连接字符串以匹配你自己的
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
});
// 清除输入字段
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;
}
}
}
}
每行注释：
-
第1行至第4行：导入必需的命名空间。 -
第6行：定义主窗口类。 -
第8行：初始化用户集合。 -
第10行：设置连接字符串。 -
第12行至第21行：添加用户到列表的方法。 -
第23行至第40行：提交数据到数据库的方法。 -
第42行至第55行：向数据库插入数据的方法。
模型类
using System;
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; }
}
}
每行注释：
-
第1行至第2行：导入必需的命名空间。 -
第4行：定义用户详细信息模型类。 -
第6行至第9行：定义属性。
四、应用程序视图解释
-
数据绑定： UserDataGridView.ItemsSource = userCollection;将ObservableCollection<UserDetailsModel>绑定到DataGrid，便于可视化添加的条目。 -
添加到列表： AddToList_Click事件从文本框读取值，转换为适当类型，并将新的UserDetailsModel实例添加到userCollection。添加后，清空文本框字段。 -
提交到数据库： SubmitToDatabase_Click事件创建一个与UserDetailsModel结构相匹配的DataTable。从userCollection填充DataTable并将数据发送到SQL Server使用表值参数。
五、结论
-
SQL Server中的表值参数（TVP）提供了一种有效的方式来处理大量的数据传输，特别是在需要一次处理多行数据的情况下。 -
通过使用TVP，可以简化应用程序的代码结构，并提高性能，尤其是在处理大数据集时。 -
示例应用程序展示了如何在WPF中实现TVP的功能，通过用户界面收集数据，并批量提交到SQL Server数据库。
通过以上步骤，你可以了解如何在SQL Server中使用表值参数来优化数据操作，并能够在实际项目中应用这一技术。