Click here to Skip to main content
15,877,754 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Im a beginner to .net and for my final year project im developing a payroll system. Now I have some issues regarding ado.net sql connection object.
To keep the connection string centrally I have used a separate class call 'db'. Taking another step to this centralization thinking, Ive initialized the connection object also centrally in this 'db' class as follows.

C#
class db
 {
        string connectionString = ("server=CHATHURANGA-PC\\SQLEXPRESS;" +
                                          "Trusted_Connection=yes;" +
                                          "database=Payroll; " +
                                          "connection timeout=30");
        public SqlConnection GetConn()
        {
            SqlConnection NewConn = new SqlConnection(connectionString);
            return NewConn;
        }
 }

Now Im using this connection object as follows in my application...
I just want to know whether I would face issues in future because of this practice and also appreciate if one of experts could explain me what is the best practice in this regard.

C#
 class client
 {

        db NewDB = new db(); // db class is instantiated...
        SqlConnection newCon; // object referece newConn is created...


        //Method to insert new clients to 'client' table
 
        public void addNewClient(DateTime entDate, client NewClient)
        {
            try
            {
                newCon = NewDB.GetConn(); // connection object is assigned to newCon... but this is optional and I put this for the clarity

                string CommandString = "INSERT INTO client(Client_Name, C_Add, Contact_Person, C_Mob_No, C_Tel_No, Remarks, Ent_Date)" +
                                        " VALUES (@CName, @CAdd, @CPerson, @CMob, @CTel, @Remarks, @entDate)";
                                              
               
                SqlCommand SqlCom = new SqlCommand();
                SqlCom.CommandText = CommandString;
                SqlCom.Parameters.Add("@CName", SqlDbType.VarChar).Value = NewClient.CName;
                SqlCom.Parameters.Add("@CAdd", SqlDbType.VarChar).Value = NewClient.CAdd;
                SqlCom.Parameters.Add("@CPerson", SqlDbType.VarChar).Value = NewClient.CPerson;
                SqlCom.Parameters.Add("@CMob", SqlDbType.Char).Value = NewClient.CMob;
                SqlCom.Parameters.Add("@CTel", SqlDbType.Char).Value = NewClient.CTel;
                SqlCom.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = NewClient.Remarks;
                SqlCom.Parameters.Add("@entDate", SqlDbType.Date).Value = entDate;
                SqlCom.Connection = newCon;
                newCon.Open();
                
                SqlCom.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }

            finally
            {
                newCon.Close(); // newCon object is global to entire class so can call its close method.
            }
        }
}
Posted
Updated 28-Feb-14 0:56am
v2

I know you are a newby, so I'll try not to confuse you too much. If I do, let me know, OK?

That's not a bad idea - it's a simple form of what is known as a Three-Tier Model, where the data I/O is handled separated from the business logic, and both are kept separate from the user interface (These are often known as a DL (or DAL), BL and PL respectively, and are frequently kept in separate assemblies).

There are couple of things you should consider:
1) try not to "hard code" connection strings: if you know how to use settings files, then keeping it in there is a lot easier to work with, as you don't have to change your application when you move to a different PC, or just move your SQL server to a different PC.
2) I would recommend adding the IDisposable interface to your db class - and it should check if any connections exist and are open, and close them when the object is disposed.
 
Share this answer
 
Comments
Chathur 5-Mar-14 0:40am    
Thank you for the advice. Is this three-tier model a good approach to develop oo systems, in this case a payroll system?
It depends on the app. I write mostly console apps, so being able to specify the connection details on the command line is vital.
I would certainly not store a "connection string"; they're too concrete. I prefer to store the parts that are required to allow the app to create the connection string (the server name and database name for example) .

While I applaud you for using parameters, I'd like to point out that setting the datatype is a waste of effort -- the datatype will be set when you set the Value.

And the catch/throw is needless in your example. Try a using statement.
 
Share this answer
 
Comments
Chathur 5-Mar-14 1:05am    
Thank you for the advice. So I think its better to build the connection string dynamically and to create new connection when needed in method levels.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900