Ejemplo n.º 1
0
        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.");
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }