Level: Intermediate Rajan Kumar (rajank@us.ibm.com), Software Developer, IBM
Brent Gross (gross@ca.ibm.com), .NET Architect for IBM Data Servers, IBM
29 Nov 2007
IBM® Data Server Provider for .NET and IBM Database Add-ins for Visual Studio
support multiple IBM data servers, including DB2®, Informix® Dynamic Server,
and the IBM U2 data servers, UniVerse®, and UniData®. This article is the
first of a series of articles and tutorials that explain the functionality of the .NET
Provider and the Add-ins for the IBM U2 data servers. As a U2 developer, you'll learn
to build Windows®, ASP.NET Web, Web services, AJAX, reporting, and Windows
Presentation Foundation (WPF) and Extensible Application Markup Language (XAML)
applications with little or no code. You'll use standard interfaces such as ADO.NET against IBM U2 data servers.
Introduction
IBM provides the following products to support application development on the .NET
platform:
- IBM Data Server Provider for .NET
- IBM Database Add-ins for Visual Studio
IBM Data Server Provider for .NET and IBM Database Add-ins are integrated
tightly into the Visual Studio Integrated Development Environment (IDE), a highly
productive development environment for building data-centric solutions. It
supports developing various target application types such as Windows, Web,
reporting, and office applications. Server Explorer Integration allows you to
develop a .NET application using UniVerse and UniData as you use other modern relational databases.
IBM Data Server Provider for .NET for UniVerse and UniData highlights
The IBM Data Server Provider for .NET for UniVerse and UniData is a set of
classes. This set of classes is found in the IBM.Data.DB2.Dll assembly, and has the namespace IBM.Data.DB2.
The IBM Data Server Provider for .NET is a high performance, managed ADO.NET data
provider. Applications can use ADO.NET Provider to connect to UniVerse and UniData, and can retrieve, manipulate, and update the data. ADO.NET supports both connected and disconnected access. For improved scalability, data processing applications running in a multi-tier environment employ disconnected access.
IBM Database Add-ins for Visual Studio for UniVerse and UniData highlights
IBM Database Add-ins for Visual Studio supports Server Explorer Integration for
UniVerse and UniData. You can use the Add-ins to connect to the UniVerse and
UniData data servers, test the connection, and populate server-side objects such
as basic routines and tables. With the Database Add-ins for Visual Studio, you can
do the following:
-
Use the IBM Data Server Provider for .NET to achieve seamless integration of
your data sources.
-
Take advantage of the rapid application development environment.
-
Build Windows, Web, Web services, and reporting applications without writing a
single line of code (zero programming).
-
Utilize support for integrating UniVerse and UniData data into your
application.
-
Execute U2 basic routines in the Server Explorer.
-
Show data from U2 tables and views in the Server Explorer.
-
Generate ASP.NET Web services from U2 basic routines.
The architecture
Figure 1 shows the architecture of .NET application development for UniVerse and
UniData.
Figure 1. Architecture
— .NET application development for UniVerse and UniData
Installation
This is a part of DB2 9.5.
You can download the ZIP file in the Resources section at the end of this article.
The DB2 Client contains the installation files for both of these:
- IBM Data Server Provider for .NET
- IBM Database Add-ins for Visual Studio
Figure 2. Installation window
You can see the installed program by navigating to Control Panel > Add or
Remove Programs.
Figure 3. Installed programs — IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio
Confirmation — IBM Data Server Provider for .NET
installation
Use the testconn20.exe utility tool to confirm the IBM .NET Provider installation.
DB2 Client installation contains a utility tool called testconn20.exe. This is shipped with installation and generally found in C:\Program Files\IBM\SQLLIB\BIN.
Perform the following steps to confirm the IBM .NET Provider installation.
- Open a command prompt.
- Type the following command:
testconn20.exe "User ID=user;Password=****;Database=HS.SALES;
Server=localhost;ServerType=universe;pooling=false"
|
Confirmation — IBM Database Add-ins Installation
You should see the following window if you run Help/"About
Microsoft Visual Studio" from Visual Studio IDE.
Figure 4. IBM Add-ins confirmation
UniVerse and UniData
database preparation
For more information on UniVerse, refer to Chapter 4 Accessing UniVerse Data
in the "Using
UniOLEDB" manual.
For more information on UniData, refer to Chapter 3 Accessing UniData Data in
the "Using UniOLEDB" manual.
You have to first run the ECL command MIGRATE.SQL.
Use Visual Schema Generator (VSG), or the schema API, to create 1NF views and
sub-tables for the runtime normalization of multi-valued and sub-valued data.
Make UniVerse and UniData
connections
Connection using testconn20.exe
The DB2 Client installation contains a utility tool called testconn20.exe. This is
shipped with the installation and generally found in C:\Program Files\IBM\SQLLIB\BIN.
UniVerse
testconn20.exe "User ID=user;Password=****;Database=HS.SALES;
Server=localhost;ServerType=universe;pooling=false"
|
UniData
testconn20.exe "User ID=user;Password=****;Database=DEMO;
Server=localhost;ServerType=unidata;pooling=false"
|
Connection using programmatically
You can write simple program in C# to connect to UniVerse or UniData as shown below:
UniVerse
DbProviderFactory provider = DbProviderFactories.GetFactory("IBM.Data.DB2");
DbConnection con = provider.CreateConnection();
string sConnectionString = "User ID=user;Password=****;Database=HS.SALES;Server=localhost;
ServerType=universe;pooling=false";
con.ConnectionString =sConnectionString;
con.Open();
con.Close();
|
UniData
DbProviderFactory provider = DbProviderFactories.GetFactory("IBM.Data.DB2");
DbConnection con = provider.CreateConnection();
string sConnectionString = "User ID=user;Password=****;Database=DEMO;
Server=localhost;ServerType=unidata;pooling=false";
con.ConnectionString =sConnectionString;
con.Open();
con.Close();
|
Connection using Visual Studio Add-ins
You can add UniVerse or UniData connections into Server Explorer using the Add Connection
option. Follow these simple steps to setup UniVerse or UniData connection:
- Open Visual Studio. If Server Explorer is not already open, click View > Server
Explorer.
Figure 5. View > Server Explorer
- From the Data Connections node, right-click and select Add Connection.
Figure 6. Data Connections > Add Connection…
- In the Data Source field, select IBM DB2. Note that UniVerse and UniData are
a part of the IBM.DB2 data source. (You can check the box for Always use this
selection, to avoid this step every time an IBM DB2 connection is added.) Click
OK.
Figure 7. Change the data source
- Add the server name, database name, user ID, and password. (You can select save
password to persist the password across Visual Studio sessions. For UniVerse and UniData, Click “Advanced…” Button and specify ServerType as “universe" or “unidata". If you are not using Connection Pooling, specify pooling is equal to false. You can also opt to Test Connection.) Click OK. A connection to your database will be added in Server Explorer.
Figure 8. Add Connection dialog window and Advanced Properties window
Server Explorer Integration
After adding a new connection, all schema information is synchronously prefetched
and cached. This allows rapid access to this information when designing
applications. These fetched server objects are listed as folders under the
corresponding connection.
Table enumeration: Expanding the Tables folder lists the tables available in the database, depending upon whether the Filter option or Exclude system schema option is supplied in the Add Connection window. By expanding a particular table, you can see the list of columns with which the table was built.
Figure 9. Tables enumeration in Server Explorer
The Properties window lists the important properties of the database object selected
in the Server Explorer. For example, if you've selected the column FANME of table
CUSTOMER of the HS.SALES database, the corresponding Properties window would appear
as follows:
Figure 10. Tables properties window
Procedure (U2 basic routines) enumeration: You can view a list of stored
procedures (U2 basic routines) by clicking the Procedures folders respectively.
Expanding each stored procedure or function lists the parameters involved. To view
the properties of parameters, right-click on the parameter and select
Properties. You can execute a U2 basic routine and see the result.
Figure 11. Procedures (U2 basic routines) enumeration in Server Explorer
You can execute a UniVerse or UniData basic routine. Select a basic routine, right
click and then click Run to execute.
Figure 12. Run procedures (U2 basic routines) in Server Explorer
If the basic routine has parameters, you can specify parameters before execution.
Figure 13. Specify parameters for procedures (U2 basic routines) in Server Explorer
The result of a basic routine is displayed in Visual Studio IDE.
Figure 14. Output of procedures (U2 basic routines) in Server Explorer
Rapid application development
The core of rapid application development is to create data sources from tables,
views, and store procedures (U2 basic routines). To achieve zero programming
development, drag and drop these data sources into Windows or Web Forms.
Data sources can be created two ways:
-
Use the Data Source Configuration Wizard
-
Add DataSet into your project
Create data sources — Use the Data Source
Configuration Wizard
You display the Data Sources window by clicking Show Data Sources on the
Data menu of Visual Studio IDE. The Data menu is only available when a project is open in Visual Studio. To populate the Data Sources, add new data sources with the Data Source Configuration Wizard.
Figure 15. Data Source window
Click Add New Data Sources…, to see the following screen:
Figure 16. Data Sources Configuration Wizard (select database)
Select Database, and click Next. You see the following screen:
Figure 17. Data Sources Configuration Wizard (select connection)
Select Connection String, and click Next. You see the following
screen. You can use New Connection… to create a new node in Server Explorer.
Select Yes to save the connection string in the project file.
Figure 18. Data Sources Configuration Wizard (save connection string)
Use the Default, and click Next. You see the following screen:
Figure 19. Data Sources Configuration Wizard (select table or procedure object)
Select a table or a procedure, and click Next. You see the following screen:
Figure 20. Data Sources Configuration Wizard (CUSTOMER Table)
You see that a data source is added into the data source windows. You can edit the data
source by right clicking and selecting Edit with Data Set Designer. You can
see the properties of the generated table adapter.
Figure 21. Data sources, DataSet Designer, table adapter
Create data sources — Add DataSet into your
project
You can also populate the data sources window by adding DataSet items to your
project.
- On the Project menu, click Add New Item.
- Select DataSet from the Add New Item dialog box.
- Type a name for the data set.
- Click Add.You see the following screen.
Figure 22. Add new item (DataSet)
The data set is added to the project and opens in the DataSet Designer.
Drag items from the DataSet tab of the Toolbox onto the designer. You can also
drag items from an active connection in Server Explorer or Database Explorer onto
the DataSet Designer.
Figure 23. DataSet Designer (drag and drop from Toolbox or Server Explorer)
Connection pooling
UniVerse and UniData data servers support connection pooling. You can specify
connection pooling parameters programmatically (IBM .NET Provider) or Server Explorer’s Add Connection Dialog (IBM Add-ins).
You need to enable U2 server-side connection pooling if you specify pooling=true
in your application. Otherwise, you get an exception. For more information about
connection pooling licenses for UniData and UniVerse Data Servers, see Chapter 2
Using UniObjects for .NET in the "UniObjects for .NET Developer's Guide."
Trace and debug
You can create a log file for different modules.
For IBM U2 protocol, specify environment variables UCINETTRACE=c:\temp and
UCINETTRACESWITCH=4.
For IBM Database Add-ins, specify environment variables vstrace=c:\temp.
Documentation
The documentation help for IBM Database Add-ins for Visual Studio is integrated into IDE. You can see this in the steps below:
- Navigate to Help > Contents.
Figure 24. Select Contents
- Select IBM Database Documentation, then IBM Database Add-ins Help or IBM Data
Server Provider for .NET.
Figure 25. Select IBM Database Add-ins Help or IBM Data
Server Provider for .NET
Potential new features
The following are some areas under consideration for future releases of the
IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio:
- Support for Web services (Create a Web Method from tables). Note that there is
already support for Web Methods from U2 basic routines.
- Data Definition Language (DDL) support
- Secure Sockets Layer (SSL) support
- Language Integrated Query (LINQ) support
- Create U2 Common language runtime (CLR) procedures from existing methods in a .NET managed language (such as C# or Visual Basic).
Conclusion
Now IBM Data Server Provider for .NET and IBM Database Add-ins for Visual Studio
support UniVerse and UniData data servers. These products have brought many new
features to the UniVerse and UniData developer, including seamless integration
with Microsoft Server Explorer, rapid application development with the Data
Sources Configuration Wizard, typed dataset creation from Universe and UniData basic routines, the capability to export UniVerse and UniData data as XML, the ability to develop reporting applications using Microsoft Reporting Services and Crystal Reports, and connection pooling for Web application.
In future articles and tutorials in this series, you'll learn to develop the
following kinds of .NET applications using the IBM .NET Provider, IBM Database
Add-ins, and U2 data servers:
- Master detail window application. Hook a table adapter’s Update, Delete, and
Insert statement to UniVerse and UniData subroutine.
- Master details ASP.NET Web application, master page, theme and style, data
access layer, business logic layer, and Web sitemap
- ASP.NET AJAX application (server centric and client centric)
- Create and consume a Web service from UniVerse and UniData subroutines and tables
- Master detail Crystal Report application using Visual Studio Crystal Report Add-ins
- Master Detail Microsoft Reporting Services application using SQL Server Business Intelligence Studio
- Master Detail WPF or XAML application using Microsoft Expression Blend, LINQ to
DataSet, CLR Object Data Provider, and XML Data Provider
Acknowledgement
This release of IBM Data Server Provider for .NET, IBM U2 Runtime, and IBM
Database Add-ins for Visual Studio have been developed by IBM teams in Toronto,
Lenexa, Denver, San Francisco, and Silicon Valley Lab.
Resources Learn
Get products and technologies
Discuss
About the authors  | 
| | 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 is a .NET architect for IBM data servers with IBM Information Management
Development in the Toronto Lab. He has more than 20 years of 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 Information Management and user group conferences.
His current role is the architect of the .NET support for IBM data servers. |
Rate this page
| 
