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.

Comments

  • http://www.oakleysunglassesoutc.com/ qzwnvo

    Posted by http://www.oakleysunglassesoutc.com/ Mandyfle on 03/29/2013 07:23pm

    ghd sale,ghd hair straightener,The Russian line of sight from Europe to the Far East is British phase Salisbury's glad to see, but the Russian forces in the Far East too expansion of British authority in the Far East, has threatened to challenge Britain's free trade policy, and even rejected British-intentioned proposals. The Tan Yankai the hearts very clear, coronation of Russian Tsar Nicholas II will make a comeback opportunity to Li Hongzhang, despite ghd,hairstraighteneraul.ghd straightener,com/" title="ghd australia"ghd australia will not have served twenty-five years the Zhili Governor and the Northern Minister of the glory, but the Sino-Japanese war failure makes the psychological fear of foreigners and Japanese, while Li Hongzhang Russians named to become the only Chinese people are eligible to travel to the Russian ceremony which component is too heavy - Russia is sending warships to Japan turn lap let succumbed, accommodating China in Korea by the Japanese played find the North, which will undoubtedly give enough of the Li Hongzhang a comeback political chips.

    Reply
  • cheap wayfarer sunglasses

    Posted by igliliImpumprvb on 03/28/2013 09:09pm

    http://sunglasswholesaleofgucci.webs.com - cheap oakleys for sale fake oakley sunglasses http://replicaguccisunglasses.webs.com - replica sunglasses cheap ray ban http://fakeGucciwayfarer.webs.com - fake ray ban cheap ray ban,,,, http://wholesalesunglassescool.webs.com - wholesale sunglasses replica oakley sunglasses http://discountsunglassessale.webs.com - akley discount fake ray ban

    Reply
  • Isabel Marant Sneakers

    Posted by Hauddessy on 03/28/2013 09:40am

    [url=http://future-select.co.uk/fckeditor/isabelmarantsneakers.aspx]isabel marant boot sale[/url] 07 qiu dong demonstrate,,, like mashups, low-key, can specification! Isabel Marant is new period designers in France, a only one won a colleague of the supranational forge have attention. After graduating from fashion style in Paris Studio Bercot, Isabel Marant Yorke and Cole in his figure as a colleague to follow. Spring/summer 2008 way divulge in Paris - the Isabel Marant [Isabel Marant is fire! Unmatched display get into increased in sneakers tide fan out street "mixing in the" unavoidable Good look at star drive part, nothing but certain, Isabel Marant sneakers increased works in Europe and the Common States is a real spark off! Star, famous type wearing! At hand fashion label Isabel ma LAN (Isabel Marant) to motivate the motion of the unique cyclone, with dream of skirts, pants, leather pants, etc. Contrastive item with Isabel ma LAN (Isabel Marant), the craze sneaker avenue quick demonstrations, recover sports sandals feeble rocks in inspiration. Isabel ma LAN (Isabel Marant) launch for good occasionally pushed on this good-natured of shoes is in dernier cri outside, snapping up, at proximate in many foreign shopping website entertain been sold out. Miranda Kerr, negroid leather pants with red [url=http://gateway.recruitment-websites.co.uk/fckeditor/isabelmarant.aspx]isabel marant heels[/url], reasonably of the color and style. Black and white alliance colors, Isabel Marant tie-in and impermeable jeans, locomotive leather, big Glowering and off-white match colors Isabel Marant jeans match and the aggregate heart Isabel Marant Black tie put on clothing + frog mirror, is not a indelicate examination Kate potts voss, Isabel Marant sneaker together with the heap green chiffon dresses to rub off last, wager is a mashup Thick cream + hand-knitted sweater fastens with color Isabel Marant Isabel Marant sneaker,Cambridge package fluorescent color highlight is the sound portion Lovers outdoors of the boulevard, Isabel Marant makes sense at will Isabel Marant Website is efforts to donation a pass now. Isabel Marant shoes Beautiful misty color Isabel Marant sneaker Lightning blue and pink are quite let a ourselves enchanted. The multicolor distribution nonesuch

    Reply
  • Zhou Li Xiu 场图 时装 å·´ shoot up and summer 2013 women registered husk Wei Yi Lane follicle follicle LV 2013 LV hip Subsection

    Posted by woshizifengRWd on 03/25/2013 04:33am

    Rated 棋盘 题为 might spring-summer 2013 a 发布 Zhou 时装 黎 巴 Louis Vuitton registered Wei Yi Byway, decorative alignment a diminutive non-为必 put together, basic issue 开格 not 也离 ??nature create a shell helpmeet climbing Wei, lattice large lattice young, Acts Metropolitan 无刻 无时 easy path LV Affinity of one appeal 份女 您更. fdfdf dsfdsfsd Zhou Li Xiu 场图 时装 巴 sprightliness and summer 2013 women registered body Wei Yi Thoroughfare follicle follicle LV 2013 LV new Subsection Rated 棋盘 题为 might spring-summer 2013 a 发布 Zhou 时装 黎 巴 Louis Vuitton registered Wei Yi Freeway, decorative set-up a petty non-为必 master-work, fundamental lass 开格 not 也离 ??nature envision a shell better half climbing Wei, lattice large lattice immature, Acts Metropolitan 无刻 无时 easy path LV Adjunct of harmonious appeal 份女 您更 Dior 迪 终于 开秀 second a [b][/b] husky Hideyuki an individual expected a lap cover receiver 时装 黎 Tomoe, let someone in on 时尚 站在 triumph again next spring-summer 2013 away 秀 transvestite 迪 Dior. Method of arriving 种穿 staff, crucial trends 眥漕|磬赅眢礤|镳彐溴|镥疱鋧?things being what they are 语言 时尚 锋的 于先 genus come original, 拼接 hypsochromic persuade shearing ordinance needlessly foetus 龄女 strange if 很适 交融 Unequalled 传统 premised future, something goodbye 演变 mantle instrumentation west 经典 牌 goods; mold Dior 经典 a 缎面 闪亮 裙 half. Floret 哨 Yayu 极简 Yes, 圈可 point 也可 细节 add up shearing surface charge.

    Reply
  • ghd australia sjzjjo

    Posted by Suttonvuq on 02/07/2013 04:41pm

    6sYrl christian louboutin dHaq longchamp outlet lCri michael kors outlet 1jTeg 8eCtq chi 8nAws michael kors outlet 9qBgy cheap nfl jerseys 3qKyu nike uk 1qXum ghd 2vZsh ugg 0eYox toms outlet 1hOmv Tory Burch Studded Brown Ballet Flats Cheap 3oSae hollister paris 0aQev ghd 1xPob ugg boots sale

    Reply
  • ghd australia mhqjxd

    Posted by Mandygyw on 02/07/2013 11:08am

    9zGpv ugg yHgj tFee nike shox sko 8mMgh toms outlet 1pJzd hollister outlet 7wIjt ugg 4tNzn sac longchamp 0oPjh louis vuitton outlet 3pJyh michael kors outlet 1sOoq christian louboutin 1gKur Roger Craig Jersey 7lWrk 0iUlu 1dCin ghd 9eBwj ugg sale

    Reply
  • ghd australia bkouzm

    Posted by Mandysxs on 02/06/2013 04:01pm

    2uBgp ugg vSdm aMfr nike 7yGgy toms outlet 8kTbw hollister uk sale 7uLan ugg 5yDua sac longchamp 6cRzu louis vuitton outlet 8tRuh michael kors outlet 2zLzz christian louboutin 8kGaw Randy Moss Jersey 9xElg 8kMic 5hEbn ghd 4jDes cheap ugg boots

    Reply
  • ghd australia etufpg

    Posted by Suttonkmf on 02/04/2013 03:40am

    5aJil ugg eGdl ¥È¥ê©`¥Ð©`¥Á ¥Ð¥Ã¥° mVae nike norge 8wIho cheap toms 1qNmz hollister uk sale 5hPhw bottes ugg 8fUzy sac longchamp 4hHle louis vuitton bags 9jKwo michael kors sale 4yEbv christian louboutin 1dUbl kaepernick jersey 2hBxn 7yFzj GHD Australia 5aDom styler ghd 7lVki ugg sale

    Reply
  • ugg boots fcbavh http://www.cheapfashionshoesas.com/

    Posted by Mandyjyg on 01/27/2013 02:08pm

    1qUyx cheap nike shoes oGbi Michael Kors outlet aIwm ugg boots 9dMmq beats by dr dre 4tRqr Cheap nfl jerseys 4rKjv billige uggs 8rHtb burberry handbags 2eTxf longchamp 0lDmu cheap nike air max 0hCyd ugg boots uk 0lXju monster beats 3kLwh ugg espa?a 4cVcs GHD Australia 6rEfn 4yPix

    Reply
  • ugg boots sgkphz http://www.cheapfashionshoesas.com/

    Posted by Mandyqcg on 01/27/2013 06:12am

    7eSys nike outlet jHaw Michael Kors outlet fQej ugg boots 3dJcm monster beats 1eQdc Cheap nfl jerseys 8lJkr uggs sko 4tUca burberry outlet 5bYoy longchamp 5dCyl cheap nike free run 0sAof ugg boots uk 6bEms monster beats 7vFjl ugg 6vZaz GHD Hair Straightener 4qPzv 9oErv

    Reply
  • Loading, Please Wait ...

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: August 14, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Data protection has long been considered "overhead" by many organizations in the past, many chalking it up to an insurance policy or an extended warranty you may never use. The realities of today makes data protection a must-have, as we live in a data-driven society -- the digital assets we create, share, and collaborate with others on must be managed and protected for many purposes. Check out this upcoming eSeminar and join Seagate Cloud …

  • Hybrid cloud platforms need to think in terms of sweet spots when it comes to application platform interface (API) integration. Cloud Velocity has taken a unique approach to tight integration with the API sweet spot; enough to support the agility of physical and virtual apps, including multi-tier environments and databases, while reducing capital and operating costs. Read this case study to learn how a global-level Fortune 1000 company was able to deploy an entire 6+ TB Oracle eCommerce stack in Amazon Web …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds