public static SqlConnection RetornaConexaoBase(DadosUsuario usr)
{
return usr.TrustedConnection
? new SqlConnection(string.Format(TrustedStringConnection, usr.Servidor, usr.DataBase))
: new SqlConnection(string.Format(StringConnection, usr.Servidor, usr.DataBase, usr.Usuario, usr.Senha));
}
/// <summary>
/// Retorna todas as Stored Procedures do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllViews(string dataBaseName, DadosUsuario usr)
{
var clsTables = new List<DadosObjeto>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var cmd = new MySqlCommand(string.Format("set global innodb_stats_on_metadata=0;SELECT TABLE_NAME, CURRENT_TIMESTAMP as CREATE_TIME, CURRENT_TIMESTAMP as UPDATE_TIME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '{0}' ORDER BY TABLE_NAME ASC;", dataBaseName), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto
{
Nome = dr[0].ToString(),
DataCriacao = Convert.ToDateTime(dr[1]),
DataAlteracao = Convert.ToDateTime(dr[2]),
Tipo = "view"
});
}
}
return clsTables;
}
}
/// <summary>
/// Retorna todas as tabelas do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllTables(string dataBaseName, DadosUsuario usr)
{
var clsTables = new List<DadosObjeto>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var cmd = new SqlCommand("SELECT name AS Nome, create_date AS DataCriacao, modify_date AS DataAlteracao,(SELECT TOP 1 st.row_count FROM sys.dm_db_partition_stats st WHERE OBJECT_NAME(OBJECT_ID)=sys.tables.name) as Qtd FROM sys.tables ORDER BY name", cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto
{
Nome = dr[0].ToString(),
DataCriacao = Convert.ToDateTime(dr[1]),
DataAlteracao = Convert.ToDateTime(dr[2]),
Tipo = "tabela",
QtdRegistros = Convert.ToInt32(dr[3])
});
}
}
}
return clsTables;
}
/// <summary>
/// Retorna todas as Stored Procedures do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllViews(string dataBaseName, DadosUsuario usr)
{
var clsTables = new List<DadosObjeto>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var cmd = new SqlCommand("SELECT name as Nome, create_date AS DataCriacao, modify_date AS DataAlteracao FROM sys.views", cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto
{
Nome = dr[0].ToString(),
DataCriacao = Convert.ToDateTime(dr[1]),
DataAlteracao = Convert.ToDateTime(dr[2]),
Tipo = "view"
});
}
}
return clsTables;
}
}
/// <summary>
/// Retorna todas as tabelas do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllTables(string dataBaseName, DadosUsuario usr)
{
var clsTables = new List<DadosObjeto>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var cmd = new MySqlCommand(string.Format("set global innodb_stats_on_metadata=0;SELECT `TABLES`.TABLE_NAME, `TABLES`.CREATE_TIME, ifnull(`TABLES`.UPDATE_TIME,`TABLES`.CREATE_TIME) as DataAlteracao,table_rows AS QtdRegistros FROM INFORMATION_SCHEMA.`TABLES` WHERE (`TABLES`.TABLE_SCHEMA = '{0}') AND (`TABLES`.TABLE_TYPE = 'BASE TABLE') ORDER BY `TABLES`.TABLE_NAME ASC;", dataBaseName), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto
{
Nome = dr[0].ToString(),
DataCriacao = Convert.ToDateTime(dr[1]),
DataAlteracao = Convert.ToDateTime(dr[2]),
Tipo = "tabela",
QtdRegistros = Convert.ToInt32(dr[3])
});
}
}
}
return clsTables;
}
public override void SalvaConexao(DadosUsuario dadosLogin)
{
if (dadosLogin==null || string.IsNullOrWhiteSpace(dadosLogin.ID))
{
throw new ArgumentNullException("dadosLogin", "ID do canexão não pode ser nulo ou vazio");
}
var conexoes = CarregaConexoes(dadosLogin.NomeProvedor);
var conexao = conexoes.FirstOrDefault(c =>
c.Usuario == dadosLogin.Usuario &&
c.Servidor == dadosLogin.Servidor &&
c.DataBase == dadosLogin.DataBase &&
c.Porta == dadosLogin.Porta);
if (conexao != null)
{
dadosLogin.ID = conexao.ID;
}
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "Usuario", dadosLogin.Usuario.Trim());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "Senha", _sh1.Criptografa(dadosLogin.Senha));
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "Servidor", dadosLogin.Servidor.Trim());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "DataBase", "");
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "Porta", dadosLogin.Porta.ToString());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "TrustedConnection", dadosLogin.TrustedConnection ? "true" : "false");
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.ID), "NomeProvedor", dadosLogin.NomeProvedor);
}
/// <summary>
/// Lista os parametros de entrada da Procedure selecionada
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <param name="procedureName">Nome da Stored Procedure alvo</param>
/// <returns></returns>
public List<DadosStoredProceduresParameters> ListaAllStoredProceduresParameters(string dataBaseName, DadosUsuario usr, string procedureName)
{
using (var cnn=Conexao.RetornaConexaoBase(usr))
{
var clsTables = new List<DadosStoredProceduresParameters>();
cnn.Open();
var sbSql = new System.Text.StringBuilder();
if (cnn.ServerVersion != null && cnn.ServerVersion.Contains("5."))
{
sbSql.AppendLine("SELECT TRIM(CAST(param_list AS CHAR(10000) CHARACTER SET utf8)) AS parametros FROM mysql.proc ");
sbSql.AppendLine(string.Format("WHERE name = '{0}' AND db='{1}';", procedureName, dataBaseName));
var cmd = new MySqlCommand(sbSql.ToString(), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var strParametros = dr[0];
var arrayParam = strParametros.ToString().Trim().Split(',');
clsTables.AddRange(arrayParam.Select(p => p.Trim().Split(' ')).Select(ajustaParam => new DadosStoredProceduresParameters
{
ParameterName = ajustaParam[1],
ParameterDataType = ajustaParam[2],
ParameterMaxBytes = RetornaBytesParametro(ajustaParam[2].ToLower()),
IsOutPutParameter = ajustaParam[0] != "IN",
ParameterDotNetType = _rorinas.RetornaTipoDadosDotNet(ajustaParam[2])
}));
}
}
}
else
{
sbSql.AppendLine("SELECT PARAMETER_NAME, '' AS ParameterValue, DATA_TYPE AS ParameterDataType, CHARACTER_MAXIMUM_LENGTH AS ParameterMaxBytes, CASE PARAMETER_MODE WHEN 'IN' THEN 0 else 1 END AS IsOutPutParameter ");
sbSql.Append("FROM INFORMATION_SCHEMA.PARAMETERS ");
sbSql.AppendFormat("WHERE SPECIFIC_NAME='{0}' ", procedureName);
sbSql.Append("ORDER BY ORDINAL_POSITION;");
var cmd = new MySqlCommand(sbSql.ToString(), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosStoredProceduresParameters { ParameterName = dr["PARAMETER_NAME"].ToString(), ParameterDataType = dr["ParameterDataType"].ToString(), ParameterMaxBytes = dr["ParameterMaxBytes"].ToString(), IsOutPutParameter = Convert.ToBoolean(dr["IsOutPutParameter"] ?? false), ParameterDotNetType = _rorinas.RetornaTipoDadosDotNet(dr["ParameterDataType"].ToString()) });
}
}
}
return clsTables;
}
}
/// <summary>
/// Testa se é possivel iniciar uma conexão com o banco de dados
/// </summary>
/// <param name="usr">dados do usuario necessarios para a conexão</param>
/// <returns></returns>
public KeyValuePair<bool, string> TestarConexao(DadosUsuario usr)
{
try
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var cmd = new MySqlCommand("select version();", cnn);
cnn.Open();
return new KeyValuePair<bool, string>(true, cmd.ExecuteScalar().ToString());
}
}
catch
{
return new KeyValuePair<bool, string>(false, "Não foi possivel conectar ao banco.");
}
}
/// <summary>
/// Retorna todas as Stored Procedures do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllStoredProcedures(string dataBaseName, DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var clsTables = new List<DadosObjeto>();
cnn.Open();
var cmd = new MySqlCommand(string.Format("SELECT ROUTINE_NAME, CREATED, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE='PROCEDURE' ORDER BY ROUTINE_NAME ASC;", dataBaseName), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto { Nome = dr[0].ToString(), DataCriacao = Convert.ToDateTime(dr[1]), DataAlteracao = Convert.ToDateTime(dr[2]), Tipo = "procedure" });
}
}
return clsTables;
}
}
/// <summary>
/// Retorna todas as Stored Procedures do banco de dados selecionado
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosObjeto> ListaAllStoredProcedures(string dataBaseName, DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var clsTables = new List<DadosObjeto>();
cnn.Open();
var cmd = new SqlCommand("select name, create_date,modify_date from sys.procedures order by name;", cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosObjeto { Nome = dr[0].ToString(), DataCriacao = Convert.ToDateTime(dr[1]), DataAlteracao = Convert.ToDateTime(dr[2]), Tipo = "procedure" });
}
}
return clsTables;
}
}
/// <summary>
/// Lista todas as databases do servidor atual
/// </summary>
/// <param name="usr">Dados do usuario</param>
/// <returns>lista com o nome dos bancos</returns>
public List<string> ListAllDatabases(DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var clsDatabases = new List<string>();
cnn.Open();
var cmd = new MySqlCommand("show databases;", cnn);
using (var dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
while (dr.Read())
{
clsDatabases.Add(dr[0].ToString());
}
}
return clsDatabases.OrderBy(d => d).ToList();
}
}
}
/// <summary>
/// Lista todos os campos da tabela
/// </summary>
/// <param name="dataBaseName"></param>
/// <param name="tableName"></param>
/// <param name="usr"></param>
/// <returns></returns>
public List<DadosColunas> ListAllFieldsFromTable(string dataBaseName, string tableName, DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var sql = new System.Text.StringBuilder(string.Format("SELECT C.name as Coluna, TY.name as Tipo, C.max_length AS Tamanho, c.is_nullable AS AceitaNull, C.is_identity ,(SELECT K.COLUMN_NAME as Coluna FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE T.CONSTRAINT_TYPE = 'PRIMARY KEY' AND T.TABLE_NAME = '{0}' and K.COLUMN_NAME=C.name) AS PK, object_definition(C.default_object_id) AS [DefaultValue] ", tableName));
sql.Append("FROM sys.columns C ");
sql.Append("INNER JOIN sys.tables T ON T.object_id = C.object_id ");
sql.Append("INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id ");
sql.Append("INNER JOIN sys.objects O ON O.object_id = C.object_id ");
sql.Append(string.Format("WHERE T.name='{0}' ", tableName));
var cmd = new SqlCommand(sql.ToString(), cnn);
var dadosTabela = new List<DadosColunas>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dadosTabela.Add(new DadosColunas
{
Coluna = dr["Coluna"].ToString(),
Tipo = _funcoes.RetornaTipoDadosDotNet(dr["Tipo"].ToString()),
AceitaNull = Convert.ToBoolean(dr["AceitaNull"]),
Tamanho = Convert.ToInt32(dr["Tamanho"]),
Pk = dr["PK"].ToString() != "",
Identity = Convert.ToBoolean(dr["is_identity"]),
DefaultValue = dr["DefaultValue"] != null ? dr["DefaultValue"].ToString() : "",
Sync = (!(dr["Coluna"].ToString().Contains("Alteracao") || dr["Coluna"].ToString().Contains("Cadastro") || dr["Coluna"].ToString().Contains("Criacao")))
});
}
}
return dadosTabela;
}
}
/// <summary>
/// Lista todos os campos da tabela
/// </summary>
/// <param name="dataBaseName"></param>
/// <param name="tableName"></param>
/// <param name="usr"></param>
/// <returns></returns>
public List<DadosColunas> ListAllFieldsFromTable(string dataBaseName, string tableName, DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var sql = new System.Text.StringBuilder("SELECT COLUMNS.COLUMN_NAME AS Coluna, COLUMNS.DATA_TYPE AS Tipo, COLUMNS.IS_NULLABLE AS AceitaNull, COLUMNS.CHARACTER_MAXIMUM_LENGTH AS Tamanho, COLUMNS.COLUMN_KEY AS PK, COLUMNS.EXTRA AS is_identity, COLUMNS.COLUMN_DEFAULT AS DefaultValue, COLUMNS.privileges as Sync ");
sql.Append("FROM INFORMATION_SCHEMA.COLUMNS COLUMNS ");
sql.Append(string.Format("WHERE (COLUMNS.TABLE_SCHEMA = '{0}') AND (COLUMNS.TABLE_NAME = '{1}');", dataBaseName, tableName));
var cmd = new MySqlCommand(sql.ToString(), cnn);
var dadosTabela = new List<DadosColunas>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dadosTabela.Add(new DadosColunas
{
Coluna = dr["Coluna"].ToString(),
Tipo = _funcoes.RetornaTipoDadosDotNet(dr["Tipo"].ToString()),
AceitaNull = dr["AceitaNull"].ToString() != "NO",
Tamanho = dr["Tamanho"] != DBNull.Value
? Convert.ToInt32(dr["Tamanho"])
: 0,
Pk = dr["PK"].ToString() == "PRI",
Identity = dr["is_identity"].ToString().Contains("auto_increment"),
DefaultValue = dr["DefaultValue"] != null ? dr["DefaultValue"].ToString() : "",
Sync = dr["Sync"].ToString().Contains("insert,update"),
});
}
}
return dadosTabela;
}
}
/// <summary>
/// Lista os parametros de entrada da Procedure selecionada
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <param name="procedureName">Nome da Stored Procedure alvo</param>
/// <returns></returns>
public List<DadosStoredProceduresParameters> ListaAllStoredProceduresParameters(string dataBaseName, DadosUsuario usr, string procedureName)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var clsTables = new List<DadosStoredProceduresParameters>();
cnn.Open();
var sbSql = new System.Text.StringBuilder("SELECT SCHEMA_NAME(SCHEMA_ID) AS [Schema], SO.name AS [ObjectName], SO.Type_Desc AS [ObjectType (UDF/SP)], P.parameter_id AS [ParameterID], P.name AS [ParameterName], TYPE_NAME(P.user_type_id) AS [ParameterDataType], P.max_length AS [ParameterMaxBytes], P.is_output AS [IsOutPutParameter] ");
sbSql.Append("FROM sys.objects AS SO ");
sbSql.Append("INNER JOIN sys.parameters AS P ON SO.OBJECT_ID = P.OBJECT_ID ");
sbSql.Append("WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID FROM sys.objects WHERE TYPE IN ('P')) and (SO.name=@ProceduresName) ");
sbSql.Append("ORDER BY [Schema], SO.name, P.parameter_id;");
var cmd = new SqlCommand(sbSql.ToString(), cnn);
cmd.Parameters.AddWithValue("proceduresName", procedureName);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
clsTables.Add(new DadosStoredProceduresParameters
{
ParameterName = dr[4].ToString(),
ParameterDataType = dr[5].ToString(),
ParameterMaxBytes = dr[6].ToString(),
IsOutPutParameter = Convert.ToBoolean(dr[7] ?? false),
ParameterDotNetType = _rorinas.RetornaTipoDadosDotNet(dr[5].ToString())
});
}
}
return clsTables;
}
}
public abstract void SalvaConexao(DadosUsuario dadosLogin);
public override void SalvaConexao(DadosUsuario dadosLogin)
{
if (dadosLogin == null || string.IsNullOrWhiteSpace(dadosLogin.ID))
{
return;
}
using (var cnn = new SQLiteConnection(_strConnection))
{
var cmd = cnn.CreateCommand();
cmd.CommandText = @"SELECT * FROM HistoricoConexao
WHERE Provider = @nomeProvedor
AND Usuario = @usuario
AND Servidor = @servidor
AND Porta = @porta;";
cmd.Parameters.AddWithValue("nomeProvedor", dadosLogin.NomeProvedor);
cmd.Parameters.AddWithValue("usuario", dadosLogin.Usuario);
cmd.Parameters.AddWithValue("servidor", dadosLogin.Servidor);
cmd.Parameters.AddWithValue("porta", dadosLogin.Porta);
cnn.Open();
DadosUsuario dadosConexao;
using (var dr = cmd.ExecuteReader())
{
dadosConexao = dr.MapToEntities<DadosUsuario>().FirstOrDefault();
if (dadosConexao != null)
{
dadosConexao.Senha = _sh1.Descriptografa(dadosConexao.Senha);
}
}
if (dadosConexao == null)
{
cmd.CommandText = @"INSERT INTO HistoricoConexao (ID, MaquinaID, Usuario, Senha, Servidor, [DataBase], Porta, TrustedConnection, Provider)
VALUES (@id, @maquinaID, @usuario, @senha, @servidor, @db, @porta, @trustedConnection, @nomeProvedor);";
cmd.Parameters.AddWithValue("id", dadosLogin.ID);
cmd.Parameters.AddWithValue("maquinaID", dadosLogin.MaquinaID);
cmd.Parameters.AddWithValue("senha", _sh1.Criptografa(dadosLogin.Senha));
cmd.Parameters.AddWithValue("db", dadosLogin.DataBase);
cmd.Parameters.AddWithValue("trustedConnection", dadosLogin.TrustedConnection);
cmd.ExecuteNonQuery();
}
else
{
cmd.CommandText = @"UPDATE HistoricoConexao SET MaquinaID = @maquinaID, Usuario = @usuario, Senha = @senha, Servidor = @servidor, [DataBase] = @db, Porta = @porta, TrustedConnection = @trustedConnection, Provider = @nomeProvedor
WHERE ID = @id;";
cmd.Parameters.AddWithValue("id", dadosLogin.ID);
cmd.Parameters.AddWithValue("maquinaID", dadosLogin.MaquinaID);
cmd.Parameters.AddWithValue("senha", _sh1.Criptografa(dadosLogin.Senha));
cmd.Parameters.AddWithValue("db", dadosLogin.DataBase);
cmd.Parameters.AddWithValue("trustedConnection", dadosLogin.TrustedConnection);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Lista todos os campos da tabela
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="procedureName">Nome da Stored Procedure alvo</param>
/// <param name="parametros"></param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosColunas> ListAllFieldsFromStoredProcedure(string dataBaseName, string procedureName, List<DadosStoredProceduresParameters> parametros, DadosUsuario usr)
{
var clsTables = new List<DadosColunas>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var cmd = new MySqlCommand(procedureName, cnn) {CommandType = CommandType.StoredProcedure};
foreach (var param in parametros.Select(parametro => new MySqlParameter
{
ParameterName = parametro.ParameterName,
Value = parametro.ParameterValue,
Direction = parametro.IsOutPutParameter ? ParameterDirection.Output : ParameterDirection.Input
}))
{
cmd.Parameters.Add(param);
}
using (var dr = cmd.ExecuteReader())
{
var schemaTable = dr.GetSchemaTable();
if (schemaTable != null)
{
clsTables.AddRange(from DataRow myField in schemaTable.Rows
select new DadosColunas
{
Coluna = myField.ItemArray[0].ToString(),
Tipo =_rorinas.RetornaTipoDadosDotNet(_rorinas.ConvertTipoClrToSql(myField.ItemArray[11].ToString().Replace("System.", ""))),
AceitaNull = false,
Tamanho = Convert.ToInt32(myField.ItemArray[2].ToString()),
Pk = false,
Identity = false,
DefaultValue = ""
});
}
}
}
return clsTables;
}
private void bntTestarConexao_Click(object sender, EventArgs e)
{
if (!errPadrao.HasErrors(tpConexao))
{
Cursor = Cursors.WaitCursor;
var dl = new DadosUsuario
{
Servidor = txtServidor.Text.Trim(),
Usuario = txtUsuario.Text.Trim(),
Senha = txtSenha.Text.Trim(),
Porta = txtPorta.Text.ToInt32(),
TrustedConnection = chkTrustedConnection.Checked
};
var retornoTeste = Gerador.TestarConexao(dl);
if (retornoTeste.Key)
{
MessageBox.Show(string.Format("Conexão realizada com sucesso.\nVersão: {0}", retornoTeste.Value), "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Erro ao efetuar a conexão", "ATENÇÃO", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Cursor = Cursors.Default;
}
else
{
errPadrao.SetErrors();
}
}
/// <summary>
/// Lista todos os campos da tabela
/// </summary>
/// <param name="dataBaseName">Nome do banco de dados alvo</param>
/// <param name="viewsName">Nome da Stored Procedure alvo</param>
/// <param name="usr">Dados do usuario</param>
/// <returns></returns>
public List<DadosColunas> ListAllFieldsFromViews(string dataBaseName, string viewsName, DadosUsuario usr)
{
return _mapTabela.ListAllFieldsFromTable(dataBaseName, viewsName, usr);
}
/// <summary>
/// Lista todos os relacionamentos de chave estrangeira que a tabela possua
/// </summary>
/// <param name="nomeTabela">Nome da tabela a ser mapeada</param>
/// <param name="dataBaseName">Nome do banco de dados </param>
/// <param name="usr">Dados do usário para conexão </param>
/// <returns></returns>
public List<DadosAssociation> RetornaMapeamentoFK(string nomeTabela, string dataBaseName, DadosUsuario usr)
{
var relacionamentos = new List<DadosAssociation>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var sbSql = new System.Text.StringBuilder("SELECT FK.TABLE_NAME AS FK_Table, CU.COLUMN_NAME AS FK_Column, PK.TABLE_NAME AS PK_Table, PT.COLUMN_NAME AS PK_Column, C.CONSTRAINT_NAME AS Constraint_Name ");
sbSql.Append("FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C ");
sbSql.Append("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME ");
sbSql.Append("INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME ");
sbSql.Append("INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME ");
sbSql.Append("INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME ");
sbSql.Append(" FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 ");
sbSql.Append(" INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME ");
sbSql.Append(" WHERE (i1.CONSTRAINT_TYPE = 'PRIMARY KEY')) AS PT ON PT.TABLE_NAME = PK.TABLE_NAME ");
sbSql.AppendFormat("WHERE FK.TABLE_NAME='{0}' ", nomeTabela);
sbSql.Append("ORDER BY FK_Table, FK_Column; ");
cnn.Open();
var cmd = new SqlCommand(sbSql.ToString(), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
relacionamentos.Add(new DadosAssociation
{
Tabela = dr[0].ToString(),
Coluna = dr[1].ToString(),
TabelaFK = dr[2].ToString(),
ColunaFK = dr[3].ToString(),
Constraint = dr[4].ToString()
});
}
}
return relacionamentos;
}
}
private void bntAvancar_Click(object sender, EventArgs e)
{
if (!errPadrao.HasErrors(tpConexao))
{
DadosLogin = new DadosUsuario
{
Servidor = txtServidor.Text.Trim(),
Usuario = txtUsuario.Text.Trim(),
Senha = txtSenha.Text.Trim(),
Porta = txtPorta.Text.ToInt32(),
MaquinaID = IdComputador,
TrustedConnection = chkTrustedConnection.Checked,
NomeProvedor = Gerador.GetType().FullName
};
tbPrincipal.SelectTab(tpBancoDados);
}
else
{
errPadrao.SetErrors();
}
}
private void tbPrincipal_SelectedIndexChanged(object sender, EventArgs e)
{
if (tbPrincipal.SelectedIndex <= 2)
{
switch (tbPrincipal.SelectedIndex)
{
case 0:
case 1:
if (errPadrao.HasErrors(tpConexao))
{
tbPrincipal.SelectTab(tpConexao);
}
else
{
DadosLogin = new DadosUsuario
{
Servidor = txtServidor.Text.Trim(),
Usuario = txtUsuario.Text.Trim(),
Senha = txtSenha.Text.Trim(),
Porta = txtPorta.Text.ToInt32(),
MaquinaID = IdComputador,
TrustedConnection = chkTrustedConnection.Checked,
NomeProvedor = Gerador.GetType().FullName
};
if (PreviousTab == tpConexao)
{
Historico.SalvaConexao(DadosLogin);
}
if (tbPrincipal.SelectedTab == tpBancoDados)
{
_objetosSelecionados = new List<KeyValuePair<TipoObjetoBanco, List<DadosColunas>>>();
}
CarregaBancosDeDados();
}
break;
case 2:
bool itemChecado = false;
for (int i = 0; i < lvObjetosBanco.Items.Count; i++)
{
if (lvObjetosBanco.Items[i].Checked)
{
itemChecado = true;
break;
}
}
if (itemChecado)
{
btnMapearSelecionados_Click(btnMapearSelecionados, EventArgs.Empty);
}
else if (_objetosSelecionados.Any(q => q.Key.QuerySql == null))
{
tbPrincipal.SelectedIndex = 1;
}
break;
}
}
}
/// <summary>
/// Lista todos os relacionamentos de chave estrangeira que a tabela possua
/// </summary>
/// <param name="nomeTabela">Nome da tabela a ser mapeada</param>
/// <param name="dataBaseName">Nome do banco de dados </param>
/// <param name="usr">Dados do usário para conexão </param>
/// <returns></returns>
public List<DadosAssociation> RetornaMapeamentoFK(string nomeTabela, string dataBaseName, DadosUsuario usr)
{
var relacionamentos = new List<DadosAssociation>();
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
var sbSql = new System.Text.StringBuilder("select distinct TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME ");
sbSql.Append("from INFORMATION_SCHEMA.KEY_COLUMN_USAGE ");
sbSql.AppendFormat("where TABLE_NAME = '{0}' and referenced_table_name is not null;", nomeTabela);
cnn.Open();
var cmd = new MySqlCommand(sbSql.ToString(), cnn);
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
relacionamentos.Add(new DadosAssociation
{
Tabela = dr[0].ToString(),
Coluna = dr[1].ToString(),
TabelaFK = dr[3].ToString(),
ColunaFK = dr[4].ToString(),
Constraint = dr[2].ToString()
});
}
}
return relacionamentos;
}
}
public bool ValidaExistente(IEnumerable<DadosUsuario> servidores, DadosUsuario login)
{
var retorno = servidores.FirstOrDefault(s => s.Servidor == login.Servidor && s.Usuario == login.Usuario);
return retorno != null && !string.IsNullOrEmpty(retorno.Servidor);
}
public static MySqlConnection RetornaConexaoBase(DadosUsuario usr)
{
return new MySqlConnection(string.Format("Data Source={0};Initial Catalog={1};UID={2}; Password={3};Port={4};", usr.Servidor, usr.DataBase, usr.Usuario, usr.Senha, usr.Porta));
}
public override void SalvaConexao(DadosUsuario dadosLogin)
{
var sh1 = new GeraHashSha1();
var pathXml = string.Format("{0}\\conexoes.xml", AppDomain.CurrentDomain.BaseDirectory);
var existe = System.IO.File.Exists(pathXml);
var xmlDoc = new XmlDocument();
if (dadosLogin==null)
{
return;
}
try
{
if (ValidaExistente(CarregaConexoes(dadosLogin.NomeProvedor), dadosLogin))
{
var doc = XElement.Load(pathXml);
var singleBook = (from b in doc.Elements("conexao")
where b.Element("servidor").Value == dadosLogin.Servidor
&& b.Element("usuario").Value == dadosLogin.Usuario
select b);
foreach (var xe in singleBook)
{
xe.SetElementValue("senha", sh1.Criptografa(dadosLogin.Senha));
xe.SetElementValue("data", XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local));
//use the ReplaceContent method to do the replacement for all attribures
//this will remove all other attributes and save only the price attribute
//xe.ReplaceAttributes(new XAttribute("price", "32$"));
}
var xmlSemAssinatura = Regex.Replace(doc.ToString(), @"<Signature(.|\n)*?Signature>", string.Empty);
EmSerialize.AssinaXml(xmlSemAssinatura, pathXml);
}
else
{
if (existe)
{
xmlDoc.Load(pathXml);
}
else
{
xmlDoc.AppendChild(xmlDoc.CreateXmlDeclaration("1.0", "ISO-8859-1", "no"));
}
var maquinaId = xmlDoc.CreateElement("maquina");
var servidor = xmlDoc.CreateElement("servidor");
var database = xmlDoc.CreateElement("database");
var usuario = xmlDoc.CreateElement("usuario");
var porta = xmlDoc.CreateElement("porta");
var senha = xmlDoc.CreateElement("senha");
var nomeProvedor = xmlDoc.CreateElement("nomeProvedor");
maquinaId.AppendChild(xmlDoc.CreateTextNode(dadosLogin.MaquinaID));
servidor.AppendChild(xmlDoc.CreateTextNode(dadosLogin.Servidor));
database.AppendChild(xmlDoc.CreateTextNode(dadosLogin.DataBase));
usuario.AppendChild(xmlDoc.CreateTextNode(dadosLogin.Usuario));
senha.AppendChild(xmlDoc.CreateTextNode(sh1.Criptografa(dadosLogin.Senha)));
porta.AppendChild(xmlDoc.CreateTextNode(dadosLogin.Porta.ToString()));
nomeProvedor.AppendChild(xmlDoc.CreateTextNode(dadosLogin.NomeProvedor));
var dataConexao = xmlDoc.CreateElement("data");
dataConexao.AppendChild(xmlDoc.CreateTextNode(XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local)));
var conexao = xmlDoc.CreateElement("conexao");
conexao.AppendChild(maquinaId);
conexao.AppendChild(servidor);
conexao.AppendChild(database);
conexao.AppendChild(usuario);
conexao.AppendChild(senha);
conexao.AppendChild(porta);
conexao.AppendChild(nomeProvedor);
conexao.AppendChild(dataConexao);
if (existe)
{
var conexoes = xmlDoc.SelectSingleNode("conexoes");
if (conexoes != null)
{
conexoes.AppendChild(conexao);
xmlDoc.AppendChild(conexoes);
}
}
else
{
var acoes = xmlDoc.CreateElement("conexoes");
acoes.AppendChild(conexao);
xmlDoc.AppendChild(acoes);
}
var xmlSemAssinatura = Regex.Replace(xmlDoc.OuterXml, @"<Signature(.|\n)*?Signature>", string.Empty);
EmSerialize.AssinaXml(xmlSemAssinatura, pathXml);
}
}
catch
{
throw;
}
}
public List<DadosColunas> MapQuery(string sql, List<DadosStoredProceduresParameters> parametros, DadosUsuario dadosLogin)
{
var retorno = new List<DadosColunas>();
DataTable dt;
using (var cnn = Conexao.RetornaConexaoBase(dadosLogin))
{
var cmd = cnn.CreateCommand();
cmd.CommandText = sql;
foreach (var parametro in parametros)
{
cmd.Parameters.AddWithValue(parametro.ParameterName, parametro.DefineNull ? null : parametro.ParameterValue);
}
cnn.Open();
using (var dr = cmd.ExecuteReader())
{
dt = dr.GetSchemaTable();
}
}
if (dt != null)
{
retorno.AddRange(from DataRow row in dt.Rows
select new DadosColunas
{
Coluna = row["ColumnName"].ToString(),
Pk = (bool) row["IsKey"],
AceitaNull = (bool) row["AllowDBNull"],
Tipo = row["DataType"].ToString().Replace("System.", "").Replace("U", ""),
TipoSync = DadosColunas.ETipoSync.Never
});
}
return retorno;
}
public string RetornaEnum(string dataBaseName, string tableName, string colName, DadosUsuario usr)
{
return null;
}
public string RetornaEnum(string dataBaseName, string tableName, string colName, DadosUsuario usr)
{
using (var cnn = Conexao.RetornaConexaoBase(usr))
{
cnn.Open();
var sql = new System.Text.StringBuilder("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS ");
sql.AppendLine(string.Format("WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}' AND COLUMN_NAME = '{2}';", dataBaseName, tableName, colName));
var cmd = new MySqlCommand(sql.ToString(), cnn);
var strEnum = "";
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr[0].ToString().Contains("enum"))
{
var strTmp = dr[0].ToString().Replace("enum(", "").Replace(")", "").Split(',');
var valores = strTmp.Where(t => t.Replace("'", "").Length > 0).Cast<object>().ToList();
strEnum = string.Format("{0}|{1}", colName, string.Join(",", valores.Select(i => i.ToString()).ToArray()));
}
}
}
return strEnum;
}
}
private void CarregaBancosDeDados()
{
try
{
Cursor = Cursors.WaitCursor;
if (DadosLogin == null)
{
DadosLogin = new DadosUsuario
{
Servidor = txtServidor.Text.Trim(),
Usuario = txtUsuario.Text.Trim(),
Senha = txtSenha.Text.Trim(),
Porta = txtPorta.Text.ToInt32(),
TrustedConnection = chkTrustedConnection.Checked
};
}
cbBancoDados.DataSource = Gerador.ListAllDatabases(DadosLogin);
}
catch (Exception ex)
{
MessageBox.Show("Houve um erro ao tentar recuperar a lista de bancos de dados.\nErro:" + ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Default;
}
}
