分頁sql寫在 stored procedure
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" PageSize="3" AllowPaging="True" DataSourceID="ObjectDataSource1" >
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
</Columns>
</asp:GridView>
</div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="PageEmployee"
TypeName="WebApplication8.EmployeeDataAccessLayer"
StartRowIndexParameterName="StartRow"
MaximumRowsParameterName="PageSize"
EnablePaging="true"
SelectCountMethod="EmployeeCount"
>
</asp:ObjectDataSource>
</form>
public static List<Employee> PageEmployee(int StartRow, int PageSize)
{
string cs = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;
using(SqlConnection conn = new SqlConnection(cs))
{
List<Employee> emp_list = new List<Employee>();
conn.Open();
SqlCommand cmd = new SqlCommand("Search_Employee", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter StartIndex = new SqlParameter();
StartIndex.ParameterName = "@StartRow";
StartIndex.Value = StartRow;
cmd.Parameters.Add(StartIndex);
SqlParameter size = new SqlParameter();
size.ParameterName = "@PageSize";
size.Value = PageSize;
cmd.Parameters.Add(size);
SqlDataReader dr = cmd.ExecuteReader();
while(dr.Read())
{
Employee emp = new Employee();
emp.EmployeeID = Convert.ToInt32(dr["EmployeeID"].ToString());
emp.FirstName = dr["FirstName"].ToString();
emp.LastName = dr["LastName"].ToString();
emp.City = dr["City"].ToString();
emp_list.Add(emp);
}
return emp_list;
}
}
public static int EmployeeCount()
{
string cs = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(cs))
{
conn.Open();
string sql = "SELECT COUNT(*) FROM Employees ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
return (int)cmd.ExecuteScalar();
}
}
sp
CREATE PROCEDURE [dbo].[Search_Employee] 0,3
@StartRow int,
@PageSize int
AS
BEGIN
SELECT * FROM
(SELECT RowNum = ROW_NUMBER() OVER (ORDER BY EmployeeID),* FROM Employees ) A
WHERE RowNum > @StartRow AND RowNum <= @StartRow + @PageSize
END