Using keyword with creating new connection with database

Question

I try to improve my progamming skill when it comes to database connections.

I found those 2 similar solutions on the internet, and I would like to know what the difference is, which one should I use and why?

using (OdbcConnection con = new OdbcConnection("connectionstring"))
{
       con.Open
       //QUERY
}

AND

private OdbcConnection con;

public Database()
{
    con = new OdbcConnection("connectionstring");
}

public insertPerson()
{
    con.Open();
    //QUERY
}

possible duplicate of [Which pattern is better for SqlConnection object?](http://stackoverflow.com/questions/3378825/which-pattern-is-better-for-sqlconnection-object) — David L, Nov 04 '13 at 23:38

score 1 · Accepted Answer · answered Nov 05 '13 at 02:23

I will go for the first example. Using keyword has a idisposable interface inherited. It has automatic disposing of object after the closing code.

On your second example. You need to make use that you will dispose the object properly.

Either way it can connect to the database. But the first one is my preference.

score 0 · Answer 2 · answered Nov 04 '13 at 23:41

Either way is acceptable. A "using" statement will dispose (return the allocated memory) of your connection object after the closing bracket.

It is a matter of personal preference. I tend to use a single connection object (like your second example) for my data access classes.

Make sure you close your connection after you have made your database calls.

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

If your code is going to be maintained by more than just yourself, it would be best practice to go with the "using" syntax:

Avoiding Problems with the Using Statement

ado.net Closing Connection when using “using” statement

edited May 23 '17 at 10:24

Community

1
1

answered Nov 05 '13 at 00:04

IrishChieftain

15,108
7
50
91

score 0 · Answer 4 · answered Nov 05 '13 at 00:06

Your second example seems the initial stage of a class that would do everything needed to work with the underlying database. If this is you intention keep in mind that this solution leads to a very difficult to maintain code base. Not to mention the potential problems when your connection is not correctly closed because some exception breaks the normal flow of code. My personal advice is 'Don't do that'.

Instead you should have a specialized function that handle the search/insert/update/delete of your data and builds your Plain Old CLR Objects. In this scenario the usual pattern is Create the connection, Open, Use and Destroy. A pattern that is correctly handled by the using statement of your first example. Today you don't need to worry about the cost of creating and opening a connection because you usually have a connection pooling infrastructure that reduce at minimum level the cost to reopen the same connection again and again.

Said that, this is an opinion and probably this question will be closed....

Using keyword with creating new connection with database

4 Answers4