我们利用的还是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 的类文件。
[Function(Name = "Employees_GetAll")]
public ISingleResult<Employee> GetAllEmployees()
{
IExecuteResult result = this.ExecuteMethodCall
(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<Employee>)(result.ReturnValue));
}
GetAllEmployees() 方法利用[Function]属性进行描述,由于存储过程的返回值可能是一个或多个记录,方法返回值的类型必须和ISingleResult类型相匹配。存储过程返回的字段页必须和Employee类中声明的相一致。
GetEmployeeByID() 接收一个参数,并且返回一行
[Function(Name="Employees_GetByID")]
public ISingleResult<Employee>
GetEmployeeByID([Parameter(Name = "ID", DbType = "Int")] System.Nullable<int> id)
{
IExecuteResult result = this.ExecuteMethodCall
(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), id);
return ((ISingleResult<Employee>)(result.ReturnValue));
}
调用存储过程的时候需要传入一个参数,方法的参数和存储类型的参数可以用[Parameter] 进行匹配。参数传给ExecuteMethodCall() 方法。大多数的代码都是差不多的。
下面是对数据添加,删除,修改的方法
[Function(Name = "Employees_Insert")]
public int InsertEmployee
([Parameter(Name = "FirstName", DbType = "nvarchar(20)")] string fname,
[Parameter(Name = "LastName", DbType = "nvarchar(20)")]string lname)
{
IExecuteResult result
= this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),
fname,lname);
return (int)result.ReturnValue;
}
[Function(Name = "Employees_Update")]
public int UpdateEmployee
([Parameter(Name = "ID", DbType = "Int")] System.Nullable<int> id,
[Parameter(Name = "FirstName", DbType = "nvarchar(20)")] string fname,
[Parameter(Name = "LastName", DbType = "nvarchar(20)")]string lname)
{
IExecuteResult result
= this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())),
id,fname, lname);
return (int)result.ReturnValue;
}
[Function(Name = "Employees_Delete")]
public int DeleteEmployee
([Parameter(Name = "ID", DbType = "Int")] System.Nullable<int> id)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), id);
return (int)result.ReturnValue;
}
好了,方法创建好了,接下来就是在ASP.NET Web Form中对这些方法进行调用。添加一个DetailsView 控件。对它进行数据的绑定,方法和(1)中的基本一样
private void BindDetailsView()
{
string strConn
= ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
ISingleResult<Employee> results = db.GetAllEmployees();
this.DetailsView1.DataSource = results;
this.DetailsView1.DataBind();
}
利用DetailsView 控件对数据执行增加,删除,修改的操作
protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)
{
string strConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
db.UpdateEmployee
((int)DetailsView1.SelectedValue,
((TextBox)DetailsView1.Rows[1].Cells[1].Controls[0]).Text,
((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text);
}
protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e){
string strConn = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
db.InsertEmployee(((TextBox)DetailsView1.Rows[1].Cells[1].Controls[0]).Text,
((TextBox)DetailsView1.Rows[2].Cells[1].Controls[0]).Text);
}
protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)
{
string strConn
= ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
db.DeleteEmployee((int)DetailsView1.SelectedValue);
}
这样通过存储过程对数据操作就搞定了。