Db Connection

Question

Hi
i'm looking for an example -> where the connection to a db is definde one time and then you can need it for all formulars
my question is
--------------------------------------------------------------------
i make a connection for example:
static String connString = Properties.Settings.Default.auftragsverwaltungConnectionString;
MySqlConnection myconn = new MySqlConnection(connString);
--------------------------------------------------------------------

and now i can use for all formulars
myconn.open
Code ...

myconn.close

thanks for help
joshua

Amit Choudhary · Answer

Dhaval , Its inside the System namespace itself but in .Net 4.0 version

Pravin More · Answer

Hi Joshua, Try this solution create 1 method which will return you opened connection like this..... namespace DBLayer.Util { public class Dbconnection { public SqlConnection getcon() { SqlConnection dbConn = null; try { dbConn = new SqlConnection(Properties.Settings.Default.auftragsverwaltungConnectionString.ToString()); dbConn.Open() } catch (Exception e1) { } return dbConn; } } so when you want to open connection just call this method like below....n close connection in every context ..i mean in every method Dbconnection dbc=new Dbconnection(); dbc.getCon(); //this will open connection // your code here con.Close(); i hope this is up to your need. Thanx, Pravin.

Dhaval Patel · Answer

Dear Amit in Singleton pattern which namespace import for The type or namespace name 'Lazy' does not exist in the namespace system .

Joshua · Answer

Hallo Thanks for the ideas. I will try it. Joshua

Sam Hobbs · Answer

I think your problem is easily solved if you understand classes and how they work and such. You can create a class to do things for the database and then use that class in many places in your program. I should say that you can create one instance of the class and then use the one instance in many places. Also, you can create a data source and use the data source. A data source would have just one set of data (columns or fields) so I am not sure if that would help you but if it would then the data source will do most of the details for you. Look for articles in this web site about the Entity framework. You probably can benefit from muli-tier designs. You can create a Data Access Layer (DAL) separate from the rest of your application. Look for articles about things like that.

Amit Choudhary · Answer

Code is little messed up here because of the editor I hope you'll manage to read this :)

Amit Choudhary · Answer

Hi Friend, Let me show something I'm came with to solve your problem first of all create a singleton pattern so that you can use it to create class that can have only one instance and that instance would be available everytime you use it.. Singleton: for .Net 3.5 version: public class Singleton where T : new () { private LazySingleton() { } public static T Instance { get { return SingletonCreator ._instance.Value; } } class SingletonCreator { static SingletonCreator() { } internal static readonly Lazy _instance = new Lazy (() => new T()); } } For .Net 4.0: public class Singleton where T : new () { private static readonly Lazy _instance = new Lazy (() => new T()); public static T Instance { get { return _instance.Value; } } } Now you will use it thorughout your application if you want to create a singleton object for any class. Lets see how your DB class will look like that'll manage the connection public class DAL { static SqlConnection connection; public DAL() { connection = new SqlConnection (); connection.ConnectionString = System.Configuration. ConfigurationSettings .AppSettings[ "myConnectionString" ]; } public DataTable GetDataTableByAdapter( string query) { connection.Open(); DataTable dt = new DataTable (); new SqlDataAdapter (query, connection).Fill(dt); connection.Close(); return dt; } } In this class we have a method and that method will open the connection use it and will close it before returning the final values. How to use both of the above classes to utilize the single object multiple times. class Program { static void Main( string [] args) { DAL dal = Singleton DAL >.Instance; string query = "Select * from Production.Product" ; DataTable dt = dal.GetDataTableByAdapter(query) ; System. Console .Read(); } } I hope this is something you wanna know about the connection management. If i'm not wrong.. and surprisingly i've found some great thing while i was creating this sample for you. Will soon publish in the article section and on my blog ;) Happy Coding!!

Joshua · Answer

Hello Javeed thank you for the answer yes this is wat i meen. If you have some formulars, is this not the normal way to solve the problem of connection with database ? joshua

Javeed M Shaikh · Answer

you mean you want to create a method like this: Class DB { MySqlConnection myconn; static String connString = Properties.Settings.Default.auftragsverwaltungConnectionString; public DB() { myconn = new MySqlConnection(connString); } public OpenConnection() { if (myconn is closed or myconn is not null) { myconn.Open(); } } public CloseConnection() { if (myconn is open or myconn is not null) { myconn.Close(); myconn.dispose(); } } } this is not an actual code and my have errors, but just to give you an idea i have written this. Let us know.

Joshua · Answer

thanks for you answer when i create a formular i give the code static String connString = Properties.Settings.Default.auftragsverwaltungConnectionString; MySqlConnection myconn = new MySqlConnection(connString) in every formular. my question is how can i create the code once an need for all formulars ? So i only give the command myconn.open Code .. mycone.close in the particulair forms thanks joshua

Javeed M Shaikh · Answer

If you mean that you want to open the connection once and use for DB activity then what you are doing is correct, but keeping the connection open for such a long time is not advisable. Please elaborate on your question more so we can try to give the solution.