Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2, IDS, and U2 servers

Rapid application development for DB2, Informix Dynamic Server, and U2 using IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio

The IBM Data Server Provider for .NET enables application developers to take advantage of the Microsoft® Entity Framework and LINQ. You can easily develop data access .NET applications using one of the IBM data servers (DB2®, Informix® Dynamic Server, or U2), the Microsoft ADO.NET Entity Framework, and LINQ. This article shows you how to get started using the Entity Framework with one of the IBM data servers, walking you step-by-step through the process of creating your own application.

Share:

Ranjan Kumar, Software Developer, IBM

Author Photo: Rajan KumarRajan is a developer with the IBM U2 Data Management Software Group in Denver, Colo. He works on .NET, Windows, and Java data access tools for UniVerse and UniData. He is currently working on the IBM data server provider for .NET and the IBM Database Add-ins for Visual Studio projects.



Brent Gross (gross@ca.ibm.com), Software Developer, IBM

Photo of Brian GrossBrent Gross is a Senior Software Engineer with IBM Information Management Development out of the Toronto Lab. He has over 20 years experience with IBM and has been developing for DB2 since 1995. His responsibilities have included architecture and development of stored procedure support and client interfaces. Brent has presented at many DB2 and user group conferences, His current role is the architect of the .NET and OLE DB APIs for DB2 and DB2 Connect.



12 March 2009

Introduction

U2 is no longer an IBM brand

In October 2009, the U2 products became products of Rocket Software.

The IBM Data Server Provider for .NET lets you take advantage of the Microsoft Entity Framework using IBM data servers (DB2, IDS, and U2). You can generate Entity Data Model (EDM) schemas, and you can write and execute EntitySQL and LINQ statements to Entities applications with the supported IBM data server versions. It enables developers to query and manipulate data using a conceptual model instead of a physical storage model.

This article shows you how to get started with LINQ and the Entity Framework using IBM data server databases. You'll walk step-by-step through the process of developing a simple Windows® application, generating an EDM model from an IBM data server database schema, creating LINQ queries that access data, binding the query results to controls, making updates to objects, and persisting the changes to the database.

IBM Data Server Provider for .NET gives you many more capabilities, including:

  • Mapping details CSDL/SSDL/MSL
  • Entity SQL and Query Builder
  • Lazy(Deferred) and Eager Loading
  • Object services and change tracking
  • EDM function import with Store Procedures and U2 subroutines
  • Canonical functions
  • Optimistic concurrency
  • ADO.NET Data Services (RESTful)
  • SilverLight and AJAX development
  • ASP.NET Dynamic Data
  • Visual Studio Tools for Office (VSTO) development using EDM

Many of these capabilities will be discussed in future articles.

Highlighting IBM Entity Framework-enabled ADO.NET Provider

The IBM Data Server Provider for .NET supports the following IBM Data Servers:

  • DB2 for Linux®, UNIX®, and Windows, Version 9.1 (or later)
  • DB2 for IBM i5/OS® V5R3
  • DB2 for IBM i 5.4
  • DB2 for z/OS® V7, V8, and V9
  • IBM Informix Dynamic Server, Version 11.10 (or later)
  • UniVerse 10.2 (or later) and UniData® 7.1 (or later)

The IBM Data Server Provider for .NET enables EDM generation using the following Visual Studio 2008’s ADO.NET Entity Data Model (see Resources for limitations):

  • Tables
  • Views
  • Stored procedures and U2 subroutines
  • LINQ to Entities
  • Entity SQL and Entity Provider
  • Query Builder
  • Object Services

Understanding system requirements

You need the following software to complete the example:

  • IBM Data Server Driver for ODBC, CLI, and .NET (version 9.5.3 or later)
  • IBM Database Add-ins for Visual Studio (version 9.5.3 or later)
  • Visual Studio 2008 Service Pack 1
  • .NET Framework 3.5 Service Pack 1

You can access links to download the products from Resources.


Installing

IBM Data Server Provider for .NET is a component of IBM Data Server Driver for ODBC, CLI, and .NET. To verify the version:

  1. Go to the \netf20 directory (such as c:\Program Files\IBM\IBM DATA SERVER DRIVER\bin\netf20).
  2. Right-click IBM.Data.DB2.dll
  3. Click Properties.

Figure 1 shows the screen that appears.

Figure 1. Data provider
Data Provider

To verify VS2008 SP1, .NET Framework 3.5 SP1, and IBM Database Add-ins for Visual Studio, do the following:

  1. Open Visual Studio 2008.
  2. Go to the Help Menu.
  3. Click About Microsoft Visual Studio.

Figure 2 shows the screen that appears.

Figure 2. IBM Database Add-ins
IBM Database Add-ins

Testing your connection

Before running the LINQ and Entity tutorial below, check your connection. IBM Client Installation contains a utility tool called testconn20.exe. This is shipped with installation, and it is usually located in c:\Program Files\IBM\SQLLIB\BIN. To confirm Connection Testing, go to a command prompt and run the utility using the appropriate command from Table 1.

Table 1. Testing connection
IBM data serverCommandRemarks
DB2 LUWtestconn20.exe database=sample;server=localhost:50000;userid=user;password=**** 
DB2 iSeries®testconn20.exe database=sample;server=myhost.ibm.com:446;userid=user;password=**** 
DB2 zSeries®testconn20.exe database=sample;server=myhost.ibm.com:446;userid=user;password=**** 
IDStestconn20.exe database=stores_demo;server=localhost:1538;userid=user;password=**** 
U2 (UniVerse)testconn20.exe database=HS.SALES;server=localhost;userid=user;password=***;ServerType=universe;pooling=falsespecify ServerType and Pooling
U2 (UniData)testconn20.exe database=demo;server=localhost;userid=user;password=***;ServerType=unidata;pooling=falsespecify ServerType and Pooling

Learning the IBM Entity Framework-enabled ADO.NET Provider architecture

Figure 3 shows the architecture of the Entity Framework.

Figure 3. Architecture of the Entity Framework
Architecture of the Entity Framework

Selecting a database for the example

You can use the following databases for the example.

Table 2. Databases
IBM data serverDatabase nameRemarks
DB2 LUWsample 
DB2 iSeriessampleUse any database
DB2 zSeriessampleUse any database
IDSstores_demo 
U2 ( UniVerse)ADONETHS.SALES is renamed to ADONET
U2 (UniData)demo 

Completing the example

This section walks you through how to use LINQ and Entity Framework with IBM data servers. At a high level, you will:

Create a Windows forms application

To create the Windows application using Visual Studio, do the following:

  1. On the File menu, click New Project.
  2. Choose either Visual Basic or Visual C# in the Project Types pane.
  3. Select Windows Forms Application in the Templates pane.
  4. Enter Test_EF for the project name.
  5. Click OK.

Figure 4 shows the screen that appears.

Figure 4. New Windows project
New Windows project

To create the form:

  1. In the Test project, select the default form (Form1).
  2. In the Toolbox, expand Common Controls, drag the ComboBox control to the form, and change the name of the control to comboBox.
  3. In the Toolbox, drag the Button control to the form, change the name of the control to closeForm, and change the Text value to Close.
  4. Double-click the closeForm button control. This opens the code page for the form, and it creates the closeForm_Click event handler method.
  5. In the closeForm_Click event handler method, enter the following code to close the form:
private void closeForm_Click(object sender, EventArgs e)
   {
       // Close the form.
           
       Close();
   }

Figure 5 shows the resulting form design.

Figure 5. Form design
Form design

Generate an Entity Data Model (EDM)

Use the Entity Data Model wizard to generate an Entity Data Model (EDM) from IBM data servers.

This article uses the IBM U2 UniVerse ADONET (HS.SALES) sample database for the example. For other servers, the specific tables and columns are different. However, the steps are the same. (Note that the DB2 iSeries servers have an APAR that affects the operation of the EDM Designer. See Resources for more information.)

To add the ADO.NET Entity Data Model item template, do the following:

  1. Select the project in Solution Explorer, point to Add, right-click, and click New Item.
  2. Select ADO.NET Entity Data Model in the Templates pane
  3. Type Customer.edmx for the model name, and click Add.
Figure 6. Add new item
Add new item

The opening page of the Entity Data Model wizard appears.

To generate the EDM:

  1. Select Generate from database in the Choose Model Contents dialog box.
  2. Click Next.
    Figure 7. Generate from database
    Generate from database
  3. Click the New Connection button.
    Figure 8. New connection
    New connection
  4. Select the IBM DB2, IDS and U2 Servers (IBM DB2, IDS and U2 Data Provider for .NET Framework) data source in the Choose Data Source dialog box.
  5. Click Continue.
  6. In the Connection Properties dialog box, enter your user name, password, server name, and database name. For example, for DB2 LUW, the database is sample, and for UniVerse, the database is ADONET.
  7. Click OK.
    Figure 9. Add connection
    Add connection
  8. For U2 (UniVerse and UniData), select the Advanced button, and indicate the ServerType and Pooling. The Choose Your Data Connections dialog box is updated with your database connection settings.
  9. Make sure that Save entity connection settings in App.Config as is checked, and that the value is set to ADONETEntities, as shown in Figure 10.
    Figure 10. Save Connection String
    Save Connection String
  10. Click Next. Figure 11 shows the Choose Your Database Objects dialog box that appears.
    Figure 11. Choose Your Database Objects
    Choose Your Database Objects
  11. Make sure that all tables and stored procedures are selected and that the value of Model namespace is ADONETModel.
  12. Click Finish to complete the wizard.

The wizard does the following:

  • Adds references to the System.Data.Entity, System.Runtime.Serialization, and System.Security assemblies
  • Generates the Customer.edmx file that defines the EDM
  • Creates a source code file that contains the classes that were generated based on the EDM. You can view the source code file by expanding the .edmx file in the Solution Explorer.
  • Creates an App.Config file

To view the EDM in the ADO.NET Entity Data Model Designer, go to the Solution Explorer and double-click the Customer.edmx file. This displays the model in the ADO.NET Entity Data Model Designer window, as shown in Figure 12.

Figure 12. Generated EDM
Generated EDM

Note that for U2 data servers, there is a limitation that prevents the EDM Designer from discovering associations automatically. See Appendix for a workaround.

Query entities, association, and data binding with controls

To query the database:

  1. At the beginning of the code file for the form, add the following code using (C#) statements to refer to the model created from the database and the entity namespace:
    C#
          
    using System.Data.Objects;
    using System.Data.Objects.DataClasses;
  2. At the top of the partial class definition for the form, add the following code:
    C#
    
    ADONETEntities m_context ;
  3. In the form designer, double-click the form. This opens the code page for the form, and it creates Form1_Load event handler method.
  4. In the Form1_Load event handler method, copy and paste the following code. Or, you can type the code and use IntelliSense for code completion.
    C#
    
       private void Form1_Load(object sender, EventArgs e)
       {
           m_context = new ADONETEntities();
           var query = from c in m_context.CUSTOMER
                       select c;
            try
           {
               this.comboBox.DisplayMember = "FNAME";
               this.comboBox.DataSource = query;
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
    
       }

To display results for the selected item, do the following:

  1. In the form designer, double-click the comboBox control. This creates the comboBox _SelectedIndexChanged event handler method.
  2. Enter the code from the following section. Or, you can type the code and use IntelliSense for code completion.
    C#
    
       private void comboBox_SelectedIndexChanged(object sender, EventArgs e)
       {
           try
           {
               // lazy loading
               CUSTOMER lCust = (CUSTOMER)this.comboBox.SelectedItem;
               var query = from o in m_context.CUSTOMER_ORDERS
                           where o.CUSTID == lCust.CUSTID
                           select o;
               dataGridView.DataSource = query;
               dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
    
       }

Insert and update data

To save changes made to objects, do the following:

  1. In the Toolbox, expand Common Controls, drag the Button control to the form designer, change the name of the control to saveChanges, and change the Text value to Update.
  2. In the form designer, double-click the saveChanges control. This creates the saveChanges_Click event handler method.
  3. Enter the following code to save object changes to the database.
    C#
    
       private void saveChanges_Click(object sender, EventArgs e)
       {
           try
           {
               // Save object changes to the database, display a message,
               // and refresh the form.
               m_context.SaveChanges();
               MessageBox.Show("Changes saved to the database.");
               this.Refresh();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
    
       }

To close connections by disposing the long-running object context in the closeForm_Click event handler method, type the following code.

C#

   private void closeForm_Click(object sender, EventArgs e)
   {
       // Dispose the object context.
       m_context.Dispose();
       // Close the form.
       Close();
   }

This code disposes of the object context before the form is closed.

To build and run the application, do the following:

  1. From the Debug menu, select Start Debugging or Start Without Debugging. This builds and starts the application.
  2. When the form loads, select a master data from the ComboBox control. This displays the details data that belong to that master data.
  3. In the DataGridView, update any information, or add a new item, and click Update. This saves changes to the database and displays a message box that declares the number of saved changes, as shown in Figure 13.
    Figure 13. Run application
    Run application

Summary

This article provides a good overview of how to use LINQ and Entity Framework with IBM data servers (DB2, IDS and U2). It demonstrated the concept of Entity Data Model and showed the various querying techniques that the Entity Framework provides. It also showed how to add, modify, and delete data using LINQ and Entity Framework.


Appendix

For U2 Data Servers, a limitation prevents the EDM Designer from discovering associations automatically. You can perform this workaround to manually add the associations.

  1. Right-click EDM Designer.
  2. Click Add > Association.
    Figure 14. Establish association
    Establish association
  3. Select CUSTOMER and CUSTOMER_ORDERS.
    Figure 15. Add association
    Add association
    Figures 16 and 17 compare the screens before and after you add the new association.
    Figure 16. Model Before association
    Model before association
    Figure 17. Model after association
    Model after association
  4. On the U2 Data Server, open the Customer.edmx file in an XML editor, and modify @ASSOC_ROW. For example, you can change "@ASSOC_ROW" to ""@ASSOC_ROW""

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=375671
ArticleTitle=Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2, IDS, and U2 servers
publish-date=03122009