public MySqlFunctionList(MySqlCommand cmd) { try { string dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();"); _sqlShowFunctions = string.Format("SHOW FUNCTION STATUS WHERE UPPER(TRIM(Db))= UPPER(TRIM('{0}'));", dbname); DataTable dt = QueryExpress.GetTable(cmd, _sqlShowFunctions); foreach (DataRow dr in dt.Rows) { var name = dr["Name"].ToString(); _lst.Add(name, new MySqlFunction(cmd, name, dr["Definer"].ToString())); } } catch (MySqlException myEx) { if (myEx.Message.ToLower().Contains("access denied")) { _allowAccess = false; } } catch { throw; } }
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) { _lst.Add(new MySqlProcedure(cmd, dr["Name"] + "", dr["Definer"] + "")); } } catch (MySqlException myEx) { if (myEx.Message.ToLower().Contains("access denied")) { _allowAccess = false; } } catch { throw; } }
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) { _lst.Add(new MySqlView(cmd, dr[0] + "")); } } catch (MySqlException myEx) { if (myEx.Message.ToLower().Contains("access denied")) { _allowAccess = false; } } catch { throw; } }
void Import_SetNames() { string setname = QueryExpress.ExecuteScalarStr(Command, "SHOW VARIABLES LIKE 'character_set_database';", 1); Command.CommandText = string.Format("/*!40101 SET NAMES {0} */;", setname); Command.ExecuteNonQuery(); _nameIsSet = true; }
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 MySqlTrigger(MySqlCommand cmd, string triggerName, string definer) { _name = triggerName; _createTriggerSQL = QueryExpress.ExecuteScalarStr(cmd, string.Format("SHOW CREATE TRIGGER `{0}`;", triggerName), 2); _createTriggerSQL = _createTriggerSQL.Replace("\r\n", "^~~~~~~~~~~~~~~^"); _createTriggerSQL = _createTriggerSQL.Replace("\n", "^~~~~~~~~~~~~~~^"); _createTriggerSQL = _createTriggerSQL.Replace("\r", "^~~~~~~~~~~~~~~^"); _createTriggerSQL = _createTriggerSQL.Replace("^~~~~~~~~~~~~~~^", "\r\n"); string[] sa = definer.Split('@'); definer = string.Format(" DEFINER=`{0}`@`{1}`", sa[0], sa[1]); _createTriggerSQLWithoutDefiner = _createTriggerSQL.Replace(definer, string.Empty); }
public MySqlEvent(MySqlCommand cmd, string eventName, string definer) { _name = eventName; _createEventSql = QueryExpress.ExecuteScalarStr(cmd, string.Format("SHOW CREATE EVENT `{0}`;", _name), "Create Event"); _createEventSql = _createEventSql.Replace("\r\n", "^~~~~~~~~~~~~~~^"); _createEventSql = _createEventSql.Replace("\n", "^~~~~~~~~~~~~~~^"); _createEventSql = _createEventSql.Replace("\r", "^~~~~~~~~~~~~~~^"); _createEventSql = _createEventSql.Replace("^~~~~~~~~~~~~~~^", "\r\n"); string[] sa = definer.Split('@'); definer = string.Format(" DEFINER=`{0}`@`{1}`", sa[0], sa[1]); _createEventSqlWithoutDefiner = _createEventSql.Replace(definer, string.Empty); }
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 MySqlFunction(MySqlCommand cmd, string functionName, string definer) { _name = functionName; string sql = string.Format("SHOW CREATE FUNCTION `{0}`;", functionName); _createFunctionSQL = QueryExpress.ExecuteScalarStr(cmd, sql, 2); _createFunctionSQL = _createFunctionSQL.Replace("\r\n", "^~~~~~~~~~~~~~~^"); _createFunctionSQL = _createFunctionSQL.Replace("\n", "^~~~~~~~~~~~~~~^"); _createFunctionSQL = _createFunctionSQL.Replace("\r", "^~~~~~~~~~~~~~~^"); _createFunctionSQL = _createFunctionSQL.Replace("^~~~~~~~~~~~~~~^", "\r\n"); string[] sa = definer.Split('@'); definer = string.Format(" DEFINER=`{0}`@`{1}`", sa[0], sa[1]); _createFunctionSqlWithoutDefiner = _createFunctionSQL.Replace(definer, string.Empty); }
public void GetDatabaseInfo(MySqlCommand cmd, bool getTotalRowsForEachTable) { _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 (getTotalRowsForEachTable) { GetTotalRows(cmd); } }
/// <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); }
public MySqlEventList(MySqlCommand cmd) { try { var dbname = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();"); SqlShowEvent = $"SHOW EVENTS WHERE UPPER(TRIM(Db))=UPPER(TRIM('{dbname}'));"; var dt = QueryExpress.GetTable(cmd, SqlShowEvent); foreach (DataRow dr in dt.Rows) { _lst.Add(new MySqlEvent(cmd, dr["Name"] + "", dr["Definer"] + "")); } } catch (MySqlException myEx) { if (myEx.Message.ToLower().Contains("access denied")) { AllowAccess = false; } } }
public void GetDatabaseInfo(MySqlCommand cmd, bool getTotalRowsForEachTable) { Name = QueryExpress.ExecuteScalarStr(cmd, "SELECT DATABASE();"); DefaultCharacterSet = QueryExpress.ExecuteScalarStr(cmd, "SHOW VARIABLES LIKE 'character_set_database';", 1); CreateDatabaseSql = QueryExpress.ExecuteScalarStr(cmd, $"SHOW CREATE DATABASE `{Name}`;", 1) .Replace("CREATE DATABASE", "CREATE DATABASE IF NOT EXISTS") + ";"; DropDatabaseSql = $"DROP DATABASE IF EXISTS `{Name}`;"; Tables = new MySqlTableList(cmd); Procedures = new MySqlProcedureList(cmd); Functions = new MySqlFunctionList(cmd); Triggers = new MySqlTriggerList(cmd); Events = new MySqlEventList(cmd); Views = new MySqlViewList(cmd); if (getTotalRowsForEachTable) { GetTotalRows(cmd); } }
public void GetDatabaseInfo(string conn, GetTotalRowsMethod enumGetTotalRowsMode) { MySqlCommand cmd = new MySqlCommand(); cmd.Connection = new MySqlConnection(conn); cmd.Connection.Open(); _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); cmd.Connection.Close(); // SELECT //CONCAT_WS(' ', // 'DELIMITER $$\n', // 'CREATE ', // CONCAT_WS('=', r.SECURITY_TYPE, CONCAT_WS('@', CONCAT('`', SUBSTRING_INDEX(r.`definer`, '@', 1), '`'), CONCAT('`', SUBSTRING_INDEX(r.`definer`, '@', -1), '`'))), // '\n', // r.ROUTINE_TYPE, // CONCAT_WS('.', ROUTINE_SCHEMA, ROUTINE_NAME), // CONCAT('(', GROUP_CONCAT(CONCAT_WS(' ', p.PARAMETER_NAME, p.DTD_IDENTIFIER)), ')'), // IF(o_p.DTD_IDENTIFIER is not null, CONCAT('RETURNS ', o_p.DTD_IDENTIFIER), ''), // ROUTINE_DEFINITION, // '$$') stmt_ FROM information_schema.routines r //LEFT JOIN information_schema.parameters p //ON p.SPECIFIC_SCHEMA = r.routine_schema AND //p.specific_name = r.routine_name AND p.PARAMETER_MODE = 'IN' //LEFT JOIN information_schema.parameters o_p ON o_p.SPECIFIC_SCHEMA = // r.routine_schema AND o_p.specific_name = r.routine_name AND o_p.PARAMETER_MODE // IS NULL WHERE ROUTINE_SCHEMA IN('grt_talentoz') //GROUP BY //CONCAT_WS('.', ROUTINE_SCHEMA, ROUTINE_NAME) var pro = Task.Factory.StartNew(() => { MySqlCommand cmd1 = new MySqlCommand(); cmd1.Connection = new MySqlConnection(conn); cmd1.Connection.Open(); _listProcedure = new MySqlProcedureList(cmd1); cmd1.Connection.Close(); }); var fn = Task.Factory.StartNew(() => { MySqlCommand cmd1 = new MySqlCommand(); cmd1.Connection = new MySqlConnection(conn); cmd1.Connection.Open(); _listFunction = new MySqlFunctionList(cmd1); cmd1.Connection.Close(); }); var trg = Task.Factory.StartNew(() => { MySqlCommand cmd1 = new MySqlCommand(); cmd1.Connection = new MySqlConnection(conn); cmd1.Connection.Open(); _listTrigger = new MySqlTriggerList(cmd1); cmd1.Connection.Close(); }); var evnt = Task.Factory.StartNew(() => { MySqlCommand cmd1 = new MySqlCommand(); cmd1.Connection = new MySqlConnection(conn); cmd1.Connection.Open(); _listEvent = new MySqlEventList(cmd1); cmd1.Connection.Close(); }); var vw = Task.Factory.StartNew(() => { MySqlCommand cmd1 = new MySqlCommand(); cmd1.Connection = new MySqlConnection(conn); cmd1.Connection.Open(); _listView = new MySqlViewList(cmd1); cmd1.Connection.Close(); }); Task.WaitAll(pro, fn, trg, evnt, vw); if (enumGetTotalRowsMode != GetTotalRowsMethod.Skip) { GetTotalRows(cmd, enumGetTotalRowsMode); } }