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 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_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_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_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_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 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_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 Test1_Select()
{
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 = "select * from ??t1 where ??c1 > ?n1 and ??c1 < ?n2";
int testN1 = 4510;
int testN2 = 4520;
MySqlCommand command = new MySqlCommand(sql, sqlConn);
command.Parameters.SetSqlPart("??t1", "saveimage");
command.Parameters.SetSqlPart("??c1", "idsaveImage");
command.Parameters.AddWithValue("?n1", testN1);
command.Parameters.AddWithValue("?n2", testN2);
var reader = command.ExecuteReader();
int count = 0;
while (reader.Read())
{
Console.WriteLine("Id : " + reader.GetInt32(0));
Console.WriteLine("Buffer size : " + reader.GetBuffer(1).Length);
++count;
if (count >= 3)
{
break;
}
}
reader.Close();
}
