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.