RE: Objects and such

That's not far off, save two things. First, we generate our classes,
saves us writing all the grunt-code. Second, we've got a single base
class that does the IO. So a class would look something like:

[DatabaseTable("Customer")]
class Customer : BusinessObject {

//define some private fields
[DatabaseField("CustomerId", IsKey=true, OmitFromInsert=true)]
int _customerId;

[DatabaseField("CustomerName", Length=100)]
string _customerName;

[DatabaseField("AddressLine1", Length=100)]
String _address1;

[DatabaseField("LastOrderDate")]
DateTime? _lastOrderDate;

//define some properties
public int CustomerID {
get {return _customerId;}
}
public string Name {
get {EnsureLoaded(); return _customerName;}
set {SetField("_customerName", value);}
}
public string AddressLine1{
get {EnsureLoaded(); return _ AddressLine1;}
set {SetField("_AddressLine1", value);}
}
public DateTime? LastOrderDate {
get {EnsureLoaded(); return _ LastOrderDate;}
set {SetField("_LastOrderDate ", value);}
}

//And define the factory methods
public static Customer GetCustomer(int CustomerId)
{
Customer c = new Customer();
c.LoadFromDB(CustomerId);
return c;
}

public static Customer NewCustomer()
{
return new Customer();
}
}

For us that's a complete definition of a customer. The customer object
inherits a ton of stuff from BusinessObject, not the least of which are
the methods LoadFromDB, EnsureLoaded, SetField and Save.

LoadFromDB looks up the customer in the Customer table (from the
DatabaseTable attribute) and loads up the data, it's overloaded to
handle single and multiple-key tables. EnsureLoaded makes sure we're not
looking at a "ghost" object, which is a concept we support to delay-load
an object, if it's a ghost is LoadFromDB's it. SetField actually sets
the member fields with the new values, but also checks that strings,
byte arrays etc. aren't too long (Length on the DatabaseField
attribute), and maintains a list of changed fields. And Save would
actually save changes (or insert a new row) into the customer table.
Only columns that have changed (and key columns) are included in the IO
requests (hence the list of changed columns).

We would consume this via:

Customer myCustomer = Customer.GetCustomer(12345);
myCustomer.Name = "Walden";
myCustomer.AddressLine1 = "123 Main st;
myCystomer.Save();

-or-

Customer myCustomer = Customer.NewCustomer();
myCustomer.Name = "Paul";
myCustomer.Save();
Console.WriteLine("New customer Id is: {0}", myCustomer.CustomerId);

-Walden