C# 调用Oracle 存储过程返回数据集 实例

1. 在oracle 下创建表

     t_user(

        ID varchar(20),

        Name varchar(20)

       )

 2.  添加数据 

           '1' , '张三'

           '2' , '李四'

 3. 创建包并且定义变量和声明存储过程

     CREATE OR REPLACE PACKAGE pkg_user AS
        --定义返回值（游标类型）
      TYPE myrctype IS REF CURSOR;
         --定义查询过程
      PROCEDURE sp_userlist (cur_result OUT myrctype);
      END pkg_user;

 4.创建存储过程 sp_userlist

     CREATE OR REPLACE PACKAGE BODY "PKG_USER" AS
       --查询过程
     PROCEDURE sp_userlist(cur_result OUT myrctype)
     IS
        sqlstr   VARCHAR2 (500);
     BEGIN
          OPEN cur_result FOR
             select * from t_user ;
    END sp_userlist ;
    END pkg_user;

5. C# 中就可以调用这个存储过程了.

    在Oracle helper 中添加

   

      public static void RunSql(string SqlString, CommandType cmdType, out DataTable dt)
        {
         
            OracleCommand cmd = CreateSqlCommand(SqlString, cmdType);
            OracleDataAdapter da;
            da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dt = ds.Tables[0];
            da.Dispose();

            Close();

        }

 

--------------------------------------------------------------------------------------------------------------------------------       

           protected static OracleCommand CreateSqlCommand(string SqlString,  CommandType cmdType)
        {
            // 确认打开连接
            Open();

            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandType = cmdType;
            cmd.CommandText = SqlString;

            OracleParameter p = new OracleParameter();
            p = new OracleParameter("cur_result", System.Data.OracleClient.OracleType.Cursor);
            p.Direction = System.Data.ParameterDirection.Output;
            cmd.Parameters.Add(p);

            return cmd;
        }

   

--------------------------------------------------------------------------------------------------------------------------------     

        protected static void Open()
        {
            // 打开数据库连接

             conn = new OracleConnection("Data Source=ORCL;User Id=mobile;Password=server;";);
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }

 

6.  获取数据

　      public DataTable  GetUserList()
        {
            try
            {
                DataTable dt = new DataTable();
              
                OracleHelper.RunSql("sp_userlist, CommandType.StoredProcedure, out dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 

   我们可以看到dt里有两条数据('张三'和'李四')