myelin: DbWrappers introduction

This is a quick introduction to using DbWrappers, a C# code generator for ADO.NET data access.

First, get the software

You need the following things:

Install Python, and put the other two files in the directory where you want to put the code for your database objects.

Now, define your tables

Create your tables in the database, then edit Tables.xml and define your tables.  There are detailed instructions in that file, but here's a quick start.

For each table, create a <table> element in the file.  For each field, create a <field>, and also add a <index> for your primary key, as follows:

	<table name="tblExample" basename="Example" namespace="Example.Data">
	  <fields>
	   <field name="id" dbtype="AutoNumber" propname="Id" />
	   <field name="text" dbtype="Memo" propname="Text" />
	  </fields>
	  <indices>
	   <index name="id" type="pkey" />
	  </indices>
	 </table>

'name' is the name of your field in the database, 'dbtype' is the type as it would appear in Access, and 'propname' is the name of the property you want to generate in your C# code.

Run it!

Go to the command line and run:

	C:\Code\Example\Data>MakeWrappers.py

It will dump out some debugging output (your tables as it sees them) and then hopefully a message saying that everything worked fine.

Currently the error reporting is pretty poor; I'm keen to fix this, but I'm too lazy to make test cases!  So whenever you make a silly mistake and it fails to generate tables, post me a copy of your Tables.xml file (click 'contact' at the bottom of this page) and complain bitterly.  It will be fixed.

Now do something in the code

Anyway, if it worked, you now have three .cs files for each table.  Add these into your project and recompile.  Did it work?  If not, post feedback immediately: if code MakeWrappers.py generates doesn't compile, I want to know :)

Make sure your database connection is open (you need an OleDbConnection object or the equivalent from the DB provider you are using), then instantiate one Table object for each table you want to access.

	ExampleTable examples = new ExampleTable( connection );

Now you can do three things: insert, update and select.

Insert

To insert, create a row object and commit it into the database:

	ExampleRow row = examples.NewRow();
	row.Text = "this is a test";
	row.Commit();

Hang onto the 'row' object: it doesn't use any database resources, so it can have as long a life as you like.  In many apps, it will be very convenient to leave an ArrayList or Hashtable (or ListView or TreeView, if tied to a GUI) full of row objects hanging around for the entire lifetime of the program.

Update

Updating is easy: just change an existing row object and call Commit().  For the row object we created above:

	row.Text += ", and again!";
	row.Commit();

Select

There are several ways to select rows out of the database, and you don't have to pull out an entire row at a time if you like.

Just one row

If you know you only need one row, use SelectOne():

	ExampleRow row = examples.SelectOne( "id=1", null );

That will pull out just the one row, or throw a nasty database exception if it doesn't exist.

Lots of rows

If you want to select a few rows, you want to pull them out with a reader object:

	using ( ExampleReader r = examples.SelectMany( null, null ) )
	{
		while ( r.Read() )
		{
			ExampleRow row = r.Row;
			// (Now do something with the row we just fetched)
		}
	}

Not so many columns

If you want to only pull out a few columns (say, if you want to populate a TreeView with a limited amount of info about each row, and then go back and fetch the full row later on when you actually want to do something with the data), you can do that with the second argument to the Select*() methods:

	ExampleRow notMuchOfARow = examples.SelectOne(
		"id=1",
		new ExampleColumn[] { ExampleColumn.id }
		);

That wasn't particularly useful, was it?  Let's get the rest of it:

	ExampleRow theWholeRow = notMuchOfARow.SelectAgain( null );

Now we've got it all.

Was that it?

AFAIK I've introduced the whole thing.  Drop me a line ('contact' link below) if these examples don't work for you.  If you want extra features added, tell me (or do it yourself and mail me a patch, if you prefer).

Good luck!