Hi buddies,
I am using MySQL Connector/Net to connector MySQL db in C# (VS 2003) for developing a chat app.
Using dll mysql.data, I have seen a strange issue that, after executing the SQL quries very fast for some time, all the pending threads 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 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
// ------------------------------------------------------------
// Date Author Reference Description
// ------------------------------------------------------------
// 22-June-2005 Vinu.P.K Iteration 1.1 Created
// ------------------------------------------------------------
// 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>
public class MYSQLLayer
{
private MySqlConnection oMySQLCon;
private MySqlCommand oMySQLCmd;
private MySqlDataAdapter oMySQLAdap;
private string sConnectionString;
private string sConUserID;
private int nPortNo;
private string sPassword;
private string sDatabaseName;
private string sHost;
private static string 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;
//}
public string ConnectionStrings
{
get
{
return sConnectionString;
}
set
{
sConnectionString = value;
}
}
//User ID for the Mysql database.
public string MySQLUserID
{
get
{
return sConUserID;
}
set
{
sConUserID = value;
}
}
//My sql Port No
public int MySQLPort
{
get
{
return nPortNo;
}
set
{
nPortNo = value;
}
}
//Mysql Password
public string MySQLPassword
{
get
{
return sPassword;
}
set
{
sPassword = value;
}
}
//Mysql Database Name
public string MySQLDatabase
{
get
{
return sDatabaseName;
}
set
{
sDatabaseName = value;
}
}
//My sql host address
public string MySQLHost
{
get
{
return sHost;
}
set
{
sHost = value;
}
}
/// <summary>
/// Establishes a Connection from the config file
/// </summary>
/// <returns></returns>
public bool 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;
return true;
}
catch(Exception ex)
{
Logging.WriteDBLog("GetConnectionstring Error due to : "+ex.Message+"\n");
Console.WriteLine("\n GetConnectionstring Error due to : {0}", ex.Message);
return false;
}
}
else
{
ConnectionStrings = ConnString;
return true;
}
}
/// <summary>
/// Opens the database connection.
/// </summary>
public bool 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();");
return true;
}
catch(MySqlException ex)
{
Logging.WriteDBLog("OpenConnection Error due to : "+ex.Message+"\n");
Console.WriteLine("\n OpenConnection Error due to : {0}", ex.Message);
return false;
}
catch(Exception oException)
{
Logging.WriteDBLog("OpenConnection Error due to : "+oException.Message+"\n");
Console.WriteLine("\n OpenConnection Error due to : {0}", oException.Message);
return false;
}
}
/// <summary>
/// Closes the database connection.
/// </summary>
public void 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();
}
return new DataSet();
}
/// <summary>
/// Executes the SQL aganist the Database.
/// </summary>
/// <param name="sQuery"></param>
public void 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>
public string ExecSqlScalar(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 "";
}
}
}
Edited 2 time(s). Last edit at 05/05/2006 12:28AM by Vinu PK.