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?

Have you considered to use technology invented like 20 years ago called object relational mapping? It is even in the .NET framework under the name "Entity Framework". — TomTom
– TomTom, Commented Oct 12, 2014 at 7:09

Patrick Hofman · Accepted Answer · 2014-10-12 07:13:20Z

1

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).

answered Oct 12, 2014 at 7:13

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vinod Kumar Y S · Accepted Answer · 2014-10-12 07:25:23Z

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.

Collectives™ on Stack Overflow

How to create database table class

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related