예제 #1
0
        public MySqlProcedureList(MySqlCommand cmd)
        {
            try
            {
                string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
                _sqlShowProcedures = string.Format("SHOW PROCEDURE STATUS WHERE UPPER(TRIM(Db))= UPPER(TRIM('{0}'));", dbname);
                DataTable dt = QueryExpress.GetTable(cmd, _sqlShowProcedures);

                foreach (DataRow dr in dt.Rows)
                {
                    var name = dr["Name"].ToString();
                    _lst.Add(name, new MySqlProcedure(cmd, name, dr["Definer"].ToString()));
                }
            }
            catch (MySqlException myEx)
            {
                if (myEx.Message.ToLower().Contains("access denied"))
                {
                    _allowAccess = false;
                }
            }
            catch
            {
                throw;
            }
        }
예제 #2
0
        public MySqlViewList(MySqlCommand cmd)
        {
            try
            {
                string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
                _sqlShowViewList = string.Format("SHOW FULL TABLES FROM `{0}` WHERE Table_type = 'VIEW';", dbname);
                DataTable dt = QueryExpress.GetTable(cmd, _sqlShowViewList);

                foreach (DataRow dr in dt.Rows)
                {
                    var nome = dr[0].ToString();
                    _lst.Add(nome, new MySqlView(cmd, nome));
                }
            }
            catch (MySqlException myEx)
            {
                if (myEx.Message.ToLower().Contains("access denied"))
                {
                    _allowAccess = false;
                }
            }
            catch
            {
                throw;
            }
        }
        public MySqlTriggerList(MySqlCommand cmd)
        {
            _sqlShowTriggers = "SHOW TRIGGERS;";
            try
            {
                DataTable dt = QueryExpress.GetTable(cmd, _sqlShowTriggers);

                foreach (DataRow dr in dt.Rows)
                {
                    var name = dr["Trigger"].ToString();
                    _lst.Add(name, new MySqlTrigger(cmd, name, dr["Definer"].ToString()));
                }
            }
            catch (MySqlException myEx)
            {
                if (myEx.Message.ToLower().Contains("access denied"))
                {
                    _allowAccess = false;
                }
            }
            catch
            {
                throw;
            }
        }
        public MySqlColumnList(MySqlCommand cmd, string tableName)
        {
            _tableName = tableName;
            DataTable dtDataType = QueryExpress.GetTable(cmd, string.Format("SELECT * FROM  `{0}` where 1 = 2;", tableName));

            _sqlShowFullColumns = string.Format("SHOW FULL COLUMNS FROM `{0}`;", tableName);
            DataTable dtColInfo = QueryExpress.GetTable(cmd, _sqlShowFullColumns);

            for (int i = 0; i < dtDataType.Columns.Count; i++)
            {
                string isNullStr = (dtColInfo.Rows[i]["Null"] + "").ToLower();
                bool   isNull    = false;
                if (isNullStr == "yes")
                {
                    isNull = true;
                }

                var name = dtDataType.Columns[i].ColumnName;
                _lst.Add(
                    name,
                    new MySqlColumn(
                        name,
                        dtDataType.Columns[i].DataType,
                        dtColInfo.Rows[i]["Type"] + "",
                        dtColInfo.Rows[i]["Collation"] + "",
                        isNull,
                        dtColInfo.Rows[i]["Key"] + "",
                        dtColInfo.Rows[i]["Default"] + "",
                        dtColInfo.Rows[i]["Extra"] + "",
                        dtColInfo.Rows[i]["Privileges"] + "",
                        dtColInfo.Rows[i]["Comment"] + "")
                    );
            }
        }
예제 #5
0
        private void Export_GetConditionString(MySqlDataReader rdr, MySqlTable table, StringBuilder sb)
        {
            bool isFirst = true;

            for (int i = 0; i < rdr.FieldCount; i++)
            {
                string colName = rdr.GetName(i);

                var col = table.Columns[colName];

                if (col.IsPrimaryKey)
                {
                    if (isFirst)
                    {
                        isFirst = false;
                    }
                    else
                    {
                        sb.Append(" and ");
                    }

                    sb.Append("`");
                    sb.Append(colName);
                    sb.Append("`=");
                    //sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
                    sb.Append(QueryExpress.ConvertToSqlFormat(rdr[i], true, true, col, ExportInfo.BlobExportMode));
                }
            }
        }
예제 #6
0
        private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)
        {
            StringBuilder sb = new StringBuilder();

            for (int i = 0; i < rdr.FieldCount; i++)
            {
                string columnName = rdr.GetName(i);

                if (table.Columns[columnName].IsGeneratedColumn)
                {
                    continue;
                }

                if (sb.Length == 0)
                {
                    sb.AppendFormat("(");
                }
                else
                {
                    sb.AppendFormat(",");
                }


                object ob  = rdr[i];
                var    col = table.Columns[columnName];

                //sb.Append(QueryExpress.ConvertToSqlFormat(rdr, i, true, true, col));
                sb.Append(QueryExpress.ConvertToSqlFormat(ob, true, true, col, ExportInfo.BlobExportMode));
            }

            sb.AppendFormat(")");
            return(sb.ToString());
        }
예제 #7
0
        public MySqlTable(MySqlCommand cmd, string name)
        {
            _name = name;
            string sql = string.Format("SHOW CREATE TABLE `{0}`;", name);

            _createTableSql = QueryExpress.ExecuteScalarStr(cmd, sql, 1).Replace(Environment.NewLine, "^~~~~~~^").Replace("\r", "^~~~~~~^").Replace("\n", "^~~~~~~^").Replace("^~~~~~~^", Environment.NewLine).Replace("CREATE TABLE ", "CREATE TABLE IF NOT EXISTS ") + ";";
            _createTableSqlWithoutAutoIncrement = RemoveAutoIncrement(_createTableSql);
            _lst = new MySqlColumnList(cmd, name);
            GetInsertStatementHeaders();
        }
        public MySqlTableList(MySqlCommand cmd)
        {
            _sqlShowFullTables = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';";
            DataTable dtTableList = QueryExpress.GetTable(cmd, _sqlShowFullTables);

            foreach (DataRow dr in dtTableList.Rows)
            {
                var table = new MySqlTable(cmd, dr[0] + "");
                _lst.Add(table.Name, table);
            }
        }
예제 #9
0
        public void GetServerInfo(MySqlCommand cmd)
        {
            _edition                = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'version_comment';", 1);
            _versionNumber          = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'version';", 1);
            _characterSetServer     = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_server';", 1);
            _characterSetSystem     = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_system';", 1);
            _characterSetConnection = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_connection';", 1);
            _characterSetDatabase   = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_database';", 1);

            _currentUserClientHost = QueryExpress.ExecuteScalarStr(cmd, "SELECT current_user;");

            string[] ca = _currentUserClientHost.Split('@');

            _currentUser       = ca[0];
            _currentClientHost = ca[1];

            GetMajorVersionNumber();
        }
        public MySqlProcedure(MySqlCommand cmd, string procedureName, string definer)
        {
            _name = procedureName;

            string sql = string.Format("SHOW CREATE PROCEDURE `{0}`;", procedureName);

            _createProcedureSQL = QueryExpress.ExecuteScalarStr(cmd, sql, 2);

            _createProcedureSQL = _createProcedureSQL.Replace("\r\n", "^~~~~~~~~~~~~~~^");
            _createProcedureSQL = _createProcedureSQL.Replace("\n", "^~~~~~~~~~~~~~~^");
            _createProcedureSQL = _createProcedureSQL.Replace("\r", "^~~~~~~~~~~~~~~^");
            _createProcedureSQL = _createProcedureSQL.Replace("^~~~~~~~~~~~~~~^", "\r\n");

            string[] sa = definer.Split('@');
            definer = string.Format(" DEFINER=`{0}`@`{1}`", sa[0], sa[1]);

            _createProcedureSQLWithoutDefiner = _createProcedureSQL.Replace(definer, string.Empty);
        }
        public void GetTotalRows(MySqlCommand cmd, GetTotalRowsMethod enumGetTotalRowsMode)
        {
            int i     = 0;
            var timer = new Timer
            {
                Interval = 10000
            };

            timer.Elapsed += (sender, e) =>
            {
                GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, i));
            };

            if (enumGetTotalRowsMode == GetTotalRowsMethod.InformationSchema)
            {
                DataTable dtTotalRows = QueryExpress.GetTable(cmd, string.Format("SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = '{0}';", _name));
                timer.Start();
                foreach (DataRow dr in dtTotalRows.Rows)
                {
                    i++;
                    var _tbname = dr["TABLE_NAME"] + "";
                    long.TryParse(dr["TABLE_ROWS"] + "", out var _totalRowsThisTable);

                    if (_listTable.Contains(_tbname))
                    {
                        _listTable[_tbname].SetTotalRows((long)(_totalRowsThisTable * 1.1)); // Adiciona 10% de erro
                    }
                }
                timer.Stop();
                GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, _listTable.Count));
            }
            else if (enumGetTotalRowsMode == GetTotalRowsMethod.SelectCount)
            {
                timer.Start();
                foreach (var table in _listTable)
                {
                    i++;
                    table.GetTotalRowsByCounting(cmd);
                }
                timer.Stop();
                GetTotalRowsProgressChanged?.Invoke(this, new GetTotalRowsArgs(_listTable.Count, _listTable.Count));
            }
        }
        public void GetDatabaseInfo(MySqlCommand cmd, GetTotalRowsMethod enumGetTotalRowsMode)
        {
            _name              = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();");
            _defaultCharSet    = QueryExpress.ExecuteScalarStr(cmd, "SHOW VARIABLES LIKE 'character_set_database';", 1);
            _createDatabaseSql = QueryExpress.ExecuteScalarStr(cmd, string.Format("SHOW CREATE DATABASE `{0}`;", _name), 1).Replace("CREATE DATABASE", "CREATE DATABASE IF NOT EXISTS") + ";";
            _dropDatabaseSql   = string.Format("DROP DATABASE IF EXISTS `{0}`;", _name);

            _listTable     = new MySqlTableList(cmd);
            _listProcedure = new MySqlProcedureList(cmd);
            _listFunction  = new MySqlFunctionList(cmd);
            _listTrigger   = new MySqlTriggerList(cmd);
            _listEvent     = new MySqlEventList(cmd);
            _listView      = new MySqlViewList(cmd);

            if (enumGetTotalRowsMode != GetTotalRowsMethod.Skip)
            {
                GetTotalRows(cmd, enumGetTotalRowsMode);
            }
        }
예제 #13
0
        /// <summary>
        /// Gets the list of document headers.
        /// </summary>
        /// <param name="cmd">The MySqlCommand that will be used to retrieve the database default character set.</param>
        /// <returns>List of document headers.</returns>
        public List <string> GetDocumentHeaders(MySqlCommand cmd)
        {
            if (_documentHeaders == null)
            {
                _documentHeaders = new List <string>();
                string databaseCharSet = QueryExpress.ExecuteScalarStr(cmd, "SHOW variables LIKE 'character_set_database';", 1);

                _documentHeaders.Add("/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;");
                _documentHeaders.Add("/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;");
                _documentHeaders.Add("/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;");
                _documentHeaders.Add(string.Format("/*!40101 SET NAMES {0} */;", databaseCharSet));
                //_documentHeaders.Add("/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;");
                //_documentHeaders.Add("/*!40103 SET TIME_ZONE='+00:00' */;");
                _documentHeaders.Add("/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;");
                _documentHeaders.Add("/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;");
                _documentHeaders.Add("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;");
                _documentHeaders.Add("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;");
            }

            return(_documentHeaders);
        }
예제 #14
0
        public static string ConvertToSqlFormat(object ob, bool wrapStringWithSingleQuote, bool escapeStringSequence, MySqlColumn col, BlobDataExportMode blobExportMode)
        {
            StringBuilder sb = new StringBuilder();

            if (ob == null || ob is System.DBNull)
            {
                sb.AppendFormat("NULL");
            }
            else if (ob is System.String)
            {
                string str = (string)ob;

                if (escapeStringSequence)
                {
                    str = QueryExpress.EscapeStringSequence(str);
                }

                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }

                sb.Append(str);

                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }
            }
            else if (ob is System.Boolean)
            {
                sb.AppendFormat(Convert.ToInt32(ob).ToString());
            }
            else if (ob is System.Byte[])
            {
                if (((byte[])ob).Length == 0)
                {
                    if (wrapStringWithSingleQuote)
                    {
                        return("''");
                    }
                    else
                    {
                        return("");
                    }
                }
                else
                {
                    if (blobExportMode == BlobDataExportMode.HexString)
                    {
                        sb.AppendFormat(CryptoExpress.ConvertByteArrayToHexString((byte[])ob));
                    }
                    else if (blobExportMode == BlobDataExportMode.BinaryChar)
                    {
                        if (wrapStringWithSingleQuote)
                        {
                            sb.Append("'");
                        }

                        foreach (byte b in ((byte[])ob))
                        {
                            char ch = (char)b;
                            escape_string(sb, ch);
                        }

                        if (wrapStringWithSingleQuote)
                        {
                            sb.Append("'");
                        }
                    }
                }
            }
            else if (ob is short)
            {
                sb.AppendFormat(((short)ob).ToString(_numberFormatInfo));
            }
            else if (ob is int)
            {
                sb.AppendFormat(((int)ob).ToString(_numberFormatInfo));
            }
            else if (ob is long)
            {
                sb.AppendFormat(((long)ob).ToString(_numberFormatInfo));
            }
            else if (ob is ushort)
            {
                sb.AppendFormat(((ushort)ob).ToString(_numberFormatInfo));
            }
            else if (ob is uint)
            {
                sb.AppendFormat(((uint)ob).ToString(_numberFormatInfo));
            }
            else if (ob is ulong)
            {
                sb.AppendFormat(((ulong)ob).ToString(_numberFormatInfo));
            }
            else if (ob is double)
            {
                sb.AppendFormat(((double)ob).ToString(_numberFormatInfo));
            }
            else if (ob is decimal)
            {
                sb.AppendFormat(((decimal)ob).ToString(_numberFormatInfo));
            }
            else if (ob is float)
            {
                sb.AppendFormat(((float)ob).ToString(_numberFormatInfo));
            }
            else if (ob is byte)
            {
                sb.AppendFormat(((byte)ob).ToString(_numberFormatInfo));
            }
            else if (ob is sbyte)
            {
                sb.AppendFormat(((sbyte)ob).ToString(_numberFormatInfo));
            }
            else if (ob is TimeSpan)
            {
                TimeSpan ts = (TimeSpan)ob;

                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }

                sb.AppendFormat(((int)ts.TotalHours).ToString().PadLeft(2, '0'));
                sb.AppendFormat(":");
                sb.AppendFormat(ts.Duration().Minutes.ToString().PadLeft(2, '0'));
                sb.AppendFormat(":");
                sb.AppendFormat(ts.Duration().Seconds.ToString().PadLeft(2, '0'));

                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }
            }
            else if (ob is System.DateTime)
            {
                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }

                sb.AppendFormat(((DateTime)ob).ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

                if (col.TimeFractionLength > 0)
                {
                    sb.Append(".");
                    string _microsecond = ((DateTime)ob).ToString("".PadLeft(col.TimeFractionLength, 'f'));
                    sb.Append(_microsecond);
                }

                if (wrapStringWithSingleQuote)
                {
                    sb.AppendFormat("'");
                }
            }
            //else if (ob is MySql.Data.Types.MySqlDateTime)
            //{
            //    MySql.Data.Types.MySqlDateTime mdt = (MySql.Data.Types.MySqlDateTime)ob;

            //    if (mdt.IsNull)
            //    {
            //        sb.AppendFormat("NULL");
            //    }
            //    else
            //    {
            //        if (mdt.IsValidDateTime)
            //        {
            //            DateTime dtime = mdt.Value;

            //            if (wrapStringWithSingleQuote)
            //                sb.AppendFormat("'");

            //            if (col.MySqlDataType == "datetime")
            //                sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
            //            else if (col.MySqlDataType == "date")
            //                sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
            //            else if (col.MySqlDataType == "time")
            //                sb.AppendFormat("{0}:{1}:{2}", mdt.Hour, mdt.Minute, mdt.Second);
            //            //sb.AppendFormat(dtime.ToString("HH:mm:ss", _dateFormatInfo));
            //            else
            //                sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

            //            if (col.TimeFractionLength > 0)
            //            {
            //                sb.Append(".");
            //                sb.Append(((MySql.Data.Types.MySqlDateTime)ob).Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
            //            }

            //            if (wrapStringWithSingleQuote)
            //                sb.AppendFormat("'");
            //        }
            //        else
            //        {
            //            if (wrapStringWithSingleQuote)
            //                sb.AppendFormat("'");

            //            if (col.MySqlDataType == "datetime")
            //                sb.AppendFormat("0000-00-00 00:00:00");
            //            else if (col.MySqlDataType == "date")
            //                sb.AppendFormat("0000-00-00");
            //            else if (col.MySqlDataType == "time")
            //                sb.AppendFormat("00:00:00");
            //            else
            //                sb.AppendFormat("0000-00-00 00:00:00");

            //            if (col.TimeFractionLength > 0)
            //            {
            //                sb.Append(".".PadRight(col.TimeFractionLength, '0'));
            //            }

            //            if (wrapStringWithSingleQuote)
            //                sb.AppendFormat("'");
            //        }
            //    }
            //}
            else if (ob is System.Guid)
            {
                if (col.MySqlDataType == "binary(16)")
                {
                    sb.Append(CryptoExpress.ConvertByteArrayToHexString(((Guid)ob).ToByteArray()));
                }
                else if (col.MySqlDataType == "char(36)")
                {
                    if (wrapStringWithSingleQuote)
                    {
                        sb.AppendFormat("'");
                    }

                    sb.Append(ob);

                    if (wrapStringWithSingleQuote)
                    {
                        sb.AppendFormat("'");
                    }
                }
                else
                {
                    if (wrapStringWithSingleQuote)
                    {
                        sb.AppendFormat("'");
                    }

                    sb.Append(ob);

                    if (wrapStringWithSingleQuote)
                    {
                        sb.AppendFormat("'");
                    }
                }
            }
            else
            {
                throw new Exception("Unhandled data type. Current processing data type: " + ob.GetType().ToString() + ". Please report this bug with this message to the development team.");
            }
            return(sb.ToString());
        }
예제 #15
0
        public void GetTotalRowsByCounting(MySqlCommand cmd)
        {
            string sql = string.Format("SELECT COUNT(1) FROM `{0}`;", _name);

            _totalRows = QueryExpress.ExecuteScalarLong(cmd, sql);
        }