Using LINQ in ASP.NET (Part 2)
Introduction
In the Part 1 of this
series I discussed the basic operations of LINQ to SQL. By default when you use
LINQ to SQL queries as shown earlier, they internally use SQL statements i.e.
INSERT, UPDATE, DELETE and SELECT. In many real world applications you use
stored procedures. Luckily, LINQ to SQL has a way to call stored procedures from
your application. It involves bit of a work as compared to almost automated way
discussed earlier. In this article I will explain how stored procedures can be
consumed using LINQ to SQL.
Creating Stored Procedures
Before going any further create the following stored procedures in the
Northwind database.
CREATE PROCEDURE [dbo].[Employees_GetAll]
AS
SELECT * FROM EMPLOYEES ORDER BY EMPLOYEEID
CREATE PROCEDURE [dbo].[Employees_GetByID]
(
@ID int
)
AS
SELECT * FROM EMPLOYEES WHERE EMPLOYEEID=@ID
CREATE PROCEDURE [dbo].[Employees_Insert]
(
@FIRSTNAME NVARCHAR(20),
@LASTNAME NVARCHAR(20)
)
AS
INSERT INTO EMPLOYEES(FIRSTNAME,LASTNAME)
VALUES(@FIRSTNAME,@LASTNAME)
CREATE PROCEDURE [dbo].[Employees_Update]
(
@ID INT,
@FIRSTNAME NVARCHAR(20),
@LASTNAME NVARCHAR(20)
)
AS
UPDATE EMPLOYEES
SET FIRSTNAME=@FIRSTNAME,
LASTNAME=@LASTNAME
WHERE EMPLOYEEID=@ID
CREATE PROCEDURE [dbo].[Employees_Delete]
(
@ID INT
)
AS
DELETE FROM EMPLOYEES WHERE EMPLOYEEID=@ID
The purpose of each stored procedure is as follows:
- Employees_GetAll : Returns all the records from
Employees table
- Employees_GetByID : Returns one record matching the
supplied EmployeeID
- Employees_Insert : Inserts a new employee record
- Employees_Update : Updates an existing employee record
- Employees_Delete : Deletes an employee record
Mapping Stored Procedures
In the previous part you learnt to map database tables to entity classes.
Similar approach is taken in case of stored procedures also. The stored
procedures are mapped with methods of your data context class and are called
from your web form code. Let's see how this works with an example.
[Function(Name = "Employees_GetAll")]
public ISingleResult<Employee> GetAllEmployees()
{
IExecuteResult result = this.ExecuteMethodCall
(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
return ((ISingleResult<Employee>)(result.ReturnValue));
}
Carefully observe the above code. The GetAllEmployees() method is decorated
with [Function] attribute. The [Function] attribute maps a class method with a
stored procedure via its Name property. For stored procedures that return one or
more records the return type of the method must be a generic collection of type
ISingleResult. The columns returned by the stored procedure and the class
property names (Employee in above example) must match. Recollect that we created
the Employee class in Part 1 of this series. Inside we call ExecuteMethodCall()
method of the DataContext base class. The results returned by the stored
procedure are returned by the ReturnValue property of IExecuteResult instance.
Remember that you must import System.Reflection before compiling the above code
as MethodInfo class resides in that namespace.
The GetEmployeeByID() method accepts an employee ID and returns just one row.
[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));
}
Notice the code marked in bold letters. Since the Employees_GetByID stored
procedure accepts one parameter, we must pass it while calling the stored
procedure. The method parameter and stored procedure parameter are mapped using
[Parameter] attribute. The data type of the parameter is specified using DbType
property of [Parameter] attribute. The ID parameter is passed to the
ExecuteMethodCall() method. The remaining code is almost identical.
The following code shows methods for inserting, updating and deleting data.
[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;
}
The above code is almost identical to the previous one except few changes.
This time the methods return integer instead of ISingleResult and hence the
ReturnValue is type casted to int.
Ok. Now you are ready to call these methods from your website.
Add a new web form to your website and place a DetailsView control on it.
Then write a private method named BindDetailsView() as shown below:
private void BindDetailsView()
{
string strConn = ConfigurationManager.ConnectionStrings
["connstr"].ConnectionString;
NorthwindDb db = new NorthwindDb(strConn);
ISingleResult<Employee> results = db.GetAllEmployees();
DetailsView1.DataSource = results;
DetailsView1.DataBind();
}
This code should be familiar to you as we used it in Part 1 also. Here, we
call GetAllEmployees() method that returns ISingleResult collection of Employee
objects. The collection is then bound with the DetailsView. The BindDetailsView()
method is called from the Page_Load event handler.
To perform insert, update and delete operations we handle several events of
DetailsView control as shown below:
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);
}
Here, we simply call the InsertEmployee(), UpdateEmployee() and
DeleteEmployee() methods of our custom data context class. These methods in turn
call the mapped stored procedures.
The following screen shot shows a sample run of the web form after updating a
record.

In the above example we wrote every bit of code ourselves. Visual Studio
comes with an object relational designer (O/R designer) that simplifies your job
to a great extent. In the next part I will show how to use the O/R designer to
perform the same operations.