public override void SalvaConexao(DatabaseUser dadosLogin)
{
if (dadosLogin == null || string.IsNullOrWhiteSpace(dadosLogin.UserId))
{
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.UserName == dadosLogin.UserName &&
c.ServerAddress == dadosLogin.ServerAddress &&
c.DatabaseName == dadosLogin.DatabaseName &&
c.Port == dadosLogin.Port);
if (conexao != null)
{
dadosLogin.UserId = conexao.UserId;
}
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "Usuario", dadosLogin.UserName.Trim());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "Senha", _sh1.Criptografa(dadosLogin.Password));
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "Servidor", dadosLogin.ServerAddress.Trim());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "DataBase", "");
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "Porta", dadosLogin.Port.ToString());
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "TrustedConnection", dadosLogin.IsTrustedConnection ? "true" : "false");
RegistroWindows.GravaValor(string.Format("{0}\\{1}", RegistroWindows.ChaveConexoes, dadosLogin.UserId), "NomeProvedor", dadosLogin.NomeProvedor);
}
/// <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<DatabaseObjectMap> ListAllTables(string dataBaseName, DatabaseUser usr)
{
var clsTables = new List<DatabaseObjectMap>();
using (var cnn = Connection.DefaultConnection(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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.Tabela,
Records = Convert.ToInt32(dr[3])
});
}
}
}
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<DatabaseObjectMap> ListAllTables(string dataBaseName, DatabaseUser usr)
{
var clsTables = new List<DatabaseObjectMap>();
using (var cnn = Connection.DefaultConnection(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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.Tabela,
Records = 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<DatabaseObjectMap> ListAllStoredProcedures(string dataBaseName, DatabaseUser usr)
{
using (var cnn = Connection.DefaultConnection(usr))
{
var clsTables = new List<DatabaseObjectMap>();
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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.Procedure
});
}
}
return clsTables;
}
}
public static SqlConnection DefaultConnection(DatabaseUser usr)
{
return usr.IsTrustedConnection
? new SqlConnection(string.Format(TrustedStringConnection, usr.ServerAddress, usr.DatabaseName))
: new SqlConnection(string.Format(StringConnection, usr.ServerAddress, usr.DatabaseName, usr.UserName, usr.Password));
}
/// <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<DatabaseObjectMap> ListAllViews(string dataBaseName, DatabaseUser usr)
{
var clsTables = new List<DatabaseObjectMap>();
using (var cnn = Connection.DefaultConnection(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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.View
});
}
}
return clsTables;
}
}
public List<DatabaseObjectMap> ListAllViews(string dataBaseName, DatabaseUser usr)
{
var clsTables = new List<DatabaseObjectMap>();
using (var cnn = Connection.DefaultConnection(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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.View
});
}
}
return clsTables;
}
}
public List<DatabaseObjectMap> ListAllStoredProcedures(string dataBaseName, DatabaseUser usr)
{
using (var cnn = Connection.DefaultConnection(usr))
{
var clsTables = new List<DatabaseObjectMap>();
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 DatabaseObjectMap
{
Name = dr[0].ToString(),
CreationDate = Convert.ToDateTime(dr[1]),
ChangeDate = Convert.ToDateTime(dr[2]),
Type = EDatabaseObjectType.Procedure
});
}
}
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> TestConnection(DatabaseUser usr)
{
try
{
using (var cnn = Connection.DefaultConnection(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>
/// Lista todos os campos da tabela
/// </summary>
/// <param name="dataBaseName"></param>
/// <param name="tableName"></param>
/// <param name="usr"></param>
/// <returns></returns>
public List<ColumnInfo> ListAllFieldsFromTable(string dataBaseName, string tableName, DatabaseUser usr)
{
using (var cnn = Connection.DefaultConnection(usr))
{
cnn.Open();
var sql = @"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 = '" + tableName +
@"' and K.COLUMN_NAME=C.name) AS PK,
object_definition(C.default_object_id) AS [DefaultValue]
FROM sys.columns C
INNER JOIN sys.tables T ON T.object_id = C.object_id
INNER JOIN sys.types TY ON TY.user_type_id = C.user_type_id
INNER JOIN sys.objects O ON O.object_id = C.object_id
WHERE T.name='" + tableName + "';";
var cmd = new SqlCommand(sql, cnn);
var dadosTabela = new List<ColumnInfo>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dadosTabela.Add(new ColumnInfo
{
ColumnName = dr["Coluna"].ToString(),
Type = _funcoes.DataTypeNetFramework(dr["Tipo"].ToString()),
IsNullability = Convert.ToBoolean(dr["AceitaNull"]),
Size = Convert.ToInt32(dr["Tamanho"]),
IsPrimaryKey = dr["PK"].ToString() != "",
IsIdentity = Convert.ToBoolean(dr["is_identity"]),
DefaultValue = dr["DefaultValue"] != null ? dr["DefaultValue"].ToString() : "",
IsSync = (!(dr["Coluna"].ToString().Contains("Alteracao") || dr["Coluna"].ToString().Contains("Cadastro") || dr["Coluna"].ToString().Contains("Criacao")))
});
}
}
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<StoredProcedureParameter> ListAllStoredProceduresParameters(string dataBaseName, DatabaseUser usr, string procedureName)
{
if (string.IsNullOrWhiteSpace(dataBaseName) || string.IsNullOrWhiteSpace(procedureName))
{
return new List<StoredProcedureParameter>();
}
using (var cnn = Connection.DefaultConnection(usr))
{
var clsTables = new List<StoredProcedureParameter>();
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 StoredProcedureParameter
{
ParameterName = dr[4].ToString(),
ParameterDataType = dr[5].ToString(),
ParameterMaxBytes = dr[6].ToString(),
IsOutPutParameter = Convert.ToBoolean(dr[7] ?? false),
ParameterDotNetType = _rorinas.DataTypeNetFramework(dr[5].ToString())
});
}
}
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(DatabaseUser usr)
{
using (var cnn = Connection.DefaultConnection(usr))
{
var clsDatabases = new List<string>();
cnn.Open();
var cmd = new SqlCommand("sp_databases", cnn);
using (var dr = cmd.ExecuteReader())
{
if (!dr.HasRows) return clsDatabases.OrderBy(d => d).ToList();
while (dr.Read())
{
clsDatabases.Add(dr[0].ToString());
}
return clsDatabases.OrderBy(d => d).ToList();
}
}
}
public List<ColumnInfo> ListAllFieldsFromTable(string dataBaseName, string tableName, DatabaseUser usr)
{
using (var cnn = Connection.DefaultConnection(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<ColumnInfo>();
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
dadosTabela.Add(new ColumnInfo
{
ColumnName = dr["Coluna"].ToString(),
Type = _funcoes.DataTypeNetFramework(dr["Tipo"].ToString()),
IsNullability = dr["AceitaNull"].ToString() != "NO",
Size = dr["Tamanho"] != DBNull.Value
? Convert.ToInt32(dr["Tamanho"])
: 0,
IsPrimaryKey = dr["PK"].ToString() == "PRI",
IsIdentity = dr["is_identity"].ToString().Contains("auto_increment"),
DefaultValue = dr["DefaultValue"] != null ? dr["DefaultValue"].ToString() : "",
IsSync = dr["Sync"].ToString().Contains("insert,update"),
});
}
}
return dadosTabela;
}
}
private void bntAvancar_Click(object sender, EventArgs e)
{
if (!errPadrao.HasErrors(tpConexao))
{
User = new DatabaseUser
{
ServerAddress = txtServidor.Text.Trim(),
UserName = txtUsuario.Text.Trim(),
Password = txtSenha.Text.Trim(),
Port = txtPorta.Text.ToInt32(),
IsTrustedConnection = chkTrustedConnection.Checked,
NomeProvedor = Gerador.GetType().FullName
};
Historico.SalvaConexao(User);
tbPrincipal.SelectTab(tpBancoDados);
_selectedObjects = new List<DatabaseObjectInfo>();
CarregaBancosDeDados();
}
else
{
errPadrao.SetErrors();
}
}
public static MySqlConnection DefaultConnection(DatabaseUser usr)
{
return new MySqlConnection(string.Format("Data Source={0};Initial Catalog={1};UID={2}; Password={3};Port={4};", usr.ServerAddress, usr.DatabaseName, usr.UserName, usr.Password, usr.Port));
}
/// <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<ColumnInfo> ListAllFieldsFromViews(string dataBaseName, string viewsName, DatabaseUser usr)
{
return _mapTabela.ListAllFieldsFromTable(dataBaseName, viewsName, usr);
}
/// <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<ColumnInfo> ListAllFieldsFromStoredProcedure(string dataBaseName, string procedureName, List<StoredProcedureParameter> parametros, DatabaseUser usr)
{
var clsTables = new List<ColumnInfo>();
using (var cnn = Connection.DefaultConnection(usr))
{
cnn.Open();
var cmd = new SqlCommand(procedureName, cnn) { CommandType = CommandType.StoredProcedure };
if (parametros != null)
{
foreach (var param in parametros.Select(parametro => new SqlParameter
{
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 ColumnInfo
{
ColumnName = myField.ItemArray[0].ToString(),
Type = _rorinas.DataTypeNetFramework(myField.ItemArray[24].ToString()),
IsNullability = false,
Size = Convert.ToInt32(myField.ItemArray[2].ToString()),
IsPrimaryKey = false,
IsIdentity = false
});
}
}
return clsTables;
}
}
/// <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<ForeignKey> ListForeignKeysTable(string nomeTabela, string dataBaseName, DatabaseUser usr)
{
var relacionamentos = new List<ForeignKey>();
using (var cnn = Connection.DefaultConnection(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 ForeignKey
{
TableName = dr[0].ToString(),
Column = dr[1].ToString(),
TableFk = dr[2].ToString(),
ColumnFk = dr[3].ToString(),
Constraint = dr[4].ToString()
});
}
}
return relacionamentos;
}
}
public override void SalvaConexao(DatabaseUser dadosLogin)
{
var sh1 = new HashSha1();
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.ServerAddress
&& b.Element("usuario").Value == dadosLogin.UserName
select b);
foreach (var xe in singleBook)
{
xe.SetElementValue("senha", sh1.Criptografa(dadosLogin.Password));
xe.SetElementValue("data", XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Local));
}
var xmlSemAssinatura = Regex.Replace(doc.ToString(), @"<Signature(.|\n)*?Signature>", string.Empty);
SerializerHelper.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.MachineId));
servidor.AppendChild(xmlDoc.CreateTextNode(dadosLogin.ServerAddress));
database.AppendChild(xmlDoc.CreateTextNode(dadosLogin.DatabaseName));
usuario.AppendChild(xmlDoc.CreateTextNode(dadosLogin.UserName));
senha.AppendChild(xmlDoc.CreateTextNode(sh1.Criptografa(dadosLogin.Password)));
porta.AppendChild(xmlDoc.CreateTextNode(dadosLogin.Port.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);
SerializerHelper.AssinaXml(xmlSemAssinatura, pathXml);
}
}
catch
{
throw;
}
}
public List<ForeignKey> ListForeignKeysTable(string nomeTabela, string dataBaseName, DatabaseUser usr)
{
var relacionamentos = new List<ForeignKey>();
using (var cnn = Connection.DefaultConnection(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 ForeignKey
{
TableName = dr[0].ToString(),
Column = dr[1].ToString(),
TableFk = dr[3].ToString(),
ColumnFk = dr[4].ToString(),
Constraint = dr[2].ToString()
});
}
}
return relacionamentos;
}
}
/// <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<StoredProcedureParameter> ListAllStoredProceduresParameters(string dataBaseName, DatabaseUser usr, string procedureName)
{
using (var cnn=Connection.DefaultConnection(usr))
{
var clsTables = new List<StoredProcedureParameter>();
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 StoredProcedureParameter
{
ParameterName = ajustaParam[1],
ParameterDataType = ajustaParam[2],
ParameterMaxBytes = RetornaBytesParametro(ajustaParam[2].ToLower()),
IsOutPutParameter = ajustaParam[0] != "IN",
ParameterDotNetType = _rorinas.DataTypeNetFramework(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 StoredProcedureParameter
{
ParameterName = dr["PARAMETER_NAME"].ToString(),
ParameterDataType = dr["ParameterDataType"].ToString(),
ParameterMaxBytes = dr["ParameterMaxBytes"].ToString(),
IsOutPutParameter = Convert.ToBoolean(dr["IsOutPutParameter"] ?? false),
ParameterDotNetType = _rorinas.DataTypeNetFramework(dr["ParameterDataType"].ToString())
});
}
}
}
return clsTables;
}
}
public List<ColumnInfo> MapQuery(string sql, List<StoredProcedureParameter> parametros, DatabaseUser dadosLogin)
{
var retorno = new List<ColumnInfo>();
DataTable dt;
using (var cnn = Connection.DefaultConnection(dadosLogin))
{
var cmd = cnn.CreateCommand();
cmd.CommandText = sql;
foreach (var parametro in parametros)
{
cmd.Parameters.AddWithValue(parametro.ParameterName, parametro.DefaultNull ? 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 ColumnInfo
{
ColumnName = row["ColumnName"].ToString(),
IsPrimaryKey = (bool) row["IsKey"],
IsNullability = (bool) row["AllowDBNull"],
Type = row["DataType"].ToString().Replace("System.", "").Replace("U", ""),
ColumnSync = EColumnSync.Never
});
}
return retorno;
}
public bool ValidaExistente(IEnumerable<DatabaseUser> servidores, DatabaseUser login)
{
var retorno = servidores.FirstOrDefault(s => s.ServerAddress == login.ServerAddress && s.UserName == login.UserName);
return retorno != null && !string.IsNullOrEmpty(retorno.ServerAddress);
}
public override void SalvaConexao(DatabaseUser dadosLogin)
{
if (dadosLogin == null || string.IsNullOrWhiteSpace(dadosLogin.UserId))
{
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.UserName);
cmd.Parameters.AddWithValue("servidor", dadosLogin.ServerAddress);
cmd.Parameters.AddWithValue("porta", dadosLogin.Port);
cnn.Open();
DatabaseUser dadosConexao;
using (var dr = cmd.ExecuteReader())
{
dadosConexao = dr.MapToEntities<DatabaseUser>().FirstOrDefault();
if (dadosConexao != null)
{
dadosConexao.Password = _sh1.Descriptografa(dadosConexao.Password);
}
}
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.UserId);
cmd.Parameters.AddWithValue("maquinaID", dadosLogin.MachineId);
cmd.Parameters.AddWithValue("senha", _sh1.Criptografa(dadosLogin.Password));
cmd.Parameters.AddWithValue("db", dadosLogin.DatabaseName);
cmd.Parameters.AddWithValue("trustedConnection", dadosLogin.IsTrustedConnection);
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.UserId);
cmd.Parameters.AddWithValue("maquinaID", dadosLogin.MachineId);
cmd.Parameters.AddWithValue("senha", _sh1.Criptografa(dadosLogin.Password));
cmd.Parameters.AddWithValue("db", dadosLogin.DatabaseName);
cmd.Parameters.AddWithValue("trustedConnection", dadosLogin.IsTrustedConnection);
cmd.ExecuteNonQuery();
}
}
}
public abstract void SalvaConexao(DatabaseUser dadosLogin);
