E:\web\learnaspcom\htdocs\freebook\learn\ubtoc.xml LearnAsp.com - ASP ASP.net Free Lessons
Search Search

#1 worldwide
FREE Coding Lessons
since 1996
   THE BEST WAY to learn ASP & Asp.net!
Advertise Here!
click for details 		Credits Host:
DiscountASP.net 		Server Admin:
The "Team" 		Contact Info.
Charles M. Carroll 		<Asp.net blog>
<personal site>
xxx

Utility Belt: Caching Demo SQLserver
    by Charles Carroll (some help by Jeff Schoolcraft, Steve Walther and Dave Wantas)

To make applications faster and provide an added benefit to using UtilityBelt all functions internally use DataTables to fetch data from the database support caching in an easy transparent fashion by setting properties instead of changing the actual library calls.

The reason that caching can be done easily is the str2md5checksum facility. The goal of that function is to take parameter strings that contain connection and SQL statements and convert them into checksums.

For example:
select distinct zip from publishers
PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=D:\domains\stage.learnasp.com\experiments\data\biblio.mdb;
can be represented as the checksum:
 41-4D-52-B5-E3-01-4B-C3-80-E3-AA-D8-5F-50-58-E9

which unlike the above checksum could be used to name a cache without having to escape any characters or truncate the string.

The Cached Search Code is one demo of Database Caching "Utility Belt" functionality.

   filename=/experiments/utilitybelt/vercurrent/ubdemo_search_cached_sqlclient.aspx

<Test Script Below>

 

<%@ debug="true" %>
<%@ Assembly src="utilitybelt.vb" %>
<script language="VB" runat="server">
    dim ub1 as new LearnAsp.utilitybelt()
    dim strConnect as string
Sub Page_PreRender(S As Object, E As EventArgs)
    ub1.Dispose
end sub
Sub Page_Load(S As Object, E As EventArgs)
    ub1.Options("cache-on")
    ub1.intCacheMinutes=20
    
    strConnect="LearnAspSamples"
    
    ub1.DBPopulate(strConnect,"select distinct city from publishers",cy)
    ub1.DBPopulate(strConnect,"select distinct state from publishers",st)
    ub1.DBPopulate(strConnect,"select distinct zip from publishers",zp)

    ub1.DBPopulate(strConnect,"select count(distinct(city)) as citycount From publishers",cycount)
    ub1.DBPopulate(strConnect,"select count(distinct(state)) as statecount From publishers",stcount)
    ub1.DBPopulate(strConnect,"select count(distinct(zip)) as zipcount From publishers",zpcount)

    ' cy.SelectedIndex = cy.Items.IndexOf(cy.Items.FindByValue(request("cy")))
    ' The Above Statement Is So Clumsy To Select a ListItem I made a SUB Out Of It
    ub1.CtrlDropdownSelect(cy,request("cy"))
    ub1.CtrlDropdownSelect(st,request("st"))
    ub1.CtrlDropdownSelect(zp,request("zp"))
    
End Sub
Sub GetResults_Click(S As Object, E As EventArgs)
    Dim strWhereClause as string
    Dim strPrefix as string
    If chkcy.checked=false AND chkst.checked=false AND chkzp.checked=false THEN
        exit sub
    END IF

    IF chkcy.checked THEN
        strWhereClause &= " city='" & cy.selecteditem.text & "' "
        strPrefix=" AND "
    END IF

    IF chkst.checked THEN
        strWhereClause &= strPrefix & " state='" & st.selecteditem.text & "' "
        strPrefix=" AND "
    END IF

    IF chkzp.checked THEN
        strWhereClause &= strPrefix & " zip='" & zp.selecteditem.text & "' "
    END IF
    ub1.DBPopulate(strConnect,"select * from publishers WHERE " & strWhereClause,grdSearchResults)
End Sub
</script>
<html><head>
<title>Utility Belt Demo</title>
</head>
<body bgcolor="#FFFFFF">
<form runat="server">

<asp:Table id="tbltest" runat="server" GridLines="both" BorderWidth="1px">

<asp:TableRow>
    <asp:TableCell>City (<asp:literal id="cycount" runat="server"/>)</asp:TableCell>
    <asp:TableCell><asp:dropdownlist id="cy" datatextfield="city" runat="server" /></asp:TableCell>
    <asp:TableCell><ASP:checkbox id="chkcy" text="include City in Search?" runat="server"/></asp:TableCell>
</asp:TableRow>


<asp:TableRow>
    <asp:TableCell>State (<asp:literal id="stcount" runat="server"/>)</asp:TableCell>
    <asp:TableCell><asp:dropdownlist id="st" datatextfield="state" runat="server" /></asp:TableCell>
    <asp:TableCell><ASP:checkbox id="chkst" text="include State in Search?" runat="server"/></asp:TableCell>
</asp:TableRow>

<asp:TableRow>
    <asp:TableCell>Zip (<asp:literal id="zpcount" runat="server"/>)</asp:TableCell>
    <asp:TableCell><asp:dropdownlist id="zp" datatextfield="zip" runat="server" /></asp:TableCell>
    <asp:TableCell><ASP:checkbox id="chkzp" text="include Zip in Search?" runat="server"/></asp:TableCell>
</asp:TableRow>

<asp:TableRow>
    <asp:TableCell>&nbsp;</asp:TableCell>
    <asp:TableCell><ASP:BUTTON text="Get the Results" onclick="getresults_click" runat="server" /></asp:TableCell>
</asp:TableRow>


</asp:Table>
<asp:datagrid id="grdSearchResults" EnableviewState="false" runat="server" />

</form>
</body></html>
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too. a $5 tip buys him lunch at McDonalds, a $20 tip buys his kid Hitoshi a new computer game, a $39 tip buys his daughter Michiko a few nice outfits. See our donor list.