what is the best practice to keep connection string and connection object in a windows forms application?

Question

0.00/5 (No votes)

See more:

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 28-Feb-14 0:31am

Chathur

Updated 28-Feb-14 0:56am

Tom Marvolo Riddle

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2014-02-28T00:51:00

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.

PIEBALDconsult · Accepted Answer · 2014-02-28T05:18:00

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.

what is the best practice to keep connection string and connection object in a windows forms application?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0