.NET -> Database best practices

[Plater]

I don't think I brought this up before, so I'm going to ask it.

My recent style of using a database in my applications (web-based and windows-based) has been to create a static class called "DBFunctions"
with functions like

public static DataTable GetAllUsers()
{/**/}
 

Now, I use ONE dbconnection for everything. At the begining of each public db function I have a helper function that checks the state of the dbconnection object and waits for it to become in either the open or closed state (as opposed to a transitional state) and then procedes to make sure it's closed, and then re-opens it.

Is closing and re-opening the connection a bad practice? I remember learning that creating a new connection to database is "costly" but that modern db server implentations actually have the connections created already in a pool and your objects just pick one from the pool, thus removing the cost of opening and closing.
Is this true? Or should I put some more thought into the best practice for database usage?

[davef]

I don't think I brought this up before, so I'm going to ask it.

My recent style of using a database in my applications (web-based and windows-based) has been to create a static class called "DBFunctions"
with functions like

public static DataTable GetAllUsers()
{/**/}
 

Now, I use ONE dbconnection for everything. At the begining of each public db function I have a helper function that checks the state of the dbconnection object and waits for it to become in either the open or closed state (as opposed to a transitional state) and then procedes to make sure it's closed, and then re-opens it.

Is closing and re-opening the connection a bad practice? I remember learning that creating a new connection to database is "costly" but that modern db server implentations actually have the connections created already in a pool and your objects just pick one from the pool, thus removing the cost of opening and closing.
Is this true? Or should I put some more thought into the best practice for database usage?

Connection pooling feature is intrinsic to the Connection object shipped with ADO.NET 2 and is enabled by default. Unless you explicitly disable it in the connection string, you seem to be inadvertently using it even with a singleton connection object. It's considered a good practice to keep connection pooling enabled to boost performance savings on connection opening-closing overhead. I believe there's a way to specify the max size of the pool, too.
You can benchmark performance gains with a code similar to this:

SqlConnection testConnection =  new SqlConnection(@"Data Source=(local)\SQLEXPRESS;Initial Catalog=DEMO;Integrated Security=SSPI;");
long startTicks = DateTime.Now.Ticks;
for (int i = 1; i <= 100; i++)
{
    testConnection.Open();
    testConnection.Close();
}
long endTicks = DateTime.Now.Ticks;
Console.WriteLine("Time taken : " + (endTicks - startTicks) + " ticks.");
testConnection.Dispose();
 

To disable connection pooling, put "Pooling=false" in connection string.
This code snippet is from
http://www.c-sharpcorner.com/UploadF...nnPooling.aspx