Database Connection

[Venkrishna]

Hi Folks,

I have written a class in the C# windows forms application. This class has a SqlConnection Object.

With this class I want to open a connection and this instance of the class (connection) should be available globally for all the forms. Just like using only one connection object through out all the forms in the application.

And I want to close this connection when the MDI form is closed.

Any ideas??

[axion_sa]

Yes - don't. Open and close your database connections as needed, preferably in a separate data layer (have a look at n tier design); .Net uses what's known as "connection pooling" - it will help prevent unnecessary locks and similar undesirable effects of single connections.

However, should you still wish to use a single connection, you could use the singleton pattern:

/*
	In MDI form.
*/

private SqlConnection _sqlConn;

public SqlConnection Connection
{
	get
	{
		if (_sqlConn == null)
			// Create & open connection to database.
			_sqlConn = new SqlConnection("Connection string");
		return _sqlConn;
	}
}

/*
	In child forms.
*/

private void FooDatabaseFunction()
{
	// Create objects etc and reference MDIForm.Connection
}

Ps - I haven't used MDI in .Net as yet, so I'm not sure how you reference the parent MDI form, so MDIForm.Connection probably isn't right.

[szlamany]

Connection pooling is not designed for app's that connect to a database on a local network and stay connected all day long. For those types of applications, having a single connection stay open is my recommended method.

Do you have a database tier to your structure?

[mar_zim]

and before opening your connection. check first its state if it's close or not.

public class xsqlconn
	{
		public static void openconn(SqlConnection cn)
		{
			if(cn.State==ConnectionState.Closed)
			{
				cn.Open();
			}
		}

		public static void closeconn(SqlConnection cn)
		{
			if(cn.State==ConnectionState.Open)
			{
				cn.Close();
			}
		}

[Venkrishna]

Guys,

I have this MDI application with SQL Server as the backend.

From your view points where should I create an instance of this class so that it is accessible by all child forms? Do I have to open the connection in the login form / mdi form once so that it is accessible by all the child forms?

If not how would I otherwise create an instance of a conn object that will be opened once and closed only once and still accessed by all the child forms in my MDI app?

For eg. As in VB6.0 I used to declare a conn object in a module and then the conn is opened in the mdi form / login form and thus the conn object is available to all child forms through out the application and the conn is closed when the user exits the application. I guess this should be possible in .Net.

Please advise.

[fret]

for what i did in my project, i used to open the connection in the presents of an mdi and when i open a child form just past the connection to opened child.

though i'm not sure if it is a good practice.

[Venkrishna]

Fret,

Thanks for the reply. I have found a solution from the help of few forum mates.

[nebulom]

A few variation from what you gathered.

using System;
using System.Data;
using System.Data.SqlClient;

namespace WindowsApplication2
{
	/// <summary>
	/// Summary description for Connection.
	/// </summary>
	public class Connection
	{
		static SqlConnection instance=null;
		private Connection()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public static SqlConnection GetInstance()
		{
			if(instance==null) instance=new SqlConnection();
			return instance;
		}

		public static void Close()
		{
			if(instance.State==ConnectionState.Open) instance.Close();
		}

		public static void Open()
		{
			if(instance.State==ConnectionState.Closed) instance.Open();
		}
	}
}

Can be used like

			Connection.GetInstance().ConnectionString="user id=sa;password=password;initial catalog=northwind;data source=localhost";
			try
			{
				Connection.Open();
			}
			catch(Exception ex)
			{
			}
			finally
			{
				Connection.Close();
			}

User of the Connection class has the option to set it from.