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
 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);
     }
 }
 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 = "";
     }
 }
示例#3
1
文件: TextTests.cs 项目: Emill/Npgsql
        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();
        }
示例#4
1
        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";
        }
示例#5
1
文件: TextTests.cs 项目: Emill/Npgsql
        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()));
        }
示例#6
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 [email protected] AND [email protected] 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);
            }
        }
示例#7
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();
                }
            }
        }
示例#8
0
文件: Return.cs 项目: roczj/mixerp
        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 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;
            }
        }
示例#10
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;
 }
示例#11
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);
        }
示例#12
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;
 }
示例#13
0
       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;
               }
           }
       }
        // 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;
        }
示例#15
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();
        }
示例#16
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;
            }
        }
示例#17
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;
 }
示例#18
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;
        }
 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 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();
     }
 }
示例#21
0
 /// <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));
         }
     }
 }
        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 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();
        }
示例#24
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);
        }
        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;
        }
        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
            {
            }
        }
示例#27
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();
                            }
                        }
                    }
                }
            }
        }
示例#28
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);
     }
 }
示例#29
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;
            }
        }
示例#30
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);
     }
 }