1 / 28

Accessing Informix With ADO.Net

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

riva
Download Presentation

Accessing Informix With ADO.Net

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related