Hi folks
Can someone redirect me to your own blog link or any other link explaining complete tutorial about how to implement gridview custom paging using stored procedure?
My search in google giving me old articles from year 2006. Right now I am using asp.net 3.5 and c#.
Since SQL's syntax hasn't changes much since 2006, there isn't much to update. On the stored procedure side you would still do a ROW_Number() call.
With table AS
( SELECT ID, NAME,
ROW_NUMBER() OVER (order by Name) as RowNumber
FROM Customers )
select *
from table
Where RowNumber >= @RowStart and RowNumber <= @RowEnd end
.aspx page
<asp:DropDownList ID="ddlSelector" AutoPostBack="true" runat="server"
onselectedindexchanged="ddlSelector_SelectedIndexChanged" >
<asp:ListItem>Employee</asp:ListItem>
<asp:ListItem>Customer</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click" Text="Search" />
<br />
<table>
<tr>
<td class="txtclmn">
First Name:</td>
<td>
<asp:TextBox ID="txtFname" runat="server"></asp:TextBox>
</td>
<td class="txtclmn">
Last Name:</td>
<td>
<asp:TextBox ID="txtLname" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="txtclmn">
Middle Name:</td>
<td>
<asp:TextBox ID="txtMname" runat="server"></asp:TextBox>
</td>
<td class="txtclmn">
Phone Number:</td>
<td>
<asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="txtclmn">
Email Address:</td>
<td>
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
</td>
<td class="txtclmn">
</td>
<td>
</td>
</tr>
</table>
<div runat="server" id="empCriteria">
<table >
<tr>
<td class="txtclmn">
<asp:Label ID="lblHdate" runat="server" Text="Hire Date:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtHMonth" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
<asp:Label ID="lblHdash1" runat="server" Text="-"></asp:Label>
<asp:TextBox ID="txtHDay" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
<asp:Label ID="lblHdash2" runat="server" Text="-"></asp:Label>
<asp:TextBox ID="txtHYear" runat="server" MaxLength="4" CssClass="input4char"></asp:TextBox>
</td>
<td class="txtclmn">
<asp:Label ID="lblBdate" runat="server" Text="Birth Date:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtBMonth" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
<asp:Label ID="lblBdash1" runat="server" Text="-"></asp:Label>
<asp:TextBox ID="txtBDay" runat="server" MaxLength="2" CssClass="input2char"></asp:TextBox>
<asp:Label ID="lblBdash2" runat="server" Text="-"></asp:Label>
<asp:TextBox ID="txtBYear" runat="server" MaxLength="4" CssClass="input4char"></asp:TextBox>
</td>
</tr>
<tr>
<td class="txtclmn">
<asp:Label ID="lblLoginId" runat="server" Text="Login ID:"></asp:Label>
</td>
<td>
<asp:TextBox ID="txtLogin" runat="server"></asp:TextBox>
</td>
<td class="txtclmn">
<asp:Label ID="lblJobTitle" runat="server" Text="Job Title:"></asp:Label></td>
<td>
<asp:TextBox ID="txtJobTitle" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="txtclmn">
<asp:Label ID="lblGender" runat="server" Text="Gender:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlGender" runat="server" style="width: 73px">
<asp:ListItem Value="-1">-----------</asp:ListItem>
<asp:ListItem Value="M">Male</asp:ListItem>
<asp:ListItem Value="F">Female</asp:ListItem>
</asp:DropDownList>
</td>
<td class="txtclmn">
<asp:Label ID="lblMarStat" runat="server" Text="Marital Status:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlMarStat" runat="server">
<asp:ListItem Value="-1">-----------</asp:ListItem>
<asp:ListItem Value="S">Single</asp:ListItem>
<asp:ListItem Value="M">Married</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="txtclmn">
<asp:Label ID="lblActive" runat="server" Text="Active:"></asp:Label>
</td>
<td>
<asp:DropDownList ID="ddlActive" runat="server">
<asp:ListItem Value="-1">-----</asp:ListItem>
<asp:ListItem Value="1">Yes</asp:ListItem>
<asp:ListItem Value="0">No</asp:ListItem>
</asp:DropDownList>
</td>
<td class="txtclmn">
<asp:Label ID="lblSalary" runat="server" Text="Salaried:"></asp:Label></td>
<td>
<asp:DropDownList ID="ddlSalary" runat="server">
<asp:ListItem Value="-1">-----</asp:ListItem>
<asp:ListItem Value="1">Yes</asp:ListItem>
<asp:ListItem Value="0">No</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</div>
<br />
<br />
<asp:GridView ID="gdvCust" runat="server" AllowPaging="True"
AutoGenerateColumns="False"
onpageindexchanging="gdvCust_PageIndexChanging">
<Columns>
<asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True"
SortExpression="ContactID" />
<asp:BoundField DataField="Title" HeaderText="Title" ReadOnly="True"
SortExpression="Title" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True"
SortExpression="FirstName" />
<asp:BoundField DataField="MiddleName" HeaderText="MiddleName" ReadOnly="True"
SortExpression="MiddleName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True"
SortExpression="LastName" />
<asp:BoundField DataField="Suffix" HeaderText="Suffix" ReadOnly="True"
SortExpression="Suffix" />
<asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress"
ReadOnly="True" SortExpression="EmailAddress" />
<asp:BoundField DataField="Phone" HeaderText="Phone" ReadOnly="True"
SortExpression="Phone" />
<asp:ButtonField ButtonType="Button" CommandName="Edit" Text="Edit" />
</Columns>
</asp:GridView>
<br />
<asp:GridView ID="gdvEmp" runat="server" AllowPaging="True"
AutoGenerateColumns="False"
onpageindexchanging="gdvEmp_PageIndexChanging">
<Columns>
<asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="True"
SortExpression="EmployeeID" />
<asp:BoundField DataField="JobTitle" HeaderText="Job Title" ReadOnly="True"
SortExpression="Title" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName" ReadOnly="True"
SortExpression="FirstName" />
<asp:BoundField DataField="MiddleName" HeaderText="MiddleName" ReadOnly="True"
SortExpression="MiddleName" />
<asp:BoundField DataField="LastName" HeaderText="LastName" ReadOnly="True"
SortExpression="LastName" />
<asp:BoundField DataField="Suffix" HeaderText="Suffix" ReadOnly="True"
SortExpression="Suffix" />
<asp:BoundField DataField="EmailAddress" HeaderText="EmailAddress"
ReadOnly="True" SortExpression="EmailAddress" />
<asp:BoundField DataField="Phone" HeaderText="Phone" ReadOnly="True"
SortExpression="Phone" />
<asp:BoundField DataField="LoginID" HeaderText="LoginID" ReadOnly="True"
SortExpression="LoginID" />
<asp:BoundField DataField="Title" HeaderText="Title" ReadOnly="True"
SortExpression="Title" />
<asp:BoundField DataField="BirthDate" HeaderText="BirthDate" ReadOnly="True"
SortExpression="BirthDate" />
<asp:BoundField DataField="MaritalStatus" HeaderText="MaritalStatus" ReadOnly="True"
SortExpression="MaritalStatus" />
<asp:BoundField DataField="Gender" HeaderText="Gender" ReadOnly="True"
SortExpression="Gender" />
<asp:BoundField DataField="HireDate" HeaderText="HireDate"
ReadOnly="True" SortExpression="HireDate" />
<asp:CheckBoxField DataField="SalariedFlag" HeaderText="SalariedFlag"
ReadOnly="True" SortExpression="SalariedFlag" />
<asp:CheckBoxField DataField="CurrentFlag" HeaderText="CurrentFlag"
ReadOnly="True" SortExpression="CurrentFlag" />
</Columns>
</asp:GridView>
</form>
codebehind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
namespace TestProj
{
public partial class Search : System.Web.UI.Page
{
static IQueryable<Contact> _conResults;
//static IQueryable<EmpContact> _empResults;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ddlSelector_SelectedIndexChanged(object sender, EventArgs e)
{
gdvCust.DataSource = null;
gdvCust.DataBind();
gdvEmp.DataSource = null;
gdvEmp.DataBind();
bool flag;
if (ddlSelector.SelectedValue == "Employee")
flag = true;
else
{
flag = false;
}
foreach( Control c in empCriteria.Controls)
{
c.Visible = flag;
}
}
private IQueryable<Contact> CreateCustQuery()
{
TestDataClassDataContext dc = new TestDataClassDataContext();
var predicate = PredicateBuilder.True<Contact>();
var cust = from individual in dc.Individuals
join contact in dc.Contacts on individual.ContactID equals contact.ContactID
select contact;
if (!string.IsNullOrEmpty(txtLname.Text))
predicate = predicate.And( e => e.LastName.Contains(txtLname.Text));
if (!string.IsNullOrEmpty(txtFname.Text))
predicate = predicate.And(e => e.FirstName.Contains(txtFname.Text));
if (!string.IsNullOrEmpty(txtMname.Text))
predicate = predicate.And(e => e.MiddleName.Contains(txtMname.Text));
if (Utility.IsValidPhone(txtPhone.Text))
predicate = predicate.And(e => e.Phone.Contains(txtPhone.Text));
if (Utility.IsValidEmailAddress(txtEmail.Text))
predicate = predicate.And(e => e.EmailAddress.Contains(txtEmail.Text));
var results = cust.Where(predicate);
return results;
}
//private void CreateEmpQuery()
//{
// TestDataClassDataContext dc = new TestDataClassDataContext();
// var emp = from c in dc.Contacts
// from e in dc.Employees
// where c.ContactID == e.ContactID
// select new
// {
// FirstName = c.FirstName,LastName = c.LastName, MiddleName =c.MiddleName, Phone = c.Phone,
// EmailAddress = c.EmailAddress
// };
// var predicate = PredicateBuilder.True<>();
// if (!string.IsNullOrEmpty(txtLname.Text))
// predicate = predicate.And(e => e.LastName.Contains(txtLname.Text));
// if (!string.IsNullOrEmpty(txtFname.Text))
// predicate = predicate.And(e => e.FirstName.Contains(txtFname.Text));
// if (!string.IsNullOrEmpty(txtMname.Text))
// predicate = predicate.And(e => e.MiddleName.Contains(txtMname.Text));
// if (Utility.IsValidPhone(txtPhone.Text))
// predicate = predicate.And(e => e.Phone.Contains(txtPhone.Text));
// if (Utility.IsValidEmailAddress(txtEmail.Text))
// predicate = predicate.And(e => e.EmailAddress.Contains(txtEmail.Text));
// var results = emp.Where(predicate);
//}
private void GetCustResults()
{
_conResults = CreateCustQuery();
gdvCust.DataSource = _conResults;
gdvCust.DataBind();
}
//private void GetEmpResults()
//{
// _empResults = CreateEmpQuery();
// gdvEmp.DataSource = _empResults;
// gdvEmp.DataBind();
//}
protected void gdvCust_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gdvCust.PageIndex = e.NewPageIndex;
gdvCust.DataSource = _conResults;
gdvCust.DataBind();
}
//protected void gdvEmp_PageIndexChanging(object sender, GridViewPageEventArgs e)
//{
// gdvCust.PageIndex = e.NewPageIndex;
// gdvEmp.DataSource = _empResults;
// gdvEmp.DataBind();
//}
protected void btnSearch_Click(object sender, EventArgs e)
{
if (ddlSelector.SelectedValue == "Customer")
GetCustResults();
//else
//GetEmpResults();
}
}
}
only finished the cust results before I moved on to another project I was using linq at the time but this might help you understand what is needed to get a gridview going. this is using linq with AdventureWorks as a sample database
Explanation of code now what you need to pay attention to is the
<asp:GridView ID="gdvCust" runat="server" AllowPaging="True"
AutoGenerateColumns="False"
onpageindexchanging="gdvCust_PageIndexChanging">
and the columns and bound fields on the aspx page where DataField = Name of column in database and the Header field is what is labeled at the top of the column on the gridview.
now on the code behind look at
private void GetCustResults()
{
_conResults = CreateCustQuery();
gdvCust.DataSource = _conResults;
gdvCust.DataBind();
}
//and
protected void gdvCust_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gdvCust.PageIndex = e.NewPageIndex;
gdvCust.DataSource = _conResults;
gdvCust.DataBind();
}
they do the binding from a data source and the paging control. comment if you need some more explaination