Using ASP.NET Parameters with the SqlDataSource Control You can use any of the following ASP.NET Parameter objects with the SqlDataSource control: Parameter Represents an arbitrary static value. ControlParameter Represents the value of a control or page property. CookieParameter Represents the value of a browser cookie. FormParameter Represents the value of an HTML form field. ProfileParameter Represents the value of a Profile property. QueryStringParameter Represents the value of a query string field. SessionParameter Represents the value of an item stored in Session state.
The SqlDataSource control includes five collections of ASP.NET parameters: SelectParameters, InsertParameters, DeleteParameters, UpdateParameters, and FilterParameters. You can use these parameter collections to associate a particular ASP.NET parameter with a particular SqlDataSource command or filter. In the following sections, you learn how to use each of these different types of parameter objects. Using the ASP.NET Parameter Object The ASP.NET parameter object has the following properties: ConvertEmptyStringToNull When true, if a parameter represents an empty string then the empty string is converted to the value Nothing (null) before the associated command is executed. DefaultValue When a parameter has the value Nothing (null), the DefaultValue is used for the value of the parameter. Direction Indicates the direction of the parameter. Possible values are Input, InputOutput, Output, and ReturnValue. Name Indicates the name of the parameter. Do not use the @ character when indicating the name of an ASP.NET parameter. Size Indicates the data size of the parameter. Type Indicates the .NET Framework type of the parameter. You can assign any value from the TypeCode enumeration to this property.
You can use the ASP.NET parameter object to indicate several parameter properties explicitly, such as a parameter's type, size, and default value. For example, the page in Listing 9.15 contains a DetailsView control bound to a SqlDataSource control. You can use the page to update records in the Movies database table (see Figure 9.9). Listing 9.15. ShowDetailsView.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show DetailsView</title> </head> <body> <form runat="server"> <div> <asp:DetailsView DataKeyNames="Id" DataSource AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:Movies %>" SelectCommand="Select * FROM Movies" UpdateCommand="UPDATE Movies SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server" /> </div> </form> </body> </html>
|
Figure 9.9. Updating movie records. 
In Listing 9.15, no ASP.NET parameter objects are declared explicitly. The DetailsView control automatically creates and adds ADO.NET parameters to the SqlDataSource control's update command before the command is executed. If you want to be explicit about the data types and sizes of the parameters used by a SqlDataSource control, then you can declare the parameters. The page in Listing 9.16 declares each of the parameters used when executing the update command. Listing 9.16. ShowDetailsViewExplicit.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show DetailsView Explicit</title> </head> <body> <form runat="server"> <div> <asp:DetailsView DataKeyNames="Id" DataSource AutoGenerateEditButton="true" DefaultMode="Edit" AllowPaging="true" runat="server" /> <asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:Movies %>" SelectCommand="Select * FROM Movies" UpdateCommand="UPDATE Movies SET Title=@Title,Director=@Director, DateReleased=@DateReleased WHERE Id=@id" Runat="server"> <UpdateParameters> <asp:Parameter Name="Title" Type="String" Size="100" DefaultValue="Untitled" /> <asp:Parameter Name="Director" Type="String" Size="100" DefaultValue="Alan Smithee" /> <asp:Parameter Name="DateReleased" Type="DateTime" /> <asp:Parameter Name="id" Type="int32" /> </UpdateParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
In Listing 9.16, each of the parameters used by the update command are provided with an explicit data type. For example, the DateReleased parameter is declared to be a DateTime parameter (if you didn't assign an explicit type to this parameter, it would default to a string). Furthermore, the Title and Director parameters are provided with default values. If you edit a movie record and do not supply a title or director, the default values are used. Note Another situation in which explicitly declaring Parameter objects is useful is when you need to explicitly order the parameters. For example, the order of parameters is important when you use the OLE DB provider with Microsoft Access.
Using the ASP.NET ControlParameter Object You use the ControlParameter object to represent the value of a control property. You can use it to represent the value of any control contained in the same page as the SqlDataSource control. The ControlParameter object includes all the properties of the Parameter object and these additional properties: For example, the page in Listing 9.17 includes a DropDownList control and a DetailsView control. When you select a movie from the DropDownList, details for the movie are displayed in the DetailsView control (see Figure 9.10). Listing 9.17. ShowControlParameter.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show Control Parameter</title> </head> <body> <form runat="server"> <div> <asp:DropDownList DataSource DataTextField="Title" DataValueField="Id" Runat="server" /> <asp:Button Text="Select" Runat="server" /> <hr /> <asp:DetailsView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT Id,Title FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT * FROM Movies WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:ControlParameter Name="Id" Control PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Figure 9.10. Show matching movies for each movie category. 
Notice that the second SqlDataSource control in Listing 9.17 includes a ControlParameter object. The ControlParameter represents the ID of the selected movie in the DropDownList control. When using a ControlParameter, you must always set the value of the ControlID property to point to a control on the page. On the other hand, you are not always required to set the PropertyName property. If you do not set PropertyName, the ControlParameter object automatically looks for a property that is decorated with the ControlValueProperty attribute. Because the SelectedValue property of the DropDownList control is decorated with this attribute, you do not really need to set this property in Listing 9.17. Because the Page class derives from the control class, you can use the ControlParameter object to represent the value of a Page property. For example, the page in Listing 9.18 contains a simple guestbook. When a user adds a new entry to the guestbook, the user's remote IP address is saved automatically with the guestbook entry (see Figure 9.11). Figure 9.11. Saving an IP address in guest book entries. 
Listing 9.18. ShowPageControlParameter.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Public ReadOnly Property IPAddress() As String Get Return Request.UserHostAddress End Get End Property </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show Page Control Parameter</title> </head> <body> <form runat="server"> <div> <asp:FormView DataSource DefaultMode="Insert" runat="server"> <InsertItemTemplate> <asp:Label Text="Your Name:" AssociatedControl Runat="server" /> <asp:TextBox Text='<%# Bind("Name") %>' Runat="server" /> <br /><br /> <asp:Label Text="Your Comments:" AssociatedControl Runat="server" /> <br /> <asp:TextBox Text='<%# Bind("Comments") %>' TextMode="MultiLine" Columns="60" Rows="4" Runat="server" /> <br /><br /> <asp:Button Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT * FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (IPAddress,Name,Comments) VALUES (@IPAddress,@Name,@Comments)" ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ControlParameter Name="IPAddress" Control PropertyName="IPAddress" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Notice that the ControlID property is set to the value __page. This value is the automatically generated ID for the Page class. The PropertyName property has the value IPAddress. This property is defined in the page. Using the ASP.NET CookieParameter Object The CookieParameter object represents a browser-side cookie. The CookieParameter includes all the properties of the base Parameter class and the following additional property: The page in Listing 9.19 illustrates how you can use the CookieParameter object. The page contains a voting form that you can use to vote for your favorite color. A cookie is added to the user's browser to identify the user and prevent someone from cheating by voting more than once (see Figure 9.12). Listing 9.19. Vote.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Private Sub Page_Load() If Request.Cookies("VoterId") Is Nothing Then Dim identifier As String = Guid.NewGuid().ToString() Dim voteCookie As HttpCookie = New HttpCookie("VoterId",identifier) voteCookie.Expires = DateTime.MaxValue Response.AppendCookie(voteCookie) End If End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Vote</title> </head> <body> <form runat="server"> <div> <asp:FormView DataSource DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label AssociatedControl Runat="server" /> <asp:RadioButtonList SelectedValue='<%#Bind("Color")%>' Runat="server"> <asp:ListItem Value="Red" Text="Red" Selected="True" /> <asp:ListItem Value="Blue" Text="Blue" /> <asp:ListItem Value="Green" Text="Green" /> </asp:RadioButtonList> <br /> <asp:Button Text="Submit" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT * FROM Vote ORDER BY Id DESC" InsertCommand="INSERT Vote (VoterId,Color) VALUES (@VoterId,@Color)" ConnectionString="<%$ ConnectionStrings:Vote %>" Runat="server"> <InsertParameters> <asp:CookieParameter Name="VoterId" CookieName="VoterId" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Figure 9.12. Vote on your favorite color. 
The cookie is added in the Page_Load() method. A unique identifier (GUID) is generated to identify the user uniquely. Using the ASP.NET FormParameter Object The FormParameter object represents a form field submitted to the server. Typically, you never work directly with browser form fields because their functionality is encapsulated in the ASP.NET form controls. The page in Listing 9.20 contains a client-side HTML form that enables you to enter a movie title and director. When the form is submitted to the server, the values of the form fields are saved to the Movies database table (see Figure 9.13). Figure 9.13. Using a client-side HTML form. 
Listing 9.20. ShowFormParameter.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Private Sub Page_Load() If Not Request.Form("AddMovie") Is Nothing Then srcMovies.Insert() End If End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show FormParameter</title> </head> <body> <form action="ShowFormParameter.aspx" method="post"> <label for="txtTitle">Movie Title:</label> <br /> <input name="txtTitle" /> <br /><br /> <label for="txtDirector">Movie Director:</label> <br /> <input name="txtDirector" /> <br /><br /> <input name="AddMovie" type="submit" value="Add Movie" /> </form> <form runat="server"> <div> <asp:GridView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT * FROM Movies" InsertCommand="INSERT Movies (Title,Director,CategoryId,DateReleased) VALUES (@Title,@Director,0,'12/25/1966')" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <InsertParameters> <asp:FormParameter Name="Title" FormField="txtTitle" DefaultValue="Untitled" /> <asp:FormParameter Name="Director" FormField="txtDirector" DefaultValue="Allen Smithee" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Notice that you check whether a form field named AddMovie exists in the Page_Load() method. This is the name of the submit button. If this field exists, then you know that the client-side form was submitted and the SqlDataSource control's Insert() method can be called to add the form fields to the database. Using the ASP.NET ProfileParameter Object The ProfileParameter object enables you to represent any of the properties of the Profile object. The ProfileParameter includes all the properties of the Parameter class and the following property: For example, imagine that you are building a Guest Book application and you want to allow users to enter their display names when adding entries to a guest book. You can add a DisplayName property to the Profile object with the web configuration file in Listing 9.21. Listing 9.21. Web.config <?xml version="1.0"?> <configuration> <connectionStrings> <add name="GuestBook" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|GuestBookDB.mdf; Integrated Security=True;User Instance=True" /> </connectionStrings> <system.web> <profile enabled="true"> <properties> <add name="DisplayName" defaultValue="Anonymous" /> </properties> </profile> </system.web> </configuration>
|
Note The Profile object automatically stores user specific information across visits to a website. The Profile object is discussed in detail in Chapter 22, "Maintaining Application State."
The web configuration file in Listing 9.21 includes the definition of a Profile property named DisplayName. Notice that the default value of this property is Anonymous. The page in Listing 9.22 uses the ProfileParameter object to read the value of the DisplayName property automatically when new entries are added to a Guest Book. Listing 9.22. ShowProfileParameter.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show ProfileParameter</title> </head> <body> <form runat="server"> <div> <asp:FormView DataSource DefaultMode="Insert" Runat="server"> <InsertItemTemplate> <asp:Label Text="Enter Your Comments:" Runat="server" /> <br /> <asp:TextBox Text='<%# Bind("Comments") %>' TextMode="MultiLine" Columns="50" Rows="4" Runat="server" /> <br /> <asp:Button Text="Add Comments" CommandName="Insert" Runat="server" /> </InsertItemTemplate> </asp:FormView> <hr /> <asp:GridView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT Name,Comments,EntryDate FROM GuestBook ORDER BY Id DESC" InsertCommand="INSERT GuestBook (Name,Comments) VALUES (@Name,@Comments)" ConnectionString="<%$ ConnectionStrings:GuestBook %>" Runat="server"> <InsertParameters> <asp:ProfileParameter Name="Name" PropertyName="DisplayName" /> </InsertParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Notice that the SqlDataSource control in Listing 9.22 includes a ProfileParameter object. This object represents the DisplayName profile property. Using the QueryStringParameter Object The QueryStringParameter object can represent any query string passed to a page. The QueryStringParameter class includes all the properties of the base Parameter class with the addition of the following property: This type of parameter is particularly useful when you build Master/Detail pages. For example, the page in Listing 9.23 displays a list of movie titles. Each movie title links to a page that contains detailed information for the movie. Listing 9.23. ShowQueryStringParameterMaster.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show QueryStringParameter Master</title> </head> <body> <form runat="server"> <div> <asp:GridView DataSource AutoGenerateColumns="false" ShowHeader="false" Runat="server"> <Columns> <asp:HyperLinkField DataTextField="Title" DataNavigateUrlFields="Id" DataNavigateUrlFormatString="ShowQueryStringParameterDetails.aspx?id={0}" /> </Columns> </asp:GridView> <asp:SqlDataSource SelectCommand="SELECT * FROM Movies" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server" /> </div> </form> </body> </html>
|
Notice that the ID of the movie is passed to the ShowQueryStringParameterDetails.aspx page. The movie ID is passed in a query string field named id. The page in Listing 9.24 displays detailed information for a particular movie. Listing 9.24. ShowQueryStringParamterDetails.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show QueryStringParameter Details</title> </head> <body> <form runat="server"> <div> <asp:DetailsView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT * FROM Movies WHERE Id=@Id" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:QueryStringParameter Name="Id" QueryStringField="Id" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Notice that the SqlDataSource control in Listing 9.24 includes a QueryStringParameter. The QueryStringParameter is used to supply the movie ID in the SqlDataSource control's SelectCommand. Using the SessionParameter Object The SessionParameter object enables you to represent any item stored in Session state. The SessionParameter object includes all the properties of the base Parameter class and the following property: Note Session state is discussed in detail in Chapter 22, "Maintaining Application State."
The page in Listing 9.25 contains a GridView that displays a list of movies matching a movie category. The movie category is stored in Session state. Listing 9.25. ShowSessionParameter.aspx <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <script runat="server"> Private Sub Page_Load() Session("MovieCategoryName") = "Animation" End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Show SessionParameter</title> </head> <body> <form runat="server"> <div> <asp:GridView DataSource Runat="server" /> <asp:SqlDataSource SelectCommand="SELECT Name As Category,Title,Director FROM Movies INNER JOIN MovieCategories ON CategoryId = MovieCategories.id WHERE Name=@Name" ConnectionString="<%$ ConnectionStrings:Movies %>" Runat="server"> <SelectParameters> <asp:SessionParameter Name="Name" SessionField="MovieCategoryName" /> </SelectParameters> </asp:SqlDataSource> </div> </form> </body> </html>
|
Notice that the current movie category is added to the Session object in the Page_Load() method. The SqlDataSource reads the MovieCategoryName item from Session state when it retrieves the list of movies that the GridView displays. | 