public static void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
lock (s_queueLock)
{
s_connPoolAgent.ReleaseConnection(connstr, conn);
}
}
public Query(Connection conn, SqlStringTemplate sql, CommandParams cmdParams)
{
//*** query use conn resource such as parser,writer
//so 1 query 1 connection
Query bindingQuery = conn.BindingQuery;
if (bindingQuery != null)
{
//check if binding query can be close
if (!bindingQuery.LateClose())
{
//can't use this conn
throw new Exception("connection is in used");
}
}
//--------------------------------------------------------------
conn.BindingQuery = this;
//--------------------------------------------------------------
if (sql == null)
{
throw new Exception("Sql command can not null.");
}
//--------------------------------------------------------------
this._conn = conn;
this._cmdParams = cmdParams;
//--------------------------------------------------------------
nestTables = false;
_sqlParserMx = conn.MySqlParserMx;
_writer = conn.PacketWriter;
//_receiveBuffer = null;
_sqlStrTemplate = sql;
}
public void ReleaseConnection(MySqlConnectionString connstr, Connection conn)
{
Queue<Connection> found;
//not found
if (!s_connQueue.TryGetValue(connstr.ConnSignature, out found))
{
found = new Queue<Connection>();
s_connQueue.Add(connstr.ConnSignature, found);
}
conn.IsStoredInConnPool = true;
found.Enqueue(conn);
}
//------------
//we create query for each command and not reuse it
//------------
internal Query(Connection conn, string sql, CommandParams cmdParams)
: this(conn, new SqlStringTemplate(sql), cmdParams)
{
}
public static void HardKill(this MySqlConnection tobeKillConn)
{
//TODO : review here ?
//we use another connection to kill current th
Connection internalConn = tobeKillConn.Conn;
string realSql = "KILL " + internalConn.threadId;
//sql = "FLUSH QUERY CACHE;";
Connection killConn = new Connection(internalConn.config);
killConn.Connect();
var q = new Query(killConn, realSql, null);
q.Execute(false); //wait
q.Close();
killConn.Disconnect();
}
internal void InternalOpen(Action onComplete = null)
{
this.FromConnectionPool = false;//reset
//get connection from pool
if (UseConnectionPool)
{
_conn = ConnectionPool.GetConnection(_connStr);
if (_conn != null)
{
FromConnectionPool = true;
if (onComplete != null)
{
onComplete();
}
}
else
{
//create new
_conn = new Connection(
new ConnectionConfig(
_connStr.Host,
_connStr.Username,
_connStr.Password,
_connStr.Database)
{ port = _connStr.PortNumber });
_conn.Connect(onComplete);
}
}
else
{
//new connection
_conn = new Connection(
new ConnectionConfig(
_connStr.Host,
_connStr.Username,
_connStr.Password,
_connStr.Database)
{ port = _connStr.PortNumber });
_conn.Connect(onComplete);
}
}
public static void Test1_OldVersionTest()
{
string filename;
filename = "TestMe.png";//216,362 bytes
//filename = "Colorful.jpg";//885,264 bytes
//filename = "TestJpg.jpg";//2,066 bytes
byte[] buffer;
buffer = File.ReadAllBytes("D:\\[]Photo\\" + filename);
//buffer = new byte[500500];
//Stream stReader = new Stream("D:\\[]Photo\\TestJpg.jpg");
//BinaryReader binaryReader = new BinaryReader(stReader);
var ss = new System.Diagnostics.Stopwatch();
ss.Start();
string sql;
string sql2;
//please note the
//field or column binding is extension, must start with ??
//sql = "INSERT INTO ??t1 (??c1, ??c2) VALUES (?n1 , ?buffer1)";
sql = "INSERT INTO ??t1 SET ??c2 = ?buffer1";
//sql = "select * from ??t1 where ??c1 > ?n1 and ?c1 < ?n2";
//sql = "select * from ??t1 where ??c1 = 4579";
//sql = "select 1+?n3 as test1";
//sql = "select concat(?s1,?s2,?s1,?s2,?s1,?s2,?s1,?s2,?s1,?s2) as test1";
//sql = "select concat(?s1,?s2,?s1,?s2) as test1";
//sql = "SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate"
// + " FROM Orders INNER JOIN Customers"
// + " ON Orders.CustomerID = Customers.CustomerID;";
//sql2 = "select * from ?t1 where ?c1 > ?n1 and ?c1 < ?n2";
//sql = "INSERT INTO ?t1 ( ?c2, ?c3) VALUES ( ?s1, ?s2)";
//sql = "DELETE FROM ?t1 WHERE ?c1=?n1";
//sql = "UPDATE ?t1 SET ?c2=?s1 WHERE ?c1=?n1";
//CommandParameters cmdValues = new CommandParameters();
//sql = "select ?n1+?n2 as test1";
int testN1 = 4520;
int testN2 = 4530;
sql = "select * from ??t1 where ??c1 > ?n1 and ??c1 < ?n2";
//sql = "select * from ?t1 where ?c1 = ?n2";
//sql = "select ?n1+?n2 as test1";
CommandParams cmd2Values = new CommandParams();
cmd2Values.SetSqlPart("??t1", "saveimage");
cmd2Values.SetSqlPart("??c1", "idsaveImage");
//cmd2Values.AddField("c2", "saveImagecol");
cmd2Values.AddWithValue("?n1", testN1);
cmd2Values.AddWithValue("?n2", testN2);
//cmd2Values.AddValue("n3", 29.5);
//cmd2Values.AddValue("s1", "foo");
//cmd2Values.AddValue("s2", "bar");
//cmd2Values.AddValue("buffer1", buffer);
ConnectionConfig config = new ConnectionConfig("root", "root");
config.database = "test";
//MySqlConnection sqlConn = new MySqlConnection(config.host, config.user, config.password, config.database);
//sqlConn.UseConnectionPool = true;
//sqlConn.Open();
//MySqlCommand command = new MySqlCommand(sql, sqlConn);
//command.Parameters.AddTable("t1", "saveimage");
//command.Parameters.AddField("c1", "idsaveImage");
//command.Parameters.AddValue("n1", testN1);
//command.Parameters.AddValue("n2", testN2);
//var reader = command.ExecuteReader();
//reader.Read();
//Connection connection = sqlConn.Conn;/*ConnectionPool.GetConnection(new MySqlConnectionString(config.host, config.user, config.password, config.database));*/
Connection connection = new Connection(config);
if (connection == null)
{
connection = new Connection(config);
connection.IsStoredInConnPool = false;
connection.Connect();
}
int count = 3;
int fCase = 1;
for (int i = 0; i < count; i++)
{
int j = 0;
//query = connection.CreateQuery(sql, cmdValues);
//query = connection.CreateQuery(cmd2Values);
//query.ExecutePrepareQuery(cmd2Values);
var query = new Query(connection, sql, cmd2Values);
query.SetResultListener(tableResult =>
{
if (query.LoadError != null)
{
Console.WriteLine("Error : " + query.LoadError.message);
}
else if (query.OkPacket != null)
{
Console.WriteLine("i : " + i + ", OkPacket : [affectedRow] >> " + query.OkPacket.affectedRows);
Console.WriteLine("i : " + i + ", OkPacket : [insertId] >> " + query.OkPacket.insertId);
}
else
{
var thead = tableResult.tableHeader;
int col_idsaveImage = thead.GetFieldIndex("idsaveImage");
int col_saveImageCol = thead.GetFieldIndex("saveImagecol");
int col_test = thead.GetFieldIndex("test1");
//if (col_idsaveImage < 0 || col_saveImageCol < 0)
//{
// throw new Exception();
//}
Console.WriteLine("Result : ");
//while (query.ReadRow())
//{
// if (col_test == 0)
// {
// Console.WriteLine("Result of " + "test1 : >> " + query.Cells[col_test] + " <<");
// }
// else
// {
// Console.WriteLine("Id : " + query.Cells[col_idsaveImage]);
// Console.WriteLine("Buffer size : " + query.Cells[col_saveImageCol].myBuffer.Length);
// }
// //Console.WriteLine(query.GetFieldData("myusercol1"));
// if (++j > 3)
// {
// break;
// }
//}
}
});
testN1 += 10;
testN2 += 10;
cmd2Values.AddWithValue("?n1", testN1);
cmd2Values.AddWithValue("?n2", testN2);
query.Execute(true); //***
query.Close();
connection.Disconnect();
connection = new Connection(config);
connection.Connect();
//j = 0;
//query = connection.CreateQuery(sql2, prepare);
//query.ExecuteQuery();
//if (query.loadError != null)
//{
// Console.WriteLine("Error : " + query.loadError.message);
//}
//else
//{
// while (query.ReadRow() && j < 3)
// {
// Console.WriteLine(query.GetFieldData("idsaveImage"));
// Console.WriteLine(query.GetFieldData("saveImagecol"));
// //Console.WriteLine(query.GetFieldData("myusercol1"));
// j++;
// }
//}
//query.Close();
}
ss.Stop();
long avg = ss.ElapsedMilliseconds / count;
Console.WriteLine("Counting : " + count + " rounds. \r\nAverage Time : " + avg + " ms");
connection.Disconnect();
}
