The next step is to create a new Class Library project using
Visual Studio 2010. This project will be the data access layer.
1.
Launch Visual Studio 2010.
2.
Select FileàNew Project from
the menu.
3.
Select Visual C# Class Library project and name the project
OrderSystemDAL.
4.
Make sure the "Create directory for solution" checkbox is
checked and change the Solution Name to OrderSystem.
5.
Click the OK button.
6.
Visual Studio will create the solution and project for you. Now you can
add an Entity Data Model to the project. Right click on the OrderSystemDAL
project and select AddàNew Item…
from the pop-up menu.
7.
The Add New Item dialog should appear. Select ADO.NET Entity Data Model
from the list of templates and change the name of the file to OrderSystemDataModel.edmx.
Click the Add button.
8.
The Entity Data Model Wizard will appear. Select "Generate from
database" and click the Next button.
9.
The next screen asks you for the database. Click the New Connection
button. Enter the name of your server and then select the OrderSystem database
from the list of databases and click the OK button.
10. Click
the Next button.
11. The
next screen asks you to choose your database objects. You want to select the
UserAccounts table and the five stored procedures create in step 1. You can
see the tables or stored procedures by click on the plus(+) sign next to the
Tables or Store Procedures nodes. Simply check the box next to the object to
indicate that you want to import the object.
12. Click
the Finish button. Visual Studio will now create the OrderSystem data model
for you.
You still need
to create the functions to execute the stored procedures. To do this you
simply right click on the stored procedure under the Stored Procedures folder
and select Add Function Import from the pop-up menu.
13. Click
the plus sign next to the Stored Procedures folder in the Model Browser and
right click on the UserAccounts_Delete stored procedure.
14. Select
Add Function Import… from the pop-up menu.
15. The
Add Function Import dialog box should appear. Leave the Function Import Name
and Stored Procedure Name the same. Since this stored procedure does not
return any value select None from the "Returns a Collection Of" list.
Now click the OK button. The method will now appear under the Function Imports
node in the Model Browser.
16. Right
click on the UserAccounts_Insert stored procedure and select Add Function
Import… from the pop-up menu. Since this procedure will return the Id of the
newly added record you need to change the "Returns a Collection Of"
to Scalars and choose Int32 from the list. Click the OK button.
17. Repeat
the same steps for the other procedures. For the SelectAll and SelectById
procedures you should change the "Returns a Collection Of" to
Entities and select UserAccounts from the list. The Update procedure does not
return a value either so you can select None for its return type.
You now have all
you need in the Entity Data Model. The next step is to create a class in the
data layer to manage the calls to the procedures associated with the
UserAccount table.
18. In
the Solution Explorer, right click on the OrderSystemDAL project and select AddàClass… from the pop-up menu.
19. Name
the class UserAccountsData.cs and click the Add button.
20. Change
the class declaration to make it public and static.
public static class UserAccountsData
21. Add
the follow methods to the class.
public static int Insert(string firstName, string lastName, DateTime insertDate)
{
using (OrderSystemEntities db = new OrderSystemEntities())
{
return Insert(db, firstName, lastName, insertDate);
}
}
public static int Insert(OrderSystemEntities db, string firstName,
string lastName, DateTime insertDate)
{
return db.UserAccounts_Insert(firstName, lastName, insertDate, insertDate).ElementAt(0).Value;
}
public static void Update(int id, string firstName, string lastName,
DateTime updateDate)
{
using (OrderSystemEntities db = new OrderSystemEntities())
{
Update(db, id, firstName, lastName, updateDate);
}
}
public static void Update(OrderSystemEntities db, int id, string firstName,
string lastName, DateTime updateDate)
{
db.UserAccounts_Update(id, firstName, lastName, updateDate);
}
public static void Delete(int id)
{
using (OrderSystemEntities db = new OrderSystemEntities())
{
Delete(db, id);
}
}
public static void Delete(OrderSystemEntities db, int id)
{
db.UserAccounts_Delete(id);
}
public static UserAccount SelectById(int id)
{
using (OrderSystemEntities db = new OrderSystemEntities())
{
return SelectById(db, id);
}
}
public static UserAccount SelectById(OrderSystemEntities db, int id)
{
return db.UserAccounts_SelectById(id).ElementAtOrDefault(0);
}
public static List<UserAccount> SelectAll()
{
using (OrderSystemEntities db = new OrderSystemEntities())
{
return SelectAll(db);
}
}
public static List<UserAccount> SelectAll(OrderSystemEntities db)
{
return db.UserAccounts_SelectAll().ToList();
}This is the
class you should use to manipulate the data in the UserAccounts table.
