The ASP.NET
application that we will create we be a 3 tier application, consisting of a data access layer (DAL
), a business logic layer (BLL
) and a presentation layer (PL
).
We will be using Visual Studio Orcas Beta 1 for this tutorial.
Defining our database schema
Before we jump into coding some C#
or ASP.NET
we need to setup our database, more importantly we first need a database! If you haven’t already go and grab yourself a copy of SQL Express 2005. I will be using SQL Server Management Studio 2005 to create and define my database; however, you can do all of this using Visual Studio 2005 if you want.
Creating a database
Let’s go ahead and create a database for this tutorial. I’m going to create a database called TodoList
. To create a database right click the Databases folder in the Object Explorer window and click New Database (Figure 4-1).
Figure 4-1: Creating a database
When the New Database window is visible enter TodoList
(or any other name you want to use) as the Database name (Figure 4-2).
Figure 4-2: Specifying the database name
Adding the Tasks table
With our database created we will now add a Tasks
table. In SQL Server Management Studio right click the Tables folder and select New Table (Figure 4-3).
Figure 4-3: Creating the Tasks table
In the table designer replicate the settings from Figure 4-4.
Figure 4-4: Defining the columns
When you save the table, save it as Tasks
(Figure 4-5).
Figure 4-5: Saving the table
Creating the stored procedures
We need to define three stored procedures, one for adding a new task
, one for updating the state of a task, and finally one for retrieving all tasks of a specific state (either active
, or closed
).
Creating a stored procedure is easy, simply right click on the Stored Procedures folder and click New Stored Procedure (Figure 4-6).
Figure 4-6: Creating a stored procedure
Below are the three code listings for the stored procedures we will use.
Figure 4-7: AddTask
- create procedure AddTask
- @Title nvarchar(50),
- @Complete bit
- as
- insert into Tasks (Title, Complete)
- values (@Title, @Complete)
Figure 4-8: GetTasksByState
- create procedure GetTasksByState
- @Complete bit
- as
- select TaskID, Title, Complete
- from Tasks
- where Complete = @Complete
- order by Title
Figure 4-9: UpdateTaskState
- create procedure UpdateTaskState
- @TaskID int,
- @Complete bit
- as
- update Tasks
- set Complete = @Complete
- where TaskID = @TaskID
Setting up our data access layer
With our database ready let’s go ahead and create our DAL
. We will use the LINQ
to SQL
file designer in Visual Studio Orcas to do this.
TodoList
database in the Server Explorer window.Right click on your website project and add a new LINQ
to SQL
file called TodoList
(Figure 4-10).
Figure 4-10: Adding a LINQ to SQL file to our project
With the file created drag and drop the Tasks
table and the three stored procedures we created on to the design canvas of the designer (Figure 4-11).
Figure 4-11: Adding our table and sprocs to our DAL
That’s it! Our DAL
is all ready to use.
Adding a business logic layer
In this tutorial there is not much point to adding a BLL
, but we will add one nonetheless.
This layer in a real life application this layer would enforce any business rules (parameter checking etc…). Also in a real life application you would want to separate your DAL
and BLL
into their own components.
Right click the App_Code
folder and add a new class called TasksBll.cs
, then copy and paste the class definition given in Figure 4-12 into that file.
Figure 4-12: TasksBll.cs
- using System;
- using System.Collections.Generic;
- using System.Linq;
- namespace Org.GBarnett.Dns
- {
- public static class TasksBll
- {
- /// <summary>
- /// Gets all tasks of a particular state
- /// </summary>
- public static List<GetTasksByState> GetTasksByState()
- {
- using (TodoListDataContext db = new TodoListDataContext())
- {
- return (from t in db.GetTasksByState() select t).ToList<GetTasksByState>();
- }
- }
- /// <summary>
- /// Adds a new task
- /// </summary>
- public static void AddTask(string title, bool complete)
- {
- using (TodoListDataContext db = new TodoListDataContext())
- {
- db.AddTask(title, complete);
- }
- }
- /// <summary>
- /// Updates a tasks state
- /// </summary>
- public static void UpdateTaskState(int taskId, bool complete)
- {
- using (TodoListDataContext db = new TodoListDataContext())
- {
- db.UpdateTaskState(taskId, complete);
- }
- }
- }
- }
Each method of the TasksBll.cs
(Figure 4-12) performs the appropriate operation by accessing methods defined in our DAL
.
Enter ASP.NET…
As previously mentioned we will use ASP.NET
to define the UI.
The UI will consist of 3 controls:
- DropDownList
- GridView
- FormView
Before we begin add a new AJAX Web Form
to your project, the name you give it is irrelevant. Add all the following code snippets inside the form
tag of the page.
We have two states
Either a task
is active
or complete
. We will use a drop down list control to store these values (Figure 4-12).
Figure 4-13: DropDownList containing the tasks states
- <asp:DropDownList ID=“ddlState” runat=“server” AutoPostBack=“true”>
- <asp:ListItem Selected=“True” Text=“Active” Value=“false” />
- <asp:ListItem Text=“Complete” Value=“true” />
- </asp:DropDownList>
Adding an ObjectDataSource
Before we add any more controls to our web page we need to hook our presentation layer up with our BLL
. To do this we will add an ObjectDataSource
and wire it up to the appropriate methods defined in our BLL
.
Figure 4-14: Adding an ObjectDataSource
- <asp:ObjectDataSource
- ID=“odsTasks”
- runat=“server”
- TypeName=“TasksBll”
- SelectMethod=“GetTasksByState”
- InsertMethod=“AddTask”
- UpdateMethod=“UpdateTaskState”>
- <SelectParameters>
- <asp:ControlParameter
- ControlID=“ddlState”
- Name=“complete”
- PropertyName=“SelectedValue”
- Type=“Boolean” />
- </SelectParameters>
- <UpdateParameters>
- <asp:Parameter Name=“taskId” Type=“int32” />
- <asp:Parameter Name=“complete” Type=“boolean” />
- </UpdateParameters>
- <InsertParameters>
- <asp:Parameter Name=“title” Type=“string” />
- <asp:Parameter Name=“complete” Type=“boolean” />
- </InsertParameters>
- </asp:ObjectDataSource>
Using a GridView to view the tasks
We will now go ahead and add a GridView
to our page (Figure 4-15).
Figure 4-15: Adding a GridView to our page to display the tasks
- <asp:GridView
- ID=“gvTasks”
- runat=“server”
- DataSourceID=“odsTasks”
- AutoGenerateColumns=“false”
- DataKeyNames=“TaskID”
- AllowPaging=“true”
- GridLines=“None”
- AlternatingRowStyle-CssClass=“alternate”>
- <Columns>
- <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
- <asp:TemplateField HeaderText=“Complete”>
- <ItemTemplate>
- <asp:CheckBox ID=“cbI” runat=“server” Checked=‘<%# Eval(“Complete”) %>’
- Enabled=“false” />
- </ItemTemplate>
- <EditItemTemplate>
- <asp:CheckBox ID=“cbE” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ />
- </EditItemTemplate>
- </asp:TemplateField>
- <asp:CommandField ShowEditButton=“true” />
- </Columns>
- </asp:GridView>
Using the FormView to add more tasks
To add a new task
we will use a FormView
(Figure 4-16).
Figure 4-16: Adding a FormView to allow the user to add new tasks
- <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
- <InsertItemTemplate>
- <p><strong>Title:</strong>
- <asp:TextBox ID=“txtTitle” runat=“server” Text=‘<%# Bind(“Title”) %>’ /></p>
- <p><strong>Complete:</strong>
- <asp:CheckBox ID=“cbC” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ /></p>
- <p><asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” /></p>
- </InsertItemTemplate>
- </asp:FormView>
Adding AJAX functionality to the GridView and the FormView controls
Just to smooth things up I’ve added a few UpdatePanel’s to the page, each wrapping the GridView
and FormView
respectively.
Figure 4-17: The updated GridView
- <asp:UpdatePanel ID=“upTasks” runat=“server”>
- <ContentTemplate>
- <asp:GridView
- ID=“gvTasks”
- runat=“server”
- DataSourceID=“odsTasks”
- AutoGenerateColumns=“false”
- DataKeyNames=“TaskID”
- AllowPaging=“true”
- GridLines=“None”
- AlternatingRowStyle-CssClass=“alternate”>
- <Columns>
- <asp:BoundField DataField=“Title” HeaderText=“Task” ReadOnly=“true” />
- <asp:TemplateField HeaderText=“Complete”>
- <ItemTemplate>
- <asp:CheckBox ID=“cbI”
- runat=“server”
- Checked=‘<%# Eval(“Complete”) %>’
- Enabled=“false” />
- </ItemTemplate>
- <EditItemTemplate>
- <asp:CheckBox ID=“cbE” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ />
- </EditItemTemplate>
- </asp:TemplateField>
- <asp:CommandField ShowEditButton=“true” />
- </Columns>
- </asp:GridView>
- </ContentTemplate>
- <Triggers>
- <asp:AsyncPostBackTrigger ControlID=“ddlState” />
- </Triggers>
- </asp:UpdatePanel>
Figure 4-18: The update FormView
- <asp:UpdatePanel ID=“upAdd” runat=“server”>
- <ContentTemplate>
- <asp:FormView ID=“fvTask” runat=“server” DefaultMode=“Insert” DataSourceID=“odsTasks”>
- <InsertItemTemplate>
- <p><strong>Title:</strong>
- <asp:TextBox ID=“txtTitle” runat=“server” Text=‘<%# Bind(“Title”) %>’ /></p>
- <p><strong>Complete:</strong>
- <asp:CheckBox ID=“cbC” runat=“server” Checked=‘<%# Bind(“Complete”) %>’ /></p>
- <p>
- <asp:LinkButton ID=“lbAdd” runat=“server” Text=“Add Task” CommandName=“Insert” />
- </p>
- </InsertItemTemplate>
- </asp:FormView>
- </ContentTemplate>
- </asp:UpdatePanel>
And on that note we are finished! You can download the full source code at the end of this tutorial.
The experience
From personal experience coding the data access layer can be a very boring process – in this tutorial we have seen how effective LINQ
to SQL
can be when creating our DAL
.
Visual Studio Orcas has introduced a few really cool tools to further assist with the creation of the DAL
– we no longer need to spend hours on OR mapping. The great thing about LINQ
is the ability to further query your DAL
, and create some anonymous type.
There is no doubt in my mind that LINQ
is set to revolutionize the way programmers interact with data, more so is the fact that we always use a familiar set of standard query operators!
If you download and run the source you will see the site shown in Figure 4-19.