linq存储过程的运用还是比较广泛的,如果想运用好linq语言,那linq存储过程是必不可少的。
本篇,关注的是linq存储过程来完成数据库的四步操作(查询,删除,更新,新增)。
数据库仍然是Northwind,数据源是表Customers
1). 首先创建一个网站,添加Linq to Sql类,名称为Northwind.dbml;
2). 打开Visual Studio的服务器资源管理器,选择数据连接-添加连接,引入Northwind数据库,展开,将Customers表拖到Northwind.dbml,这样就完成了创建Customers对象;
3). 创建查询,删除,更新,新增 Customers 的linq存储过程,代码如下:
linq存储过程之查询Customers
- Procedure Name: [dbo].[selCustomers]
- Note : get Customers
- Created By : Tom
- Created Date : 2008-5-28
- CREATE PROCEDURE [dbo].[selCustomers]
- (
- @ncCoustomerID NVARCHAR(5) = NULL,
- @nvcCountry NVARCHAR(15) = NULL
- AS
- IF @ncCoustomerID IS NULL OR @ncCoustomerID = ''
- SET @ncCoustomerID = NULL
- IF @nvcCountry IS NULL OR @nvcCountry = ''
- SET @nvcCountry = NULL
- SELECT *
- FROM CUSTOMERS
- WHERE CUSTOMERID LIKE ISNULL(@ncCoustomerID, '%')
- AND COUNTRY LIKE ISNULL(@nvcCountry, '%')
- RETURN
linq存储过程之删除Customers
- Procedure Name : [dbo].[delCustomers]
- Note : delete Customer
- Created By : Tom
- Created Date : 2008-5-28
- CREATE PROCEDURE [dbo].[delCustomers]
- (
- @ncCustomerID NCHAR(5)
- )
- AS
- DELETE CUSTOMERS
- WHERE CUSTOMERID = @ncCUSTOMERID
- RETURN
linq存储过程之更新Customer
- Procedure Name : [dbo].[updCustomers]
- Note : modify Customer
- Created By : Tom
- Created Date : 2008-5-28
- CREATE PROCEDURE [dbo].[updCustomers]
- (
- @ncCustomerID NCHAR(5),
- @nvcCompanyName NVARCHAR(40),
- @nvcAddress NVARCHAR(60),
- @intReturnValue INT OUTPUT
- )
- AS
- --不返回受影响的行
- SET NOCOUNT ON
- --更新指定的Customers
- UPDATE CUSTOMERS
- SET COMPANYNAME = @nvcCompanyName,
- ADDRESS = @nvcAddress
- WHERE CUSTOMERID = @ncCustomerID
- IF @@ERROR <> 0
- BEGIN
- --如果更新发生异常,返回-1
- SET @intReturnValue = -1
- RETURN
- END
- RETURN
linq存储过程之新增Customer
- Procedure Name : [dbo].[updCustomers]
- Note : modify Customer
- Created By : Tom
- Created Date : 2008-5-28
- CREATE PROCEDURE [dbo].[updCustomers]
- (
- @ncCustomerID NCHAR(5),
- @nvcCompanyName NVARCHAR(40),
- @nvcAddress NVARCHAR(60),
- @intReturnValue INT OUTPUT
- )
- AS
- --不返回受影响的行
- SET NOCOUNT ON
- --更新指定的Customers
- UPDATE CUSTOMERS
- SET COMPANYNAME = @nvcCompanyName,
- ADDRESS = @nvcAddress
- WHERE CUSTOMERID = @ncCustomerID
- IF @@ERROR <> 0
- BEGIN
- --如果更新发生异常,返回-1
- SET @intReturnValue = -1
- RETURN
- END
- RETURN
4). 打开Visual Studio2008的服务器资源管理器,展开Northwind数据库的linq存储过程目录,将上叙四个存储过程依次拖入到打开的Northwind.dbml右侧方法区,查看引入的四个linq存储过程的属性,修改其Name名为"GetCustomers","DeleteCustomers","UpdateCustomers","InsertCustomers",如下图所示:
四个linq存储过程的属性
【编辑推荐】