This is a little wierd, but I want to check if connection to my database is already open or not? How do I check that? and if open I want to be able to work with it straightaway without going through all the statements:
sqlconnection conn = new sqlconnection("string ...");
Can this be done? I know the connection string and the connection name too. I want to check if this connection is available first and then proceed.
Have you looked at the documentation for SqlConnection?
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx
I believe the "State" property will tell you what you want.
If you are rather asking more generally how you can use an existing connection from a connection pool, this will be done automatically when you create a new SqlConnection with an identical connection string as an active connection.
If you're just trying to avoid writing redundant code, then put it in a class and reuse it.
Although you are probably just using SQL Server it might be good practice to get familiar with all the core interfaces in System.Data since all data providers implement them. I believe the State property on IDbConnection returns the information you are asking for (IDbConnection)
Also you may want to hide that logic in some kind of centralized method:
public static IDbConnection RetrieveConnection(){
if(DataAccess.Connection.State == ConnectionState.Open) return DataAccess.Connection;
conn.Dispose(); //to be clean, I believe this is safe if it's already disposed
//retrieve configured connection string
//create and open connection
return DataAccess.Connection;
}
So maybe DataAccess is some place you can put and retrieve your connection object, but I would avoid having everyone use it directly. Instead have them go through this method that can ensure the connection is usable. I'm just trying to give you ideas.
Also you may want to take it a step further and use something like NHibernate that will manage connections and all that stuff for you. Although that's not always worth the effort if the project is small.
EDIT: made the code a little more explicit
If you know the connection string then the easiest way of obtaining a new usable sql connection is to create a new instance of the SqlConnection class:
using (SqlConnection conn = new SqlConnection("MyConnectionString"))
{
conn.Open();
// Use the connection
}
The .Net framework uses connection pooling and so there is no need to worry about opening efficiency & multiple connections - the above code will either re-use an available existing connection, or create a new one as required.
If you want to save yourself some typing then you might find it useful to create yourself a small helper method or property:
class SqlHelper
{
public static SqlConnection GetConn()
{
SqlConnection returnValue = new SqlConnection("MyConnectionString");
returnValue.Open();
return returnValue;
}
}
Usage:
using (SqlConnection conn = SqlHelper.GetConn())
{
// Use the connection
}
The Façade Design Pattern should help you here. Here's an example.
An "intelligent" façade knows what method needs to connect where, etc. The façade opens the connection and pass it to an underlying piece of code, generally contained in a factory class or something alike.
public class DoSomethingFacade {
private static readonly DoSomethingFactory _doSomethingFactory = new DoSomethingFactory();
public static IList<T> GetList<T>() {
using(IDbConnection connection = OpenConnection("string..."))
return _doSomethingFactory.GetList<T>(connection);
}
public static IDbConnection OpenConnection(string connectionString) {
IDbConnection openedConnection = new SqlConnection(connectionString);
openedConnection.Open();
return openedConnection;
}
}
internal class DoSomethingFactory {
internal DoSomethingFactory() { }
internal IList<T> GetList<T>(IDbConnection connection) {
IList<T> results = new List<T>();
// use connection here without caring about it,
// as it should be provided as an opened available connection.
return results;
}
}