protected void Button1_Click(object sender, EventArgs e) { string checks; string checkw; string id_sali = DropDownList1.SelectedItem.ToString(); string id_wyp = DropDownList2.SelectedItem.ToString(); NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn); NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn); checks = (String)check1.ExecuteScalar(); checkw = (String)check2.ExecuteScalar(); if (checks == id_sali && checkw == id_wyp) { Label2.Text = ""; Label3.Text = "Wyposażenie jest już przypisane do sali!"; } else if (checkw != id_wyp) { NpgsqlCommand add = new NpgsqlCommand("insert into wyp_sali values ('" + id_wyp + "', '" + id_sali + "')", conn); add.ExecuteScalar(); conn.Close(); Label2.Text = "Dodano wyposażenie do sali!"; Label3.Text = ""; } }
private bool LoginUser(Credentials credentials) { string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; NpgsqlConnection conn = new NpgsqlConnection(connectionString); NpgsqlCommand command = new NpgsqlCommand(@"SELECT COUNT(*) FROM clients WHERE email=@email AND password=@password"); command.Parameters.Add("@email", NpgsqlDbType.Varchar, 100).Value = credentials.Email; command.Parameters.Add("@password", NpgsqlDbType.Varchar, 100).Value = credentials.Password; command.Connection = conn; try { conn.Open(); int result = Convert.ToInt32(command.ExecuteScalar()); if (result > 0) return true; else return false; } catch (NpgsqlException e) { return false; } finally { conn.Close(); } }
internal static void PostgreSqlTest() { const string connectionString = @"Server=127.0.0.1;Port=5432;Database=test;User Id=postgres;Password=123456;"; try { using (NpgsqlConnection npgsql = new NpgsqlConnection(connectionString)) { npgsql.Open(); //执行查询命令 NpgsqlCommand command = new NpgsqlCommand("select *from student", npgsql); NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine($"SNO:{reader[0]} SNAME:{reader[1]} AGE:{reader[2]}"); } reader.Close(); //执行ExecuteScalar方法 command = new NpgsqlCommand("select count(*) from student", npgsql); int count = Convert.ToInt32(command.ExecuteScalar()); Console.WriteLine($"总共有{count}条记录"); } } catch (NpgsqlException) { throw; } }
protected void Button1_Click(object sender, EventArgs e) { string checks; string checkw; string id_sali = TextBox1.Text; string id_wyp = TextBox2.Text; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from wyp_sali where id_sali = '" + id_sali + "'", conn); NpgsqlCommand check2 = new NpgsqlCommand("select id_wyp from wyp_sali where id_sali = '" + id_sali + "'and id_wyp = '" + id_wyp + "'", conn); checks = (String)check1.ExecuteScalar(); checkw = (String)check2.ExecuteScalar(); if (checkw != id_wyp) { Label2.Text = ""; Label3.Text = "Wyposażenie nie istnieje w tej sali"; } else if (checkw == id_wyp && checks == id_sali) { NpgsqlCommand del = new NpgsqlCommand("delete from wyp_sali where id_wyp ='" + id_wyp + "' and id_sali = '" + id_sali + "'", conn); del.ExecuteScalar(); conn.Close(); Label2.Text = "Usunięto wyposażenie z sali!"; Label3.Text = ""; } }
//создание формы public Form_Countries(string connStr) { InitializeComponent(); this.connStr = connStr; this.conn = new NpgsqlConnection(connStr); //инициализация грида this.table = new DataTable(); try { Class_Helper.SetColumns(table, dataGridView_Countries, columns); } catch (Exception ex) { throw new Exception("Can't init datagrid: " + ex.Message); } //получить ид России (сервер Б) try { string query = "SELECT id from sb.countries"; NpgsqlCommand cmd = new NpgsqlCommand(query, conn); conn.Open(); label_ServerB.Text = ((int)cmd.ExecuteScalar()).ToString(); conn.Close(); } catch (Exception ex) { conn.Close(); throw new Exception("Can't connect to B: " + ex.Message); } }
public void Incluir(Model_Vo_MovimentacaoEstoque pMovimentacaoEstoque) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into movimentacaoestoque(datahora,idclientesolicitante,idproduto,idreservaorigem,quantidade,valorunitario,valortotal,tipodemovimento) values (@datahora,@idclientesolicitante,@idproduto,@idreservaorigem,@quantidade,@valorunitario,@valortotal,@tipodemovimento);"; cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr(pMovimentacaoEstoque.DataHora, false)); cmd.Parameters.AddWithValue("@idclientesolicitante", pMovimentacaoEstoque.IdClienteSolicitante); cmd.Parameters.AddWithValue("@idproduto", pMovimentacaoEstoque.IdProduto); cmd.Parameters.AddWithValue("@idreservaorigem", pMovimentacaoEstoque.IdReservaOrigem); cmd.Parameters.AddWithValue("@quantidade", pMovimentacaoEstoque.Quantidade); cmd.Parameters.AddWithValue("@valorunitario", pMovimentacaoEstoque.ValorUnitario); cmd.Parameters.AddWithValue("@valortotal", pMovimentacaoEstoque.ValorTotal); cmd.Parameters.AddWithValue("@tipodemovimento", pMovimentacaoEstoque.TipoDeMovimento.ToString()); cn.Open(); pMovimentacaoEstoque.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public void Incluir(Model_Vo_Agenda pAgenda) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into agenda(datahorareserva,idcliente,idsala) values (@datahorareserva,@idcliente,@idsala);"; cmd.Parameters.AddWithValue("@datahorareserva", Dados.Model_Dao_Dados.ConverterDataToStr(pAgenda.DataHoraReserva, false)); cmd.Parameters.AddWithValue("@idcliente", pAgenda.IdCliente); cmd.Parameters.AddWithValue("@idsala", pAgenda.IdSala); cn.Open(); pAgenda.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public static object GetScalar(NpgsqlCommand command, NpgsqlConnection connection) { using (connection) { return command.ExecuteScalar(); } }
protected void Button1_Click(object sender, EventArgs e) { string check; string id_sali = TextBox1.Text; int pojemnosc = System.Int32.Parse(TextBox2.Text); string typ = DropDownList1.SelectedItem.ToString(); NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check1 = new NpgsqlCommand("select id_sali from sala where id_sali = '" + id_sali + "'", conn); check = (String)check1.ExecuteScalar(); if (check != id_sali) { NpgsqlCommand add = new NpgsqlCommand("insert into sala values ('" + id_sali + "', '" + typ + "', '" + pojemnosc + "')", conn); add.ExecuteScalar(); conn.Close(); TextBox1.Text = ""; TextBox2.Text = ""; Label2.Text = "Dodano salę!"; Label3.Text = ""; } else if (check == id_sali) { Label2.Text = ""; Label3.Text = "Sala już istnieje!"; } }
public int IntScalar(NpgsqlCommand cmd) { try { int dataset = 0; using (cmd) { cmd.Connection = GlobalVariables.Connection; dataset = Convert.ToInt32(cmd.ExecuteScalar()); } return dataset; } catch (NpgsqlException e) { sMessage = e.ToString(); MessageBox.Show(sMessage.ToString()); return 0; } catch (Exception e) { sMessage = e.ToString(); MessageBox.Show(sMessage.ToString()); return 0; } }
protected void Button1_Click(object sender, EventArgs e) { try { string id_wyp = TextBox1.Text; string check; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check_user = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn); check = (String)check_user.ExecuteScalar(); if (check == id_wyp) { NpgsqlCommand delete = new NpgsqlCommand("delete from wyposazenie where id_wyp = '" + id_wyp + "'", conn); delete.ExecuteScalar(); TextBox1.Text = ""; TextBox2.Text = ""; Label3.Text = "Wyposażenie zostało usunięta!"; Label2.Text = ""; } else if (check != id_wyp) { Label2.Text = "Brak wyposażenia w bazie!"; Label3.Text = ""; } } catch { } }
public static object Function(string connectionString, string functionName, params NpgsqlParameter[] parameters) { var npgsqlConnection = OpenConnection(connectionString); try { var command = new NpgsqlCommand(functionName, npgsqlConnection) { CommandType = CommandType.StoredProcedure }; command.Parameters.AddRange(parameters); var result = command.ExecuteScalar(); return result; } catch (Exception ex) { throw new FhirbaseException( $"Call {functionName} FHIRbase function failed. Reason {ex.Message}", ex); } finally { npgsqlConnection.Close(); } }
public void Incluir(Model_Vo_LivroCaixa pLivroCaixa) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into livrocaixa(datahora,descricao,idcontasareceber,tipodemovimento,valor) values (@datahora,@descricao,@idcontasareceber,@tipodemovimento,@valor);"; cmd.Parameters.AddWithValue("@datahora", Dados.Model_Dao_Dados.ConverterDataToStr((pLivroCaixa.DataHora), false)); cmd.Parameters.AddWithValue("@descricao", pLivroCaixa.Descricao); cmd.Parameters.AddWithValue("@idcontasareceber", pLivroCaixa.IdContasAReceber); cmd.Parameters.AddWithValue("@tipodemovimento", pLivroCaixa.TipoDeMovimento.ToString()); cmd.Parameters.AddWithValue("@valor", pLivroCaixa.Valor); cn.Open(); pLivroCaixa.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public void Incluir(Model_Vo_ContasAReceber pContasAReceber) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into contasareceber(datahoracriacao,descricao,idcliente,idproduto,idreservaorigem,origem,recebido,valorareceber) values (@datahoracriacao,@descricao,@idcliente,@idproduto,@idreservaorigem,@origem,@recebido,@valorareceber);"; cmd.Parameters.AddWithValue("@datahoracriacao", Dados.Model_Dao_Dados.ConverterDataToStr(pContasAReceber.DataHoraCriacao, false)); cmd.Parameters.AddWithValue("@descricao", pContasAReceber.Descricao); cmd.Parameters.AddWithValue("@idcliente", pContasAReceber.IdCliente); cmd.Parameters.AddWithValue("@idproduto", pContasAReceber.IdProduto); cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem); cmd.Parameters.AddWithValue("@origem", pContasAReceber.Origem.ToString()); cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido); cmd.Parameters.AddWithValue("@valorareceber", pContasAReceber.ValorAReceber); cn.Open(); pContasAReceber.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public void Incluir(Model_Vo_Sala pSala) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into sala(nome,capacidade,tipo,idproduto) values (@nome,@capacidade,@tipo,@idproduto);"; cmd.Parameters.AddWithValue("@nome", pSala.Nome); cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade); cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString()); cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto)); cn.Open(); pSala.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
protected void Button1_Click(object sender, EventArgs e) { try { Int32 numer = System.Int32.Parse(TextBox1.Text); Int32 check; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check_user = new NpgsqlCommand("select nr_rezerwacji from rezerwacja where nr_rezerwacji = '" + numer + "'", conn); check = (Int32)check_user.ExecuteScalar(); if (check == numer) { NpgsqlCommand delete = new NpgsqlCommand("delete from rezerwacja where nr_rezerwacji = '" + numer + "'", conn); delete.ExecuteScalar(); TextBox1.Text = ""; TextBox2.Text = ""; Label3.Text = "Rezerwacja została anulowana!"; Label2.Text = ""; } else if (check != numer) { Label2.Text = "Brak rezerwacji w bazie!"; Label3.Text = ""; } } catch { } }
public static void CreateTables(NpgsqlConnection conn) { using (var cmdRename1 = new NpgsqlCommand("select table_name from information_schema.tables where table_name = 'rn_results'", conn)) { var tblExists = false; using (var rdr1 = cmdRename1.ExecuteReader()) { tblExists = rdr1.Read(); } cmdRename1.Dispose(); if (tblExists) { var curCount = 0; using (var cmdRename2 = new NpgsqlCommand("select count(*) from rn_results", conn)) { curCount = Convert.ToInt32(cmdRename2.ExecuteScalar()); } if (curCount == 0) { using (var cmdRename3 = new NpgsqlCommand("drop table rn_results", conn)) { cmdRename3.ExecuteNonQuery(); } } else { using (var cmdRename2 = new NpgsqlCommand("alter table rn_results rename to rn_results_old" + (curDBVersion - 1).ToString(), conn)) { cmdRename2.ExecuteNonQuery(); } } } } using (var cmd1 = new NpgsqlCommand("drop table rn_version;", conn)) { cmd1.ExecuteNonQuery(); } using (var cmd2 = new NpgsqlCommand("create table rn_version (ver integer);", conn)) { cmd2.ExecuteNonQuery(); } using (var cmd3 = new NpgsqlCommand("insert into rn_version (ver) values (" + curDBVersion.ToString() + ")", conn)) { cmd3.ExecuteScalar(); } using (var cmd4 = new NpgsqlCommand("create table rn_results (email varchar(100), name varchar(100), phone_no varchar(100), daum_id varchar(100), work_place varchar(200), client_ip varchar(50), sign_image1 text, sign_image2 text, insert_dt timestamp, sign_radio1 integer, sign_radio2 integer, PRIMARY KEY(email));", conn)) { cmd4.ExecuteNonQuery(); } using (var cmd5 = new NpgsqlCommand("create index rn_results_idx_insert_dt on rn_results (insert_dt)", conn)) { cmd5.ExecuteNonQuery(); } }
public Boolean addTeam(string team) { string sql = "SELECT COUNT(*) FROM team WHERE name = :team"; NpgsqlCommand command = new NpgsqlCommand(sql, conn); command.Parameters.Add(new NpgsqlParameter("team", NpgsqlDbType.Text)); command.Parameters[0].Value = team; long numFound = (long) command.ExecuteScalar(); if (numFound > 0) { sql = "UPDATE team SET num_matches = num_matches + 1 WHERE name = :team"; command = new NpgsqlCommand(sql, conn); command.Parameters.Add(new NpgsqlParameter("team", team)); command.ExecuteNonQuery(); } else { sql = "INSERT INTO team (name, num_matches) VALUES (:team, 1)"; command = new NpgsqlCommand(sql, conn); command.Parameters.Add(new NpgsqlParameter("team", team)); command.ExecuteNonQuery(); } return true; }
private PostgreSQLDBManager() { _connection = new NpgsqlConnection(Configuration.Settings("PostgreSQLDBConnectionString", "User ID=postgres;Password=Passw0rd;Host=localhost;Port=5432;Database=postgres;Pooling=true;")); try { _connection.Open(); } catch (Exception exception) { Log.Error(ExceptionHelper.FormatStackTrace("Connect to PostgreSQL failed.",exception)); throw new RuntimeBinderException("Connect to Database Failed",exception); } var cmd = new NpgsqlCommand("select count(*) from content", _connection); var count = Convert.ToInt32(cmd.ExecuteScalar()); if (count > 0) return; var rootId = Configuration.Settings("Root", "42c5eb51-0e1c-4de1-976d-733bde24220a"); CreateSubItem(rootId, "Folder", "Data"); CreateSubItem(rootId, "Folder", "UI"); CreateSubItem(rootId, "Folder", "Translation"); CreateSubItem(rootId, "Folder", "Project"); CreateSubItem(rootId, "Folder", "Result"); CreateSubItem("", "Project", "Root", rootId); }
public void Incluir(Model_Vo_Produto pProduto) { SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); //command SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = "insert into produto(descricao,unidade,estoque,valordevenda,observacao) values (@descricao,@unidade,@estoque,@valordevenda,@observacao);"; cmd.Parameters.AddWithValue("@descricao", pProduto.Descricao); cmd.Parameters.AddWithValue("@unidade", pProduto.Unidade); cmd.Parameters.AddWithValue("@estoque", pProduto.Estoque); cmd.Parameters.AddWithValue("@valordevenda", pProduto.ValorDeVenda); cmd.Parameters.AddWithValue("@observacao", pProduto.Observacao); cn.Open(); pProduto.Id = Convert.ToInt32(cmd.ExecuteScalar()); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public int Salvarcliente(string nome, int codempresa) { using (var conn = new NpgsqlConnection(Connectionstring)) { conn.Open(); using (var trans = conn.BeginTransaction()) { try { int id; using (var cmd = new NpgsqlCommand("SELECT MIN(PORT_VNC) FROM PORTAS WHERE PORT_VNC NOT iN(SELECT CLIE_IDVNC FROM CLIENTE)", conn, trans)) { id = int.Parse(cmd.ExecuteScalar().ToString()); } using ( var cmd = new NpgsqlCommand( "INSERT INTO cliente (clie_nome,clie_idvnc, empr_codigo) values ('" + nome + "'," + id + ","+codempresa+")", conn, trans)) { cmd.ExecuteNonQuery(); trans.Commit(); return id; } } catch (Exception) { trans.Rollback(); return 0; } } } }
public static void Main(String[] args) { NpgsqlConnection conn = null; try { conn = new NpgsqlConnection(NpgsqlTests.getConnectionString()); conn.Open(); Console.WriteLine("Connection completed"); NpgsqlCommand command = new NpgsqlCommand(); command.CommandText = "select count(*) from tablea"; command.Connection = conn; Object result = command.ExecuteScalar(); Console.WriteLine(result.ToString()); } catch(NpgsqlException e) { Console.WriteLine(e.ToString()); } finally { if (conn != null) conn.Close(); } }
/// <summary> /// Get Message by Container Number /// </summary> /// <param name="containerNumber"></param> /// <returns></returns> public string GetMessageByContainerNumber(string containerNumber) { if (string.IsNullOrEmpty(containerNumber)) { return(string.Empty); } string message = string.Empty; try { using (NpgsqlConnection npgsqlConnection = AppConfig.GetConnection()) { if (npgsqlConnection.State == ConnectionState.Closed) { npgsqlConnection.Open(); } string query = string.Format("SELECT message FROM {0} WHERE cont = @ContainerNumber and disableduntil <= now()", DEFAULT_TABLE); using (NpgsqlCommand npgsqlCommand = new Npgsql.NpgsqlCommand(query, npgsqlConnection)) { npgsqlCommand.Parameters.AddWithValue("@ContainerNumber", containerNumber); object result = npgsqlCommand.ExecuteScalar(); if (result != null) { message = result.ToString(); } } } } catch (Exception ex) { throw ex; } return(message); }
public static string Get(string strcmd) { if (connectionString == null || connectionString.Length <= 0) { Initialize(); } using (NpgsqlConnection conn = new NpgsqlConnection(connectionString)) { string ret = null; NpgsqlCommand cmd = new NpgsqlCommand(strcmd, conn); conn.Open(); try { ret = cmd.ExecuteScalar().ToString(); } catch(Exception e) { } finally { conn.Close(); } return ret; } }
protected void Button1_Click(object sender, EventArgs e) { string check; string typ_sali = TextBox1.Text; string opis = TextBox2.Text; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check1 = new NpgsqlCommand("select typ_sali from typ_sali where typ_sali = '" + typ_sali + "'", conn); check = (String)check1.ExecuteScalar(); if (check != typ_sali) { NpgsqlCommand add = new NpgsqlCommand("insert into typ_sali values ('" + typ_sali + "', '" + opis + "')", conn); add.ExecuteScalar(); conn.Close(); TextBox1.Text = ""; TextBox2.Text = ""; Label2.Text = "Dodano typ!"; Label3.Text = ""; } else if (check == typ_sali) { Label2.Text = ""; Label3.Text = "Typ już istnieje!"; } }
public static void Initialize() { var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString; var connectionBuilder = new NpgsqlConnectionStringBuilder(connectionString); //connect to postgres database to create a new database var databaseName = connectionBuilder.Database; connectionBuilder.Database = "postgres"; connectionString = connectionBuilder.ToString(); using (var conn = new NpgsqlConnection(connectionString)) { conn.Open(); bool dbExists; using (var cmd = new NpgsqlCommand()) { cmd.CommandText = string.Format(@"SELECT TRUE FROM pg_database WHERE datname='{0}'", databaseName); cmd.Connection = conn; var result = cmd.ExecuteScalar(); dbExists = result != null && Convert.ToBoolean(result); } if (dbExists) { DoClean(conn); } else { DoCreate(conn, databaseName); } } }
protected void Button1_Click(object sender, EventArgs e) { string check; string id_wyp = TextBox1.Text; string nazwa = TextBox2.Text; string opis = TextBox3.Text; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check1 = new NpgsqlCommand("select id_wyp from wyposazenie where id_wyp = '" + id_wyp + "'", conn); check = (String)check1.ExecuteScalar(); if (check != id_wyp) { NpgsqlCommand add = new NpgsqlCommand("insert into wyposazenie values ('" + id_wyp + "', '" + nazwa + "', '" + opis + "')", conn); add.ExecuteScalar(); conn.Close(); TextBox1.Text = ""; TextBox2.Text = ""; TextBox3.Text = ""; Label2.Text = "Dodano wyposażenie!"; Label3.Text = ""; } else if (check == id_wyp) { Label2.Text = ""; Label3.Text = "Wyposażenie już istnieje!"; } }
protected void Button1_Click(object sender, EventArgs e) { try { string user_id = TextBox1.Text; string check; NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;Password=projekt;Database=projekt;"); conn.Open(); NpgsqlCommand check_user = new NpgsqlCommand("select id_prac from pracownicy where id_prac = '" + user_id + "'", conn); check = (String)check_user.ExecuteScalar(); if (check == user_id) { NpgsqlCommand delete = new NpgsqlCommand("delete from pracownicy where id_prac = '" + user_id + "'", conn); delete.ExecuteScalar(); TextBox1.Text = ""; TextBox2.Text = ""; Label3.Text = "Użytkownik został usunięty!"; Label2.Text = ""; } else if (check != user_id) { Label2.Text = "Brak użytkownika w bazie!"; Label3.Text = ""; } } catch { } }
public static void HandleRedirects(XmlReader reader, NpgsqlConnection connection) { var redirect_to = reader.GetAttribute("redirect"); if (string.IsNullOrEmpty(redirect_to)) return; var title = reader.GetAttribute("title"); var fetch_row_command = new NpgsqlCommand(@"with redirect_page as (SELECT id,title,redirect FROM pages WHERE title = :title) select * from redirect_page union all select page.id,page.title,page.redirect from pages page join redirect_page on page.title = redirect_page.redirect", connection); fetch_row_command.Parameters.AddWithValue("title", title); var query_reader = fetch_row_command.ExecuteReader(); if (!query_reader.HasRows) return; query_reader.Read(); int redirect_id = query_reader.GetInt32(0); query_reader.Read(); int real_page_id = query_reader.GetInt32(0); query_reader.Close(); var update = string.Format(@"UPDATE pages SET links = (links - ARRAY[{0}]) || ARRAY[{1}] WHERE links @> ARRAY[{0}];", redirect_id, redirect_id); var command = new NpgsqlCommand(update, connection); command.ExecuteScalar(); }
public void ChangeDatabase() { Conn.ChangeDatabase("template1"); var command = new NpgsqlCommand("select current_database()", Conn); var result = (String)command.ExecuteScalar(); Assert.AreEqual("template1", result); }
public void DistributedTransactionRollback() { int field_serial1; int field_serial2; var connectionString = ConnectionString + ";enlist=true"; using (var scope = new TransactionScope()) { //UseStringParameterWithNoNpgsqlDbType using (var connection = new NpgsqlConnection(connectionString)) { connection.Open(); var command = new NpgsqlCommand("insert into data (field_text) values (:p0)", connection); command.Parameters.Add(new NpgsqlParameter("p0", "test")); Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Text); Assert.AreEqual(command.Parameters[0].DbType, DbType.String); object result = command.ExecuteNonQuery(); Assert.AreEqual(1, result); field_serial1 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar(); var command2 = new NpgsqlCommand("select field_text from data where field_serial = (select max(field_serial) from data)", connection); result = command2.ExecuteScalar(); Assert.AreEqual("test", result); } //UseIntegerParameterWithNoNpgsqlDbType using (var connection = new NpgsqlConnection(connectionString)) { connection.Open(); var command = new NpgsqlCommand("insert into data(field_int4) values (:p0)", connection); command.Parameters.Add(new NpgsqlParameter("p0", 5)); Assert.AreEqual(command.Parameters[0].NpgsqlDbType, NpgsqlDbType.Integer); Assert.AreEqual(command.Parameters[0].DbType, DbType.Int32); Object result = command.ExecuteNonQuery(); Assert.AreEqual(1, result); field_serial2 = (int) new NpgsqlCommand("select max(field_serial) from data", connection).ExecuteScalar(); var command2 = new NpgsqlCommand( "select field_int4 from data where field_serial = (select max(field_serial) from data)", connection); result = command2.ExecuteScalar(); Assert.AreEqual(5, result); // using new connection here... make sure we can't see previous results even though // it is the same distributed transaction var command3 = new NpgsqlCommand("select field_text from data where field_serial = :p0", connection); command3.Parameters.Add(new NpgsqlParameter("p0", field_serial1)); result = command3.ExecuteScalar(); // won't see value of "test" since that's // another connection Assert.AreEqual(null, result); } // not commiting here. } // This is an attempt to wait for the distributed transaction to rollback // not guaranteed to work, but should be good enough for testing purposes. System.Threading.Thread.Sleep(500); AssertNoPreparedTransactions(); // ensure they no longer exist since we rolled back AssertRowNotExist("field_text", field_serial1); AssertRowNotExist("field_int4", field_serial2); }
public static object GetScalarValue(Npgsql.NpgsqlCommand command) { if (command != null) { using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString())) { command.Connection = connection; connection.Open(); return(command.ExecuteScalar()); } } return(null); }
/// <summary> /// 查询一个字段 /// </summary> /// <param name="sql"></param> /// <returns></returns> public object ExecuteScalar(string sql) { object obj = null; try { using (Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(sql, conn)) { obj = command.ExecuteScalar(); } } catch { Console.WriteLine("查询数据失败"); } return(obj); }
internal void InsertAttachment(long newsId, string key, byte[] value) { var strInsertAttachmentCommand = @"INSERT INTO news.news_attachment( news_attachment_id, attachment_description, attachment_file, attachment_file_path, type, userid, bst_suggestion_register_id, news_id, proposed_news_approval_id, attachment_type_id) VALUES (nextval('log_seq'), null, '@value', '" + key + @"', null, null, null, '" + newsId + @"', null, null);"; var cmd = new Npgsql.NpgsqlCommand(strInsertAttachmentCommand, conn); cmd.Parameters.AddWithValue("value", value); cmd.Parameters.Add(new NpgsqlParameter() { }); cmd.ExecuteScalar(); }
private long InsertNews(RasadNews rasadNews, long forignSourceId) { var strInsertNewsCommand = @"INSERT INTO news.news( news_id, bst_suggestion_register, consideration, realtime, news_content, declarationdate_time, news_lead, news_level_type, news_number, occurrence_time, register_date, register_news_type, title, approvedorganizationalposition, bst_accuracy_degree_id, bst_confidential_id, bst_refer_level_id, forignsource_forign_source_id, newspic_news_pic_id, organizationalposition_id, news_sources_str, news_types_str, bst_news_type_id) VALUES (nextval('log_seq'), null, null, false, '" + rasadNews.Content + @"', '" + rasadNews.PubTime + @"', '" + rasadNews.Description + @"', null, '" + GetStationNumber() + "-" + DateTime.Now.ToString("yyyyMMddHHss") + @"', '" + rasadNews.PubTime + @"', '" + rasadNews.PubTime + @"', 10, '" + rasadNews.Title + @"', null, 950, 218, 1, '" + forignSourceId + @"', null, null, null, null, null);"; var cmd = new Npgsql.NpgsqlCommand(strInsertNewsCommand, conn); return((long)cmd.ExecuteScalar()); }
private long InsertForignSource(RasadNews rasadNews, string folder, long line) { var strInsertForignCommand = @"INSERT INTO news.forign_source( forign_source_id, author, category_type, content, description, fetch_time, folder, id, insert_on, line, mesbahdocid, page_url, pub_time, source_id, title, news_id) VALUES (nextval('log_seq'), @autor, @CategoryType, @Content, @Description, '" + new DateTime(long.Parse(rasadNews.FetchTime)) + "', '" + folder + "', " + rasadNews.Id + @", '" + DateTime.Now + "', " + line + ", " + rasadNews.mesbahdocid + ", '" + rasadNews.PageUrl + @"', '" + new DateTime(long.Parse(rasadNews.PubTime)) + @"', '" + rasadNews.SourceId + "', @Title, null) RETURNING forign_source_id;"; var cmd = new Npgsql.NpgsqlCommand(strInsertForignCommand, conn); cmd.Parameters.AddWithValue("autor", rasadNews.Author); cmd.Parameters.AddWithValue("CategoryType", rasadNews.CategoryType); cmd.Parameters.AddWithValue("Content", (rasadNews.Content.Length > 10000) ? rasadNews.Content.Substring(0, 9999) : rasadNews.Content); cmd.Parameters.AddWithValue("Description", rasadNews.Description); cmd.Parameters.AddWithValue("Title", rasadNews.Title); var a = cmd.ExecuteScalar(); return((long)a); }
/// <summary> /// This method checks if the connector is still ok. /// We try to send a simple query text, select 1 as ConnectionTest; /// </summary> internal Boolean IsValid() { try { // Here we use a fake NpgsqlCommand, just to send the test query string. // Get random test value. Byte[] testBytes = new Byte[2]; rng.GetNonZeroBytes(testBytes); String testValue = String.Format("Npgsql{0}{1}", testBytes[0], testBytes[1]); //Query(new NpgsqlCommand("select 1 as ConnectionTest", this)); string compareValue = string.Empty; string sql = "select '" + testValue + "'"; // restore initial connection parameters resetted by "Discard ALL" if (SupportsDiscard) { sql = this.initQueries + sql; } using (NpgsqlCommand cmd = new NpgsqlCommand(sql, this)) { compareValue = (string)cmd.ExecuteScalar(); } if (compareValue != testValue) { return(false); } this.RequireReadyForQuery = true; } catch { return(false); } return(true); }
public bool ExecuteNonQuery(out int Id) { Id = 0; try { if (!string.IsNullOrEmpty(Script)) { if (Conectar()) { using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(Script, Connection)) { Parametros.ForEach(x => cmd.Parameters.AddWithValue(x.Parameter.ToString(), x.Valor)); Id = (Int32)cmd.ExecuteScalar(); return(true); } } else { return(false); } } msg = "Comando inválido."; return(false); } catch (NpgsqlException ex) { msg = ex.Message; return(false); } finally { if (Transacao == false) { Desconectar(); } } }
private static void DoDeriveParameters(NpgsqlCommand command) { // See http://www.postgresql.org/docs/current/static/catalog-pg-proc.html command.Parameters.Clear(); // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote. // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType) var serverVersion = command.Connection.Connector.ServerVersion; string query; string procedureName; string schemaName = null; var fullName = command.CommandText.Split('.'); if (fullName.Length > 1 && fullName[0].Length > 0) { // proargsmodes is supported for Postgresql 8.1 and above query = serverVersion >= new Version(8, 1, 0) ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname" : "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname"; schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); // The pg_temp pseudo-schema is special - it's an alias to a real schema name (e.g. pg_temp_2). // We get the real name with pg_my_temp_schema(). if (schemaName == "pg_temp") { using (var c = new NpgsqlCommand("SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()", command.Connection)) { schemaName = (string)c.ExecuteScalar(); } } } else { // proargsmodes is supported for Postgresql 8.1 and above query = serverVersion >= new Version(8, 1, 0) ? "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname" : "select proargnames, proargtypes from pg_proc where proname = :proname"; procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); } using (var c = new NpgsqlCommand(query, command.Connection)) { c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text)); c.Parameters[0].Value = procedureName.Replace("\"", "").Trim(); if (fullName.Length > 1 && !string.IsNullOrEmpty(schemaName)) { var prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text)); prm.Value = schemaName.Replace("\"", "").Trim(); } string[] names = null; uint[] types = null; char[] modes = null; using (var rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (rdr.Read()) { if (!rdr.IsDBNull(0)) { names = rdr.GetValue(0) as string[]; } if (serverVersion >= new Version("8.1.0")) { if (!rdr.IsDBNull(2)) { types = rdr.GetValue(2) as uint[]; } if (!rdr.IsDBNull(3)) { modes = rdr.GetValue(3) as char[]; } } if (types == null) { if (rdr.IsDBNull(1) || rdr.GetFieldValue <uint[]>(1).Length == 0) { return; // Parameterless function } types = rdr.GetFieldValue <uint[]>(1); } } else { throw new InvalidOperationException(string.Format("{0} does not exist in pg_proc", command.CommandText)); } } command.Parameters.Clear(); for (var i = 0; i < types.Length; i++) { var param = new NpgsqlParameter(); // TODO: Fix enums, composite types var npgsqlDbType = c.Connection.Connector.TypeHandlerRegistry[types[i]].NpgsqlDbType; if (npgsqlDbType == NpgsqlDbType.Unknown) { throw new InvalidOperationException(string.Format("Invalid parameter type: {0}", types[i])); } param.NpgsqlDbType = npgsqlDbType; if (names != null && i < names.Length) { param.ParameterName = ":" + names[i]; } else { param.ParameterName = "parameter" + (i + 1); } if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported) { param.Direction = ParameterDirection.Input; } else { switch (modes[i]) { case 'i': param.Direction = ParameterDirection.Input; break; case 'o': param.Direction = ParameterDirection.Output; break; case 'b': param.Direction = ParameterDirection.InputOutput; break; case 'v': throw new NotImplementedException("Cannot derive function parameter of type VARIADIC"); case 't': throw new NotImplementedException("Cannot derive function parameter of type TABLE"); default: throw new ArgumentOutOfRangeException("proargmode", modes[i], "Unknown code in proargmodes while deriving: " + modes[i]); } } command.Parameters.Add(param); } } }
/*/// <value>Counts the numbers of Connections that share * /// this Connector. Used in Release() to decide wether this * /// connector is to be moved to the PooledConnectors list.</value> * // internal int mShareCount;*/ /// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown) ? ProtocolVersion.Version3 : settings.Protocol; // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this); } catch (NpgsqlException ne) { // Check for protocol not supported. If we have been told what protocol to use, // we will not try this step. if (settings.Protocol != ProtocolVersion.Unknown) { throw; } // If we attempted protocol version 3, it may be possible to drop back to version 2. if (BackendProtocolVersion != ProtocolVersion.Version3) { throw; } NpgsqlError Error0 = (NpgsqlError)ne.Errors[0]; // If NpgsqlError..ctor() encounters a version 2 error, // it will set its own protocol version to version 2. That way, we can tell // easily if the error was a FATAL: protocol error. if (Error0.BackendProtocolVersion != ProtocolVersion.Version2) { throw; } // Try using the 2.0 protocol. _mediator.ResetResponses(); BackendProtocolVersion = ProtocolVersion.Version2; CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); // Establish protocol communication and handle authentication... CurrentState.Startup(this); } // Change the state of connection to open and ready. _connection_state = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this); ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } // Adjust client encoding. NpgsqlParameterStatus clientEncodingParam = null; if ( !ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) || (!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase)) ) { new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind(); } if (!string.IsNullOrEmpty(settings.SearchPath)) { /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String); * p.Value = settings.SearchPath; * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this); * commandSearchPath.Parameters.Add(p); * commandSearchPath.ExecuteNonQuery();*/ /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String); * p.Value = settings.SearchPath; * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this); * commandSearchPath.Parameters.Add(p); * commandSearchPath.ExecuteNonQuery();*/ // TODO: Add proper message when finding a semicolon in search_path. // This semicolon could lead to a sql injection security hole as someone could write in connection string: // searchpath=public;delete from table; and it would be executed. if (settings.SearchPath.Contains(";")) { throw new InvalidOperationException(); } // This is using string concatenation because set search_path doesn't allow type casting. ::text NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH=" + settings.SearchPath, this); commandSearchPath.ExecuteBlind(); } /* * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it. * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here. * This only works on postgresql servers where the ssl renegotiation settings is supported of course. * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information. */ try { NpgsqlCommand commandSslrenegotiation = new NpgsqlCommand("SET ssl_renegotiation_limit=0", this); commandSslrenegotiation.ExecuteBlind(); } catch {} // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
private Boolean CheckFunctionNeedsColumnDefinitionList() { // If and only if a function returns "record" and has no OUT ("o" in proargmodes), INOUT ("b"), or TABLE // ("t") return arguments to characterize the result columns, we must provide a column definition list. // See http://pgfoundry.org/forum/forum.php?thread_id=1075&forum_id=519 // We would use our Output and InputOutput parameters to construct that column definition list. If we have // no such parameters, skip the check: we could only construct "AS ()", which yields a syntax error. // Updated after 0.99.3 to support the optional existence of a name qualifying schema and allow for case insensitivity // when the schema or procedure name do not contain a quote. // The hard-coded schema name 'public' was replaced with code that uses schema as a qualifier, only if it is provided. String returnRecordQuery; StringBuilder parameterTypes = new StringBuilder(""); // Process parameters Boolean seenDef = false; foreach (NpgsqlParameter p in Parameters) { if ((p.Direction == ParameterDirection.Input) || (p.Direction == ParameterDirection.InputOutput)) { parameterTypes.Append(Connection.Connector.OidToNameMapping[p.TypeInfo.Name].OID.ToString() + " "); } if ((p.Direction == ParameterDirection.Output) || (p.Direction == ParameterDirection.InputOutput)) { seenDef = true; } } if (!seenDef) { return(false); } // Process schema name. String schemaName = String.Empty; String procedureName = String.Empty; String[] fullName = CommandText.Split('.'); String predicate = "prorettype = ( select oid from pg_type where typname = 'record' ) " + "and proargtypes=:proargtypes and proname=:proname " // proargmodes && array['o','b','t']::"char"[] performs just as well, but it requires PostgreSQL 8.2. + "and ('o' = any (proargmodes) OR 'b' = any (proargmodes) OR 't' = any (proargmodes)) is not true"; if (fullName.Length == 2) { returnRecordQuery = "select count(*) > 0 from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where " + predicate + " and n.nspname=:nspname"; schemaName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower(); procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower(); } else { // Instead of defaulting don't use the nspname, as an alternative, query pg_proc and pg_namespace to try and determine the nspname. //schemaName = "public"; // This was removed after build 0.99.3 because the assumption that a function is in public is often incorrect. returnRecordQuery = "select count(*) > 0 from pg_proc p where " + predicate; procedureName = (CommandText.IndexOf("\"") != -1) ? CommandText : CommandText.ToLower(); } bool ret; using (NpgsqlCommand c = new NpgsqlCommand(returnRecordQuery, Connection)) { c.Parameters.Add(new NpgsqlParameter("proargtypes", NpgsqlDbType.Oidvector)); c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Name)); c.Parameters[0].Value = parameterTypes.ToString(); c.Parameters[1].Value = procedureName; if (schemaName != null && schemaName.Length > 0) { c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Name)); c.Parameters[2].Value = schemaName; } ret = (Boolean)c.ExecuteScalar(); } return(ret); }
/*/// <value>Counts the numbers of Connections that share * /// this Connector. Used in Release() to decide wether this * /// connector is to be moved to the PooledConnectors list.</value> * // internal int mShareCount;*/ /// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown) ? ProtocolVersion.Version3 : settings.Protocol; // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Keep track of time remaining; Even though there may be multiple timeout-able calls, // this allows us to still respect the caller's timeout expectation. int connectTimeRemaining = this.ConnectionTimeout * 1000; DateTime attemptStart = DateTime.Now; // Get a raw connection, possibly SSL... CurrentState.Open(this, connectTimeRemaining); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this); } catch (NpgsqlException ne) { connectTimeRemaining -= Convert.ToInt32((DateTime.Now - attemptStart).TotalMilliseconds); // Check for protocol not supported. If we have been told what protocol to use, // we will not try this step. if (settings.Protocol != ProtocolVersion.Unknown) { throw; } // If we attempted protocol version 3, it may be possible to drop back to version 2. if (BackendProtocolVersion != ProtocolVersion.Version3) { throw; } NpgsqlError Error0 = (NpgsqlError)ne.Errors[0]; // If NpgsqlError..ctor() encounters a version 2 error, // it will set its own protocol version to version 2. That way, we can tell // easily if the error was a FATAL: protocol error. if (Error0.BackendProtocolVersion != ProtocolVersion.Version2) { throw; } // Try using the 2.0 protocol. _mediator.ResetResponses(); BackendProtocolVersion = ProtocolVersion.Version2; CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this, connectTimeRemaining); // Establish protocol communication and handle authentication... CurrentState.Startup(this); } // Change the state of connection to open and ready. _connection_state = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { //NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this); //ServerVersion = new Version(PGUtil.ExtractServerVersion((string) command.ExecuteScalar())); using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this)) { ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } } // Adjust client encoding. NpgsqlParameterStatus clientEncodingParam = null; if ( !ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) || (!string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase)) ) { new NpgsqlCommand("SET CLIENT_ENCODING TO UTF8", this).ExecuteBlind(); } if (!string.IsNullOrEmpty(settings.SearchPath)) { /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String); * p.Value = settings.SearchPath; * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this); * commandSearchPath.Parameters.Add(p); * commandSearchPath.ExecuteNonQuery();*/ /*NpgsqlParameter p = new NpgsqlParameter("p", DbType.String); * p.Value = settings.SearchPath; * NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH TO :p,public", this); * commandSearchPath.Parameters.Add(p); * commandSearchPath.ExecuteNonQuery();*/ // TODO: Add proper message when finding a semicolon in search_path. // This semicolon could lead to a sql injection security hole as someone could write in connection string: // searchpath=public;delete from table; and it would be executed. if (settings.SearchPath.Contains(";")) { throw new InvalidOperationException(); } // This is using string concatenation because set search_path doesn't allow type casting. ::text NpgsqlCommand commandSearchPath = new NpgsqlCommand("SET SEARCH_PATH=" + settings.SearchPath, this); commandSearchPath.ExecuteBlind(); } if (!string.IsNullOrEmpty(settings.ApplicationName)) { if (!SupportsApplicationName) { //TODO //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported")); throw new InvalidOperationException("ApplicationName not supported."); } if (settings.ApplicationName.Contains(";")) { throw new InvalidOperationException(); } NpgsqlCommand commandApplicationName = new NpgsqlCommand("SET APPLICATION_NAME='" + settings.ApplicationName + "'", this); commandApplicationName.ExecuteBlind(); } /* * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it. * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here. * This only works on postgresql servers where the ssl renegotiation settings is supported of course. * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information. */ try { NpgsqlCommand commandSslrenegotiation = new NpgsqlCommand("SET ssl_renegotiation_limit=0", this); commandSslrenegotiation.ExecuteBlind(); } catch {} /* * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3. * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least. * Check bug report #1010992 for more information. */ try { NpgsqlCommand commandSingleDoublePrecision = new NpgsqlCommand("SET extra_float_digits=2;SET extra_float_digits=3;", this); commandSingleDoublePrecision.ExecuteBlind(); } catch {} /* * Set lc_monetary format to 'C' ir order to get a culture agnostic representation of money. * I noticed that on Windows, even when the lc_monetary is English_United States.UTF-8, negative * money is formatted as ($value) with parentheses to indicate negative value. * By going with a culture agnostic format, we get a consistent behavior. */ try { NpgsqlCommand commandMonetaryFormatC = new NpgsqlCommand("SET lc_monetary='C';", this); commandMonetaryFormatC.ExecuteBlind(); } catch { } // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
/// <value>Counts the numbers of Connections that share /// this Connector. Used in Release() to decide wether this /// connector is to be moved to the PooledConnectors list.</value> // internal int mShareCount; /// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ProtocolVersion PV; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. if (ConnectionString.Contains(ConnectionStringKeys.Protocol)) { PV = ConnectionString.ToProtocolVersion(ConnectionStringKeys.Protocol); } else { PV = ProtocolVersion.Unknown; } _backendProtocolVersion = (PV == ProtocolVersion.Unknown) ? ProtocolVersion.Version3 : PV; // Reset state to initialize new connector in pool. Encoding = Encoding.Default; CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); // Establish protocol communication and handle authentication... CurrentState.Startup(this); // Check for protocol not supported. If we have been told what protocol to use, // we will not try this step. if (_mediator.Errors.Count > 0 && PV == ProtocolVersion.Unknown) { // If we attempted protocol version 3, it may be possible to drop back to version 2. if (BackendProtocolVersion == ProtocolVersion.Version3) { NpgsqlError Error0 = (NpgsqlError)_mediator.Errors[0]; // If NpgsqlError.ReadFromStream_Ver_3() encounters a version 2 error, // it will set its own protocol version to version 2. That way, we can tell // easily if the error was a FATAL: protocol error. if (Error0.BackendProtocolVersion == ProtocolVersion.Version2) { // Try using the 2.0 protocol. _mediator.ResetResponses(); BackendProtocolVersion = ProtocolVersion.Version2; CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); // Establish protocol communication and handle authentication... CurrentState.Startup(this); } } } // Check for errors and do the Right Thing. // FIXME - CheckErrors needs to be moved to Connector CheckErrors(); _backend_keydata = _mediator.BackendKeyData; // Change the state of connection to open and ready. _connection_state = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; String ServerVersionString = String.Empty; // First try to determine backend server version using the newest method. if (((NpgsqlParameterStatus)_mediator.Parameters["__npgsql_server_version"]) != null) { ServerVersionString = ((NpgsqlParameterStatus)_mediator.Parameters["__npgsql_server_version"]).ParameterValue; } // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersionString.Length == 0) { NpgsqlCommand command = new NpgsqlCommand("select version();set DATESTYLE TO ISO;", this); ServerVersionString = PGUtil.ExtractServerVersion((String)command.ExecuteScalar()); } // Cook version string so we can use it for enabling/disabling things based on // backend version. ServerVersion = PGUtil.ParseServerVersion(ServerVersionString); // Adjust client encoding. //NpgsqlCommand commandEncoding1 = new NpgsqlCommand("show client_encoding", _connector); //String clientEncoding1 = (String)commandEncoding1.ExecuteScalar(); if (ConnectionString.ToString(ConnectionStringKeys.Encoding, ConnectionStringDefaults.Encoding).ToUpper() == "UNICODE") { Encoding = Encoding.UTF8; NpgsqlCommand commandEncoding = new NpgsqlCommand("SET CLIENT_ENCODING TO UNICODE", this); commandEncoding.ExecuteNonQuery(); } // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
/// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. _backendProtocolVersion = (settings.Protocol == ProtocolVersion.Unknown) ? ProtocolVersion.Version3 : settings.Protocol; // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this); } catch (NpgsqlException ne) { // Check for protocol not supported. If we have been told what protocol to use, // we will not try this step. if (settings.Protocol != ProtocolVersion.Unknown) { throw; } // If we attempted protocol version 3, it may be possible to drop back to version 2. if (BackendProtocolVersion != ProtocolVersion.Version3) { throw; } NpgsqlError Error0 = (NpgsqlError)ne.Errors[0]; // If NpgsqlError..ctor() encounters a version 2 error, // it will set its own protocol version to version 2. That way, we can tell // easily if the error was a FATAL: protocol error. if (Error0.BackendProtocolVersion != ProtocolVersion.Version2) { throw; } // Try using the 2.0 protocol. _mediator.ResetResponses(); BackendProtocolVersion = ProtocolVersion.Version2; CurrentState = NpgsqlClosedState.Instance; // Get a raw connection, possibly SSL... CurrentState.Open(this); // Establish protocol communication and handle authentication... CurrentState.Startup(this); } // Change the state of connection to open and ready. State = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this)) { ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } } StringBuilder sbInit = new StringBuilder(); // Adjust client encoding. NpgsqlParameterStatus clientEncodingParam = null; if (!ServerParameters.TryGetValue("client_encoding", out clientEncodingParam) || !string.Equals(clientEncodingParam.ParameterValue, "UTF8", StringComparison.OrdinalIgnoreCase) && !string.Equals(clientEncodingParam.ParameterValue, "UNICODE", StringComparison.OrdinalIgnoreCase)) { sbInit.AppendLine("SET CLIENT_ENCODING TO UTF8;"); } if (!string.IsNullOrEmpty(settings.SearchPath)) { // TODO: Add proper message when finding a semicolon in search_path. // This semicolon could lead to a sql injection security hole as someone could write in connection string: // searchpath=public;delete from table; and it would be executed. if (settings.SearchPath.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET SEARCH_PATH=" + settings.SearchPath + ";"); } if (!string.IsNullOrEmpty(settings.ApplicationName)) { if (!SupportsApplicationName) { //TODO //throw new InvalidOperationException(resman.GetString("Exception_ApplicationNameNotSupported")); throw new InvalidOperationException("ApplicationName not supported."); } if (settings.ApplicationName.Contains(";")) { throw new InvalidOperationException(); } sbInit.AppendLine("SET APPLICATION_NAME='" + settings.ApplicationName.Replace('\'', '-') + "';"); } /* * Try to set SSL negotiation to 0. As of 2010-03-29, recent problems in SSL library implementations made * postgresql to add a parameter to set a value when to do this renegotiation or 0 to disable it. * Currently, Npgsql has a problem with renegotiation so, we are trying to disable it here. * This only works on postgresql servers where the ssl renegotiation settings is supported of course. * See http://lists.pgfoundry.org/pipermail/npgsql-devel/2010-February/001065.html for more information. */ sbInit.AppendLine("SET ssl_renegotiation_limit=0;"); /* * Set precision digits to maximum value possible. For postgresql before 9 it was 2, after that, it is 3. * This way, we set first to 2 and then to 3. If there is an error because of 3, it will have been set to 2 at least. * Check bug report #1010992 for more information. */ sbInit.AppendLine("SET extra_float_digits=3;"); try { new NpgsqlCommand(sbInit.ToString(), this).ExecuteBlind(); } catch { foreach (var line in sbInit.ToString().Split(Environment.NewLine.ToCharArray())) { try { if (line.Length > 0) { new NpgsqlCommand(line, this).ExecuteBlind(); } } catch { } } } // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. _oidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); ProcessServerVersion(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }
/*/// <value>Counts the numbers of Connections that share * /// this Connector. Used in Release() to decide wether this * /// connector is to be moved to the PooledConnectors list.</value> * // internal int mShareCount;*/ /// <summary> /// Opens the physical connection to the server. /// </summary> /// <remarks>Usually called by the RequestConnector /// Method of the connection pool manager.</remarks> internal void Open() { ServerVersion = null; // If Connection.ConnectionString specifies a protocol version, we will // not try to fall back to version 2 on failure. // Reset state to initialize new connector in pool. CurrentState = NpgsqlClosedState.Instance; // Keep track of time remaining; Even though there may be multiple timeout-able calls, // this allows us to still respect the caller's timeout expectation. int connectTimeRemaining = this.ConnectionTimeout * 1000; DateTime attemptStart = DateTime.Now; // Get a raw connection, possibly SSL... CurrentState.Open(this, connectTimeRemaining); try { // Establish protocol communication and handle authentication... CurrentState.Startup(this, settings); } catch (NpgsqlException) { if (_stream != null) { try { _stream.Dispose(); } catch { } } throw; } // Change the state of connection to open and ready. _connection_state = ConnectionState.Open; CurrentState = NpgsqlReadyState.Instance; // After attachment, the stream will close the connector (this) when the stream gets disposed. _baseStream.AttachConnector(this); // Fall back to the old way, SELECT VERSION(). // This should not happen for protocol version 3+. if (ServerVersion == null) { //NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this); //ServerVersion = new Version(PGUtil.ExtractServerVersion((string) command.ExecuteScalar())); using (NpgsqlCommand command = new NpgsqlCommand("set DATESTYLE TO ISO;select version();", this)) { ServerVersion = new Version(PGUtil.ExtractServerVersion((string)command.ExecuteScalar())); } } ProcessServerVersion(); StringWriter sbInitQueries = new StringWriter(); // Some connection parameters for protocol 3 had been sent in the startup packet. // The rest will be setted here. if (SupportsExtraFloatDigits3) { sbInitQueries.WriteLine("SET extra_float_digits=3;"); } if (SupportsSslRenegotiationLimit) { sbInitQueries.WriteLine("SET ssl_renegotiation_limit=0;"); } initQueries = sbInitQueries.ToString(); NpgsqlCommand.ExecuteBlind(this, initQueries, 60); // Make a shallow copy of the type mapping that the connector will own. // It is possible that the connector may add types to its private // mapping that will not be valid to another connector, even // if connected to the same backend version. NativeToBackendTypeConverterOptions.OidToNameMapping = NpgsqlTypesHelper.CreateAndLoadInitialTypesMapping(this).Clone(); // The connector is now fully initialized. Beyond this point, it is // safe to release it back to the pool rather than closing it. IsInitialized = true; }