asked on

C# Class Design

Hi,

I am trying to create a class library. The idea is that it will be the class that takes care of the connections to my database & retrieving or writing data to the database.

In the code example below I have a class called Prices in the namespace Securities & two other classes called WriteToDataBase & Retrieve in the namespace Results.

These classes will all do their separate things. They will all need to open and close an connection to the database. I do not want to have to write code in every class to open and close a connection to a the database. What be the best way to go about solving this?

Thanks,
M

namespace TradingDatabase
{
    class ConnectionHandler
    {
        public SqlConnection connection;
        public void Open()
        {
        }
        public void Close()
        {
        }
    }

    namespace Securities
    {
        public class Prices
        {                        
        }        
    }

    namespace Results
    {
        public class WriteToDataBase
        {
        }

        public class Retrieve
        {
        }
    }
}

Open in new window

AndyAinscow

How about something like this with inheritance so the common code is in a base class
class X
{
protected SqlConnection connection;
public void Open()
{
}
public void Close()
{
}
}

class Y : X
{
public foo()
{
Open();
//do something();
Close();
}

}

ASKER CERTIFIED SOLUTION

Jacques Bourgeois (James Burger)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndyAinscow

Hmm, considering a class is to be called eg. WriteToDatabase I find it odd you do not recommend inheritance.

Anyway - make certain you understand what static means and what effect it can have on your code. If you don't be prepared for problems.

Jacques Bourgeois (James Burger)

Good point Andy. It was very late for me when I answered, and I should have gone a little further and complete my answer. A sentence such as "These classes will all do their separate things" should have triggered a more complete explanation.

You do not create a class to do different things. You create a class because you want to group related operations into one object that is dependant upon these operations.

Classes named WriteToDataBase and Retrieve are not good things. Look in the framework, and try to find a class name that is a verb. You do not create a class to perform only one operation. A verb like WriteToDataBase and Retrieve usually shows the need for a method, not for a class.

The role of a class is to accumulate a set of related data in a single entity, along with the operations (methods) needed to manipulate that data.

If you want only a collection of methods, you use a static class, such as System.IO.File.

And one can question the need to create a generic class to perform these operations. Writing and retrieving data to and from a database can be very different from one operation or table to another. Each requires different SQL or stored procedure, different parameters. And classes to work with these already exist in the framework. So why reinvent the wheel.

And since the recommended way to go for most operations is to use a local Connection variable, opening and closing in 2 different methods is not a very good idea either. The way to go, for most situations would look more something like the following:

namespace TradingDatabase
	{
	class ConnectionHandler
		{
		public static SqlConnection CreateConnection ( )
			{
			SqlConnection con=new SqlConnection("connectionString");
			// Code to create the connection
			return con;
			}
		}

		public class TradingItem
		{
			public void GetPrices ( )
			{
			SqlCommand cmd = new SqlCommand ( "command", ConnectionHandler.CreateConnection ( ) );
			// Prepare the command: SQL or stored procedure, parameters
			cmd.Connection.Open ( );
			cmd.ExecuteReader ( ); // Or any other Execute suited for the task
			// Retrieve the information in a format that is suitable for the application, such as filling the current class properties
			cmd.Connection.Close ( );
			}
		}
	}

Open in new window

As you can see, Open and Close already exist. What would you gain by creating your own? The line where we ExecuteReader plays the role of Retrieve, and it also already exists in the framework. In the same way, similar code with ExecuteNonQuery would WriteToDatabase.

The code in the 2 comment lines would be different for each operation, so you would need to write it anyway before calling your own methods, that would only call the already existing Open, Close, ExecuteReader and ExecuteNonQuery in the background.