ExecuteReader() public method

Sends the CommandText to the Connection and builds a NpgsqlDataReader.
public ExecuteReader ( ) : Npgsql.NpgsqlDataReader
return Npgsql.NpgsqlDataReader
Example #1
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;
               }
           }
       }
 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);
     }
 }
Example #3
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()));
        }
Example #4
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));
         }
     }
 }
Example #5
0
        public void SchemaOnly([Values(PrepareOrNot.NotPrepared, PrepareOrNot.Prepared)] PrepareOrNot prepare)
        {
            using (var conn = OpenConnection())
            {
                conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
                using (var cmd = new NpgsqlCommand(
                    "SELECT 1 AS some_column;" +
                    "UPDATE data SET name='yo' WHERE 1=0;" +
                    "SELECT 1 AS some_other_column",
                    conn))
                {
                    if (prepare == PrepareOrNot.Prepared)
                        cmd.Prepare();
                    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                    {
                        Assert.That(reader.Read(), Is.False);
                        var t = reader.GetSchemaTable();
                        Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_column"));
                        Assert.That(reader.NextResult(), Is.True);
                        Assert.That(reader.Read(), Is.False);
                        t = reader.GetSchemaTable();
                        Assert.That(t.Rows[0]["ColumnName"], Is.EqualTo("some_other_column"));
                        Assert.That(reader.NextResult(), Is.False);
                    }

                    // Close reader in the middle
                    using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
                        reader.Read();
                }
            }
        }
Example #6
0
        public void LoadValuesFor(NeuralNetwork neuralNetwork)
        {
            var firstLayerWeights = new ValueList<double>();
            var secondLayerWeights = new ValueList<double>();

            var secondLayerThresholds = new ValueList<double>();
            var thirdLayerThresholds = new ValueList<double>();

            try
            {
                var connection = new NpgsqlConnection(ConnectionString);
                connection.Open();
                var command = new NpgsqlCommand { Connection = connection };

                command.CommandText = "SELECT weight FROM pr4_weights_layer1 ORDER BY id ASC";
                var reader = command.ExecuteReader();

                while (reader.Read())
                    firstLayerWeights.Add(reader.GetDouble(0));

                reader.Close();
                command.CommandText = "SELECT weight FROM pr4_weights_layer2 ORDER BY id ASC";
                reader = command.ExecuteReader();

                while (reader.Read())
                    secondLayerWeights.Add(reader.GetDouble(0));

                reader.Close();
                command.CommandText = "SELECT threshold FROM pr4_thresholds_layer2 ORDER BY id ASC";
                reader = command.ExecuteReader();

                while (reader.Read())
                    secondLayerThresholds.Add(reader.GetDouble(0));

                reader.Close();
                command.CommandText = "SELECT threshold FROM pr4_thresholds_layer3 ORDER BY id ASC";
                reader = command.ExecuteReader();

                while (reader.Read())
                    thirdLayerThresholds.Add(reader.GetDouble(0));

                neuralNetwork.SetWeightsForLayer(1, firstLayerWeights);
                neuralNetwork.SetWeightsForLayer(2, secondLayerWeights);

                neuralNetwork.SetThresholdsForLayer(2, secondLayerThresholds);
                neuralNetwork.SetThresholdsForLayer(3, thirdLayerThresholds);
            }
            catch (Exception)
            {
                Console.WriteLine("There was a problem loading values from the database.");
                throw;
            }
        }
        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;
            }
        }
Example #8
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);
        }
Example #9
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);
        }
Example #10
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();
                            }
                        }
                    }
                }
            }
        }
Example #11
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();
        }
        // 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;
        }
Example #13
0
        public static DBclasses.KindOfAnimal getKindOfAnimalsById(int id)
        {
            DBclasses.KindOfAnimal result = new DBclasses.KindOfAnimal();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from kindofanimals where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id      = id;
                    result.name    = reader["name"].ToString();
                    result.photo   = reader["photo"].ToString();
                    result.infoURL = reader["infoURL"].ToString();
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    return(null);
                }
            }

            return(result);
        }
Example #14
0
        //Método responsável por retornar todas as categorias fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
        public List <Categoria> getAll()
        {
            List <Categoria> retorno = new List <Categoria>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_categoria";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Categoria c = new Categoria();
                    c.Cod_categoria  = dr.GetInt32(0);
                    c.Desc_categoria = dr.GetString(1);
                    retorno.Add(c);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Example #15
0
        //Método responsável por retornar uma Categoria cujo ID seja igual o passado pelo parâmetro.
        public Categoria getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_categoria WHERE cod_categoria = :id";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

                sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                sql.Prepare();

                sql.Parameters[0].Value = id;

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Categoria c = new Categoria();
                    c.Cod_categoria  = dr.GetInt32(0);
                    c.Desc_categoria = dr.GetString(1);
                    return(c);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Example #16
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;
 }
 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);
     }
 }
Example #18
0
        public static DBclasses.AsksCategory getAsksCategoriesById(int id)
        {
            DBclasses.AsksCategory result = new DBclasses.AsksCategory();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from askscategories where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id          = id;
                    result.name        = reader["name"].ToString();
                    result.photo       = reader["photo"].ToString();
                    result.description = reader["description"].ToString();
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    return(null);
                }
            }

            return(result);
        }
Example #19
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);
        }
Example #20
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);
     }
 }
Example #21
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";
        }
        public string markeratid()
        {
            //Skapar int nummer som innehåller en vald rad i dataGridView1.
            int nummer = dataGridView1.CurrentRow.Index;

            //Skapar strängen valtid.
            //Strängen innehåller information om spelare. Hämtar golfid från databasen, tabellen person.
            String valtid = "select golfid from person;";
            //Skapar ett nytt Npgsql kommando, command10.
            NpgsqlCommand command10 = new NpgsqlCommand(valtid, Huvudfönster.conn);
            //Skapar en Npgsql "DataReader", dr4. Samt utför kommando, command10.
            NpgsqlDataReader dr4 = command10.ExecuteReader();

            //Skapar strängen id.
            //Skapar int räknare och sätter denna till 0.
            string id = "";
            int räknare = 0;

            //Skapar en while-loop.
            while (dr4.Read())
            {
                //Gör en uträkning för golfid.
                if (räknare == nummer)
                {
                    id = dr4["golfid"].ToString();
                }
                räknare = räknare + 1;
            }

            //Stänger DataReader.
            dr4.Close();

            return id;
        }
Example #23
0
        public void LogarUsuario(string email, string pass)
        {
            string strCom = "Select id_usuario, email_usuario , nm_usuario , senha_usuario from usuario where email_usuario = :email_usuario and senha_usuario = :senha_usuario";
            try
            {
                using (NpgsqlConnection conexao = ConnectionFactory.createConnection())
                {
                    Model.Aluno objAluno = new Model.Aluno();
                    NpgsqlCommand comm = new NpgsqlCommand(strCom, conexao);
                    comm.Parameters.AddWithValue("email_usuario", email);
                    comm.Parameters.AddWithValue("senha_usuario", pass);
                    NpgsqlDataReader dataReader = comm.ExecuteReader();

                    CarregarAluno(dataReader);
                    //carregarCursos
                    CursoDAL dalCurso = new CursoDAL();
                    List<Model.Curso> vListCurso = new List<Model.Curso>();
                    vListCurso = dalCurso.ObterCursoXAluno(Model.Session.Session.Aluno.Id);
                    Model.Session.Session.Aluno.CursoCadastrado = new List<Model.Curso>();
                    Model.Session.Session.Aluno.CursoCadastrado = vListCurso;
                }
            }
            catch (Exception ex)
            {
                //log
                throw new ExceptionDAL(ex.Message, ex);
            }
        }
Example #24
0
        /// <summary>
        /// Обновить данные
        /// </summary>
        protected override void UpdateDefaultButton()
        {
            NpgsqlConnection npgSqlConnection = new NpgsqlConnection(WaveEnvirument.ConnectionString);
            try
            {
                npgSqlConnection.Open();

                    //формируем запрос
                    using (NpgsqlCommand sqlQuery = new NpgsqlCommand("SELECT * FROM public.object_structure;", npgSqlConnection))
                    {
                        NpgsqlDataReader reader = sqlQuery.ExecuteReader();
                        lastUpdate = DateTime.Now;

                        while (reader.Read())
                        {

                        }

                }
            }
            catch (Exception ex)
            {
            }
            finally
            {

            }
        }
Example #25
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;
 }
        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);
        }
        /// <summary>
        /// This method reads users from database, looks for username match and checks
        /// if pass is correct for given username.
        /// </summary>
        /// <returns> Bool representing success or failure of user login</returns>
        public static bool Verify(string name, string pass)
        {
            using(NpgsqlConnection conn = Connect())
            {
                conn.Open();
                NpgsqlCommand command = new NpgsqlCommand("select name, pass from users order by id ASC", conn);
                NpgsqlDataReader table = command.ExecuteReader();

                List<Users> output = new List<Users>();
                foreach (IDataRecord row in table)
                {
                    if (name == (string)row["name"])
                    {
                        if(pass == (string)row["pass"])
                        {
                            // do something here to give authorization cookie?
                            // or if not here, then on client side?
                            return true;
                        }
                        else{
                            throw new System.ApplicationException("Name and pass do not match.");
                            // return false;
                        }
                    }
                }
            }
            return false;
        }
Example #28
0
        protected void Page_Load(object sender, EventArgs e)
        {
            if (SiteMaster.Rank == "2" && SiteMaster.LoggedIn)
            {
                // Specify connection options and open an connection
                NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=postgres;" +
                                        "Password=root;Database=project56;");
                conn.Open();

                // Define query
                NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM users", conn);

                // Execute query
                NpgsqlDataReader dr = cmd.ExecuteReader();

                //Get rows and place in ArrayList
                while (dr.Read())
                {
                    maillist.Add(dr[0]);
                    ranklist.Add(dr[2]);
                }

                // Close connection
                conn.Close();

                for(int i=0;i<maillist.Count;i++)
                {
                    maildrop.Items.Add(maillist[i].ToString()); //Add all emails to dropdown
                }
            }
        }
Example #29
0
        public Main()
        {
            InitializeComponent();
            string str = "Uid=postgres; Password=123; server=localhost; port=5432; Database=classmanagement;";
            NpgsqlConnection conn = new NpgsqlConnection(str);
            conn.Open();
            String select = "SELECT name FROM subject";
            NpgsqlCommand select_command = new NpgsqlCommand(select, conn);
            NpgsqlDataReader reader = select_command.ExecuteReader();

            while (reader.Read())
            {
                comboBox1.Items.Add(reader[0]);
                comboBox3.Items.Add(reader[0]);
                comboBox4.Items.Add(reader[0]);
                comboBox6.Items.Add(reader[0]);
                comboBox7.Items.Add(reader[0]);
            }
            dataGridView2.Refresh();
            dataGridView2.ColumnCount = 2;
            dataGridView2.Columns[0].Name = "Name";
            dataGridView2.Columns[1].Name = "Surname";
            reader.Close();
            conn.Close();
        }
Example #30
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();
            }
        }
Example #31
0
        private void initDataListView()
        {
            dataListView.Items.Clear();

            var date = dateTimePicker.Value.Date;
            var dateQuery = "'" + date.Year + "-" + date.Month + "-" + date.Day + "'";
            var query = "SELECT Data.start_t, Data.end_t, Category.name" +
                        " FROM Data" +
                        " JOIN Mem_cat ON (Data.mem_cat_id=Mem_cat.id)" +
                        " JOIN Category ON (Mem_cat.category_id=Category.id)" +
                        " WHERE Mem_cat.member_id=" + id +
                        " AND Data.day=" + dateQuery;

            var cmd = new NpgsqlCommand(query, npgsqlConnection);
            var dr = cmd.ExecuteReader();

            while (dr.Read())
            {
                var startTime = dr.GetTimeSpan(0);
                var endTime = dr.GetTimeSpan(1);
                var category = dr.GetString(2);

                var item = new ListViewItem(startTime.ToString());
                item.SubItems.Add(endTime.ToString());
                item.SubItems.Add(category);
                dataListView.Items.Add(item);
            }

            dr.Close();
        }
Example #32
0
 public string getXml(string testID)
 {
     string result = "";
     try
     {
         NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
         conn.Open();
         NpgsqlCommand cmd = new NpgsqlCommand("SELECT  id, xml_answer as qXml FROM completed_test  where id= @testID", conn);
         cmd.Parameters.AddWithValue("testID", int.Parse(testID));
         //cmd.Parameters.Add("testID", testID);
         NpgsqlDataReader dr = cmd.ExecuteReader();
         if (dr.Read())
         {
             result = dr["qXml"].ToString();
         }
         dr.Close();
         conn.Close();
         return result.TrimStart();
     }
     catch (Exception ex)
     {
         Debug.WriteLine(ex.ToString());
     }
     return "";
 }
        public static List<ModeloUsuario> ObtenerTodosUsuarios()
        {
            try
            {
                List<ModeloUsuario> usuarios = null;
                NpgsqlCommand cmd = new NpgsqlCommand("select nombre,apellido,direccion,nombreusuario,email from usuario", Conexion.conexion);
                Conexion.abrirConexion();
                NpgsqlDataReader reader = cmd.ExecuteReader();
                if (reader.HasRows)
                {
                    usuarios = new List<ModeloUsuario>();
                    while (reader.Read())
                    {
                        ModeloUsuario usuario = new ModeloUsuario();
                        usuario.nombre = reader["nombre"].ToString();
                        usuario.apellido = reader["apellido"].ToString();
                        usuario.direccion = reader["direccion"].ToString();
                        usuario.nombreUsuario = reader["nombreusuario"].ToString();
                        //usuario.contrasena = reader["contrasena"].ToString();
                       // usuario.nivelAcceso = reader["nivelacceso"].ToString();
                        usuario.email = reader["email"].ToString();
                        usuarios.Add(usuario);
                    }
                }
                Conexion.cerrarConexion();
                return usuarios;

            }
            catch (Exception ex)
            {
                throw new Exception("Hubo un error con la base de datos, intente de nuevo más tarde");
            }
        }
        static int limitForPointView = 10000; //количество точек для отображения на Zedgraph

        #endregion Fields

        #region Methods

        public static List<List<string>> GetAllExperimentGroups(ADatabase db)
        {
            try
            {
                List<List<string>> result = new List<List<string>>();
                string sql = "SELECT  experimentgroups.experimentgroupsid, experimentgroups.datestart, experimentgroups.datestop - experimentgroups.datestart,(select Count(*) from experiments where experimentgroups.experimentgroupsid = experiments.experimentgroupsid) as cn ,(select Count(*) as pointcount from parametersvalues where  parametersvalues.experimentid = (select experimentid from experiments where experiments.experimentgroupsID = experimentgroups.experimentgroupsID FETCH FIRST ROW ONLY)  and parametersvalues.parametersid = 1), experimentgroups.powerdelay, experimentgroups.commdelay, experimentgroups.description FROM ExperimentGroups order by experimentgroups.experimentgroupsid";
                NpgsqlCommand readCommand = new NpgsqlCommand(sql, db.Connection);
                NpgsqlDataReader myreader = readCommand.ExecuteReader();
                List<string> line;
                while (myreader.Read())
                {
                    line = new List<string>();
                    // load the combobox with the names of the people inside.
                    // myreader[0] reads from the 1st Column
                    for (int i = 0; i < myreader.FieldCount; i++)
                    {
                        line.Add(myreader[i].ToString());
                    }

                    result.Add(line);
                }
                myreader.Close(); // we are done with the reader
                return result;
            }
            catch (Exception ex)
            {
                FileWorker.WriteEventFile(DateTime.Now, "DatabaseWorker", "GetAllExperimentGroups", ex.Message);
                return null;
            }
        }
Example #35
0
        public string canHandIn(string testID)
        {
            string result = "";
            NpgsqlConnection conn = new NpgsqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JE"].ConnectionString);
            conn.Open();
            NpgsqlCommand cmd = new NpgsqlCommand("SELECT  id, start_time, end_time FROM completed_test  where id= @testID ", conn);
            cmd.Parameters.AddWithValue("testID", int.Parse(testID));
            //cmd.Parameters.Add("testID", testID);
            NpgsqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = dr["start_time"].ToString();
                if (dr["end_time"] != null)
                {

                }
            }
            else
            {
                dr.Close();
                conn.Close();
                return "FINNS INGET TEST";
            }
            dr.Close();
            conn.Close();
            TimeSpan diffTime = DateTime.Parse(DateTime.Now.ToString()) - DateTime.Parse(result);
            if (diffTime.TotalMinutes > 29)
            {
                return "TIDEN DROG ÖVER";
            }
            return "OK";
        }
Example #36
0
 public void MultipleCommands(bool[] queries)
 {
     using (var conn = OpenConnection())
     {
         conn.ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
         var sb = new StringBuilder();
         foreach (var query in queries)
             sb.Append(query ? "SELECT 1;" : "UPDATE data SET name='yo' WHERE 1=0;");
         var sql = sb.ToString();
         foreach (var prepare in new[] {false, true})
         {
             using (var cmd = new NpgsqlCommand(sql, conn))
             {
                 if (prepare)
                     cmd.Prepare();
                 using (var reader = cmd.ExecuteReader())
                 {
                     var numResultSets = queries.Count(q => q);
                     for (var i = 0; i < numResultSets; i++)
                     {
                         Assert.That(reader.Read(), Is.True);
                         Assert.That(reader[0], Is.EqualTo(1));
                         Assert.That(reader.NextResult(), Is.EqualTo(i != numResultSets - 1));
                     }
                 }
             }
         }
     }
 }
Example #37
0
        public void Int32()
        {
            var cmd = new NpgsqlCommand("SELECT @p1, @p2, @p3", Conn);
            var p1 = new NpgsqlParameter("p1", NpgsqlDbType.Integer);
            var p2 = new NpgsqlParameter("p2", DbType.Int32);
            var p3 = new NpgsqlParameter { ParameterName = "p3", Value = 8 };
            Assert.That(p3.NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
            Assert.That(p3.DbType, Is.EqualTo(DbType.Int32));
            cmd.Parameters.Add(p1);
            cmd.Parameters.Add(p2);
            cmd.Parameters.Add(p3);
            p1.Value = p2.Value = (long)8;
            var reader = cmd.ExecuteReader();
            reader.Read();

            for (var i = 0; i < cmd.Parameters.Count; i++)
            {
                Assert.That(reader.GetInt32(i),                 Is.EqualTo(8));
                Assert.That(reader.GetInt64(i),                 Is.EqualTo(8));
                Assert.That(reader.GetInt16(i),                 Is.EqualTo(8));
                Assert.That(reader.GetByte(i),                  Is.EqualTo(8));
                Assert.That(reader.GetFloat(i),                 Is.EqualTo(8.0f));
                Assert.That(reader.GetDouble(i),                Is.EqualTo(8.0d));
                Assert.That(reader.GetDecimal(i),               Is.EqualTo(8.0m));
                Assert.That(reader.GetValue(i),                 Is.EqualTo(8));
                Assert.That(reader.GetProviderSpecificValue(i), Is.EqualTo(8));
                Assert.That(reader.GetFieldType(i),             Is.EqualTo(typeof(int)));
                Assert.That(reader.GetDataTypeName(i),          Is.EqualTo("int4"));
            }

            reader.Dispose();
            cmd.Dispose();
        }
Example #38
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;
 }
Example #39
0
 public void RecordsAffected()
 {
     var command = new NpgsqlCommand("insert into data (field_int4) values (7); insert into data (field_int4) values (8)", Conn);
     using (var dr = command.ExecuteReader()) {
         Assert.AreEqual(2, dr.RecordsAffected);
     }
 }
Example #40
0
        public static List <DBclasses.Pet> getAllPets()
        {
            List <DBclasses.Pet> result = new List <DBclasses.Pet>();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from pets order by id";

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    DBclasses.Pet row = new DBclasses.Pet();

                    row.id                  = (long)reader["id"];
                    row.kindOfAnimals       = (int)reader["kindOfAnimals"];
                    row.addedByUser         = (long)reader["addedByUser"];
                    row.addedDateTime       = reader["addedDateTime"].ToString();
                    row.lastUpdatedByUser   = (long)reader["lastUpdatedByUser"];
                    row.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
                    row.name                = reader["name"].ToString();
                    row.photos              = reader["photos"] as string[];
                    row.code                = reader["code"].ToString();
                    row.sterilised          = (bool)reader["sterilised"];
                    row.pasported           = (bool)reader["pasported"];
                    row.isLosted            = (bool)reader["isLosted"];
                    row.birthDate           = reader["birthDate"].ToString();
                    row.roughlyBirth        = (bool)reader["roughlyBirth"];
                    row.roughlyDay          = (bool)reader["roughlyDay"];
                    row.roughlyMonth        = (bool)reader["roughlyMonth"];
                    row.organizationID      = (int)reader["organizationID"];
                    row.address             = reader["address"].ToString();
                    row.isDisabled          = (bool)reader["isDisabled"];
                    row.disableDescription  = reader["disableDescription"].ToString();
                    row.price               = (long)reader["price"];
                    row.callTimeFrom        = reader["_callTimeFrom"].ToString();
                    row.callTimeTo          = reader["_callTimeTo"].ToString();
                    row.region              = (int)reader["region"];
                    row.phones              = reader["phones"] as string[];
                    row.vaccinated          = (bool)reader["vaccinated"];
                    row.sex                 = (bool)reader["sex"];

                    result.Add(row);
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    return(null);
                }
            }

            return(result);
        }
Example #41
0
        public static DBclasses.Organization getOrganizationById(int id)
        {
            DBclasses.Organization result = new DBclasses.Organization();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from organizations where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id            = id;
                    result.isDeleted     = (bool)reader["isDeleted"];
                    result.name          = reader["name"].ToString();
                    result.contactPerson = reader["contactPerson"].ToString();

                    result.emails = reader["emails"] as string[];
                    result.phones = reader["phones"] as string[];

                    /* TODO */
                    result.callTimeFrom = reader["callTimeFrom"].ToString();;
                    result.callTimeTo   = reader["callTimeTo"].ToString();;

                    result.addresses = reader["addresses"] as string[];
                    result.photo     = reader["photo"].ToString();

                    result.aboutOrg = reader["aboutOrg"].ToString();
                    result.needHelp = reader["needHelp"].ToString();
                    result.donation = reader["donation"].ToString();
                    result.otherOrg = reader["otherOrg"].ToString();
                    result.linkToVK = reader["linkToVK"].ToString();
                    result.linkToFB = reader["linkToFB"].ToString();
                    result.linkToYT = reader["linkToYT"].ToString();
                    result.linkToTG = reader["linkToTG"].ToString();
                    result.linkToIG = reader["linkToIG"].ToString();

                    result.region = (int)reader["region"];
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    result.name = ex.StackTrace + ex.Message;
                    return(result);
                }
            }

            return(result);
        }
Example #42
0
        //Método responsável por retornar um Filme com todos os seus diretores cujo ID seja igual o passado pelo parâmetro.
        public Filme getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_filme WHERE cod_filme = :id";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

                sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                sql.Prepare();

                sql.Parameters[0].Value = id;

                NpgsqlDataReader dr = sql.ExecuteReader();

                CategoriaDAO cdao = new CategoriaDAO();

                while (dr.Read())
                {
                    Filme f = new Filme();
                    f.Cod_filme  = dr.GetInt32(0);
                    f.Nome_filme = dr.GetString(1);
                    f.Data       = dr.GetDateTime(2);

                    //Como a classe filme tem um atributo do tipo Objeto Categoria,
                    //utilizo a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco me retorna, com esse ID passo pro
                    //método getOne que retorna um Objeto do tipo Categoria.
                    f.Categoria = cdao.getOne(3);

                    //É utilizado o método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
                    f.Diretores = this.getDiretores(f.Cod_filme);

                    return(f);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Example #43
0
        public static Npgsql.NpgsqlDataReader GetDataReader(Npgsql.NpgsqlCommand command)
        {
            if (command != null)
            {
                Npgsql.NpgsqlDataReader reader = default(Npgsql.NpgsqlDataReader);
                using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(MixERP.Net.DBFactory.DBConnection.ConnectionString()))
                {
                    command.Connection = connection;

                    command.Connection.Open();
                    reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    return(reader);
                }
            }

            return(null);
        }
Example #44
0
        //Método responsável por retornar todos os filmes e seus diretores fazendo um cast para objeto, retornando uma lista com todos os filmes do banco.
        public List <Filme> getAll()
        {
            List <Filme> retorno = new List <Filme>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_filme";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                CategoriaDAO cdao = new CategoriaDAO();

                //Esse trecho é executado pra cada filme.
                while (dr.Read())
                {
                    Filme f = new Filme();
                    f.Cod_filme  = dr.GetInt32(0);
                    f.Nome_filme = dr.GetString(1);
                    f.Data       = dr.GetDateTime(2);

                    //Como a classe filme tem um atributo do tipo Objeto Categoria,
                    //é utilizada a instancia de CategoriaDAO pra pegar o objeto a partir do ID que o banco retorna, esse ID é passado para o
                    //método getOne que retorna um Objeto do tipo Categoria.
                    f.Categoria = cdao.getOne(3);

                    //É utilizado método getDiretores que retorna toda lista de diretores do filme para fazer a atribuição.
                    f.Diretores = this.getDiretores(f.Cod_filme);

                    retorno.Add(f);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Example #45
0
        public void quartier_traitement(int code)
        {
            cnx.Close();
            cnx.Open();
            n.NpgsqlCommand cm = new n.NpgsqlCommand("create or replace view buffer_all as select num,ST_buffer(emp,puissance)as bf from antenne;", cnx);
            cm.ExecuteNonQuery();
            n.NpgsqlCommand cma = new n.NpgsqlCommand("create or replace view quart as select st_intersection(quartier.zone,bf)as inter from quartier,buffer_all where st_intersects(quartier.zone,bf) and quartier.code=" + code + "", cnx);
            cma.ExecuteNonQuery();
            n.NpgsqlCommand sauvegarde = new n.NpgsqlCommand("CREATE OR REPLACE VIEW cell as select ST_UNION(inter) as geom  from quart", cnx);
            sauvegarde.ExecuteNonQuery();
            n.NpgsqlCommand c   = new n.NpgsqlCommand(" select ST_AREA(geom) as valeur from cell", cnx);
            double          i   = 0;
            var             rea = c.ExecuteReader();

            while (rea.Read())
            {
                i = Double.Parse(rea["valeur"].ToString());
            }


            n.NpgsqlCommand ca = new n.NpgsqlCommand(" select ST_AREA(zone) as valeur from quartier where code=" + code + "", cnx);
            double          ia = 0;
            var             re = ca.ExecuteReader();

            while (re.Read())
            {
                ia = Double.Parse(re["valeur"].ToString());
            }
            tc.Text    = ((i / ia) * 100) + "%";
            supc.Text  = i + "";
            supnc.Text = (ia - i) + "";

            n.NpgsqlCommand de  = new n.NpgsqlCommand(" select densite as valeur from quartier where code=" + code + "", cnx);
            double          iaa = 0;
            var             e   = de.ExecuteReader();

            while (e.Read())
            {
                iaa = Double.Parse(e["valeur"].ToString());
            }
            pc.Text  = (iaa * i) + "";
            pnc.Text = ((ia - i) * iaa) + "";
        }
Example #46
0
        public string Comment(string json)

        {
            string status = String.Empty;

            try

            {
                using (NpgsqlConnection con = new NpgsqlConnection(_connectionString))
                {
                    con.Open();


                    var command = new Npgsql.NpgsqlCommand("_bt_comments_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);
                    NpgsqlDataReader reader    = command.ExecuteReader();
                    string           resultset = string.Empty;
                    while (reader.Read())
                    {
                        if (reader[0] != null)
                        {
                            resultset = reader[0].ToString();
                        }
                    }

                    status = "ok";

                    status = resultset;
                }
            }
            catch (Exception ex)
            {
                status = ex.Message;
            }
            return(status);
        }
Example #47
0
        public static DBclasses.User getUserById(long id)
        {
            DBclasses.User result = new DBclasses.User();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from users where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id             = id;
                    result.isDeleted      = (bool)reader["isDeleted"];
                    result.rank           = (int)reader["rank"];
                    result.organizationID = (int)reader["organizationID"];
                    result.name           = reader["name"].ToString();
                    result.email          = reader["email"].ToString();
                    result.phone1         = reader["phone1"].ToString();
                    result.phone2         = reader["phone2"].ToString();
                    result.getAsksInfo    = reader["getAsksInfo"] as int[];
                    result.photo          = reader["photo"].ToString();
                    /* TODO */
                    result.callTimeFrom = reader["callTimeFrom"].ToString();;
                    result.callTimeTo   = reader["callTimeTo"].ToString();;
                    result.region       = (int)reader["region"];
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    result.name = ex.StackTrace + ex.Message;
                    return(result);
                }
            }

            return(result);
        }
Example #48
0
        //Método responsável por retornar um Diretor cujo ID seja igual o passado pelo parâmetro.
        public Diretor getOne(int id)
        {
            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_diretor WHERE cod_diretor = :id";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

                sql.Parameters.Add(new NpgsqlParameter("id", NpgsqlTypes.NpgsqlDbType.Integer));
                sql.Prepare();

                sql.Parameters[0].Value = id;

                NpgsqlDataReader dr = sql.ExecuteReader();

                while (dr.Read())
                {
                    Diretor d = new Diretor();
                    d.Cod_diretor  = dr.GetInt32(0);
                    d.Nome_diretor = dr.GetString(1);

                    //É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
                    d.filmes = this.getFilmes(d.Cod_diretor);

                    return(d);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(null);
        }
Example #49
0
        //Método responsável por retornar todos os diretores fazendo um cast para objeto, retornando uma lista com todos os diretores do banco.
        public List <Diretor> getAll()
        {
            List <Diretor> retorno = new List <Diretor>();

            try
            {
                bd.OpenConnection();

                String query             = "SELECT * FROM tab_diretor";
                Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);
                sql.Prepare();

                NpgsqlDataReader dr = sql.ExecuteReader();

                //Esse trecho é executado para cada diretor
                while (dr.Read())
                {
                    Diretor d = new Diretor();
                    d.Cod_diretor  = dr.GetInt32(0);
                    d.Nome_diretor = dr.GetString(1);

                    //É utilizado o método getFilmes que retorna toda lista de filmes do diretor para fazer a atribuição.
                    d.filmes = this.getFilmes(d.Cod_diretor);

                    retorno.Add(d);
                }
            }
            catch (NpgsqlException e)
            {
                Console.WriteLine(e);
            }
            finally
            {
                bd.CloseConnection();
            }

            return(retorno);
        }
Example #50
0
        public static DBclasses.ArchivePet getArchivePetById(int id)
        {
            DBclasses.ArchivePet result = new DBclasses.ArchivePet();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from archivepets where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id                      = id;
                    result.kindOfAnimals           = (int)reader["kindOfAnimals"];
                    result.addedByUser             = (long)reader["addedByUser"];
                    result.addedDateTime           = reader["addedDateTime"].ToString();
                    result.organizationID          = (int)reader["organizationID"];
                    result.name                    = reader["name"].ToString();
                    result.photos                  = reader["photos"] as string[];
                    result.homeFound               = (bool)reader["homeFound"];
                    result.newOwnerName            = reader["newOwnerName"].ToString();
                    result.newOwnerPhone           = reader["newOwnerPhone"].ToString();
                    result.lastContactWithNewOwner = reader["lastContactWithNewOwner"].ToString();
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    result.name = ex.StackTrace + ex.Message;
                    return(result);
                }
            }

            return(result);
        }
Example #51
0
        public static DBclasses.Ask getAskById(int id)
        {
            DBclasses.Ask result = new DBclasses.Ask();

            Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(PetFinder.Code.Constants.NpgsqlConnect);
            string queue = "select * from asks where \"id\"=" + id.ToString();

            Npgsql.NpgsqlCommand command = new Npgsql.NpgsqlCommand(queue, connection);
            connection.Open();
            Npgsql.NpgsqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    result.id                  = id;
                    result.isDeleted           = (bool)reader["isDeleted"];
                    result.addedByUser         = (long)reader["addedByUser"];
                    result.addedDateTime       = reader["addedDateTime"].ToString();
                    result.lastUpdatedByUser   = (long)reader["lastUpdatedByUser"];
                    result.lastUpdatedDateTime = reader["lastUpdatedDateTime"].ToString();
                    result.categories          = reader["categories"] as int[];
                    result.name                = reader["name"].ToString();
                    result.desciption          = reader["desciption"].ToString();
                    result.organizationID      = (int)reader["organizationID"];
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    result.name = ex.StackTrace + ex.Message;
                    return(result);
                }
            }

            return(result);
        }
Example #52
0
        private T RunStoredFunction <T>(string procedure, Func <NpgsqlDataReader, T> mapColumns, List <Npgsql.NpgsqlParameter> paramsNpgsqlParameters = null)
        {
            if (paramsNpgsqlParameters == null)
            {
                paramsNpgsqlParameters = new List <NpgsqlParameter>();
            }

            using (var connection = new Npgsql.NpgsqlConnection(_databaseConfig.ConnectionString))
            {
                connection.Open();

                using (var command = new Npgsql.NpgsqlCommand(procedure, connection))
                {
                    command.CommandType = CommandType.StoredProcedure;
                    foreach (var parameter in paramsNpgsqlParameters)
                    {
                        command.Parameters.Add(parameter);
                    }

                    command.Prepare();
                    return(mapColumns(command.ExecuteReader()));
                }
            }
        }
Example #53
0
        //Método responsavel por retornar todos os filmes do diretor.
        public List <Filme> getFilmes(int cod_diretor)
        {
            bd.OpenConnection();
            List <Filme> filmes = new List <Filme>();

            String query = "SELECT * FROM tab_filme_diretor WHERE cod_diretor = :cod_diretor";

            Npgsql.NpgsqlCommand sql = new Npgsql.NpgsqlCommand(query, bd.getConnection);

            sql.Parameters.Add(new NpgsqlParameter("cod_diretor", NpgsqlTypes.NpgsqlDbType.Integer));
            sql.Prepare();
            sql.Parameters[0].Value = cod_diretor;

            NpgsqlDataReader dr = sql.ExecuteReader();

            while (dr.Read())
            {
                FilmeDAO fdao = new FilmeDAO();
                //Adiciona na lista de diretores que vai ser retornada, o diretor pelo metodo
                //getOne que recebe como parâmetro o ID retornado do banco e faz o retorno do Objeto.
                filmes.Add(fdao.getOne(dr.GetInt32(1)));
            }
            return(filmes);
        }
        private void FillOptimizedChangeLog(ref Npgsql.NpgsqlCommand command, ref List <OptimizedChangeLogElement> optimizedChangeLog, int startChangeId)
        {
            Logger.Info("FillOptimizedChangeLog START");
            try
            {
                OrderedDictionary tempOptimizedChangeLog = new OrderedDictionary();
                //Fill optimizedChangeLog
                using (NpgsqlDataReader dr = command.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string gmlId = dr.GetString(0);

                        // TODO: Fix dirty implementation later - 20121006-Leg: Uppercase First Letter
                        //gmlId = char.ToUpper(gmlId[0]) + gmlId.Substring(1);

                        string transType   = dr.GetString(1);
                        long   changelogId = dr.GetInt64(2);


                        OptimizedChangeLogElement optimizedChangeLogElement;
                        if (transType.Equals("D"))
                        {
                            //Remove if inserted or updated earlier in this sequence of transactions
                            if (tempOptimizedChangeLog.Contains(gmlId))
                            {
                                optimizedChangeLogElement = (OptimizedChangeLogElement)tempOptimizedChangeLog[gmlId];
                                string tempTransType = optimizedChangeLogElement.TransType;
                                tempOptimizedChangeLog.Remove(gmlId);
                                if (tempTransType.Equals("U"))
                                {
                                    //Add delete if last operation was update.
                                    tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
                                }
                            }
                            else
                            {
                                tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
                            }
                        }
                        else
                        {
                            if (!tempOptimizedChangeLog.Contains(gmlId))
                            {
                                tempOptimizedChangeLog.Add(gmlId, new OptimizedChangeLogElement(gmlId, transType, changelogId));
                            }
                        }
                    }
                }

                //Fill optimizedChangeLog
                foreach (var item in tempOptimizedChangeLog.Values)
                {
                    optimizedChangeLog.Add((OptimizedChangeLogElement)item);
                }
            }
            catch (System.Exception exp)
            {
                Logger.ErrorException("FillOptimizedChangeLog function failed:", exp);
                throw new System.Exception("FillOptimizedChangeLog function failed", exp);
            }
            Logger.Info("FillOptimizedChangeLog END");
        }
Example #55
0
        /// <summary>
        /// 执行存储过程或Sql语句返回DataTable
        /// </summary>
        /// <param name="sql">存储过程名称或Sql语句</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="paras">参数</param>
        /// <returns></returns>
        public DataTable Run(string sql, CommandType commandType, params object[] paras)
        {
            DataTable table = new DataTable();

            switch (this.DBType)
            {
            case DBTypeEnum.SqlServer:
                SqlConnection  con     = this.Database.GetDbConnection() as SqlConnection;
                SqlDataAdapter adapter = new SqlDataAdapter();
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    adapter.SelectCommand = cmd;
                    cmd.CommandTimeout    = 2400;
                    cmd.CommandType       = commandType;
                    if (paras != null)
                    {
                        foreach (var param in paras)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    adapter.Fill(table);
                    adapter.SelectCommand.Parameters.Clear();
                }
                break;

            case DBTypeEnum.MySql:
                MySqlConnection mySqlCon = this.Database.GetDbConnection() as MySqlConnection;
                using (MySqlCommand cmd = new MySqlCommand(sql, mySqlCon))
                {
                    if (mySqlCon.State == ConnectionState.Closed)
                    {
                        mySqlCon.Open();
                    }
                    cmd.CommandTimeout = 2400;
                    cmd.CommandType    = commandType;
                    if (paras != null)
                    {
                        foreach (var param in paras)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    MySqlDataReader dr = cmd.ExecuteReader();
                    table.Load(dr);
                    dr.Close();
                    mySqlCon.Close();
                }
                break;

            case DBTypeEnum.PgSql:
                Npgsql.NpgsqlConnection npgCon = this.Database.GetDbConnection() as Npgsql.NpgsqlConnection;
                using (Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sql, npgCon))
                {
                    if (npgCon.State == ConnectionState.Closed)
                    {
                        npgCon.Open();
                    }
                    cmd.CommandTimeout = 2400;
                    cmd.CommandType    = commandType;
                    if (paras != null)
                    {
                        foreach (var param in paras)
                        {
                            cmd.Parameters.Add(param);
                        }
                    }
                    Npgsql.NpgsqlDataReader dr = cmd.ExecuteReader();
                    table.Load(dr);
                    dr.Close();
                    npgCon.Close();
                }
                break;

            case DBTypeEnum.SQLite:
            case DBTypeEnum.Oracle:
                var connection = this.Database.GetDbConnection();
                var isClosed   = connection.State == ConnectionState.Closed;
                if (isClosed)
                {
                    connection.Open();
                }
                using (var command = connection.CreateCommand())
                {
                    command.CommandText    = sql;
                    command.CommandTimeout = 2400;
                    command.CommandType    = commandType;
                    if (paras != null)
                    {
                        foreach (var param in paras)
                        {
                            command.Parameters.Add(param);
                        }
                    }
                    using (var reader = command.ExecuteReader())
                    {
                        table.Load(reader);
                    }
                }
                if (isClosed)
                {
                    connection.Close();
                }
                break;
            }
            return(table);
        }
Example #56
0
        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="dataTable">The data table to return containing the data.</param>
        /// <param name="queryText">The query text to execute.</param>
        /// <param name="commandType">The command type.</param>
        /// <param name="connectionString">The connection string to use.</param>
        /// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
        /// returning data from the database for a particilar table.</param>
        /// <param name="values">The collection of sql parameters to include.</param>
        /// <returns>The sql command containing any return values.</returns>
        public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType,
                                      string connectionString, bool getSchemaTable, params DbParameter[] values)
        {
            // Initial connection objects.
            DbCommand dbCommand = null;

            PostgreSqlClient.NpgsqlConnection pgConnection = null;
            IDataReader dataReader = null;

            try
            {
                // Create a new connection.
                using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
                {
                    // Open the connection.
                    pgConnection.Open();

                    // Create the command and assign any parameters.
                    dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
                                                                       ConnectionContext.ConnectionDataType.PostgreSqlDataType, queryText), pgConnection);
                    dbCommand.CommandType = commandType;

                    if (values != null)
                    {
                        foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
                        {
                            dbCommand.Parameters.Add(sqlParameter);
                        }
                    }

                    // Load the data into the table.
                    using (dataReader = dbCommand.ExecuteReader())
                    {
                        // Get the schema from the data because the
                        // table has not predefined schema
                        if (getSchemaTable)
                        {
                            // Load the table after the schema is
                            // returned.
                            dataTable = dataReader.GetSchemaTable();
                            dataTable = new DataTable();
                            System.Data.DataSet localDataSet = new System.Data.DataSet();
                            localDataSet.EnforceConstraints = false;
                            localDataSet.Tables.Add(dataTable);
                            dataTable.Load(dataReader);
                        }
                        else
                        {
                            // Load the data into a table schema.
                            // Load the data into a table schema.
                            System.Data.DataSet localDataSet = new System.Data.DataSet();
                            localDataSet.EnforceConstraints = false;
                            localDataSet.Tables.Add(dataTable);
                            dataTable.Load(dataReader);
                        }

                        dataReader.Close();
                    }

                    // Close the database connection.
                    pgConnection.Close();
                }

                // Return the sql command, including
                // any parameters that have been
                // marked as output direction.
                return(dbCommand);
            }
            catch (Exception ex)
            {
                // Throw a general exception.
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }

                if (pgConnection != null)
                {
                    pgConnection.Close();
                }
            }
        }
Example #57
0
        /// <summary>
        /// Executes the query.
        /// </summary>
        /// <param name="dataSet">The data set to return containing the data.</param>
        /// <param name="tables">The datatable schema to add.</param>
        /// <param name="queryText">The query text to execute.</param>
        /// <param name="commandType">The command type.</param>
        /// <param name="connectionString">The connection string to use.</param>
        /// <param name="values">The collection of sql parameters to include.</param>
        /// <returns>The sql command containing any return values.</returns>
        public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText,
                                      CommandType commandType, string connectionString, params DbParameter[] values)
        {
            // Initial connection objects.
            DbCommand dbCommand = null;

            PostgreSqlClient.NpgsqlConnection pgConnection = null;
            IDataReader dataReader = null;

            try
            {
                // Create a new connection.
                using (pgConnection = new PostgreSqlClient.NpgsqlConnection(connectionString))
                {
                    // Open the connection.
                    pgConnection.Open();

                    // Create the command and assign any parameters.
                    dbCommand = new PostgreSqlClient.NpgsqlCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
                                                                       ConnectionContext.ConnectionDataType.PostgreSqlDataType, queryText), pgConnection);
                    dbCommand.CommandType = commandType;

                    if (values != null)
                    {
                        foreach (PostgreSqlClient.NpgsqlParameter sqlParameter in values)
                        {
                            dbCommand.Parameters.Add(sqlParameter);
                        }
                    }

                    // Load the data into the table.
                    using (dataReader = dbCommand.ExecuteReader())
                    {
                        dataSet = new System.Data.DataSet();
                        dataSet.Tables.AddRange(tables);
                        dataSet.EnforceConstraints = false;
                        dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
                        dataReader.Close();
                    }

                    // Close the database connection.
                    pgConnection.Close();
                }

                // Return the sql command, including
                // any parameters that have been
                // marked as output direction.
                return(dbCommand);
            }
            catch (Exception ex)
            {
                // Throw a general exception.
                throw new Exception(ex.Message, ex.InnerException);
            }
            finally
            {
                if (dataReader != null)
                {
                    dataReader.Close();
                }

                if (pgConnection != null)
                {
                    pgConnection.Close();
                }
            }
        }
Example #58
0
        public bool GetByPrimaryKey(string pKey)
        {
            string sQuery = "select * from tbm_carrepair WHERE repairid='" + pKey + "'";

            Npgsql.NpgsqlCommand cmd = new Npgsql.NpgsqlCommand(sQuery, Koneksi);
            cmd.CommandText = sQuery;
            Npgsql.NpgsqlDataReader rdr = cmd.ExecuteReader();
            try
            {
                if (rdr.Read())
                {
                    if (!rdr.IsDBNull(rdr.GetOrdinal("repairid")))
                    {
                        m_repairid = rdr.GetString(rdr.GetOrdinal("repairid"));
                    }
                    else
                    {
                        m_repairid = "";
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("carid")))
                    {
                        m_carid = rdr.GetString(rdr.GetOrdinal("carid"));
                    }
                    else
                    {
                        m_carid = "";
                    };

                    if (!rdr.IsDBNull(rdr.GetOrdinal("opadd")))
                    {
                        m_opadd = rdr.GetString(rdr.GetOrdinal("opadd"));
                    }
                    else
                    {
                        m_opadd = "";
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("pcadd")))
                    {
                        m_pcadd = rdr.GetString(rdr.GetOrdinal("pcadd"));
                    }
                    else
                    {
                        m_pcadd = "";
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("luadd")))
                    {
                        m_luadd = rdr.GetDateTime(rdr.GetOrdinal("luadd"));
                    }
                    else
                    {
                        m_luadd = System.DateTime.MinValue;
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("opedit")))
                    {
                        m_opedit = rdr.GetString(rdr.GetOrdinal("opedit"));
                    }
                    else
                    {
                        m_opedit = "";
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("pcedit")))
                    {
                        m_pcedit = rdr.GetString(rdr.GetOrdinal("pcedit"));
                    }
                    else
                    {
                        m_pcedit = "";
                    };
                    if (!rdr.IsDBNull(rdr.GetOrdinal("luedit")))
                    {
                        m_luedit = rdr.GetDateTime(rdr.GetOrdinal("luedit"));
                    }
                    else
                    {
                        m_luedit = System.DateTime.MinValue;
                    };
                    m_dlt = rdr.GetBoolean(rdr.GetOrdinal("dlt"));
                }
                return(true);
            }
            catch (Npgsql.NpgsqlException Ex)
            {
                System.Windows.Forms.MessageBox.Show(Ex.Message, "An error occurred while processing!!!");
                return(false);
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }
            }
        }
Example #59
0
        ///<summary>
        ///
        /// This method is reponsible to derive the command parameter list with values obtained from function definition.
        /// It clears the Parameters collection of command. Also, if there is any parameter type which is not supported by Npgsql, an InvalidOperationException will be thrown.
        /// Parameters name will be parameter1, parameter2, ...
        /// For while, only parameter name and NpgsqlDbType are obtained.
        ///</summary>
        /// <param name="command">NpgsqlCommand whose function parameters will be obtained.</param>
        public static void DeriveParameters(NpgsqlCommand command)
        {
            // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
            // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
            String query         = null;
            string procedureName = null;
            string schemaName    = null;

            string[] fullName = command.CommandText.Split('.');
            if (fullName.Length > 1 && fullName[0].Length > 0)
            {
                query =
                    "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
                schemaName    = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
                procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
            }
            else
            {
                query         = "select proargnames, proargtypes from pg_proc where proname = :proname";
                procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
            }

            using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
            {
                c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
                c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
                if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
                {
                    NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
                    prm.Value = schemaName.Replace("\"", "").Trim();
                }

                String[] names = null;
                String[] types = null;

                using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                        {
                            names = rdr.GetValue(0) as String[];
                        }
                        if (!rdr.IsDBNull(1))
                        {
                            types = rdr.GetString(1).Split();
                        }
                    }
                }

                if (types == null)
                {
                    throw new InvalidOperationException(
                              String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
                }

                command.Parameters.Clear();
                for (Int32 i = 0; i < types.Length; i++)
                {
                    // skip parameter if type string is empty
                    // empty parameter lists can cause this
                    if (!string.IsNullOrEmpty(types[i]))
                    {
                        NpgsqlBackendTypeInfo typeInfo = null;
                        if (!c.Connector.OidToNameMapping.TryGetValue(int.Parse(types[i]), out typeInfo))
                        {
                            command.Parameters.Clear();
                            throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
                        }
                        if (names != null && i < names.Length)
                        {
                            command.Parameters.Add(new NpgsqlParameter(":" + names[i], typeInfo.NpgsqlDbType));
                        }
                        else
                        {
                            command.Parameters.Add(new NpgsqlParameter("parameter" + (i + 1).ToString(), typeInfo.NpgsqlDbType));
                        }
                    }
                }
            }
        }
Example #60
0
        private static void DoDeriveParameters(NpgsqlCommand command)
        {
            // See http://www.postgresql.org/docs/9.3/static/catalog-pg-proc.html
            command.Parameters.Clear();
            // Updated after 0.99.3 to support the optional existence of a name qualifying schema and case insensitivity when the schema ror procedure name do not contain a quote.
            // This fixed an incompatibility with NpgsqlCommand.CheckFunctionReturn(String ReturnType)
            var    serverVersion = command.Connector.ServerVersion;
            String query         = null;
            string procedureName = null;
            string schemaName    = null;

            string[] fullName = command.CommandText.Split('.');
            if (fullName.Length > 1 && fullName[0].Length > 0)
            {
                // proargsmodes is supported for Postgresql 8.1 and above
                if (serverVersion >= new Version(8, 1, 0))
                {
                    query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
                }
                else
                {
                    query = "select proargnames, proargtypes from pg_proc p left join pg_namespace n on p.pronamespace = n.oid where proname=:proname and n.nspname=:nspname";
                }
                schemaName    = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
                procedureName = (fullName[1].IndexOf("\"") != -1) ? fullName[1] : fullName[1].ToLower();
            }
            else
            {
                // proargsmodes is supported for Postgresql 8.1 and above
                if (serverVersion >= new Version(8, 1, 0))
                {
                    query = "select proargnames, proargtypes, proallargtypes, proargmodes from pg_proc where proname = :proname";
                }
                else
                {
                    query = "select proargnames, proargtypes from pg_proc where proname = :proname";
                }
                procedureName = (fullName[0].IndexOf("\"") != -1) ? fullName[0] : fullName[0].ToLower();
            }

            using (NpgsqlCommand c = new NpgsqlCommand(query, command.Connection))
            {
                c.Parameters.Add(new NpgsqlParameter("proname", NpgsqlDbType.Text));
                c.Parameters[0].Value = procedureName.Replace("\"", "").Trim();
                if (fullName.Length > 1 && !String.IsNullOrEmpty(schemaName))
                {
                    NpgsqlParameter prm = c.Parameters.Add(new NpgsqlParameter("nspname", NpgsqlDbType.Text));
                    prm.Value = schemaName.Replace("\"", "").Trim();
                }

                string[] names = null;
                int[]    types = null;
                string[] modes = null;

                using (NpgsqlDataReader rdr = c.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
                {
                    if (rdr.Read())
                    {
                        if (!rdr.IsDBNull(0))
                        {
                            names = rdr.GetValue(0) as String[];
                        }
                        if (serverVersion >= new Version("8.1.0"))
                        {
                            if (!rdr.IsDBNull(2))
                            {
                                types = rdr.GetValue(2) as int[];
                            }
                            if (!rdr.IsDBNull(3))
                            {
                                modes = rdr.GetValue(3) as String[];
                            }
                        }
                        if (types == null)
                        {
                            if (rdr.IsDBNull(1) || rdr.GetString(1) == "")
                            {
                                return;  // Parameterless function
                            }
                            types = rdr.GetString(1).Split().Select(int.Parse).ToArray();
                        }
                    }
                    else
                    {
                        throw new InvalidOperationException(String.Format(resman.GetString("Exception_InvalidFunctionName"), command.CommandText));
                    }
                }

                command.Parameters.Clear();
                for (var i = 0; i < types.Length; i++)
                {
                    var param = new NpgsqlParameter();
                    NpgsqlBackendTypeInfo typeInfo = null;
                    if (!c.Connector.OidToNameMapping.TryGetValue(types[i], out typeInfo))
                    {
                        throw new InvalidOperationException(String.Format("Invalid parameter type: {0}", types[i]));
                    }
                    param.NpgsqlDbType = typeInfo.NpgsqlDbType;

                    if (names != null && i < names.Length)
                    {
                        param.ParameterName = ":" + names[i];
                    }
                    else
                    {
                        param.ParameterName = "parameter" + (i + 1);
                    }

                    if (modes == null) // All params are IN, or server < 8.1.0 (and only IN is supported)
                    {
                        param.Direction = ParameterDirection.Input;
                    }
                    else
                    {
                        switch (modes[i])
                        {
                        case "i":
                            param.Direction = ParameterDirection.Input;
                            break;

                        case "o":
                            param.Direction = ParameterDirection.Output;
                            break;

                        case "b":
                            param.Direction = ParameterDirection.InputOutput;
                            break;

                        case "v":
                            throw new NotImplementedException("Cannot derive function parameter of type VARIADIC");

                        case "t":
                            throw new NotImplementedException("Cannot derive function parameter of type TABLE");

                        default:
                            throw new ArgumentOutOfRangeException("proargmode", modes[i],
                                                                  "Unknown code in proargmodes while deriving: " + modes[i]);
                        }
                    }

                    command.Parameters.Add(param);
                }
            }
        }