Issue with threading
Hi Buddies,
I am using 3rd party adapter to connector MySQL db in C# (VS 2003) for developing a chat app.
Using that adapter, I have seen a strange issue that, after executing the SQL quries very fast for some time, all the pending threads in MySQL db is going to a sleep state. After that, no more SQLs are executing. Ie, my C# app is not able to communicate with MySQL db.
Because of this reason my application got hanged. I am very much worried at this point.
For my app all the SQLs are executing in threads.
I use connection string like,
"Server=192.168.1.78;Port=3306;Database=xxx;Uid=user;Pwd=pwd;pooling=true;min pool size=0;max pool size=100;"
Do I need to change my connection string for this issue?
Is this an issue with .net framework or DB or adapter?
Please give me a solution so that I can run my app smooth.
Thanks,
Vinu.P.K
I am using my DB layer code like this,
//
//Project: XXX
//File: MYSQLLayer.cs
//Author: Vinu.P.K
//Date: 22-June-2005
//Description: Classes to manage the Database.
//History
//
//DateAuthorReferenceDescription
//
//22-June-2005Vinu.P.KIteration 1.1Created
//
//Intellectual property of XXX (c) 2005
//
using System;
//using CoreLab.MySql;
//using CoreLab.Common;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;
using System.ComponentModel;
namespace Server.DL
{
///<summary>
/// Summary description for MYSQLLayer.
///</summary>
publicclass MYSQLLayer
{
private MySqlConnection oMySQLCon;
private MySqlCommand oMySQLCmd;
private MySqlDataAdapter oMySQLAdap;
privatestring sConnectionString;
privatestring sConUserID;
privateint nPortNo;
privatestring sPassword;
privatestring sDatabaseName;
privatestring sHost;
privatestaticstring ConnString = "";
// Other managed resource this class uses.
//private Component component = new Component();
// Track whether Dispose has been called.
//private bool disposed = false;
public MYSQLLayer()
{
//GetConnectionstring();
}
//~MYSQLLayer()
//{
//Dispose(true);
//}
//public void Dispose()
//{
//Dispose(true);
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
//GC.SuppressFinalize(this);
//}
//private void Dispose(bool disposing)
//{
// Check to see if Dispose has already been called.
//if(!this.disposed)
//{
// If disposing equals true, dispose all managed
// and unmanaged resources.
//if(disposing)
//{
// Dispose managed resources.
//component.Dispose();
//}
// Call the appropriate methods to clean up
// unmanaged resources here.
// If disposing is false,
// only the following code is executed.
//CloseHandle(my_LoutSocketClient, m_ClientList);
//my_LoutSocketClient= null;
//m_ClientList = null;
//}
//disposed = true;
//}
publicstring ConnectionStrings
{
get
{
return sConnectionString;
}
set
{
sConnectionString =value;
}
}
//User ID for the Mysql database.
publicstring MySQLUserID
{
get
{
return sConUserID;
}
set
{
sConUserID =value;
}
}
//My sql Port No
publicint MySQLPort
{
get
{
return nPortNo;
}
set
{
nPortNo =value;
}
}
//Mysql Password
publicstring MySQLPassword
{
get
{
return sPassword;
}
set
{
sPassword =value;
}
}
//Mysql Database Name
publicstring MySQLDatabase
{
get
{
return sDatabaseName;
}
set
{
sDatabaseName =value;
}
}
//My sql host address
publicstring MySQLHost
{
get
{
return sHost;
}
set
{
sHost =value;
}
}
///<summary>
/// Establishes a Connection from the config file
///</summary>
///<returns></returns>
publicbool GetConnectionstring()
{
if(ConnString ==null || ConnString == "")
{
try
{
string sInput =ConfigurationSettings.AppSettings["MYSQLConnectionString"].ToString();
int i=sInput.IndexOf(":");
string DatabseServer = sInput.Substring(0,i);
MySQLHost =DatabseServer;
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
int portNo =int.Parse(sInput.Substring(0,i));
MySQLPort =portNo;
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
string DatabseName = sInput.Substring(0,i);
MySQLDatabase =DatabseName;
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
string UserID = sInput.Substring(0,i);
MySQLUserID =UserID;
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
string Password = sInput.Substring(0,i);
MySQLPassword =Password;
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
string pooling = sInput.Substring(0,i);
sInput=sInput.Substring(i+1,sInput.Length-i-1);
i=sInput.IndexOf(":");
string min_pool = sInput.Substring(0,i);
sInput=sInput.Substring(i+1,sInput.Length-i-1);
string max_pool = sInput;
//Server=Server;Port=1234;Database=Test;Uid=UserName;Pwd=asdasd;
//connection timeout=5;use compression=true;pooling=true;min pool size=5;max pool size=100;
sInput = "Server="+DatabseServer+";Port=3306;Database="+DatabseName+";Uid="+UserID+";Pwd="+Password+";connection timeout=5;pooling="+pooling+";min pool size="+min_pool+";max pool size="+max_pool+";";
ConnectionStrings =sInput;
ConnString = ConnectionStrings;
returntrue;
}
catch(Exception ex)
{
Logging.WriteDBLog("GetConnectionstring Error due to : "+ex.Message+"\n");
Console.WriteLine("\n GetConnectionstring Error due to : {0}", ex.Message);
returnfalse;
}
}
else
{
ConnectionStrings = ConnString;
returntrue;
}
}
///<summary>
/// Opens the database connection.
///</summary>
publicbool OpenConnection()
{
try
{
Console.WriteLine("\n in OpenConnection()");
oMySQLCon =new MySqlConnection();
Console.WriteLine("\n bf GetConnectionstring();");
GetConnectionstring();
Console.WriteLine("\n af GetConnectionstring();");
oMySQLCon.ConnectionString = ConnectionStrings;
Console.WriteLine("\n bf oMySQLCon.Open();");
oMySQLCon.Open();
Console.WriteLine("\n af oMySQLCon.Open();");
returntrue;
}
catch(MySqlException ex)
{
Logging.WriteDBLog("OpenConnection Error due to : "+ex.Message+"\n");
Console.WriteLine("\n OpenConnection Error due to : {0}", ex.Message);
returnfalse;
}
catch(Exception oException)
{
Logging.WriteDBLog("OpenConnection Error due to : "+oException.Message+"\n");
Console.WriteLine("\n OpenConnection Error due to : {0}", oException.Message);
returnfalse;
}
}
///<summary>
/// Closes the database connection.
///</summary>
publicvoid CloseConnection()
{
try
{
oMySQLCon.Close();
oMySQLCon.Dispose();
}
catch(MySqlException oMySQLException)
{
Logging.WriteDBLog("CloseConnection Error due to : "+oMySQLException.Message+"\n");
}
catch(Exception oException)
{
Logging.WriteDBLog("CloseConnection Error due to : "+oException.Message+"\n");
}
}
///<summary>
/// Executes the SQL aganist Database and returns the dataset.
///</summary>
///<param name="sQuery"></param>
///<returns></returns>
public DataSet ExecuteSqlDS(string sQuery)
{
try
{
Console.WriteLine("\n" +sQuery);
OpenConnection();
Console.WriteLine("\n af OpenConnection();");
DataSet oDsData =new DataSet();
oMySQLCmd =new MySqlCommand(sQuery, oMySQLCon);
oMySQLAdap =new MySqlDataAdapter(oMySQLCmd);
Console.WriteLine("\n bf oMySQLAdap.Fill(oDsData);");
oMySQLAdap.Fill(oDsData);
Console.WriteLine("\n af oMySQLAdap.Fill(oDsData);");
return oDsData;
}
catch(MySqlException oMySQLException)
{
Logging.WriteDBLog("ExecuteSqlDS Error due to : "+oMySQLException.Message+"\n");
Logging.WriteDBLog("ExecuteSqlDS by SQL : "+sQuery+"\n");
}
catch(Exception oException)
{
Logging.WriteDBLog("ExecuteSqlDS Error due to : "+oException.Message+"\n");
Logging.WriteDBLog("ExecuteSqlDS by SQL : "+sQuery+"\n");
}
finally
{
CloseConnection();
}
returnnew DataSet();
}
///<summary>
/// Executes the SQL aganist the Database.
///</summary>
///<param name="sQuery"></param>
publicvoid ExecSql(string sQuery)
{
try
{
OpenConnection();
oMySQLCmd =new MySqlCommand(sQuery, oMySQLCon);
oMySQLCmd.ExecuteNonQuery();
}
catch(MySqlException oMySQLException)
{
Logging.WriteDBLog("ExecSql Error due to : "+oMySQLException.Message+"\n");
Logging.WriteDBLog("ExecSql by SQL : "+sQuery+"\n");
}
catch(Exception oException)
{
Logging.WriteDBLog("ExecSql Error due to : "+oException.Message+"\n");
Logging.WriteDBLog("ExecSql by SQL : "+sQuery+"\n");
}
finally
{
CloseConnection();
}
}
///<summary>
/// Executes the SQL and returns the first coloumn.
///</summary>
///<param name="sQuery"></param>
///<returns></returns>
publicstringExecSqlScalar(string sQuery)
{
try
{
OpenConnection();
oMySQLCmd =new MySqlCommand(sQuery, oMySQLCon);
object obj = oMySQLCmd.ExecuteScalar();
if(obj !=null)
{
return oMySQLCmd.ExecuteScalar().ToString();
}
}
catch(MySqlException oMySQLException)
{
Logging.WriteDBLog("ExecSqlScalar by SQL : "+sQuery+"\n");
Logging.WriteDBLog("ExecSqlScalar Error due to : "+oMySQLException.Message+"\n");
}
catch(Exception oException)
{
Logging.WriteDBLog("ExecSqlScalar by SQL : "+sQuery+"\n");
Logging.WriteDBLog("ExecSqlScalar Error due to : "+oException.Message+"\n");
}
finally
{
CloseConnection();
}
return "";
}
}
}
