上一篇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 的类文件。
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() 接收一个参数,并且返回一行
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() 方法。大多数的代码都是差不多的。
下面是对数据添加,删除,修改的方法
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)中的基本一样
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 控件对数据执行增加,删除,修改的操作
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
分享到:
相关推荐
Start with a concise introduction to C# fundamentals using an early classes and objects approach, then rapidly move on to more advanced topics, including multithreading, .NET 4, LINQ, WPF, ASP.NET 4, ...
2008 along with the multitude of language improvements in C# 2008 and Visual Basic® 2008 as well as powerful new technology called LINQ, together with the ASP.NET 2.0 Framework you already know and ...
In this first book to provide a hands-on approach to refactoring in C# and ASP.NET, you’ll discover to apply refactoring techniques to manage and modify your code. Plus, you’ll learn how to build a...
This book will teach readers how to build and deploy a fully working example retail website using Microsoft ASP.NET MVC and Entity Framework technologies and recommendations. This book contains ...
Applied ASP.NET 4 in Context 952 pages Publisher: Apress; 1 edition (September 12, 2011) Language: English ISBN-10: 1430234679 ISBN-13: 978-1430234678 What you’ll learn •Understand the ...
using System.Linq; using System.Net; using System.Net.Http; using System.Text; using System.Web.Http; namespace DocOnlineView.UI.Controllers.MVCAPI { public class HomeController : ApiController { ...
asp.net发送电子邮件例子: using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI....
Mario was involved in several projects based on COM+ and DCOM with Visual Basic and Visual C++ as well as projects based on Java and J2SE. With Beta 2 of the .NET Framework, he started developing Web ...
Mario was involved in several projects based on COM+ and DCOM with Visual Basic and Visual C++ as well as projects based on Java and J2SE. With Beta 2 of the .NET Framework, he started developing Web ...
To be a successful ASP.NET 4 developer, you need to know how to apply the vast array of new functionality available in the latest release of the .NET 4 Framework and Visual Studio 2010. This book ...
在ASP.NET中,可以创建ASP.NET网站和ASP.NET应用程序,但是ASP.NET网站和ASP.NET应用程序开发过程和编译过程是有区别的。ASP.NET应用程序主要有以下特点: q 可以将ASP.NET应用程序拆分成多个项目以方便开发,管理和...
这是ASP.NET的文档using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace ATM { class Bank { private List<Account> accounts; public List<Account> Accounts ...
This book will teach readers how to build and deploy a fully working example retail website using Microsoft ASP.NET MVC and Entity Framework technologies and recommendations. This book contains ...
using System.Linq; using System.Text; using System.Windows.Forms; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private ...
It based on ADO.NET, and supported C#, Visual Basic, ASP.NET etc. It also provide DbEntryDataSource for ASP.NET 2.0 and a Rails style MVC framework and a simple IoC framework. The samples of the ...
If you are already familiar with MVC 5, the content in this book can get you started with ASP.NET Core 1.1 in a fast, no-fluff way. It's important to mention that this book is practical and tactical,...
using System.Linq; using System.Text; namespace information_of_computers { [Serializable] public class Computer { public Computer() { } public Computer(string name, string brand, string cpu, ...
using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApp1 { public class NBA_Star { public string FirstName { get; set; } public string LastName { get; set; }
Create powerful cross-platform applications using C# 6, .NET Core 1.0, ASP.NET Core 1.0, and Visual Studio 2015 About This Book Build modern, cross-platform applications with .NET Core 1.0 Get up-...