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);
}
}
