ITAP3011
Developing Windows Applications
Lab Tutorial – 9 & 10
LAB INSTRUCTIONS & EXERCISES
Object Persistence & Windows Forms Application Development using Entity Framework
The goal of this tutorial is to train you on how to work with ORM and carry out Object Persistence using the Microsoft Entity Framework when building a Windows Application. This tutorial assumes that you are familiar with the basics of EF through earlier tutorial and have practiced EDM creation and retrieving data using LINQ and Lambda. This tutorials and builds on the persistence operations which would help you to modify, add and delete records through working at Entity level.
The Microsoft Entity Framework is an Object Relational Mapping (O/RM) tool that enables you to generate a data access layer from a database automatically. The Entity Framework enables you to avoid the tedious work of building your data access classes by hand.
We shall continue working with the Dafesty database to illustrate the Entity Framework.
EDM Creation:
Create an Entity Data Model consisting of Customer and Country Entities to arrive at the following Model.
Note:
a. The following are the naming and choices that we have made:
i. Model Name: DafestyModel
ii. Context: DafestyEntities
iii. Include Foreign Key field is unchecked – so that country code does not appear as an attribute to the Customers entity.
b. Further Modelling: As a proof of concept to mapping variations, the MemberCategory column generated as an attribute has been renamed to MemberRating with appropriate mapping.
Driver Form:
Create a form as below to test the various operations.
Retrieving a single customer
Using the EDM, we would retrieve a single customer with id 1818 and display his Customer Name and Member Category in the Label.
private void QueryButton_Click(object sender, EventArgs e)
{
DafestyEntities ctx = new DafestyEntities();
Customer c = ctx.Customers.Where(x = x.CustomerID == -1818-).First(); label1.Text = c.CustomerName + - - + c.MemberRating;
}
Note:
• We use MemberRating for the field when working with the object (not the column name MemberCategory).
• The .First method throws exception if there are no retrievals. If you want to avoid exception and assign null to c, then you would use .FirstOrDefault method.
The output would be as below:
Updating customer data
We would now modify the member category of customer 1818 from A to B.
Note:
• To modify an existing customer, you should first retrieve the customer data from the database, make the desired changes to the customer object and persist back to the database.
• Often, beginners make the mistake of trying to re-create a customer, which is wrong as you may be trying to create a duplicate row for the same customer and a exception would result. Hence first retrieve the object and then persist it.
private void QueryButton_Click(object sender, EventArgs e)
{
DafestyEntities ctx = new DafestyEntities();
Customer c = ctx.Customers.Where(x = x.CustomerID == -1818-).First();
c.MemberRating = -B-;
ctx.SaveChanges();
}
• The save changes method performs the necessary updates of all changes that has been done on the data held by the EDM context.
• This could include several changes to several entities and entity collections.
• Changes can be inserts, deletes, modification of values or even object association changes.
• Upon performing the updates review the database to ensure that the new value of MemberCategory (i.e., Customer 1818 is now B).
Creating a new customer
We would now create a new customer with id 1007 with CustomerName Venkat and MemberCategory as B. This would eventually mean that a customer record gets inserted when the persistence operation is carried out.
private void QueryButton_Click(object sender, EventArgs e) {
DafestyEntities ctx = new DafestyEntities();
Customer c = new Customer();
c.CustomerID = -1007-;
c.CustomerName = -Venkat-;
c.MemberRating = -B-; ctx.Customers.Add(c);
ctx.SaveChanges(); } Note:
• Since the customer is a new customer, create a new object.
• Set all values and importantly the Customer ID, which should be assigned a value that no other current customer uses (else a primary key violation would occur).
• Add the customer object to the Customers collection of the context. Creation of new Customer object does not link it to the context automatically.
• The SaveChanges would determine that this is a new object added to the Customer Collection and hence would issue an Insert command to the database.
Deleting an existing customer
We would now demonstrate how to delete an existing customer. i.e, Customer with id 1007.
private void QueryButton_Click(object sender, EventArgs e)
{
DafestyEntities ctx = new DafestyEntities();
Customer c = ctx.Customers.Where(x = x.CustomerID == -1007-).First(); ctx.Customers.Remove(c);
ctx.SaveChanges(); }
Note:
• Since the customer is existing customer, we would retrieve the customer object and delete.
• The operation requires to remove the object from the collection and save.
• The SaveChanges would issue the necessary delete statement to the database.
• It is important that you should delete a Customer who has no other related records in other tables. Otherwise a foreign key violation may happen due to referential integrity check at the database.
WORKING WITH MULTIPLE OBJECTS
Customer 1818 is currently in Singapore (SIN) and we wish that he should be moved to Malaysia (MAL).
private void QueryButton_Click(object sender, EventArgs e)
{
DafestyEntities ctx = new DafestyEntities();
Customer c = ctx.Customers.Where(x = x.CustomerID == -1818-).First(); Country cn = ctx.Countries.Where(x = x.CountryCode == -MAL-).First(); c.Country = cn;
ctx.SaveChanges();
}
Note:
• In object model we associate objects directly rather than updating join fields.
• This is not the case in relational table where we need to have the country code to link. This is the reason why pure object orientation demands that you should not include the country code in Customer, but rather provide a reference to an appropriate country object.
• The operation actually updates the database and sets the country code value for customer 1818 to MAL.
Alternate approach:
• The above example performed the association from Customer Side, i.e., n-side pointing to 1 side.
• Since EDM associations are bi-directional, we can perform the same operation from Country Side (i.e., n-side to 1-side) – even though it sounds rather unnatural, it is the case in many situations (esp if it is n-n association)
• This is akin to adding the customer to the list of customers in that country.
• The following code produces identical results to the above while performing the association.
private void QueryButton_Click(object sender, EventArgs e)
{
DafestyEntities ctx = new DafestyEntities();
Customer c = ctx.Customers.Where(x = x.CustomerID == -1818-).First();
Country cn = ctx.Countries.Where(x = x.CountryCode == -MAL-).First(); cn.Customers.Add(c);
ctx.SaveChanges();
}
EXERCISES
1. Create an EDM that would consist of Movie entity and Producer Entity.
2. Make sure your entity is called Movie and not Movy!
3. In the object model, MovieType should be called Genre and RentalPrice as RentalCost. Make the appropriate changes in one instance from the designer and in other instance in the XML code that defines the EDM.
4. Perform the following CRUD operations
a. Retrieve the movie with code 5 (Nemesis)
b. Update the rental cost of this movie to $1.80
c. Create a new Movie with id 400, name of the movie is Sully, genere is Drama, Producer is Warner Brothers, rental price is $2.50, Rating is U, number of copies (total stock) is 4.
d. Modify the Producer of Demolition Man (code 4) from Universal to Pixar. Alter the association from Movie object (1-side).
e. Modify the Producer of Die Hard 2 (code 11) from Pixar to Warner. Alter the association from the Producer side (n-side).
f. Delete the movie with code 400.
5. Create a Movie Data Maintenance Screen as below. Provide Navigation Buttons, and buttons for various CRUD operation and Find operation. Try to make the screen as functional and user friendly as possible.