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; }