Programmatically Executing SqlDataSource Commands
Programmatically Executing SqlDataSource CommandsYou aren't required to use the SqlDataSource control only when working with DataBound controls. You can create parameters and execute the commands represented by a SqlDataSource control by working directly with the properties and methods of the SqlDataSource control in your code. In this section, you learn how to add parameters programmatically to a SqlDataSource control. You also learn how to execute select, insert, update, and delete commands when using the SqlDataSource control. Adding ADO.NET ParametersUnder the covers, the SqlDataSource control uses ADO.NET objects such as the ADO.NET DataSet, DataReader, Parameter, and Command objects to interact with a database. In particular, any ASP.NET Parameter objects that you declare when working with the SqlDataSource control get converted into ADO.NET Parameter objects. In some cases, you will want to work directly with these ADO.NET Parameter objects when using the SqlDataSource control. For example, you might want to add additional ADO.NET parameters programmatically before executing a command. The page in Listing 9.26 automatically adds an ADO.NET parameter that represents the current user's username to the command that the SqlDataSource executes. Listing 9.26. AddParameter.aspx
Notice that the page in Listing 9.26 includes a srcGuestBook_Inserting() event handler. This event handler executes immediately before the SqlDataSource control executes its insert command. In the event handler, a new ADO.NET Parameter is added to the insert command, which represents the current user's username. Note The names of ADO.NET parameters, unlike ASP.NET parameters, always start with the character @. Executing Insert, Update, and Delete CommandsThe SqlDataSource control has methods that correspond to each of the different types of commands that it represents:
For example, the page in Listing 9.27 contains a form for adding new entries to the GuestBook database table. This form is not contained in a DataBound control such as the FormView or DetailsView controls. The form is contained in the body of the page. When you click the Add Entry button, the SqlDataSource control's Insert() method is executed. Listing 9.27. ExecuteInsert.aspx
Executing Select CommandsThe procedure for executing a select command is different from executing insert, update, and delete commands because a select command returns data. This section discusses how you can execute the SqlDataSource control's Select() method programmatically and represent the data that the method returns. Remember that a SqlDataSource control can return either a DataView or DataReader depending on the value of its DataSourceMode property. The SqlDataSource control's Select() method returns an object of type IEnumerable. Both DataViews and DataReaders implement the IEnumerable interface. To understand how you can call the Select() method programmatically, look at the following simple photo gallery application. This application enables you to upload images to a database table and display them in a page (see Figure 9.14). Figure 9.14. A photo gallery application. First, you need to create the page that displays the images and contains the form for adding new images. The PhotoGallery.aspx page is contained in Listing 9.28. Listing 9.28. PhotoGallery.aspx
The page in Listing 9.28 has a FormView control that contains a FileUpload control. You can use the FileUpload control to upload images from your local hard drive to the application's database table. Also, the page contains a DataList control that is used to display the image. Notice that the Image control contained in the DataList control's ItemTemplate points to a file named DynamicImage.ashx. The DynamicImage.ashx file represents an HTTP Handler that renders a particular image. The DynamicImage.ashx handler is contained in Listing 9.29. Note HTTP handlers are discussed in detail in Chapter 25, "Working with the HTTP Runtime." Listing 9.29. DynamicImage.ashx
In the ProcessRequest() method, an instance of the SqlDataSource control is created. The SqlDataSource control's ConnectionString and SelectCommand properties are initialized. Finally, the SqlDataSource control's Select() command is executed and the results are rendered with the Response.BinaryWrite() method. Notice that the return value from the Select() method is cast explicitly to a DataView object. You need to cast the return value to either a DataView or IDataReader for it to work with the results of the Select() method. In Listing 9.29, the image bytes are returned in a DataView. To illustrate how you can use the Select() method to return a DataReader, I've also included the code for returning the image with a DataReader, but I've added comments to the code so that it won't execute. |
SqlDataSourceCommandEventArgs) e.Command.Parameters.Add(New SqlParameter("@Name", User.Identity.Name)) End Sub </script> <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" OnInserting="srcGuestBook_Inserting" /> </div> </form> </body> </html>