Easy-to-use SQL tvp~~ exclusive [add-delete-change-check] Example

first, what is TVP?

table-valued parameter Table-value Parameter (TVP)Provides a way to convert a client application in amultiple rows of dataA simple way to marshal to SQL Server without requiring multiple round trips or special server-side logic to process data. You can use table-valued parameters to wrap data rows in a client application and send data to the server using a single parameterized command.   The incoming data row is stored in a table variable, and you can then manipulate the table variable by using Transact-SQL.

You can use standard Transact-SQL SELECT statements to access column values in table-valued parameters.   

> Simply say that when you want to pass aaaa,bbbb,cccc,dddd to a stored procedure, you can first save the AAA,BBB,CCC,DDDD to a table:

Aaaa

bbbb

Cccc

dddd

The table is then passed to the stored procedure.

For example, when we need to query the information of the specified product, we can usually pass a serial product ID into the stored procedure, such as "1,2,3,4", and then query the product information of id=1 or id=2 or id=3 or id=4.

You can first save the "1,2,3,4" in a table, and then pass the table to the stored procedure.

1

2

3

4

So what are the advantages of this approach? Please look down.

how are the earlier versions passing multiple lines in SQL Server?

before you introduce table-valued parameters in SQL Server 2008, the options for passing multiple rows of data to stored procedures or parameterized SQL commands are limited.   The developer can choose to pass multiple rows to the server using the following options:

• represents multiple data columns and values in rows using a single series of parameters.   The amount of data passed using this method is limited by the number of allowed parameters.  sql Server process can have a maximum of 2,100 parameters.   You must use server-side logic to combine these individual values into a table variable or temporary table for processing.

• bind multiple data values into delimited strings or XML documents, and then pass those literal values to the procedure or statement.  

• sqldataadapter. " > Create a series of individual SQL statements for data modifications that affect multiple rows, such as those created by calling  SqlDataAdapter    Update   method.   You can submit changes individually to the server, or you can batch them as a group.  

• Use the bcp utility program or the SqlBulkCopy object to load many rows of data into a table. Although this technique is very effective, server-side processing is not supported unless the data is loaded into a temporary table or table variable.

Iii. examples

When we need to query the information of the specified product, we can usually pass a serial product ID into the stored procedure, such as "1,2,3,4", and then query out the product information of id=1 or id=2 or id=3 or id=4.

We can first save "1,2,3,4" in a table, and then pass it as a parameter to the stored procedure. This parameter is manipulated inside the stored procedure.

1. Use TVP to search for products

Query Product id=1,2,3,4,5 Products

public static void Testgetproductsbyids () {    collection<int> productids = new collection<int> ();    Console.WriteLine ();    Console.WriteLine ("-----Get Product------");    Console.WriteLine ("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);}    }

How to query:

<summary>///Data Access layer. Gets products by the collection of the specific product ' id.///</summary>///<param name= ' conn ' ></param&gt ;///<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);　 }} return products;}

Create a TVP with the product ID as the column name:

IF not EXISTS (  

To query a product's stored procedure:

/****** Object:  StoredProcedure [dbo].[ Procgetproductsbyproductidstvp]******/set ansi_nulls ongoset quoted_identifier ONGOIF EXISTS (SELECT * FROM Dbo.sysobjects WHERE id = object_id (N ' [dbo].[ PROCGETPRODUCTSBYPRODUCTIDSTVP] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) DROP PROCEDURE [dbo]. [PROCGETPRODUCTSBYPRODUCTIDSTVP] Gocreate PROCEDURE [dbo]. [PROCGETPRODUCTSBYPRODUCTIDSTVP] (@ProductIDsTVP PRODUCTIDSTVP READONLY)            as SELECT p.id, P.namefrom Product as PINNER JOIN @ProductIDsTVP as T on p.id = T.id

2. Deleting a product using TVP

Remove products from product id=1,5,6

public static void Testdeleteproductsbyids () {    collection<int> productids = new collection<int> ();    Console.WriteLine ();    Console.WriteLine ("-----Delete products------");    Console.WriteLine ("Product ids:1,5,6");    Productids.add (1);    Productids.add (5);    Productids.add (6);    Deleteproductsbyids (productids);}

method to delete:

<summary>///deletes products by the collection of the specific product ' id///</summary>///<param nam E= "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);}

To delete a product's stored procedure:

/****** Object:  StoredProcedure [dbo].[ Procdeleteproductsbyidsbyproductidstvp]******/set ansi_nulls ongoset quoted_identifier ONGOIF EXISTS (SELECT * FROM Dbo.sysobjects WHERE id = object_id (N ' [dbo].[ PROCDELETEPRODUCTSBYPRODUCTIDSTVP] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) DROP PROCEDURE [dbo]. [PROCDELETEPRODUCTSBYPRODUCTIDSTVP] Gocreate PROCEDURE [dbo]. [PROCDELETEPRODUCTSBYPRODUCTIDSTVP] (@ProductIDsTVP PRODUCTIDSTVP READONLY) As            DELETE p from Product as PINNER JOIN @ProductIDsTVP as-t on p.id = T.id

3. Adding products using TVP

Add 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);}

Added method:

<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);}

To increase the product's stored procedures:

/****** Object:  StoredProcedure [dbo].[ Procinsertproductsbyproducttvp]******/set ansi_nulls ongoset quoted_identifier ONGOIF EXISTS (SELECT * FROM Dbo.sysobjects WHERE id = object_id (N ' [dbo].[ PROCINSERTPRODUCTSBYPRODUCTTVP] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) DROP PROCEDURE [dbo]. [PROCINSERTPRODUCTSBYPRODUCTTVP] Gocreate PROCEDURE [dbo]. [PROCINSERTPRODUCTSBYPRODUCTTVP] (@ProductTVP PRODUCTTVP READONLY)            as INSERT into Product (ID, Name) selectt.id, T.namefrom @ProductTVP as TGO

4. Using TVP to make new products

Update the name of the id=2 product to BBB

Update the name of the id=6 product 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);}

The updated 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 a TVP with the product ID and product name as the column name:

IF not EXISTS (  SELECT * from sys.types WHERE name = ' PRODUCTTVP ') CREATE TYPE [dbo].[ PRODUCTTVP] as TABLE ([ID] [int] null,[name] NVARCHAR (+)) GO

To increase the product's stored procedures:

/****** Object:  StoredProcedure [dbo].[ Procupdateproductsbyids]******/set ansi_nulls ongoset quoted_identifier ongoif EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ PROCUPDATEPRODUCTSBYPRODUCTTVP] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1) DROP PROCEDURE [dbo]. [PROCUPDATEPRODUCTSBYPRODUCTTVP] Gocreate PROCEDURE [dbo]. [PROCUPDATEPRODUCTSBYPRODUCTTVP] (@ProductTVP PRODUCTTVP READONLY)            as Update pSET p.id = t.id, p.name = T.namefrom product as PINNER JOIN @ProductTVP as T on p.id = T.idgo

Results:

Attention:

(1) The data cannot be returned in the table-valued parameter. a table-valued parameter is a parameter that can only be entered, and the OUTPUT keyword is not supported.

(2) The table-valued parameter is strongly typed and its structure is automatically validated.

(3) The size of the table-valued parameter is limited only by server memory.

(4) When you delete a table-valued parameter, you need to first delete the stored procedure that references the table-valued parameter.

Four, written in the last

Later, TVP performance issues and SQL Bulk copy usage will be used.

V. References

Table-Valued parameter https://msdn.microsoft.com/zh-cn/library/bb675163.aspx

Table-Valued parameters (Database engine) https://msdn.microsoft.com/zh-CN/Library/bb510489 (sql.100). aspx

Recommended reading: 30 minutes Full Parse-sql transaction + Isolation level + blocking + deadlock

Recommended reading: T-SQL Base Blog directory

Easy-to-use SQL tvp~~ exclusive [add-delete-change-check] Example