Using ASP.NET Parameters with the SqlDataSource Control


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:

  • ControlID The ID of the control that the parameter represents.

  • PropertyName The name of the property that the parameter represents.

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:

  • CookieName The name of the browser cookie.

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:

  • PropertyName Indicates the name of the Profile property associated with this ProfileParameter.

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:

  • QueryStringField The name of the query string that the QueryStringParameter represents.

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:

  • SessionField The name of the item stored in Session state that the SessionParameter represents.

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.




ASP. NET 2.0 Unleashed
ASP.NET 2.0 Unleashed
ISBN: 0672328232
EAN: 2147483647
Year: 2006
Pages: 276

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net