Using the ADO.NET Entity Framework with the Advantage Database Server

Introduction

Whether you are new to Advantage databases or to the .NET framework and the Entity Framework, this article will provide you with the necessary information to productively use both together. Before getting started there are several prerequisites you will need to have installed before being able to develop with both the Entity Framework and the Advantage Database Server.

The Entity Framework was made available as part of Microsoft Visual Studio 2008 SP1. Updating VS 2008 to SP1 will enable the use of the Entity Framework as well as numerous other valuable enhancements. Advantage Database Server support for the Entity Framework was made available in the 9.10.0.9 version of the Advantage ADO.NET Data Provider. The latest Advantage ADO.NET Data Provider is available for download at Sybase's Getting Started for .NET Developers site.

Universal example databases, such as Northwind, are not as prevalent for Advantage. As such, this article and subsequent example will be using a custom database. The custom database is for storing information on pet owners and their pets. It captures basic information for the owner and links that information to basic information about one or more of the owner's pets. The database uses a data dictionary to link the tables called "PetOwners.add". The two tables that make up the PetOwners database are 'Pet' and 'Owner'.


Figure 1

Solution Setup

Open up Microsoft Visual Studio 2008 SP1 and create a new Windows Forms project called "OwnerPetMatcher". On the form, add the following controls:

  • DataGridView - dgvOwners
  • DataGridView - dgvPets
  • Button - cmdSave
  • Button - cmdClose

When finished your forms should look similar to the one captured in the screen shot below.


Figure 2

Next, create and wire-up the events for the Form load and the Close click. Inside the Close click event add the line 'this.Close();'. Before we can wire up the Form load event to populate the Owners data grid, we must create and configure the Entity Data Model for the custom PetOwners database.

Creating the ADO.NET Entity Framework Data Model

In the Solution Explorer window, right-click on the Project and select Add -> New Item... From the list of templates, select the ADO.NET Entity Data Model and name it 'PetOwner.edmx'


Figure 3 ADO.NET Entity Framework Data Model

When you click Add, Microsoft Visual Studio 2008 will launch the Entity Data Model Wizard. This wizard is helpful in walking you through the necessary steps to either generate a data model from an existing database or to create one manually. It is highly recommended that if you have a defined database before beginning development that you use the 'Generate from database' option. In this example, since there is an existing database, select the 'Generate from database' option and click Next.

[model4.jpg]
Figure 4

In the next step, you configure your connection to the database. Click the New Connection button in the top right of the screen. Depending on your settings in Microsoft Visual Studio 2008, you may have a default data provider already selected. If this is the case, and it is not Advantage Database Server, then you will need to click the Change... button to change the provider to the Advantage data provider. The Choose Data Source dialog will pop-up automatically if you don't have a pre-selected data provider. In either case you will want to select the Advantage Database Server data provider before continuing to configure the connection properties.


Figure 5

The Advantage ADO.NET Data Provider allows you to configure the numerous connection properties to the Advantage database. Those properties not configured are automatically populated with the default values by the data provider. The properties that must be configured are the Data Source and User ID / Password, if the database is configured to use a User ID and Password. For the PetOwners database enter the path to the ADD file, and if you created the database with a User ID and Password enter those as well.

NOTE: As a helpful hint, version 9.1 does not provide 64-bit support for the Advantage Local Server. Attempt to connect using the Server Type of LOCAL, and if that fails with a 6420 error then switch to REMOTE.

When finished entering all the properties, click the Test Connection button to ensure that the properties entered create a valid connection to the database. Once the connection test is successful click OK and then Next on the wizard screen.

The final step in the Entity Data Model Wizard is to select which database objects (tables, views, stored procedures) are going to be mapped in the data model. For the PetOwners database, this means selecting the two tables and clicking Finish. This step in the wizard also allows you to enter a namespace for the data model, if you so choose. After clicking Finish the wizard will create the data model automatically, adding all tables that were selected as well as mapping the relationships between the tables. Finally, the wizard adds a reference to System.Data.Entity in the project. When the wizard is finished processing, Microsoft Visual Studio displays the data model as shown in the example below for the PetOwners database.


Figure 6

Consuming the Advantage database via the ADO.NET Entity Data Model

In the code view of the form, create a new form level variable for the PetOwner entity data model, called 'PetOwnerContext'. The form level variable is used rather than a local variable due to the need to update the data later in the example. In the form's load event, initialize the form variable by using the new command. From here are there numerous ways the data can be accessed and bound to the data grid views created earlier. The quickest approach may just be to use LINQ and a var variable to query and bind the data. While this works, some nice Visual Studio IntelliSense features are lost without performing some casting that can be confusing depending on your knowledge of LINQ and Extension Methods.

This example will use an ObjectQuery to return an Owner type that can then be bound to the owner's data grid view. Before doing so the following using statements have to be added to the form.

using System.Data.Objects;
using System.Data.Objects.DataClasses;

Once the using statements are inserted, add the following code the form's load event. Note that in the query for Owners, the Pet objects are also being included. This is a very nice feature of the Entity Data Model that allows objects to be nested when a relationship exist in the database. By including the Pet objects now, we save ourselves from having to write more code later to query the database again to get the Pet data.

private void OwnerPetMatcherForm_Load(object sender, EventArgs e)
{
      PetOwnerContext = new PetOwners();

      ObjectQuery<Owner> owners = PetOwnerContext.Owner.Include("Pet");

      dgvOwners.DataSource = owners;
      dgvOwners.Columns["OwnerID"].Visible = false;
      dgvOwners.Columns["Pet"].Visible = false;

      dgvOwners.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
      dgvOwners.AutoResizeColumns();
}

Run the application and you will see the owner's data grid view populated with the owner table's data. This is a good example of the Entity Framework's power: With only ~2 lines of code (the additional lines are primarily for formatting), this was possible. Now we will want to populate the pet's data grid view based on the selected owner. To do so first create the event handler for the SelectionChanged event on the owner's data grid view. Inside this event we will retrieve the selected owner object from the control and use it to bind the linked pets to the pet's data grid view. Add the following code to the SelectionChanged event.

  private void dgvOwners_SelectionChanged(object sender, EventArgs e)
{
      if (dgvOwners.SelectedRows.Count <= 0)
          return;

      Owner selectedOwner = (Owner)dgvOwners.SelectedRows[0].DataBoundItem;

      dgvPets.DataSource = selectedOwner.Pet;
      dgvPets.Columns["PetID"].Visible = false;
      dgvPets.Columns["Owner"].Visible = false;

      dgvPets.AutoResizeColumns();
}

Run the application and click through the owners to retrieve and display the list of pets each owns. The next step will be to code the form to save changes made in the grids.

Updating the Advantage database via the ADO.NET Entity Data Model

Reading data from a database is typically fairly simple and straightforward. Complications traditionally introduce themselves when trying to write data back to a database in the form of insert and update statements. Constraints, data types, default values, auto-incrementing values, and relationships all come to mind as some of the little nuances that make updating data more complicated than reading data. The good news is that this is not the case with the Entity Framework. The Entity Framework removes the developer from having to deal with those little nuances.

In fact it could be argued that updating is less complicated with the Entity Framework than reading. This is because while it took ~2 lines of code to read in the data, it will only take 1 line of code to update the data.

Create the event handler for the Save button's click event. Inside the event add the following code.

  private void cmdSave_Click(object sender, EventArgs e)
{
     PetOwnerContext.SaveChanges();
}

With that line of code all changes to the data, owners and pets, as well as any new owner and pet records will be updated back to the Advantage database.

A Few Helpful Tips

If you are unhappy with the whitespace or trailing spaces left by reading in Advantage character data types to the form controls, use the TrimTrailingSpaces properties when configuring the connection to the database.

I have noticed an interesting issue when configuring a connection string to an Advantage database where Microsoft Visual Studio will test the connection successfully either as a server type of Local or Remote. Then when the application runs it fails. I found that if I change the build configuration from 'Any CPU' to 'x86', this is resolved. While I have not been able to track down the exact cause, I believe this has to do with the current version (v9.1) not fully supporting 64-bit architecture. The good news is that the upcoming release of v10 will fully support 64-bit architecture.

Conclusion

You have just walked through creating a Windows Forms application that uses the Entity Framework to consume and update data from an Advantage database. From the example you can see that the ADO.NET Entity Framework Data Model is a powerful addition to Visual Studio that allows quick and efficient access to Advantage databases.



About the Author

Matt Goebel

Matt Goebel is the Founder and President of AP Innovation, Inc. in Indianapolis, Indiana. He can be reached at 859-802-7591 or matt.goebel@apinnovation.com.