荣耀彩票代理

IT技术互动交流平台

好用的SQL TVP~~独家赠送[增删改查]的例子

作者:jackson0714  来源:IT165收集  发布日期:2016-12-16 20:35:43

荣耀彩票代理 YIQIANZONGSHIZHUIQIUXINDONGXI,FAXIANJICHUCAISHIZUIZHONGYAODE,JINNIANZHUYAODEMUBIAOSHIJINGTONGSQLCHAXUNHESQLXINGNENGYOUHUA。

 

一、什么是TVP?

表值参数Table-Value Parameter (TVP) 提供一种将客户端应用程序中的多行数据封送到 SQL Server 的简单方式,而不需要多次往返或特殊服务器端逻辑来处理数据。 您可以使用表值参数来包装客户端应用程序中的数据行,并使用单个参数化命令将数据发送到服务器。 传入的数据行存储在一个表变量中,然后您可以通过使用 Transact-SQL 对该表变量进行操作。

KEYISHIYONGBIAOZHUNDE Transact-SQL SELECT YUJULAIFANGWENBIAOZHICANSHUZHONGDELIEZHI。  

JIANDANDIANSHUOJIUSHIDANGXIANGCHUANDIaaaa,bbbb,cccc,ddddJICUNCHUGUOCHENGSHI,KEYIXIANJIANGaaa,bbb,ccc,ddddCUNDAOYIZHANGBIAOZHONG:

aaaa
bbbb
cccc
dddd

RANHOUJIANGZHEIZHANGBIAOCHUANDIJICUNCHUGUOCHENG。

荣耀彩票代理RU:DANGWOMENXUYAOCHAXUNZHIDINGCHANPINDEXINXISHI,TONGCHANGKEYICHUANDIYICHUANCHANPINIDDAOCUNCHUGUOCHENGLIMIAN,RU'1,2,3,4',RANHOUCHAXUNCHUID=1HUOID=2HUOID=3HUOID=4DECHANPINXINXI。

KEYIXIANJIANG'1,2,3,4'CUNDAOYIZHANGBIAOZHONG,RANHOUJIANGZHEIZHANGBIAOCHUANJICUNCHUGUOCHENG。

1
2
3
4

 

 

 

 

 

 

那么这种方法有什么优势呢?请接着往下看。

二、早期版本是怎么在 SQL Server 中传递多行的?

荣耀彩票代理ZAI SQL Server 2008 ZHONGYINRUBIAOZHICANSHUZHIQIAN,YONGYUJIANGDUOXINGSHUJUCHUANDIDAOCUNCHUGUOCHENGHUOCANSHUHUA SQL MINGLINGDEXUANXIANGSHOUDAOXIANZHI。 KAIFARENYUANKEYIXUANZESHIYONGYIXIAXUANXIANG,JIANGDUOGEXINGCHUANDIJIFUWUQI:

SHIYONGYIXILIEDANGECANSHUBIAOSHIDUOGESHUJULIEHEXINGZHONGDEZHI。 SHIYONGCIFANGFACHUANDIDESHUJULIANGSHOUSUOYUNXUDECANSHUSHULIANGDEXIANZHI。 SQL Server GUOCHENGZUIDUOKEYIYOU 2100 GECANSHU。 BIXUSHIYONGFUWUQIDUANLUOJICAINENGJIANGZHEIXIEDANGEZHIZUHEDAOBIAOBIANLIANGHUOLINSHIBIAOZHONGYIJINXINGCHULI。

荣耀彩票代理JIANGDUOGESHUJUZHIKUNBANGDAOFENGEZIFUCHUANHUO XML WENDANGZHONG,RANHOUJIANGZHEIXIEWENBENZHICHUANDIJIGUOCHENGHUOYUJU。 CIGUOCHENGYAOQIUXIANGYINGDEGUOCHENGHUOYUJUBAOKUOYANZHENGSHUJUJIEGOUHEQUXIAOKUNBANGZHISUOXUDELUOJI。

针对影响多个行的数据修改创建一系列的单个 SQL 语句,例如通过调用 SqlDataAdapter 的 Update 方法创建的内容。 可将更改单独提交给服务器,也可以将其作为组进行批处理。 不过,即使是以包含多个语句的批处理形式提交的,每个语句在服务器上还是会单独执行。

使用 bcp 实用工具程序或 SqlBulkCopy 对象将很多行数据加载到表中。 尽管这项技术非常有效,但不支持服务器端处理,除非将数据加载到临时表或表变量中。

三、例子

DANGWOMENXUYAOCHAXUNZHIDINGCHANPINDEXINXISHI,TONGCHANGKEYICHUANDIYICHUANCHANPINIDDAOCUNCHUGUOCHENGLIMIAN,RU'1,2,3,4',RANHOUCHAXUNCHUID=1HUOID=2HUOID=3HUOID=4DECHANPINXINXI。

荣耀彩票代理WOMENKEYIXIANJIANG“1,2,3,4”CUNDAOYIZHANGBIAOZHONG,RANHOUZUOWEICANSHUCHUANJICUNCHUGUOCHENG。ZAICUNCHUGUOCHENGLIMIANCAOZUOZHEIGECANSHU。

1.使用TVP 查询产品

荣耀彩票代理CHAXUNCHANPINID=1,2,3,4,5DECHANPIN

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

CHAXUNDEFANGFA:

/// <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);
        }
    }
    return products;
} 

荣耀彩票代理CHUANGJIANYICHANPINIDZUOWEILIEMINGDETVP:

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

CHAXUNCHANPINDECUNCHUGUOCHENG:

/****** 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

2.使用TVP 删除产品

 SHANCHUCHANPINID=1,5,6DECHANPIN

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

 SHANCHUDEFANGFA:

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

SHANCHUCHANPINDECUNCHUGUOCHENG:

/****** 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

3.使用TVP 增加产品

ZENGJIACHANPIN

荣耀彩票代理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);
}

ZENGJIADEFANGFA:

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

ZENGJIACHANPINDECUNCHUGUOCHENG:

/****** 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

4.使用TVP 更新产品

 JIANGID=2DECHANPINDENameGENGXINWEIbbb

   JIANGID=6DECHANPINDENameGENGXINWEIabc

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

 GENGXINDEFANGFA:

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

CHUANGJIANYICHANPINIDHECHANPINNameZUOWEILIEMINGDETVP:

IF NOT EXISTS(  SELECT * FROM sys.types WHERE name = 'ProductTVP')

	CREATE TYPE [dbo].[ProductTVP] AS TABLE(
		[ID] [int] NULL,
		[Name] NVARCHAR(100)
	)

GO

ZENGJIACHANPINDECUNCHUGUOCHENG:

/****** 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

JIEGUO:

ZHUYI:

荣耀彩票代理(1)WUFAZAIBIAOZHICANSHUZHONGFANHUISHUJU。 BIAOZHICANSHUSHIZHIKESHURUDECANSHU;BUZHICHI OUTPUT GUANJIANZI。

(2)BIAOZHICANSHUWEIQIANGLEIXING,QIJIEGOUHUIZIDONGJINXINGYANZHENG。 

(3)BIAOZHICANSHUDEDAXIAOJINSHOUFUWUQINEICUNDEXIANZHI。

荣耀彩票代理(4)SHANCHUBIAOZHICANSHUSHI,XUYAOXIANSHANCHUYINYONGBIAOZHICANSHUDECUNCHUGUOCHENG。

四、写在最后

荣耀彩票代理HOUQIHUIJIANGTVPDEXINGNENGWENTIHESQL Bulk CopyDEYONGFABUSHANG。

五、参考资料

荣耀彩票代理BIAOZHICANSHU http://msdn.microsoft.com/zh-cn/library/bb675163.aspx

  • 表值参数(数据库引擎)http://msdn.microsoft.com/zh-CN/Library/bb510489(SQL.100).aspx 

    TUIJIANYUEDU:30FENZHONGQUANMIANJIEXI-SQLSHIWU+GELIJIBIE+ZUSAI+SISUO

    TUIJIANYUEDU:T-SQLJICHUBOKEMULU


    作  者: Jackson0714
    出  处:http://www.cnblogs.com/jackson0714/

Tag标签:      
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规