in c# global connection to be used in all classes

Question

I've a connection string saved to Properties.Settings.Default, i need to use an sql connection in all classes without having to declare it everytime, so how should it be declared?

Contrast

Answer 1

You actually don't need to use a single SqlConnection everywhere. A better approach would be to create a class to manage you data access. Typically this is the duty of your Data Access Layer (DAL). DAL is a set of classes that handle all the database related stuff.

A very simple class for this purpose could be something like this:

public class DatabaseManager
{
    private static DatabaseManager _instance;
    private DatabaseManager()
    {
    }

    static DatabaseManager()
    {
        _instance = new DatabaseManager();
    }

    public DatabaseManager Instance
    {
        get { return _instance; }
    }

    private string GetConnectionString()
    {
        return Properties.Settings.Default.MyConnectionString;
    }

    public SqlConnection CreateConnection()
    {
        return new SqlConnection(GetConnectionString());
    }
}

You can extend this class to contain another methods to execute your queries.

I highly recommend you to use an ORM (Object-Relational Mapper) like Entity Framework.

Daphnedaphnis

Answer 2

It can be done with the help of STATIC variable in any class

Biyearly

Answer 3

Whenever you instantiate a new object pass to its constructor a reference to that connection.

Colucci

Answer 4

I've have something similar set up in an old project like so. It's worth noting that you should always be using a new SqlConnection for all your operations, because of connection pooling.

public static class SqlConnectionUtil
{
    public static string DefaultConnectionString { get; private set; }

    static SqlConnectionUtil()
    {
        SqlConnectionUril.DefaultConnectionString = 
                Properties.Settings.Default.TheConnectionString;
    }

    public static SqlConnection Create()
    {
        return new SqlConnection(SqlConnectionUtil.DefaultConnectionString);
    }
}

You would then use it like this.

using (var connection = SqlConnectionUtil.Create())
{
    using (var command = connection.CreateCommand())
    {
        // do things.
    }
}

Winn

this can't be used in static – Contrast

What do you mean? I edited a typo by the way, the constructor shouldn't have been declared as public (I didn't have time to try compile this). – Winn

this.DefaultConnectionString this shouldn't be there – Contrast

Answer 5

You can use this code for get coonection string

      var      defaultConn= new SqlConnection(ConfigurationManager.AppSettings["defaultConn"].ToString());

Lappet