c#调用存储过程

下面是分页存储过程：

create proc [dbo].[proc_value_page]
@pageIndex int,
@pageSize  int,
@pageCount int output
as
declare @totalRecord int
select @totalRecord=count(*) from dbo.spt_values
set @pageCount=ceiling(@totalRecord*1.0/@pageSize)
select * from
(select row_number() over(order by number asc) as num,* from dbo.spt_values)AS t
    where    t.num  between(@pageIndex-1)*@pageSize and @pageIndex*@pageSize
    order by t.num asc

下面是后台：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace WebApplication2
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        int pageIndex = 1;
        int pageSize = 10;
        public int pageCount;
        protected void Page_Load(object sender, EventArgs e)
        {          

            if (!IsPostBack)
            {
                Bind(pageIndex, pageSize);

            }

        }
        public void Bind(int pageIndex, int pageSize)
        {
            repeater1.DataSource = GetDataSource(pageIndex, pageSize);
            repeater1.DataBind();
        }
        protected void PrePage(object sender, EventArgs e)
        {

            pageIndex -= 1;
            Bind(pageIndex, pageSize);

        }
        protected void NextPage(object sender, EventArgs e)
        {
            pageIndex += 1;
            Bind(pageIndex, pageSize);
        }


        public DataSet GetDataSource(int pageIndex, int pageSize)
        {
            SqlConnection conn = new SqlConnection(@"database=master;server=.;uid=sa;pwd=123456;");
            conn.Open();
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "proc_value_page";
            comm.CommandType = System.Data.CommandType.StoredProcedure;
       

            List<SqlParameter> list = new List<SqlParameter>()
                                   {  new SqlParameter("@pageIndex",SqlDbType.Int),
                                      new SqlParameter("@pageSize",SqlDbType.Int),
                                      new SqlParameter("@pageCount",SqlDbType.Int) };

            list[0].Value = pageIndex;
            list[1].Value = pageSize;
            list[2].Direction = ParameterDirection.Output;
            comm.Parameters.AddRange(list.ToArray());
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();          

            adapter.Fill(ds);
            //必须在Fill之后调用
            Label1.Text = list[2].Value.ToString();
            return ds;

        }




        protected void Page_Error(object sender, EventArgs e)
        {

            Exception ex = Server.GetLastError();

            string errMsg = ex.Message;
            string source = ex.Source;
            Response.Write(string.Format("错误信息:{0},源地址{1}", errMsg, source));
            HttpContext.Current.Server.ClearError();
        }
    }
}

 

下面是页面：

View Code 
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="WebApplication2.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <asp:Repeater ID="repeater1" runat="server" >
        <ItemTemplate>
        <tr>
            
            <td><%#Eval("name") %></td>
            <td><%#Eval("number") %></td>
            <td><%#Eval("type") %></td>
        </tr> 
        </ItemTemplate> 
        <FooterTemplate runat="server">
        <asp:Button ID="btnPrePage" runat="server" Text="上一页" OnClick="PrePage"></asp:Button>
        <asp:Button ID="btnNextPage" runat="server" Text="下一页" OnClick="NextPage"></asp:Button>
        <asp:Panel runat="server">跳到</asp:Panel><asp:TextBox runat="server" ID="tbPageIndex"></asp:TextBox>
        <asp:Button runat="server" ID="btnPage" Text="调到"  />
        </FooterTemplate>      
        </asp:Repeater>       
    </table>
     
    总页<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

 

 

posted @ 2012-11-22 21:23  金河  阅读(964)  评论(0)  编辑  收藏  举报