Represents a SQL statement or function (stored procedure) to execute against a PostgreSQL database. This class cannot be inherited.
Inheritance: System.Data.Common.DbCommand, ICloneable
Ejemplo n.º 1
43
 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()));
     }
       }
     }
 }
Ejemplo n.º 2
40
       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;
               }
           }
       }
Ejemplo n.º 3
4
 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);
     }
 }
Ejemplo n.º 4
3
        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()));
        }
Ejemplo n.º 5
1
        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 = "";
     }
 }
Ejemplo n.º 7
1
 /// <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));
         }
     }
 }
Ejemplo n.º 8
0
 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;
 }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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;
            }
        }
Ejemplo n.º 11
0
 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();
            }
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
        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);
        }
Ejemplo n.º 15
0
        //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");
        }
Ejemplo n.º 16
0
        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);
        }
Ejemplo n.º 17
0
        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);
        }
Ejemplo n.º 18
0
        //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();
            }
        }
Ejemplo n.º 19
0
        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();
            }
        }
Ejemplo n.º 20
0
        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
            {
            }
        }
Ejemplo n.º 22
0
        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();
            }
        }
Ejemplo n.º 23
0
        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;
            }
        }
Ejemplo n.º 24
0
        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 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();
     }
 }
Ejemplo n.º 26
0
        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;
        }
Ejemplo n.º 28
0
        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);
        }
Ejemplo n.º 29
0
        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);
        }
Ejemplo n.º 31
0
        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);
        }
Ejemplo n.º 32
0
        /// <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!";
            }
        }
Ejemplo n.º 34
0
        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);
        }
Ejemplo n.º 36
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();
            }
        }
Ejemplo n.º 37
0
 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;
 }
Ejemplo n.º 38
0
        //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();
            }
        }
Ejemplo n.º 39
0
        //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();
        }
Ejemplo n.º 40
0
        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);
        }
Ejemplo n.º 41
0
        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);
        }
Ejemplo n.º 42
0
        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();
            }
        }
Ejemplo n.º 43
0
        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();
                            }
                        }
                    }
                }
            }
        }
Ejemplo n.º 44
0
        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();
            }
        }
Ejemplo n.º 45
0
 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;
 }
Ejemplo n.º 46
0
        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);
        }
Ejemplo n.º 47
0
        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");
        }
Ejemplo n.º 48
0
        /// <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);
        }
Ejemplo n.º 49
0
        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();
                }
            }
        }
Ejemplo n.º 50
0
        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);
        }
Ejemplo n.º 51
0
        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();
        }
Ejemplo n.º 52
0
        //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();
            }
        }
Ejemplo n.º 53
0
        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();
        }
Ejemplo n.º 54
0
        //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;
        }
Ejemplo n.º 56
0
        //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();
            }
        }
Ejemplo n.º 57
0
        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";
        }
Ejemplo n.º 58
0
 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);
     }
 }
Ejemplo n.º 59
0
        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;
            }
        }
Ejemplo n.º 60
0
        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;
                }
            }
        }