Vinit Patel

Create Pager with Alphabets from A-Z, Create Pager with Alphabets available in SQL Server Table, Create Alphabetic Pager with Stored Procedure

Posted on: September 25, 2009


Create Pager with Alphabets from A-Z

private void CreateAlphaPagings()
{
DataTable dt = new DataTable();
dt.Columns.Add(“PageIndex”);
dt.Columns.Add(“PageText”);
if (this.ViewState[“Paging”] == null)
{
for (int i = 65; i <= 90; i++)
{
DataRow dr = dt.NewRow();
dr[0] = Char.ConvertFromUtf32(i);
dr[1] = Char.ConvertFromUtf32(i);
dt.Rows.Add(dr);
}
DataRow drNew = dt.NewRow();
drNew[“PageIndex”] = “All”;
drNew[“PageText”] = “All”;
dt.Rows.Add(drNew);
this.ViewState[“Paging”] = dt;
}
else
dt = (DataTable)this.ViewState[“Paging”];
DataList1.DataSource = dt;
DataList1.DataBind();
}

Bind Gridview

private void BindGrid(string StartAlpha)
{
string sql = “”;
if (StartAlpha == “All”)
sql = “Select * from Customers Order By Cus_Code Desc”;
else
sql = “Select * from Customers Where Cus_Name Like ‘” + StartAlpha
+ “%’ Order By Cus_Code Desc “;

SqlDataAdapter da = new SqlDataAdapter(sql, “YourConnectionString”);
DataTable dtSelect = new DataTable();
da.Fill(dtSelect);
GridView1.DataSource = dtSelect;
GridView1.DataBind();
}

Page Load Event

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.ViewState[“SelectedText”] = “All”;
CreateAlphaPagings();
BindGrid(this.ViewState[“SelectedText”].ToString());
}
}

DataList ItemCommand Event

BindGrid

Protected void Datalist1_ItemCommand(object source, DataListCommandEventArgs e ) { LinkButton lbkbtnPaging = (LinkButton)e.CommandSource;

BindGrid(e.CommandArgument.ToString());

this.ViewState[“SelectedText”] = e.CommandArgument.ToString();

CreateAlphaPagings();

}

DataList ItemDataBound Event

protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.AlternatingItem
|| e.Item.ItemType == ListItemType.Item)
{
if (this.ViewState[“SelectedText”] != null)
{
LinkButton lbkbtnPaging = (LinkButton)e.Item.FindControl(“lnkbtnPaging”);
if (this.ViewState[“SelectedText”].ToString() == lbkbtnPaging.Text)
lbkbtnPaging.Enabled = false;
}
}
}

Create Pager with Alphabets available in SQL Server Table

private void CreatePagingsWithFirstLetter()
{
DataTable dtSelect = new DataTable();
if (this.ViewState[“Paging”] == null)
{
SqlDataAdapter da = new SqlDataAdapter(“Select Distinct Upper(Left(Cus_Name,1))
[PageText], Upper(Left(Cus_Name,1)) [PageIndex] from Customers Order By
PageText Asc”, “YourConnectionString”);

da.Fill(dtSelect);

DataRow drNew = dtSelect.NewRow();
drNew[“PageIndex”] = “All”;
drNew[“PageText”] = “All”;
dtSelect.Rows.Add(drNew);

this.ViewState[“Paging”] = dtSelect;
}
else
dtSelect = (DataTable)this.ViewState[“Paging”];

DataList1.DataSource = dtSelect;
DataList1.DataBind();
}

Create Alphabetic Pager with Stored Procedure

Create Proc Proc_Paging(@OptionId Int) As  
Begin  
	Create Table #Paging (PageIndex VarChar(3), PageText VarChar(3)) 	

	If (@OptionId=1)
	Begin
		Declare @StartIndex Int, @EndIndex Int, @Incr Int  
		Select @StartIndex = Ascii('A')  
		Select @EndIndex = Ascii('Z')  
		Select @Incr = @StartIndex  
		While (@EndIndex >= @Incr )  
		Begin  
			Insert Into #Paging  
			Select Char(@Incr), Char(@Incr)  
			Select @Incr = @Incr+1  
		End  
	End
	Else
	begin
		Insert Into #Paging
		Select Distinct Upper(Left(Cus_Name,1)) [PageIndex], 
		Upper(Left(Cus_Name,1))[PageText] 
		from Customers Order By PageText Asc
	End

	Insert Into #Paging Values ('All', 'All')  
	Select * from #Paging 
End

The Stored Procedure takes a parameter @OptionId to decide the type output. If the parameter @OptionId is 1, 
it will return all alphabets from A to Z. Otherwise it will return only the first letter of the Customer Name 
column in the Customer Table.                             
 
To create the pager, we have to bind the Stored Procedure with the DataList control with the following code.

private void CreateAlphaPagings()                                         
 {                                         
 DataTable dt = new DataTable(); 
 if (this.ViewState["Paging"] == null) 
 { 
 SqlDataAdapter da = new SqlDataAdapter("Proc_Paging", "YourConnectionString"); 
 da.SelectCommand.CommandType = CommandType.StoredProcedure; 
 da.SelectCommand.Parameters.Add("@OptionId", SqlDbType.Int).Value                                         = 2; 
 da.Fill(dt); 
 this.ViewState["Paging"] = dt; 
 } 
 else 
 dt = (DataTable)this.ViewState["Paging"]; 
 
 DataList1.DataSource = dt; 
 DataList1.DataBind();                                         
 }
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Calendar

September 2009
M T W T F S S
« Jul   Oct »
 123456
78910111213
14151617181920
21222324252627
282930  

Stats Of Blog

  • 6,824 hits

Cluster Map

%d bloggers like this: