C# NpgsqlDataAdapter.Fill方法代码示例

        public DataTable ExecuteSelectQuery(NpgsqlCommand command, NpgsqlConnection conn)
        {
            try
            {
                if (conn == null)
                {
                    return null;
                }

                DataTable dataTable = new DataTable();
                DataSet dataSet = new DataSet();
                NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter(command);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dataTable = dataSet.Tables[0];
                conn.Close();

                return dataTable.Rows.Count <= 0 ? null : dataTable;
            }
            catch (NpgsqlException ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
        }

    protected void OnExecuteActionActivated(object sender, System.EventArgs e)
    {
        string connectionString = "Server=localhost;Database=aula;User Id=aula;Password=clase" ;

        IDbConnection dbConnection = new NpgsqlConnection(connectionString);
        IDbCommand selectCommand = dbConnection.CreateCommand();
        selectCommand.CommandText = "select * from articulo where id=4";
        IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataSet = new DataSet();

        dbDataAdapter.Fill (dataSet);

        Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);
        foreach (DataTable dataTable in dataSet.Tables)
            show (dataTable);

        DataRow dataRow = dataSet.Tables[0].Rows[0];
        dataRow["nombre"] = DateTime.Now.ToString ();
        Console.WriteLine("Tabla con los cambios");
        show (dataSet.Tables[0]);

        dbDataAdapter.Update (dataSet);
    }

        private List<string> getFreeDoctors()
        {
            DTDoctors = new DataTable();
            string gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors where special < 4";
            fDoc = new List<string>();
            if (this.comboBox1.Items.Count > 0)
            {
                int _trial = (int)DTuserTrials.Rows[this.comboBox1.SelectedIndex]["tr_id"];
                gfd = "Select doc_id, trim(family_name) as family_name, trim(first_name) as first_name from doctors ds where ds.special < 4 and ds.doc_id not in "
                +"(select doc_id from trial_role where trial_id = '"+_trial+"')";
            }

            NpgsqlDataAdapter getfd = new NpgsqlDataAdapter(gfd,DBExchange.Inst.connectDb);
            try
            {
                getfd.Fill(DTDoctors);

            }
            catch (Exception exception)
            {
                System.Windows.Forms.MessageBox.Show(exception.Message.ToString() + " getFreeDoctors");
            }

            foreach (DataRow ro in DTDoctors.Rows)
            {
                fDoc.Add(ro["family_name"] + " " + ro["first_name"]);

            }

            return fDoc;
        }

        public static User GetChatUser(string pEmail, string pPassword)
        {
            if (ConnectToDatabase())
            {
                try
                {
                    string query = "SELECT * FROM \"User\" WHERE \"Mail\"=\'" + pEmail + "\' AND \"Password\"=\'" + pPassword + "\'";

                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection);
                    ds.Reset();
                    da.Fill(ds);
                    dt = ds.Tables[0];
                    User u = new User();
                    DataRow row = dt.Rows[0];
                    u.UserID = row.Field<int>(dt.Columns[0]);
                    u.Email = row.Field<string>(dt.Columns[1]);
                    u.Username = row.Field<string>(dt.Columns[3]);
                    u.IsAdmin = row.Field<bool>(dt.Columns[5]);
                    //u.LastOnline = row.Field<DateTime>(dt.Columns[4]);                    
                    return u;
                }
                catch (Exception ex)
                {
                    LoggingService.LogToFile(ex);
                    return null;
                }
                finally
                {
                    DisconnectFromDatabase();
                }               
            }
            return null;
        }

        public static User[] GetChatUsers()
        {
            if (ConnectToDatabase())
            {
                string query = "SELECT \"Mail\", \"Username\", \"LastLogin\", \"IsAdmin\", \"ID\" FROM User";

                NpgsqlDataAdapter da = new NpgsqlDataAdapter(query, mDatabaseConnection);
                ds.Reset();
                da.Fill(ds);
                dt = ds.Tables[0];
                User[] users = new User[dt.Rows.Count];
                int count = 0;
                foreach (DataRow row in dt.Rows)
                {
                    User u = new User();
                    u.UserID = row.Field<int>(dt.Columns[4]);
                    u.Email = row.Field<string>(dt.Columns[0]);
                    u.Username = row.Field<string>(dt.Columns[1]);
                    u.IsAdmin = row.Field<bool>(dt.Columns[3]);
                    u.LastOnline = row.Field<DateTime>(dt.Columns[2]);
                    users[count] = u;
                    count++;
                }
                DisconnectFromDatabase();
                return users;


            }
            return null;
        }

        private void pay_Load(object sender, EventArgs e)
        {
            NpgsqlCommand acc_info = new NpgsqlCommand("SELECT DISTINCT "
                               + " places_cnt1.name, "
                               + "places_cnt2.name, "
                               + "public.acc_info.flat "
                             + "FROM "
                               + "public.places_cnt places_cnt2 "
                               + "INNER JOIN public.places_cnt places_cnt1 ON (places_cnt2.parent_id = places_cnt1.plc_id) "
                               + "INNER JOIN public.places_cnt ON (places_cnt1.parent_id = public.places_cnt.plc_id) "
                               + "INNER JOIN public.acc_info ON (places_cnt2.plc_id = public.acc_info.plc_id) "
                             + "WHERE "
                              + " public.acc_info.acc_id = " + all_user.acc_code + "", conn);
            conn.Open();
            NpgsqlDataReader reader = acc_info.ExecuteReader();
            while (reader.Read())
            {
                this.Text = "Оплаты по ЛС: " + all_user.acc_code + " Адрес:  " + reader[0].ToString() + " " + reader[1].ToString() + " " + reader[2].ToString();
            }
            conn.Close();

            DataTable dt = new DataTable();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter("SELECT * FROM public.pay WHERE acc_id = " + all_user.acc_code + " ORDER BY date_pay", conn);

            conn.Open();
            da.Fill(dt);
            conn.Close();

            dataGridView1.DataSource = dt;
        }

        public DataTable[] ExecuteDataset(CommandType commandType, string query, NpgsqlParameter[] npgsqlParameters)
        {
            using (NpgsqlCommand command = GetCommand(query, npgsqlParameters, commandType))
            {
                try
                {
                    DataSet myDS = new DataSet();

                    NpgsqlTransaction t = command.Connection.BeginTransaction();

                    NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
                    da.Fill(myDS);

                    t.Commit();

                    DataTable[] tables = new DataTable[myDS.Tables.Count];

                    myDS.Tables.CopyTo(tables, 0);

                    return tables;

                }
                catch (Exception Ex)
                {
                    throw Ex;
                }

                finally
                {
                    command.Connection.Close();
                }
            }
        }

    protected void OnExecuteActionActivated(Object sender, System.EventArgs e)
    {
        String connectionString ="Server=localhost;Database=dbprueba;User Id=dbprueba;Password=1234";
        NpgsqlCommand selectCommand = dbConnection.CreateCommand ();
        selectCommand.CommandText = "select * from categoria";
        NpgsqlDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
        new NpgsqlCommandBuilder(dbDataAdapter);

            dbDataAdapter.SelectCommand = selectCommand;

        DataSet dataset = new Dataset();
        dbDataAdapter.Fill (DataSet);
        //Console.WriteLine("Tables.Count={0}", dataSet.Tables.Count);

        foreach(DataTable dataTable in dataset.Tables)
            show (dataTable);

        DataRow dataRow = dataset.Tables[0].Rows[0];
        dataRow["Nombre"] = DateTime.Now.ToString();

        Console.WriteLine("Cambios");
        show (dataset.Tables[0]);

        dbDataAdapter.Update(dataset);
    }

        protected void Page_Load(object sender, EventArgs e)
        {
            string sQuery = "SELECT title, vote_average from movie order by vote_average DESC, release_date ASC LIMIT 20";
            string sConn = String.Format("Server=ec2-54-217-202-110.eu-west-1.compute.amazonaws.com;Port=5432;User Id=iwzexazhfjxbbt;Password=4JVMJFooosyfdM5Y79Si-c691D;Database=d8u6uelvine6d6;Protocol=3;SSL=true;SslMode=Require;");

            NpgsqlConnection Conn = new NpgsqlConnection(sConn);

            Conn.Open();

            DataSet DS = new DataSet("DS1");
            NpgsqlDataAdapter DA = new NpgsqlDataAdapter();

            DA.SelectCommand = new NpgsqlCommand(sQuery, Conn);

            DA.Fill(DS, "DS1");
            DS.Tables[0].Columns.Add("Linki", typeof(string));
            foreach (DataRow item in DS.Tables[0].Rows)
            {
                string link="http://www.omdbapi.com/?t=" + item[0].ToString();
                //string link=String.Format("<a href=\"http://www.omdbapi.com/?t={0}\">Opis</a>", item[0].ToString());
                item[2] = link;
            }

            GridView1.DataSource = DS;
            GridView1.DataBind();

            Conn.Close();
        }

        public static void getWords()
        {
            #region Npgsql
            NpgsqlConnection conn;
            conn = new NpgsqlConnection("Server=" + Settings.DBSrvIP + ";Port=" + Settings.DBSrvPort + ";User Id=" +
                Settings.DBconnectID + ";Password=" + Settings.DBconnectPw + ";Database=endoDB;" + Settings.sslSetting);

            try
            {
                conn.Open();
            }
            catch (NpgsqlException)
            {
                MessageBox.Show(Properties.Resources.CouldntOpenConn, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
            catch (System.IO.IOException)
            {
                MessageBox.Show(Properties.Resources.ConnClosed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                conn.Close();
            }
            #endregion

            string sql = "SELECT no, words1, words2, words3, operator, word_order FROM words ORDER BY word_order";

            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            da.Fill(localDB, "words");
            if (localDB.Tables["words"].Rows.Count == 0)
            { MessageBox.Show("[words]" + Properties.Resources.NoRecord, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); }
            conn.Close();
        }

        public static DataTable GetTable(string catalog, string sql, Collection<ReportParameter> parameters)
        {
            using (NpgsqlConnection connection = new NpgsqlConnection(GetConnectionString(catalog)))
            {
                using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
                {
                    foreach (ReportParameter parameter in parameters)
                    {
                        if (parameter != null && !string.IsNullOrWhiteSpace(parameter.Name))
                        {
                            command.Parameters.AddWithValue(parameter.Name, parameter.TestValue);
                        }
                    }

                    using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(command))
                    {
                        using (DataTable dataTable = new DataTable())
                        {
                            dataTable.Locale = Thread.CurrentThread.CurrentCulture;
                            dataTable.TableName = "Report";
                            adapter.Fill(dataTable);
                            return dataTable;
                        }
                    }
                }
            }
        }

        public void update_table_period()
        {
            NpgsqlDataAdapter sql_table = new NpgsqlDataAdapter("SELECT * FROM period ORDER BY id DESC ", conn);
            DataTable table = new DataTable();
            conn.Open();
            sql_table.Fill(table);
            dataGridView1.DataSource = table;

            conn.Close();
            DateTime commentDate = table.Rows[0].Field<DateTime>("date_cnt");
            textBox1.Text = commentDate.ToString("dd.MM.yyyy");
            textBox2.Text = table.Rows[0].Field<string>("name");

            dataGridView1.Columns[0].Width = 60;
            dataGridView1.Columns[0].HeaderText = "id";

            dataGridView1.Columns[1].Width = 170;
            dataGridView1.Columns[1].HeaderText = "Название периода";

            dataGridView1.Columns[2].Width = 100;
            dataGridView1.Columns[2].HeaderText = "Начальная дата";

            dataGridView1.Columns[3].Width = 100;
            dataGridView1.Columns[3].HeaderText = "Конечная дата";

            dataGridView1.Columns[4].Width = 100;
            dataGridView1.Columns[4].HeaderText = "Дата периода";

            dataGridView1.Columns[5].Width = 100;
            dataGridView1.Columns[5].HeaderText = "Доп.";
        }

        //Йнкции пользователя
        /// <summary>
        /// добавление\удаление пользователя
        /// добавление записи
        /// удаление своей незакрытой записи
        /// проверка
        /// изменение прав пользователя
        /// 
        /// удаление закрытой записи
        /// редактирование своей записи
        /// редактирование чужой записи
        /// 
        /// </summary>
        public RegisterForm()
        {
            InitializeComponent();
            divList = new List<string>();
            staList = new List<string>();
            speList = new List<string>();
            NpgsqlConnection cdbo = DBExchange.Inst.connectDb;
            NpgsqlDataAdapter div = new NpgsqlDataAdapter("Select trim(name) from divisions order by division ASC", cdbo);
            DataTable divTable = new DataTable();
            div.Fill(divTable);
            foreach (DataRow roww in divTable.Rows )
            {
                divList.Add((string)roww[0]);
            }
            otdel.DataSource = divList;
            otdel.SelectedIndex = 1;
            NpgsqlDataAdapter sta = new NpgsqlDataAdapter("Select trim(name) from posts order by post ASC", cdbo);
            DataTable staTable = new DataTable();
            sta.Fill(staTable);
            foreach (DataRow roww in staTable.Rows)
            {
                staList.Add((string)roww[0]);
            }
            status.DataSource = staList;

            status.SelectedIndex = 39;
            //special.SelectedIndex = 0;
        }

        /// <summary>
        /// Method gets both person table and testoccasion table.
        /// </summary>
        /// <returns>Returns datatable with one row holding all info regarding a user and what test he or she have done</returns>
        public DataTable GetPersonAndTestInfo(string userName)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Database=kompetensportal;Server=localhost;User Id=postgres;Password=anna;");
            try
            {
                conn.Open();
                NpgsqlCommand cmdGetUserInfo = new NpgsqlCommand("SELECT * FROM person " +
                                                                 "INNER JOIN testoccasion ON person.id = id_user " +
                                                                 "WHERE person.id = (SELECT id FROM person WHERE username = @userName) " +
                                                                 "ORDER BY date DESC LIMIT 1; ", conn);
                cmdGetUserInfo.Parameters.AddWithValue("@userName", userName);
                NpgsqlDataAdapter nda = new NpgsqlDataAdapter();
                nda.SelectCommand = cmdGetUserInfo;
                DataSet ds = new DataSet();
                nda.Fill(ds);
                DataTable dt = ds.Tables[0];

                return dt;
            }
            catch (NpgsqlException ex)
            {
                NpgsqlException = ex.Message;
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

		override internal void LoadAll()
		{
			try
			{
				string query =
					"SELECT CAST(current_database() AS character varying) AS table_catalog, " +
					"CAST(nc.nspname AS character varying) AS table_schema, " +
					"CAST(c.relname AS character varying) AS table_name, " +
					"CAST('BASE TABLE' AS character varying) AS table_type, d.description " +
					"FROM pg_namespace nc, pg_user u, pg_class c LEFT OUTER JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = 0  " +
					"WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner AND c.relkind= 'r'";

				if(!this.dbRoot.ShowSystemData)
				{
					query += " AND nc.nspname <> 'pg_catalog'";
				}

				query += " ORDER BY c.relname";

				NpgsqlConnection cn = ConnectionHelper.CreateConnection(this.dbRoot, this.Database.Name);

				DataTable metaData = new DataTable();
				NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(query, cn);

				adapter.Fill(metaData);
				cn.Close();
		
				PopulateArray(metaData);
			}
			catch {}
		}