Transcript Title of Presentation Two Lines Maximum

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
• 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
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.)
•
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.
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
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) { }
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);
}
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);
}
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);
}
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
DataSet Diagram
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
Building Your Own Strongly Typed
DataSets
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
•
–
XxxBuilder for each DataSet (inherits from DataSetBuilder)
•
–
Includes generalized CompileXSD() that executes the xsd.exe
compiler
Key method to override is BuildXSD()
DataLibraryBuilder
•
Builds each IBuildable class that it finds
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
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”] );
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)
)
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);
}
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() )
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
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
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.
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
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)
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.
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 Informixspecific 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.
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)
Informix User Forum 2005
Moving Forward With Informix
Accessing Informix With
ADO.Net
Sean R. Durity
[email protected]
Atlanta, Georgia
December 8-9, 2005