© 2001 International Business Machines Corporation. All rights reserved.

Introduction

Using a sample application, this article demonstrates how client applications can be built using the IBM Informix .NET provider to access data from an IBM Informix database. Throughout this article, I will use code snippets to demonstrate the following capabilities of the IBM Informix .NET provider:

• Auto generate commands using IfxCommandBuilder
• Building DataSets and relations using IfxDataAdapter
• Inserting, updating and deleting data using IfxDataAdapter
• Using transactions with IfxCommandBuilder

Back to top

Overview of sample application

The sample application demonstrates order entry and retrieval using an orders database called dotnet_demodb (See attached sample dotnetdemo.zip for the complete code). The application consists of Windows Forms that have controls bound to data from an IBM Informix database.

The DemoUtils class (DemoUtils.cs in the DemoUtils project) is used to load the data from the database. The data is read from an XML file and inserted into the database using instances of the IfxCommand class. The following code snippet from the LoadData operation of the DemoUtils class demonstrates this :

 
IfxCommand InsertCommand = new IfxCommand(FinalInsertClause,IfxCon); 
InsertCommand.CommandType = CommandType.Text; 
int RowsIn = InsertCommand.ExecuteNonQuery(); 

Back to top

Data retrieval, inserts, updates and deletes

Retrieving data into a DataSet

When the application loads, it creates an instance of the IfxDemo class. This class encapsulates all data operations to the database. Initializing an instance of IfxDemo first calls DemoUtils.LoadData to load the data into the database and then calls an internal operation, FillDataSet. FillDataSet uses instances of IfxDataAdapter and IfxCommandBuilder to fill the DataSet member _DemoDataSet of the IfxDemo class. The following code snippet demonstrates how data from the tables orders and customers is filled into the DataSet:

 
_DemoDataSet = new DataSet(); 
 
_IfxDaOrders = new IfxDataAdapter("select 
* from orders",Conn); 
 
_OrderCmdBuilder = new IfxCommandBuilder(_IfxDaOrders); 
 
_OrderCmdBuilder.GetInsertCommand(); 
_OrderCmdBuilder.GetUpdateCommand(); 
_OrderCmdBuilder.GetDeleteCommand(); 
 
_IfxDaOrders.Fill(_DemoDataSet, "orders"); 
_IfxDaCustomers = new IfxDataAdapter("select * from customer",Conn); 
_CustCmdBuilder = new IfxCommandBuilder(_IfxDaCustomers); 
//generate ahead the update command 
_CustCmdBuilder.GetUpdateCommand(); 
_IfxDaCustomers.Fill(_DemoDataSet, "customer"); 

We use the IfxCommandBuilder instance to generate our insert, delete and update statements since there is only a single table involved and is thus more convenient than building the commands by hand.

We build the IfxCommandBuilder instance by supplying it with an IfxDataAdapter instance that has the required select statement. IfxCommandBuilder will generate the insert, update and delete statements based on the query or select statement.

Once we have retrieved the data, we need to set the relation between the customer and orders table as defined by the schema. The following code snippet from the FillDataSet operation of IfxDemo illustrates this:

 
_DemoDataSet.Tables["customer"].PrimaryKey = ColArr; 
_DemoDataSet.Tables["orders"].PrimaryKey = ColArrOrder; 
_DemoDataSet.Relations.Add("CustomerOrders", 
_DemoDataSet.Tables["customer"].Columns["cust_num"], 
_DemoDataSet.Tables["orders"].Columns["cust_num"]); 
ForeignKeyConstraint FKCust = new 
ForeignKeyConstraint("CustomerOrder",Cust_Num,Order_Cust_Num); 
FKCust.DeleteRule = Rule.Cascade; 
_DemoDataSet.EnforceConstraints = true; 

Inserting, updating and deleting data

Once our DataSet object is ready, we bind it to our form controls as the following code from CustomerRecord class illustrates:

 
_IfxDemo = IfxDemo.GetInstance(); 
 
//get the customers datatable from the DataSet 
DataTable CustTable = _IfxDemo.DemoCustomers; 
this.FNameCombo.DataSource = CustTable; 
this.FNameCombo.DisplayMember = "fname"; 
this.FNameCombo.ValueMember = "cust_num"; 

Once the main form is displayed, we can add orders or update customer information. Let's create a new order using the Add Order interface, which launches the following form:

Select an item to enable the quantity field. Once the quality field is enabled, you can enter a quantity of up to the maximum number displayed in the field. Click on Add Order. Once you add multiple orders, you can submit all of the new orders to the database by clicking on the Done button. The following code in the CreateOrders operation of IfxDemo illustrates what happens when you are ready to submit changes to the database:

 
int Increment = order_num + 1; 
foreach(Order NewOrder in Orders) 
{ 
DataRow NewRow = OrdersTable.NewRow(); 
NewRow["order_num"] = Increment; //equivalent 
to rows+1 
NewRow["cust_num"] = NewOrder.CustomerID ; 
NewRow["item_num"] = NewOrder.ItemID; 
NewRow["order_date"] = NewOrder.Date.Date; 
NewRow["quantity"] = NewOrder.Quantity; 
_DemoDataSet.Tables["orders"].Rows.Add(NewRow); 
Increment++; 
} 

For each new order, we create a new DataRow in the "orders" DataTable within the DataSet.

We then start a transaction by calling the BeginTransaction operation on the IfxConnection instance:

 
IfxTransaction Trans = Conn.BeginTransaction(); 

Now in order to enlist a command in the above transaction, we need to associate the transaction with the Transaction property of the command object. This becomes tricky when we are using a command builder instance. This is because IfxCommandBuilder does not generate insert, update or delete commands until the Update operation on the IfxDataAdapter instance is called. To workaround this and use transactions with an IfxCommandBuilder instance, we need to force the IfxCommandBuilder instance to generate the command objects before the Update call and then set the transaction property of the command objects. This is shown in the code snippet below:

 
_OrderCmdBuilder.GetInsertCommand().Connection = Conn; 
_OrderCmdBuilder.GetUpdateCommand().Connection = Conn; 
_OrderCmdBuilder.GetInsertCommand().Transaction = Trans; 
_OrderCmdBuilder.GetUpdateCommand().Transaction = Trans; 

Once the commands have been set and enlisted in a transaction, we can submit our changes to the database by calling Update on the IfxDataAdapter instance as follows:

 
Inserts = _IfxDaOrders.Update(_DemoDataSet,"orders"); 
Trans.Commit(); 

Since we have only the orders table to update in the above code, we specify which DataTable to pick up the changes from (in the above case, the orders table).

Similarly the DeleteOrders operation in IfxDemo demonstrates how a row is deleted from the database:

 
DataTable DelTable = 
 _DemoDataSet.Tables["orders"].GetChanges(DataRowState.Deleted); 
if(DelTable != null) 
{ 
 _OrderCmdBuilder.GetDeleteCommand().Connection = _IfxCon; 
try 
{ 
int Deletes = _IfxDaOrders.Update(_DemoDataSet, 
"orders"); 
int x = Deletes; 
} 
catch(Exception excp) 
{ 
 Console.WriteLine(excp.Message); 
} 
} 

To see how updates are transmitted from an application to the database, let's look at the Update Customer interface, which can be launched from the main form:

Change one of the address fields in the form and click Update. The form combo controls are associated with the customers DataTable of the DataSet. When the Update button is clicked, the control's event handler is called. In this case, the event handler is the UpdtBtn_Click operation in CustomerRecord. The updates are made directly to the data source as illustrated in the code below:

 
DataRowView MyRow = (DataRowView);this.FNameCombo.SelectedItem; 
int SelIndex = FNameCombo.SelectedIndex; 
DataTable CustTable = (DataTable)FNameCombo.DataSource; 
DataRow ThisRow = CustTable.Rows[SelIndex]; 
 
ThisRow["address1"] = NewAddr; 
ThisRow["city"] = NewCity; 
ThisRow["state"] = NewState; 
ThisRow["Zip"] = NewZip; 
IfxDemo Demo = IfxDemo.GetInstance(); 
Demo.UpdateCustomers(); 

Since the changes have already been made to the DataTable customers, the UpdateCustomers operation of IfxDemo can directly call the Update operation on IfxDataAdapter as follows:

<blockquote> 
_IfxDaCustomers.Update(_DemoDataSet,"customer"); 
</blockquote>

Back to top

Summary

We looked at how the Informix .NET provider can be used to retrieve, insert, update and delete data from an IBM Informix database. Specifically, we saw how IfxDataAdapter can be used to fill a DataSet and how schema relations can be maintained within the DataSet. Further, we saw how to use the IfxCommandBuilder to auto generate commands and how transactions can be used with IfxCommandBuilder. We looked at how updates are transmitted from a user interface field to the IBM Informix database.

Back to top

Download

Name	Size	Download method
ifmxdotnetdemo.zip	50 KB	FTP | HTTP

Information about download methods

About the author

Rajesh Nair is an Advisory Software Engineer at the IBM Informix Lab in Lenexa, California. He works with the IBM Informix CSDK Team and is involved in the development of Informix tools and APIs.You can reach Rajesh at rajeshn@us.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Overview of sample application
• Data retrieval, inserts, updates and deletes
• Summary
• Download
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers