public static void T_PrepareStatement() { var connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); { string sql = "drop table if exists test001"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "create table test001(col_id int(10) unsigned not null auto_increment, col1 int(10)," + "col2 char(2),col3 varchar(255),col4 datetime, primary key(col_id) )"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "insert into test001(col1,col2,col3,col4) values(?col1,?col2,?col3,?col4)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); for (int i = 0; i < 100; ++i) { var pars = cmd.Parameters; pars.AddWithValue("?col1", 10); pars.AddWithValue("?col2", "AA"); pars.AddWithValue("?col3", "0123456789"); pars.AddWithValue("?col4", "0001-01-01"); cmd.ExecuteNonQuery(); } } { string sql = "select col1,col2 from test001 where col1>?col1_v"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); cmd.Parameters.AddWithValue("?col1_v", 0); var reader = cmd.ExecuteReader(); while (reader.Read()) { } reader.Close(); } conn.Close(); Report.WriteLine("ok"); }
public static void T_FloatingRange() { MySqlConnectionString connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); { string sql = "drop table if exists test002"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { //num3 decimal(65,30) is max range is possible //num3 decimal if not define range defualt is decimal(10,0) string sql = "create table test002(col_id int(10) unsigned not null auto_increment," + "num1 float, num2 double, num3 decimal(65,30), primary key(col_id) )"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "insert into test002 (num1, num2, num3) values (?num1, ?num2, ?num3)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); var pars = cmd.Parameters; pars.AddWithValue("?num1", 10.15); pars.AddWithValue("?num2", -101.5); pars.AddWithValue("?num3", 1015.00); cmd.ExecuteNonQuery(); pars.ClearDataValues(); pars.AddWithValue("?num1", 10.15d); pars.AddWithValue("?num2", -101.5f); pars.AddWithValue("?num3", (decimal)1015.00); cmd.ExecuteNonQuery(); pars.ClearDataValues(); pars.AddWithValue("?num1", float.MaxValue); pars.AddWithValue("?num2", double.MaxValue); //decimal of C# have the number of digits to the right of the decimal point less or equal 5 digits pars.AddWithValue("?num3", decimal.MaxValue); cmd.ExecuteNonQuery(); pars.ClearDataValues(); pars.AddWithValue("?num1", float.MinValue); pars.AddWithValue("?num2", double.MinValue); pars.AddWithValue("?num3", decimal.MinValue); cmd.ExecuteNonQuery(); } conn.Close(); Report.WriteLine("ok"); }
public static void T_NumRange() { var connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); { string sql = "drop table if exists test001"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "create table test001(col_id int(10) unsigned not null auto_increment," + "num1 int,num2 int unsigned, " + "num3 smallint, num4 smallint unsigned, " + "num5 bigint, num6 bigint unsigned, " + "num7 tinyint, num8 tinyint unsigned, " + "num9 decimal(32,2), num10 decimal(32,2) unsigned," + " primary key(col_id) )"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "insert into test001(num1,num2,num3,num4,num5,num6,num7,num8,num9,num10) values(?num1,?num2,?num3,?num4,?num5,?num6,?num7,?num8,?num9,?num10)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); var pars = cmd.Parameters; //ok pars.AddWithValue("?num1", -10); pars.AddWithValue("?num2", 20); pars.AddWithValue("?num3", -10); pars.AddWithValue("?num4", 20); pars.AddWithValue("?num5", -10); pars.AddWithValue("?num6", 20); pars.AddWithValue("?num7", -10); pars.AddWithValue("?num8", 20); pars.AddWithValue("?num9", -10); pars.AddWithValue("?num10", 20); cmd.ExecuteNonQuery(); //--------------------------- pars.ClearDataValues(); pars.AddWithValue("?num1", -10); pars.AddWithValue("?num2", 20); pars.AddWithValue("?num3", (short)-10); pars.AddWithValue("?num4", (ushort)20); pars.AddWithValue("?num5", (long)-10); pars.AddWithValue("?num6", (ulong)20); pars.AddWithValue("?num7", (sbyte)-10); pars.AddWithValue("?num8", (byte)20); pars.AddWithValue("?num9", (decimal)-10); pars.AddWithValue("?num10", (decimal)20); cmd.ExecuteNonQuery(); //--------------------------- pars.ClearDataValues(); pars.AddWithValue("?num1", int.MinValue); pars.AddWithValue("?num2", uint.MaxValue); pars.AddWithValue("?num3", short.MinValue); pars.AddWithValue("?num4", ushort.MaxValue); pars.AddWithValue("?num5", long.MinValue); pars.AddWithValue("?num6", ulong.MaxValue); pars.AddWithValue("?num7", sbyte.MinValue); pars.AddWithValue("?num8", byte.MaxValue); pars.AddWithValue("?num9", decimal.MinValue); pars.AddWithValue("?num10", decimal.MaxValue); cmd.ExecuteNonQuery(); //--------------------------- //expected errors ... //--------------------------- //pars.ClearDataValues(); //pars.AddWithValue("?num1", -10); //ok -unsigned //pars.AddWithValue("?num2", -20); //err -no record insert //cmd.ExecuteNonQuery(); //--------------------------- //pars.ClearDataValues(); //pars.AddWithValue("?num1", int.MinValue); //ok -unsigned //pars.AddWithValue("?num2", uint.MaxValue); //err -no record insert //cmd.ExecuteNonQuery(); ////--------------------------- } conn.Close(); Report.WriteLine("ok"); }
public static void T_NullData() { var connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); { string sql = "drop table if exists test001"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "create table test001(col_id int(10) unsigned not null auto_increment, myname varchar(20),mydtm datetime, col1 char(2)," + "col2 varchar(10), primary key(col_id) )"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "insert into test001(myname) values(?myname)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); cmd.Parameters.AddWithValue("?myname", "\"a"); //empty string //cmd.Parameters.AddWithValue("?col1", "OK1"); //width =2 ,so in MySQL 5.6 strict mode, err-> data is too long for column //cmd.Parameters.AddWithValue("?col2", "1000"); cmd.ExecuteNonQuery(); //ppctx.Close(); } { string sql = "select col2,myname,mydtm from test001"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); var reader = cmd.ExecuteReader(); while (reader.Read()) { string f0 = reader.GetString(0); string f1 = reader.GetString("myname"); DateTime dtm = reader.GetDateTime(2); } reader.Close(); } conn.Close(); Report.WriteLine("ok"); }
public static void T_StringData3() { var connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); { string sql = "drop table if exists test001"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "create table test001(col_id int(10) unsigned not null auto_increment, myname varchar(20), col1 char(2)," + "col2 varchar(10), primary key(col_id) )"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "insert into test001(myname,col1,col2) values(?myname,?col1,?col2)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); cmd.Parameters.AddWithValue("?myname", "OKOK!"); cmd.Parameters.AddWithValue("?col1", "OK1"); //width =2 ,so in MySQL 5.6 strict mode, err-> data is too long for column cmd.Parameters.AddWithValue("?col2", "1000"); cmd.ExecuteNonQuery(); } conn.Close(); Report.WriteLine("ok"); }
static void InsertMore(MySqlConnection conn) { string data = "a";//1 char data = "aaaaaaaaaa";//10 char data += data + data + data + data;//50 char data += data;//100 char uint lastInsertId = 0; { string sql = "insert into testmore(mydata) values(?mydata)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); for (int i = 0; i < 50; i++) { cmd.Parameters.AddWithValue("?mydata", data); //cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); data += "a"; //sql = "insert into testmore(mydata) values(\"" + data + "\")"; } lastInsertId = cmd.LastInsertedId; } }
public static void T_InsertBlobData() { var connStr = GetMySqlConnString(); var conn = new MySqlConnection(connStr); conn.Open(); conn.UpdateMaxAllowPacket(); //DropIfExist(conn); //CreateNewTable(conn); //InsertMore(conn); //if (ReadAll(conn)) //{ // return; //} { string sql = "drop table if exists test001"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } { string sql = "create table test001(col_id int(10) unsigned not null auto_increment, mydata longblob,primary key(col_id)) ENGINE=MyISAM DEFAULT CHARSET=latin1"; var cmd = new MySqlCommand(sql, conn); cmd.ExecuteNonQuery(); } //create sample blob byte[] data = CreateTestData(); uint lastInsertId = 0; //int testdata_crc32 = 0; { string sql = "insert into test001(mydata) values(?mydata)"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); //testdata_crc32 = SharpConnect.CRC32Calculator.CalculateCrc32(data); cmd.Parameters.AddWithValue("?mydata", data); for(int i = 0; i < 5; i++) { cmd.ExecuteNonQuery(); } lastInsertId = cmd.LastInsertedId; } { if (lastInsertId > 0) { //test download back string sql = "select mydata from test001 where col_id<=?col_id"; var cmd = new MySqlCommand(sql, conn); cmd.Prepare(); cmd.Parameters.AddWithValue("?col_id", lastInsertId); var reader = cmd.ExecuteReader(); while (reader.Read()) { byte[] dataBuffer = reader.GetBuffer(0); if (Match(data, dataBuffer)) { Console.WriteLine("All Matching!!!"); } else { Console.WriteLine("Some byte not match!!"); } } reader.Close(); } } conn.Close(); Report.WriteLine("ok"); }