Building Informix Data-Driven Applications with .Net

1. Informix User Forum 2006 Building Informix Data-Driven Applications with .Net Sean R. Durity Manager of IT CornerCap Investment Counsel Washington, D.C. December 8-9, 2006

2. Agenda • The IBM Informix ADO.Net Driver • ADO.Net Driver Basics (with examples) • Building A Real Application (demo) • Q & A

3. The Informix ADO.Net Driver • Current version is Client SDK 2.90TC6 • Now installed by default, must have .Net framework 1st (works with either 1.1 or 2.0 framework) • Namespace is IBM.Data.Informix - reference the library IBM.Data.Informix.dll • Client SDK must be installed on any client that will use the driver (not self-contained in the .dll) • Current documentation and examples are better than the previous ones; reference guide twice as long • Objects are similar to Microsoft’s Sql* objects, but use “Ifx” prefix (IfxConnection, IfxCommand, etc.)

4. The Informix ADO.Net Driver • Installation Issues • If you have the older driver (2.81), you should either install into the same directory or remove the previous installation first. I could not get the two versions to peacefully co-exist. • There is a stored procedure creation script that must be run once against the sysmaster database (as user ‘informix’). Otherwise, features like the DataAdapter wizard won’t connect. Script is $INFORMIXDIR/etc/cdotnet.sql on any client machine.

5. The Informix ADO.Net Driver • New features in 2.90 • Adds an IfxDataAdapter Configure Data Adapter wizard • Adds new types including IfxDateTime, IfxDecimal, IfxBlob, and IfxClob • Supports IPv6 protocol

6. The Informix ADO.Net Driver Basics • Connection String (getting a connection) • Semi-colon delimited list of attributes put into the ConnectionString property of the IfxConnection object string ConnectionString = "Host=" + HOST + "; " + "Service=" + SERVICENUM + "; " + "Server=" + SERVER + "; " + "Database=" + DATABASE + "; " + "User Id=" + USER + "; " + "Password=" + PASSWORD + "; "; IfxConnection conn = new IfxConnection(); conn.ConnectionString = ConnectionString; try { conn.Open(); } catch (IfxException ex) { }

7. The Informix ADO.Net Driver Basics • Executing an insert, update or delete IfxCommand cmd = new IfxCommand("insert into test values (1, 2, ‘ABC’)",bconn.conn); cmd.CommandTimeout = 200; //seconds allowed for command to finish, default is 30 try { int rows = cmd.ExecuteNonQuery(); } catch (IfxException ex) { Console.WriteLine("Error "+ex.Message); }

8. The Informix ADO.Net Driver Basics • Iterating through a SELECT’s result set one-time IfxCommand cmd = new IfxCommand("select * from test",bconn.conn); try { IfxDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { int a = dr.GetInt32(0); int b = Convert.ToInt32(dr["b"]); string c = (String)dr[2]; } dr.Close(); } catch (IfxException ex) { Console.WriteLine("Error "+ex.Message); }

9. The Informix ADO.Net Driver Basics • Executing a Stored Procedure with “in” parameter IfxCommand cmd = new IfxCommand("test_proc",bconn.conn); cmd.CommandType = CommandType.StoredProcedure; //from System.Data cmd.Parameters.Add("in_parameter",2); //many ways to create these try { cmd.ExecuteScalar(); } catch (IfxException ifxe) { Console.WriteLine("Error "+ifxe.Message); }

10. ADO.Net 2.0 • MicroSoft fixed some underlying problems with its DataSet implementation (indexing). For large DataSets it can provide an order of magnitude performance improvement. • Note: the current Informix driver has its own performance problems marshaling data from unmanaged to managed code. This also impacts the performance of large DataSets. • Driver Availability from IBM – Open Beta just announced on www.iiug.org (12/02/2006)

11. Building Data Applications • The DataSet • Microsoft’s disconnected database object – like an in-memory database • One or more DataTables • Each DataTable has a DataAdapter that interacts with the actual data store • Can have primary keys, relations, etc. • Derived from ComponentModel, so it is available in the Designer as graphical object

12. DataSet Diagram

13. Framework Considerations • Concurrency checking • Could be implemented with interface and concurrency column • Data binding – built-in can be flaky and not always bi-directional • Data caching and lazy instantiation • Can’t make DataSets “global” and still use designer-aided binding • How to define relations between objects • Null field checking; null objects allowed? • How to send data across the wire in n-tier architecture • Open source .Net ORM frameworks (like nHibernate) still emerging – no Informix-specific implementations that I have found

14. DevForce Framework from IdeaBlade

15. DevForce Editions • Express – Free version: ORM, UI DataBinding, RAD features, one-click deployment, royalty free • Professional – support for stored procedures, databinding to 3rd party controls, multiple database connections, support • Enterprise – Business Objects Server for n-tier deployment, disconnected or offline functionality, distributed transactions, web services objects (at CornerCap, we use this one)

16. DevForce Framework Particulars • Uses OleDb for ORM (must run coledb script against sysmaster) • No direct support for serial datatype, but I have Informix id generator code I am willing to share • Includes its own object query language for writing queries (will probably move to LINQ. Pass-thru sql allowed.) • Uses .Net Remoting for moving objects between tiers • Concepts Guide is a must-read; other documentation, videos, and samples are good

17. DevForce – How Does it Work? • ORM tool generates a DataRow class for each table/object (e.g., PlayerDataRow) • Generates a descendent class from the DataRow (Player). This is where your custom code goes • PersistenceManager class handles interactions with the database (no data adapters) • BindingManagers handle UI binding • Business objects have relationships that allow for “dot navigation” of properties from related objects • Let’s go to the demo!

18. Q&A/Discussion • Other Resources • DevForce framework (www.ideablade.com), especially read the Concepts Guide! • DevForce user group (www.ibrunner.com) • Expert C# 2005 Business Objects, 2nd ed. Rockford Lhotka (CSLA framework, if you want to do more of your own building. Many of the ideas are incorporated into DevForce) • IdeaBlade contact – Lisa Martin (lisam@ideablade.com)

19. Informix User Forum 2006 Building Informix Data-Driven Applications with .Net Sean R. Durity sdurity@cornercap.com Washington, D.C. December 8-9, 2006