Data Access Strategies

[Oyvind]

I'm working on a Windows forms/C# database application. My background is
6-7 years of VB 4 - 6, MS Access, VC++, mixed in with a lot of T-SQL and MS
SQL Server in general and some OOA/OOD.

Previously, I haven't been overly impressed with the capabilities of the
various "graphical" data access tools that have been provided with VB etc.
Though deceptively simple to get started with, IMHO they haven't provided
the necessary flexibility when the going gets tough and (especially) the
business rules start piling up.

So, when starting on my project I discarded data sets and all that
complexity in favor of using the simple data reader to retrieve my data.
All updates, inserts and deletes are handled through stored procedures, and
the select statements are dynamically created in the appropriate data access
classes. It's a conceptual n-tier architecture, where the GUI only
interacts with business classes, and see no underlying data reader or
similar objects. The architecture of my business layer is, briefly, as
follows:

[Serializable]
public class Contact
{
int m_contactID;
string m_name;
// etc...

public Contact()
{}

public int ContactID
{
get
{
return m_contactID
}
}

public string Name
{
get
{
return m_name;
}
set
{
m_name = value;
}
}

// more property get/set statements

public void Get(int contactID)
{
IDataReader dReader = ContactDBAccess.Get(contactID); // calls a
static method
if(dReader.Read())
Populate(dReader);

dReader.Close();
}

public void Save()
{
IDataReader dReader = ContactDBAccess.Save(this);
if(dReader.Read())
Populate(dReader);

dReader.Close();
}

public void Delete()
{
ContactDBAccess.Delete(this);
}

internal void Populate(IDataReader dReader)
{
m_contactID = Convert.ToInt32(dReader["ContactID"]);
m_name = Convert.ToString(dReader["Name"]);
// etc...
}
}

// An example of a collection class. My actuall collection
// classes inherit from a more complex custom collection class
// and are more strongly typed
[Serializable]
public class ContactCollection : ArrayList
{
public ContactCollection()
{}

public void Get()
{
// E.g. a metod to retrieve all contacts in the DB
IDataReader dReader = ContactDBAccess.Get();
Populate(dReader);
dReader.Close():
}

private void Populate(IDataReader dReader)
{
Contact newContact;
while(dReader.Read())
{
newContact = new Contact();
newContact.Populate(dReader);
base.Add(newContact);
}
}
}

internal class ContactDBAccess
{
static internal IDataReader Get(int contactID)
{
string sql = SQLSelect + " and ContactID = ?";
OleDbParameter para = new OleDbParameter("@ContactID", contactID);
DBConnection db = new DBConnection(); // a custom class to
provide basic db access functionality

// Call a custom function that takes a string and a parameter,
// and returns an OleDbDataReader or similar. An
// overloaded version will, e.g., take an array of OleDbParameters
OleDbDataReader myReader = db.ExecRead(sql, para);
return myReader;
}

static internal IDataReader Get()
{
// E.g., returns all records
}

static internal IDataReader Save(Contact saveObject)
{
// Code to save the passed object to the db and return the new
// ContactID if this is a new contact

return Get(contactID); // look up the added/updated contact and
return it to the caller.
}

static internal void Delete(Contact deleteObject)
{
// code to delete the contact
}

static private string SQLSelect
{
get
{
string sql = "SELECT ContactID,
+ "Name "
+ "FROM Contact WHERE 1=1 ";
return sql;
}
}
}

The GUI only sees and accesses the Contact and ContactCollection classes.

This has given me a pretty flexible business layer, but as you can see
there's a fair amount of boiler plate code in there. Now, I realise that I
may have pretty much replicated the functionality of data sets. My question
is really whether or not I may have just generated a ot of extra work, and
would have been just as well off with just using data sets. Would they
offer any real advantages without sacrificing too much of the control I
meant to achieve by using the strategy outlined above? I'm fully aware of
the capabilities of the data sets to consume data from XML etc, but then I
could always code that capability into my existing classes, too.

TIA,
OyvindS

 

[Jerry Negrelli]

How would your GUI handle a situation where you want to
display a list of 10,000 Contacts? Create a
ContactCollection and loop over each object?

That's where I find the ADO objects especially useful.
You can continue to have a structure similar to what you
have now but extend it to include something like
a "GetList" method which returns an ADO object instead of
a collection of "Contact" objects. In my experience this
has provided much better performance.

I'm always looking for a more efficient way to handle the
data layer, so I encourage you to keep pressing this
topic!

JER

-----Original Message-----
I'm working on a Windows forms/C# database application. My background is
6-7 years of VB 4 - 6, MS Access, VC++, mixed in with a lot of T-SQL and MS
SQL Server in general and some OOA/OOD.

Previously, I haven't been overly impressed with the capabilities of the
various "graphical" data access tools that have been provided with VB etc.
Though deceptively simple to get started with, IMHO they haven't provided
the necessary flexibility when the going gets tough and (especially) the
business rules start piling up.

So, when starting on my project I discarded data sets and all that
complexity in favor of using the simple data reader to retrieve my data.
All updates, inserts and deletes are handled through stored procedures, and
the select statements are dynamically created in the appropriate data access
classes. It's a conceptual n-tier architecture, where the GUI only
interacts with business classes, and see no underlying data reader or
similar objects. The architecture of my business layer is, briefly, as
follows:

[Serializable]
public class Contact
{
int m_contactID;
string m_name;
// etc...

public Contact()
{}

public int ContactID
{
get
{
return m_contactID
}
}

public string Name
{
get
{
return m_name;
}
set
{
m_name = value;
}
}

// more property get/set statements

public void Get(int contactID)
{
IDataReader dReader = ContactDBAccess.Get (contactID); // calls a
static method
if(dReader.Read())
Populate(dReader);

dReader.Close();
}

public void Save()
{
IDataReader dReader = ContactDBAccess.Save(this);
if(dReader.Read())
Populate(dReader);

dReader.Close();
}

public void Delete()
{
ContactDBAccess.Delete(this);
}

internal void Populate(IDataReader dReader)
{
m_contactID = Convert.ToInt32(dReader ["ContactID"]);
m_name = Convert.ToString(dReader["Name"]);
// etc...
}
}

// An example of a collection class. My actuall collection
// classes inherit from a more complex custom collection class
// and are more strongly typed
[Serializable]
public class ContactCollection : ArrayList
{
public ContactCollection()
{}

public void Get()
{
// E.g. a metod to retrieve all contacts in the DB
IDataReader dReader = ContactDBAccess.Get();
Populate(dReader);
dReader.Close():
}

private void Populate(IDataReader dReader)
{
Contact newContact;
while(dReader.Read())
{
newContact = new Contact();
newContact.Populate(dReader);
base.Add(newContact);
}
}
}

internal class ContactDBAccess
{
static internal IDataReader Get(int contactID)
{
string sql = SQLSelect + " and ContactID = ?";
OleDbParameter para = new OleDbParameter ("@ContactID", contactID);
DBConnection db = new DBConnection(); // a custom class to
provide basic db access functionality

// Call a custom function that takes a string and a parameter,
// and returns an OleDbDataReader or similar. An
// overloaded version will, e.g., take an array of OleDbParameters
OleDbDataReader myReader = db.ExecRead(sql, para);
return myReader;
}

static internal IDataReader Get()
{
// E.g., returns all records
}

static internal IDataReader Save(Contact saveObject)
{
// Code to save the passed object to the db and return the new
// ContactID if this is a new contact

return Get(contactID); // look up the added/updated contact and
return it to the caller.
}

static internal void Delete(Contact deleteObject)
{
// code to delete the contact
}

static private string SQLSelect
{
get
{
string sql = "SELECT ContactID,
+ "Name "
+ "FROM Contact WHERE 1=1 ";
return sql;
}
}
}

The GUI only sees and accesses the Contact and ContactCollection classes.

This has given me a pretty flexible business layer, but as you can see
there's a fair amount of boiler plate code in there. Now, I realise that I
may have pretty much replicated the functionality of data sets. My question
is really whether or not I may have just generated a ot of extra work, and
would have been just as well off with just using data sets. Would they
offer any real advantages without sacrificing too much of the control I
meant to achieve by using the strategy outlined above? I'm fully aware of
the capabilities of the data sets to consume data from XML etc, but then I
could always code that capability into my existing classes, too.

TIA,
OyvindS


.

 

[Oyvind]

That's an interesting point. Of course, I could always extend my classes to
provide similar functionality to what you suggest. OTOH, I'm not convinced
I'd actually want to display 10 000+ records to my users all at once. As
far as the current implementation goes, the answer is yes, populate the
collection and loop through it to display the data. Since my project is
still in the development phase there isn't a lot of data in the test db, so
I haven't done any real performance testing yet. However, this discussion
has prompted me to reread the documentation on data reader, and I realize
that I will at the very least want to rewrite my Populate() methods to take
advantage of IDataReader.GetValues()

 

[Jerry Negrelli]

Is there any reason that your ContactCollection
class' "Get" method is public? Since it returns void, I
get the impression that the only thing that will ever
call it is your ContactCollection's default constructor
so that you would write GUI code like this:

ContactCollection c = new ContactCollection();
foreach(Contact aContact in c){
....
}

instead of:

ContactCollection c = new ContactCollection();
c.Get();
foreach(Contact aContact in c){
....
}

And since I'm on the subject, I think the name Get is
confusing since a call to it doesn't actually Get
anything. I'd say you should just add the code that
calls Populate to your default constructor and drop
the "Get" method altogether. And if you decide you need
a public way to repopulate the collection, create a
public "Refresh" method.

JER

 

[Oyvind]

I'll agree that the Get() method may be redundant. I guess I just wanted to
provide an alternative method for those who may come after me (I'm working
alone on this project so far). However, as you will see in the example code
below, my classes may have methods that take the same class as an argument
in different contexts, e.g. OrderCollection.GetCustomerOrders(Contact) and
OrderCollection.GetShipperOrders(Contact).

The example I posted was very brief. Actually, my real collection classes
would look more like this. Please note that this is just an example and a
lot of code is omitted.

/////////////////////////////////////////////////////////
public class Order
{
int m_orderID = 0;

int m_customerID = 0;
Contact m_customer = null;

int m_shipperID = 0;
Contact m_shipper = null;

int m_shippingDestinationID = 0;
Location m_shippingDestination = null;

bool m_dirty = false;

public Order()
{}

// This is a read-only property
public int OrderID
{
get
{
return m_orderID;
}
}

public int CustomerID
{
get
{
return m_customerID;
}
set
{
if(value != m_customerID)
m_dirty = true;

m_customerID = value;
}
}

public Contact Customer
{
get
{
return m_contact;
}
set
{
m_customer = value;
if(m_customer == null)
this.CustomerID = 0;
else
this.CustomerID = m_customer.ContactID;
}
}

public void GetCustomer()
{
// Assume a Contact constructor that takes the ContactID as a parameter
this.Customer = new Contact(m_customerID);
}

public int ShipperID
{
get
{
return m_shipperID;
}
set
{
if(value != m_shipperID)
m_dirty = true;

m_shipperID = value;
}
}

public Contact Shipper
{
get
{
return m_shipper;
}
set
{
m_shipper = value;
if(m_shipper == null)
this.ShipperID = 0;
else
this.ShipperID = m_shipper.ContactID;
}
}

public void GetShipper()
{
// see comment for GetCustomer()
this.Shipper = new Contact(m_shipperID);
}

public int DestinationID
{
// get/set as above
}

public Location Destination
{
// get/set as above
}

public bool Dirty
{
get
{
return m_dirty;
}
}

public void Save()
{
// code to save the object data to the db
}

public void Delete()
{
// code to delete the order from the db
}

internal void Populate(IDataReader dReader)
{
// code to put data from a single data reader record into the current
object
m_dirty = false;
}
}

public class OrderCollection : ArrayList
{
Contact m_shipper = null;
Contact m_customer = null;
Location m_destination = null;

// The default constructor
public OrderCollection()
{}

// Another overload, e.g. get all orders that have been shipped to a
certain location
public OrderCollection(Location destination)
{
Get(destination);
}

// Get all orders placed by a customer
public void GetCustomerOrders(Contact customer)
{
m_customer = customer
IDataReader dReader = OrderDBAccess.GetCustomerOrders(m_customer);
this.Populate(dReader);
dReader.Close();
}

// Get all orders shipped by a shipper
public void GetShipperOrders(Contact shipper)
{
m_shipper = shipper;
IDataReader dReader = OrderDBAccess.GetShipperOrders(m_shipper);
this.Populate(dReader);
dReader.Close();
}

// OK, this may be redundant since it could just as well
// have been caontained in the overloaded constructor
public void Get(Location destination)
{
m_destination = destination;
IDataReader dReader = OrderDBAccess.Get(m_destination);
this.Populate(dReader);
dReader.Close();
}

// Other methods, e.g. a general search method etc.
// ...

private void Populate(IDataReader dReader)
{
Order newOrder;
while(dReader.Read())
{
newOrder = new Order();
newOrder.Populate(dReader);

if(m_customer != null)
newOrder.Customer = m_customer;

if(m_shipper != null)
newOrder.Shipper = m_shipper;

if(m_destination != null)
newOrder.Destination = m_destination;

base.Add(newOrder);
}
}
}

// Code for OrderDBAccess not shown

///////////////////////////////////////////////////////