Reusability of objects in MySQL Connector/NET

microsoft.public.dotnet.framework.adonet

12 Feb 2006 4:17 PM

Mochuelo

Hi,

I'm using:
- Visual Studio 2005.
- MySQL Server v4.1.9.
- MySQL Connector/NET v1.0.7 (and I have referenced the dll for .NET
2.0).

I wrote a class "C_MySQL" that attempts to encapsulate the low level
details of doing SELECT-type and INSERT-type queries against a local
MySQL server. The listing of this class is at the end of this post.

You can disregard class "C_Global" and object "glb" in the listing
below. It has to do with traces, which work perfectly.

When calling the constructor of "C_MySQL", I specify to which database
I want to link the instance, and how many "channels" (simultaneous
independent connections) I am going to need. For instance, if, in my
main program, I have a SELECT query, and a while loop that goes
through each of the rows returned by the SELECT query, and on each
execution of the loop I need to do an UPDATE that has to be completely
independent on the outer SELECT query, then I create the instance (of
C_MySQL) with two channels, and use channel 0 for the outer SELECT
query, and channel 1 for the inner UPDATE. If I foresee that I will
have, at some place, three levels of nesting (including the outermost
one), then I create three channels.

For two channels:
----------------------------------------
The instantiation could be:

sql                             =new C_MySQL(glb,"db1",2);
----------------------------------------
And the main program could be:

if (!sql.select(0,"SELECT * FROM table1 ORDER BY ID ASC")) return;
while (sql.read(0))
{
    s_dt                =sql.res[0].GetString(2);
    lintime_old         =sql.res[0].GetInt32(3);
    lintime_new         =tm_new.lintime_s2i(s_dt);
    data_ID             =sql.res[0].GetInt32(0);
    glb.trc.DispI(0x0001,"ID={0:D10} Comp={1:D2} DT={2} ({3:D8} -->
{4:D8}) {5:F2}",
                    data_ID,
                    sql.res[0].GetInt32(1),
                    s_dt,
                    lintime_old,
                    lintime_new,
                    sql.res[0].GetDouble(4));
    if (!sql.insert(1,String.Format("UPDATE table2 SET lintime={0}
WHERE (ID={1})",lintime_new,data_ID))) return;
} // while
----------------------------------------

I hope the use is more or less clear.

Problems I have: The nested UPDATE works correctly only some times. If
I limit the outer SELECT with a "LIMIT 100" clause, the UPDATE works
ok for each row. But If I do not limit the outer SELECT, the nested
UPDATE hangs at the very first row. The line
"cmd[ch].ExecuteNonQuery();" in "public bool insert(int ch,string
txt)" hangs, and _without_ even throwing an exception.

All this sounds to me as if I am doing an incorrect use of the "new"
statement. Maybe I'm reusing objects too much. Maybe I should use the
"new" statement more times. I use the "new" statement in the
constructor, and never again. Notice that, the "C_MySQL" constructor
creates (in this case) two "MySqlConnection" instances, two
"MySqlCommand" instances, two "MySqlDataReader" instances, and don't
use "new" again. Every time I call "sql.insert()" or "sql.select()" I
am reusing the same two connections, the same two commands, and the
same two data readers, and maybe this is causing my problems.

I can give many more details, but maybe this is enough (or even too
much) for now. Does anyone know what I am doing wrong?

Thank you very much in advance,
Mochuelo.

=========================================================================
=========================================================================
using System;
using MySql.Data.MySqlClient;
using MySql.Data.Types;
using N_Global;

namespace N_MySQL
{
    //
=========================================================================
    public class C_MySQL
    {
        public C_Global                    glb;
        private MySqlConnection[]           con;
        private MySqlCommand[]              cmd;
        public MySqlDataReader[]           res;
        private int                         numch;
        //
......................................................................
        public C_MySQL(C_Global glb1,string db,int numch_)
        {
            glb                             =glb1;
            numch                           =numch_;
            con                             =new
MySqlConnection[numch];
            cmd                             =new MySqlCommand[numch];
            res                             =new
MySqlDataReader[numch];

            for (int ch=0;ch<numch;ch++)
            {
                try
                {
                    con[ch]                 =new
MySqlConnection("Database="+db.Trim()+";Server=localhost;Uid=exampleusr;Password=examplepwd");
                    con[ch].Open();
                    cmd[ch]                 =new MySqlCommand();
                    cmd[ch].Connection      =con[ch];
                    glb.trc.DispI(0x0100,"[constructor][{0}]
ServerThread={1}",ch,con[ch].ServerThread);
                }
                catch (Exception e)
                {
                    glb.trc.DispI(0x0100,"[constructor] Exception :
{0}",e);
                }
            } // for ch
        } // C_MySQL
        //
......................................................................
        public void Close()
        {
            for (int ch=0;ch<numch;ch++)
            {
                cmd[ch].Dispose();
                con[ch].Close();
                con[ch].Dispose();
            } // for ch
        } // Close
        //
......................................................................
        public bool insert(int ch,string txt)
        {
            cmd[ch].Parameters.Clear();
            cmd[ch].CommandText             =txt;
            glb.trc.DispI(0x0100,"[insert][{0}] {1}",ch,txt);
            if (res[ch]!=null)
            {
                res[ch].Close();
            } // if
            try
            {
                cmd[ch].ExecuteNonQuery();
            } // try
            catch (Exception e)
            {
                glb.trc.DispI(0x0100,"[insert] Exception : {0}",e);
                return(false);
            } // catch
            return(true);
        } // insert
        //
......................................................................
        public bool select(int ch,string txt)
        {
            cmd[ch].Parameters.Clear();
            cmd[ch].CommandText             =txt;
            glb.trc.DispI(0x0100,"[select][{0}] {1}",ch,txt);
            if (res[ch]!=null)
            {
                res[ch].Close();
            } // if
            try
            {
                res[ch]
=cmd[ch].ExecuteReader();
            } // try
            catch (Exception e)
            {
                glb.trc.DispI(0x0100,"[select] Exception : {0}",e);
                res[ch]=null;
                return(false);
            } // catch
            return(true);
        } // select
        //
......................................................................
        public bool read(int ch)
        {
            glb.trc.DispI(0x0100,"[read][{0}]",ch);
            if (res[ch]!=null)
            {
                return(res[ch].Read());
            } // if
            return(false);
        } // read
        //
......................................................................
    } // C_MySQL
    //
=========================================================================
} // N_MySQL