Advice on use of static methods (and ADO)

[Laban]

Hi,

I find myself using static methods more than I probably should, so I am
looking for some advice on a better approach.

For example, I am writing an app that involves quite a bit of database
operations on purchase orders and inventory. I have created a PurchaseOrder
class and Inventory class to encapsulate operations like creating POs,
finding items, etc. These two classes are used extensively from different
parts of the app.

In order to not have to create instances of these classes from all classes
that use them, I created a Database class with static members holding
instances of the PurchaseOrder and Inventory classes (and some database
objects used in various places).

Using this approach, looking up an item number is very easy, and the code is
easy to read:
itemNumber = Database.Inventory.GetItemNumber(barcode);

Or to load a purchase order:
if (Database.PO.POExists(poNumber))
{
Database.PO.Load(poNumber);
}

Is there a better approach that would be about as easy to use? Other
advice?

--- Here is the Database class:

public class Database
{
private const string TASK_ID = "purchasing";
private const string CONNECTION_STRING = "the conn. string...";

private static MySqlConnection _mySqlConnection1;
private static MySqlDataAdapter _mySqlDataAdapter1;
private static DataSet _dataSet1;

private static PurchaseOrder _purchaseOrder;
private static Inventory _inventory;

public static string TaskId
{
get { return TASK_ID; }
}

public static MySqlConnection Connection
{
get { return _mySqlConnection1; }
}

public static MySqlDataAdapter DataAdapter
{
get { return _mySqlDataAdapter1; }
}

public static DataSet DataSet
{
get { return _dataSet1; }
}

public static PurchaseOrder PO
{
get { return _purchaseOrder; }
}

public static Inventory Inventory
{
get { return _inventory; }
}

static Database()
{
_mySqlConnection1 = new MySqlConnection(CONNECTION_STRING);
_mySqlConnection1.Open();
_dataSet1 = new DataSet();
_mySqlDataAdapter1 = new MySqlDataAdapter();
_purchaseOrder = new PurchaseOrder();
_inventory = new Inventory();
}
}
Thanks in advance,

Laban

[Hasan O. Zavalsiz]

Hi Laban ,
have a look at my question " design pattern of "Business Layer" you may
find some answers

"Laban" <no****@dev.null> wrote in message
news:11*************@corp.supernews.com...

Hi,

I find myself using static methods more than I probably should, so I am
looking for some advice on a better approach.

For example, I am writing an app that involves quite a bit of database
operations on purchase orders and inventory. I have created a PurchaseOrder class and Inventory class to encapsulate operations like creating POs,
finding items, etc. These two classes are used extensively from different
parts of the app.

In order to not have to create instances of these classes from all classes
that use them, I created a Database class with static members holding
instances of the PurchaseOrder and Inventory classes (and some database
objects used in various places).

Using this approach, looking up an item number is very easy, and the code is easy to read:
itemNumber = Database.Inventory.GetItemNumber(barcode);

Or to load a purchase order:
if (Database.PO.POExists(poNumber))
{
Database.PO.Load(poNumber);
}

Is there a better approach that would be about as easy to use? Other
advice?

--- Here is the Database class:

public class Database
{
private const string TASK_ID = "purchasing";
private const string CONNECTION_STRING = "the conn. string...";

private static MySqlConnection _mySqlConnection1;
private static MySqlDataAdapter _mySqlDataAdapter1;
private static DataSet _dataSet1;

private static PurchaseOrder _purchaseOrder;
private static Inventory _inventory;

public static string TaskId
{
get { return TASK_ID; }
}

public static MySqlConnection Connection
{
get { return _mySqlConnection1; }
}

public static MySqlDataAdapter DataAdapter
{
get { return _mySqlDataAdapter1; }
}

public static DataSet DataSet
{
get { return _dataSet1; }
}

public static PurchaseOrder PO
{
get { return _purchaseOrder; }
}

public static Inventory Inventory
{
get { return _inventory; }
}

static Database()
{
_mySqlConnection1 = new MySqlConnection(CONNECTION_STRING);
_mySqlConnection1.Open();
_dataSet1 = new DataSet();
_mySqlDataAdapter1 = new MySqlDataAdapter();
_purchaseOrder = new PurchaseOrder();
_inventory = new Inventory();
}
}
Thanks in advance,

Laban

[Scott Roberts]

"Laban" <no****@dev.null> wrote in message
news:11*************@corp.supernews.com...

In order to not have to create instances of these classes from all classes
that use them, I created a Database class with static members holding
instances of the PurchaseOrder and Inventory classes (and some database
objects used in various places).
Why do you not want to create instances of the PO class?

Or to load a purchase order:
if (Database.PO.POExists(poNumber))
{
Database.PO.Load(poNumber);
}

Is there a better approach that would be about as easy to use? Other
advice?

In our app, an object instance represents a single database row. I think
this is typical. So "Database.LoadPO()" would return an instance of PO
instead of loading data into a static instance of PO.

PO po1 = Database.LoadPO(poNumber);
if ( po1 != null )
{
po1.PODate = DateTime.Now;
POLine line1 = po1.Lines.Add();
line1.ItemNumer = 123456;
po1.Save();
}

This has the added advantage of being able to have multiple POs in memory at
once (i.e. a collection) so they can be compared, manipulated, displayed in
a list, etc.

I also don't like having static connection objects that remain connected
throughout the life of the application. IMO it's better to open/close the
connection as needed and utilize connection pooling for performance.

[WJ]

"Laban" <no****@dev.null> wrote in message
news:11*************@corp.supernews.com...

Hi,

I find myself using static methods more than I probably should, so I am
looking for some advice on a better approach.

You may take a look at the DAAB written by MS. It is a very stable program
that uses singleton class.

John

[Laban]

You may take a look at the DAAB written by MS. It is a very stable program
that uses singleton class.

I have read up on the singleton class - that seems to be a better approach
than what I was using.

Thanks,
Laban

[Laban]

You may take a look at the DAAB written by MS. It is a very stable program
that uses singleton class.

I have read up on the singleton class and it seems to be a better approach
than what I was using.

Thanks,
Laban

[Kevin Spencer]

I can see a few problems with your design.

First, one important principle of OOP is that a busines class should "mind
its own business." A Database class should be agnostic of business classes
that it may interact with. You just might want to use that Database class
with another project, and if you design it well, you can.

A class should follow a certain type of design pattern, a methodology that
transcends all of your projects, so that you don't have to keep track of
which methodology is used by which project, but can easily jump from one to
another with little self re-education. This is where "Best Practices" come
from. Conventions and uniform methodologies make it relatively easy to move
from one project to another.

So, let's have a look at the particular problem I'm talking about, with
regards to re-usability and uniform methodology:

private static PurchaseOrder _purchaseOrder;
private static Inventory _inventory;
You have static instances of a couple of business classes here that are
clients of a database, but business objects in fact. Now, imagine you start
another project which has nothing to do with Purchase Orders or Inventory.
Let's say, a Human Resources project. Now you have a class called Employee.
Are you going to add the Employee class to the Database class in order to
keep a uniform methodolgy? Over a period of years, you might end up adding
static instances of dozens of classes to the Database class, only 1 or 2 of
which is used by any given project. The rest are simply taking up space, and
making the Database class increasingly more complex and difficult to manage.

So, let's get down to principles here. The purpose of a Database class is to
provide data to business classes. It should therefore be completely agnostic
of the clients it serves. The 911 operator doesn't need to know how to get
in touch with you. Imagine the list of phone numbers a 911 operator would
have to keep! And why would the 911 operator need to call you? Instead, and
logically so, everybody who may need to call 911 knows the number to call.
Some relationships have one-way dependencies, and your classes should keep
this in mind. A business class should "mind its own business."

Next problem:
private static MySqlDataAdapter _mySqlDataAdapter1;
private static DataSet _dataSet1;
Yes, your present project, and the business classes in it may need a DataSet
from time to time. With a stretch of the imagination, and a certain lack of
creativity, one could perhaps even justify exposing a DataAdapter, although
I would surmise that the DataAdapter should be used exclusively by the
Database class (after all, it is a Database class, and a class should "mind
its own business" - no reason why anyone else should be messing with it).
But all that aside, this is a single instance of a DataSet, and a single
instance of a DataAdapter. What happens when another DataSet is needed at
the same time? Oh yes, I can hear you now telling me, "my project never
needs more than one DataSet at a time." Of course, your project isn't
finished, right? And you may want to use this Database class with future
projects, right? Wouldn't it make more sense to create a static method that
returns an instance of a DataSet, and/or a DataAdapter? After all, a future
project might not even need such a heavy thing as a DataSet. From time to
time, a DataTable, or even a DataReader will do the trick. And of course,
sometimes all that is needed is a single data value, or nothing at all (for
example, when doing an INSERT).

So, again, this is not extensible, or very re-usable.

Now for a biggie:
private static MySqlConnection _mySqlConnection1;
A Database Connection has a lot in common with a File pointer. It is an open
connection to a database. It consumes resources, and there are a limited
number of available connections to the database. It also exposes unmanaged
objects. Think of it like a telephone connected to a switchboard. The
switchboard has 20 available lines. There may be 25 rooms in the hotel. So,
in order to make sure you get to the switchboard operator as fast as
possible, you call the switchboard, taking up one of the 20 lines, and leave
your phone off the hook. Now, what happens if the other 24 guests decide to
do the same? Ony 20 of you will be able to get to the switchboard operator
at all. Everyone else is SOL, like the loser in a game of musical chairs. Of
course, with your Database class, the switchboard only has one line, one
Connection. Everyone in the hotel is going to be scrambling to your room and
waiting in line to talk to the Operator, and you're not likely to get much
sleep! ;-)

Again, the Connection is connected to unmanaged resources, just like a File
pointer. This introduces the possibility of memory leakage. A small
possibility with your current configuration, but again, you've got to think
about the future. Your future self will be glad you did!

So, bottom line is, the .Net Platform uses Connection Pooling by default, as
a means of getting through to the Operator as quickly as possible. So, you
really don't have to worry about all the expense of opening a Connection.
Therefore, like a file, open and close it as quickly as possible, and free
up the Connection for use by the Connection Pool. The Pool also has a limit
on the number of available Connections.

In other words, opening and closing of a Connection can be handled withing
function scope, with a few exceptions. You can account for the exceptions.
So, while a global Connection String may be a good idea (although you may
need more than one from time to time in the future), a global Connection
object is definitely not.

If you keep these principles of business classes "minding their own
business," plan for re-usability and extensibility, make your application
tiers independent of one another, and keep one-way dependencies in mind,
you're more than halfway to a solid design pattern. The rest is pretty much
up to you.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

"Laban" <no****@dev.null> wrote in message
news:11*************@corp.supernews.com... Hi,

I find myself using static methods more than I probably should, so I am
looking for some advice on a better approach.

For example, I am writing an app that involves quite a bit of database
operations on purchase orders and inventory. I have created a
PurchaseOrder class and Inventory class to encapsulate operations like
creating POs, finding items, etc. These two classes are used extensively
from different parts of the app.

In order to not have to create instances of these classes from all classes
that use them, I created a Database class with static members holding
instances of the PurchaseOrder and Inventory classes (and some database
objects used in various places).

Using this approach, looking up an item number is very easy, and the code
is easy to read:
itemNumber = Database.Inventory.GetItemNumber(barcode);

Or to load a purchase order:
if (Database.PO.POExists(poNumber))
{
Database.PO.Load(poNumber);
}

Is there a better approach that would be about as easy to use? Other
advice?

--- Here is the Database class:

public class Database
{
private const string TASK_ID = "purchasing";
private const string CONNECTION_STRING = "the conn. string...";

private static MySqlConnection _mySqlConnection1;
private static MySqlDataAdapter _mySqlDataAdapter1;
private static DataSet _dataSet1;

private static PurchaseOrder _purchaseOrder;
private static Inventory _inventory;

public static string TaskId
{
get { return TASK_ID; }
}

public static MySqlConnection Connection
{
get { return _mySqlConnection1; }
}

public static MySqlDataAdapter DataAdapter
{
get { return _mySqlDataAdapter1; }
}

public static DataSet DataSet
{
get { return _dataSet1; }
}

public static PurchaseOrder PO
{
get { return _purchaseOrder; }
}

public static Inventory Inventory
{
get { return _inventory; }
}

static Database()
{
_mySqlConnection1 = new MySqlConnection(CONNECTION_STRING);
_mySqlConnection1.Open();
_dataSet1 = new DataSet();
_mySqlDataAdapter1 = new MySqlDataAdapter();
_purchaseOrder = new PurchaseOrder();
_inventory = new Inventory();
}
}
Thanks in advance,

Laban

[Peter Kirk]

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> skrev i en meddelelse
news:uX**************@tk2msftngp13.phx.gbl...

I can see a few problems with your design.

First, one important principle of OOP is that a busines class should "mind
its own business." A Database class should be agnostic of business classes
that it may interact with. You just might want to use that Database class
with another project, and if you design it well, you can.
What exactly do you mean by "business class" and "database class"? For me a
"business" class might represent some data used in the particular
application, for example an "Employee" class. A "database" class interacts
with a database on behalf of the application, retrieving or storing business
classes.

This is maybe an incorrect way to view it?

Do you mean that a database class is a generic class you write to interact
with a generic database? That would be very hard to write, wouldn't it?

What objects does it return from a query? A Hashtable with column names and
values? An IList of "Database row" objects? Where does the database class
receive knowledge of what query to execute?

The database classes I have written usually do know the "business" classes.
For example I might have a "FetchEmployee" method in my database class. This
method knows the query to execute and instantiates and returns a known type
of object (for example an instance of IEmployee).

Is this bad design? How could it be done better?
So, let's get down to principles here. The purpose of a Database class is
to provide data to business classes.

Do you mean that a business class should query the database (via the
Database class) to populate itself with data?

Peter

[Kevin Spencer]

Hi Peter,

A Database class should do one thing and do it well: Interact with both
business classes (as a server) and with a database (as a client). It is the
intermediary between the business classes and the database itself (wherever
and whatever the database may be). The .Net Framework has some really keen
classes for working with data, such as the DataSet, DataTable, DataAdapter,
DataReader, and so on. These classes present a uniform interface to any
business class that needs data. So, the Database class functions like a
"Universal Translator," an "ambassador," if you will, between the business
classes and the Database itself.

The classic 3-tier application model is the basis for all good multi-tiered
application models (which are generally sub-divided in one way or another).
It represents the core levels of the application. An application is a tool
that enables humans to work with data. A Database is a container for data,
and contains functionality for working with the data, inserting, updating,
deleting, sorting, indexing, querying, etc. It presents the data in it in
the form of an in-memory cursor, in a proprietry format that is unique to
the database product itself.

Technologies like the SQL language, OLE DB, the native SQL Provider, ODBC,
etc., present a "driver-oriented" and more universal means for applications
to interact with databases. However, they are too universal to be useful to
a business class, which may need to change the data source from time to
time, from, for example, Access to SQL Server.

The Data Layer, or Data Tier of an application is developed with the same
programming technology (in this case, the .Net Framework) as the business
classes. It also has the capability of working with a variety of data
sources. It's purpose is to present to the application as a whole an
interface to the underlying data store, whatever that may be. If the
database changes, the Database class can still work with it. Yet, on the
client end, it will present the data in the same format regardless, in this
case, as DataSets, DataTables, DataReaders, etc. If it is well-designed, it
can serve many types of business classes. It knows nothing about the data it
is serving; it only follows instructions from the Business classes
themselves, which know what the data is, and what they want to do with it.

The Business tier is called this because it contains the "business rules"
for working with the specific data it is designed to work with. It contains
all the logic for creating new records, updating records, munging data in a
variety of ways, and exposes an object-oriented API (programming interface)
that is easier for client classes (and developers) to work with, than, for
example, a table, or the results of a JOIN query.

You have, for example, a PurchaseOrder class. Now, in the database, it is
nothing but a row in a table, or a JOIN query result set, with a bunch of
columns in it. But you expose it as if it were a real Purchase Order. The
PurchaseOrder class can do things like look up Inventory (using the
Inventory class), perform calculations on the data, perhaps present a string
representation of a Purchase Order, an XML document, or even present a
stream for printing, who knows? So, the PurchaseOrder business class
encapsulates all of the functionality for working with a Purchase Order, as
well as the data.

On the top tier, you have the User Interface layer. It is important to keep
this separate from the Business layer, as you may want to expose the data in
a different User Interface at some point in the future. So, the Interface
classes don't do the munging of the data. They simply provide a
user-friendly interface ot the user, and a connection on the back end to the
business classes they work with. Again, the business classes actually do all
the work, each "minding its own business."

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.
"Peter Kirk" <[e-mail]> wrote in message
news:e5*************@tk2msftngp13.phx.gbl...

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> skrev i en meddelelse
news:uX**************@tk2msftngp13.phx.gbl...

I can see a few problems with your design.

First, one important principle of OOP is that a busines class should
"mind its own business." A Database class should be agnostic of business
classes that it may interact with. You just might want to use that
Database class with another project, and if you design it well, you can.

What exactly do you mean by "business class" and "database class"? For me
a "business" class might represent some data used in the particular
application, for example an "Employee" class. A "database" class interacts
with a database on behalf of the application, retrieving or storing
business classes.

This is maybe an incorrect way to view it?

Do you mean that a database class is a generic class you write to interact
with a generic database? That would be very hard to write, wouldn't it?

What objects does it return from a query? A Hashtable with column names
and values? An IList of "Database row" objects? Where does the database
class receive knowledge of what query to execute?

The database classes I have written usually do know the "business"
classes. For example I might have a "FetchEmployee" method in my database
class. This method knows the query to execute and instantiates and returns
a known type of object (for example an instance of IEmployee).

Is this bad design? How could it be done better?

So, let's get down to principles here. The purpose of a Database class is
to provide data to business classes.

Do you mean that a business class should query the database (via the
Database class) to populate itself with data?

Peter

[Laban]

Hi Kevin,

Thank you for a very informative reply.

If I understand you correctly, I would rewrite to do something like this:

* Data layer *
Database class
Not sure what goes here. Just one singleton database class serving all
business objects?
Based on the business classes below, what would be examples of suitable
members here?

* Business layer *
Inventory (handles all inventory)
Methods: GetItemNumber(string barcode), GetItemDescription(string
itemNumber), etc.8

PurchaseOrder (handles all purchase orders)
Methods: POExists(...), Load(...), ItemIsOnCurrentPO(), etc.
PurchaseOrder would use Inventory as needed

Receiving (for receiving orders into the warehouse)
Methods: CreateNewReceiver(...), AddItemToReceiver(...), Save(),
SendReceiverToServer()
Receiving would use PurchaseOrder & Inventory as needed

Transfer (for transferring items between warehouses)
Methods: CreateNewTransfer(...), AddItemToTransfer(...), Save(),
SendTransferToServer()
Transfer would use PurchaseOrder & Inventory as needed

User
Properties: Instance, UserId, SecurityLevel, AllowedSend, AllowedUpdates
This class is a Singleton, since there is never more than one user at a
time.
* User Interface layer *
Form_Main
Just a menu for user to select action (Receive PO / Transfer Out / Transfer
In)

Form_Receiving
Contains information about the PO being received and a grid control with the
items received (e.g. ItemNo, Description, QtyOrdered, QtyReceived, etc.).
QUESTION: The grid has to be bound to a dataset, so the Receiving class
would have to expose a dataset, right?

Form_Transfer
Contains information about the transfer and a grid with the items
transferred (grid bound to dataset exposed by Transfer class).

Does the above make sense? Viewpoints?

Thanks,

Laban