Grid之服务器端存储过程分页

[sql] view plain copy print ?

1. CREATE   procedure   [dbo].[AspNetPage] 
2.     @tblName       varchar(1000),        -- 表名 
3. @SelectFieldName varchar(4000),              -- 要显示的字段名(不要加select) 
4.     @strWhere       varchar(4000),              -- 查询条件(注意: 不要加 where) 
5. @OrderFieldName varchar(255),               -- 排序索引字段名 
6.     @PageSize       int ,                 -- 页大小 
7.     @PageIndex      int = 1,                  -- 页码 
8.     @iRowCount      int output,                 -- 返回记录总数 
9.     @OrderType      bit = 0                  -- 设置排序类型, 非 0 值则降序 
10.             
11. AS 
12. declare @strSQL    varchar(4000)       -- 主语句 
13. declare @strTmp    varchar(4000)        -- 临时变量 
14. declare @strOrder varchar(400)        -- 排序类型 
15. declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句 
16. set @OrderFieldName=ltrim(rtrim(@OrderFieldName)) 
17. if @OrderType != 0 
18. begin 
19.     set @strTmp = '<(select min' 
20.     set @strOrder = ' order by ' + @OrderFieldName +' desc' 
21. end 
22. else 
23. begin 
24.     set @strTmp = '>(select max' 
25.     set @strOrder = ' order by ' + @OrderFieldName +' asc' 
26. end 
27. set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' 
28.     + @tblName + ' where ' + @OrderFieldName + @strTmp + '(' 
29.     + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize) 
30.     + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)' 
31.     + @strOrder 
32. if @strWhere != '' 
33.     set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' 
34.         + @tblName + ' where ' + @OrderFieldName + @strTmp + '(' 
35.         + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize) 
36.         + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' ' 
37.         + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder 
38. if @PageIndex = 1 
39. begin 
40.     set @strTmp = '' 
41.     if @strWhere != '' 
42.         set @strTmp = ' where ' + @strWhere 
43.     set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from ' 
44.         + @tblName + @strTmp + ' ' + @strOrder 
45. end 
46. exec(@strSQL) 
47. if @strWhere!='' 
48. begin 
49. set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere 
50. end 
51. else 
52. begin 
53. set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName 
54. end 
55. exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out 

CREATE	 procedure   [dbo].[AspNetPage]
    @tblName       varchar(1000),        -- 表名
    @SelectFieldName    varchar(4000),              -- 要显示的字段名(不要加select)
    @strWhere       varchar(4000),              -- 查询条件(注意: 不要加 where)
    @OrderFieldName      varchar(255),               -- 排序索引字段名
    @PageSize       int ,                 -- 页大小
    @PageIndex      int = 1,                  -- 页码
    @iRowCount      int output,                 -- 返回记录总数
    @OrderType      bit = 0                  -- 设置排序类型, 非 0 值则降序
           
AS
declare @strSQL    varchar(4000)       -- 主语句
declare @strTmp    varchar(4000)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句
set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
    set @strTmp = '<(select min'
    set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
    set @strTmp = '>(select max'
    set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
    + @tblName + ' where ' + @OrderFieldName + @strTmp + '('
    + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
    + @OrderFieldName + ' from ' + @tblName + @strOrder + ') as tblTmp)'
    + @strOrder
if @strWhere != ''
    set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
        + @tblName + ' where ' + @OrderFieldName + @strTmp + '('
        + right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
        + @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
        + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
    set @strTmp = ''
    if @strWhere != ''
        set @strTmp = ' where ' + @strWhere
    set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
        + @tblName + @strTmp + ' ' + @strOrder
end
exec(@strSQL)
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out

HTML:

[html] view plain copy print ?

1. <ext:ResourceManager ID="ResourceManager1" runat="server"> 
2. </ext:ResourceManager> 
3. <ext:Store runat="server" ID="ItemStore" OnRefreshData="Store1_RefershData"> 
4.     <Reader> 
5.         <ext:JsonReader> 
6.             <Fields> 
7.                 <ext:RecordField Name="MB001" /> 
8.                 <ext:RecordField Name="MB002" /> 
9.             </Fields> 
10.         </ext:JsonReader> 
11.     </Reader> 
12.     <Proxy> 
13.         <ext:PageProxy> 
14.         </ext:PageProxy> 
15.     </Proxy> 
16. </ext:Store> 
17. <ext:TextField runat="server" ID="txtItem"> 
18. </ext:TextField> 
19. <ext:Button runat="server" ID="btnQuery" Text="查询" Icon="Find"> 
20.     <DirectEvents> 
21.       <Click OnEvent="ShowChage"></Click> 
22.     </DirectEvents> 
23. </ext:Button> 
24. <ext:GridPanel ID="ItemGrid" runat="server" Border="true" Margins="5 5 5 5" AnchorHorizontal="98%" 
25.     Height="550" Icon="Layers" TrackMouseOver="true" StripeRows="true" Title="品号基本信息" 
26.     StoreID="ItemStore"> 
27.     <ColumnModel ID="ColumnModel1" runat="server"> 
28.         <Columns> 
29.             <ext:Column Header="品号" DataIndex="MB001" Width="250"> 
30.             </ext:Column> 
31.             <ext:Column Header="品名" DataIndex="MB002" Width="250"> 
32.             </ext:Column> 
33.         </Columns> 
34.     </ColumnModel> 
35.     <LoadMask ShowMask="true" /> 
36.     <SelectionModel> 
37.         <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" /> 
38.     </SelectionModel> 
39.     <BottomBar> 
40.         <ext:PagingToolbar ID="PagingToolBar1" StoreID="ItemStore" PageSize="3" runat="server" /> 
41.     </BottomBar> 
42. </ext:GridPanel> 

    <ext:ResourceManager ID="ResourceManager1" runat="server">
    </ext:ResourceManager>
    <ext:Store runat="server" ID="ItemStore" OnRefreshData="Store1_RefershData">
        <Reader>
            <ext:JsonReader>
                <Fields>
                    <ext:RecordField Name="MB001" />
                    <ext:RecordField Name="MB002" />
                </Fields>
            </ext:JsonReader>
        </Reader>
        <Proxy>
            <ext:PageProxy>
            </ext:PageProxy>
        </Proxy>
    </ext:Store>
    <ext:TextField runat="server" ID="txtItem">
    </ext:TextField>
    <ext:Button runat="server" ID="btnQuery" Text="查询" Icon="Find">
        <DirectEvents>
          <Click OnEvent="ShowChage"></Click>
        </DirectEvents>
    </ext:Button>
    <ext:GridPanel ID="ItemGrid" runat="server" Border="true" Margins="5 5 5 5" AnchorHorizontal="98%"
        Height="550" Icon="Layers" TrackMouseOver="true" StripeRows="true" Title="品号基本信息"
        StoreID="ItemStore">
        <ColumnModel ID="ColumnModel1" runat="server">
            <Columns>
                <ext:Column Header="品号" DataIndex="MB001" Width="250">
                </ext:Column>
                <ext:Column Header="品名" DataIndex="MB002" Width="250">
                </ext:Column>
            </Columns>
        </ColumnModel>
        <LoadMask ShowMask="true" />
        <SelectionModel>
            <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" />
        </SelectionModel>
        <BottomBar>
            <ext:PagingToolbar ID="PagingToolBar1" StoreID="ItemStore" PageSize="3" runat="server" />
        </BottomBar>
    </ext:GridPanel>

CS:

[csharp] view plain copy print ?

1. protected void Store1_RefershData(object sender, StoreRefreshDataEventArgs e) 
2.         { 
3.             int PageSize = this.PagingToolBar1.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值 
4.             int Count = 0; 
5.             int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页 
6.             string strWhere = ""; 
7.             if (txtItem.Text.Trim() != "")  
8.             { 
9.                 strWhere = "  MB001 like '%" + txtItem.Text.Trim() + "%'"; 
10.             } 
11.  
12.             var store = this.ItemGrid.GetStore(); 
13.  
14.             DataTable dt = GetPageList("INVMB", " MB001,MB002,MB003", strWhere, PageSize, CurPage, out Count); //连接数据库 
15.             e.Total = Count; 
16.             this.PagingToolBar1.DisplayMsg = CurPage + " - " + PageSize + "条 共 " + e.Total + " 条"; 
17.             if (Count > 0) 
18.             { 
19.                 store.DataSource = dt;//绑定数据 
20.                 store.DataBind(); 
21.             } 
22.             else 
23.             { 
24.                 X.Msg.Alert("查询结果", "<font style='color:red;'>没有找到任何数据!</font>").Show(); 
25.             } 
26.         } 
27.         /// <summary> 
28.         ///  
29.         /// </summary> 
30.         /// <param name="TableName">表名称</param> 
31.         /// <param name="ReturnFields">要显示的字段名</param> 
32.         /// <param name="strwhere">查询条件</param> 
33.         /// <param name="PageSize">每页显示数据条数</param> 
34.         /// <param name="CurPage">当前第几页，页码</param> 
35.         /// <param name="RowCount">返回的总页数</param> 
36.         /// <returns></returns> 
37.         public DataTable GetPageList(string TableName, string ReturnFields, string strwhere, int PageSize, int CurPage, out int RowCount) 
38.         { 
39.             SqlCommand cmd = new SqlCommand("AspNetPage");//存储过程名称 
40.             cmd.CommandType = CommandType.StoredProcedure; 
41.             cmd.Parameters.AddWithValue("@tblName", TableName); //表名称 
42.             cmd.Parameters.AddWithValue("@OrderFieldName", "MB001");//排序索引字段名 
43.             cmd.Parameters.AddWithValue("@PageIndex", CurPage);//当前第几页，页码 
44.             cmd.Parameters.AddWithValue("@PageSize", PageSize);//每页显示数据条数 
45.             cmd.Parameters.AddWithValue("@SelectFieldName", ReturnFields);//要显示的字段名(不要加select) 
46.             cmd.Parameters.AddWithValue("@OrderType", 1);//设置排序类型, 非 0 值则降序 
47.             cmd.Parameters.AddWithValue("@strWhere", strwhere);//查询条件(注意: 不要加 where) 
48.             cmd.Parameters.Add(new SqlParameter("@iRowCount", SqlDbType.Int)); 
49.             cmd.Parameters["@iRowCount"].Direction = ParameterDirection.Output; 
50.             DataTable dt = RunProcedureCmd(cmd); 
51.             RowCount = Convert.ToInt32(cmd.Parameters["@iRowCount"].Value.ToString()); //返回的总页数 
52.             return dt; 
53.         } 
54.         /// <summary> 
55.         /// 执行存储过程，返回DataTable 
56.         /// </summary> 
57.         /// <param name="cmd">存储过程名称</param> 
58.         /// <returns></returns> 
59.         public DataTable RunProcedureCmd(SqlCommand cmd) 
60.         { 
61.             DataTable result = new DataTable(); 
62.             SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DEMO"].ConnectionString);//你自己的链接字符串 
63.             try 
64.             { 
65.                 if ((conn.State == ConnectionState.Closed)) 
66.                 { 
67.                     conn.Open(); 
68.                 } 
69.                 cmd.Connection = conn; 
70.                 SqlDataAdapter da = new SqlDataAdapter(cmd); 
71.                 da.Fill(result); 
72.                 da.Dispose(); 
73.                 conn.Close(); 
74.                 conn.Dispose(); 
75.                 return result; 
76.             } 
77.             catch (Exception ex) 
78.             { 
79.                 conn.Close(); 
80.                 conn.Dispose(); 
81.                 throw ex; 
82.             } 
83.  
84.         } 
85.  
86.     } 

protected void Store1_RefershData(object sender, StoreRefreshDataEventArgs e)
        {
            int PageSize = this.PagingToolBar1.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值
            int Count = 0;
            int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页
            string strWhere = "";
            if (txtItem.Text.Trim() != "") 
            {
                strWhere = "  MB001 like '%" + txtItem.Text.Trim() + "%'";
            }

            var store = this.ItemGrid.GetStore();

            DataTable dt = GetPageList("INVMB", " MB001,MB002,MB003", strWhere, PageSize, CurPage, out Count); //连接数据库
            e.Total = Count;
            this.PagingToolBar1.DisplayMsg = CurPage + " - " + PageSize + "条 共 " + e.Total + " 条";
            if (Count > 0)
            {
                store.DataSource = dt;//绑定数据
                store.DataBind();
            }
            else
            {
                X.Msg.Alert("查询结果", "<font style='color:red;'>没有找到任何数据!</font>").Show();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName">表名称</param>
        /// <param name="ReturnFields">要显示的字段名</param>
        /// <param name="strwhere">查询条件</param>
        /// <param name="PageSize">每页显示数据条数</param>
        /// <param name="CurPage">当前第几页，页码</param>
        /// <param name="RowCount">返回的总页数</param>
        /// <returns></returns>
        public DataTable GetPageList(string TableName, string ReturnFields, string strwhere, int PageSize, int CurPage, out int RowCount)
        {
            SqlCommand cmd = new SqlCommand("AspNetPage");//存储过程名称
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@tblName", TableName); //表名称
            cmd.Parameters.AddWithValue("@OrderFieldName", "MB001");//排序索引字段名
            cmd.Parameters.AddWithValue("@PageIndex", CurPage);//当前第几页，页码
            cmd.Parameters.AddWithValue("@PageSize", PageSize);//每页显示数据条数
            cmd.Parameters.AddWithValue("@SelectFieldName", ReturnFields);//要显示的字段名(不要加select)
            cmd.Parameters.AddWithValue("@OrderType", 1);//设置排序类型, 非 0 值则降序
            cmd.Parameters.AddWithValue("@strWhere", strwhere);//查询条件(注意: 不要加 where)
            cmd.Parameters.Add(new SqlParameter("@iRowCount", SqlDbType.Int));
            cmd.Parameters["@iRowCount"].Direction = ParameterDirection.Output;
            DataTable dt = RunProcedureCmd(cmd);
            RowCount = Convert.ToInt32(cmd.Parameters["@iRowCount"].Value.ToString()); //返回的总页数
            return dt;
        }
        /// <summary>
        /// 执行存储过程，返回DataTable
        /// </summary>
        /// <param name="cmd">存储过程名称</param>
        /// <returns></returns>
        public DataTable RunProcedureCmd(SqlCommand cmd)
        {
            DataTable result = new DataTable();
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DEMO"].ConnectionString);//你自己的链接字符串
            try
            {
                if ((conn.State == ConnectionState.Closed))
                {
                    conn.Open();
                }
                cmd.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(result);
                da.Dispose();
                conn.Close();
                conn.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                throw ex;
            }

        }

    }

Oracle版本：

[sql] view plain copy print ?

1. create or replace package pkg_AspNetPage is 
2.  
3.   type mycursor  is ref cursor; 
4.      
5.   procedure proc_AspNetPage(tblName in varchar2,SelectFieldName in varchar2,strWhere in varchar2,OrderFieldName in varchar2,OrderType in number, start1 in number,limit1 in number,RecoudCount out number,datatable out mycursor ); 
6.  
7. end pkg_AspNetPage; 
8.  
9.  
10.  
11. create or replace package body pkg_AspNetPage is 
12.  
13. procedure proc_AspNetPage(tblName in varchar2,SelectFieldName in varchar2,strWhere in varchar2,OrderFieldName in varchar2,OrderType in number, start1 in number,limit1 in number,RecoudCount out number,datatable out mycursor ) is 
14. sqltxt varchar2(4000); 
15. v_sql varchar2(4000); 
16. v_ordertype varchar2(10); 
17. begin 
18. IF OrderType=0 THEN 
19. v_ordertype:='asc'; 
20. ELSE 
21. v_ordertype:='desc'; 
22. END IF; 
23.  
24. sqltxt:='select * from ' || tblName || ' u where ' || strWhere ||' order by ' || OrderFieldName || ' ' || v_ordertype; 
25. sqltxt:='select rownum rn,t.* from ('||sqltxt||') t';  
26. v_sql:='select count(*) from('||sqltxt||')'; 
27.  
28. execute immediate v_sql into RecoudCount; 
29. v_sql := 'select '|| SelectFieldName ||' from (' || sqltxt || ') where rn between ' ||start1 || ' and ' || limit1; 
30.   
31. open datatable for v_sql; 
32. end proc_AspNetPage; 
33. end pkg_AspNetPage; 

create or replace package pkg_AspNetPage is

  type mycursor  is ref cursor;
    
  procedure proc_AspNetPage(tblName in varchar2,SelectFieldName in varchar2,strWhere in varchar2,OrderFieldName in varchar2,OrderType in number, start1 in number,limit1 in number,RecoudCount out number,datatable out mycursor );

end pkg_AspNetPage;



create or replace package body pkg_AspNetPage is

procedure proc_AspNetPage(tblName in varchar2,SelectFieldName in varchar2,strWhere in varchar2,OrderFieldName in varchar2,OrderType in number, start1 in number,limit1 in number,RecoudCount out number,datatable out mycursor ) is
sqltxt varchar2(4000);
v_sql varchar2(4000);
v_ordertype varchar2(10);
begin
IF OrderType=0 THEN
v_ordertype:='asc';
ELSE
v_ordertype:='desc';
END IF;

 sqltxt:='select * from ' || tblName || ' u where ' || strWhere ||' order by ' || OrderFieldName || ' ' || v_ordertype;
 sqltxt:='select rownum rn,t.* from ('||sqltxt||') t'; 
 v_sql:='select count(*) from('||sqltxt||')';

 execute immediate v_sql into RecoudCount;
 v_sql := 'select '|| SelectFieldName ||' from (' || sqltxt || ') where rn between ' ||start1 || ' and ' || limit1;
 
 open datatable for v_sql;
end proc_AspNetPage;
end pkg_AspNetPage;

CS

[csharp] view plain copy print ?

1. using System; 
2. using System.Collections.Generic; 
3. using System.Configuration; 
4. using System.Data; 
5. using System.Text; 
6. using System.Data.OracleClient; 
7. using System.Collections;        
8. //操作函数 
9. public static readonly string CONN_STRING_Oracle = "Data Source=XXXX;Password=XXXX;User ID=XXXX"; 
10.  
11.         public static OracleConnection conn = new OracleConnection(CONN_STRING_Oracle); 
12.  
13.  
14.         public static void OpenConnection()     
15.         { 
16.             if (conn.State != ConnectionState.Open) 
17.                 conn.Open(); 
18.         } 
19.  
20.         public static void CloseConnection()     
21.         { 
22.             if (conn.State == ConnectionState.Open) 
23.                 conn.Close(); 
24.         } 
25.  
26.  
27. public static DataTable GetPageList_Oracle(string tblName, string selectFieldName, string strWhere, string OrderFieldName, int OrderType, int start, int limit, out int RecoudCount) 
28.         { 
29.  
30.             try 
31.             { 
32.                 OracleParameter[] optr = new OracleParameter[9];  // 初始化参数 
33.  
34.                 optr[0] = new OracleParameter(); 
35.                 optr[0].ParameterName = "tblName"; 
36.                 optr[0].OracleType = OracleType.VarChar; 
37.                 optr[0].Value = tblName; 
38.  
39.                 optr[1] = new OracleParameter(); 
40.                 optr[1].ParameterName = "SelectFieldName"; 
41.                 optr[1].OracleType = OracleType.VarChar; 
42.                 optr[1].Value = selectFieldName; 
43.  
44.                 optr[2] = new OracleParameter(); 
45.                 optr[2].ParameterName = "strWhere"; 
46.                 optr[2].OracleType = OracleType.VarChar; 
47.                 optr[2].Value = strWhere; 
48.  
49.                 optr[3] = new OracleParameter(); 
50.                 optr[3].ParameterName = "OrderFieldName"; 
51.                 optr[3].OracleType = OracleType.VarChar; 
52.                 optr[3].Value = OrderFieldName; 
53.  
54.                 optr[4] = new OracleParameter(); 
55.                 optr[4].ParameterName = "OrderType"; 
56.                 optr[4].OracleType = OracleType.Number; 
57.                 optr[4].Value = OrderType; 
58.  
59.                 optr[5] = new OracleParameter(); 
60.                 optr[5].ParameterName = "start1"; 
61.                 optr[5].OracleType = OracleType.Number; 
62.                 optr[5].Value = start; 
63.  
64.                 optr[6] = new OracleParameter(); 
65.                 optr[6].ParameterName = "limit1"; 
66.                 optr[6].OracleType = OracleType.Number; 
67.                 optr[6].Value = limit; 
68.  
69.                 optr[7] = new OracleParameter(); 
70.                 optr[7].ParameterName = "RecoudCount"; 
71.                 optr[7].OracleType = OracleType.Number; 
72.                 optr[7].Direction = ParameterDirection.Output; 
73.  
74.                 optr[8] = new OracleParameter(); 
75.                 optr[8].ParameterName = "datatable"; 
76.                 optr[8].OracleType = OracleType.Cursor; 
77.                 optr[8].Direction = ParameterDirection.Output; 
78.  
79.                 OracleCommand cmd = new OracleCommand(); 
80.                 cmd.CommandText = "pkg_AspNetPage.proc_AspNetPage"; 
81.                 cmd.CommandType = CommandType.StoredProcedure; 
82.  
83.                 foreach (OracleParameter soptr in optr) 
84.                 { 
85.                     cmd.Parameters.Add(soptr); 
86.                 } 
87.  
88.                 DataTable dt = RunProcedureCmd(cmd);   //执行存储过程 
89.                 RecoudCount = Int32.Parse(optr[7].Value.ToString()); 
90.                 return dt; 
91.  
92.             } 
93.             catch 
94.             { 
95.                 RecoudCount = 0; 
96.                 return null; 
97.             } 
98.         } 
99.  
100.         public static DataTable RunProcedureCmd(OracleCommand cmd) 
101.         { 
102.             DataTable result = new DataTable(); 
103.             try 
104.             { 
105.                 OpenConnection(); 
106.                 cmd.Connection = conn; 
107.                 OracleDataAdapter da = new OracleDataAdapter(cmd); 
108.                 da.SelectCommand.ExecuteNonQuery(); 
109.                 da.Fill(result); 
110.                 return result; 
111.             } 
112.             catch (Exception ex) 
113.             { 
114.                 throw ex; 
115.             } 
116.             finally 
117.             { 
118.                 CloseConnection(); 
119.             } 
120.         } 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;
using System.Data.OracleClient;
using System.Collections;       
//操作函数
 public static readonly string CONN_STRING_Oracle = "Data Source=XXXX;Password=XXXX;User ID=XXXX";

        public static OracleConnection conn = new OracleConnection(CONN_STRING_Oracle);


        public static void OpenConnection()    
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
        }

        public static void CloseConnection()    
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
        }


public static DataTable GetPageList_Oracle(string tblName, string selectFieldName, string strWhere, string OrderFieldName, int OrderType, int start, int limit, out int RecoudCount)
        {

            try
            {
                OracleParameter[] optr = new OracleParameter[9];  // 初始化参数

                optr[0] = new OracleParameter();
                optr[0].ParameterName = "tblName";
                optr[0].OracleType = OracleType.VarChar;
                optr[0].Value = tblName;

                optr[1] = new OracleParameter();
                optr[1].ParameterName = "SelectFieldName";
                optr[1].OracleType = OracleType.VarChar;
                optr[1].Value = selectFieldName;

                optr[2] = new OracleParameter();
                optr[2].ParameterName = "strWhere";
                optr[2].OracleType = OracleType.VarChar;
                optr[2].Value = strWhere;

                optr[3] = new OracleParameter();
                optr[3].ParameterName = "OrderFieldName";
                optr[3].OracleType = OracleType.VarChar;
                optr[3].Value = OrderFieldName;

                optr[4] = new OracleParameter();
                optr[4].ParameterName = "OrderType";
                optr[4].OracleType = OracleType.Number;
                optr[4].Value = OrderType;

                optr[5] = new OracleParameter();
                optr[5].ParameterName = "start1";
                optr[5].OracleType = OracleType.Number;
                optr[5].Value = start;

                optr[6] = new OracleParameter();
                optr[6].ParameterName = "limit1";
                optr[6].OracleType = OracleType.Number;
                optr[6].Value = limit;

                optr[7] = new OracleParameter();
                optr[7].ParameterName = "RecoudCount";
                optr[7].OracleType = OracleType.Number;
                optr[7].Direction = ParameterDirection.Output;

                optr[8] = new OracleParameter();
                optr[8].ParameterName = "datatable";
                optr[8].OracleType = OracleType.Cursor;
                optr[8].Direction = ParameterDirection.Output;

                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "pkg_AspNetPage.proc_AspNetPage";
                cmd.CommandType = CommandType.StoredProcedure;

                foreach (OracleParameter soptr in optr)
                {
                    cmd.Parameters.Add(soptr);
                }

                DataTable dt = RunProcedureCmd(cmd);   //执行存储过程
                RecoudCount = Int32.Parse(optr[7].Value.ToString());
                return dt;

            }
            catch
            {
                RecoudCount = 0;
                return null;
            }
        }

        public static DataTable RunProcedureCmd(OracleCommand cmd)
        {
            DataTable result = new DataTable();
            try
            {
                OpenConnection();
                cmd.Connection = conn;
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.SelectCommand.ExecuteNonQuery();
                da.Fill(result);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

[csharp] view plain copy print ?

1. protected void Store1_RefreshData(object sender, StoreRefreshDataEventArgs e) 
2. { 
3.      int PageSize = this.PagingToolBar1.PageSize; //获取当前在页面中PagingToolBar 的PageSize的值 
4.      int Count = 0; 
5.      int CurPage = e.Start / PageSize + 1; //获取当前的页码是多少，也就是第几页 
6.  
7.      int start = PageSize * (CurPage - 1) + 1; 
8.      int limit = start + PageSize - 1; 
9.  
10.      string strWhere = "1=1"; //编辑条件 
11.      int order=1; //1为倒序，0为顺序 
12.      DataTable dt = new DataTable(); 
13.      dt = Opp.OracleOpp.GetPageList_Oracle("(表名)", "ID,...(字段列表)", strWhere, "ID", order, start, limit, out Count);  
14.      Store1.DataSource = dt; 
15.      Store1.DataBind(); 
16.      e.Total = Count; 
17. }