Opening database connection once outside of method but using in several different methods

Question

Hi First of all I've only been programming in C# for a week or two and I am teaching myself so not completely confident with terminology or OOP. I have a method which I am calling hundreds of times from various other methods. Inside this I am opening a database connection and running a query and returning a result. Now I know that opening a new database connection hundreds of times is not very good - so I am trying to only open the database connection once on opening the program and closing the program. I have pasted my method at the bottom of this post. Now I was thinking I could open the database connection beside intizialize component and close it on exit - but the problem with this is I would then have to pass two extra variables to the method every single place I call it (which is hundreds of times) and also pass these variables to the dozen or so other methods I have which are calling the database method somewhere inside - which all becomes very messy and I would imagine is unnecessary if I could somehow just make the two variables public and accessible from all method. So my question is there is any way of doing this - or at least a more simple way of trying to achieve this? thanks. P.S sorry for the bad formatting - this thing makes you type in your own html!? -------------------------------------- My database method is as follows private string CheckIfExists(String strSql) { string MyConString = SERVER=***; + DATABASE=***; + UID=***; + PASSWORD=***;; MySqlConnection connection = new MySqlConnection(MyConString); MySqlCommand command = connection.CreateCommand(); MySqlDataReader Reader; command.CommandText = strSql; connection.Open(); Reader = command.ExecuteReader(); //return Reader.Read(); string xreturn = null; while (Reader.Read()) { xreturn = Reader[id].ToString(); //MessageBox.Show(xreturn); } connection.Close(); return xreturn; }

theLizard · Answer

Sam what are you talking about!!! The concept is simple Open your connection Do your work Close your connection. The application has FINISHED it's processing as soon as the WORK IS DONE  in database (sql at least) work that is the statement has been executed there is NO MORE processing on the connection to be done we don't need it open any more. Sorry if I sound harsh but I get the impression that you have not done a lot of database work!.

Sam Hobbs · Answer

Except you are suggesting that the connecion be closed before the program has completed it's processing which means that if the number of connections is relevant then he program would be caught during processing before it completes processing. It would be better if the new appliction does not start processing until the subject application has finished.

theLizard · Answer

Sam If you have not already gone to the link supplied by Kumar please do so http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx The above link has this Caution.. We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection using statement in C# or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information see using Statement (C# Reference) or How to: Dispose of a System Resource for Visual Basic. You can quite easily exhaust the connection pool if you do not close the connection you can't have the same data reader reading two or three tables at the same time you would need two or three data readers which would need their own connection that is why you may sometimes get an reader is already associated with this connection error or reader is already open. Most back end databases like Sql Server MySql will manage their own data pool and reuse connections with the same configuration so closing the connection does not remove the availability of the connection in the pool this only occurs after an elapsed amount of time thus releasing and making available the pool connection to another user. I have been writing database systems for a VERY long time and unless you have a specific need to manage your own connections (not that easy to do) the best way of doing things is to open and close you end up with a LOT less problems. Also there are only x number of connections available (yes the size can be increased or decreased) so in a system say like banking where you can have hundreds thousands of queries to a database keeping a connection open would lock out everyone after the pool has been exhausted typically about 500 connections and NO one would be able to access the database until a pool connection becomes available.

Sam Hobbs · Answer

theLizard: why is it to best to open and close database connections after each data access instead of using one connection for database operations? In this case especially there is no user interaction during the processing so I don't understand the advantage of closing and re-opening the connection. Is there someplace where that is described? As far as I know there is no reason to close and re-open the connection for this and I want people to get the best advice possible. It seems to me that closing the connection makes the program vulnerable to getting locked out from accessing the database if some other program uses it and that could lock the program out for a long time potentially for days.

Kumar AG · Answer

Please have a glance on connection pooling http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Michael Reid · Answer

Thank's guys I moved the connection string into the class as a static (which I didn't know you could do) and then open and close the connection at the start and end of a method in practical terms I can see no real increase in performance which is a little bit unexpected. Just incase you were wondering basically I was retrieving around 12 csv files from a remote source and looping through each row in each file splitting the data up and inserting into several database tables - I would need to return the id of the first table insert to give each corresponding table insert a related id.

theLizard · Answer

Hello Michael It is best to Open then close your database connection after you have obtained the data you are after however there are certain times that you want to keep connections so that the connection pool is not overloaded stopping others from connecting to the database. I don't know your reasons for opening hundreds of times and how many users are connecting to the same database so the question to keep or discard the connection is up in the air. I have written components lizardControls (these are just for MS Sql) but am also in the process of finishing a MySql (using MySql Connector) OleDb and Odbc components which when placed on a form allows you to get results with simple commands like this lizardMySqlConnection1.ConnectionsString = @Persist Security Info=False;database=odbcClass;server=localhost;Connect Timeout=30;user id=uid; pwd=password;; lizardMySqlConnection1.FillCombo(comboBox1 SELECT * FROM myTable 0); The Connection String can be set once in the forms constructor or form load event then the only thing you need to do to fill a combo box (you can also fill trees and list boxes as well as any other component) with a single line of code. You wont have to worry about opening or closing although the component does allow you to control that as well by you writing extra lines of code to what you want. Implementation of the control is simple in the form class declare a lizardMySqlConnection con in constructor con = new lizardMySqlConnection1() the component also embodies a Crypto class that allows you to read encrypted connection strings or set encrypted connection strings which you can get from an app.config or using the Registry Property get an encrypted connection string from the Registry in the path you set. The component also gives you access to its Command data adapter etc. So using lizardMySqlConnection1 The sample code you gave in your question is replaced with something like xreturn = lizardMySqlConnection1.Exists( SELECT * FROM myTable where id =3 id 0); At the end of the day you could create your own class for the connection that does much the same as what I have done and do things like if(con != null) { con.terminate(); } this function would then check to see if cmd or reader is not null and close or dispose of them.

Sam Hobbs · Answer

Move the connection object to the form's class; in other words make the connection object a member of the form. Then you can use it from any member fuction without passing it to the functions. I would use the form's Load event to open the connection and the form's Closing event to close the connection but I am not sure where is the best place for those. You can move other items to the form's class that need to be used from many member functions. That should work well if you have just one form in your application. If you have more forms then you can put the connection in the Application class and access it from any form. I won't get into that here.

Kirtan Patel · Answer

Friend that is true that opening and closing connection will affect the performance of application . but if you keep open the connection through out the program run then if program close the connection accidentally then it may cause error in application at many places as connection is closed and now application can not reopen it because we don't have written code for it :) if my answer helped you then check Do you like this answer check box please :)