C# NpgsqlCommand.ExecuteReader方法代码示例

 public static List<GrilleRabais> getListGrilleRistourne(String query)
 {
     NpgsqlConnection con = Connexion.Connection();
     try
     {
         List<GrilleRabais> l = new List<GrilleRabais>();
         NpgsqlCommand Lcmd = new NpgsqlCommand(query, con);
         NpgsqlDataReader lect = Lcmd.ExecuteReader();
         if (lect.HasRows)
         {
             while (lect.Read())
             {
                 GrilleRabais a = new GrilleRabais();
                 a.Id = Convert.ToInt64(lect["id"].ToString());
                 a.Nature = lect["nature_montant"].ToString();
                 a.Minimal = (Double)((lect["montant_minimal"] != null) ? ((!lect["montant_minimal"].ToString().Trim().Equals("")) ? lect["montant_minimal"] : 0) : 0);
                 a.Maximal = (Double)((lect["montant_maximal"] != null) ? ((!lect["montant_maximal"].ToString().Trim().Equals("")) ? lect["montant_maximal"] : 0) : 0);
                 a.Montant = (Double)((lect["montant_ristourne"] != null) ? ((!lect["montant_ristourne"].ToString().Trim().Equals("")) ? lect["montant_ristourne"] : 0) : 0);
                 a.Update = true;
                 l.Add(a);
             }
             lect.Close();
         }
         return l;
     }
     catch (NpgsqlException e)
     {
         Messages.Exception(e);
         return null;
     }
     finally
     {
         Connexion.Deconnection(con);
     }
 }

        public void Long([Values(CommandBehavior.Default, CommandBehavior.SequentialAccess)] CommandBehavior behavior)
        {
            var builder = new StringBuilder("ABCDEééé", Conn.BufferSize);
            builder.Append('X', Conn.BufferSize);
            var expected = builder.ToString();
            ExecuteNonQuery("CREATE TEMP TABLE data (name TEXT)");
            var cmd = new NpgsqlCommand(@"INSERT INTO data (name) VALUES (@p)", Conn);
            cmd.Parameters.Add(new NpgsqlParameter("p", expected));
            cmd.ExecuteNonQuery();

            const string queryText = @"SELECT name, 'foo', name, name, name, name FROM data";
            cmd = new NpgsqlCommand(queryText, Conn);
            var reader = cmd.ExecuteReader(behavior);
            reader.Read();

            var actual = reader[0];
            Assert.That(actual, Is.EqualTo(expected));

            if (IsSequential(behavior))
                Assert.That(() => reader[0], Throws.Exception.TypeOf<InvalidOperationException>(), "Seek back sequential");
            else
                Assert.That(reader[0], Is.EqualTo(expected));

            Assert.That(reader.GetString(1), Is.EqualTo("foo"));
            Assert.That(reader.GetFieldValue<string>(2), Is.EqualTo(expected));
            Assert.That(reader.GetValue(3), Is.EqualTo(expected));
            Assert.That(reader.GetFieldValue<string>(4), Is.EqualTo(expected));
            Assert.That(reader.GetFieldValue<char[]>(5), Is.EqualTo(expected.ToCharArray()));
        }

        public 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();
                }
            }
        }

        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 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);
            }
        }

 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);
     }
 }

        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;
        }

        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();
        }

 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;
 }

        private static void Run()
        {
            var connStr = ConfigurationManager.AppSettings["ConnectionString"];
            using(NpgsqlConnection conn = new NpgsqlConnection(connStr))
            using(NpgsqlConnection updateConn = new NpgsqlConnection(connStr))
            {
                conn.Open();
                updateConn.Open();

                // Define a query returning a single row result set
                using (NpgsqlCommand command = new NpgsqlCommand("select id, file_name, fk_file_id from source_documents where thumbnail_created = false;", conn))
                {
                    using (NpgsqlDataReader dr = command.ExecuteReader())
                    {
                        // Output rows
                        while (dr.Read())
                        {
                            CreateThumbnail((string)dr[1], (long)dr[2]);
                            using (NpgsqlCommand update = new NpgsqlCommand("update source_documents set thumbnail_created = true where id = " + dr[0], updateConn))
                            {
                                update.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
        }

 public 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 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");
            }
        }

 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 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";
        }

        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;
            }
        }