Best way to make central SqlConnection

Question

I have wondered what is the best way to make one central SqlConnection. So first thing when I started programming in C# was to put SqlConnection like this into each form I made:

public partial class form1 : Form
{
    SqlConnection conn = new SqlConnection(
"Data Source=SERVER\\SQL;Initial Catalog=DataBase;User ID=user;Password=pass");

    public form1 ()
    {
        InitializeComponent();
        timer1.Start();
    }
   }

Now I would like to make one central connection and get rid of all of these codes in beggining of each form.

I thought that class would be best way to do that. So I wanted to ask you if there is another good method how to make that.

As I'm begginer, please excuse my level of description.

Thank you for your answer/comments/opinions.

Answer 1

The standard way to store SqlConnection information is to use a configuration file such as app.config or web.config. Alternatively, you may create your own configuration file.

After that, use ConfigurationManager.ConnectionStrings["YourConnectionStringName"].ConnectionString rather than the hardcoded connection settings

Answer 2

Using one "central" connection is highly discouraged as it breaks multiple patterns that ADO.NET implements. It is much easier to use a "central connection string" instead. If you want to use dynamic parameters, you might want to look into the "SqlConnectionStringBuilder" class.

ADO.NET is built around an "aquire late, release early" pattern for DB connections. Every other attempt will sooner or later cause massive problems (trust me on that, seen it many times: network errors/transaction errors/concurrency errors/multithreading errors...)

ADO.NET uses a "connection pool" for the actual physical connection to the database. So unless you use different connection strings for each conneciton, you should end up with one connection anyway. But since the "pool" manages that one, it will always be in a clean state when it is (re)opened.

Personally, I like to use something like this for my connection strings:

internal static class DataSource
{
    private static string _ConnectionString;
    public static string ConnectionString
    {
        get
        {
            if (_ConnectionString == null)
                _ConnectionString = FunctionToDynamicallyCreateConnectionstring();
            return _ConnectionString;
        }
    }
    private static string FunctionToDynamicallyCreateConnectionstring()
    {
         SqlConnectionStringBuilder cb = new SqlConnectionStringBuilder();
         // initialize cb's properties here...
         return cb.ToString();
    }
}

and later

SqlConnection connection = new SqlConnection(DataSource.ConnectionString);

This pattern will ensure that the exact same connection string is used thorughout my code and that the dynamic code still only runs once.

[EDIT] In most cases I avoid coding the entire connection string into the app.config file because some parameters might be mandatory for my code to work and should never be messed with. I create custom settings for "server" or "database" properties that I read and assign to the ConnectionStringBuilder...

Answer 3

Don't have one global connection because if it breaks (due to a network error) your app stops working. Also, you might have bugs with transactions accidentally left open or options set that other parts of your code do not expect.

Instead, store the connection string globally and create a fresh connection every time you need one. It is better to start fresh every time.

static class ConnectionFactory
{
 public static SqlConnection Create() {
  return new SqlConnection(GetConnectionStringSomehow());
 }
}

Use it like this:

using (var conn = ConnectionFactory.Create()) {
 //do something
}

Answer 4

If you just want one globally accessible sql connection object then just look into static classes, as you can have a static class with a static constructor so everything can access it.

I would not do this, however if you are learning there is no harm in it, but statics/singletons are often used to have 1 instance of a component throughout an application.

// Simple static example
public static class DatabaseConnection
{
    public static IDBConnection ActiveConnection {get; private set;}

    static DatabaseConnection()
    {
        var myConnectionString = // get your connection string;
        ActiveConnection = new SqlConnection(myConnectionString);
        ActiveConnection.Connect(); // This is bad, really should be in a using
    }
}

// Simple static usage
DatabaseConnection.ActiveConnection.ExecuteQuery(blah);

Problem is that you wont be controlling that resource, and it wont close the connection until the app closes which is bad practice, so you could improve on this slightly while still keeping your globally accessible functionality.

// Better static example using actions
public static class DatabaseConnection
{
    private static string connectionString;

    public static void OpenConnectionAnd(Action<Connection> actionToDo)
    {
        using(var connection = new SqlConnection(this.connectionString))
        {
            connection.Connect();
            actionToDo.Invoke(connection);
            connection.Disconnect();
        }       
    }

    static DatabaseConnection()
    {
        this.connectionString = // get your connection string;
    }
}

// Better usage example
DatabaseConnection.OpenConnectionAnd(x => x.Execute(blah));

Syntax may not be 100% right as i'm just writing the above off top of my head, but should be close enough to be useful.

Answer 5

You can look into the Data Access Pattern to handle all of the interfacing with sql.

This is usually the recommended approach, so multiple forms (or whatever the case may be) can access the same methods that retrieve and store data.

Here is a question about how to use it (with answers).