net 调用存储过程的out参数（在oracle中测试正确，但net显示不对）

Question

测试oracle存储

Answer 1

OracleParameter可以直接用在MSSQL数据库中吗？

Answer 2

pan[6].Direction = ParameterDirection.Output;

Answer 3

pan[6].Direction = ParameterDirection.Output;使用上面的，显示为0也不对

Answer 4

pan[6].Direction = ParameterDirection.Output;应该是这个问题了， ParameterDirection的定义是enum ParameterDirection
{
     Input,
     InputOutput,
     Output,
     ReturnValue
}Output就是2.

Answer 5

你确定你传入参数能得到非0效果吗
如果是
pan[6].Value = ParameterDirection.Output;
 pan[7].Value = ParameterDirection.Output;
显示如果是
pan[6].Direction = ParameterDirection.Output; 
pan[7].Direction = ParameterDirection.Output; 
显示

Answer 6

这上CS
代码
 protected void Button4_Click(object sender, EventArgs e)
    {
        int kk=0;
        int pp = 0;
        DataTable nn = new DataTable();
        string yy = getpages(1, "t.new_title,t.new_content", 10, "qg_news", "new_type='1'", "new_sendtime DESC", out kk,out pp, out nn);
        if (nn.Rows.Count > 0)
        {
            for (int i = 0; i < nn.Rows.Count; i++)
            {
                Response.Write("title is:" + nn.Rows[i]["new_title"].ToString());
                Response.Write("<br />");
            }
        }
        Response.Write(kk.ToString());
        Response.Write(pp.ToString());
    }
    /////////////////////////////////
        private static string getpages(int pages,string fields,int pagesize,string tablename,string strwhere,string orderby,  out int Vnumber,out int pagecounts, out DataTable dtresult)
        {
            string restr="";
           int num=0;
           int pagec=0;
            DataTable kk = new DataTable();
            OracleParameter[] pan = { 
                                   new OracleParameter("pages",  OracleDbType.Int32),//--数据页数，从1开始
                                   new OracleParameter("fields",  OracleDbType.Varchar2),
                                   new OracleParameter("pageSize",  OracleDbType.Int32),//--每页大小
                                   new OracleParameter("tableName", OracleDbType.Varchar2),
                                   new OracleParameter("strWhere", OracleDbType.Varchar2),
                                   new OracleParameter("Orderby", OracleDbType.Varchar2),
                                   new OracleParameter("numCount", OracleDbType.Int32),//--总记录数
                                   new OracleParameter("pagecounts", OracleDbType.Int32),//--总记录数
                                   new OracleParameter("v_cur",  OracleDbType.RefCursor)                                 
                                  };
                pan[0].Value = pages;
                pan[1].Value = fields;
                pan[2].Value = pagesize;
                pan[3].Value = tablename;
                pan[4].Value = strwhere;
                pan[5].Value = orderby;
                pan[6].Value = ParameterDirection.Output;
                pan[7].Value = ParameterDirection.Output;
                pan[8].Direction = ParameterDirection.Output; 
                
            try
            {              
                kk=qg_oracleconnet.ExecuteDataTableByProc("qg_pages",pan);
                 num  = Convert.ToInt32(pan[6].Value);//总共的记录数
                 pagec = Convert.ToInt32(pan[7].Value);//总共的页数                restr="1";
            }
            catch (Exception ex)
            {
               restr=ex.ToString();
               num = 0;
            }
            Vnumber = num;
            dtresult = kk;
            pagecounts = pagec;
            return restr;
        }/////下面为存储过程代码
create or replace PROCEDURE qg_pages 
(
   page in number,--数据页数，从1开始
   fields in varchar2,
   pageSize in number,--每页大小
   tableName nvarchar2,--表名
   strWhere nvarchar2,--where条件
   Orderby nvarchar2,
   numCount out number,--总记录数
   pagecounts out number,
   v_cur out qgpackage.qg_cursor
) 
is 
   strSql varchar2(2000);
   pageCount number;
   startIndex number;
   endIndex number;
BEGIN
  strSql:='select count(1) from '||tableName;
  if strWhere is not null or strWhere<>'' then 
     strSql:=strSql||' where '||strWhere;
  end if;  
  EXECUTE IMMEDIATE strSql INTO numCount;
  --计算数据记录开始和结束
  pageCount:=numCount/pageSize+1;
  startIndex:=(page-1)*pageSize+1;
  endIndex:=page*pageSize;
  pagecounts:=pageCount;
  
  strSql:='select rownum ro,'|| fields ||' from '||tableName||' t';  
  strSql:=strSql||' where rownum<='||endIndex;
  
  if strWhere is not null or strWhere<>'' then 
     strSql:=strSql||' and '||strWhere;
  end if;
  
  if  Orderby is not null or Orderby<>'' then 
     strSql:=strSql||' order by '||Orderby;
  end if;
  
  strSql:='select * from ('||strSql||') where ro >='||startIndex;  
 --DBMS_OUTPUT.put_line(strSql);  OPEN v_cur FOR strSql; 
END qg_pages;

Answer 7

做几件事确认错误地点
1.先测试存储过程是否异常，是否有异常被catch
2.kk的值是否正常
3.存储过程内部打日志看out的值是否正确

Answer 8

你确定使用DataSet能读取到out的值？？？？就算要使用DataSet也不用这么麻烦啊，直接反正多个表的查询结果就可以了。
select * from useer
select count(0) from user
获取：dataset.Tables[0]   dataset.Tables[1]

Answer 9

CS代码有异常catch 提示是不能将空的转为数字型

Answer 10

已经和你说了，在C#中使用SqlDataAdapter无法获取Output的值，
换句话说，你只能使用SqlDataReader才能读取到Output的值，
为什么你在那么简单的问题上能纠结几天，还是你根本就看不懂我在说什么？

Answer 11

private static string getpages(int pages,string fields,int pagesize,string tablename,string strwhere,string orderby,  out int Vnumber,out int pagecounts, out DataTable dtresult)
         {
             string restr="";
            int num=0;
            int pagec=0;
             DataTable kk = new DataTable();
             OracleParameter[] pan = { 
                                    new OracleParameter("pages",  OracleDbType.Int32),//--数据页数，从1开始
                                   new OracleParameter("fields",  OracleDbType.Varchar2),
                                    new OracleParameter("pageSize",  OracleDbType.Int32),//--每页大小
                                   new OracleParameter("tableName", OracleDbType.Varchar2),
                                    new OracleParameter("strWhere", OracleDbType.Varchar2),
                                    new OracleParameter("Orderby", OracleDbType.Varchar2),
                                    new OracleParameter("numCount", OracleDbType.Int32),//--总记录数
                                   new OracleParameter("pagecounts", OracleDbType.Int32),//--总记录数
                                 //  pagecounts
                                    new OracleParameter("v_cur",  OracleDbType.RefCursor)                                 
                                   };
                 pan[0].Value = pages;
                 pan[1].Value = fields;
                 pan[2].Value = pagesize;
                 pan[3].Value = tablename;
                 pan[4].Value = strwhere;
                 pan[5].Value = orderby;
                 pan[6].Value = ParameterDirection.Output;
                 pan[7].Value = ParameterDirection.Output;
                 pan[8].Direction = ParameterDirection.Output; 
                  
             try
             {              
                 OleDbConnection conn=new OleDbConnection("连接字符串");
                 conn.Open();
                OleDbCommand cmd=new OleDbCommand();
                cmd.CommandText="qg_pages";
                cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.AddRange(pan);        
                 var dr = cmd.ExecuteReader();     
                while (dr.Read())
                {
                       //
                 }     
                 num  = Convert.ToInt32(pan[6].Value);//总共的记录数
                 pagec = Convert.ToInt32(pan[7].Value);//总共的页数                restr="1";
             }
             catch (Exception ex)
             {
                restr=ex.ToString();
                num = 0;
             }
             Vnumber = num;
             dtresult = kk;
             pagecounts = pagec;
             return restr;
         }

Answer 12

 num  = int.Parse(pan[6].Value.ToString();