public static void AccessMaster(string Action, string TableName, Hashtable hash) { using(NpgsqlConnection con = new NpgsqlConnection(CONNECTION_STRING)) { string query = string.Format(@"SELECT * FROM ""{0}""", TableName); using(NpgsqlCommand cmd = new NpgsqlCommand(@query, con)) { NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(); adapter.SelectCommand = cmd; NpgsqlCommand actionCommand = null; if(Action == "Entry"){ actionCommand = (new NpgsqlCommandBuilder(adapter)).GetInsertCommand(); } else if(Action == "Modify"){ actionCommand = (new NpgsqlCommandBuilder(adapter)).GetUpdateCommand(); } else if(Action == "Remove"){ actionCommand = (new NpgsqlCommandBuilder(adapter)).GetDeleteCommand(); } try { foreach(NpgsqlParameter param in actionCommand.Parameters){ if(hash[param.SourceColumn] != null) param.Value = hash[param.SourceColumn]; } actionCommand.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(string.Format("{0}", ex.Message)); MessageBox.Show(string.Format("{0}", ex.StackTrace.ToString())); } } } }
public List<Product> getList() { using (NpgsqlConnection connection = getConnection()) { using (NpgsqlCommand cmd = new NpgsqlCommand(SELECT_CMD, connection)) { connection.Open(); List<Product> result = new List<Product>(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Product temp = new Product(); temp.id = reader.GetGuid(0); temp.description = reader.GetString(1); temp.productGroupID = reader.GetGuid(2); temp.Unit = Units.FromName(reader.GetString(3)); if (!reader.IsDBNull(4)) { temp.weight = (Decimal)reader.GetFloat(4); } temp.money = reader.GetDecimal(5); temp.quantity = reader.GetInt32(6); result.Add(temp); } } return result; } } }
public static List<GrilleRabais> getListGrilleRistourne(String query) { NpgsqlConnection con = Connexion.Connection(); try { List<GrilleRabais> l = new List<GrilleRabais>(); NpgsqlCommand Lcmd = new NpgsqlCommand(query, con); NpgsqlDataReader lect = Lcmd.ExecuteReader(); if (lect.HasRows) { while (lect.Read()) { GrilleRabais a = new GrilleRabais(); a.Id = Convert.ToInt64(lect["id"].ToString()); a.Nature = lect["nature_montant"].ToString(); a.Minimal = (Double)((lect["montant_minimal"] != null) ? ((!lect["montant_minimal"].ToString().Trim().Equals("")) ? lect["montant_minimal"] : 0) : 0); a.Maximal = (Double)((lect["montant_maximal"] != null) ? ((!lect["montant_maximal"].ToString().Trim().Equals("")) ? lect["montant_maximal"] : 0) : 0); a.Montant = (Double)((lect["montant_ristourne"] != null) ? ((!lect["montant_ristourne"].ToString().Trim().Equals("")) ? lect["montant_ristourne"] : 0) : 0); a.Update = true; l.Add(a); } lect.Close(); } return l; } catch (NpgsqlException e) { Messages.Exception(e); return null; } finally { Connexion.Deconnection(con); } }
public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior) { var builder = new StringBuilder("ABCDEééé", Conn.BufferSize); builder.Append('X', Conn.BufferSize); var expected = builder.ToString(); ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)"); var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn); cmd.Parameters.Add(new NpgsqlParameter("p", expected)); cmd.ExecuteNonQuery(); const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data"; cmd = new NpgsqlCommand(queryText, Conn); var reader = cmd.ExecuteReader(behavior); reader.Read(); var actual = reader[0]; Assert.That(actual, Is.EqualTo(expected)); if (IsSequential(behavior)) Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential"); else Assert.That(reader[0], Is.EqualTo(expected)); Assert.That(reader.GetString(1), Is.EqualTo("foo")); Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected)); Assert.That(reader.GetValue(3), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray())); }
public static DataTable GetMenuTable(int parentMenuId, short level, int userId, int officeId, string culture) { if (userId.Equals(0)) { return null; } if (officeId.Equals(0)) { return null; } string sql = "SELECT * FROM policy.get_menu(@UserId, @OfficeId, @Culture) WHERE parent_menu_id is null ORDER BY menu_id;"; if (parentMenuId > 0) { sql = "SELECT * FROM policy.get_menu(@UserId, @OfficeId, @Culture) WHERE parent_menu_id=@ParentMenuId AND level=@Level ORDER BY menu_id;"; } using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@OfficeId", officeId); command.Parameters.AddWithValue("@Culture", culture); if (parentMenuId > 0) { command.Parameters.AddWithValue("@ParentMenuId", parentMenuId); command.Parameters.AddWithValue("@Level", level); } return DbOperations.GetDataTable(command); } }
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 = ""; } }
/// <summary> /// Get all CBS neighbourhoods, specifically its name, boundary (in WKT), and center. /// </summary> public void Enhance(IEnumerable<LocationDescription> locationDescriptions) { using (var conn = new NpgsqlConnection(connectionString)) { conn.Open(); var streets = new List<string>(); foreach (var locationDescription in locationDescriptions) { var query = string.Format(Query, locationDescription.RdBoundary); using (var command = new NpgsqlCommand(query, conn)) { try { using (var dr = command.ExecuteReader()) { while (dr.Read()) { streets.Add(dr["straat"].ToString()); } } } catch (SystemException e) { Console.WriteLine(e.Message); } } locationDescription.Features.Add("straten", string.Join(";", streets)); } } }
public static TestPerson BuildPersonFromDataBaseData(string idPerson, string MIS) { using (NpgsqlConnection connection = Global.GetSqlConnection()) { string findPatient = "SELECT * FROM public.person WHERE id_person = '" + idPerson + "'"; NpgsqlCommand person = new NpgsqlCommand(findPatient, connection); using (NpgsqlDataReader personFromDataBase = person.ExecuteReader()) { Person p = new Person(); while (personFromDataBase.Read()) { //что делать с DateSpecified и Мисами? if (personFromDataBase["birthday"]!= DBNull.Value) p.BirthDate = Convert.ToDateTime(personFromDataBase["birthday"]); if (MIS == "") p.IdPatientMis = null; if ((personFromDataBase["family_name"] != DBNull.Value) || (personFromDataBase["given_name"] != DBNull.Value) || (personFromDataBase["middle_name"] != DBNull.Value)) { p.HumanName = new HumanName(); if (personFromDataBase["family_name"] != DBNull.Value) p.HumanName.FamilyName = Convert.ToString(personFromDataBase["family_name"]); if (personFromDataBase["given_name"] != DBNull.Value) p.HumanName.GivenName = Convert.ToString(personFromDataBase["given_name"]); if (personFromDataBase["middle_name"] != DBNull.Value) p.HumanName.MiddleName = Convert.ToString(personFromDataBase["middle_name"]); } TestPerson pers = new TestPerson(p); if (personFromDataBase["id_sex"] != DBNull.Value) pers.sex = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_sex"])); return pers; } } } return null; }
public Registration Get(Guid id) { var registrationEvents = new List <IRegistrationEvent>(); using (var sqlConnection = new NpgsqlConnection(ConnectionString)) { sqlConnection.Open(); var command = "SELECT * FROM schooled.Registration WHERE id = @id ORDER BY timestamp"; using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection)) { sqlCommand.Parameters.AddWithValue("id", NpgsqlDbType.Uuid, id); using (var reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { var eventType = Convert.ToString(reader["event_type"]); var theEvent = (IRegistrationEvent)JsonConvert.DeserializeObject( Convert.ToString(reader["event"]), RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key); registrationEvents.Add(theEvent); } } } } return(registrationEvents.Any() ? new Registration(id, registrationEvents) : null); }
public static long PostTransaction(string catalog, long transactionMasterId, DateTime valueDate, int officeId, int userId, long loginId, int storeId, string partyCode, int priceTypeId, string referenceNumber, string statementReference, Collection<StockDetail> details, Collection<Attachment> attachments) { string detail = StockMasterDetailHelper.CreateStockMasterDetailParameter(details); string attachment = AttachmentHelper.CreateAttachmentModelParameter(attachments); string sql = string.Format(CultureInfo.InvariantCulture, "SELECT * FROM transactions.post_purchase_return(@TransactionMasterId::bigint, @OfficeId::integer, @UserId::integer, @LoginId::bigint, @ValueDate::date, @StoreId::integer, @PartyCode::national character varying(12), @PriceTypeId::integer, @ReferenceNumber::national character varying(24), @StatementReference::text, ARRAY[{0}], ARRAY[{1}]);", detail, attachment); using (NpgsqlCommand command = new NpgsqlCommand(sql)) { command.Parameters.AddWithValue("@TransactionMasterId", transactionMasterId); command.Parameters.AddWithValue("@OfficeId", officeId); command.Parameters.AddWithValue("@UserId", userId); command.Parameters.AddWithValue("@LoginId", loginId); command.Parameters.AddWithValue("@ValueDate", valueDate); command.Parameters.AddWithValue("@StoreId", storeId); command.Parameters.AddWithValue("@PartyCode", partyCode); command.Parameters.AddWithValue("@PriceTypeId", priceTypeId); command.Parameters.AddWithValue("@ReferenceNumber", referenceNumber); command.Parameters.AddWithValue("@StatementReference", statementReference); command.Parameters.AddRange(StockMasterDetailHelper.AddStockMasterDetailParameter(details).ToArray()); command.Parameters.AddRange(AttachmentHelper.AddAttachmentParameter(attachments).ToArray()); long tranId = Conversion.TryCastLong(DbOperation.GetScalarValue(catalog, command)); return tranId; } }
public static List<PlanCommission> getListPlanCommission(String query) { NpgsqlConnection con = Connexion.Connection(); try { List<PlanCommission> l = new List<PlanCommission>(); NpgsqlCommand Lcmd = new NpgsqlCommand(query, con); NpgsqlDataReader lect = Lcmd.ExecuteReader(); if (lect.HasRows) { while (lect.Read()) { PlanCommission a = new PlanCommission(); a.Id = Convert.ToInt64(lect["id"].ToString()); a.Nature = lect["nature_commission"].ToString(); a.Reference = lect["reference"].ToString(); a.Commissions = BLL.CommissionBll.Liste("select * from yvs_com_commission where plan = " + a.Id); a.Update = true; l.Add(a); } lect.Close(); } return l; } catch (NpgsqlException e) { Messages.Exception(e); return null; } finally { Connexion.Deconnection(con); } }
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 List <Models.TestLevels.TestStructures> GetTopStructures() { List <Models.TestLevels.TestStructures> list = new List <Models.TestLevels.TestStructures>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = '00000000-0000-0000-0000-000000000000' order by name;", con); da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.TestLevels.TestStructures item = new Models.TestLevels.TestStructures(); item.id = new Guid(Convert.ToString(DR["row_idnumber"])); item.Name = Convert.ToString(DR["name"]); item.ShortName = Convert.ToString(DR["srtname"]); item.Parent_IDNumber = new Guid(Convert.ToString(DR["parent_idnumber"])); list.Add(item); } return(list); }
public List <Models.Application> GetApplications() { con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from qadata.ref_structure where parent_idnumber = @NoParent order by name;", con); cmd.Parameters.Add("@NoParent", NpgsqlTypes.NpgsqlDbType.Uuid).Value = new Guid(); da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); List <Models.Application> LI = new List <Models.Application>(); foreach (DataRow I in ds.Tables[0].Rows) { Models.Application application = new Models.Application(); application.id = new Guid(Convert.ToString(I["row_idnumber"])); application.Name = Convert.ToString(I["name"]); application.ShortName = Convert.ToString(I["srtname"]); LI.Add(application); } return(LI); }
//Build changelog responsefile public override void MakeChangeLog(int startChangeId, int count, string dbConnectInfo, string wfsUrl, string changeLogFileName, int datasetId) { Logger.Info("MakeChangeLog START"); try { if (OptimizedChangeLog.Count == 0) { //Connect to postgres database Npgsql.NpgsqlConnection conn = null; conn = new NpgsqlConnection(dbConnectInfo); conn.Open(); //Get max changelogid Int64 endChangeId = GetMaxChangeLogId(conn, datasetId); //Prepare query against the changelog table in postgres Npgsql.NpgsqlCommand command = null; PrepareChangeLogQuery(conn, ref command, startChangeId, endChangeId, datasetId); //Execute query against the changelog table and remove unnecessary transactions. FillOptimizedChangeLog(ref command, ref OptimizedChangeLog, startChangeId); conn.Close(); } //Get features from WFS and add transactions to changelogfile BuildChangeLogFile(count, wfsUrl, startChangeId, changeLogFileName, datasetId); } catch (System.Exception exp) { Logger.Error(exp, "MakeChangeLog function failed:"); throw new System.Exception("MakeChangeLog function failed", exp); } Logger.Info("MakeChangeLog END"); }
public List <Models.Automation.Test_Run_Notes> GetRunNote(Guid ID) { List <Models.Automation.Test_Run_Notes> list = new List <Models.Automation.Test_Run_Notes>(); con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"select * from automation.runnotes where row_idnum = @ID;", con); cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Uuid).Value = ID; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataRow DR in ds.Tables[0].Rows) { Models.Automation.Test_Run_Notes item = new Models.Automation.Test_Run_Notes { idnum = new Guid(Convert.ToString(DR["row_idnum"])), run_idnum = new Guid(Convert.ToString(DR["run_idnum"])), testset_idnum = new Guid(Convert.ToString(DR["testset_idnum"])), test_idnum = new Guid(Convert.ToString(DR["test_idnum"])), id = Convert.ToInt32(DR["id"]), notes = Convert.ToString(DR["notes"]), method_name = Convert.ToString(DR["method_name"]), Status = Convert.ToInt32(DR["status"]) }; list.Add(item); } return(list); }
public Guid Maintain_RunNotes(Models.Automation.Test_Run_Notes item, string username) { con = new NpgsqlConnection(conFIG.ConnFor_PG_Admin); cmd = new NpgsqlCommand(@"SELECT automation.maintain_runnote(@IdNum, @run_IdNum, @testset_idnum,@test_IdNum, @ID,@method_name, @notes, @username, @status);", con); cmd.Parameters.Add("@IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.idnum; cmd.Parameters.Add("@run_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.run_idnum; cmd.Parameters.Add("@testset_idnum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.testset_idnum; cmd.Parameters.Add("@test_IdNum", NpgsqlTypes.NpgsqlDbType.Uuid).Value = item.test_idnum; cmd.Parameters.Add("@ID", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.id; cmd.Parameters.Add("@method_name", NpgsqlTypes.NpgsqlDbType.Text).Value = item.method_name ?? ""; cmd.Parameters.Add("@notes", NpgsqlTypes.NpgsqlDbType.Text).Value = item.notes ?? ""; cmd.Parameters.Add("@username", NpgsqlTypes.NpgsqlDbType.Text).Value = username; cmd.Parameters.Add("@status", NpgsqlTypes.NpgsqlDbType.Integer).Value = item.Status; da = new NpgsqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); Guid IDNUM = new Guid(Convert.ToString(ds.Tables[0].Rows[0]["maintain_runnote"])); return(IDNUM); }
//Adição na tabela associativa, recebendo o ID do filme e do diretor respectivamente. public void Add(int id_filme, int id_diretor) { try { bd.OpenConnection(); String query = "INSERT INTO tab_filme_diretor VALUES (:cod_filme, :cod_diretor)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = id_filme; sql.Parameters[1].Value = id_diretor; int linhasAfetadas = sql.ExecuteNonQuery(); } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
public void Alterar(Model_Vo_Sala pSala) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update sala set nome = @nome, capacidade = @capacidade, tipo = @tipo, idproduto = @idproduto where id = @id;"; cmd.Parameters.AddWithValue("@id", pSala.Id); cmd.Parameters.AddWithValue("@nome", pSala.Nome); cmd.Parameters.AddWithValue("@capacidade", pSala.Capacidade); cmd.Parameters.AddWithValue("@tipo", pSala.Tipo.ToString().ToString()); cmd.Parameters.AddWithValue("@idproduto", Convert.ToString(pSala.IdProduto)); cn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public void FecharContasAReceber(Model_Vo_ContasAReceber pContasAReceber) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update contasareceber set recebido = @recebido where idreservaorigem = @idreservaorigem;"; cmd.Parameters.AddWithValue("@idreservaorigem", pContasAReceber.IdReservaOrigem); cmd.Parameters.AddWithValue("@recebido", pContasAReceber.Recebido); cn.Open(); cmd.ExecuteNonQuery(); } 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 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_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 List<OrganizationType> QueryAllOnOrganizationType() { try { this.OpenConn(); string SQL = "SELECT * FROM Organization_Type"; NpgsqlCommand command = new NpgsqlCommand(SQL, conn); NpgsqlDataReader dr = command.ExecuteReader(); List<OrganizationType> orgTypes = new List<OrganizationType>(); while (dr.Read()) { OrganizationType type = new OrganizationType(); type.Name = dr["name"].ToString(); type.Description = dr["description"].ToString(); orgTypes.Add(type); } this.CloseConn(); return orgTypes; } catch (Exception ne) { Console.WriteLine("error on query table connecting to server, Error details {0}", ne.ToString()); return null; } }
public void Alterar(Model_Vo_Agenda pAgenda) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update agenda set datahorareserva = @datahorareserva, idcliente = @idcliente, idsala = @idsala where id = @id;"; cmd.Parameters.AddWithValue("@id", pAgenda.Id); 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(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public bool Update() { string sQuery = "UPDATE tbm_carrepair SET " + "carid=@cid,dlt=@dlt,opedit=@opedit,pcedit=@pcedit,luedit=now()" + "WHERE repairid = @rid"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); if (m_repairid != null) { cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = repairid; } else { cmd.Parameters.Add("@rid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (m_carid != null) { cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = carid; } else { cmd.Parameters.Add("@cid", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (opedit != null) { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = opedit; } else { cmd.Parameters.Add("@opedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (pcedit != null) { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = pcedit; } else { cmd.Parameters.Add("@pcedit", NpgsqlTypes.NpgsqlDbType.Varchar).Value = DBNull.Value; } if (luedit != null && luedit != DateTime.MinValue) { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = luedit; } else { cmd.Parameters.Add("@luedit", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = DBNull.Value; } cmd.Parameters.Add("@dlt", NpgsqlTypes.NpgsqlDbType.Boolean).Value = dlt; cmd.CommandText = sQuery; try { cmd.ExecuteNonQuery(); return(true); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(false); } }
// GET api/values public IEnumerable<string> Get() { // return new string[] { "value1", "value2" }; var result = new List<string>(); using (var conn = new NpgsqlConnection()) { conn.ConnectionString = "PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;DATABASE=pdtgis;HOST=localhost;USER ID=postgres;PASSWORD=morty"; conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; cmd.CommandText = "SELECT name,amenity, ST_AsGeoJson(way) FROM planet_osm_point WHERE amenity = \'pub\' LIMIT 10;"; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { result.Add(reader.GetString(2)); } } } } return result; }
public string NewID() { string i = ""; string sQuery = "select '" + clsGlobal.pstrservercode + "'||nextval('tbm_carrepair_nextid') as id;"; Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi); cmd.CommandText = sQuery; try { Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader(); if (rdr.Read()) { if (!rdr.IsDBNull(rdr.GetOrdinal("id"))) { i = rdr.GetValue(0).ToString(); } else { i = ""; }; } rdr.Close(); } catch (Npgsql.NpgsqlException Ex) { System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!"); return(""); } return(i); }
public TableInfo GetTableInfo(string ConnectionString, string schema, string table_name) { var ret = new TableInfo(); var sql = @"SELECT column_name, column_default,is_nullable,data_type,character_maximum_length FROM information_schema.columns WHERE table_schema = :schema AND table_name = :table"; var cnn = new Npgsql.NpgsqlConnection(ConnectionString); var cmd = new Npgsql.NpgsqlCommand("select * from " + Globals.Compiler.GetQName(schema, table_name), cnn); //cmd.Parameters.Add(new NpgsqlParameter() //{ // ParameterName="schema", // Value=schema //}); //cmd.Parameters.Add(new NpgsqlParameter() //{ // ParameterName = "table", // Value = table_name //}); DataTable tbl = new DataTable(); //var adp = new Npgsql.NpgsqlDataAdapter(cmd); try { cnn.Open(); tbl = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly).GetSchemaTable(); //adp.Fill(tbl); } catch (Exception ex) { cnn.Close(); throw (ex); } finally { cnn.Close(); } ret.Columns = tbl.Rows.Cast <DataRow>().Select(p => new ColumInfo() { Name = p["ColumnName"].ToString(), IsUnique = (p["IsUnique"] == DBNull.Value) ? false : (bool)p["IsUnique"], IsAutoIncrement = (p["IsAutoIncrement"] == DBNull.Value) ? false : (bool)p["IsAutoIncrement"], IsKey = (p["IsKey"] == DBNull.Value) ? false : (bool)p["IsKey"], AllowDBNull = (p["AllowDBNull"] == DBNull.Value) ? false : (bool)p["AllowDBNull"], IsReadOnly = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"], IsExpression = (p["IsReadOnly"] == DBNull.Value) ? false : (bool)p["IsReadOnly"], IsIdentity = (p["IsIdentity"] == DBNull.Value) ? false : (bool)p["IsIdentity"], DataType = p["DataType"], ColumnSize = (int)p["ColumnSize"] //DefaultValue = p["column_default"].ToString(), //IsAuto = p["column_default"].ToString().Split("(")[0] == "nextval", //AutoConstraint = ((p["column_default"].ToString().Split("(")[0] == "nextval") ? p["column_default"].ToString().Split("(")[1].Split("::")[0] : "") }).ToList(); return(ret); }
public static bool SendPasswordRecoveryMail(EmailInfo eInfo) { bool success = false; DataTable dt = new DataTable(); IDatabaseFactory dbFactory = new DatabaseFactory(); IDatabase dbObject = dbFactory.CreateDatabaseInstance(ACTIVE_DATABASE); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SQLClass.GET_FORGETUSERPASSWORD); command.Parameters.Add("email", eInfo.Email.ToLower()); dt = dbObject.GetDataTable(command); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { eInfo.Email = dr["email"].ToString(); eInfo.Password = dr["webshop_pwd"].ToString(); eInfo.CompanyName = dr["companytype"].ToString(); } string adminMail = Hey.Common.Utils.Functions.GetValueFromWebConfig("hey-admin-mail"); try { success = SendPasswordRecoveryMailToUser(eInfo, adminMail); } catch (Exception ex) { throw new Exception("The email is unknown to the server."); } } else { success = false; } return(success); }
public void ParametersGetName() { var command = new NpgsqlCommand(); // Add parameters. command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean)); command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32)); command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime)); command.Parameters.Add(new NpgsqlParameter("Parameter4", DbType.DateTime)); var idbPrmtr = command.Parameters["Parameter1"]; Assert.IsNotNull(idbPrmtr); command.Parameters[0].Value = 1; // Get by indexers. Assert.AreEqual(":Parameter1", command.Parameters[":Parameter1"].ParameterName); Assert.AreEqual(":Parameter2", command.Parameters[":Parameter2"].ParameterName); Assert.AreEqual(":Parameter3", command.Parameters[":Parameter3"].ParameterName); //Assert.AreEqual(":Parameter4", command.Parameters["Parameter4"].ParameterName); //Should this work? Assert.AreEqual(":Parameter1", command.Parameters[0].ParameterName); Assert.AreEqual(":Parameter2", command.Parameters[1].ParameterName); Assert.AreEqual(":Parameter3", command.Parameters[2].ParameterName); Assert.AreEqual("Parameter4", command.Parameters[3].ParameterName); }
/// <param name="catalog">The name of the database on which queries are being executed to.</param> public static IEnumerable<DisplayField> GetDisplayFields(string catalog) { List<DisplayField> displayFields = new List<DisplayField>(); const string sql = "SELECT employee_type_id AS key, employee_type_code || ' (' || employee_type_name || ')' as value FROM hrm.employee_types;"; using (NpgsqlCommand command = new NpgsqlCommand(sql)) { using (DataTable table = DbOperation.GetDataTable(catalog, command)) { if (table?.Rows == null || table.Rows.Count == 0) { return displayFields; } foreach (DataRow row in table.Rows) { if (row != null) { DisplayField displayField = new DisplayField { Key = row["key"].ToString(), Value = row["value"].ToString() }; displayFields.Add(displayField); } } } } return displayFields; }
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!"; } }
public static bool ExecuteNonQuery(string catalog, NpgsqlCommand command) { try { if (command != null) { if (ValidateCommand(command)) { using (NpgsqlConnection connection = new NpgsqlConnection(DbConnection.GetConnectionString(catalog))) { command.Connection = connection; connection.Open(); command.ExecuteNonQuery(); return true; } } } return false; } catch (NpgsqlException ex) { if (ex.Code.StartsWith("P")) { string errorMessage = GetDBErrorResource(ex); throw new MixERPException(errorMessage, ex); } throw; } }
public static List <MaterialGroupFilter> GetNumberOfFilterCombos(string code) { List <MaterialGroupFilter> ListOfFilters = new List <MaterialGroupFilter>(); MaterialGroupFilter FilterObject; DataTable dt = new DataTable(); IDatabaseFactory dbFactory = new DatabaseFactory(); IDatabase dbObject = dbFactory.CreateDatabaseInstance(ACTIVE_DATABASE); Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(SQLClass.GET_NUMBEROFFIILTERCOMBO); command.Parameters.Add("group_code", code.ToUpper()); dt = dbObject.GetDataTable(command); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { FilterObject = new MaterialGroupFilter(); FilterObject.FilterId = int.Parse(dr["filter_id"].ToString()); FilterObject.FilterLabel = dr["description"].ToString(); FilterObject.FieldName = dr["field_name"].ToString(); FilterObject.TableName = dr["table_name"].ToString(); FilterObject.ListPosition = int.Parse(dr["list_position"].ToString()); ListOfFilters.Add(FilterObject); } } return(ListOfFilters); }
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 static TestReferralSurvey BuildAdditionalFromDataBaseData(string idReferral) { using (NpgsqlConnection connection = Global.GetSqlConnection()) { string findPatient = "SELECT id_survey_organ, survey_comment, id_survey_type FROM public.referral WHERE id_referral = '" + idReferral + "' ORDER BY id_referral DESC LIMIT 1"; NpgsqlCommand person = new NpgsqlCommand(findPatient, connection); using (NpgsqlDataReader personFromDataBase = person.ExecuteReader()) { Survey p = new Survey(); while (personFromDataBase.Read()) { //что делать с DateSpecified и Мисами? if (personFromDataBase["survey_comment"] != DBNull.Value) p.Comment = Convert.ToString(personFromDataBase["survey_comment"]); TestReferralSurvey pers = new TestReferralSurvey(p); pers.additional = TestAdditional.BuildAdditionalFromDataBaseData(idReferral); if (personFromDataBase["id_survey_organ"] != DBNull.Value) pers.surveyOrgan = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_organ"])); if (personFromDataBase["id_survey_type"] != DBNull.Value) pers.surveyType = TestCoding.BuildCodingFromDataBaseData(Convert.ToString(personFromDataBase["id_survey_type"])); return pers; } } } return null; }
//Insert no banco recebendo como parametro um Filme. public void Add(Filme f) { try { bd.OpenConnection(); String query = "INSERT INTO tab_filme VALUES (:cod_filme, :nome_filme, :data_filme, :cod_categoria)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_filme", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("nome_filme", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Parameters.Add(new NpgsqlParameter("data_filme", NpgsqlTypes.NpgsqlDbType.Date)); sql.Parameters.Add(new NpgsqlParameter("cod_categoria", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = f.Cod_filme; sql.Parameters[1].Value = f.Nome_filme; sql.Parameters[2].Value = f.Data; sql.Parameters[3].Value = f.Categoria.Cod_categoria; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Filme adicionado com sucesso!"); } } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
//När användaren kilckar på "OK" sker följande: private void OK_button_Click(object sender, EventArgs e) { //Deklarerar variabel klass var klass = ""; //Om "A" är iklickat av användaren får variabel klass värdet A. if (A_radioButton.Checked == true) { klass = "A"; } //Om "B" är iklickat av användaren får variabel klass värdet B. else if (B_radioButton.Checked == true) { klass = "B"; } //Om "C" är iklickat av användaren får variabel klass värdet C. else if (C_radioButton.Checked == true) { klass = "C"; } //Skapar strängen anmaldeltagare. //Strängen innehåller information om tävlingsdeltagare. Lägger in golfid, tävlingid och klass i databasen, i tabellen deltari. string anmaldeltagare = "insert into deltari (golfid, tavlingid, klass) values ('" + Golfid_textBox.Text + "'," + Tävlingid_textBox.Text + ",'" + klass + "')"; //Skapar ett nytt Npgsql-kommando, command1. NpgsqlCommand command1 = new NpgsqlCommand(anmaldeltagare, Huvudfönster.conn); //Utför kommando, command1. command1.ExecuteNonQuery(); //När allt ovan är utfört visas en meddelanderuta. MessageBox.Show("Spelare är nu anmäld till tävling!"); //Sedan stängs hela detta form, AnmälDeltagare. this.Close(); }
public IEnumerable <EventStoreItem> GetAll() { var entities = new List <EventStoreItem>(); using (var sqlConnection = new NpgsqlConnection(ConnectionString)) { sqlConnection.Open(); var command = "SELECT db_id, id, event_type, event, timestamp FROM schooled.Registration"; using (var sqlCommand = new Npgsql.NpgsqlCommand(command, sqlConnection)) { using (var reader = sqlCommand.ExecuteReader()) { while (reader.Read()) { var eventType = Convert.ToString(reader["event_type"]); var entity = new EventStoreItem { Id = Guid.Parse(Convert.ToString(reader["id"])), EventType = eventType, TimeStamp = DateTimeOffset.Parse(Convert.ToString(reader["timestamp"])), Event = (IRegistrationEvent)JsonConvert.DeserializeObject( Convert.ToString(reader["event"]), RegistrationEvents.EventTypeLookup.Single(x => x.Value == eventType).Key) }; entities.Add(entity); } } } } return(entities); }
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(); } }
private static void Run() { var connStr = ConfigurationManager.AppSettings["ConnectionString"]; using(NpgsqlConnection conn = new NpgsqlConnection(connStr)) using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr)) { conn.Open(); updateConn.Open(); // Define a query returning a single row result set using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn)) { using (NpgsqlDataReader dr = command.ExecuteReader()) { // Output rows while (dr.Read()) { CreateThumbnail((string)dr[1], (long)dr[2]); using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn)) { update.ExecuteNonQuery(); } } } } } }
public void Alterar(Model_Vo_Produto pProduto) { // conexao SqlConnection cn = new SqlConnection(); try { cn.ConnectionString = Academia.Model.Dao.Dados.Model_Dao_Dados.getStringDeConexao(); SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandType = CommandType.Text; cmd.CommandText = "update produto set descricao = @descricao, unidade = @unidade, estoque = @estoque, valordevenda = @valordevenda, observacao = @observacao where id = @id;"; 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); cmd.Parameters.AddWithValue("@id", pProduto.Id); cn.Open(); cmd.ExecuteNonQuery(); } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } }
public IEnumerable<Routine> GetRoutines() { var list = new List<Routine>(); using (var sqlConnection = new NpgsqlConnection(connectionString)) { using (var cmd = new NpgsqlCommand(@" SELECT routine_name FROM INFORMATION_SCHEMA.routines WHERE routine_schema <> 'pg_catalog' and routine_schema <>'information_schema' ", sqlConnection)) { sqlConnection.Open(); cmd.CommandType = CommandType.Text; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(new Routine(reader.GetString(0))); } } } } return list; }
public Boolean Excluir(int id) { //conexao 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 = "delete from produto where id = " + Convert.ToString(id); cn.Open(); int resultado = cmd.ExecuteNonQuery(); if (resultado != 1) { throw new Exception("Não foi possível excluir a produto " + Convert.ToString(id)); } } catch (SqlException ex) { throw new Exception("Servidor SQL Erro:" + ex.Number); } catch (Exception ex) { throw new Exception(ex.Message); } finally { cn.Close(); } return(true); }
private void PrepareChangeLogQuery(Npgsql.NpgsqlConnection conn, ref Npgsql.NpgsqlCommand command, int startChangeId, Int64 endChangeId, int datasetId) { Logger.Info("PrepareChangeLogQuery START"); try { //20121021-Leg: Correction "endringsid >= :startChangeId" //20121031-Leg: rad is now lokalId string sqlSelectGmlIds = "SELECT tabell || '.' || lokalid, type, endringsid FROM " + PDbSchema + ".endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid"; //string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid >= :startChangeId AND endringsid <= :endChangeId ORDER BY endringsid"; // string sqlSelectGmlIds = "SELECT tabell || '.' || rad, type FROM tilbyder.endringslogg WHERE endringsid > :startChangeId AND endringsid <= :endChangeId "; command = new NpgsqlCommand(sqlSelectGmlIds, conn); command.Parameters.Add(new NpgsqlParameter("startChangeId", NpgsqlDbType.Integer)); command.Parameters.Add(new NpgsqlParameter("endChangeId", NpgsqlDbType.Integer)); command.Prepare(); command.Parameters[0].Value = startChangeId; command.Parameters[1].Value = endChangeId; } catch (System.Exception exp) { Logger.ErrorException("PrepareChangeLogQuery function failed:", exp); throw new System.Exception("PrepareChangeLogQuery function failed", exp); } Logger.Info("PrepareChangeLogQuery END"); }
/// <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 void Append(string name, byte[] data, long expectedVersion) { using (var conn = new NpgsqlConnection(_connectionString)) { conn.Open(); using (var tx = conn.BeginTransaction()) { var version = MakeSureLastVersionMatches(name, expectedVersion, conn, tx); const string txt = @"INSERT INTO ES_Events2 (CustomerId, Name, Version, Data) VALUES(:customerId, :name, :version, :data)"; using (var cmd = new NpgsqlCommand(txt, conn, tx)) { cmd.Parameters.AddWithValue(":name", name); cmd.Parameters.AddWithValue(":version", version+1); cmd.Parameters.AddWithValue(":data", data); cmd.Parameters.AddWithValue(":customerId", customerId); cmd.ExecuteNonQuery(); } tx.Commit(); } } }
public string AccountCreation(string json) { string status = String.Empty; try { using (NpgsqlConnection con = new NpgsqlConnection(_connectionString)) { con.Open(); var command = new Npgsql.NpgsqlCommand("_bt_account_create", con); command.CommandType = System.Data.CommandType.StoredProcedure; var parameter = command.CreateParameter(); parameter.ParameterName = "input"; parameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Json; parameter.Value = json; command.Parameters.Add(parameter); int output = command.ExecuteNonQuery(); status = "ok"; } } catch (Exception ex) { status = ex.Message; } return(status); }
public void Roundtrip() { const string expected = "Something"; var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3, @p4", Conn); var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Text); var p2 = new NpgsqlParameter("p2", NpgsqlDbType.Varchar); var p3 = new NpgsqlParameter("p3", DbType.String); var p4 = new NpgsqlParameter { ParameterName = "p4", Value = expected }; Assert.That(p2.DbType, Is.EqualTo(DbType.String)); Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text)); Assert.That(p3.DbType, Is.EqualTo(DbType.String)); cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); p1.Value = p2.Value = p3.Value = expected; var reader = cmd.ExecuteReader(); reader.Read(); for (var i = 0; i < cmd.Parameters.Count; i++) { Assert.That(reader.GetFieldType(i), Is.EqualTo(typeof(string))); Assert.That(reader.GetString(i), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<string>(i), Is.EqualTo(expected)); Assert.That(reader.GetValue(i), Is.EqualTo(expected)); Assert.That(reader.GetFieldValue<char[]>(i), Is.EqualTo(expected.ToCharArray())); } reader.Close(); cmd.Dispose(); }
//Insert no banco recebendo como parametro um Diretor. public void Add(Diretor d) { try { bd.OpenConnection(); String query = "INSERT INTO tab_diretor VALUES (:cod_diretor, :nome_diretor)"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Parameters.Add(new NpgsqlParameter("nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Prepare(); sql.Parameters[0].Value = d.Cod_diretor; sql.Parameters[1].Value = d.Nome_diretor; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Diretor adicionado com sucesso!"); } } catch (NpgsqlException ex) { Console.WriteLine(ex); } finally { bd.CloseConnection(); } }
public void Create(IEnumerable<string> columnDefinitions) { var sb = new StringBuilder(); if(!string.IsNullOrEmpty(_schemaName)) sb.AppendFormat("CREATE SCHEMA \"{0}\";",_schemaName); sb.Append("CREATE TABLE "); sb.Append(NameWithSchema); sb.Append(" ("); foreach (string definition in columnDefinitions) { sb.Append(definition); sb.Append(", "); } sb.Remove(sb.Length - 2, 2); sb.Append(")"); var s = sb.ToString(); using (var command = new NpgsqlCommand(s, Connection, Transaction)) command.ExecuteNonQuery(); }
//Mostra todos os registro do banco diretamente, sem fazer cast para objeto. public void ShowAll() { try { bd.OpenConnection(); String query = "SELECT * FROM tab_diretor"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Prepare(); NpgsqlDataReader dr = sql.ExecuteReader(); while (dr.Read()) { //Listar todos os campos automatizado for (int i = 0; i < dr.FieldCount; i++) { Console.Write("{0} ", dr[i].ToString()); } Console.Write("\n"); } } catch (NpgsqlException e) { Console.WriteLine(e); } finally { bd.CloseConnection(); } }
internal NpgsqlTransaction(NpgsqlConnection conn, IsolationLevel isolation) { resman = new System.Resources.ResourceManager(this.GetType()); NpgsqlEventLog.LogMethodEnter(LogLevel.Debug, CLASSNAME, CLASSNAME); if ((isolation != IsolationLevel.ReadCommitted) && (isolation != IsolationLevel.Serializable)) throw new ArgumentOutOfRangeException(resman.GetString("Exception_UnsopportedIsolationLevel"), "isolation"); _conn = conn; _isolation = isolation; StringBuilder commandText = new StringBuilder("SET TRANSACTION ISOLATION LEVEL "); if (isolation == IsolationLevel.ReadCommitted) commandText.Append("READ COMMITTED"); else commandText.Append("SERIALIZABLE"); commandText.Append("; BEGIN"); NpgsqlCommand command = new NpgsqlCommand(commandText.ToString(), conn.Connector); command.ExecuteNonQuery(); _conn.Connector.Transaction = this; }
//Atualização passando como paramentro o ID e o novo atributo do Diretor. public void Update(int id, string new_nome_diretor) { try { bd.OpenConnection(); String query = "UPDATE tab_diretor SET nome_diretor = :new_nome_diretor WHERE cod_diretor = :id"; Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection); sql.Parameters.Add(new NpgsqlParameter("new_nome_diretor", NpgsqlTypes.NpgsqlDbType.Varchar)); sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer)); sql.Prepare(); sql.Parameters[0].Value = new_nome_diretor; sql.Parameters[1].Value = id; int linhasAfetadas = sql.ExecuteNonQuery(); if (Convert.ToBoolean(linhasAfetadas)) { Console.WriteLine("Diretor atualizado com sucesso!"); } else { Console.WriteLine("Não existe esse diretor!"); } } catch (NpgsqlException e) { Console.WriteLine(e); } finally { bd.CloseConnection(); } }
private void UpdateTable() { DataTable dt = new DataTable(); dt.Columns.Add("GolfID", typeof(string)); dt.Columns.Add("Förnamn", typeof(string)); dt.Columns.Add("Efternamn", typeof(string)); String sql = "SELECT golf_id, förnamn, efternamn FROM medlem;"; NpgsqlCommand command = new NpgsqlCommand(sql, GolfReception.conn); NpgsqlDataReader ndr = command.ExecuteReader(); while (ndr.Read()) { DataRow row = dt.NewRow(); row["GolfID"] = ndr["golf_id"]; row["Förnamn"] = ndr["förnamn"]; row["Efternamn"] = ndr["efternamn"]; dt.Rows.Add(row); } ndr.Close(); DataView dv = new DataView(dt); //TODO Fix this filter, only golfId working dv.RowFilter = "GolfID LIKE '" + golfId_textBox.Text + "*' AND Förnamn LIKE '" + firstName_textBox.Text + "*' AND Efternamn LIKE '" + lastName_textBox.Text + "*'"; //Set the component data dataGridView.DataSource = dv; //Set column header text dataGridView.Columns[0].HeaderText = "Golf-ID"; dataGridView.Columns[1].HeaderText = "Förnamn"; dataGridView.Columns[2].HeaderText = "Efternamn"; }
public static List<Remise> getListRemise(String query) { NpgsqlConnection con = Connexion.Connection(); try { List<Remise> l = new List<Remise>(); NpgsqlCommand Lcmd = new NpgsqlCommand(query, con); NpgsqlDataReader lect = Lcmd.ExecuteReader(); if (lect.HasRows) { while (lect.Read()) { Remise a = new Remise(); a.Id = Convert.ToInt64(lect["id"].ToString()); a.Reference = lect["reference"].ToString(); a.BaseRemise = lect["base_remise"].ToString(); a.Permanent = Convert.ToBoolean((lect["permanent"] != null) ? (!lect["permanent"].ToString().Trim().Equals("") ? lect["permanent"].ToString().Trim() : "false") : "false"); a.Grilles = BLL.GrilleRemiseBll.Liste("SELECT * FROM yvs_com_grille_remise WHERE remise =" + a.Id); a.Update = true; l.Add(a); } lect.Close(); } return l; } catch (NpgsqlException e) { Messages.Exception(e); return null; } finally { Connexion.Deconnection(con); } }
static void ClearCatalogue(string connString) { try { NpgsqlConnection conn = new NpgsqlConnection(connString); conn.Open(); Console.Clear(); string clearTable = "DELETE FROM phone_book"; NpgsqlCommand cmd = new NpgsqlCommand(clearTable, conn); cmd.ExecuteNonQuery(); Console.WriteLine(">>> Catalogue cleared"); Console.ReadKey(); Console.Clear(); conn.Close(); } catch (Exception msg) { Console.WriteLine(msg.ToString()); throw; } }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { if (comboBox1.SelectedValue.ToString() == "Quartiers") { grd.ClearSelection(); cmd = new n.NpgsqlCommand("select code,nom,densite from quartier", cnx); ada = new n.NpgsqlDataAdapter(cmd); dt = new DataTable(); ada.Fill(dt); grd.DataSource = dt; } else { if (comboBox1.SelectedValue.ToString() == "Antennes") { grd.ClearSelection(); cmd = new n.NpgsqlCommand("select num,nom,puissance from antenne", cnx); ada = new n.NpgsqlDataAdapter(cmd); dt = new DataTable(); ada.Fill(dt); grd.DataSource = dt; } else { dt = new DataTable(); grd.DataSource = dt; } } }