How to create database table class

Question

I have a project in c# about a hospital system which contains 30 child forms.

I have created database which contain more than 30 tables.

I created data access like this:

namespace emamTree
{
    public  class  DBAccess
    {
        public static string connectionString = ConfigurationManager.ConnectionStrings["TreeFinal"].ConnectionString ;

       public SqlCommand Intialize(string query, params SqlParameter[] prmArray)
        {
                SqlConnection cn = new SqlConnection(connectionString);

                SqlCommand cmd = new SqlCommand(query, cn);

                if (!query.Contains(" "))
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                if (prmArray.Length > 0)
                    cmd.Parameters.AddRange(prmArray);

                cn.Open();

                return cmd;
        }

        public  int ExcuteNonQuery(string query, params SqlParameter[] prmArray)
        {
            try
            {
                SqlCommand cmd = Intialize(query, prmArray);

                int affectedRows = cmd.ExecuteNonQuery();

                cmd.Connection.Close();

                return affectedRows;
            }
            catch (SqlException ex)
            {
                return ex.Number;
            }
        }

        public object ExcuteScalar(string query, params SqlParameter[] prmArray)
        {
            try
            {
                SqlCommand cmd = Intialize(query, prmArray);

                object value = cmd.ExecuteScalar();

                cmd.Connection.Close();

                return value;
            }
            catch (SqlException ex)
            {
                return ex.Number;
            }
        }

        public  SqlDataReader ExcuteReader(string query, params SqlParameter[] prmArray)
        {
                SqlCommand cmd = Intialize(query, prmArray);

                SqlDataReader sqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return sqlDataReader;
        }

        public  DataTable ExcuteDataTable(string query, params SqlParameter[] prmArray)
        {
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);

                if (!query.Contains(" "))
                    sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

                if (prmArray.Length > 0)
                    sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);

                DataTable dt = new DataTable();
                sqlDataAdapter.Fill(dt);

                return dt;
        }

        public  DataSet ExcuteDataSet(string query, params SqlParameter[] prmArray)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, connectionString);

            if (!query.Contains(" "))
                sqlDataAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

            if (prmArray.Length > 0)
                sqlDataAdapter.SelectCommand.Parameters.AddRange(prmArray);

            DataSet ds = new DataSet();
            sqlDataAdapter.Fill(ds);

            return ds;
        }
   }
}

In form patient(Table Patient) I create save method and works fine:

public void Save()
{

     DBAccess db = new DBAccess();
     db.ExcuteNonQuery("insert into Patients  (FileNum,PatientTypeID,EngName,NationalityID,RelegionID) values  (@FileNum,@PatientTypeID,@EngName,@NationalityID,@RelegionID)",
         new SqlParameter("@FileNum", txtFileNum.Text),
         new SqlParameter("@PatientTypeID", txtPatientTypeID.Text),
         new SqlParameter("@EngName", txtEngName.Text),
         new SqlParameter("@NationalityID", txtNationalityID.Text),
         new SqlParameter("@RelegionID", txtRelegionID.Text)
                         );

}

My question is how to make classes for each table in the database?

Answer 1

Creating a data access layer is a very critical part of any application. It has to be a separate library so that you can use it in any project today and in the future. Say, tomorrow you want your windows app to be converted to a web application! You can add the DAL libray and start using it.

Having said that, building your own DAL is time cosuming and its like reinventing the wheel. So you need to explore available options which might suite your requirements. Out of the box you have an ORM called EntityFramework. Its pretty straight forward to use but performace wise, its slow compared to handwritten sql. There is also another popular ORM called NHibernate. Its original counterpart Hibernate is huge in java community but it has a very steep learning curve.

But i like to use PetaPoco. It gives best of both worlds. ORM + sql. There are also other such micro ORMs like Dapper, Massive, etc. You need try each one of them and pick the one that suits your application at hand.

Even after choosing a framework that fits your bill, you need to create abstractions to make sure you can change frameworks later if required. Creating a proper DAL is a huge undertaking and has taken big chunk of my time as a developer to get things right. You can explore and find it out by your self. Good luck.

Answer 2

Use existing solutions like EntityFramework. It provided all the functionality you need and it will make your life much easier than writing it all yourself (and I know, I have done it).