Re: connections from a data access class never get closed

[Claus Helth]

Hi

I'm new to the "MySql.Data.MySqlClient", but are haveing some thoughts about this subject.

I best like to use the MySqlDataReader, because I dont have to declare how my talbe looks like, or which fields I have to use when I retrieve the data from Table, like with the dataset.

I'm not using the MySqlDataReader with a asp.net control (datagrid etc), so I can't close the connection in the same call.

But I have a SqlBaseClass with som main functions to the MySqlClient.

When this class is called I initialize a new connection (this is only being done one time for each page loaded (webpage)).

private MySqlConnection connection;
private string connectionString;

public MySqlTest(){
connectionString = "Data Source=x.x.x.x;Database=gffgg;User ID=dfdf;Password=xxx";
connection = getMySQLConnection();
}

the SqlBaseClass then has a SqlExecute and SqlSelect function. The Selecte function returns MySqlDataReader. In MySqlBaseClass I also have functions that are more specific like GetUsers(). This function has the specific sql command, and then calls the SqlSelect. The GetUsers retrieves the MySqlDataReader, and unwraps it regarding to the usertables structure.

protected MySqlDataReader SqlSelect(string sqlCommand){
MySqlCommand myCommand = new MySqlCommand(sqlCommand, connection);
MySqlDataReader reader = myCommand.ExecuteReader();
myCommand.Dispose();
return reader;
}

public string GetUsers(){
string strResult = "";
MySqlDataReader reader = SqlSelect("SELECT * FROM `User`");

while(reader.Read() == true)
strResult += reader.GetString(0) + " ";
return strResult;
}

Now I can make all my custom DB functions, using the select and execute functions.
The DB will be open in the whole pageload process, so in the end I have to cloce it again. For this I user My SqlBaseClass's destructor

~MySqlTest(){
connection.Close();
}

This should work...I hope :-), please write if you have commands to this solution!
Regards Claus