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

brawnywinderSoftware and s/w Development

Dec 13, 2013 (3 years and 8 months ago)

425 views

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
Skill Level:Introductory
Rajan Kumar(rajank@us.ibm.com)
Software Developer
IBM
Brent Gross(gross@ca.ibm.com)
Software Developer
IBM
12 Mar 2009
The IBMData 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 IBMdata 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.
Introduction
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
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 1 of 25
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 IBMData Server Provider for.NET supports the following IBMData Servers:
 DB2 for Linux®,UNIX®,and Windows,Version 9.1 (or later)
 DB2 for IBMi5/OS® V5R3
 DB2 for IBMi 5.4
 DB2 for z/OS® V7,V8,and V9
 IBMInformix Dynamic Server,Version 11.10 (or later)
 UniVerse 10.2 (or later) and UniData® 7.1 (or later)
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 2 of 25 © Copyright IBM Corporation 2008.All rights reserved.
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 fromResources.
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 asc:\Program Files\IBM\IBM
DATA SERVER DRIVER\bin\netf20).
2.Right-clickIBM.Data.DB2.dll
3.ClickProperties.
Figure 1 shows the screen that appears.
Figure 1.Data provider
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 3 of 25
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.
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 4 of 25 © Copyright IBM Corporation 2008.All rights reserved.
Figure 2 shows the screen that appears.
Figure 2.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 inc:\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 server
Command
Remarks
DB2 LUW
testconn20.exe
database=sample;server=localhost:50000;userid=user;password=****
DB2 iSeries®
testconn20.exe
database=sample;server=myhost.ibm.com:446;userid=user;password=****
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 5 of 25
DB2 zSeries®
testconn20.exe
database=sample;server=myhost.ibm.com:446;userid=user;password=****
IDS
testconn20.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=false
specify ServerType
and Pooling
U2 (UniData)
testconn20.exe
database=demo;server=localhost;userid=user;password=***;ServerType=unidata;pooling=false
specify 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
Selecting a database for the example
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 6 of 25 © Copyright IBM Corporation 2008.All rights reserved.
You can use the following databases for the example.
Table 2.Databases
IBM data server
Database name
Remarks
DB2 LUW
sample
DB2 iSeries
sample
Use any database
DB2 zSeries
sample
Use any database
IDS
stores_demo
U2 ( UniVerse)
ADONET
HS.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
 Generate an Entity Data Model (EDM)
 Query entities,association,and data binding with controls
 Insert and update data
Create a Windows forms application
To create the Windows application using Visual Studio,do the following:
1.On the File menu,clickNew Project.
2.Choose eitherVisual Basic orVisual C#in the Project Types pane.
3.SelectWindows Forms Application in the Templates pane.
4.EnterTest_EF for the project name.
5.ClickOK.
Figure 4 shows the screen that appears.
Figure 4.New Windows project
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 7 of 25
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.
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 8 of 25 © Copyright IBM Corporation 2008.All rights reserved.
Figure 5.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.
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 9 of 25
Figure 6.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
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 10 of 25 © Copyright IBM Corporation 2008.All rights reserved.
3.Click the New Connection button.
Figure 8.New connection
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 11 of 25
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
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 12 of 25 © Copyright IBM Corporation 2008.All rights reserved.
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
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 13 of 25
10.Click Next.Figure 11 shows the Choose Your Database Objects dialog
box that appears.
Figure 11.Choose Your Database Objects
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 14 of 25 © Copyright IBM Corporation 2008.All rights reserved.
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
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 15 of 25
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
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
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 16 of 25 © Copyright IBM Corporation 2008.All rights reserved.
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)
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 17 of 25
{
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();
}
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 18 of 25 © Copyright IBM Corporation 2008.All rights reserved.
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
Summary
This article provides a good overview of how to use LINQ and Entity Framework with
IBM data servers (DB2,IDSand 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.
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 19 of 25
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-clickEDM Designer.
2.Click Add > Association.
Figure 14.Establish association
3.SelectCUSTOMER andCUSTOMER_ORDERS.
Figure 15.Add association
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 20 of 25 © Copyright IBM Corporation 2008.All rights reserved.
Figures 16 and 17 compare the screens before and after you add the new
association.
Figure 16.Model Before association
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 21 of 25
Figure 17.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""
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 22 of 25 © Copyright IBM Corporation 2008.All rights reserved.
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 23 of 25
Resources
Learn
 Visit theIBM Information Management and Visual Studio resource page on
developerWorksto read articles and tutorials and connect to other resources to
expand your.NET skills.
 Refer to theIBM Data Server limitations article in the wiki.Look for theDB2 for
IBM i section for information about the DB2 iSeries server APARthat affects the
operation of the EDM Designer.
 Start with overview information from"Access IBM U2 data server from your
.NET applications,Part 1:Get an overview of the IBM Data Server for.NET and
IBM Database Add-ins"(developerWorks,November 2007).
 Find more from"Create IBM Data Web Services using IBM Database Add-Ins
for Visual Studio"(developerWorks,February 2009).
 Work through the tutorial"ASP.NET development with IBM U2 Data Server"
(developerWorks,February 2008) to build the next generation application using
UniVerse,UniData,IBM Data Server Provider for.NET,IBM Database Add-ins,
and ASP.NET 2.0.
 Learn more about theU2 product family.
 Select the appropriate IBM Data Server client or driver based on thisoverview.
 Learn more about Information Management at thedeveloperWorks Information
Management zone.Find technical documentation,how-to articles,education,
downloads,product information,and more.
 Stay current withdeveloperWorks technical events and webcasts.
Get products and technologies
 Download theIBM Data Server Driver for ODBC,CLI,and.NET.
 DownloadIBM Database Add-ins for Visual Studio.
 Build your next development project withIBM trial software,available for
download directly from developerWorks.
Discuss
 Participate in thediscussion forumfor.NET development with DB2,IDS,and
U2.
 Read theADO.NET Team blog.
 Check out thedeveloperWorks blogsand get involved in thedeveloperWorks
community.
developerWorks® ibm.com/developerWorks
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
Page 24 of 25 © Copyright IBM Corporation 2008.All rights reserved.
About the authors
Rajan Kumar
Rajan 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
Brent 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.
Trademarks
IBM,DB2,i5/OS,iSeries,Informix,UniData,z/OS,and zSeries are trademarks of
IBM Corporation in the United States,other countries,or both.
Microsoft,Windows,Windows NT,and the Windows logo are trademarks of Microsoft
Corporation in the United States,other countries,or both.
UNIX is a registered trademark of The Open Group in the United States and other
countries.
Linux is a registered trademark of Linus Torvalds in the United States,other
countries,or both.
Other company,product,or service names may be trademarks or service marks of
others.
ibm.com/developerWorks developerWorks®
Develop a sample application using LINQ programming and the ADO.NET Entity Framework with IBM DB2,IDS,and U2 servers
© Copyright IBM Corporation 2008.All rights reserved.Page 25 of 25