C# and mySQL Connection

naifwonder · March 7th, 2008, 04:31 PM

I have been working on a financial analysis program for some time now, and have run into some what of a snag. Before I delve into the details, I just want to state that I am fairly new at C# so please pardon any noob statements.

The program takes data from a given financial market, processes it (very small pieces of data.. a number such as 1192.75 would be the longest double to come in), then saves the output from the processing to a mySQL database. It does this very rapidly many times a second (usually around 5 to 10 times per second, although sometimes more).

Now here comes the problem. The output which the program saves in the database is something I need to be able to access in real-time. However, the program has about a 1 to 2 minute lag in saving the output to the database. I have identified the part of the program most likely creating the lag and am having difficulty fixing it.

The main class has two parts to it: One method loads when the program first initializes, and the next method runs whenever a new piece of data comes in (usually around 5-10 times per second). The problem is that in the second method, I have to reestablish a connection to the mySQL database every single time, then input the data. Is there anyway that I could establish a connection to the mySQL database in the first method and use that connection in the second method, instead of having to constantly reestablish a connection to the database several times per second? I think that being able to do so would significantly improve the performance of the program, and may help to eliminate the lag.

panda869 · March 7th, 2008, 06:30 PM

Yes you can, because the connection is passed by reference you can open it in the first method.

Code:

void MethodOne()
{
    SqlConnection conn = null;
    try
    {
       conn.Open();
       MethodTwo( conn );
    }
    finally
    {
        if( conn != null && conn.State == ConnectionState.Open() )
        {
           conn.Close();
        }
    }
}

void MethodTwo( SqlConnection conn )
{
   if( conn.State == ConnectionState.Open() )
   {
        // do stuff with connection 
   }
}

One important thing to remember not to keep the connection open any longer than you have to, but this will work.

I hope this answers your question.

panda869 · March 7th, 2008, 08:05 PM

Re-reading your original post, I think I misunderstood your question. I think you want this instead:

Code:

void MethodOne()
{
    SqlConnection conn = new SqlConnection( CONN_STRING );
    conn.Open();       
}

void MethodTwo()
{
   if( this.conn.State == ConnectionState.Open() )
   {
        // do stuff with connection 
   }
}

And depending on when you're done with it, close it.

dparsons · March 7th, 2008, 09:59 PM

Quote:

quote:Originally posted by panda869
Re-reading your original post, I think I misunderstood your question. I think you want this instead:

Code:

void MethodOne()
{
    SqlConnection conn = new SqlConnection( CONN_STRING );
    conn.Open();       
}

void MethodTwo()
{
   if( this.conn.State == ConnectionState.Open() )
   {
        // do stuff with connection 
   }
}

And depending on when you're done with it, close it.

This example will not compile since conn is out of scope for MethodTwo.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

panda869 · March 8th, 2008, 12:59 AM

Quote:

quote:Originally posted by dparsons

Quote:

quote:Originally posted by panda869

Quote:

Re-reading your original post, I think I misunderstood your question. I think you want this instead:

Code:

void MethodOne()
{
    SqlConnection conn = new SqlConnection( CONN_STRING );
    conn.Open();       
}

void MethodTwo()
{
   if( this.conn.State == ConnectionState.Open() )
   {
        // do stuff with connection 
   }
}

And depending on when you're done with it, close it.

This example will not compile since conn is out of scope for MethodTwo.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

Thanks for catching that, and sorry for posting incorrect info. That'll teach me to answer posts when I'm in a hurry.

Code:

SqlConnection instanceConn;

void MethodOne()
{
   instanceConn = new SqlConnection( CONN_STRING );
   instanceConn.Open();       
}

void MethodTwo()
{
   if( this.instanceConn.State == ConnectionState.Open() )
   {
        // do stuff with connection 
   }
}

dparsons · March 8th, 2008, 01:52 PM

No worries. No one is perfect =]
-Doug

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========