Accessing Informix With ADO.Net

1. Informix User Forum 2005Moving Forward With Informix Accessing Informix With ADO.Net Sean R. Durity Manager of IT CornerCap Investment Counsel Atlanta, Georgia December 8-9, 2005

2. Agenda • The IBM Informix ADO.Net Driver • ADO.Net Driver Basics (with examples) • Building Your Own Strongly Typed Data Sets (with examples) • ADO.Net 2.0? • Limitations and Other Frameworks • Q & A

3. The Informix ADO.Net Driver • Current version is Client SDK 2.90TC1 • Now installed by default, must have .Net framework 1st • 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 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. Building Your Own Strongly Typed DataSets • 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

11. DataSet Diagram

12. Building Your Own Strongly Typed DataSets • Why Strongly Typed? • Intellisense for remembering and inserting table and column names • Compiler catches table and column name errors • Property editors show table and column names • Creating a Strongly Typed DataSet • Microsoft DataAdapter has Configure Data Adapter and Generate DataSet wizards • Informix DataAdapter has just added Configure Data Adapter. It has no Generate DataSet wizard. • More manual coding required to build strongly typed DataSets for Informix

13. Building Your Own Strongly Typed DataSets

14. Building Your Own Strongly Typed DataSets • Built-in Tools • DataSet.WriteXmlSchema() • XSD schema file • XSD compiler (xsd.exe provided w/ .Net framework) • Created Tools • DataSetBuilder and IBuildable interface • Includes generalized CompileXSD() that executes the xsd.exe compiler • XxxBuilder for each DataSet (inherits from DataSetBuilder) • Key method to override is BuildXSD() • DataLibraryBuilder • Builds each IBuildable class that it finds

15. Building Your Own Strongly Typed DataSets • Generates • A dsXxx strongly typed DataSet class • Extending • Create an Xxx class that inherits from dsXxx • Insert all the IfxDataAdapters and commands required to interact with the database • Xxx is a fully-contained data object

16. Examples With Code • Goal is code like this: Console.WriteLine(client.clientcode + “ “ + client.clientacctname+ “ “ + client.createdate); • Not this: Console.WriteLine(ds.Tables[“clients”].Rows[ “clientcode”] + “ “ + ds.Tables[“clients”].Rows[ “clientacctname”] + “ “ + ds.Tables[“clients”].Rows [“createdate”] );

17. Examples With Code Clientstest table create table 'infx'.clientstest ( clientcode SERIAL not null, clientacctname CHAR(60) not null, primarycontact CHAR(30) not null, primaddrcode CHAR(10), createdate DATE, initialamt DECIMAL(18,0) )

18. Examples With Code IBuildable interface public interface IBuildable { string FileName {get; set;} string FilePath {get; set;} Logger Log {get; set;} DataSet DS {get; set;} void BuildXSD(); void CompileXSD(string outputDirectory); }

19. Examples With Code • Abstract DataSetBuilder class • Implements IBuildable • Has a concrete method for CompileXSD(), so we only write it once • BuildXSD() is the virtual method • Also includes the property definitions • Is the parent for the concrete Builder classes (which must override BuildXSD() )

20. Examples With Code • ClientsBuilder class • Concrete descendant of DataSetBuilder • Key method is the override of BuildXSD() • Manually build an untyped DataSet (tables, keys, relations, etc.) then call the DataSet’s WriteXMLSchema() method

21. Examples With Code • DataLibraryBuilder class • WindowsForm application that uses reflection to generate the .cs files for each DataSet • IMPORTANT: The BuilderAssembly must point to the one in this project’s directory. Otherwise, reflection will think that the types do not match and nothing will get built • If the DataSet in the Builder class changes, this must be re-run

22. Examples With Code • dsClients.xsd and dsClients.cs • Generated by the DataLibraryBuilder and put into the directory for the DataLibrary project • Our 42 line ClientsBuilder becomes a 43 line xsd file, which then generates a 500 line .cs file. That .cs file (dsClients.cs) is a strongly-typed DataSet. We will extend it for even more convenience.

23. Examples With Code • Extending to Clients.cs • Inherits from the strongly typed dsClients DataSet • Includes IfxDataAdapters and their associated commands for selecting, inserting, deleting, and updating • Becomes the developer-friendly data object • The place where database-intensive knowledge is required • Overrides Fill() and Update() from DataSet

24. Examples With Code • Using Our Clients Object • There is a typed AddclientsRow() method on the clients DataTable • There is a FindByclientcode() method on the clients DataTable • Clients can be dragged onto Windows (or Web) form and bound to controls • clientsRow has members named and typed for each column (e.g., clientsRow.initialamt)

25. 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 - No word.

26. Limitations and Other Options • Concurrency checking • Could be implemented with interface and concurrency column • Data binding – can be flaky and not always bi-directional • No caching or lazy instantiation • Can’t make DataSets “global” and still use designer-aided binding • No relations between objects • Null data can cause problems • Open source .Net ORM frameworks still emerging – no Informix-specific implementations that I have found • However, for a very robust ORM and databinding framework, look at DevForce from IdeaBlade. There is a Lite/free version for client server applications. (www.ideablade.com) It initially uses OLEDb and works with Informix.

27. Q&A/Discussion • Other Resources • Code and article of this content at: www.ibm.com/ • ADO.Net Cookbook - Bill Hamilton • Microsoft ADO.Net - David Sceppa • DevForce framework (www.ideablade.com)

28. Informix User Forum 2005Moving Forward With Informix Accessing Informix With ADO.Net Sean R. Durity sdurity@cornercap.com Atlanta, Georgia December 8-9, 2005