Connection, parameter management

[VBAHole22]

I have a long block of code that makes about 10 or 12 different parameterized queries against SQL server. Some are readers others are NonQuery. My question is what is the best way to manage the connection(s) to optimize performance.

One way I had was opening and closing the connection after each execute, that required a lot more code.
So I moved to a single connection and a single command and just cleared the parameters out and added them back each and every time. That seemed to work okay but I'm having issues with timeouts accessing the db on what seem like simple queries after about 5 queries into the code. Could it be that I need to close the connection at least every now and then?

Any general suggestions on how to maintain this kind of scenario best?

Thanks

 

[brd24gor]

If it is a single connection, I would put your connection into a separate database library and manage it from there. obislavu gave me a class in a previous post that has worked like a charm for working with my database:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.OleDb;

namespace SCBCTrainingCenter
{
	/// <summary>
	/// Holds and maintaines connection to testing database
	/// Notes: Database path is hard-coded.  If you need to be able to dynamically associate the path,
	///			create a constructor that takes the database path and assigns it to the connection string
	/// </summary>
	public class DBLibrary
	{
		private System.Data.OleDb.OleDbConnection m_SQLConnection;
		private string m_ConnectionString = @"<your connection string>;
   
		public DBLibrary()
		{
		}
		public bool Disconnect()
			//Closes the connection to the database
		{
			try
			{
				m_SQLConnection.Close();
				m_SQLConnection=null;
				return(true);
			}
			catch(Exception vException)
			{
				MessageBox.Show("Error: Disconnect() - Unable to disconnect from database. Please verify .../n" + vException.ToString(),"Unable to disconnect from Database",MessageBoxButtons.OK,MessageBoxIcon.Stop  );
				return(false);
			}
		}
		public bool Connect()
			//Opens the connection to the database
		{
			try
			{
				m_SQLConnection= new OleDbConnection(m_ConnectionString);
				return(true);
			}
			catch(Exception vException)
			{
				MessageBox.Show("Error: Connect() - Unable to connect to database. Please verify server, database, user and password information and try again.","Unable to connect to Database",MessageBoxButtons.OK,MessageBoxIcon.Stop  );
				return(false);
			}
		}
		public System.Data.OleDb.OleDbConnection SQLConnection
			//Holds the connection that can be accessed from outside classes
		{
			get{return m_SQLConnection;}
			set{m_SQLConnection=value;}
		}
	}
}

This way you only have to initialize the connection once, and it can be passed around to other objects. With this, your queries are about 3-4 lines of code. Here is an example using OLEDB:

//Initialize DBLibrary, done only once
DBLibrary aLib = new DBLibrary();
aLib.Connect();

string sql = "<your sql statement>";
OleDbCommand myCommand = new OleDbCommand(sql, aLib.SQLConnection);
OleDbDataReader myReader = myCommand.ExecuteReader();
...
myReader.Close();

myCommand.CommandText = <new sql statement>
myCommand.ExecuteNonQuery();

The only thing you need to check for is that the connection is open anytime you use it. Something like:

			//Check database connection.  If closed, open it
			if (aLib.SQLConnection.State==System.Data.ConnectionState.Closed)
				aLib.SQLConnection.Open();

Hope this helps,
--Brad

"Life is too important to be taken seriously" --Albert Einstein

 

[VBAHole22]

That is precisely what I am doing and you are right it clears up the clutter alot and makes the code easier to reader. My concern is primarily with commands, readers and parameters. How best to manage those?

 

[brd24gor]

Most of my experience is with Access, so I'm not sure I can be of much help for SQL Server. Are you doing any work with the returned results from a query before executing the next one? If so, you may want to temporarily store all of your query results and do all of the work after they are done running. Anyone out there with more SQL Server experience have any ideas?

--Brad

"Life is too important to be taken seriously" --Albert Einstein

 

[vixen925]

Is this the proper format for the connection string?
Could you please post an example of your string?

private string m_ConnectionString = "Data Source=SERVER1;Initial Catalog=Database1;User Id=testuser;Password=testpassword";

 

[brd24gor]

There is a little shortcut you can do to determine what your connection string will be. Create a file on your Desktop with a .udl file extension. If you double-click on it, you will get a window that will help you setup your connection string.

1. Provider tab - Pick the type of database you will be connecting to. If you are using an SQL server, you will probably want to pick 'Microsoft OLE DB Provider for SQL Server.

2. Connection tab - Enter your database server, login type, and select the database on your server you are going to use.

Click OK. Now, right-click on the file, then Open WIth, then Notepad (you can also open it from within Notepad). Viola, your connection string. Copy and paste that sucker into your code and you should be set.

Note: You can do the same thing from within Visual Studio. In Form Design view, go to the Toolbox, click on the Data header, and drag an SQLConnection onto your form. Go to the SQLConnection properties and click on the arrow by Connection. You will get the same window as described above, without the copying-and-pasting.

"Life is too important to be taken seriously" --Albert Einstein

 

[vixen925]

Awesome, thats working - new issue: I initialize the connection here, when the form loads

public void Form1_Load(object sender, System.EventArgs e)
{
//Initialize DBLibrary, done only once
DBLibrary aLib = new DBLibrary();
aLib.Connect();
if (aLib.SQLConnection.State==System.Data.ConnectionState.Closed)
aLib.SQLConnection.Open();
...

But then I seem to have to do it again here, when I make another call to my connection... when I don't include it inside the btnNewClick event, I get:
ERROR:
"The type or namespace name 'aLib' could not be found (are you missing a using directive or an assembly reference?)
"


private void btnNew_Click(object sender, System.EventArgs e)
{
OleDbCommand cmdLoad3 = new OleDbCommand ("nicknewclicked2_sp", aLib.SQLConnection);
cmdLoad3.CommandType = CommandType.StoredProcedure;
cmdLoad3.ExecuteNonQuery(); txtSettleCtrlNum.DataBindings.Add("Text",cmdLoad3,"@masked");

 

[brd24gor]

That's because you initialized the aLib variable in the Form1_Load event and it goes out of scope when the code in that event has finished executing. What you need to do is declare a private global variable called aLib and then set it up in the Form1_Load event like you are doing now.

using System;
using System.Windows.Forms;
using System.Data.OleDb;

namespace MyNamespace
{
    public class Form1
    {
        private DBLibrary aLib;

        public Form1
        {
            //Whatever you are doing in your constructor
        }

        public void Form1_Load(object sender, System.EventArgs e)
        {
            //Initialize DBLibrary, done only once
            aLib = new DBLibrary();
            aLib.Connect();
            if (aLib.SQLConnection.State==System.Data.ConnectionState.Closed)
            aLib.SQLConnection.Open();
            ...
        }

You could also setup the library in your constructor. Either way, aLib can now be seen by your entire class.

Hope this helps,
--Brad

"Life is too important to be taken seriously" --Albert Einstein

 

[VBAHole22]

But getting back to the original post. How do you manage consecutive reader requests, parameters, etc.

 

[brd24gor]

I always just use the same reader and command and change the .CommandText and other parameters on those. I have yet to run into a timeout, but I have never done more than three in a row.

"Life is too important to be taken seriously" --Albert Einstein