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; }
static void InsertLargeData(MySqlConnection conn2) { int size = 1024 * 1000 * 22;//ok //int size = 1024 * 1000 * 10;//ok //int size = 1024; byte[] buffer = new byte[size]; byte dd = 0; for (int i = 0; i < size; ++i) { buffer[i] = dd; dd++; if (dd > 200) { dd = 0;//reset; } } SqlStringTemplate sql_template = new SqlStringTemplate("insert into table2(data) values(?d)"); var cmd2 = new MySqlCommand(sql_template, conn2); cmd2.Parameters.AddWithValue("?d", buffer); cmd2.ExecuteNonQuery();// if (!cmd2.HasError) { uint inser_id = cmd2.LastInsertedId; } else { //error } }
public static void T_LongData() { var connStr = GetMySqlConnString(); { var conn2 = new MySqlConnection(connStr); conn2.Open(); conn2.ChangeDB("test"); SqlStringTemplate sql_template = new SqlStringTemplate("insert into table1(name) values(?i)"); for (int i = 0; i < 100000; ++i) { var cmd2 = new MySqlCommand(sql_template, conn2); cmd2.Parameters.AddWithValue("?i", i); cmd2.ExecuteNonQuery();// } conn2.Close(); } //-------------- var conn = new MySqlConnection(connStr); conn.Open(); conn.ChangeDB("test"); { var cmd = new MySqlCommand("select * from table1", conn); var reader = cmd.ExecuteReader(); while (reader.Read()) { } reader.Close(); } conn.Close(); }
static void TestJson4(MySqlConnection conn) { //------------ // mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; //mysql > SET @j2 = '1'; //mysql > SELECT JSON_CONTAINS(@j, @j2, '$.a'); //+-------------------------------+ //| JSON_CONTAINS(@j, @j2, '$.a') | //+-------------------------------+ //| 1 | //+-------------------------------+ //, // "SET @j2 = '1'", { string[] test_some_json_func = new string[] { SwapQuote("SET @j = ''{'a': 1, 'b': 2, 'c': {'d': 4}}'';"), "SET @j2 = '1';" }; for (int i = 0; i < test_some_json_func.Length; ++i) { SqlStringTemplate sqlTemplate = new SqlStringTemplate(test_some_json_func[i], false); new MySqlCommand(sqlTemplate, conn).ExecuteNonQuery(); } } { SqlStringTemplate sqlTemplate = new SqlStringTemplate("SELECT JSON_CONTAINS(@j, @j2, '$.a')", false); var cmd = new MySqlCommand(sqlTemplate, conn); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string data = reader.GetString(0); System.Diagnostics.Debug.WriteLine(data); } reader.Close(); } }
public PreparedContext(uint statementId, SqlStringTemplate sqlStringTemplate, TableHeader tableHeader) { this.statementId = statementId; _sqlStringTemplate = sqlStringTemplate; _keys = _sqlStringTemplate.GetValueKeys(); //---------------------------------------------- _tableHeader = tableHeader; int serverFieldCount = tableHeader.ColumnCount; //add field information to _keys List<FieldPacket> fields = tableHeader.GetFields(); //---------------------------------------------- int bindingCount = 0; for (int i = 0; i < serverFieldCount; ++i) { FieldPacket f = fields[i]; if (f.name == "?") // { //this is binding field _keys[bindingCount].fieldInfo = f; bindingCount++; } } //some field from server is not binding field //so we select only binding field if (bindingCount != _keys.Count) { throw new Exception("key num not matched!"); } //------------------------------------------------- _preparedValues = new MyStructData[bindingCount]; //*** }
static void TestJson5(MySqlConnection conn) { //from https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html { //mysql > SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; //mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); //+---------------------------------------------+ //| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') | //+---------------------------------------------+ //| 1 | //+---------------------------------------------+ //mysql > SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); //+---------------------------------------------+ //| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') | //+---------------------------------------------+ //| 0 | //+---------------------------------------------+ //mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d'); //+----------------------------------------+ //| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') | //+----------------------------------------+ //| 1 | //+----------------------------------------+ //mysql > SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d'); //+----------------------------------------+ //| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') | //+----------------------------------------+ //| 0 | //+----------------------------------------+ string[] test_some_json_func = new string[] { SwapQuote("SET @j = ''{'a': 1, 'b': 2, 'c': {'d': 4}}'';") }; for (int i = 0; i < test_some_json_func.Length; ++i) { SqlStringTemplate sqlTemplate = new SqlStringTemplate(test_some_json_func[i], false); new MySqlCommand(sqlTemplate, conn).ExecuteNonQuery(); } } { string[] select_sqls = new string[] { "SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');", "SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');", "SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');", "SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');" }; for (int i = 0; i < select_sqls.Length; ++i) { SqlStringTemplate sqlTemplate = new SqlStringTemplate(select_sqls[i], false); var cmd = new MySqlCommand(sqlTemplate, conn); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string data = reader.GetString(0); System.Diagnostics.Debug.WriteLine(data); } reader.Close(); } } }