static void SelectDataBack(MySqlConnection conn)
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
#if DEBUG
conn.dbugPleaseBreak = true;
#endif
//this is very basic mapper***
var reader = cmd.ExecuteReader();
var mapper = Mapper.Map((SimpleInfo t, int col_id, string col2, string col3) =>
{
t.col1 = col_id;
t.col2 = col2;
});
mapper.DataReader = reader;
while (reader.Read())
{
//simple map query result to member of the target object
//we create simpleinfo and use mapper to map field
var simpleInfo = mapper.Map(new SimpleInfo());
}
reader.Close();
}
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_DropCreateInsert()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
var tc = new TaskChain();
conn.AsyncOpen(tc);
{
//1. drop tabled
var cmd = new MySqlCommand("drop table if exists user_info2", conn);
cmd.AsyncExecuteNonQuery(tc);
}
{
var cmd = new MySqlCommand("drop table if exists user_info2", conn);
cmd.AsyncExecuteNonQuery(tc);
}
//2. create new one
{
var cmd = new MySqlCommand("create table user_info2(uid int(10),u_name varchar(45));", conn);
cmd.AsyncExecuteNonQuery(tc);
}
//3. add some data
{
var cmd = new MySqlCommand("insert into user_info2(uid, u_name) values(?uid, 'abc')", conn);
cmd.Parameters.AddWithValue("?uid", 10);
cmd.AsyncExecuteNonQuery(tc);
}
Report.WriteLine("ok");
conn.AsyncClose(tc);
tc.Start();
}
public static async void T_InsertAndSelect_TAP()
{
System.Diagnostics.Stopwatch stopW = new System.Diagnostics.Stopwatch();
stopW.Start();
await Task.Run(async () =>
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
await conn.OpenAsync();
//------------------------------------------
//drop table if exist
{
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
await cmd.ExecuteNonQueryAsync();
}
//------------------------------------------
{
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);
await cmd.ExecuteNonQueryAsync();
}
//---------------------------------------------
for (int i = 0; i < 2000; ++i)
{
string sql = "insert into test001(col1,col2,col3,col4) values(10,'AA','123456789','0001-01-01')";
var cmd = new MySqlCommand(sql, conn);
await cmd.ExecuteNonQueryAsync();
}
//--------------------------------------------
//select back
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
#if DEBUG
conn.dbugPleaseBreak = true;
#endif
var reader = await cmd.ExecuteReaderAsync();
while (reader.Read())
{
//test immediate close
await reader.CloseAsync();
}
await reader.CloseAsync();
}
});
//--------------------------------------------
stopW.Stop();
Report.WriteLine("avg:" + stopW.ElapsedTicks);
}
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");
}
static void SelectDataBack(MySqlConnection conn)
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
#if DEBUG
conn.dbugPleaseBreak = true;
#endif
var reader = cmd.ExecuteReader();
while (reader.Read())
{
//test immediate close
reader.Close();
}
reader.Close();
}
public static void T_InsertAndSelect_Async()
{
System.Diagnostics.Stopwatch stopW = new System.Diagnostics.Stopwatch();
stopW.Start();
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
var tc = new TaskChain();
conn.AsyncOpen(tc);
{
//drop table if exist
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
}
{
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.AsyncExecuteNonQuery(tc);
}
for (int i = 0; i < 2000; ++i)
{
string sql = "insert into test001(col1,col2,col3,col4) values(10,'AA','123456789','0001-01-01')";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
}
conn.AsyncClose(tc);
tc.WhenFinish(() =>
{
stopW.Stop();
Report.WriteLine("avg:" + stopW.ElapsedTicks);
});
tc.Start();
}
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 SelectDataBack(MySqlConnection conn)
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
#if DEBUG
conn.dbugPleaseBreak = true;
#endif
var reader = cmd.ExecuteReader();
while (reader.Read())
{
//test immediate close
//reader.Close();
object o0 = reader.GetValue(0);
object o1 = reader.GetValue(1);
object o2 = reader.GetValue("col3");
object o3 = reader.GetValue("col4");
}
reader.Close();
}
public static void T_Select_sysdate()
{
int n = 100;
long total;
long avg;
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open();
Test(n, TimeUnit.Ticks, out total, out avg, () =>
{
var cmd = new MySqlCommand("select sysdate()", conn);
var reader = cmd.ExecuteReader();
if (reader.Read())
{
var dtm = reader.GetDateTime(0);
}
reader.Close();
});
Report.WriteLine("avg:" + avg);
conn.Close();
}
public static void T_Select_sysdate2()
{
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open(() =>
{
var cmd = new MySqlCommand("select sysdate()", conn);
cmd.ExecuteReader(reader =>
{
if (reader.Read())
{
var dtm = reader.GetDateTime(0);
}
reader.Close(() =>
{
conn.Close(() => { });
});
});
});
}
static void DropTableIfExists(MySqlConnection conn)
{
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
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();
}
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_InsertAndSelect_Async3()
{
System.Diagnostics.Stopwatch stopW = new System.Diagnostics.Stopwatch();
stopW.Start();
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.UseConnectionPool = true;
var tc = new TaskChain();
//add task chain too connection object
conn.AsyncOpen(tc);
//-----------------------------------------
{
//drop table if exist
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
}
//-----------------------------------------
{
//drop table if exist
string sql = "drop table if exists test001";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
}
//-----------------------------------------
{
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.AsyncExecuteNonQuery(tc);
}
//-----------------------------------------
for (int i = 0; i < 100; ++i)
{
string sql = "insert into test001(col1,col2,col3,col4) values(10,'AA','123456789','0001-01-01')";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
}
//-----------------------------------------
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteSubTableReader(tc, subtable =>
{
//when new task is add after tc is started
//then this new task is immmediately insert
//after current task
});
}
{
string sql = "select sysdate()";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteScalar(tc, obj =>
{
});
}
//-----------------------------------------
conn.AsyncClose(tc);
tc.WhenFinish(() =>
{
stopW.Stop();
Report.WriteLine("avg:" + stopW.ElapsedTicks);
});
tc.BeforeEachTaskBegin(() =>
{
Console.WriteLine(tc.CurrentTaskIndex + "/" + tc.TaskCount);
});
//----------------------------------------
tc.Start();
}
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();
}
});
}
}
static void InsertData(MySqlConnection conn, TaskChain tc)
{
string sql = "insert into test001(col1,col2,col3,col4) values(10,'AA','123456789','0001-01-01')";
var cmd = new MySqlCommand(sql, conn);
cmd.AsyncExecuteNonQuery(tc);
tc.AddTask(() =>
{
var lastInsertId = cmd.LastInsertedId;
});
}
public static void T_Select_sysdate3()
{
//prefer this
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
var tc = new TaskChain();
conn.AsyncOpen(tc);
var cmd = new MySqlCommand("select sysdate()", conn);
cmd.AsyncExecuteSubTableReader(tc, reader =>
{
if (reader.Read())
{
var dtm = reader.GetDateTime(0);
}
});
conn.AsyncClose(tc);
tc.WhenFinish(() =>
{
});
tc.Start();
}
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_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_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_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 SelectDataBack(MySqlConnection conn, TaskChain tc)
{
string sql = "select * from test001";
var cmd = new MySqlCommand(sql, conn);
tc.AddTask(() =>
{
#if DEBUG
conn.dbugPleaseBreak = true;
#endif
});
//this is very basic mapper***
var mapper = Mapper.Map((SimpleInfo t, int col_id, string col2, string col3) =>
{
t.col1 = col_id;
t.col2 = col2;
});
cmd.AsyncExecuteSubTableReader(tc, reader =>
{
mapper.DataReader = reader;
while (reader.Read())
{
var simpleInfo = mapper.Map(new SimpleInfo());
}
////simple map query result to member of the target object
////we create simpleinfo and use mapper to map field
tc.AutoCallNext = reader.CurrentSubTable.IsLastTable;
});
}
static bool ReadAll(MySqlConnection conn)
{
string sql = "select mydata from testmore";
var cmd = new MySqlCommand(sql, conn);
//cmd.Prepare();
var reader = cmd.ExecuteReader();
string data = "";
int count = 0;
while (reader.Read())
{
data = reader.GetString(0);
Console.WriteLine("data["+(++count)+"] : " + data);
}
reader.Close();
return true;
}
public static void T_Select_ExecuteScalar()
{
int n = 100;
long total;
long avg;
var connStr = GetMySqlConnString();
var conn = new MySqlConnection(connStr);
conn.Open();
Test(n, TimeUnit.Ticks, out total, out avg, () =>
{
var cmd = new MySqlCommand("select sysdate()", conn);
object result = cmd.ExecuteScalar();
});
Report.WriteLine("avg:" + avg);
conn.Close();
}
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_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();
}
