在上一篇中介绍了如何使用odp.net代替ado.net，不过demo程序是C/S的，今天写一个B/S的，主要是获取翻页存储过程的数据和行数。

首先，在Web.Config中配置数据库连接字串，具体企业库怎么配置连接字串这里就不介绍了。代码如下：

<add name="OracleSource" connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.88)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=sys;Password=123;" providerName="Oracle.DataAccess.Client" />

然后，在.cs文件中编写如下代码，代码要注意的地方是存储过程的参数名要与代码中的参数名一致：

        /// <summary>
        /// 获取当前页数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetCurrentDataSet()
        {
            //调用EnterpriseLibrary获取Database对象
            Database db = DatabaseFactory.CreateDatabase("OracleSource");
            //获取OracleCommand
            OracleCommand com = db.GetStoredProcCommand("WP_ALL_PAGINATION") as OracleCommand;
            //表名称
            com.Parameters.Add("TABLENAME_IN", OracleDbType.Varchar2, 50);
            com.Parameters["TABLENAME_IN"].Direction = ParameterDirection.Input;
            com.Parameters["TABLENAME_IN"].Value = tableName;

            //每页显示记录数
            com.Parameters.Add("PAGESIZE_IN", OracleDbType.Int32);
            com.Parameters["PAGESIZE_IN"].Direction = ParameterDirection.Input;
            com.Parameters["PAGESIZE_IN"].Value = PageSize * PageNo;

            //当前页索引值
            com.Parameters.Add("PAGEINDEX_IN", OracleDbType.Int32);
            com.Parameters["PAGEINDEX_IN"].Direction = ParameterDirection.Input;
            com.Parameters["PAGEINDEX_IN"].Value = (PageNo - 1) * PageSize;

            //排序字段
            com.Parameters.Add("ORDERSYNTAX_IN", OracleDbType.Varchar2, 3000);
            com.Parameters["ORDERSYNTAX_IN"].Direction = ParameterDirection.Input;
            com.Parameters["ORDERSYNTAX_IN"].Value = orderBY;

            //Where子句下条件
            com.Parameters.Add("CONDITION_IN", OracleDbType.Varchar2, 3000);
            com.Parameters["CONDITION_IN"].Direction = ParameterDirection.Input;
            com.Parameters["CONDITION_IN"].Value = whereCondition;

            //显示的字段
            com.Parameters.Add("FIELDSNAME_IN", OracleDbType.Varchar2,300);
            com.Parameters["FIELDSNAME_IN"].Direction = ParameterDirection.Input;
            com.Parameters["FIELDSNAME_IN"].Value = showFields;

            //主键
            com.Parameters.Add("ONLYFIELD_IN", OracleDbType.Varchar2, 100);
            com.Parameters["ONLYFIELD_IN"].Direction = ParameterDirection.Input;
            com.Parameters["ONLYFIELD_IN"].Value = guid;

            //返回总记录数
            com.Parameters.Add("COUNT_OUT", OracleDbType.Int32);
            com.Parameters["COUNT_OUT"].Direction = ParameterDirection.InputOutput;
            com.Parameters["COUNT_OUT"].Value = 0;

            //返回记录集
            com.Parameters.Add("cur_out", OracleDbType.RefCursor);
            com.Parameters["cur_out"].Direction = ParameterDirection.Output;
            DataSet ds = db.ExecuteDataSet(com);
            //符合条件的记录总条数
            Count = int.Parse(com.Parameters["COUNT_OUT"].Value.ToString());

            return ds;
        }

Tags: oracle