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 = ""; } }
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; }
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); } } }
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 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; } }
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(); } }
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 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 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; } }
//создание формы 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); } }
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 protected] AND [email protected]"); 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 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 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(); } }
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 { } }
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!"; } }
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 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 static object GetScalar(NpgsqlCommand command, NpgsqlConnection connection) { using (connection) { return command.ExecuteScalar(); } }
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 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(); } }
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!"; } }
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 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(); } }
public void ChangeDatabase() { Conn.ChangeDatabase("template1"); var command = new NpgsqlCommand("select current_database()", Conn); var result = (String)command.ExecuteScalar(); Assert.AreEqual("template1", result); }
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 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 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); }