This is the 26th day of my participation in Gwen Challenge.
Hello, I am Wukong.
Star: github.com/Jackson0714… Personal website: www.passjava.cn
Database topics
- Why NoSQL when you have MySQL?
- TempDB usage and performance issues
- Table expressions (1)
- Table expressions (2)
- Explain SQL set operations in detail
- Explain SQL single table query
- Explain join queries in SQL
- Explain subqueries in SQL
- Explain SQL set operations in detail
What is TVP?
The table-value Parameter (TVP) provides an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic to process the data. You can use table-valued arguments to wrap rows of data in a client application and send data to the server using a single parameterized command. The incoming rows are stored in a table variable, which you can then manipulate by using Transact-SQL.
Column values in table value parameters can be accessed using standard Transact-SQL SELECT statements.
If you want to pass aAAA, BBBB, CCCC, DDDD to a stored procedure, you can first store aaa, BBB, CCC, DDDD in a table:
The table is then passed to the stored procedure.
For example, when we need to query information about a specific product, we can usually pass a series of product ids to the stored procedure, such as “1,2,3,4”, and then query the product information with ID=1 or ID=2 or ID=3 or ID=4.
You can store “1,2,3,4” in a table and then pass the table to the stored procedure.
What are the advantages of this approach? Please read on.
TVP passes multiple lines
How do earlier versions pass multiple rows in SQL Server? Before the introduction of table-valued parameters in SQL Server 2008, the options for passing multiple rows of data to stored procedures or parameterized SQL commands were limited. The developer can choose to pass multiple rows to the server using the following options:
Use a series of single parameters to represent values in multiple data columns and rows. The amount of data passed using this method is limited by the number of parameters allowed. SQL Server procedures can have up to 2100 parameters. You must use server-side logic to combine these individual values into table variables or temporary tables for processing.
Multiple data values are bundled into delimited strings or XML documents, and these text values are then passed to procedures or statements. This procedure requires that the corresponding procedure or statement include the logic needed to validate data structures and unbind values.
Create a series of single SQL statements for data changes that affect multiple rows, such as content created by calling the SqlDataAdapter’s Update method. Changes can be committed to the server individually or they can be batched as a group. However, even if it is submitted as a batch containing multiple statements, each statement is executed individually on the server.
Many rows of data are loaded into tables using the BCP utility program or the SqlBulkCopy object. Although this technique is very effective, server-side processing is not supported unless data is loaded into temporary tables or table variables.
Three cases,
When we need to query information about a specific product, we can usually pass a series of product ids to the stored procedure, such as “1,2,3,4”, and then query for product information with ID=1 or ID=2 or ID=3 or ID=4.
We can store “1,2,3,4” in a table and pass it as a parameter to the stored procedure. Manipulate this parameter in the stored procedure.
3.1 Using TVP to Query Products
Query products whose product ID is 1,2,3,4,5
public static void TestGetProductsByIDs()
{
Collection<int> productIDs = new Collection<int> (); Console.WriteLine(); Console.WriteLine("----- Get Product ------");
Console.WriteLine("The Product IDs: 1, 2, 3, 4, 5");
productIDs.Add(1);
productIDs.Add(2);
productIDs.Add(3);
productIDs.Add(4);
productIDs.Add(5);
Collection<Product> dtProducts = GetProductsByIDs(productIDs);
foreach (Product product in dtProducts)
{
Console.WriteLine("{0} {1}", product.ID, product.Name); }}Copy the codeQuery method:
/// <summary>
/// Data access layer. Gets products by the collection of the specific product' ID.
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
/// <returns></returns>
public static Collection<Product> GetProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
Collection<Product> products = new Collection<Product>();
DataTable dtProductIDs = new DataTable("Product");
dtProductIDs.Columns.Add("ID".typeof(int));
foreach (int id in productIDs)
{
dtProductIDs.Rows.Add(
id
);
}
SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
tvpProduct.SqlDbType = SqlDbType.Structured;
//SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procGetProducts", tvpProduct);
using (SqlDataReader dataReader = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "procGetProductsByProductIDsTVP", tvpProduct))
{
while (dataReader.Read())
{
Product product = new Product();
product.ID = dataReader.IsDBNull(0)?0 : dataReader.GetInt32(0);
product.Name = dataReader.IsDBNull(1)? (string)null : dataReader.GetString(1).Trim(); products.Add(product); }}returnproducts; }Copy the codeCreate TVP with product ID as column name:
IF NOT EXISTS( SELECT * FROM sys.types WHERE name = 'ProductIDsTVP')
CREATE TYPE [dbo].[ProductIDsTVP] AS TABLE
(
[ID] INT
)
GO
Copy the codeQuery the stored procedure of a product:
/****** Object: StoredProcedure [dbo].[procGetProductsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procGetProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
GO
Create PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
(
@ProductIDsTVP ProductIDsTVP READONLY
)
AS
SELECT p.ID, p.Name
FROM Product as p
INNER JOIN @ProductIDsTVP as t on p.ID = t.ID
Copy the code3.2 Using TVP to Delete products
Delete the products whose product IDS are 1,5, and 6
public static void TestDeleteProductsByIDs()
{
Collection<int> productIDs = new Collection<int> (); Console.WriteLine(); Console.WriteLine("----- Delete Products ------");
Console.WriteLine("The Product IDs: 1 and 6");
productIDs.Add(1);
productIDs.Add(5);
productIDs.Add(6); DeleteProductsByIDs(productIDs); } Delete method:/// <summary>
/// Deletes products by the collection of the specific product' ID
/// </summary>
/// <param name="conn"></param>
/// <param name="productIDs"></param>
public static void DeleteProductsByIDs(SqlConnection conn, Collection<int> productIDs)
{
Collection<Product> products = new Collection<Product>();
DataTable dtProductIDs = new DataTable("Product");
dtProductIDs.Columns.Add("ID".typeof(int));
foreach (int id in productIDs)
{
dtProductIDs.Rows.Add(
id
);
}
SqlParameter tvpProduct = new SqlParameter("@ProductIDsTVP", dtProductIDs);
tvpProduct.SqlDbType = SqlDbType.Structured;
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procDeleteProductsByProductIDsTVP", tvpProduct);
}
Copy the codeDelete a stored procedure for a product:
/****** Object: StoredProcedure [dbo].[procDeleteProductsByIDsByProductIDsTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procDeleteProductsByProductIDsTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
GO
Create PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
(
@ProductIDsTVP ProductIDsTVP READONLY
)
AS
DELETE p FROM Product AS p
INNER JOIN @ProductIDsTVP AS t on p.ID = t.ID
Copy the code3.3 Adding products using TVP
To increase product
ID=5,Name=bbb
ID=6,Name=abc
public static void TestInsertProducts()
{
Collection<Product> products = new Collection<Product>();
Console.WriteLine();
Console.WriteLine("----- Insert Products ------");
Console.WriteLine("Product IDs: 5-bbb,6-abc");
products.Add(
new Product()
{
ID = 5,
Name = "qwe"
});
products.Add(
new Product()
{
ID = 6,
Name = "xyz"
});
InsertProducts(products);
}
Copy the codeMethods of increase:
/// <summary>
/// Inserts products by the collection of the specific products.
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void InsertProducts(SqlConnection conn, Collection<Product> products)
{
DataTable dtProducts = new DataTable("Product");
dtProducts.Columns.Add("ID".typeof(int));
dtProducts.Columns.Add("Name".typeof(string));
foreach (Product product in products)
{
dtProducts.Rows.Add(
product.ID,
product.Name
);
}
SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
tvpProduct.SqlDbType = SqlDbType.Structured;
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procInsertProductsByProductTVP", tvpProduct);
}
Copy the codeAdd a stored procedure for the product:
/****** Object: StoredProcedure [dbo].[procInsertProductsByProductTVP]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procInsertProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procInsertProductsByProductTVP]
GO
Create PROCEDURE [dbo].[procInsertProductsByProductTVP]
(
@ProductTVP ProductTVP READONLY
)
AS
INSERT INTO Product (ID, Name)
SELECT
t.ID,
t.Name
FROM @ProductTVP AS t
GO
Copy the code3.4 Using TVP to update products
Update the Name of the product with ID=2 to BBB
Update the Name of the product with ID=6 to ABC
public static void TestUpdateProducts()
{
Collection<Product> products = new Collection<Product>();
Console.WriteLine();
Console.WriteLine("----- Update Products ------");
Console.WriteLine("Product IDs: 2-bbb,6-abc");
products.Add(
new Product()
{
ID = 2,
Name = "bbb"
});
products.Add(
new Product()
{
ID = 6,
Name = "aaa"
});
UpdateProducts(products);
}
Copy the codeUpdate method:
/// <summary>
/// Updates products by the collection of the specific products
/// </summary>
/// <param name="conn"></param>
/// <param name="products"></param>
public static void UpdateProducts(SqlConnection conn, Collection<Product> products)
{
DataTable dtProducts = new DataTable("Product");
dtProducts.Columns.Add("ID".typeof(int));
dtProducts.Columns.Add("Name".typeof(string));
foreach (Product product in products)
{
dtProducts.Rows.Add(
product.ID,
product.Name
);
}
SqlParameter tvpProduct = new SqlParameter("@ProductTVP", dtProducts);
tvpProduct.SqlDbType = SqlDbType.Structured;
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "procUpdateProductsByProductTVP", tvpProduct); } create TVP with product ID and product Name as column names:IF NOT EXISTS( SELECT * FROM sys.types WHERE name = 'ProductTVP')
CREATE TYPE [dbo].[ProductTVP] AS TABLE(
[ID] [int] NULL,
[Name] NVARCHAR(100)
)
GO
Copy the codeAdd a stored procedure for the product:
/****** Object: StoredProcedure [dbo].[procUpdateProductsByIDs]******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[procUpdateProductsByProductTVP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[procUpdateProductsByProductTVP]
GO
Create PROCEDURE [dbo].[procUpdateProductsByProductTVP]
(
@ProductTVP ProductTVP READONLY
)
AS
Update p
SET
p.ID = t.ID,
p.Name = t.Name
FROM product AS p
INNER JOIN @ProductTVP AS t on p.ID = t.ID
GO
Copy the codeResults:
Note:
(1) Cannot return data in table value parameter. Table – valued parameters are input-only parameters; OUTPUT keyword is not supported.
(2) The table value parameter is strongly typed, and its structure will be verified automatically.
(3) The size of table value parameters is limited only by server memory.
(4) Before deleting table – valued parameters, delete the stored procedure that references table – valued parameters first.
References:
Table value parameter msdn.microsoft.com/zh-cn/libra…
Table value parameters (database engine) msdn.microsoft.com/zh-CN/Libra…
About the author: Wu Kong, 8 years of experience in Internet development and architecture, explains distributed, architecture design, Java core technology with stories. “JVM performance optimization practice” column author, open source “Spring Cloud practice PassJava” project, independently developed a PMP brush small program.
I am Wukong, strive to become stronger, become super Saiya people!