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