ASP.NET 2.0: Populating a Datalist using a T-SQL Stored Procedure & Datalist Custom Paging

Posted on July 21, 2008. Filed under: .NET | Tags: , |

Highlights:

– Dataset with 2 tables
– Custom paging on the Datalist
– Stored Procedure returns only the records needed for a page. This is useful when the table has thousands of rows
– Source code in both VB 2005 and C# 2005

Source Code Snippets: VB 2005, C# 2005, T-SQL, web.config

To see the demo, please go to: http://www.jotnow.net

VB 2005:

DataList:

<asp:Literal runat=”server” ID=”litMsg”></asp:Literal>
<asp:DataList runat=”server” ID=”dlStories” EnableViewState=”false”>

<ItemTemplate>

<div class=”storyTitle”><%#DataBinder.Eval(Container.DataItem, “Title”)%></div>
<div class=”storyBy”><b>By: </b><%#DataBinder.Eval(Container.DataItem, “SubmittedBy”)%></div>
<div class=”storyAudience”><b>Category: </b><%#DataBinder.Eval(Container.DataItem, “IntendedAudience”)%></div>
<div class=”storyDate”><b>Date Posted: </b><%#DataBinder.Eval(Container.DataItem, “DateSubmitted”)%> <i>GMT</i>   <span class=”viewed”><b>Viewed</b> <%#DataBinder.Eval(Container.DataItem, “NbrOfViews”)%> times</span></div>
<div class=”storyBodyHead”><b>Story/Jot: </b></div>
<div class=”storyBody”><%#Replace(DataBinder.Eval(Container.DataItem, “Story”), vbCrLf, “<br>”)%>…  <a href='<%#DataBinder.Eval(Container.DataItem, “StoryId”, “full-Story.aspx?i={0}&Story=”)%><%#Server.UrlEncode(DataBinder.Eval(Container.DataItem, “TitleUrl”))%>’><b>Full Story</b></a></div>

<div class=”storySeperater”> </div>

</ItemTemplate>

</asp:DataList>

<div class=”pageNbrs”><asp:Literal runat=”server” ID=”litPageNbrs”></asp:Literal></div>

Populating the Datalist

Imports System
Imports System.Data
Imports System.Data.SqlClient

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
litMsg.Text = “”

If Not IsPostBack Then

Dim strCategory As String = “”
Dim strSearch As String = “”
Dim currentPage As Integer = 0
If Not IsNothing(Request.QueryString(“category”)) Then
If Request.QueryString(“category”).Trim.Length > 0 Then
strCategory = Server.UrlDecode(Request.QueryString(“category”).Trim)
litMsg.Text = “<span class=’msg’>Category = ” & strCategory & “<br /></span>”
End If
End If
If Not IsNothing(Request.QueryString(“search”)) Then
If Request.QueryString(“search”).Trim.Length > 0 Then
strSearch = Server.UrlDecode(Request.QueryString(“search”).Trim)
litMsg.Text &= “<span class=’msg’>Search Phrase = ” & strSearch & “<br /></span>”
End If
End If
If IsNumeric(Request.QueryString(“cp”)) Then
currentPage = Request.QueryString(“cp”).Trim
End If
If litMsg.Text.Trim.Length > 0 Then
litMsg.Text &= “<br />”
End If

Dim ds As DataSet
Dim cn As SqlConnection
Dim da As SqlDataAdapter

cn = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(“snjConnectionString”))
da = New SqlDataAdapter(“usp_get_Stories”, cn)
ds = New DataSet()

Try
With da.SelectCommand
.CommandType = CommandType.StoredProcedure

If strCategory <> “” AndAlso strCategory <> “All” Then
.Parameters.Add(New SqlParameter(“@Category”, SqlDbType.VarChar, 50))
.Parameters(“@Category”).Value = strCategory
End If

If strSearch <> “” Then
.Parameters.Add(New SqlParameter(“@Search”, SqlDbType.VarChar, 50))
.Parameters(“@Search”).Value = strSearch
End If

If currentPage <> 0 Then
.Parameters.Add(New SqlParameter(“@CurrentPage”, SqlDbType.Int))
.Parameters(“@CurrentPage”).Value = currentPage
End If

End With

da.Fill(ds)
If ds.Tables.Count > 0 Then
ds.Tables(0).TableName = “Stories”
ds.Tables(1).TableName = “Stats”
End If

If Not IsNothing(ds.Tables(“Stories”)) Then
dlStories.DataSource = ds.Tables(“Stories”).DefaultView
dlStories.DataBind()
End If

If ds.Tables(“Stats”).Rows.Count > 0 Then
Dim dtRow As DataRow = ds.Tables(“Stats”).Rows(0)
If Not IsDBNull(dtRow(“TotalJots”)) Then Session(“TotalJots”) = dtRow(“TotalJots”)
If Not IsDBNull(dtRow(“TotalViews”)) Then Session(“TotalViews”) = dtRow(“TotalViews”)
If Not IsDBNull(dtRow(“TotalDeleted”)) Then Session(“TotalDeleted”) = dtRow(“TotalDeleted”)
If Not IsDBNull(dtRow(“TotalPages”)) Then
PageList(dtRow(“TotalPages”), strCategory, strSearch, currentPage)
End If
End If

Catch ex As Exception
Response.Write(“<span class=’error’>There’s an unexpected error. Please try again.<br /></span>”)

Finally
cn.Close()
da.Dispose()
ds.Dispose()

End Try

End If

End Sub

Custom Paging:

Private Sub CustomPaging(ByVal pages As Integer, ByVal cat As String, ByVal srch As String, Optional ByVal currpage As Integer = 1)
Dim i As Integer
Dim pageBegin As Integer
Dim pageEnd As Integer
Dim pgNbrs As String = “Pages (” & String.Format(“{0:0,0}”, pages.ToString) & “):  ”
If currpage < 1 Then currpage = 1
pageBegin = currpage – 2
If pageBegin < 1 Then
pageBegin = 1
End If
pageEnd = pageBegin + 4
If pageEnd > pages Then
pageEnd = pages
End If
If pageEnd – pageBegin < 4 Then
pageBegin = pageBegin – (4 – (pageEnd – pageBegin))
End If
If pageBegin < 1 Then
pageBegin = 1
End If
If currpage > 1 Then
pgNbrs &= “<a href='” & “Default.aspx?category=” & cat & “&cp=1&search=” & srch & “‘ title= ‘First Page’><<</a>   ”
pgNbrs &= “<a href='” & “Default.aspx?category=” & cat & “&cp=” & CStr(currpage – 1) & “&search=” & srch & “‘ title=’Previous Page’><</a>  ”
End If
For i = pageBegin To pageEnd
If i <> currpage Then
pgNbrs &= ” <a href='” & “Default.aspx?category=” & cat & “&cp=” & i & “&search=” & srch & “‘>” & i & “” & “</a> ”
Else
pgNbrs &= ” <span class=’selectedPage’> ” & i & ” </span> ”
End If
Next
If pageEnd > currpage Then
pgNbrs &= ”  <a href='” & “Default.aspx?category=” & cat & “&cp=” & CStr(currpage + 1) & “&search=” & srch & “‘ title=’Next Page’>></a>   ”
End If
If currpage <> pages And pages <> 0 Then
pgNbrs &= “<a href='” & “Default.aspx?category=” & cat & “&cp=” & CStr(pages) & “&search=” & srch & “‘ title=’Last Page’>>></a>”
End If
litPageNbrs.Text = pgNbrs
End Sub

C# 2005:

DataList:

<asp:Literal runat=”server” ID=”litMsg”></asp:Literal>
<asp:DataList runat=”server” ID=”dlStories” EnableViewState=”false”>

<ItemTemplate>

<div class=”storyTitle”><%#DataBinder.Eval(Container.DataItem, “Title”)%></div>
<div class=”storyBy”><b>By: </b><%#DataBinder.Eval(Container.DataItem, “SubmittedBy”)%></div>
<div class=”storyAudience”><b>Category: </b><%#DataBinder.Eval(Container.DataItem, “IntendedAudience”)%></div>
<div class=”storyDate”><b>Date Posted: </b><%#DataBinder.Eval(Container.DataItem, “DateSubmitted”)%> <i>GMT</i>   <span class=”viewed”><b>Viewed</b> <%#DataBinder.Eval(Container.DataItem, “NbrOfViews”)%> times</span></div>
<div class=”storyBodyHead”><b>Story/Jot: </b></div>
<div class=”storyBody”><%#DataBinder.Eval(Container.DataItem, “Story”)%>…  <a href=’full-story.aspx?storyId=<%#DataBinder.Eval(Container.DataItem, “StoryId”)%>’><b>Full Story</b></a></div>

<div class=”storySeperater”> </div>

</ItemTemplate>

</asp:DataList>

<div class=”pageNbrs”><asp:Literal runat=”server” ID=”litPageNbrs”></asp:Literal></div>

Populating the Datalist

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
litMsg.Text = “”;

if (!IsPostBack)
{
string strCategory = “”;
string strSearch = “”;
int currentPage = 0;
if (Request.QueryString[“category”] != null)
{
if (Request.QueryString[“category”].Trim().Length > 0)
{
strCategory = Server.UrlDecode(Request.QueryString[“category”].Trim());
litMsg.Text = “<span class=’msg’>Category = ” + strCategory + “<br /></span>”;
}
}
if (Request.QueryString[“search”] != null)
{
if (Request.QueryString[“search”].Trim().Length > 0)
{
strSearch = Server.UrlDecode(Request.QueryString[“search”].Trim());
litMsg.Text += “<span class=’msg’>Search Phrase = ” + strSearch + “<br /></span>”;
}
}
int result;
if (int.TryParse(Request.QueryString[“cp”], out result))
{
currentPage = int.Parse(Request.QueryString[“cp”].Trim());
}
if (litMsg.Text.Trim().Length > 0)
{
litMsg.Text += “<br />”;
}

DataSet ds = null;
SqlConnection cn = null;
SqlDataAdapter da = null;

cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings[“snjConnectionString”]);
da = new SqlDataAdapter(“usp_get_Stories”, cn);
ds = new DataSet();

try
{
da.SelectCommand.CommandType = CommandType.StoredProcedure;

if (strCategory != “” && strCategory != “All”)
{
da.SelectCommand.Parameters.Add(new SqlParameter(“@Category”, SqlDbType.VarChar, 50));
da.SelectCommand.Parameters[“@Category”].Value = strCategory;
}

if (strSearch != “”)
{
da.SelectCommand.Parameters.Add(new SqlParameter(“@Search”, SqlDbType.VarChar, 50));
da.SelectCommand.Parameters[“@Search”].Value = strSearch;
}

if (currentPage != 0)
{
da.SelectCommand.Parameters.Add(new SqlParameter(“@CurrentPage”, SqlDbType.Int));
da.SelectCommand.Parameters[“@CurrentPage”].Value = currentPage;
}

da.Fill(ds);
if (ds.Tables.Count > 0)
{
ds.Tables[0].TableName = “Stories”;
ds.Tables[1].TableName = “Stats”;
}

if (ds.Tables[“Stories”] != null)
{
dlStories.DataSource = ds.Tables[“Stories”].DefaultView;
dlStories.DataBind();
}

if (ds.Tables[“Stats”].Rows.Count > 0)
{
DataRow dtRow = ds.Tables[“Stats”].Rows[0];
if (!(System.Convert.IsDBNull(dtRow[“TotalJots”])))
{
Session[“TotalJots”] = dtRow[“TotalJots”];
}
if (!(System.Convert.IsDBNull(dtRow[“TotalViews”])))
{
Session[“TotalViews”] = dtRow[“TotalViews”];
}
if (!(System.Convert.IsDBNull(dtRow[“TotalDeleted”])))
{
Session[“TotalDeleted”] = dtRow[“TotalDeleted”];
}
if (!(System.Convert.IsDBNull(dtRow[“TotalPages”])))
{
int totPages;
//totPages = int.Parse(dtRow[“TotalPages”].ToString);
totPages = (int)dtRow[“TotalPages”];
CustomPaging(totPages, strCategory, strSearch, currentPage);
}
}

}
catch
{
Response.Write(“<span class=’error’>There’s an unexpected error. Please try again.<br /></span>”);

}
finally
{
cn.Close();
da.Dispose();
ds.Dispose();

}

}

}

Custom Paging:

private void CustomPaging(int pages, string cat, string srch)
{
CustomPaging(pages, cat, srch, 1);
}

private void CustomPaging(int pages, string cat, string srch, int currpage)
{
int i = 0;
int pageBegin = 0;
int pageEnd = 0;
string pgNbrs = “Pages (” + string.Format(“{0:0,0}”, pages.ToString()) + “):  “;
if (currpage < 1)
{
currpage = 1;
}
pageBegin = currpage – 2;
if (pageBegin < 1)
{
pageBegin = 1;
}
pageEnd = pageBegin + 4;
if (pageEnd > pages)
{
pageEnd = pages;
}
if (pageEnd – pageBegin < 4)
{
pageBegin = pageBegin – (4 – (pageEnd – pageBegin));
}
if (pageBegin < 1)
{
pageBegin = 1;
}
if (currpage > 1)
{
pgNbrs += “<a href='” + “Default.aspx?category=” + cat + “&cp=1&search=” + srch + “‘ title= ‘First Page’><<</a>   “;
pgNbrs += “<a href='” + “Default.aspx?category=” + cat + “&cp=” + System.Convert.ToString(currpage – 1) + “&search=” + srch + “‘ title=’Previous Page’><</a>  “;
}
for (i = pageBegin; i <= pageEnd; i++)
{
if (i != currpage)
{
pgNbrs += ” <a href='” + “Default.aspx?category=” + cat + “&cp=” + i + “&search=” + srch + “‘>” + i + “” + “</a> “;
}
else
{
pgNbrs += ” <span class=’selectedPage’> ” + i + ” </span> “;
}
}
if (pageEnd > currpage)
{
pgNbrs += ”  <a href='” + “Default.aspx?category=” + cat + “&cp=” + System.Convert.ToString(currpage + 1) + “&search=” + srch + “‘ title=’Next Page’>></a>   “;
}
if (currpage != pages & pages != 0)
{
pgNbrs += “<a href='” + “Default.aspx?category=” + cat + “&cp=” + System.Convert.ToString(pages) + “&search=” + srch + “‘ title=’Last Page’>>></a>”;
}
litPageNbrs.Text = pgNbrs;
}

}

Stored Procedure:

CREATE PROCEDURE dbo.usp_get_Stories
@Category varchar(50) = NULL,
@Search varchar(50) = NULL,
@CurrentPage int = NULL
AS
BEGIN

SET NOCOUNT ON;

declare @pageSize int
set @pageSize = 15

declare @totalJots numeric(18,0), @totalViews numeric(18,0), @totalDeleted numeric(18,0)
declare @avJots int, @totalPages int

/*========================================
“Stories”
=========================================*/
if @CurrentPage is null
set @CurrentPage = 1

select Identity(int,1,1) as thisId,
cast(StoryId as int) as StoryId, –‘cos can’t insert another identity
Title,
TitleUrl = Replace(Replace(Replace(Replace(Replace(Title, ‘ ‘, ‘-‘), ‘&’, ”), ‘,’, ”), ””, ”), ‘?’, ”),
SubmittedBy,
IntendedAudience,
Keywords,
Story = Replace(Replace(cast(Story as varchar(200)), ”, ”), ”, ”),
WebsiteName,
DateSubmitted = convert(varchar, DateSubmitted, 100),
NbrOfViews = isnull(NbrOfViews, 0)
into #stories
from Stories
where Cast(IsApprove as int) = 1 and isnull(IsDelete, 0) = 0
order by StoryId desc

if @Category is null and @Search is null
BEGIN
select @avJots = count(*) –@avJots = available jots for this search
from #stories

select top 15 *    –instead of using exec, 15 is hardcoded here
from #stories
where thisId > (@CurrentPage – 1) * @pageSize
order by thisId
END
else if @Category is not null and @Search is null
BEGIN

select @avJots = count(*)
from #stories
where IntendedAudience like ‘%’ + @Category + ‘%’

select top 15 *
from #stories
where thisId > (@CurrentPage – 1) * @pageSize
and IntendedAudience like ‘%’ + @Category + ‘%’
order by thisId
END
else if @Category is null and @Search is not null
BEGIN

select @avJots = count(*)
from #stories
where (Title like ‘%’ + @Search + ‘%’
or SubmittedBy like ‘%’ + @Search + ‘%’
or IntendedAudience like ‘%’ + @Search + ‘%’
or Keywords like ‘%’ + @Search + ‘%’
or Story like ‘%’ + @Search + ‘%’
or WebsiteName like ‘%’ + @Search + ‘%’
)

select top 15 *
from #stories
where thisId > (@CurrentPage – 1) * @pageSize
and (Title like ‘%’ + @Search + ‘%’
or SubmittedBy like ‘%’ + @Search + ‘%’
or IntendedAudience like ‘%’ + @Search + ‘%’
or Keywords like ‘%’ + @Search + ‘%’
or Story like ‘%’ + @Search + ‘%’
or WebsiteName like ‘%’ + @Search + ‘%’
)
order by thisId
END
else if @Category is not null and @Search is not null
BEGIN

select @avJots = count(*)
from #stories
where IntendedAudience like ‘%’ + @Category + ‘%’
and (Title like ‘%’ + @Search + ‘%’
or SubmittedBy like ‘%’ + @Search + ‘%’
or IntendedAudience like ‘%’ + @Search + ‘%’
or Keywords like ‘%’ + @Search + ‘%’
or Story like ‘%’ + @Search + ‘%’
or WebsiteName like ‘%’ + @Search + ‘%’
)

select top 15 *
from #stories
where thisId > (@CurrentPage – 1) * @pageSize
and IntendedAudience like ‘%’ + @Category + ‘%’
and (Title like ‘%’ + @Search + ‘%’
or SubmittedBy like ‘%’ + @Search + ‘%’
or IntendedAudience like ‘%’ + @Search + ‘%’
or Keywords like ‘%’ + @Search + ‘%’
or Story like ‘%’ + @Search + ‘%’
or WebsiteName like ‘%’ + @Search + ‘%’
)
order by thisId
END

/*========================================
“Stats”
==========================================*/
select @totalJots = count(*),
@totalViews = sum(isnull(NbrOfViews, 0))
from Stories
where Cast(IsApprove as int) = 1 and isnull(IsDelete, 0) = 0

select @totalDeleted = count(*)
from Stories
where isnull(IsDelete, 0) = 1

if @avJots % @pageSize = 0  –@avJots = available jots from above
set @totalPages = @avJots / @pageSize
else
set @totalPages = (@avJots / @pageSize) + 1

select TotalJots = @totalJots,
TotalViews = @totalViews,
TotalDeleted = @totalDeleted,
TotalPages = @totalPages

SET NOCOUNT OFF;

END

web.config:

<?xml version=”1.0″?>
<configuration>
<appSettings>
<add key=”snjConnectionString” value=”Data Source=(server name);Initial Catalog=(database name);Integrated Security=True;”/>
</appSettings>
<system.web>


</system.web>


</configuration>

To see the demo, please go to: http://www.jotnow.net

Advertisements
Read Full Post | Make a Comment ( None so far )

    About

    My stuff about .NET, C#, ASP.NET, AJAX, Visual Studio, SQL Server, XML, Web Services, WCF, WPF, WF, CRM, etc.

    RSS

    Subscribe Via RSS

    • Subscribe with Bloglines
    • Add your feed to Newsburst from CNET News.com
    • Subscribe in Google Reader
    • Add to My Yahoo!
    • Subscribe in NewsGator Online
    • The latest comments to all posts in RSS

    Meta

Liked it here?
Why not try sites on the blogroll...