C# Data Connections Best Practice

C# Data Connections Best Practice?

Connections are pooled by .NET, so re-creating them generally isn't an expensive operation. Keeping connections open for long periods of time, however, can cause issues.

Most "best practices" tell us to open connections as late as possible (right before executing any SQL) and closing them as soon as possible (right after the last bit of data has been extracted).

An effective way of doing this automatically is with using statements:

using (SqlConnection conn = new SqlConnection(...))
{
    using(SqlCommand cmd = new SqlCommand(..., conn))
    {
        conn.Open();
        using(DataReader dr = cmd.ExecuteReader())  // or load a DataTable, ExecuteScalar, etc.    
        {
             ...
        {
    }
}

That way, the resources are closed and disposed of even if an exception is thrown.

In short, opening a connection when the app opens or when each form opens is probably not the best approach.

What Is The Best Practices For Database Connection String Management Among Multiple Applications

Try to get the variables you need from environment instead, there's a way to do it inside your web.config files too. Look this answer!

Supposing that you're running the tens application on the same machine and they're sharing the same database you'll find easier to change the environment variables than updating manually every web.config file.

Then if you want to have different env variables based on where you're application is executed give direnv a try.

Best Practices: C# working with DB

First about connection pooling. If you will use ADO.NET then you do not need to worry about that, because it's already there. You don't need to do any extra work, you just create a connection:

using (var connection = new SqlConnection(connectionString))
{
    // Queries to DB
}

You should always Close or Dispose you connections. The names of the methods look "scary" but actually connections are reused. Please read this MSDN article to get more details.

The code you proposed looks over-complicated. I think you should consider using async/await pattern which is in general not multithreaded, but it handles UI responsiveness issues and simplifies writing/reading of the code. In newer versions of .NET almost all methods that are potentially long to execute has async versions. So for example your data access layer might look like that (I'm using Dapper ORM's QueryAsync method just to keep code short and simple):

public async Task<IList<District>> GetAllDistrictsAsync()
{
    using (var connection = await GetConnectionAsync())
    {
        return (await connection.QueryAsync<District>("select * from Districts")).ToList();
    }
}

public async Task<IDbConnection> GetConnectionAsync()
{
    var connectionString = 
        ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;
    var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();
    return connection;
}

And then somewhere on UI:

private async void Button_Click(object sender, EventArgs e)
{
    var districts = await GetAllDistrictsAsync();
}

If you still need to execute some code in different thread you should look at Tasks namespace.

Task.Factory
    .StartNew<IList<District>>(GetAllDistricts)
    .ContinueWith(districts =>
    {
        // UI thread
    }, TaskScheduler.FromCurrentSynchronizationContext());

In this example GetAllDistricts is not async and is executed in different thread. But ContinueWith will be executed in UI thread because of TaskScheduler.FromCurrentSynchronizationContext().

Best practice for handle connections/queries

Usually, with respect to database connections, the policy should be open as late as possible and close as early as possible. So lets say if you have a single statement/query to execute then open the connection before executing the query and close your connection after that.

But, in your case you are executing 5000 queries in a loop, so there is no point in opening/closing the connection 5000 times. Instead utilize the single connection and execute all your queries.

Also, opening and closing connection just returns the connection to .Net connection pool. If there is already an open connection then opening a connection using Conn.Open(); would return that opened connection from the connection pool. See: SQL Server Connection Pooling (ADO.NET) and Creating database connections - Do it once or for each query?

What is Best Practice for C# ADO.NET database connection and parameterized query?

The code you've supplied could be improved a bit. You may want to consider something like the following. It uses the C# using construct to ensure that disposable objects, such as the SqlConnection object and the SqlCommand objects are closed and disposed when you've finished using them.

Also, though I haven't shown it here, you may want to validate your textbox contents prior to stuffing those into parameter values.

using System.Data.SqlClient;

...

string connectionString = "Data Source=S9; " +
                   "Initial Catalog=Hp; " +
                   "Integrated Security=SSPI";
string sql = "Insert into tt(cr,TimeSpent_Min,Date,Empid) values (@p1,@p2,@p3,@p4)";

using (var connection = new SqlConnection(connectionString))
{
    using (var command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@p1", textBox1.Text);
        command.Parameters.AddWithValue("@p2", textBox2.Text);
        command.Parameters.AddWithValue("@p3", textBox3.Text);
        command.Parameters.AddWithValue("@p4", textBox4.Text);
        connection.Open();
        command.ExecuteNonQuery();
    }
}
MessageBox.Show("Successfully Inserted");

It's important to note that using the Parameters.AddWithValue() method can be troublesome because the library makes assumptions about your parameters that may or may not be true. It is safer to build individual Parameter objects and add them to your Parameters collection.

EDIT

I was thinking about my answer to your question, and it seems obvious that there will be problems here with the Parameters.AddWithValue() calls, and here's why:
your query inserts 4 values into table TT: [cr], [TimeSpent_Min], [Date], and [EmpID], and they're probably not all nvarchar fields. Looking at the field names, it's hard to infer any datatype for [cr] - possibly varchar, nvarchar, char, or nchar. But [TimeSpent_Min] seems like it might be either int, time, or datetime. Your field [Date] might be date or datetime. And finally, your field [EmpID] might be type varchar, char, or int.

Of course, these are all just guesses. It's impossible for me to infer the datatypes of these values from their name. In your case, by using Parameters.AddWithValue(), you're asking the .NET framework to make an inference about their datatype by matching it to the type of the parameter being passed into the AddWithValue() call.

You're passing in Textbox.Text values, which are, of course, type string. Therefore, all of these parameters will be created with type nvarchar, because that's what the framework methods are coded to match a .NET string. If the database fields aren't actually nvarchar, then there could be problems.

There is a better way. As an example, ASSUMING that your parameters are the following types, here's how you can populate the Parameters collection more accurately:

Field            Parameter  Datatype
---------------  ---------  -------------
[cr]             @p1        nvarchar(100)
[TimeSpent_Min]  @p2        int
[Date]           @p3        datetime
[EmpID]          @p4        int

Code to go with this could be as follows:

// convert textbox values to proper datatypes
string parmP1Value = textBox1.Text;
int parmP2Value = int.Parse(textBox2.Text);
DateTime parmP3Value = DateTime.Parse(textBox3.Text);
int parmP4Value = int.Parse(textBox4.Text);

// create and load parameters
command.Parameters.AddRange(new[]
                            {
                                new SqlParameter
                                {
                                    ParameterName = "@p1",
                                    SqlDbType = SqlDbType.NVarChar,
                                    Direction = ParameterDirection.Input,
                                    Size = 100,
                                    Value = parmP1Value
                                },
                                new SqlParameter
                                {
                                    ParameterName = "@p2",
                                    SqlDbType = SqlDbType.Int,
                                    Direction = ParameterDirection.Input,
                                    Value = parmP2Value
                                },
                                new SqlParameter
                                {
                                    ParameterName = "@p3",
                                    SqlDbType = SqlDbType.DateTime,
                                    Direction = ParameterDirection.Input,
                                    Value = parmP3Value
                                },
                                new SqlParameter
                                {
                                    ParameterName = "@p4",
                                    SqlDbType = SqlDbType.Int,
                                    Direction = ParameterDirection.Input,
                                    Value = parmP4Value
                                }
                            }
                           );

Best practice for reusing SqlConnection

Creating a new instance of the class SqlConnection does not create a new network connection to SQL Server, but leases an existing connection (or creates a new one). .NET handles the physical connection pooling for you.

When you have finished with your connection (through which you can send multiple queries) just Close() or Dispose() (or use a using{} block preferably).

There is no need, and not good practise, to cache instances of the SqlConnection class.

What is the best practice for storing database connection details in .NET?

I think the best practice is to use integrated security if possible, for 2 reasons... It is the easiest solution to manage and it is the easiest solution to get right.

If for some reason you can't use integrated security and you have to use username and password encrypt them using something like the Enterprise Library's Config Manager to encrypt sections of your web.config.

Is it best practice to use a static database connection across multiple threads?

As discussed on the comments of your question. The best practice would be to leave the connection handling to ADO.Net as it contains connection pooling control so all you should do is open a connection every time you need execute a bit of SQL and then close it. The connection pool will not immediately close the connection as it will leave it open for configurable time to be able to pass it over to other threads requesting a new connection to be open. Additionally connections are not thread safe so each thread should have its on connection but again ADO.Net will deal with that.

If you want to learn more about the connection pool i suggest the following MSDN article: http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

I also highly recommend that you read Microsofts Best practices for ado .net here: http://msdn.microsoft.com/en-us/library/ms971481.aspx

Some other articles:

ADO.Net Best practices http://www.codemag.com/Article/0311051
GOOD READ is the Enterprise Patterns and Practices for Improving .Net application has a great part on ADO.net: http://msdn.microsoft.com/en-us/library/ff649152.aspx

C# Data Connections Best Practice