|
xxx
Utility Belt:
Populating objects using SQL Server stored procedures
by Paul Brophy
Calling a stored procedure shouldn't be harder than creating a SQL query at runtime.
Now, with "Utility Belt" it's often much easier. We showed how easy it is to populate a objects from a SQL Server
database in this example. Now we'll show you the same example using stored procedures
instead of dynamic queries.
Performance was never this easy!
The code below shows "Utility Belt"
using three stored procedures to populate a dropdown list, a label and a datagrid.
From Dynamic queries to stored procedures in 3 easy steps
-
Put the name of the stored procedure in strSQL. Where's the query? "We don't need no stinkin' query!"
-
Create a hashtable and add the parameters to the stored procedure. If you have no parameters, just create a Hashtable and omit the parameter "adding".
-
Call the appropriate "Utility Belt" method and don't forget to include the Hashtable you created to hold the parameters.
filename=/experiments/utilitybelt/vernext/ubdemo_search_sqlsp.aspx
<%@trace="true" debug="true" %>
<%@ Import Namespace="System.data"%>
<%@ Assembly src="utilitybelt.vb" %>
<script language="VB" runat="server">
dim ub1 as new learnasp.utilitybelt()
dim strConnect as string="learnaspsamplesfull"
Sub Page_Load(S As Object, E As EventArgs)
ub1.Options("Advice-on")
IF ispostback = false
ub1.spDBPopulate(strConnect,"GetDistinctColumn",cy,"@selcol","city")
ub1.spDBPopulate(strConnect,"GetDistinctColumnCount",cycount,"@selcol","city")
ub1.spDBPopulate(strConnect,"GetDistinctColumn",st,"@selcol","state")
ub1.spDBPopulate(strConnect,"GetDistinctColumnCount",stCount,"@selcol","state")
ub1.spDBPopulate(strConnect,"GetDistinctColumn",zp,"@selcol","zip")
ub1.spDBPopulate(strConnect,"GetDistinctColumnCount",zpCount,"@selcol","zip")
END IF
End Sub
Sub GetResults_Click(S As Object, E As EventArgs)
Dim htParm as New Hashtable()
IF chkcy.checked=false AND chkst.checked=false AND chkzp.checked=false THEN
exit sub
END IF
IF chkcy.checked THEN
htParm.Add("@city",cy.selecteditem.text)
END IF
IF chkst.checked THEN
htParm.Add("@state",st.selecteditem.text)
END IF
IF chkzp.checked THEN
htParm.Add("@zip",zp.selecteditem.text)
END IF
ub1.DBPopulate(strConnect,"SearchCSZ",htParm,grdSearchResults)
End Sub
</script>
<html><head>
<title>Utility Belt Demo</title>
</head>
<body bgcolor="#FFFFFF">
<asp:placeholder id="plcErr" runat="server"/>
<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> </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.
| |
 | |  |
|
|
|
|