简单的C#使用Oracle

 1.设置Scott账户的权限，使之能创建表。
2.create table employee(UserID number(4), UserName nvarchar2(32));
3.创建包，存储过程。

create or replace procedure AddNew(pUserID in Number,pUserName in nvarchar2) is
begin
  insert into Employee(UserID ,UserName) values(pUserID,pUserName);
end AddNew;

－－
create or replace package Employee_Op
as
 type mycType is ref cursor;
 procedure ShowAll(pC out mycType);
end Employee_Op;


－－
create or replace package body Employee_Op
as
 procedure ShowAll(pC out mycType)
 is
 begin
  open pC for select UserID,UserName from Employee;
 end ShowAll;
end Employee_Op;

4.在C# 中调用：
 用DataReader:
 

string ConnStr = "data source=First;User ID = scott;Password=tiger";
 
                OracleConnection Conn = new OracleConnection(ConnStr);
                Conn.Open();

                OracleCommand Cmd = new OracleCommand();
                Cmd.Connection = Conn;

                Cmd.CommandText = "EMPLOYEE_OP.SHOWALL";
                Cmd.CommandType = CommandType.StoredProcedure;

                Cmd.Parameters.Add("PC",OracleType.Cursor);
                Cmd.Parameters[0].Direction = ParameterDirection.Output;

                OracleDataReader DR = Cmd.ExecuteReader();

                DataGrid1.DataSource = DR;
                DataBind();

                DR.Close();
                Conn.Close();

用DataSet:
  

    string ConnStr = "data source=First;User ID = scott;Password=tiger";
 
            OracleConnection Conn = new OracleConnection(ConnStr);
            Conn.Open();

            OracleCommand Cmd = new OracleCommand();
            Cmd.Connection = Conn;

            Cmd.CommandText = "EMPLOYEE_OP.SHOWALL";
            Cmd.CommandType = CommandType.StoredProcedure;

            Cmd.Parameters.Add("PC",OracleType.Cursor);
            Cmd.Parameters[0].Direction = ParameterDirection.Output;
                
            OracleDataAdapter DA = new OracleDataAdapter(Cmd);

            //DA.TableMappings.Add("Table","Employee");

            DataSet DS = new DataSet();
            DA.Fill(DS);
            
            Response.Write( DS.Tables[0].Rows.Count.ToString() );
            
            DataGrid2.DataSource = DS.Tables[0].DefaultView;
            DataGrid2.DataBind();

            Cmd.Parameters.Clear();
            Conn.Close();
        

插入数据：
 

string ConnStr = "data source=First;User ID = scott;Password=tiger";
 
            OracleConnection Conn = new OracleConnection(ConnStr);
            Conn.Open();

            OracleCommand Cmd = new OracleCommand();
            Cmd.Connection = Conn;

            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.CommandText = "AddNew";

            Cmd.Parameters.Add( new OracleParameter("pUserID",OracleType.Number,4) );
            Cmd.Parameters.Add(new OracleParameter("pUserName",OracleType.NVarChar,32) );
                              

            Cmd.Parameters[0].Direction = ParameterDirection.Input;
            Cmd.Parameters[1].Direction = ParameterDirection.Input;

            Cmd.Parameters[0].Value = int.Parse( lblID.Text.ToString() ) ;
            Cmd.Parameters[1].Value = lblName.Text.ToString() ;

            Cmd.ExecuteNonQuery();

            Conn.Close();

            dsbtn_Click(this,EventArgs.Empty);