Managing and Closing Dynamically Generated SQL Connections in .net

Question

I have a C # form application that dynamically connects to databases, where each user can connect to different databases.

The current implementation is as follows:

Connection repository containing a dynamically populated list of connections (per user).

When a user initiates a request that requires a connection to the database, the corresponding connection is viewed from the connection repository, opened, and then used in the user's request.

Sample code from the connection repository

public class RepoItem
{
    public string databasename;
    public SqlConnection sqlcnn;
}

public class ConnectionRepository
{
    private List<RepoItem> connectionrepositroylist;

    public SqlConnection getConnection(String dbname)
    {
        SqlConnection cnn = (from n in connectionrepositroylist
                             where n.databasename == dbname
                             select n.sqlcnn).Single;

        cnn.Open();
        return cnn;
    }
}

Sorry for any code errors, I just improvised a small version of the implementation for a demo purpose.

, .

:

• ?

• , ?

, Executing ConnectionState Enumeration . .

, getConnection ConnectionRepository .

PS: , , .

Answer 1

SQLConnection . , Action<SqlConnection>, using

, , , - , :

public class RepoItem
{
    public string databasename;
    public SqlConnection sqlcnn;
}

public class DatabaseConnector
{
    private List<RepoItem> connectionrepositroylist;

    private SqlConnection GetConnection(String dbname)
    {
        return (from n in connectionrepositroylist
                where n.databasename == dbname
                select n.sqlcnn).SingleOrDefault();
    }

    public void Execute(String dbname, Action<SqlConnection> action)
    {
        using (var cnn = GetConnection(dbname))
        {
            if (cnn != null) // in case dbname is not in the list...
            {
                cnn.Open();
                action(cnn);
            }
        }

    }
}

, sql, - :

public void ExecuteReaderExample(string dbName, string sql)
{
    Execute("dbName",
    connection =>
    {
        using (var cmd = new SqlCommand(sql, connection))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // do stuff with data form the database
                }
            }
        }
    });
}

, SqlCommand , . , , . , , git hub .
ado.net, , , . .

P.S. :

?

, .

, ?

, .

, , , .

Update

Yahfoufi, , SQLConnection, , . - , SQLConnection RepoItem, :

public class RepoItem
{
    public string DatabaseName {get; set;}
    public string ConnectionString {get; set;}
}

GetConnection :

    private SqlConnection GetConnection(String dbname)
    {
        return new SqlConnection(from n in connectionrepositroylist
                where n.databasename == dbname
                select n.sqlcnn).SingleOrDefault());
    }

Execute SQLConnection, - .

. , RepoItem , List<RepoItem> Dictionary<string, string>, , - . , , GetConnection :

private Dictionary<string, string> connectionrepositroylist;

    private string GetConnectionString(String dbname)
    {
        return connectionrepositroylist.ContainsKey(dbname) ? connectionrepositroylist[dbname] : "";
    }

, DatabaseConnector :

public class DatabaseConnector
{
    private Dictionary<string, string> connectionrepositroylist;

    private string GetConnectionString(String dbname)
    {
        return connectionrepositroylist.ContainsKey(dbname) ? connectionrepositroylist[dbname] : "";
    }

    public void Execute(String dbname, Action<SqlConnection> action)
    {
        var connectionString = GetConnectionString(dbname);
        if(!string.IsNullOrEmpty(connectionString))
        {    
            using (var cnn = new SqlConnection(connectionString))
            {
                cnn.Open();
                action(cnn);
            }
        }
    }

    // Of course, You will need a way to populate your dictionary - 
    // I suggest having a couple of methods like this to add, update and remove items.
    public bool AddOrUpdateDataBaseName(string dbname, string connectionString)
    {
        if(connectionrepositroylist.ContainsKey(dbname))
        {
            connectionrepositroylist[dbname] = connectionString;
        }
        else
        {
            connectionrepositroylist.Add(dbname, connectionString);
        }
    }
}

Answer 2

, ADO.Net , . , .

, :

?

, , . Microsoft , () . - . , , , .

, ?

Dispose Finalize. , . , ConnectionRepository .

,

- , . , ,

RepoItem , , ADO.Net .

public static class ConnectionRepository
{
    private static readonly Dictionary<string, string> Connections = new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase);

    public static bool Contains(string key)
    {
        return Connections.ContainsKey(key);
    }

    public static void Add(string key, string connectionString)
    {
        Connections.Add(key, connectionString);
    }

    public static SqlConnection Get(string key)
    {
        var con = new SqlConnection(Connections[key]);
        con.Open();
        return con;
    }
}

:

public static void foo()
{
    using (var con = ConnectionRepository.Get("MyConnection"))
    using (var cmd = new SqlCommand("SELECT * FROM MyTable", con))
    {
        var dr = cmd.ExecuteReader();
        //...
    }
}

, , using() Dispose() .

Answer 3

@tinudu, SqlConnection - . . SQL Server.

SqlConnection using, # .

( , , , ) - . , .

SQL (, , proc), - .

Answer 4

, , .

public class RepoItem
{
    public string databasename;
    public SqlConnection sqlcnn;
}

public class ConnectionRepository
{
    private List<RepoItem> connectionrepositroylist;

    public SqlConnection getConnection(String dbname)
    {
        SqlConnection cnn = (from n in connectionrepositroylist
                             where n.databasename == dbname
                             select n.sqlcnn).Single;
        if (cnn!= null && cnn.State == cnn.Closed) // Impelement other checks as well
        {
         cnn.Open();
        }
         return cnn;
    }
}

CloseConnections i.e

Application.ApplicationExit

 public void CloseConnections()
    {
        foreach (var connection in connectionrepositroylist)
        {
            try
            {
                if (connection.State == System.Data.ConnectionState.Open) // check other conditions
                {
                    connection.Close();
                }
            }
            catch (Exception)
            {
                //logging or special handling
            }
        }

    }

, - ,   

• . .

Answer 5

It is best to close the sql connection manually, as it can free the connection, which can be used for other processes. Also note that you must open the connection as much as you can and close it as soon as possible.