功能强大的SQL TVP~~独家代理赠予[删改改查]的事例

摘要: 之前一直追求完美新物品,发觉基本才算是最大要的,2020年关键的总体目标是熟练SQL查寻和SQL特性提升。 一、什么叫TVP?表值主要参数Table-Value Parameter (TVP) 出示一种将顾客端运用程序...

 之前一直追求完美新物品,发觉基本才算是最大要的,2020年关键的总体目标是熟练SQL查寻和SQL特性提升。

 

一、什么叫TVP?

表值主要参数Table-Value Parameter (TVP) 出示一种将顾客端运用程序中的几行数据信息封送至 SQL Server 的简易方法,而不用数次来回或独特网络服务器端逻辑性来解决数据信息。 您可使用表值主要参数来包裝顾客端运用程序中的数据信息行,并应用单独主要参数化指令将数据信息推送到网络服务器。 传到的数据信息行储存在一个表自变量中,随后您能够根据应用 Transact-SQL 对该表自变量开展实际操作。

可使用规范的 Transact-SQL SELECT 句子到访问表值主要参数中的列值。 

cc,dddd给储存全过程时,c,dddd存到一张表格中:


随后将这张表传送给储存全过程。

如:当我们们必须查寻特定商品的信息内容时,一般能够传送一串商品ID到储存全过程里边,如 1,2,3,4 ,随后查寻出ID=1或ID=2或ID=3或ID=4的商品信息内容。

能够先将 1,2,3,4 存到一张表格中,随后将这张表发送给储存全过程。


那麼这类方式有哪些优点呢?请然后向下看。

二、初期版本号是如何在 SQL Server 中国传媒大学递几行的?

在 SQL Server 2008 中引进表值主要参数以前,用以将几行数据信息传送到储存全过程或主要参数化 SQL 指令的选择项遭受限定。 开发设计工作人员能够挑选应用下列选择项,将好几个行传送给网络服务器:

应用一系列产品单独主要参数表明好几个数据信息列和行中的值。 应用此方式传送的数据信息量受所容许的主要参数总数的限定。 SQL Server 全过程数最多能够有 2100 个主要参数。 务必应用网络服务器端逻辑性才可以将这种单独值组成到表自变量或临时性表格中以开展解决。

将好几个数据信息值捆缚到隔开标识符串或 XML 文本文档中,随后将这种文字值传送给全过程或句子。 此全过程规定相对的全过程或句子包含认证数据信息构造和撤销捆缚值需要的逻辑性。

对于危害好几个行的数据信息改动建立一系列产品的单独 SQL 句子,比如根据启用 SqlDataAdapter 的 Update 方式建立的內容。 可将变更独立递交给网络服务器,还可以将其做为组开展批处理命令。 但是,即便是以包括好几个句子的批处理命令方式递交的,每一个句子在网络服务器上還是会独立实行。

应用 bcp 好用专用工具程序或 SqlBulkCopy 目标将许多行数据信息载入到表格中。 虽然此项技术性十分合理,但不兼容网络服务器端解决,除非是将数据信息载入到临时性表或表自变量中。

当我们们必须查寻特定商品的信息内容时,一般能够传送一串商品ID到储存全过程里边,如 1,2,3,4 ,随后查寻出ID=1或ID=2或ID=3或ID=4的商品信息内容。

大家能够先将 1,2,3,4 存到一张表格中,随后做为主要参数发送给储存全过程。在储存全过程里边实际操作这一主要参数。

1.应用TVP 查寻商品

查寻商品ID=1,2,3,4,5的商品

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

查寻的方式:

/// 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(
 pProduct = 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;
} 

建立以商品ID做为列名的TVP:

IF NOT EXISTS( SELECT * FROM sys.types WHERE name = ProductIDsTVP )
 CREATE TYPE [dbo].[ProductIDsTVP] AS TABLE
 [ID] INT

查寻商品的储存全过程:

/****** Object: StoredProcedure [dbo].[procGetProductsByProductIDsTVP]******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N [dbo].[procGetProductsByProductIDsTVP] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
 DROP PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
Create PROCEDURE [dbo].[procGetProductsByProductIDsTVP]
 @ProductIDsTVP ProductIDsTVP READONLY
SELECT p.ID, p.Name
FROM Product as p
INNER JOIN @ProductIDsTVP as t on p.ID = t.ID
2.应用TVP 删掉商品

 删掉商品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);
}

 删掉的方式:

/// 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(
 pProduct = new SqlParameter( @ProductIDsTVP , dtProductIDs);
 tvpProduct.SqlDbType = SqlDbType.Structured;
 SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procDeleteProductsByProductIDsTVP , tvpProduct);

删掉商品的储存全过程:

/****** Object: StoredProcedure [dbo].[procDeleteProductsByIDsByProductIDsTVP]******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N [dbo].[procDeleteProductsByProductIDsTVP] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
 DROP PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
Create PROCEDURE [dbo].[procDeleteProductsByProductIDsTVP]
 @ProductIDsTVP ProductIDsTVP READONLY
DELETE p FROM Product AS p
INNER JOIN @ProductIDsTVP AS t on p.ID = t.ID
3.应用TVP 提升商品

提升商品

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

提升的方式:

/// 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
 pProduct = new SqlParameter( @ProductTVP , dtProducts);
 tvpProduct.SqlDbType = SqlDbType.Structured;
 SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procInsertProductsByProductTVP , tvpProduct);

提升商品的储存全过程:

/****** Object: StoredProcedure [dbo].[procInsertProductsByProductTVP]******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N [dbo].[procInsertProductsByProductTVP] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
 DROP PROCEDURE [dbo].[procInsertProductsByProductTVP]
Create PROCEDURE [dbo].[procInsertProductsByProductTVP]
 @ProductTVP ProductTVP READONLY
INSERT INTO Product (ID, Name)
SELECT
 t.ID, 
 t.Name
FROM @ProductTVP AS t
GO
4.应用TVP 升级商品

将ID=2的商品的Name升级为bbb

   将ID=6的商品的Name升级为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);

升级的方式:

/// 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
 pProduct = new SqlParameter( @ProductTVP , dtProducts);
 tvpProduct.SqlDbType = SqlDbType.Structured;
 SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procUpdateProductsByProductTVP , tvpProduct);

建立以商品ID和商品Name做为列名的TVP:

IF NOT EXISTS( SELECT * FROM sys.types WHERE name = ProductTVP )
 CREATE TYPE [dbo].[ProductTVP] AS TABLE(
 [ID] [int] NULL,
 [Name] NVARCHAR(100)
GO

提升商品的储存全过程:

/****** Object: StoredProcedure [dbo].[procUpdateProductsByIDs]******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N [dbo].[procUpdateProductsByProductTVP] ) and OBJECTPROPERTY(id, N IsProcedure ) = 1)
 DROP PROCEDURE [dbo].[procUpdateProductsByProductTVP]
Create PROCEDURE [dbo].[procUpdateProductsByProductTVP]
 @ProductTVP ProductTVP READONLY
Update p
 p.ID = t.ID, 
 p.Name = t.Name
FROM product AS p
INNER JOIN @ProductTVP AS t on p.ID = t.ID
GO

結果:

留意:

(1)没法在表值主要参数中回到数据信息。 表值主要参数是只可键入的主要参数;不兼容 OUTPUT 重要字。

(2)表值主要参数为强种类,其构造会全自动开展认证。 

(3)表值主要参数的尺寸仅受网络服务器运行内存的限定。

(4)删掉表值主要参数时,必须先删掉引入表值主要参数的储存全过程。

四、写在最终

中后期会将TVP的特性难题和SQL Bulk Copy的使用方法补好。

五、参照材料

表值主要参数 /x

表值主要参数(数据信息库模块)zh-CN/Library/bb510489(SQL.100).aspx 

强烈推荐阅读文章:三十分钟全方位分析-SQL事务管理+防护级別+堵塞+死链接

强烈推荐阅读文章:T-SQL基本blog文件目录


作 者: Jackson0714
出 处:jackson0714/



联系我们

全国服务热线:4000-399-000 公司邮箱:343111187@qq.com

  工作日 9:00-18:00

关注我们

官网公众号

官网公众号

Copyright?2020 广州凡科互联网科技股份有限公司 版权所有 粤ICP备10235580号 客服热线 18720358503

技术支持:定制网站建设