how to give common Database connection for different forms in an application.

[Debst]

I am new to programming this is my first application.Please help me .

I use this code for connecting to database.


Public Sub setConnection1(ByVal ConnectionStr As String, Optional ByVal Provider As String = "System.Data.Odbc")

' Allows for changeing database 'command' provided type: SqlClient, Odbc, Ole
Console.WriteLine(ConnectionStr)
Dim DBFactory As Common.DbProviderFactory = Common.DbProviderFactories.GetFactory(Provider)
Cmd = DBFactory.CreateCommand
Cmd.Connection = DBFactory.CreateConnection()
Cmd.Connection.ConnectionString = ConnectionStr
End Sub
i use this statment in Login form.

setConnection("ODBC;DATABASE=xxx;UID='" xxxx "';PWD='" xxx& "';DSN=xxx")

I have 6 forms in my application so it connects 6 times to database . how can i make a common connection. Please help me.

[nmadd]

Hi there,
Check this out:
http://msdn2.microsoft.com/en-us/lib...94(VS.80).aspx

Good luck.

[vijy]

U can write the function in a class/ module and can access in any form.
This also reduce the time complexity in connecting the database

[nebulom]

I am comfortable with my ConnectionSingleton where the implementation is just

class ConnectionSingleton {
static IDbConnection connection;
public static IDbConnection Connection {
get { return connection; }
set { connection = value; }
}

and I set it like ConnectionSingleton.Connection = new MySqlConnection("someConnectionString"); and access it like

IDbCommand cmd = ConnectionSingleton.Connection.CreateCommand();
cmd.CommandText = "someSQLStatment";
cmd.ExecuteNonQuery() or IDataReader reader = cmd.ExecuteReader();

But of course, we have our own way of solving things. Mine is just one.

[RaviIntegra]

You can declare your connection function in a seperate module you can use it every where in your project.

Public con As New OleDb.OleDbConnection
Public Function MakeConnection() As Boolean
        Try
            If con.State = ConnectionState.Open Then con.Close()
            con = New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Persist Security Info=False;network=dbmssocn;User ID=xxx;pwd=xxx;Initial Catalog=WMS;Data Source=xxxx)
            con.Open()
            MakeConnection = True
        Catch ex As Exception
            MakeConnection = False
            'MessageBox.Show(ex.Message)
        End Try
    End Function

Type this function in your module and you can make use of this any where in your project

[Debst]

Hi RaviIntegra


Can i use your code in Login form and call in each module. how does it work and can you explain in detail for me. Thank you very much

You can declare your connection function in a seperate module you can use it every where in your project.

Public con As New OleDb.OleDbConnection
Public Function MakeConnection() As Boolean
        Try
            If con.State = ConnectionState.Open Then con.Close()
            con = New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Persist Security Info=False;network=dbmssocn;User ID=xxx;pwd=xxx;Initial Catalog=WMS;Data Source=xxxx)
            con.Open()
            MakeConnection = True
        Catch ex As Exception
            MakeConnection = False
            'MessageBox.Show(ex.Message)
        End Try
    End Function

Type this function in your module and you can make use of this any where in your project

[jmcilhinney]

I am comfortable with my ConnectionSingleton where the implementation is just

class ConnectionSingleton {
static IDbConnection connection;
public static IDbConnection Connection {
get { return connection; }
set { connection = value; }
}

and I set it like ConnectionSingleton.Connection = new MySqlConnection("someConnectionString"); and access it like

IDbCommand cmd = ConnectionSingleton.Connection.CreateCommand();
cmd.CommandText = "someSQLStatment";
cmd.ExecuteNonQuery() or IDataReader reader = cmd.ExecuteReader();

But of course, we have our own way of solving things. Mine is just one.

I'd just like to point out that that class is NOT a singleton. The Singleton pattern dictates that you be allowed to create one and only one instance of the class. You're not creating any instances of that class, nor would there be any point because it has no instance members. There's nothing inherent in the class to stop you creating as many instances as you like though. A Singleton has instance members and a static property that returns the one and only instance of the class. That also requires one private constructor.

[RaviIntegra]

If MakeConnection() = False Then MessageBox.Show("Error while making connction") : Exit Sub

use this code to check whether ths connection is established or not.

[nebulom]

Ok, sorry. I forgot the private constructor. It's not a fully implementation of a Singleton as we are not really doing anything with the ConnectionSingleton but to the IDbConnection instead. But generally a Singleton is something which exist alone in some way. So basically it's a singleton. You can of course name it the way you want. I've just seen implementation of the WorkbenchSingleton from the #D to be this way so I'm influenced with the design.

If any way I'm doing it not right, I'm sorry but as I've said I'm comfortable with it. As a connection exists only once in most common applications. Although we can have a manager to handle the current connection if having a multiple DbConnections.

Thanks for pointing out btw, JM.

[jmcilhinney]

Ok, sorry. I forgot the private constructor. It's not a fully implementation of a Singleton as we are not really doing anything with the ConnectionSingleton but to the IDbConnection instead. But generally a Singleton is something which exist alone in some way. So basically it's a singleton. You can of course name it the way you want. I've just seen implementation of the WorkbenchSingleton from the #D to be this way so I'm influenced with the design.

If any way I'm doing it not right, I'm sorry but as I've said I'm comfortable with it. As a connection exists only once in most common applications. Although we can have a manager to handle the current connection if having a multiple DbConnections.

Thanks for pointing out btw, JM.

There's nothing wrong with your class, but it is in no way a Singleton. It serves its purpose exactly as it should, so I'm not suggesting that you change it at all. I'm merely pointing out that if you consider that to be a Singleton then you misunderstand what a Singleton is, which could be an issue in other circumstances.

[nebulom]

Ok, got your point. Thanks.