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