public void GerarArquivoDumpView(string view, string fileName)
{
try
{
using (_exportToFile = new ExportToFile(fileName))
{
_exportToFile.ExportHeaderDatabase();
_exportToFile.ExportWriteLine(string.Format("DROP TABLE IF EXISTS `{0}`;", view));
_exportToFile.ExportWriteLine(string.Format("DROP VIEW IF EXISTS `{0}`;", view));
_exportToFile.ExportWriteComment("");
_exportToFile.ExportWriteComment(string.Format("Definições da View '{0}'", view));
_exportToFile.ExportWriteComment("");
var sqlCreateView = FactorySql.SqlCreateView(view);
_exportToFile.ExportWriteLine(sqlCreateView);
_exportToFile.Export_EndInfo();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public void GerarArquivoDumpTrigger(string trigger, string fileName)
{
try
{
using (_exportToFile = new ExportToFile(fileName))
{
var queryExpress = VariaveisGeral.Container.GetInstance <IQueryExpress>();
var sqlDefiner = FactorySql.SqlGetAllTriggers;
var definer = queryExpress.ExecuteScalarString(sqlDefiner, "Definer");
_exportToFile.ExportHeaderDatabase();
_exportToFile.ExportWriteLine(string.Format("DROP TRIGGER `{0}`;", trigger));
_exportToFile.ExportWriteComment("");
_exportToFile.ExportWriteComment(string.Format("Definições da Trigger '{0}'", trigger));
_exportToFile.ExportWriteComment("");
var sqlCreateTrigger = FactorySql.SqlCreateTrigger(trigger: trigger, definer: definer) + ";";
_exportToFile.ExportWriteLine(sqlCreateTrigger);
_exportToFile.Export_EndInfo();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void setFailConn()
{
ISql conn = fooCon;
FactorySql factory = new FactorySql();
factory.SetManager(conn);
}
public void GerarArquivoDumpEvent(string eventName, string fileName)
{
try
{
using (_exportToFile = new ExportToFile(fileName))
{
var queryExpress = VariaveisGeral.Container.GetInstance <IQueryExpress>();
var sqlDefiner = FactorySql.SqlGetAllEvents();
var definer = queryExpress.ExecuteScalarString(sqlDefiner, "Definer");
_exportToFile.ExportHeaderDatabase();
_exportToFile.ExportWriteLine(string.Format("DROP EVENT IF EXISTS `{0}`;", eventName));
_exportToFile.ExportWriteComment("");
_exportToFile.ExportWriteComment(string.Format("Definições do Event '{0}'", eventName));
_exportToFile.ExportWriteComment("");
var sqlCreateProcedure = FactorySql.SqlCreateEvent(eventName: eventName, definer: definer) + ";";
_exportToFile.ExportWriteLine(sqlCreateProcedure);
_exportToFile.Export_EndInfo();
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
public bool GerarArquivoCriacaoTable(string table, string fileName)
{
try
{
using (_exportToFile = new ExportToFile(fileName))
{
_exportToFile.ExportHeaderDatabase();
_exportToFile.ExportWriteLine();
_exportToFile.ExportLinhaTracejada();
_exportToFile.ExportWrite(string.Format("USE {0};", VariaveisGeral.Database));
_exportToFile.ExportLinhaTracejada();
var headerTable = HeaderTable();
_exportToFile.ExportLinhaTracejada();
_exportToFile.ExportWrite(headerTable);
_exportToFile.ExportLinhaTracejada();
var sqlDropIfExiste = SqlDropTableIfExiste(table);
_exportToFile.ExportWrite(sqlDropIfExiste);
_exportToFile.ExportLinhaTracejada();
var sqlCreate = FactorySql.SqlCreateTable(table);
_exportToFile.ExportWrite(sqlCreate);
_exportToFile.ExportWriteLine();
_exportToFile.ExportLinhaTracejada();
_exportToFile.Export_EndInfo();
}
}
catch (Exception e)
{
Console.Write(e);
return(false);
}
return(true);
}
private void connect()
{
if (sql == null)
{
sql = FactorySql.Create(sqlprovider);
}
if (password != string.Empty && !passEstablished)
{
sqlConnectionString += password;
passEstablished = true;
}
conn = sql.Connection(sqlConnectionString);
conn.Open();
}
public IEnumerable <MySqlConstraint> RelacaoConstraintsTable(string table)
{
var sql = FactorySql.SqlGetConstraints();
var result = new List <MySqlConstraint>();
//var tableIndex = QueryExpress.GetTable(sql);
var tableIndex = new DataTable();
for (var i = 0; i < tableIndex.Rows.Count; i++)
{
var constraintName = tableIndex.Rows[i][0].ToString();
var tableName = tableIndex.Rows[i][1].ToString();
var constraintType = tableIndex.Rows[i][2].ToString().ToUpper();
var constraint =
new MySqlConstraint()
{
ConstraintName = constraintName, TableName = tableName
};
switch (constraintType)
{
case "PRIMARY KEY":
constraint.ConstraintType = ConstraintType.PrimaryKey;
break;
case "UNIQUE":
constraint.ConstraintType = ConstraintType.Unique;
break;
case "FOREIGN KEY":
constraint.ConstraintType = ConstraintType.ForeignKey;
break;
default:
constraint.ConstraintType = ConstraintType.Desconhecido;
break;
}
result.Add(constraint);
}
return(result);
}
public IEnumerable <MySqlForeignKey> RelacaoForeignKey(string table)
{
var result = new List <MySqlForeignKey>();
try
{
var commandText = FactorySql.SqlGetAllForeignKey();
var tableIndex = _queryExpress.GetTable(commandText);
for (var i = 0; i < tableIndex.Rows.Count; i++)
{
var indexName = tableIndex.Rows[i][0].ToString();
var indexColummNameLocal = tableIndex.Rows[i][1].ToString();
var tableReferencedName = tableIndex.Rows[i][2].ToString();
var indexColummReferenced = tableIndex.Rows[i][3].ToString();
var tableLocalName = tableIndex.Rows[i][6].ToString();
var idx = new MySqlForeignKey
{
IndexName = indexName,
IndexColummNameLocal = indexColummNameLocal,
TableReferencedName = tableReferencedName,
IndexColummReferenced = indexColummReferenced,
TableLocalName = tableLocalName
};
idx.OnDelete = idx.SelecionarReferentialAction(tableIndex.Rows[i][4].ToString());
idx.OnUpdate = idx.SelecionarReferentialAction(tableIndex.Rows[i][5].ToString());
result.Add(idx);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(result);
}
/// <summary>
/// Conecta a la base de datos mediante una única conexión.
/// </summary>
/// <param name="connStringName">Nombre de la connectionString que está definida en el archivo de configuración de la aplicación.</param>
static public void Connect(string connStringName)
{
if (uniqueconn != null)
{
string sqlConnString = ConfigurationManager.ConnectionStrings [connStringName].ConnectionString;
string sqlProvider = ConfigurationManager.ConnectionStrings [connStringName].ProviderName;
ISql consql = FactorySql.Create(sqlProvider);
if (password != string.Empty)
{
sqlConnString += password;
}
uniqueconn = consql.Connection(sqlConnString);
uniqueconn.Open();
}
}
private void InitBD()
{
string connString = ConfigurationManager.ConnectionStrings["GebatDataConnectionString"].ConnectionString;
connString += "Password=root";
string provider = ConfigurationManager.ConnectionStrings ["GebatDataConnectionString"].ProviderName;
ISql manager = FactorySql.Create(provider);
FileInfo file = new FileInfo(scriptfilename);
StreamReader lector = file.OpenText();
string script = lector.ReadToEnd();
lector.Close();
DbConnection conn = manager.Connection(connString);
conn.Open();
DbCommand comando = manager.Command(script, conn);
comando.ExecuteNonQuery();
conn.Close();
}
public IEnumerable <string> RelacaoNomeViews()
{
try
{
var commandText = FactorySql.SqlGetAllViews();
try
{
var listQueries = _queryExpress.ExecuteReader(commandText, 0).ToList();
return(listQueries.OrderBy(x => x).ToList());
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <string>());
}
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <string>());
}
}
public IEnumerable <string> RelacaoNomeEvents()
{
try
{
var sql = FactorySql.SqlGetAllEvents();
try
{
var listFunctions = _queryExpress.ExecuteReader(sql, "name").ToList();
return(listFunctions.OrderBy(x => x).ToList());
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <string>());
}
}
catch (Exception e)
{
Console.WriteLine(e);
return(new List <string>());
}
}
static public bool AttemptConnection(string connStringName)
{
try
{
string trysqlConnectionString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;
string trysqlprovider = ConfigurationManager.ConnectionStrings [connStringName].ProviderName;
ISql tryisql = FactorySql.Create(trysqlprovider);
if (password != string.Empty)
{
trysqlConnectionString += password;
}
DbConnection tryconn = tryisql.Connection(trysqlConnectionString);
tryconn.Open();
tryconn.Close();
return(true);
}
catch (Exception ex)
{
return(false);
}
}
public bool GerarArquivoDumpTable(string table, string fileName)
{
try
{
using (_exportToFile = new ExportToFile(fileName))
{
_exportToFile.ExportHeaderDatabase();
_exportToFile.ExportWriteComment("");
_exportToFile.ExportWriteComment(string.Format("Definições da table '{0}'", table));
_exportToFile.ExportWriteComment("");
_exportToFile.ExportWriteLine();
_exportToFile.ExportWriteLine();
_exportToFile.ExportLinhaTracejada();
_exportToFile.ExportWrite(string.Format("USE {0};", VariaveisGeral.Database));
_exportToFile.ExportLinhaTracejada();
var headerTable = HeaderTable();
_exportToFile.ExportLinhaTracejada();
_exportToFile.ExportWrite(headerTable);
_exportToFile.ExportLinhaTracejada();
var sqlDropIfExiste = SqlDropTableIfExiste(table);
_exportToFile.ExportWrite(sqlDropIfExiste);
_exportToFile.ExportWriteLine();
var sqlCreate = FactorySql.SqlCreateTable(table);
_exportToFile.ExportWrite(sqlCreate);
_exportToFile.ExportWriteLine();
_exportToFile.ExportWriteLine("/*!40101 SET character_set_client = @saved_cs_client */;");
_exportToFile.ExportWriteLine();
_exportToFile.ExportLinhaTracejada();
ExportarLinhasTable(_exportToFile, table);
ExportEndDump();
_exportToFile.Export_EndInfo();
}
}
catch (Exception e)
{
Console.Write(e);
return(false);
}
return(true);
}
public IEnumerable <MySqlIndex> RelacaoIndexTable(string table)
{
var result = new List <MySqlIndex>();
try
{
var commandText = FactorySql.SqlGetAllIndexFromTable(table);
var tableIndex = _queryExpress.GetTable(commandText);
for (var i = 0; i < tableIndex.Rows.Count; i++)
{
var keyName = tableIndex.Rows[i][2].ToString();
var seqInIndex = Convert.ToInt16(tableIndex.Rows[i][3].ToString());
var columnName = tableIndex.Rows[i][4].ToString();
var comment = tableIndex.Rows[i][11].ToString();
var indexComment = tableIndex.Rows[i][12].ToString();
var nonUnique = tableIndex.Rows[i][1].ToString() == "0";
var tipoIndexSort = tableIndex.Rows[i][5].ToString();
var nullAble = tableIndex.Rows[i][8].ToString() != string.Empty;
var indexType = tableIndex.Rows[i][10].ToString();
var idx = new MySqlIndex
{
KeyName = keyName,
SeqInIndex = seqInIndex,
ColumnName = columnName,
Comment = comment,
IndexComment = indexComment,
NonUnique = nonUnique,
};
idx.PrimaryKey = string.Equals(idx.KeyName.ToUpper(), "PRIMARY".ToUpper(),
StringComparison.OrdinalIgnoreCase);
switch (tipoIndexSort)
{
case "A":
idx.Collation = TipoIndexSort.Ascending;
break;
case "D":
idx.Collation = TipoIndexSort.Descending;
break;
default:
idx.Collation = TipoIndexSort.NotSorted;
break;
}
idx.NullAble = nullAble;
switch (indexType)
{
case "BTREE":
idx.IndexType = TipoIndex.Btree;
break;
case "FULLTEXT":
idx.IndexType = TipoIndex.Fulltext;
break;
case "HASH":
idx.IndexType = TipoIndex.Hash;
break;
case "RTREE":
idx.IndexType = TipoIndex.Rtree;
break;
default:
idx.IndexType = TipoIndex.Desconhecido;
break;
}
result.Add(idx);
}
}
catch (Exception e)
{
Console.WriteLine(e);
}
return(result);
}
public void IncluirIndex(string table)
{
var mySqlDatabaseRepository = VariaveisGeral.Container.GetInstance <IMySqlDatabaseRepository>();
var listIndexGeral = mySqlDatabaseRepository.RelacaoIndexTable(table);
var listIndex = listIndexGeral.Where(x => !x.PrimaryKey && !x.NonUnique).ToList();
var listConstraints = mySqlDatabaseRepository.RelacaoConstraintsTable(table);
var listIndexIncluir =
(from i in listIndex
join con in listConstraints on i.KeyName equals con.ConstraintName into All
from al in All.DefaultIfEmpty()
select new { i.KeyName }).ToList();
var listForeignKeys = mySqlDatabaseRepository.RelacaoForeignKey(table).ToList();
var sqlCreate = FactorySql.SqlCreateTable(table);
var listIndexDdl = sqlCreate.Split('\n').Where(x => x.Contains("KEY") && !x.Contains("UNIQUE KEY") && !x.Contains("PRIMARY KEY")).ToList();
var addKey = string.Empty;
var listAddIndex = new List <string>();
foreach (var item in listIndexIncluir)
{
if (listForeignKeys.Any(x => x.IndexColummNameLocal.Contains(item.KeyName)))
{
continue;
}
addKey = listIndexDdl.FirstOrDefault(x => x.Contains(item.KeyName));
if (addKey != null && addKey.EndsWith("ENGINE"))
{
var addKeyRemove = addKey.Split('\n').Last();
addKey = addKey.Replace(addKeyRemove, "");
}
if (addKey != null && addKey.EndsWith(","))
{
addKey = addKey.Substring(0, addKey.Length - 1);
}
if (addKey == null)
{
continue;
}
var stringAddKey = string.Format("ALTER TABLE `{0}` ADD {1} ;", table, addKey);
if (listAddIndex.Contains(stringAddKey))
{
continue;
}
listAddIndex.Add(stringAddKey);
}
foreach (var dropIndex in listAddIndex)
{
_exportToFile.ExportWriteLine(dropIndex);
}
}
private void ResetConn()
{
FactorySql factory = new FactorySql();
factory.ResetManager();
}