static void CreateTable(MySqlConnection conn)
{
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();
}
static void InsertData(MySqlConnection conn)
{
string sql = "insert into test001(col1,col2,col3,col4) values(10,'AA','123456789','0001-01-01')";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
uint lastInsertId = cmd.LastInsertedId;
}
public static void T_StoreProcMultiResultSet2()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
//Console.WriteLine("1");
conn.Open();
PrepareTable1(conn);
// Console.WriteLine("2");
{
string createStoreProcSql = @"DROP PROCEDURE IF EXISTS multi;";
var cmd = new MySqlCommand(createStoreProcSql, conn);
// Console.WriteLine("3");
cmd.ExecuteNonQuery();
}
{
string createStoreProcSql = @"CREATE PROCEDURE multi() BEGIN
SELECT 1011 as A;
SELECT 1022 as B;
select col_id from test001;
END";
//Console.WriteLine("4");
var cmd = new MySqlCommand(createStoreProcSql, conn);
cmd.ExecuteNonQuery();
}
{
// Console.WriteLine("5");
string callProc = "call multi();";
var cmd = new MySqlCommand(callProc, conn);
var reader = cmd.ExecuteReader();
//access to sub table
MySqlSubTable currentSubTable;
while (reader.Read())
{
MySqlSubTable subTable = reader.CurrentSubTable;
if (subTable != currentSubTable)
{
//change to new table
currentSubTable = subTable;
}
// Console.WriteLine("6");
//we read each row from
int data1 = reader.GetInt32(0);
Console.WriteLine(data1);
}
//Console.WriteLine("7");
reader.Close();
}
//--------------------------
conn.Close();
// Report.WriteLine("ok");
}
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_StoreProcMultiResultSet()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
//Console.WriteLine("1");
conn.Open();
// Console.WriteLine("2");
{
string createStoreProcSql = @"DROP PROCEDURE IF EXISTS multi;";
var cmd = new MySqlCommand(createStoreProcSql, conn);
// Console.WriteLine("3");
cmd.ExecuteNonQuery();
}
{
string createStoreProcSql = @"CREATE PROCEDURE multi() BEGIN
SELECT 1 as A;
SELECT 2 as B;
END";
//Console.WriteLine("4");
var cmd = new MySqlCommand(createStoreProcSql, conn);
cmd.ExecuteNonQuery();
}
{
// Console.WriteLine("5");
string callProc = "call multi();";
var cmd = new MySqlCommand(callProc, conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
// Console.WriteLine("6");
//we read each row from
int data1 = reader.GetInt32(0);
}
//Console.WriteLine("7");
reader.Close();
}
//--------------------------
conn.Close();
// Report.WriteLine("ok");
}
static void DropTableIfExists(MySqlConnection conn)
{
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
public static void T_StoreProcMultiResultSet3()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
//Console.WriteLine("1");
conn.Open();
PrepareTable1(conn);
// Console.WriteLine("2");
{
string createStoreProcSql = @"DROP PROCEDURE IF EXISTS multi;";
var cmd = new MySqlCommand(createStoreProcSql, conn);
// Console.WriteLine("3");
cmd.ExecuteNonQuery();
}
{
string createStoreProcSql = @"CREATE PROCEDURE multi() BEGIN
SELECT 1011 as A;
SELECT 1022 as B;
select col_id from test001;
END";
//Console.WriteLine("4");
var cmd = new MySqlCommand(createStoreProcSql, conn);
cmd.ExecuteNonQuery();
}
{
// Console.WriteLine("5");
string callProc = "call multi();";
var cmd = new MySqlCommand(callProc, conn);
//access to sub table
var currentSubTable = MySqlSubTable.Empty;
cmd.ExecuteSubTableReader(reader =>
{
if (reader.CurrentSubTable.Header != currentSubTable.Header)
{
//change main table
//some table may split into many sub table
}
currentSubTable = reader.CurrentSubTable;
//on each subtable
//create data reader for the subtable
while (reader.Read())
{
Console.WriteLine(reader.GetInt32(0));
}
//last table
if (currentSubTable.IsLastTable)
{
conn.Close();
}
});
}
}
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_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");
}
public static void T_DateTimeData()
{
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 datetime," +
"col2 date,col3 time,col4 timestamp, primary key(col_id) )";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
{
string sql = "insert into test001(myname,col1,col2,col3,col4) values(?myname,?col1,?col2,?col3,?col4)";
var cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("?myname", "OKOK!");
cmd.Parameters.AddWithValue("?col1", DateTime.Now);
cmd.Parameters.AddWithValue("?col2", DateTime.Now);
cmd.Parameters.AddWithValue("?col3", DateTime.Now);
cmd.Parameters.AddWithValue("?col4", DateTime.Now);
cmd.ExecuteNonQuery();
}
conn.Close();
Report.WriteLine("ok");
}
public static void T_StringEscape()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open();
//1. drop table
{
var cmd = new MySqlCommand("drop table if exists user_info2", conn);
cmd.ExecuteNonQuery();
}
//2. create new one
{
var cmd = new MySqlCommand("create table user_info2(uid int(10),u_name varchar(45));", conn);
cmd.ExecuteNonQuery();
}
//3. add some data
{
var cmd = new MySqlCommand("insert into user_info2(uid, u_name) values(?uid, '?????')", conn);
cmd.Parameters.AddWithValue("?uid", 10);
cmd.ExecuteNonQuery();
}
Report.WriteLine("ok");
conn.Close();
}
public static void T_CreateTable()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open();
var cmd = new MySqlCommand("create table user_info2(uid int(10),u_name varchar(45));", conn);
cmd.ExecuteNonQuery();
Report.WriteLine("ok");
conn.Close();
}
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;
}
}
static void CreateNewTable(MySqlConnection conn)
{
string sql = "create table testmore(col_id int(10) unsigned not null auto_increment, mydata text,primary key(col_id)) ENGINE=MyISAM DEFAULT CHARSET=latin1";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
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");
}
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 Test1_Insert()
{
string filename = "TestMe.png";//216,362 bytes
//filename = "Colorful.jpg";//885,264 bytes
//filename = "TestJpg.jpg";//2,066 bytes
byte[] buffer = File.ReadAllBytes("D:\\[]Photo\\" + filename);
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.InternalOpen();
string sql = "INSERT INTO ??t1 SET ??c2 = ?buffer1";
MySqlCommand command = new MySqlCommand(sql, sqlConn);
command.Parameters.SetSqlPart("??t1", "saveimage");
command.Parameters.SetSqlPart("??c2", "saveImagecol");
command.Parameters.AddWithValue("?buffer1", buffer);
command.ExecuteNonQuery();
}