internal string BindValues(CommandParams cmdParams, bool forPrepareStmt)
{
StringBuilder strBuilder = new StringBuilder();
int count = _sqlSections.Count;
for (int i = 0; i < count; i++)
{
var sqlSection = _sqlSections[i];
switch (sqlSection.SectionKind)
{
default:
throw new NotSupportedException();
case SqlSectionKind.SqlText:
strBuilder.Append(sqlSection.Text);
break;
case SqlSectionKind.ValueKey:
if (forPrepareStmt)
{
strBuilder.Append('?');
}
else
{
//get bind data
MyStructData bindedData;
if (!cmdParams.TryGetData(sqlSection.Text, out bindedData))
{
throw new Exception("Error : This key not assign." + sqlSection.Text);
}
else
{
//format data
FormatAndAppendData(strBuilder, ref bindedData);
}
}
break;
case SqlSectionKind.SpecialKey:
string found;
if (cmdParams.TryGetSqlPart(sqlSection.Text, out found))
{
strBuilder.Append(found);
}
else
{
throw new Exception("not found " + sqlSection.Text);
}
break;
}
}
return(strBuilder.ToString());
}
public MySqlCommand(SqlStringTemplate sql, CommandParams cmds, MySqlConnection conn)
{
_sqlStringTemplate = sql;
Parameters = cmds;
Connection = conn;
if (conn != null)
{
this.StringConverter = conn.StringConv;
}
}
internal string BindValues(CommandParams cmdParams, bool forPrepareStmt)
{
StringBuilder strBuilder = new StringBuilder();
int count = _sqlSections.Count;
for (int i = 0; i < count; i++)
{
var sqlSection = _sqlSections[i];
switch (sqlSection.sectionKind)
{
default:
throw new NotSupportedException();
case SqlSectionKind.SqlText:
strBuilder.Append(sqlSection.Text);
break;
case SqlSectionKind.ValueKey:
if (forPrepareStmt)
{
strBuilder.Append('?');
}
else
{
//get bind data
MyStructData bindedData;
if (!cmdParams.TryGetData(sqlSection.Text, out bindedData))
{
throw new Exception("Error : This key not assign." + sqlSection.Text);
}
else
{
//format data
FormatAndAppendData(strBuilder, ref bindedData);
}
}
break;
case SqlSectionKind.SpecialKey:
string found;
if (cmdParams.TryGetSqlPart(sqlSection.Text, out found))
{
strBuilder.Append(found);
}
else
{
throw new Exception("not found " + sqlSection.Text);
}
break;
}
}
return strBuilder.ToString();
}
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 = true;
connection.Connect();
}
int count = 3;
Query query;
query = connection.CreateQuery(sql, cmd2Values);
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);
query = connection.CreateQuery(sql, cmd2Values);
query.Execute();
//switch (fCase)
//{
// case 0:
// filename = "TestMe.png";
// fCase++;
// break;
// case 1:
// filename = "Colorful.jpg";
// fCase++;
// break;
// case 2:
// filename = "TestJpg.jpg";
// fCase = 0;
// break;
//}
//buffer = File.ReadAllBytes("D:\\[]Photo\\" + filename);
//cmd2Values.AddValue("buffer1", buffer);
//Console.WriteLine("Expected Result : " + (testN1 + testN2));
testN1 += 10;
testN2 += 10;
cmd2Values.AddWithValue("?n1", testN1);
cmd2Values.AddWithValue("?n2", testN2);
//query.ExecuteQuery();
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
{
int col_idsaveImage = query.GetColumnIndex("idsaveImage");
int col_saveImageCol = query.GetColumnIndex("saveImagecol");
int col_test = query.GetColumnIndex("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;
}
}
}
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();
}
query.Close();
ss.Stop();
long avg = ss.ElapsedMilliseconds / count;
Console.WriteLine("Counting : " + count + " rounds. \r\nAverage Time : " + avg + " ms");
connection.Disconnect();
}
public MySqlCommand(string sql, CommandParams cmds, MySqlConnection conn)
: this(new SqlStringTemplate(sql), cmds, conn)
{
}
public MySqlCommandAsync()
{
Parameters = new CommandParams();
}
public MySqlCommandAsync(string sql, MySqlConnectionAsync conn)
{
CommandText = sql;
Connection = conn;
Parameters = new CommandParams();
}
public MySqlCommand(string sql, CommandParams cmds, MySqlConnection conn)
: this(new SqlStringTemplate(sql), cmds, conn)
{
}
public MySqlCommand(SqlStringTemplate sql, CommandParams cmds, MySqlConnection conn)
{
_sqlStringTemplate = sql;
Connection = conn;
Parameters = cmds;
}
public MySqlCommand(string sql, CommandParams cmds, MySqlConnection conn)
{
CommandText = sql;
Connection = conn;
Parameters = cmds;
}
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();
}
public MySqlCommand(SqlStringTemplate sql, CommandParams cmds, MySqlConnection conn)
{
_sqlStringTemplate = sql;
Connection = conn;
Parameters = cmds;
}