public static void Testing()
{
string connStr = "server=10.186.60.61;user=test;database=schema1;port=7144;password=111111";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to mysql...");
conn.Open();
//string sql = "show @@connection;";
string sql = "(select name from aly_test where pad=1 order by id limit 10) union all (select name from aly_order where pad=1 order by id limit 10) /*allow_diff_sequence*/";
//MySqlCommand showcmd = new MySqlCommand(sql, conn);
////showcmd.CommandType = System.Data.CommandType.StoredProcedure;
//MySqlDataReader showrdr = showcmd.ExecuteReader();
////List<String> showlist = new List<string>();
int PID = 0;
//while (showrdr.Read())
//{
// var pid = showrdr.GetValue(1);
// PID = Convert.ToInt32(pid);
// break;
//}
//showrdr.Close();
if (PID != -1)
{
//string killsql = "kill @@connection " + PID.ToString();
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
List <String> dblelist = new List <string>();
while (rdr.Read())
{
StringBuilder sb = new StringBuilder();
String line;
int fc = 0;
for (int i = 0; i < rdr.FieldCount; i++)
{
Type type = rdr.GetFieldType(rdr.GetName(i));
if (type.Name == "VarChar" || type.Name == "Char" || type.Name == "String" || type.Name == "Byte" || type.Name == "SByte")
{
if (fc == (rdr.FieldCount - 1))
{
line = "\"" + rdr.GetValue(i) + "\"";
}
else
{
line = "\"" + rdr.GetValue(i) + "\",";
}
}
else if (type.Name == "DateTime")
{
if (fc == (rdr.FieldCount - 1))
{
line = "{" + rdr.GetValue(i) + "}";
}
else
{
line = "{" + rdr.GetValue(i) + "},";
}
}
else
{
if (fc == (rdr.FieldCount - 1))
{
line = rdr.GetValue(i).ToString();
}
else
{
line = rdr.GetValue(i) + ",";
}
}
sb.Append(line);
fc++;
}
Console.WriteLine(sb.ToString());
String linestr = "(" + sb.ToString() + "),";
Console.WriteLine(linestr);
dblelist.Add(linestr);
}
String strr = ResultConvert.CovertListToString(dblelist);
Console.WriteLine(strr);
rdr.Close();
}
}
catch (MySqlException ex)
{
Console.WriteLine(ex.Number + ":" + ex.Message);
}
//object result = cmd.ExecuteScalar();
//if (result != null)
//{
// int r = Convert.ToInt32(result);
// Console.WriteLine(r);
//}
// MySqlDataReader rdr = cmd.ExecuteReader();
// List<String> dblelist = new List<string>();
// while (rdr.Read())
// {
// StringBuilder sb = new StringBuilder();
// String line;
// int fc = 0;
// for (int i = 0; i < rdr.FieldCount; i++)
// {
// Type type = rdr.GetFieldType(rdr.GetName(i));
// if (type.Name == "VarChar" || type.Name == "Char" || type.Name == "String" || type.Name == "Byte" || type.Name == "SByte")
// {
// if (fc == (rdr.FieldCount - 1))
// {
// line = "\"" + rdr.GetValue(i) + "\"";
// }
// else
// {
// line = "\"" + rdr.GetValue(i) + "\",";
// }
// }
// else if (type.Name == "DateTime")
// {
// if(fc == (rdr.FieldCount - 1))
// {
// line = "{" + rdr.GetValue(i) + "}";
// }
// else
// {
// line = "{" + rdr.GetValue(i) + "},";
// }
// }
// else
// {
// if (fc == (rdr.FieldCount - 1))
// {
// line = rdr.GetValue(i).ToString();
// }
// else
// {
// line = rdr.GetValue(i) + ",";
// }
// }
// sb.Append(line);
// fc++;
// }
// Console.WriteLine(sb.ToString());
// String linestr = "(" + sb.ToString() + "),";
// Console.WriteLine(linestr);
// dblelist.Add(linestr);
// }
// String strr = ResultConvert.CovertListToString(dblelist);
// Console.WriteLine(strr);
// rdr.Close();
//}
//catch (MySqlException ex)
//{
// Console.WriteLine(ex.Number + ":" + ex.Message);
//}
//catch (Exception e)
//{
// if (e is MySqlException)
// {
// var ee = e as MySqlException;
// //ee.Number;
// }
// e.ToString();
//}
//conn.Close();
//Console.WriteLine("Done.");
}
public static void Execute(String sqlfile, String[] logfiles, MySqlConnection dblemanagerconn)
{
//excute the sqls one by one
int idNum = 1;
string line;
StreamReader sr = null;
StreamWriter passsw = null;
StreamWriter failsw = null;
try
{
sr = new StreamReader(sqlfile);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamReader(sr);
}
//open log files
try
{
passsw = new StreamWriter(logfiles[0], true);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamWriter(passsw);
}
try
{
failsw = new StreamWriter(logfiles[1], true);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamWriter(failsw);
}
while ((line = sr.ReadLine()) != null)
{
if (!line.StartsWith("#"))
{
String exec = "===file:" + sqlfile + ",id:" + idNum + ",sql:" + line + "===";
line = line.ToLower();
//for kill command
//int PID = 0;
//if (line.StartsWith("kill"))
//{
// try
// {
// string showsql = "show @@connection;";
// MySqlCommand showcmd = new MySqlCommand(showsql, dblemanagerconn);
// MySqlDataReader showrdr = showcmd.ExecuteReader();
// //int PID = 0;
// while (showrdr.Read())
// {
// var pid = showrdr.GetValue(1);
// PID = Convert.ToInt32(pid);
// break;
// }
// showrdr.Close();
// }
// catch (MySqlException e) {
// }
//}
List <String> dblerslist = new List <string>();
try
{
MySqlCommand cmd = new MySqlCommand(line, dblemanagerconn);
if (dblemanagerconn.State != System.Data.ConnectionState.Open)
{
dblemanagerconn.Open();
}
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
StringBuilder sb = new StringBuilder();
String column;
int fc = 0;
for (int i = 0; i < rdr.FieldCount; i++)
{
Type type = rdr.GetFieldType(rdr.GetName(i));
if (type.Name == "VarChar" || type.Name == "Char" || type.Name == "String" || type.Name == "Byte" || type.Name == "SByte")
{
if (fc == (rdr.FieldCount - 1))
{
column = "\"" + rdr.GetValue(i) + "\"";
}
else
{
column = "\"" + rdr.GetValue(i) + "\",";
}
}
else if (type.Name == "DateTime")
{
if (fc == (rdr.FieldCount - 1))
{
column = "{" + rdr.GetValue(i) + "}";
}
else
{
column = "{" + rdr.GetValue(i) + "},";
}
}
else
{
if (fc == (rdr.FieldCount - 1))
{
column = rdr.GetValue(i).ToString();
}
else
{
column = rdr.GetValue(i) + ",";
}
}
sb.Append(column);
fc++;
}
String linestr = "(" + sb.ToString() + "),";
//Console.WriteLine(linestr);
dblerslist.Add(linestr);
}
rdr.Close();
//write to logs
passsw.WriteLine(exec);
String listStr = ResultConvert.CovertListToString(dblerslist);
passsw.WriteLine("dble: [" + listStr + "]");
}
catch (MySqlException e)
{
String errMsg = e.Message;
String errCode = Convert.ToString(e.ErrorCode);
String errStr = "dble: [(" + errCode + ": " + errMsg + ")]";
failsw.WriteLine(exec);
failsw.WriteLine(errStr);
}
}
idNum++;
}
//close the opened iostream
CleanUp.CloseStreamWriter(failsw);
CleanUp.CloseStreamWriter(passsw);
CleanUp.CloseStreamReader(sr);
}
public static void Execute(String sqlfile, String[] logfiles, MySqlConnection dbleconn, MySqlConnection mysqlconn)
{
//excute the sqls one by one
int idNum = 1;
string line;
StreamReader sr = null;
StreamWriter passsw = null;
StreamWriter failsw = null;
try
{
sr = new StreamReader(sqlfile);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamReader(sr);
Environment.Exit(-1);
}
//open the log files
try
{
passsw = new StreamWriter(logfiles[0], true);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamWriter(passsw);
Environment.Exit(-1);
}
try
{
failsw = new StreamWriter(logfiles[1], true);
}
catch (IOException ioe)
{
Console.WriteLine(ioe.Message);
CleanUp.CloseStreamWriter(failsw);
Environment.Exit(-1);
}
while ((line = sr.ReadLine()) != null)
{
if (!line.StartsWith("#"))
{
bool allow_diff = false;
if (line.Contains("allow_diff"))
{
allow_diff = true;
}
String exec = "===file:" + sqlfile + ",id:" + idNum + ",sql:" + line + "===";
line = line.ToLower().Trim();
//dble
List <String> dblerslist = new List <string>();
try
{
MySqlCommand dblecmd = new MySqlCommand(line, dbleconn);
if (dbleconn.State != System.Data.ConnectionState.Open)
{
dbleconn.Open();
}
if (line.Contains("insert") || line.Contains("update") || line.Contains("delete"))
{
int count = dblecmd.ExecuteNonQuery();
String countStr = Convert.ToString(count);
dblerslist.Add(countStr);
}
else if (line.StartsWith("select") || line.StartsWith("show") || line.StartsWith("check") || line.Contains("union"))
{
MySqlDataReader rdr = dblecmd.ExecuteReader();
while (rdr.Read())
{
StringBuilder sb = new StringBuilder();
String column;
int fc = 0;
for (int i = 0; i < rdr.FieldCount; i++)
{
Type type = rdr.GetFieldType(rdr.GetName(i));
if (type.Name == "VarChar" || type.Name == "Char" || type.Name == "String" || type.Name == "Byte" || type.Name == "SByte")
{
if (fc == (rdr.FieldCount - 1))
{
column = "\"" + rdr.GetValue(i) + "\"";
}
else
{
column = "\"" + rdr.GetValue(i) + "\",";
}
}
else if (type.Name == "DateTime")
{
if (fc == (rdr.FieldCount - 1))
{
column = "{" + rdr.GetValue(i) + "}";
}
else
{
column = "{" + rdr.GetValue(i) + "},";
}
}
else
{
if (fc == (rdr.FieldCount - 1))
{
column = rdr.GetValue(i).ToString();
}
else
{
column = rdr.GetValue(i) + ",";
}
}
sb.Append(column);
fc++;
}
String linestr = "(" + sb.ToString() + "),";
//Console.WriteLine(linestr);
dblerslist.Add(linestr);
}
rdr.Close();
}
else
{
object result = dblecmd.ExecuteScalar();
if (result == null)
{
dblerslist.Add("null");
}
else
{
String resultstr = Convert.ToString(result);
dblerslist.Add(resultstr);
}
}
}
catch (MySqlException ex)
{
String errMsg = ex.Number + ": " + ex.Message;
dblerslist.Add(errMsg);
}
//mysql
List <String> mysqlrslist = new List <string>();
try
{
MySqlCommand mysqlcmd = new MySqlCommand(line, mysqlconn);
if (mysqlconn.State != System.Data.ConnectionState.Open)
{
mysqlconn.Open();
}
if (line.Contains("insert") || line.Contains("update") || line.Contains("delete"))
{
int count = mysqlcmd.ExecuteNonQuery();
String countStr = Convert.ToString(count);
mysqlrslist.Add(countStr);
}
else if (line.StartsWith("select") || line.StartsWith("show") || line.StartsWith("check") || line.Contains("union"))
{
MySqlDataReader rdr = mysqlcmd.ExecuteReader();
while (rdr.Read())
{
StringBuilder sb = new StringBuilder();
String column;
int fc = 0;
for (int i = 0; i < rdr.FieldCount; i++)
{
Type type = rdr.GetFieldType(rdr.GetName(i));
if (type.Name == "VarChar" || type.Name == "Char" || type.Name == "String" || type.Name == "Byte" || type.Name == "SByte")
{
if (fc == (rdr.FieldCount - 1))
{
column = "\"" + rdr.GetValue(i) + "\"";
}
else
{
column = "\"" + rdr.GetValue(i) + "\",";
}
}
else if (type.Name == "DateTime")
{
if (fc == (rdr.FieldCount - 1))
{
column = "{" + rdr.GetValue(i) + "}";
}
else
{
column = "{" + rdr.GetValue(i) + "},";
}
}
else
{
if (fc == (rdr.FieldCount - 1))
{
column = rdr.GetValue(i).ToString();
}
else
{
column = rdr.GetValue(i) + ",";
}
}
sb.Append(column);
fc++;
}
String linestr = "(" + sb.ToString() + "),";
//Console.WriteLine(linestr);
mysqlrslist.Add(linestr);
}
rdr.Close();
}
else
{
object result = mysqlcmd.ExecuteScalar();
if (result == null)
{
mysqlrslist.Add("null");
}
else
{
String resultstr = Convert.ToString(result);
mysqlrslist.Add(resultstr);
}
}
}
catch (MySqlException ex)
{
String errMsg = ex.Number + ": " + ex.Message;
mysqlrslist.Add(errMsg);
}
//compare and write to logs
bool same = CompareRs.CompareRS(dblerslist, mysqlrslist, allow_diff);
if (same)
{
passsw.WriteLine(exec);
String pass = ResultConvert.CovertListToString(dblerslist);
String passstr = "dble: [(" + pass + ")]";
passsw.WriteLine(passstr);
}
else
{
String dblefailstr = ResultConvert.CovertListToString(dblerslist);
String mysqlfailstr = ResultConvert.CovertListToString(mysqlrslist);
String failstr = "dble: [(" + dblefailstr + ")]\r\nmysql: [(" + mysqlfailstr + ")]";
failsw.WriteLine(exec);
failsw.WriteLine(failstr);
}
}
idNum++;
}
//close the opened iostream
CleanUp.CloseStreamWriter(failsw);
CleanUp.CloseStreamWriter(passsw);
CleanUp.CloseStreamReader(sr);
}