Building a c# 3-tier project - database handling issues

Question

I have to create a c# application which I expect it will grow out pretty large so I want to start with a good setup. Because I mostly use PHP and it has been a very long time since I wrote a C# application, I'm having some issues which I cannot get my head around because of the various information and bad examples I can find online.

I have set up a 3-tier structure having a form, a business logic class and a dataclass. I also have a databaseclass to perform the database operations.

How should I handle databaseconnections? I now handle them in the Database class itself by opening the connection and closing it in the crud operations(get, insert, delete, ...)

I based myself on this article: http://www.codeproject.com/Articles/36847/Three-Layer-Architecture-in-C-NET Is it a good one?

I'm a bit confused because in PHP, I open my connection in the beginning of my page using a new instance where the constructor immediately provides a connection. In c# I see a lot of opening and closing for every query?

frmUsers (the users form)

private void btnListUsers_Click(object sender, EventArgs e)
{
   UsersBLL usersbll = new UsersBLL();
   DataTable dtUsers = usersbll.getUsers();
}

UsersBLL (the business logic class)

class UsersBLL
{
   private DataUsers du;
   public UsersBLL()
   {
      du = new DataUsers();
   }
   public DataTable getUsers()
   {
      return du.getUsers();
   }
}

DataUsers (the data class)

public class DataUsers
{
   private Database db;

   public DataUsers()
   {
      db = new Database();
   }
   public DataTable getUsers()
   {
      return db.get("select * from users");
   }
}

Snippet from the Database class:

class Database
{
        private iDB2Connection cn;


        public Database(){
            cn = new iDB2Connection(ConfigurationManager.ConnectionStrings["iseries"].ToString());
        }

        public void openConnection()
        {    
            cn.Open();
        }

        public void closeConnection()
        {
            if (cn != null)
            {
                cn.Close();
                cn.Dispose();
            }
        }
        public DataTable get(String query)
        {
            try
            {
                openConnection();
                iDB2Command cmd = cn.CreateCommand();
                cmd.CommandText = query;
                iDB2DataAdapter da = new iDB2DataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            finally
            {
                closeConnection();
            }
        }
}

Answer 1

The general practice for database connections is to "Acquire Late, Release Early". This is because database connections are precious resources and we do not want to hold on to them unnecessarily.

Your implementation of the Database class looks clean and you can easily support both ways i.e. Instantiating and calling on each call or having a global variable. Personally, I will prefer and recommend using it per-call.

You may want to consider implementing IDisposable in your Database class.