.NET wrappers for data objects

Note: This page is an early note about what has now become DbWrappers, a free C# data object code generator.  Source code is available.  Check that out; it's much more up-to-date than the info here.  It works, too ;)

I've been coding in C# for a month or so now, and I think I finally understand how System.Data works.  The idea of a DataSet sounds cool, but they don't seem to work very well for me in practise.  The real advantage of C# for me is how well IntelliSense works with it (compared to C++, where the IDE often freezes for 10 seconds while it hunts down the object definition, or Python, which I edit in a text editor), but this only works if the data I'm after is a property, not a member of a hash-like object.

Justin Rudd is working on a simple way to create DataSets from XML.  This looks like a good idea.  I'm subscribed to his RSS feed; I hope this works well for him.

Personally I want something along the lines of JDO; specifically some way to generate useful C# code from a databae schema.  For each table (let's call our hypothetical table 'tblExample'), I want the following interface / objects:

• class TblExample: A wrapper around a table

  • public TblExample( Connection connection )

    • ctor

  • public TblExampleRow SelectOne( string queryString )

    • Queries the database for a single row - pass it an SQL query string and it will query the database, pull out a single row, drop all the useful information into a new instance of TblExampleRow and return that.

  • public TblExampleReader SelectMany( string queryString )

    • Queries the database with the intent to fetch multiple rows (i.e. with a query string that isn't an equality test on a unique key) and returns a wrapper around a DataReader that will return TblExampleRow objects on command.

  • public TblExampleRow NewRow()

    • This works like the NewRow() method on a DataTable - it creates a TblExampleRow object that you can populate and then add to the table with Add().

  • public int Add( TblExampleRow newRow )

    • This generates an SQL INSERT command and adds the new row into the database.  It will then grab the generated primary key (if an AutoNumber field) and put it in the row object so you can modify it and update the database later on.

• class TblExampleReader: A wrapper around a DataReader

  • (no public constructor)

  • public bool Read()

    • This reads a row from the DataReader and stuffs the data into a TblExampleRow (which is then accessible as the 'Row' property).  When the DataReader runs out of rows, Read() returns false (i.e. it returns true if it successfully fetched a row, false otherwise).

  • public TblExampleRow Row

    • This returns the TblExampleRow constructed by the last Read() operation.

• class TblExampleRow:

  • (no public constructor)

  • public (type) (column name)

    • A property is created for each column, with the relevant C# type.  This stores a cache of the data read through the TblExampleReader, so will persist once the reader is disposed of.  If the column isn't marked read only, this will be writeable as well.

  • public int Commit()

    • This will push any modified data back into the database by generating an SQL UPDATE command.  It will return the number of rows modified (should be 1) as with an ExecuteNonQuery() call.

Is there already something around that does this?  If not, I'll write it myself because it will make my life much much easier when coding for .NET.  If done cleverly, it might also be usable in other languages - it would be cool to have a cross-language data access code generator.  I wouldn't mind something like this for C++ or Python (although you could probably do it all at runtime in Python ;-)

I'll consider implementation in another story; this one's getting a bit long.