C# OracleClient.OracleDataAdapter类代码示例

        public static bool DeckIsRefD(int Deck_id)
        {
            try
            {
                OracleConnection OraCon = new OracleConnection("Data Source=oids;User ID=plm;Password=123!feed;Unicode=True");
                OraCon.Open();

                OracleDataAdapter OrclPrjAdapter = new OracleDataAdapter();
                OracleCommand OrclPrjCmd = OraCon.CreateCommand();
                OrclPrjAdapter.SelectCommand = OrclPrjCmd;
                OrclPrjCmd.CommandText = @"SELECT T.TABLE_NAME,T.COLUMN_NAME FROM use_deckid_tables_view T";
                DataSet Mydata = new DataSet();
                OrclPrjAdapter.Fill(Mydata);
                DataSet tmpData = new DataSet();
                string QueryPrjIdCmdStr = string.Empty;
                for (int i = 0; i < Mydata.Tables[0].Rows.Count; i++)
                {
                    QueryPrjIdCmdStr = @"SELECT T." + Mydata.Tables[0].Rows[i][1] + " FROM " + Mydata.Tables[0].Rows[i][0] + " T WHERE TO_CHAR(T." + Mydata.Tables[0].Rows[i][1] + ")=TO_CHAR(" + Deck_id + ")";
                    OrclPrjCmd.CommandText = QueryPrjIdCmdStr;
                    OrclPrjAdapter.Fill(tmpData);
                    if (tmpData.Tables[0].Rows.Count > 0)
                        return true;
                }

                return false;
            }
            catch (Exception err)
            {
                System.Console.WriteLine(err.Message);
                return true;
            }
        }

 /// <summary>
 /// execute a query£¬return DataSet
 /// </summary>
 /// <param name="SQLString"></param>
 /// <returns>DataSet</returns>
 public static DataSet Query(string connectionString, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand();
         PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
         using (OracleDataAdapter da = new OracleDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             //try
             //{
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             //}
             //catch (System.Data.OracleClient.OracleException ex)
             //{
             //    throw new Exception(ex.Message);
             //}
             //finally
             //{
             //    if (connection.State != ConnectionState.Closed)
             //    {
             //        connection.Close();
             //    }
             //}
             return ds;
         }
     }
 }

		public static DbDataAdapter CreateDataAdapter(DbProviderType DbProviderType, IDbCommand command, IDbConnection connection)
		{
			switch (DbProviderType)
			{
				case DbProviderType.SQLSERVER:
					SqlDataAdapter sqlda = new SqlDataAdapter();
					sqlda.SelectCommand = (SqlCommand) command;
					command.Connection = connection;
					return sqlda;
				case DbProviderType.ORACLE:
					OracleDataAdapter orada = new OracleDataAdapter();
					orada.SelectCommand = (OracleCommand) command;
					command.Connection = connection;
					return orada;
//				case DbProviderType.MYSQL:
//					MySqlDataAdapter mysqlda = new MySqlDataAdapter();
//					mysqlda.SelectCommand = (MySqlCommand) command;
//					command.Connection = connection;
//					return mysqlda;
				default:
					OleDbDataAdapter oleda = new OleDbDataAdapter();
					oleda.SelectCommand = (OleDbCommand) command;
					command.Connection = connection;
					return oleda;
			}
		}

 /// <summary>
 /// ִ�в�ѯ��䣬����DataSet
 /// </summary>
 /// <param name="SQLString">��ѯ���</param>
 /// <returns>DataSet</returns>
 public static DataSet Query(string SQLString)
 {
     if (SQLString != null && SQLString.Trim() != "")
     {
         using (OracleConnection connection = new OracleConnection(connectionString))
         {
             DataSet ds = new DataSet();
             try
             {
                 connection.Open();
                 OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
                 command.Fill(ds, "ds");
             }
             catch (System.Data.OracleClient.OracleException ex)
             {
                 throw new Exception(ex.Message);
             }
             finally
             {
                 connection.Close();
             }
             return ds;
         }
     }
     else
     {
         return null;
     }
 }

        private void stdnt_payment_info_btn_Click(object sender, EventArgs e)
        {
            dbConnect conObj = new dbConnect();
            OracleConnection conn = conObj.connFunc();
            OracleParameter parOut = new OracleParameter();

            parOut.ParameterName = "res_cur";
            parOut.OracleType = OracleType.Cursor;
            parOut.Size = 32;
            parOut.Direction = System.Data.ParameterDirection.Output;

            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "student_payment_procedure";
            cmd.Parameters.Add(parOut);

            conn.Open();

            OracleDataAdapter adapter = new OracleDataAdapter(cmd);
            DataSet myset = new DataSet("res_cur");
            adapter.Fill(myset);
            stdnt_payment_dataGridView.DataSource = myset;
            stdnt_payment_dataGridView.DataSource = myset.Tables[0];

            conn.Close();
        }

 public DataSet ReadDataToDataSet(OracleConnection conn, String SQL, string diff)
 {
     OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);
     DataSet testDataSet = new DataSet();
     adapter.Fill(testDataSet);
     return testDataSet;
 }

        //Filling the booking details into the return cars datagridview
        public void bookingsDetails(DataGridView grd_Bookings)
        {
            var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy");
                var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy");

                string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " +
                                       "Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " +
                                       "WHERE  Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow );

               // MessageBox.Show(query_String);

                try
                {
                    connection.Open();

                    cmd = connection.CreateCommand();
                    cmd.CommandText = query_String;
                    data_Adapter = new OracleDataAdapter(cmd);
                    data_Set = new DataSet();
                    data_Adapter.Fill(data_Set, "Bookings");
                    grd_Bookings.DataSource = data_Set.Tables["Bookings"];
                    data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
                    data_reader.Read();

                    connection.Close();
                }
                catch (Exception ex) {MessageBox.Show("" + ex);}
        }

 protected DataSet cmis_search(string ma_khang, string so_cto, out string txt_err)
 {
     DataSet ds = new DataSet();
     try
     {
         string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", "THAINGUYEN", "CMIS01PC1");
         OracleConnection ora_con = new OracleConnection(txt_cmd);
         txt_cmd = @"select TEN_KHANG, DCHI_KHANG, DCHI_TTOAN, KY, THANG, NAM, NGAY_DKY, NGAY_CKY, LOAI_HDON, TONG_TIEN, DIEN_TTHU, NGAY_PHANH
             from cmis01.hdn_hdon
             where MA_KHANG=:ma_khang or SO_CTO=:so_cto
             order by NGAY_CKY desc";
         OracleParameter[] pa = new OracleParameter[2];
         pa[0] = new OracleParameter("ma_khang", ma_khang.ToUpper());
         pa[1] = new OracleParameter("so_cto", so_cto);
         OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
         ora_da.SelectCommand.CommandType = CommandType.Text;
         ora_da.SelectCommand.Parameters.Add(pa[0]);
         ora_da.SelectCommand.Parameters.Add(pa[1]);
         ora_con.Open();
         ora_da.Fill(ds);
         ora_con.Close();
         txt_err = "";
     }
     catch (Exception ex) { txt_err = ex.Message; }
     return ds;
 }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string strConn = "Data Source=orcl;User Id=chatroom;Password=chatroom;Integrated Security=no;";
            OracleConnection oleDbConnection = new OracleConnection(strConn);
            oleDbConnection.Open();
            string strSql;
            if (this.name.Text.ToString() == "")
            {
                strSql = "select * from scott.ChatRoomLog order by id desc";
            }
            else
            {
                strSql = "select * from scott.chatroomlog where name='" + this.name.Text.ToString() + "' order by id";
            }
            System.Data.OracleClient.OracleCommand oleDbCommand = new System.Data.OracleClient.OracleCommand();
            oleDbCommand.CommandText = strSql;
            oleDbCommand.Connection = oleDbConnection;
            System.Data.OracleClient.OracleDataAdapter oleDbDataAdapter = new System.Data.OracleClient.OracleDataAdapter(oleDbCommand);
            DataTable table = new DataTable();
            oleDbDataAdapter.Fill(table);
            oleDbConnection.Close();
            this.GridView1.DataSource = table.DefaultView;
            this.GridView1.DataBind();

        }
        catch
        {
        }
    }

        private void frmMain_Load(object sender, EventArgs e)
        {
            my_rpt objRpt;
            // Creating object of our report.
            objRpt = new my_rpt();

            String ConnStr = "SERVER=myDb;USER ID=user1;PWD=user1";

            OracleConnection myConnection = new OracleConnection(ConnStr);

            String Query1 = "select a.PROJECT_ID,a.PROJECT_NAME,b.GROUP_NAME from tbl_project a,tbl_project_group b where a.group_code= b.group_code";

            OracleDataAdapter adapter = new OracleDataAdapter(Query1, ConnStr);

            DataSet Ds = new DataSet();

            // here my_dt is the name of the DataTable which we created in the designer view.
            adapter.Fill(Ds, "my_dt");

            if (Ds.Tables[0].Rows.Count == 0)
            {
                MessageBox.Show("No data Found", "CrystalReportWithOracle");
                return;
            }

            // Setting data source of our report object
            objRpt.SetDataSource(Ds);

            CrystalDecisions.CrystalReports.Engine.TextObject root;
            root = (CrystalDecisions.CrystalReports.Engine.TextObject)objRpt.ReportDefinition.ReportObjects["txt_header"];
            root.Text = "Sample Report By Using Data Table!!";

            // Binding the crystalReportViewer with our report object.
            crystalReportViewer1.ReportSource = objRpt;
        }

        /// <summary>
        /// ���������ļ��������õ����ݿ�����
        /// �ʹ�������������������Ӧ���ݿ�����������
        /// </summary>
        /// <returns></returns>
        public static IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
        {
            IDbDataAdapter adapter = null;
            switch (AdoHelper.DbType)
            {
                case DatabaseType.SQLSERVER:
                    adapter = new SqlDataAdapter((SqlCommand)cmd);
                    break;
                case DatabaseType.ORACLE:
                    adapter = new OracleDataAdapter((OracleCommand)cmd);
                    break;
                case DatabaseType.MYSQL:
                    adapter = new MySqlDataAdapter((MySqlCommand)cmd);
                    break;
                case DatabaseType.ACCESS:
                    adapter = new OleDbDataAdapter((OleDbCommand)cmd);
                    break;
                case DatabaseType.SQLITE:
                    adapter = new SQLiteDataAdapter((SQLiteCommand)cmd);
                    break;
                default: throw new Exception("���ݿ�����Ŀǰ��֧�֣�");
            }

            return adapter;
        }

        public void Finish()
        {
            _output = Output ?? "Output";
            if (!Directory.Exists(_output))
            {
                Directory.CreateDirectory(_output);
            }

            Console.Write("输出路径：");
            Console.WriteLine(_output);
            using (OracleConnection conn = new OracleConnection(ConnectionString))
            {
                conn.Open();
                using (OracleCommand cmd = new OracleCommand())
                {
                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                    {
                        cmd.Connection = conn;
                        foreach (string tableName in TableNames)
                        {
                            CreateFile(tableName, cmd, da);
                            Console.WriteLine("成功创建文件:{0}.cs", tableName);
                        }

                        Console.WriteLine("操作成功，成功生成{0}个文件", TableNames.Length);
                    }
                }
            }
        }

 public static void BuscaDGV(DataGridView dgv, String nome)
 {
     OracleDataAdapter da = new OracleDataAdapter("SELECT f.funcionarioid, f.nome, c.nome cargo, f.numero, f.ativo FROM funcionarios f JOIN cargos c USING (cargoid) WHERE f.nome LIKE '" + nome + "%'", ConexaoOracle.ObterConexao());
     DataTable dt = new DataTable();
     da.Fill(dt);
     dgv.DataSource = dt;
 }

 private void button1_Click(object sender, EventArgs e)
 {
     try
     {
         OracleConnection OrclConn = new OracleConnection(DataAccess.OIDSConnStr);
         OrclConn.Open();
         OracleDataAdapter OrclNestAdapter = new OracleDataAdapter();
         OracleCommand OrclNestCmd = OrclConn.CreateCommand();
         OracleCommand OrclCmd = OrclConn.CreateCommand();
         //OracleCommandBuilder myCommandBuilder = new OracleCommandBuilder(mOrclAdapter);
         //mOrclAdapter.Update(MyDataSet);
         //conn.Close();
         //for each row and each spool , insert into single_inventory_table
         for (int i = 0; i < InventoryTable.Rows.Count; i++)
         {
             string pipetype = InventoryTable.Rows[i]["类型"].ToString();
             Int32 pipelength = Convert.ToInt32(InventoryTable.Rows[i]["长度"].ToString());
             string erpcode = InventoryTable.Rows[i]["ERP编码"].ToString();
             double weight = (Convert.ToDouble(InventoryTable.Rows[i]["重量"].ToString())) / (Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()));
             string batch_no = InventoryTable.Rows[i]["炉批号"].ToString();
             for (int j = 0; j < Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()); j++)
             {
                 //insert into oracle
                 OrclCmd.CommandText = "INSERT INTO pipe_inventory_single_spool (PIPETYPE,PIPELENGTH,ERPCODE,WEIGHT,BATCH_NO,STATE) VALUES ('" + pipetype + "'," + pipelength + ",'" + erpcode + "'," + weight + ",'" + batch_no + "',1)";
                 OrclCmd.ExecuteNonQuery();
             }
         }
     }
     catch (Exception err)
     {
         MessageBox.Show(err.Message);
     }
     MessageBox.Show("更新成功!");
 }

 /// <summary>
 /// 执行查询语句，返回DataSet
 /// </summary>
 /// <param name="SQLString">查询语句</param>
 /// <returns>DataSet</returns>
 public override DataSet ExecuteDataSet(string SQLString, params IDataParameter[] cmdParms)
 {
     using (OracleConnection connection = new OracleConnection(connectionString))
     {
         OracleCommand cmd = new OracleCommand();
         PrepareCommand(cmd, connection, null, SQLString, cmdParms);
         using (OracleDataAdapter da = new OracleDataAdapter(cmd))
         {
             DataSet ds = new DataSet();
             try
             {
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
             }
             catch (OracleException e)
             {
                 //LogManage.OutputErrLog(e, new Object[] { SQLString, cmdParms });
                 throw;
             }
             finally
             {
                 cmd.Dispose();
                 connection.Close();
             }
             return ds;
         }
     }
 }