C# OracleDataAdapter.Fill方法代码示例

        private void button1_Click(object sender, EventArgs e)
        {
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = "User ID=SYSTEM; Password=19; Data Source=PC";

            try
            {
                conn.Open();
                MessageBox.Show("Veritabanına Baglantı Basarılı.");
            }
            catch
            {
                MessageBox.Show("Veritabanına Baglanılamadı.");
            }

            OracleCommand cmd = new OracleCommand();
            cmd.CommandText = "SELECT ogrenci_tablosu.ogrenci_ad, fakulte_tablosu.fakulte_ad FROM ogrenci_tablosu, bolum_tablosu, fakulte_tablosu WHERE ogrenci_tablosu.ogrenci_bolum = bolum_tablosu.bolum_id AND bolum_tablosu.bolum_fakulte = fakulte_tablosu.fakulte_id";
            cmd.Connection = conn;

            OracleDataAdapter adapter = new OracleDataAdapter();
            adapter.SelectCommand = cmd;

            DataSet dataset = new DataSet();

            adapter.Fill(dataset);

            dataGridView1.DataSource = dataset.Tables[0];

            conn.Close();
        }

        public override bool ExecuteProcedure(string ProcedureName, List<System.Data.IDbDataParameter> parameters, System.Data.DataSet ds)
        {
            bool result = false;

            try
            {

                this.DBCommand.CommandType = CommandType.StoredProcedure;
                this.DBCommand.CommandText = ProcedureName;
                this.DBCommand.Connection = this.DBConnection;
                this.DBCommand.Parameters.Clear();

                foreach (IDataParameter parameter in parameters)
                {
                    this.DBCommand.Parameters.Add(parameter);
                }

                OracleParameter paramRefCursor = new OracleParameter();
                paramRefCursor.OracleDbType = OracleDbType.RefCursor;
                paramRefCursor.Direction = ParameterDirection.Output;
                paramRefCursor.ParameterName = PARAM_REF_CURSOR;
                this.DBCommand.Parameters.Add(paramRefCursor);

                OracleDataAdapter da = new OracleDataAdapter((OracleCommand)this.DBCommand);
                da.Fill(ds);
                result = true;

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }

        void stuffload()
        {
            try
            {
                conn.Open();
                string sqlquery = "SELECT EMP_ID,EMP_NAME,MOBILE,JOIN_DATE,POSITION,SALARY FROM EMPLOYE_INFO";
                //string sqlquery = "SELECT * FROM MED_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
                //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Mobile";
                dt.Columns[3].ColumnName = "Joining Date";
                dt.Columns[4].ColumnName = "Position";
                dt.Columns[4].ColumnName = "Salary";

                dt.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void changeChartView()
        {
            using (OracleConnection connection = new OracleConnection(FormLogin.connString))
            {
                connection.Open();
                String query = "SELECT m.idRound AS ROUND, ROUND(SUM(part.match_Rating)/COUNT(part.match_Rating)) AS TEAMRATING"
                    + " FROM participates part JOIN player p ON (part.idPlayer = p.idPlayer) JOIN match m ON (part.idMatch = m.idMatch)"
                    + " WHERE p.idTeam = " + Team.Id
                    + " GROUP BY m.idRound"
                    + " ORDER BY m.idRound";
                OracleDataAdapter adapter = new OracleDataAdapter(query, connection);
                DataTable table = new DataTable();
                adapter.Fill(table);

                chartTeamRating.DataSource = table;
                chartTeamRating.Series["Series1"].XValueMember = "Round";
                chartTeamRating.Series["Series1"].YValueMembers = "TeamRating";
            }

            chartTeamRating.ChartAreas["ChartArea1"].AxisX.Title = "Rounds";
            chartTeamRating.ChartAreas["ChartArea1"].AxisY.Title = "Rating";
            chartTeamRating.ChartAreas["ChartArea1"].AxisX.Minimum = 1;
            chartTeamRating.ChartAreas["ChartArea1"].AxisX.Maximum = 38;
            chartTeamRating.ChartAreas["ChartArea1"].AxisX.Interval = 1;
            chartTeamRating.ChartAreas["ChartArea1"].AxisY.Minimum = 1000;
            chartTeamRating.ChartAreas["ChartArea1"].AxisY.Maximum = 3000;
            chartTeamRating.ChartAreas["ChartArea1"].Visible = true;
            chartTeamRating.Series["Series1"].BorderWidth = 3;



        }

        void MgfLoad()
        {
            try
            {
                conn.Open();
              //  string sqlquery = "SELECT MED_ID,MED_NAME,MED_STG,MED_MGF,MED_BATCH,MED_GROUP,MED_TYPE,COST_PRICE,SELL_PRICE,NOTES FROM MED_INFO";
                string sqlquery = "SELECT * FROM MFG_INFO";
                OracleCommand cmd = new OracleCommand(sqlquery, conn);
                OracleDataAdapter oda = new OracleDataAdapter();
                oda.SelectCommand = cmd;
                dt = new DataTable();
                //     dt.Columns["MED_NAME"].ColumnName = "Name";

                oda.Fill(dt);
                BindingSource bsource = new BindingSource();
                bsource.DataSource = dt;
                dataGridView1.DataSource = bsource;
                oda.Update(dt);
                dt.Columns[0].ColumnName = "ID";
                dt.Columns[1].ColumnName = "Name";
                dt.Columns[2].ColumnName = "Address";
                dt.Columns[3].ColumnName = "Mobile";
                dt.AcceptChanges();

                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                string ab =  Session["B_c_package"].ToString();
                string cmdQuery = "Select * from Decuration_Package where dec_pack_service = '"+ ab +"' ";
                OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
                OracleCommandBuilder builder = new OracleCommandBuilder(a);
                DataSet ds = new DataSet();
                a.Fill(ds, "Decuration_Package");
                foreach (DataRow r in ds.Tables["Decuration_Package"].Rows)
                {
                    ListItem i = new ListItem();
                    i.Text = r["DEC_PACK_NAME"].ToString();
                    i.Value = r["DEC_PACK_ID"].ToString();
                    Session["Decuration_price"]= r["DEC_PACK_PRICE"].ToString();
                    DropDownList_decuration.Items.Add(i);
                }
                string cmdQuery2 = "Select * from FOOD_PACKAGE where f_pack_service = '" + ab + "' ";
                OracleDataAdapter b = new OracleDataAdapter(cmdQuery2, ConnectionString);
                OracleCommandBuilder builder2 = new OracleCommandBuilder(b);
                DataSet ds2 = new DataSet();
                b.Fill(ds2, "FOOD_PACKAGEes");
                foreach (DataRow r in ds2.Tables["FOOD_PACKAGEes"].Rows)
                {
                    ListItem i = new ListItem();
                    i.Text = r["f_pack_name"].ToString();
                    i.Value = r["f_pack_id"].ToString();
                    Session["FOOD_price"] = r["f_pack_price"].ToString();
                    DropDownList_food_package.Items.Add(i);
                }

            }
        }

        /// <summary>
        /// 将数据读取到 DataSet 中.
        /// </summary>
        public void ReadDataToDataSet()
        {

            Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");

            // 建立数据库连接.
            OracleConnection conn = new OracleConnection(connString);

            // 创建一个适配器
            OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);

            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();

            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");

            // 关闭数据库连接.
            conn.Close();

            // 处理DataSet中的每一行数据.
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                // 将检索出来的数据，输出到屏幕上.
                Console.WriteLine("NO:{0} ;  Date:{1} ; Money:{2}   ",
                    testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
                    );
            }
        }

        public static DataTable GetItensEstoque()
        {
            DataTable dt = new DataTable();
            OracleDataAdapter da = new OracleDataAdapter();

            OracleConnection conn = Conexao.GetInstance();

            OracleCommand cmd = new OracleCommand();
            try
            {
                cmd.CommandText = "SELECT * FROM ITEM_ESTOQUE ";

                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                strErrMsg = "Atenção, o sistema detectou o seguinte problema: " + "\r\n" +
                    "Descrição: " + Convert.ToString(ex.Message) + "\r\n" +
                    "Origem: " + Convert.ToString(ex.Source);
                MessageBox.Show(strErrMsg, "Procedimento: " + Convert.ToString(ex.TargetSite),
                      MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }

        private void LoginRandomEmployee()
        {
            DataTable employeeTable = new DataTable();
            Person employee = null;

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                OracleCommand objCmd = new OracleCommand("TICKETS_QUERIES.getRandomEmployee", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };

                objCmd.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

                try
                {
                    objConn.Open();
                    OracleDataAdapter adapter = new OracleDataAdapter(objCmd);
                    adapter.Fill(employeeTable);
                    employee = Mapper.DynamicMap<IDataReader, List<Person>>(employeeTable.CreateDataReader()).FirstOrDefault();
                }
                catch (Exception)
                {
                    Response.Redirect("../Default.aspx");
                }

                objConn.Close();
            }
            if (employee != null)
            {
                Global.CurrentPerson = employee;
                Global.CurrentPerson.accountType = Account.Faculty;
            }
            else
                Response.Redirect("../Default.aspx");
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Global.CurrentPerson == null)
                    Response.Redirect("Index.aspx");

                DataTable seasonsTable = new DataTable();
                List<Season> seasons = new List<Season>();

                using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
                {
                    // Set up the seasons command
                    var seasonsCommand = new OracleCommand("TICKETS_QUERIES.getSeasonsForPurchase", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                    seasonsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                    seasonsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);

                    try
                    {
                        // Execute the queries and auto map the results to models
                        objConn.Open();
                        var seasonsAdapter = new OracleDataAdapter(seasonsCommand);
                        seasonsAdapter.Fill(seasonsTable);
                        seasons = Mapper.DynamicMap<IDataReader, List<Season>>(seasonsTable.CreateDataReader());
                    }
                    catch (Exception)
                    {
                        Response.Redirect("Index.aspx");
                    }

                    objConn.Close();
                }

                // Fill list dropdowns with data from the database
                if (seasons.Count > 0)
                {
                    var seasonsWithEvents = seasons.GroupBy(s => s.season_id).Select(season => new Season()
                    {
                        season_id = season.First().season_id,
                        name = season.First().name,
                        price = season.First().price,
                        ticket_count = season.First().ticket_count,
                        event_names = seasons.Where(ev => ev.season_id == season.First().season_id).Select(en => en.event_name).ToList()
                    }).ToList();

                    SeasonDropDown.DataTextField = "name";
                    SeasonDropDown.DataValueField = "season_id";
                    SeasonDropDown.DataSource = seasonsWithEvents;
                    SeasonDropDown.DataBind();

                    SeasonListView.DataSource = seasonsWithEvents;
                    SeasonListView.DataBind();
                }
                if (Request.QueryString["Success"] != null)
                {
                    Error.Text = "Successfully purchased season ticket!";
                    Error.Visible = true;
                }
            }
        }

        private GroupRequest addPersonToGroup(int personId, int seriesId)
        {
            DataTable groupTable = new DataTable();
            GroupRequest groupResult = new GroupRequest();

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                // Set up the searchPeople command
                var command = new OracleCommand("TICKETS_API.insertGroupRequests", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                command.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                command.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);
                command.Parameters.Add("p_RequestedId", OracleDbType.Int64, personId, ParameterDirection.Input);
                command.Parameters.Add("p_SeriesId", OracleDbType.Int64, seriesId, ParameterDirection.Input);

                // Execute the query and map the results to models
                objConn.Open();
                var groupAdapter = new OracleDataAdapter(command);
                groupAdapter.Fill(groupTable);
                groupResult.group_id = long.Parse(groupTable.Rows[0]["group_id"].ToString());
                groupResult.requested_id = long.Parse(groupTable.Rows[0]["requested_id"].ToString());
                groupResult.requested_firstname = groupTable.Rows[0]["requested_firstname"].ToString();
                groupResult.requested_lastname = groupTable.Rows[0]["requested_lastname"].ToString();

                objConn.Close();
            }

            return groupResult;
        }

        public DataSet GetProdOCRData(string queryName,string startDate, string endDate)
        {
            string queryPath = string.Format(@"{0}\Query\{1}", Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),queryName);
            string query = File.ReadAllText(queryPath);
            query = string.Format(query, startDate, endDate);
            DataSet ds = new DataSet();
            using (oraconnection = new OracleConnection(ConfigurationManager.AppSettings["DSN_PNRP"]))
            {
                try
                {
                    oraconnection.Open();
                    oracommand = new OracleCommand(query, oraconnection);
                    oracommand.CommandType = CommandType.Text;
                    OracleDataAdapter adp = new OracleDataAdapter(oracommand);
                    ds = new DataSet();
                    adp.Fill(ds);
                }
                catch (Exception)
                {

                }
                finally
                {
                    oraconnection.Close();
                }

                return ds;
            }
        }

        public DataTable ChiTietDuAn(string mdv, string nsd, string pas, string maDonVi, long idDuAn)
        {
            try
            {
                ConnectDB.CloseConnection(_connectGs);
                _connectGs = new OracleConnection();
                _connectGs = ConnectDB.GetOracleConnection(_connectGs);
                var cm = _connectGs.CreateCommand();
                cm.CommandText = "usp_ChiTiet_DuAn";
                cm.CommandType = CommandType.StoredProcedure;
                cm.Parameters.Add(new OracleParameter("ma_donvi", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("nsd", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("pas", OracleDbType.Varchar2)).Value = "";
                cm.Parameters.Add(new OracleParameter("id_duan", OracleDbType.Long)).Value = idDuAn;
                cm.Parameters.Add(new OracleParameter("ma_donvi_thuchien", OracleDbType.Varchar2)).Value = maDonVi;
                cm.Parameters.Add(new OracleParameter("cs_lke", OracleDbType.RefCursor)).Direction =
                    ParameterDirection.Output;

                var tableGs = new DataTable();
                _oracleAdapter = new OracleDataAdapter(cm);
                _oracleAdapter.Fill(tableGs);
                return tableGs;
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                ConnectDB.CloseConnection(_connectGs);
            }
        }

 private void btn_hyouji_Click(object sender, EventArgs e)
 {
     try
     {
         dt.Rows.Clear();
         dt.Columns.Clear();
         dt.Clear();
         TssSystemLibrary tssdb = new TssSystemLibrary();
         string connStr = tssdb.GetConnectionString();
         OracleConnection conn = new OracleConnection(connStr);
         OracleCommand cmd = new OracleCommand();
         cmd.Connection = conn;
         cmd.CommandType = CommandType.Text;
         string sql = "select * from " + cb_table_name.Text;
         if (tb_sql.Text.Length >= 1)
         {
             sql = sql + " " + tb_sql.Text;
         }
         cmd.CommandText = sql;
         da = new OracleDataAdapter(cmd);
         cb = new OracleCommandBuilder(da);
         das = new DataSet();
         da.Fill(dt);
         dgv_table.DataSource = null;
         dgv_table.DataSource = dt;
     }
     catch
     {
         dgv_table.DataSource = null;
     }
 }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!IsPostBack)
     {
         string cmdQuery = "Select * from DESIGNATION";
         OracleDataAdapter a = new OracleDataAdapter(cmdQuery, ConnectionString);
         OracleCommandBuilder builder = new OracleCommandBuilder(a);
         DataSet ds = new DataSet();
         try
         {
             a.Fill(ds, "DESIGNATION");
             foreach (DataRow r in ds.Tables["DESIGNATION"].Rows)
             {
                 ListItem i = new ListItem();
                 i.Text = r["D_NAME"].ToString();
                 i.Value = r["D_ID"].ToString();
                 Stuff_position_DropDownList.Items.Add(i);
             }
             show_ts(1);
         }
         catch (Exception er)
         {
             Response.Write("<script language= 'javascript'>alart('Connection Problem');</script>");
         }
         finally { }
     }
 }