473,727 Members | 2,035 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Data Access Strategies

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(dReade r);

dReader.Close() ;
}

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

dReader.Close() ;
}

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

internal void Populate(IDataR eader dReader)
{
m_contactID = Convert.ToInt32 (dReader["ContactID"]);
m_name = Convert.ToStrin g(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 ContactCollecti on : ArrayList
{
public ContactCollecti on()
{}

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

private void Populate(IDataR eader dReader)
{
Contact newContact;
while(dReader.R ead())
{
newContact = new Contact();
newContact.Popu late(dReader);
base.Add(newCon tact);
}
}
}

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 ContactCollecti on 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
Nov 15 '05 #1
4 2087
How would your GUI handle a situation where you want to
display a list of 10,000 Contacts? Create a
ContactCollecti on 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 is6-7 years of VB 4 - 6, MS Access, VC++, mixed in with a lot of T-SQL and MSSQL Server in general and some OOA/OOD.

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

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

[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 astatic method
if(dReader.Read ())
Populate(dReade r);

dReader.Close() ;
}

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

dReader.Close() ;
}

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

internal void Populate(IDataR eader dReader)
{
m_contactID = Convert.ToInt32 (dReader ["ContactID"]); m_name = Convert.ToStrin g(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 ContactCollecti on : ArrayList
{
public ContactCollecti on()
{}

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

private void Populate(IDataR eader dReader)
{
Contact newContact;
while(dReader.R ead())
{
newContact = new Contact();
newContact.Popu late(dReader);
base.Add(newCon tact);
}
}
}

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 toprovide 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 andreturn 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 ContactCollecti on classes.
This has given me a pretty flexible business layer, but as you can seethere's a fair amount of boiler plate code in there. Now, I realise that Imay have pretty much replicated the functionality of data sets. My questionis really whether or not I may have just generated a ot of extra work, andwould have been just as well off with just using data sets. Would theyoffer any real advantages without sacrificing too much of the control Imeant to achieve by using the strategy outlined above? I'm fully aware ofthe capabilities of the data sets to consume data from XML etc, but then Icould always code that capability into my existing classes, too.
TIA,
OyvindS
.

Nov 15 '05 #2
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.Get Values()

"Jerry Negrelli" <je************ @removethispart datascientific. com> skrev i
melding news:00******** *************** *****@phx.gbl.. .
How would your GUI handle a situation where you want to
display a list of 10,000 Contacts? Create a
ContactCollecti on 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!

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

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

instead of:

ContactCollecti on c = new ContactCollecti on();
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
-----Original Message-----
That's an interesting point. Of course, I could always extend my classes toprovide similar functionality to what you suggest. OTOH, I'm not convincedI'd actually want to display 10 000+ records to my users all at once. Asfar as the current implementation goes, the answer is yes, populate thecollection and loop through it to display the data. Since my project isstill in the development phase there isn't a lot of data in the test db, soI haven't done any real performance testing yet. However, this discussionhas prompted me to reread the documentation on data reader, and I realizethat I will at the very least want to rewrite my Populate () methods to takeadvantage of IDataReader.Get Values()

"Jerry Negrelli" <je************ @removethispart datascientific. com> skrev imelding news:00******** *************** *****@phx.gbl.. .
How would your GUI handle a situation where you want to
display a list of 10,000 Contacts? Create a
ContactCollecti on 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!

.

Nov 15 '05 #4
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 .GetCustomerOrd ers(Contact) and
OrderCollection .GetShipperOrde rs(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_shippingDesti nationID = 0;
Location m_shippingDesti nation = 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.Cont actID;
}
}

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

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.Conta ctID;
}
}

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

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(IDataR eader 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 GetCustomerOrde rs(Contact customer)
{
m_customer = customer
IDataReader dReader = OrderDBAccess.G etCustomerOrder s(m_customer);
this.Populate(d Reader);
dReader.Close() ;
}

// Get all orders shipped by a shipper
public void GetShipperOrder s(Contact shipper)
{
m_shipper = shipper;
IDataReader dReader = OrderDBAccess.G etShipperOrders (m_shipper);
this.Populate(d Reader);
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.G et(m_destinatio n);
this.Populate(d Reader);
dReader.Close() ;
}

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

private void Populate(IDataR eader dReader)
{
Order newOrder;
while(dReader.R ead())
{
newOrder = new Order();
newOrder.Popula te(dReader);

if(m_customer != null)
newOrder.Custom er = m_customer;

if(m_shipper != null)
newOrder.Shippe r = m_shipper;

if(m_destinatio n != null)
newOrder.Destin ation = m_destination;

base.Add(newOrd er);
}
}
}

// Code for OrderDBAccess not shown

///////////////////////////////////////////////////////
"Jerry Negrelli" <je************ @removethispart datascientific. com> skrev i
melding news:01******** *************** *****@phx.gbl.. .
Is there any reason that your ContactCollecti on
class' "Get" method is public? Since it returns void, I
get the impression that the only thing that will ever
call it is your ContactCollecti on's default constructor
so that you would write GUI code like this:

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

instead of:

ContactCollecti on c = new ContactCollecti on();
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
-----Original Message-----
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.Get Values()

"Jerry Negrelli"

<je************ @removethispart datascientific. com> skrev i
melding news:00******** *************** *****@phx.gbl.. .
How would your GUI handle a situation where you want to
display a list of 10,000 Contacts? Create a
ContactCollecti on 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!

.

Nov 15 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
12454
by: ank | last post by:
Hi, I was curious about how to define static data member of template class. Should I put the definition in a separate source file or in the same header file as its template class? And when this data will be initialized if it is used across many translation unit, assume that it has constructor and needs dynamic initialization? I have blindly searched for the answer but I still not thoroughly
2
1528
by: robin | last post by:
The Oblique Strategies were originally a set of one-hundred cards, each bearing a short phrase. They were devised by Brian Eno and Peter Schmidt as ways of working through creative problems. When a blockage occurs, draw a card, and see if it can direct you in a tangential way that helps solve the problem. I have created a Python implementation that includes two different decks. Since one of these is my own, I can be sure this is an...
0
1458
by: DB2 DBA wanted | last post by:
Maines Paper & Food Service is a $2 billion/yr food distribution company with 9 distribution centers in the US. We are currently interviewing for the position detailed below. Relocation to Binghamton, NY will be necessary. If interested, please forward a resume to Bill.Kimler@maines.net ========================================================== Job Title: Database Architect
0
899
by: satya | last post by:
Hi All, I am confused about the data access strategies followed in ASP.Net with ADO.Net in a n-tier architecture.Can any one diffrentiate between Microsoft Data Access Application Block and Typed Dataset with in Visual Studio.Net which method need i follow. Satya
4
2745
by: DylanM | last post by:
I've seen a few examples on how to cache data in a WinForms GUI, just after some thought on the best solution. The data I'm trying to cache will be generally be small collections of Business Objects (eg Lookup data) 1) Using the Web Cache objects 2) Using AppDomain.CurrentDomain.SetData 3) Using the Caching Application Block
13
1778
by: CoreyWhite | last post by:
When playing games, perhaps the most simple is tic-tac-toe. The game has two simple strategies, one is defensive and the other offensive. It is not hard at first to learn how to tie games when playing an opponent. And then the next stage in development comes after you learn how to beat an opponent. You really can only employ either strategy when you get to make the first move, and your opponent will quickly learn what you are doing...
3
5346
by: Karabo | last post by:
What Are The Features And Data Management Strategies Of Postgresql
24
2524
by: David | last post by:
Hi list. What strategies do you use to ensure correctness of new code? Specifically, if you've just written 100 new lines of Python code, then: 1) How do you test the new code? 2) How do you ensure that the code will work correctly in the future? Short version:
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9406
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4786
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2158
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.