Reusability of objects in MySQL Connector/NET

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={010} Comp={12} DT={2} ({38} -->
{48}) {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