public void Clone() { var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { Pooling = false }.ToString(); using (var conn = new NpgsqlConnection(connString)) { ProvideClientCertificatesCallback callback1 = certificates => { }; conn.ProvideClientCertificatesCallback = callback1; RemoteCertificateValidationCallback callback2 = (sender, certificate, chain, errors) => true; conn.UserCertificateValidationCallback = callback2; conn.Open(); #if NETCOREAPP1_1 using (var conn2 = conn.Clone()) #else using (var conn2 = (NpgsqlConnection)((ICloneable)conn).Clone()) #endif { Assert.That(conn2.ConnectionString, Is.EqualTo(conn.ConnectionString)); Assert.That(conn2.ProvideClientCertificatesCallback, Is.SameAs(callback1)); Assert.That(conn2.UserCertificateValidationCallback, Is.SameAs(callback2)); conn2.Open(); } } }
static void BeginConvert(dynamic conf) { NpgsqlConnection pgConnection = new NpgsqlConnection(conf.pgConnection); pgConnection.Open(); Console.WriteLine("pgServer:\r\nHost: {0} ({1})", pgConnection.Host, pgConnection.Port); Console.WriteLine("Версия: {0}\r\nБаза данных: {1}", pgConnection.ServerVersion, pgConnection.Database); foreach (var table in conf.DBFTables) { ProcessConvert(table, pgConnection); } if (conf.skipVacuum == null) { var vacuum = pgConnection.CreateCommand(); Console.WriteLine("VACUUM FULL! Может занять продолжительное время"); vacuum.CommandText = "VACUUM FULL;"; vacuum.CommandTimeout = 60 * 60;//1 hour vacuum.ExecuteNonQuery(); } if (conf.execAfter != null) { foreach (var script in conf.execAfter) { var command = pgConnection.CreateCommand(); Console.WriteLine("SCRIPT: {0}", script); command.CommandText = script; command.CommandTimeout = 60 * 60;//1 hour command.ExecuteNonQuery(); } } pgConnection.Clone(); }
//Get a connection public static void ReturnPgsConn(ref NpgsqlConnection pPgsConn) { try { pPgsConn = m_pPgsConn.Clone(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "ReturnPgsConn"); } }
internal ReadOnlyCollection <NpgsqlDbColumn> GetColumnSchema() { var fields = _rowDescription.Fields; if (fields.Count == 0) { return(new List <NpgsqlDbColumn>().AsReadOnly()); } var result = new List <NpgsqlDbColumn>(fields.Count); for (var i = 0; i < fields.Count; i++) { result.Add(null); } var populatedColumns = 0; // We have two types of fields - those which correspond to actual database columns // and those that don't (e.g. SELECT 8). For the former we load lots of info from // the backend (if fetchAdditionalInfo is true), for the latter we only have the RowDescription var columnFieldFilter = _rowDescription.Fields .Where(f => f.TableOID != 0) // Only column fields .Select(c => $"(attr.attrelid={c.TableOID} AND attr.attnum={c.ColumnAttributeNumber})") .Join(" OR "); if (_fetchAdditionalInfo && columnFieldFilter != "") { var query = _connection.PostgreSqlVersion >= new Version(8, 2) ? GenerateColumnsQuery(columnFieldFilter) : GenerateOldColumnsQuery(columnFieldFilter); #if NETSTANDARD1_3 using (var connection = _connection.Clone()) #else using (new TransactionScope(TransactionScopeOption.Suppress)) using (var connection = (NpgsqlConnection)((ICloneable)_connection).Clone()) #endif { connection.Open(); using (var cmd = new NpgsqlCommand(query, connection)) using (var reader = cmd.ExecuteReader()) { for (; reader.Read(); populatedColumns++) { var column = LoadColumnDefinition(reader, _connection.Connector.TypeHandlerRegistry); var ordinal = fields.FindIndex(f => f.TableOID == column.TableOID && f.ColumnAttributeNumber - 1 == column.ColumnAttributeNumber); Debug.Assert(ordinal >= 0); // The column's ordinal is with respect to the resultset, not its table column.ColumnOrdinal = ordinal; result[ordinal] = column; } } if (populatedColumns == fields.Count) { // All columns were regular table columns that got loaded, we're done Debug.Assert(result.All(c => c != null)); return(result.AsReadOnly()); } } } // We had some fields which don't correspond to regular table columns (or fetchAdditionalInfo is false). // Fill in whatever info we have from the RowDescription itself for (var i = 0; i < fields.Count; i++) { if (result[i] != null) { continue; } var column = SetUpNonColumnField(fields[i]); column.ColumnOrdinal = i; result[i] = column; populatedColumns++; } if (populatedColumns != fields.Count) { throw new NpgsqlException("Could not load all columns for the resultset"); } return(result.AsReadOnly()); }
private void button1_Click(object sender, EventArgs e) { Dados_nfe DadosNfe = new Dados_nfe(); string html = null; //STRING DE CONEXÃO NpgsqlConnection conn = new NpgsqlConnection(connstring); try { conn.Open(); } catch (Exception msg) { MessageBox.Show("Não foi possivel conectar com o Banco de Dados ! \n" + msg.ToString(), "Erro", MessageBoxButtons.OK, ErrorIcon); return; } //VERIFICAÇÃO PARA SABE SE EXISTEM DADOS A SEREM IMPORTADOS if (webBrowser1.Url.ToString() != "http://www.nfe.fazenda.gov.br/portal/consulta.aspx?tipoConsulta=completa&tipoConteudo=XbSeqxE8pl8=") { try { webBrowser1.Navigate("http://www.nfe.fazenda.gov.br/portal/consultaImpressao.aspx?tipoConsulta=completa"); while (webBrowser1.ReadyState != WebBrowserReadyState.Complete) { Application.DoEvents(); } //CORREÇÃO DE ENCODING DO CONTEUDO HTML RETORNADO Stream documentStream = webBrowser1.DocumentStream; StreamReader reader = new StreamReader(documentStream, Encoding.GetEncoding(28591)); documentStream.Position = 0; html = reader.ReadToEnd(); //Metodo para ler os valores da Nota fiscal utilizando a Classe Dados_Nfe DadosNfe.LerValoresNota(html); NpgsqlCommand valida = new NpgsqlCommand("SELECT cli_nm_cliente, cli_id_cliente FROM " + schema + "bpm_cli_cliente where cli_cnpj = '" + DadosNfe._CnpjDest + "' AND cli_id_cliente NOT LIKE '%NFS%' ", conn); //NpgsqlDataReader sel = valida.ExecuteReader(); string sel; try //BLOCO PARA VALIDAR SE O CLIENTE E/OU A NOTA FISCAL ESTÃO CADASTRADOS NO BANCO DE DADOS { sel = (string)valida.ExecuteScalar(); if (sel == null) //SE O RETORNO DO SELECT QUE VERIFICA O CNPJ DO DESTINATARIO FOR NULO, ENTÃO A EMPRESA NÃO ESTÁ CADASTRADA NA BASE // E NÃO REALIZO O INSERT. { webBrowser1.GoBack(); InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0); //COM ESSE COMANDO EU ENCERRO A SESSÃO DO NAVEGADOR, SE MANTER A SESSÃO ABERTA, OCORRE ERROS NA CHAVE. MessageBox.Show("Empresa não cadastrada", "Aviso", MessageBoxButtons.OK, ErrorIcon); //Resetando dados da Classe para realizar parse para as proximas notas. DadosNfe.ResetaDados(); } else { NpgsqlDataReader sel_dat = valida.ExecuteReader(); while (sel_dat.Read()) //REALIZO NOVAMENTE O SELECT DESSA VEZ COM O ExecuteReader() QUE TRARA OS DOIS CAMPOS NUMERO DA EMPRESA E NOME //USO ESSES CAMPOS PARA VALIDAÇÃO E PARA INSERT NO BANCO. { string nr_empresa = sel_dat[0].ToString().Trim(); string nm_empresa = sel_dat[1].ToString().Trim(); NpgsqlCommand valida_nf = new NpgsqlCommand("SELECT HNFE_NR_NF FROM " + schema + "header_nfe_thor WHERE BPM_ID_CLIENTE = '" + sel_dat[1].ToString().Trim() + "' AND HNFE_CHAVE = '" + DadosNfe._NfChave + "'", conn); conn.Close(); //É NECESSARIO FECHAR A CONEXÃO E REABRIR PARA REALIZAR UM SELECT DENTRO DO OUTRO; conn.Open(); string sel_nf = (string)valida_nf.ExecuteScalar(); conn.Close(); if (sel_nf == null) //SE O RETORNO DO SELECT FOR NULO, A NOTA FISCAL NÃO EXISTE NA BASE, E COM ISSO EU REALIZO OS INSERTS { string Parametro_cfop = null; string Parametro_data = null; if (chbx_cfop.Checked != true && txt_cfop.Text.Length != 0) { Parametro_cfop = txt_cfop.Text; } if (txt_data_ref.Text != " /" && txt_data_ref.Text != " -") { Parametro_data = txt_data_ref.Text; } string InsertHeaderCommand = DadosNfe.SQLInsertHeader(html, Parametro_data, schema, nr_empresa, nm_empresa); string InsertDetailCommand = DadosNfe.SQLInsertDetail(html, Parametro_cfop, schema, nr_empresa, Parametro_data, nm_empresa); string InsertFornecedorCommand = DadosNfe.SQLInsertFornecedor(html, schema, nr_empresa, nm_empresa); string[,] SQLInsertProdutosCommand = DadosNfe.SQLInsertProdutos(html, schema, nr_empresa, nm_empresa); using (TransactionScope Transaction = new TransactionScope()){ //Abrinco conexão com o Banco conn.Open(); //Inserindo Header NpgsqlCommand InsertHeader = new NpgsqlCommand(InsertHeaderCommand, conn); object resp_InsertHeader = InsertHeader.ExecuteScalar(); //Antes de Inserir o Fornecedore, verifico se o mesmo já existe na Base. NpgsqlCommand valida_forn = new NpgsqlCommand("SELECT forn_cgc FROM " + schema + "CAD_FORNEC WHERE BPM_ID_CLIENTE = '" + nm_empresa + "' AND forn_cod_cli = '" + DadosNfe._CnpjEmit + "'", conn); string sel_forn = (string)valida_forn.ExecuteScalar(); if (sel_forn == null) { NpgsqlCommand InsertFornecedor = new NpgsqlCommand(InsertFornecedorCommand, conn); object resp_InsertFornecedor = InsertFornecedor.ExecuteScalar(); } //Inserindo Detail NpgsqlCommand InsertDetail = new NpgsqlCommand(InsertDetailCommand, conn); object resp_InsertDetail = InsertDetail.ExecuteScalar(); //Antes de inserir os produtos com a query retornada pelo metodo da classe, verifico se o mesmo já exste. for (int x = 0; x < (SQLInsertProdutosCommand.Length / 2); x++) { NpgsqlCommand valida_prod = new NpgsqlCommand("SELECT prod_cod FROM " + schema + "cad_produtos WHERE BPM_ID_CLIENTE = '" + nm_empresa + "' AND prod_cod = '" + SQLInsertProdutosCommand[x, 0] + "'", conn); string select_prod = (string)valida_prod.ExecuteScalar(); if (select_prod == null) { NpgsqlCommand InsertProdutos = new NpgsqlCommand(SQLInsertProdutosCommand[x, 1], conn); object resp_InsertProdutos = InsertProdutos.ExecuteScalar(); } } //Resetando os dados da Classe para realizar o parse para outras Notas. DadosNfe.ResetaDados(); webBrowser1.GoBack(); while (webBrowser1.ReadyState != WebBrowserReadyState.Complete) { Application.DoEvents(); } InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0); MessageBox.Show("Nota inserida corretamente", "Aviso", MessageBoxButtons.OK, InfoIcon); Transaction.Complete(); } } else //CASO O SELECT RETORNE ALGO, EU INFORMO QUE A NOTA JÁ EXISTE NA BASE DE DADOS. { webBrowser1.GoBack(); while (webBrowser1.ReadyState != WebBrowserReadyState.Complete) { Application.DoEvents(); } MessageBox.Show("Nota já existe na base de dados", "Aviso", MessageBoxButtons.OK, WarningIcon); InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0); conn.Close(); //Resetando dados da Classe para realizar parse para as proximas notas. DadosNfe.ResetaDados(); } } } } finally { conn.Clone(); } } catch (Exception) { MessageBox.Show("Erro ao tentar ler os dados do site da receita,\n tente novamente ou entre em contato com o administrador!", "Erro", MessageBoxButtons.OK, ErrorIcon); webBrowser1.GoBack(); InternetSetOption(IntPtr.Zero, INTERNET_OPTION_END_BROWSER_SESSION, IntPtr.Zero, 0); conn.Close(); } } else { MessageBox.Show("Não foram encontrados dados para importar, por favor insira a chave e o captcha para importar os dados", "Aviso", MessageBoxButtons.OK, WarningIcon); return; } webBrowser1.Focus(); }
internal ReadOnlyCollection <NpgsqlDbColumn> GetColumnSchema() { var fields = _rowDescription.Fields; if (fields.Count == 0) { return(new List <NpgsqlDbColumn>().AsReadOnly()); } var result = new List <NpgsqlDbColumn>(fields.Count); for (var i = 0; i < fields.Count; i++) { result.Add(null); } var populatedColumns = 0; // We have two types of fields - those which correspond to actual database columns // and those that don't (e.g. SELECT 8). For the former we load lots of info from // the backend, for the latter we only have the RowDescription var columnFieldFilter = _rowDescription.Fields .Where(f => f.TableOID != 0) // Only column fields .Select(c => $"(attr.attrelid={c.TableOID} AND attr.attnum={c.ColumnAttributeNumber})") .Join(" OR "); if (columnFieldFilter != "") { var query = string.Format(GetColumnsQuery, columnFieldFilter); #if NET45 || NET451 using (var connection = (NpgsqlConnection)((ICloneable)_connection).Clone()) #else using (var connection = _connection.Clone()) #endif { connection.Open(); using (var cmd = new NpgsqlCommand(query, connection)) using (var reader = cmd.ExecuteReader()) { for (; reader.Read(); populatedColumns++) { var column = LoadColumnDefinition(reader); var ordinal = fields.FindIndex(f => f.TableOID == column.TableOID && f.ColumnAttributeNumber - 1 == column.ColumnAttributeNumber); Contract.Assert(ordinal >= 0); var field = fields[ordinal]; Contract.Assert(field.Name == column.ColumnName); // The column's ordinal is with respect to the resultset, not its table column.ColumnOrdinal = ordinal; // Overwrite the column's DataType because the field provides more information about it column.DataType = field.FieldType; result[ordinal] = column; } } if (populatedColumns == fields.Count) { // All columns were regular table columns that got loaded, we're done Contract.Assert(result.All(c => c != null)); return(result.AsReadOnly()); } } } // We had some fields which don't correspond to regular table columns // Fill in whatever info we have from the RowDescription itself for (var i = 0; i < fields.Count; i++) { if (result[i] != null) { continue; } var column = SetUpNonColumnField(fields[i]); column.ColumnOrdinal = i; result[i] = column; populatedColumns++; } if (populatedColumns != fields.Count) { throw new NpgsqlException("Could not load all columns for the resultset"); } return(result.AsReadOnly()); }
private void btnInstall_Click(object sender, EventArgs e) { if (ValidateInfo()) { pbDBInstall.Select(); btnInstall.Visible = false; btnBack.Visible = false; lblWaitingtxt.Visible = true; pbDBInstall.Visible = true; Application.DoEvents(); Thread.Sleep(1000); if (CreateNg) { pbDBInstall.Maximum = 11; } try { string superDBConnectionString = Config.GetSuperConnectionString(); string sysDBConnectionString = Config.GetSysDBConnectionString(); string projectDBConnectionString = Config.GetProjectDBConnectionString(ProjectDBName); string changeOwnerConnectionString = Config.GetChangeOwnerConnectionString(ProjectDBName); //create role DataTable dtRole = _dbInstall.CheckRoleExist(superDBConnectionString, ProjectDBName.ToUpper()); if (dtRole.Rows.Count <= 0) { _dbInstall.CreateRole(superDBConnectionString, ProjectDBName.ToUpper()); } ProgressBarUpdate(); LogWrite("User Role Installed."); //create database bool createDb = true; DataTable dtDB = _dbInstall.CheckDatabaseExist(superDBConnectionString, ProjectDBName.ToUpper()); if (dtDB.Rows.Count > 0) { if (Config.Host == "37.34.53.43") { MessageBox.Show("Database already exist"); pbDBInstall.Value = 0; pbDBInstall.Visible = false; lblWaitingtxt.Visible = false; btnInstall.Visible = true; btnBack.Visible = true; return; } if (MessageBox.Show("Database already exist. Sure to replace it?", "Replace", MessageBoxButtons.YesNo) == System.Windows.Forms.DialogResult.Yes) { if (!_dbInstall.DeleteDB(Config.GetSuperConnectionString(), ProjectDBName.ToUpper())) { success = false; ErrorMsg = string.Format("Can not delete database: {0}. Please delete the database manually and run installer again.", ProjectDBName); OpenSummaryWindow(); return; } } else { createDb = false; } } if (createDb) { _dbInstall.CreateDatabase(superDBConnectionString, ProjectDBName.ToUpper(), Config.TemplateDBName); } ProgressBarUpdate(); LogWrite("Database Created."); //chnage owner _dbInstall.ChangeOwner(changeOwnerConnectionString, ProjectDBName.ToUpper()); ProgressBarUpdate(); LogWrite("Owner is changed."); using (NpgsqlConnection pgConn = new NpgsqlConnection(sysDBConnectionString)) { pgConn.Open(); using (NpgsqlTransaction tran = pgConn.BeginTransaction()) { // Sys _dbInstall.Sys_Delete_Prev_Data(sysDBConnectionString, "Sys_BGT_DB\\1BGT_Sys_DB_Script_DELETE.sql", ProjectDBName, sysSuperUserId, TeamleadUserID, GuestUserID); ProgressBarUpdate(); LogWrite("Sys table is clean."); _dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\2BGT_Sys_DB_Script_Role_Independend_Entry-Reports.sql", ProjectDBName, Config.Port); LogWrite("Sys Role independent entry-reports data installed."); ProgressBarUpdate(); _dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\3BGT_Sys_DB_Script_Role_Independend-Report_Functions.sql", ProjectDBName, Config.Port); LogWrite("Role independent report functions installed."); ProgressBarUpdate(); _dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\4BGT_Sys_DB_Script_Role_Independend-Metadata.sql", ProjectDBName, Config.Port); ProgressBarUpdate(); LogWrite("Role independent metadata installed."); _dbInstall.Sys_Install_SuperRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_SUPER.sql", ProjectDBName, sysSuperUserName, sysSuperUserId, sysSuperUserPassword); LogWrite("Super Role Installed."); _dbInstall.Sys_Install_TeamleadRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_Teamlead.sql", ProjectDBName, TeamleadUserName, TeamleadUserID, TeamleadUserPassword); LogWrite("Teamlead Role Installed."); _dbInstall.Sys_Install_GuestRole(sysDBConnectionString, "Sys_BGT_DB\\5BGT_Sys_DB_Script_Guest.sql", ProjectDBName, GuestUserName, GuestUserID, GuestUserPassword); LogWrite("Guest Role Installed."); //dsah-board entry _dbInstall.Sys_Install_Role_Independent_Data(sysDBConnectionString, "Sys_BGT_DB\\6BGT_Sys_DB_Script_Dashboard.sql", ProjectDBName, Config.Port); LogWrite("Dashboard installed."); ProgressBarUpdate(); tran.Commit(); pgConn.Clone(); } } // Project _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\1Tables.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\2Tables_Index.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\3Tables_Trigger.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\4Tables_values.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date, Bronhouder, ApplicationName, Administration, ReferencePrefix, IdentificationPrefix, MsgToelichting); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\5Functions.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\6Views.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); _dbInstall.Install_ProjectData(projectDBConnectionString, "AOXX_DB\\7GT_Views.sql", ProjectDBName, Organization, MethodIndex, MethodName, Precision, Date); ProgressBarUpdate(); LogWrite("Project data installed."); //run ned graphics db script if (CreateNg) { using (NpgsqlConnection pgConn = new NpgsqlConnection(sysDBConnectionString)) { pgConn.Open(); using (NpgsqlTransaction tran = pgConn.BeginTransaction()) { _dbInstall.Install_NedGraphics_Sys(sysDBConnectionString, "NG\\NedGraphics_ChaloIS_SYS_TABLES_SuperUser.sql", ProjectDBName, Config.Port); ProgressBarUpdate(); LogWrite("Ned Graphics Sys data installed."); _dbInstall.Install_NedGraphics_Projects(projectDBConnectionString, "NG\\NedGraphics_Script.sql", ProjectDBName); ProgressBarUpdate(); LogWrite("Ned Graphics Project data installed."); tran.Commit(); pgConn.Close(); } } } success = true; LogWrite("Installation is successful."); } catch (Exception ex) { LogWrite(ex.Message.ToString()); success = false; ErrorMsg = ex.Message; RollBack(); } OpenSummaryWindow(); pbDBInstall.Value = 0; } }
internal NpgsqlConnection GetConnection() { return(Connection.Clone()); }
public NpgsqlConnection GetConnection() { return(Connection.Clone()); }
public void Desconectar() { conexao.Clone(); conexao.Dispose(); }