Using LINQ in ASP.NET (2)

上一篇Using LINQ in ASP.NET (1)中介绍了利用LINQ to SQL实现对数据的增删改的操作,但是在实际的项目应用中,我们经常会使用到存储过程。本篇将介绍如何利用LINQ对存储过程进行操作。

我们利用的还是Northwind数据库,首先创建存储过程

(1)返回所有EMPLOYEES 的信息

CREATE PROCEDURE [dbo].[Employees_GetAll]
AS
SELECT * FROM EMPLOYEES ORDER BY EMPLOYEEID

(2)根据EMPLOYEEID获得信息

CREATE PROCEDURE [dbo].[Employees_GetByID]
(
@ID int
)
AS
SELECT * FROM EMPLOYEES WHERE EMPLOYEEID=@ID

(3)添加职员信息

CREATE PROCEDURE [dbo].[Employees_Insert]
(
@FIRSTNAME NVARCHAR(20),
@LASTNAME NVARCHAR(20)
)
AS
INSERT INTO EMPLOYEES(FIRSTNAME,LASTNAME)
VALUES(@FIRSTNAME,@LASTNAME)

(4)更新职员信息

CREATE PROCEDURE [dbo].[Employees_Update]
(
@ID INT,
@FIRSTNAME NVARCHAR(20),
@LASTNAME NVARCHAR(20)
)
AS
UPDATE EMPLOYEES
SET FIRSTNAME=@FIRSTNAME,
LASTNAME=@LASTNAME
WHERE EMPLOYEEID=@ID

(5)删除职员信息

CREATE PROCEDURE [dbo].[Employees_Delete]
(
@ID INT
)
AS
DELETE FROM EMPLOYEES WHERE EMPLOYEEID=@ID

前面我们知道了如何将一个数据表映射为实体类,现在我们要将存储过程也相应的映射成为实体类,创建LINQ to SQL 的类文件。

ContractedBlock.gif ExpandedBlockStart.gif Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->[Function(Name="Employees_GetAll")]
publicISingleResult<Employee>GetAllEmployees()
{
IExecuteResultresult
=this.ExecuteMethodCall
(
this,((MethodInfo)(MethodInfo.GetCurrentMethod())));
return((ISingleResult<Employee>)(result.ReturnValue));
}

GetAllEmployees() 方法利用[Function]属性进行描述,由于存储过程的返回值可能是一个或多个记录,方法返回值的类型必须和ISingleResult类型相匹配。存储过程返回的字段页必须和Employee类中声明的相一致。

GetEmployeeByID() 接收一个参数,并且返回一行

ContractedBlock.gif ExpandedBlockStart.gif Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->[Function(Name="Employees_GetByID")]
publicISingleResult<Employee>GetEmployeeByID
([Parameter(Name
="ID",DbType="Int")]
System.Nullable
<int>iD)
{
IExecuteResultresult
=this.ExecuteMethodCall
(
this,((MethodInfo)(MethodInfo.GetCurrentMethod())),
iD);
return((ISingleResult<Employee>)(result.ReturnValue));
}

调用存储过程的时候需要传入一个参数,方法的参数和存储类型的参数可以用[Parameter] 进行匹配。参数传给ExecuteMethodCall() 方法。大多数的代码都是差不多的。

下面是对数据添加,删除,修改的方法

ContractedBlock.gif ExpandedBlockStart.gif Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->[Function(Name="Employees_Insert")]
publicintInsertEmployee([Parameter(Name="FirstName",DbType="nvarchar(20)")]stringfname,[Parameter(Name="LastName",DbType="nvarchar(20)")]stringlname)
{
IExecuteResultresult
=this.ExecuteMethodCall(this,((MethodInfo)(MethodInfo.GetCurrentMethod())),fname,lname);
return(int)result.ReturnValue;
}

[Function(Name
="Employees_Update")]
publicintUpdateEmployee([Parameter(Name="ID",DbType="Int")]System.Nullable<int>iD,[Parameter(Name="FirstName",DbType="nvarchar(20)")]stringfname,[Parameter(Name="LastName",DbType="nvarchar(20)")]stringlname)
{
IExecuteResultresult
=this.ExecuteMethodCall(this,((MethodInfo)(MethodInfo.GetCurrentMethod())),iD,fname,lname);
return(int)result.ReturnValue;
}

[Function(Name
="Employees_Delete")]
publicintDeleteEmployee([Parameter(Name="ID",DbType="Int")]System.Nullable<int>iD)
{
IExecuteResultresult
=this.ExecuteMethodCall(this,((MethodInfo)(MethodInfo.GetCurrentMethod())),iD);
return(int)result.ReturnValue;
}

好了,方法创建好了,接下来就是在ASP.NET Web Form中对这些方法进行调用。

添加一个DetailsView 控件。对它进行数据的绑定,方法和(1)中的基本一样

ContractedBlock.gif ExpandedBlockStart.gif Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->privatevoidBindDetailsView()
{
stringstrConn=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDbdb
=newNorthwindDb(strConn);
ISingleResult
<Employee>results=db.GetAllEmployees();
DetailsView1.DataSource
=results;
DetailsView1.DataBind();
}

利用DetailsView 控件对数据执行增加,删除,修改的操作

ContractedBlock.gif ExpandedBlockStart.gif Code
<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->protectedvoidDetailsView1_ItemUpdating(objectsender,DetailsViewUpdateEventArgse)
{
stringstrConn=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDbdb
=newNorthwindDb(strConn);
db.UpdateEmployee((
int)DetailsView1.SelectedValue,((TextBox)DetailsView1.Rows[1].Cells[1].Controls[0]).Text,((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text);
}
protectedvoidDetailsView1_ItemInserting(objectsender,DetailsViewInsertEventArgse)
{
stringstrConn=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDbdb
=newNorthwindDb(strConn);
db.InsertEmployee(((TextBox)DetailsView1.Rows[
1].Cells[1].Controls[0]).Text,((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text);
}
protectedvoidDetailsView1_ItemDeleting(objectsender,DetailsViewDeleteEventArgse)
{
stringstrConn=ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDbdb
=newNorthwindDb(strConn);
db.DeleteEmployee((
int)DetailsView1.SelectedValue);
}

这样通过存储过程对数据操作就搞定了。

源码:/Files/gaoweipeng/aspLINQ2.rar

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值