DataSource Control Events

In the previous two chapters, you've seen the SqlDataSource and Object-DataSource controls in action, and you've seen how they can be used to fetch and modify data. A common requirement is to hook into that fetching and updating mechanism, perhaps to modify or access the parameters of the command, or perhaps to perform validation.

The data source controls provide events for the major operations: select, insert, update, and delete. Each operation has a pair of events, one that is raised before the operation, and one that is raised afterwards. These events are: Selecting, Selected, Inserting, Inserted, Updating, Updated, Deleting, and Deleted.

Modifying Parameters before Command Execution

The parameters of the data source control allow selection based upon a range of external criteria, but sometimes you need more than these can provide. For example, what if your Web site uses membership, and you want to filter a query based upon the login name? There's no parameter type to handle this explicitly, so you can use the Selecting event and dynamically add a parameter, as shown here.

protected void SqlDataSource1_Selecting(object sender,                               SqlDataSourceSelectingEventArgs e) {   SqlParameter sp = new SqlParameter("UserName", User.Identity.Name);   sp.Direction = ParameterDirection.Input;   e.Command.Parameters.Add(sp); }

Alternatively, you could declare the parameter as a standard asp:Parameter object and then just fill in the missing value. This is possibly more useful because it allows you to declaratively state the parameter order. For example, the SelectParameters could be declared as:

<SelectParameters>   <asp:Parameter Name="UserName" Direction="input" Type="string"/>   ... </SelectParameters>

The event procedure would then be:

protected void SqlDataSource1_Selecting(object sender,                               SqlDataSourceSelectingEventArgs e) {   e.Command.Parameters["UserName"].Value = User.Identity.Name; }

These techniques give you complete control over the parameters before the query is executed.

For the data modification events, the event parameter is of type SqlDataSourceCommandEventArgs, which has two properties: Cancel and Command. If you set the Cancel property to TRue anywhere in the event procedure, then the event is cancelled and the command is not executed. This is a perfect scenario for validation; if you use BoundColumn fields, then you can't put validation onto themyou would have to use TemplateColumn fields and validation controls.

The Command property gives you access to the command being executed, so you can modify the command text or parameters.

Synchronizing DataSource Controls after Updates

One common use for the events is when you have linked controls, such as a GridView for display and a DetailsView for edit. When you modify data via the DetailsView, the GridView isn't updated, so the events can be used to refresh the data. For example, consider Listing 5.1, which has the OnDeleted, OnInserted, and OnUpdated properties set (these properties match to the events raised).

Listing 5.1. Using the Post-Execution Events of a SqlDataSource Control

<asp:SqlDataSource  runat="server">   <ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   <SelectCommand="SELECT * FROM [Products]                   WHERE ([ProductID] = @ProductID)">   <OnDeleted="SqlDataSource2_Deleted">   <OnInserted="SqlDataSource2_Inserted">   <OnUpdated="SqlDataSource2_Updated">   <SelectParameters>     <asp:ControlParameter Control Name="ProductID"       <PropertyName="SelectedValue" Type="Int32" />   </SelectParameters> </asp:SqlDataSource>

The code for these events is shown in Listing 5.2.

Listing 5.2. Handling the Post-Command Events

protected void SqlDataSource2_Inserted(object sender,                               SqlDataSourceStatusEventArgs e) {   GridView1.DataBind(); } protected void SqlDataSource2_Updated(object sender,                               SqlDataSourceStatusEventArgs e) {   GridView1.DataBind(); } protected void SqlDataSource2_Deleted(object sender,                               SqlDataSourceStatusEventArgs e) {   GridView1.DataBind(); }

The code here is extremely easy, simply calling DataBind on the grid that is the filter for the data source control. Since these events run after the appropriate command has been executed, rebinding the grid will show the updated data. One thing to notice is that all of the events have a parameter of type SqlDataSourceStatusEventArgs, which contains details of the command just executed. The properties of this parameter allow access to the number of rows affected by the command, the command itself, and any exception details.

Accessing Output Parameters after Command Execution

One good use for the post command events is to access output parameters from commands, notably stored procedures. This is especially useful when you want to avoid having to return a rowset when only one or two values are required, such as the ID field from a newly inserted row. Returning values via parameters is faster than generating a rowset.

Let's consider a fairly typical stored procedure to insert a row into the Shippers table, as seen in Listing 5.3, which inserts a row with the supplied values and returns the new identity field.

Listing 5.3. The usp_InsertShippers Stored Procedure

CREATE PROCEDURE usp_InsertShippers   @CompanyName  nvarchar(40),   @Phone        nvarchar(24),   @ShipperID    int OUTPUT AS   INSERT INTO Shippers(CompanyName, Phone)   VALUES(@CompanyName, @Phone)   SELECT @ShipperID = SCOPE_IDENTITY()

In the ASP.NET page, there would be a SqlDataSource control with a DetailsView, as shown in Listing 5.4. In this code, the InsertCommand is set to usp_InsertShippers, and there are three InsertParameters: two input parameters to hold the company name and phone number to be inserted, and an output parameter for the returned ID value. Notice that the Direction property on the ShipperID parameter is set to Output. There is also a label on the page for the display of the identity value.

Listing 5.4. A Page that Uses Output Parameters

<asp:SqlDataSource  runat="server"   ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"   InsertCommand="usp_InsertShippers"   InsertCommandType="StoredProcedure"   SelectCommand="SELECT * FROM Shippers"   OnInserted="SqlDataSource1_Inserted">   <InsertParameters>     <asp:Parameter Name="CompanyName" Type="String"  />     <asp:Parameter Name="Phone" Type="String" />     <asp:Parameter Name="ShipperID" Type="Int32"       Direction="Output" />   </InsertParameters> </asp:SqlDataSource> <asp:DetailsView  runat="server"   DataKeyNames="ShipperID" DataSource   AllowPaging="True" AutoGenerateRows="False"   AutoGenerateInsertButton="True">   <Fields>     <asp:BoundField DataField="CompanyName"       HeaderText="Company Name" />     <asp:BoundField DataField="Phone"       HeaderText="Phone Number" />   </Fields> </asp:DetailsView> <br /><br /> Identity Value: <asp:Label  runat="server" />

The Inserted event is shown in Listing 5.5, where you can see we simply index into the Parameters collection of the Command.

Listing 5.5. Extracting the Output Parameter

protected void SqlDataSource1_Inserted(object sender,                               SqlDataSourceStatusEventArgs e) {   IdentityValue.Text =     e.Command.Parameters["@ShipperID"].Value.ToString(); }

The results of this are shown in Figure 5.1the new shipper has been added, with an ID value of 5.

Figure 5.1. The extracted Identity value

Output Parameters with the ObjectDataSource Control

The technique of using output parameters is similar when using a data layer and the ObjectDataSource control, which supports the same set of events as the SqlDataSource control. Output parameters appear as reference parameters in class methods, and should match with the parameters in the stored procedure. Listing 5.6 shows just such a stored procedure, with two parameters with data to be inserted, and the third declared as an OUTPUT parameter, which is set to the new inserted identity value.

Listing 5.6. The usp_InsertShippers Stored Procedure

CREATE PROCEDURE usp_InsertShippers       @CompanyName      nvarchar(40),       @Phone                  nvarchar(24),       @ShipperID        int OUTPUT AS       INSERT INTO Shippers(CompanyName, Phone)       VALUES(@CompanyName, @Phone)       SELECT @ShipperID = SCOPE_IDENTITY()

The data layer class method that calls this procedure is shown in Listing 5.7.

This Insert method accepts three parameters, the first two being the values to be inserted. The third, ShipperID, is declared as ref and is the ID value of the newly created row to be returned to the user interface layer. Note that for the SqlParameter objects, this parameter has its Direction set to ParameterDirection.Output. After the procedure has been executed, the output parameter is accessed and its value used to set the value of ShipperIDthe ref parameter of the method.

Listing 5.7. A Data Layer Class Method with Output Parameters

public static int Insert(string CompanyName, string Phone,                          ref int ShipperID) {   SqlParameter[] sqlParams = new SqlParameter[] {     new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40),     new SqlParameter("@Phone", SqlDbType.NVarChar, 24),     new SqlParameter("@ShipperID", SqlDbType.Int)};   sqlParams[0].Value = CompanyName;   sqlParams[1].Value = Phone;   sqlParams[2].Direction = ParameterDirection.Output;   int rv = DataLayerHelper.ExecuteNonQuery("usp_InsertShippers",            ref sqlParams, CommandType.StoredProcedure);   ShipperID = (int)sqlParams[2].Value;   return rv; }

The parameters of the ObjectDataSource are similar, as seen in Listing 5.8.

Listing 5.8. An ObjectDataSource with Output Parameters

<asp:ObjectDataSource  runat="server"   InsertMethod="Insert" SelectMethod="GetShippers"   TypeName="ShipperDataLayer"   OnInserted="ObjectDataSource1_Inserted" >   <InsertParameters>     <asp:Parameter Name="CompanyName" Type="String" />     <asp:Parameter Name="Phone" Type="String" />     <asp:Parameter Name="ShipperID" Type="Int32"       Direction="Output" />   </InsertParameters> </asp:ObjectDataSource>

Within the Inserted event (see Listing 5.9), the output parameters can be accessed, but not in the same way as for the SqlDataSource. Although the ObjectDataSourceStatusEventArgs has properties of the number of rows affected and any exception details, it doesn't have a parameters collection. Instead, it has an explicit OutputParameters collection through which you can access the output parameters from the underlying method.

Listing 5.9. The Inserted Event Procedure

protected void ObjectDataSource1_Inserted(object sender,                                  ObjectDataSourceStatusEventArgs e) {   IdentityValue.Text = e.OutputParameters["ShipperID"].ToString(); }

The Inserted event also has a ReturnValue property, allowing you to see the explicit return value from the method.

Custom Paging with an ObjectDataSource

If your data or business layer returns DataSet or DataTable objects, then the ObjectDataSource control automatically supports paging, but if you have a business layer that returns strongly typed objects, you have to manage the paging yourself. Paging revolves around the ObjectDataSource knowing the total number of rows in the underlying data, the number of rows in a page, and the item to start the page on.

To see this in action, consider the class in Listing 5.10. There's nothing special about this class; it simply represents an individual shipper.

Listing 5.10. The Shipper Class

public class Shipper {   private int _id;   private string _companyName;   private string _phone;   public Shipper(int id, string companyName, string phone)   {     _id = id;     _companyName = companyName;     _phone = phone;   }   public int ID   {     get { return _id; }     set { _id = value; }   }   public string CompanyName   {     get { return _companyName; }     set { _companyName = value; }   }   public string Phone   {     get { return _phone; }     set { _phone = value; }   } }

On its own, the Shipper class isn't much use alone and requires a class to represent a collection of shippers, as seen in Listing 5.11, which uses a Generic collection to represent the shippers.

Listing 5.11. The Shippers Class

public class Shippers {   public IList<Shipper> GetItems(int startIndex, int maxRows)   {     IList<Shipper> shippers = this.GetItemsInternal();     IList<Shipper> pageShippers = new List<Shipper>();     for (int i = startIndex; i < startIndex + maxRows; i++)       pageShippers.Add(shippers[i]);     return pageShippers;   }   public int GetShippersCount()   {     return this.GetItemsInternal().Count;   }   private IList<Shipper> GetItemsInternal()   {     IList<Shipper> shippers = new List<Shipper>();     DataTable tbl = ShipperDataLayer.GetShippers();     foreach (DataRow row in tbl.Rows)       shippers.Add(new Shipper((int)row["ShipperID"],         row["CompanyName"].ToString(),         row["Phone"].ToString()));     return shippers;   } }

The class has two public methods (methods for data updates have been omitted for brevity), and one private method. The private method, GetItemsInternal, calls the data layer to fetch a DataTable of shippers, and for each row in the table it creates a Shipper object, adding it to a collection. This method is the interface between the data layer and business layer.

The ObjectDataSource needs to know the total number of rows, so the GetShippersCount simply fetches the data and returns the number of items in the collection. This works as an example, but is not the best solution for real life, where you wouldn't want to fetch the entire set of records just to find the total number. In reality, you would probably perform a SELECT COUNT(*) statement, or call a method on the data layer that does this for you. In fact, if you are building a business layer like this, you may even want to remove the use of DataTable objects and use data readers directly in order to get the very best performance.

The GetItems method, which contains two parameters, is used to return the data to the ObjectDataSource. The first, startIndex, is the item number to start returning data from, and the second, maxRows, is the maximum number of rows to return. These can be used to selectively return the correct rows.

To use this class, the ObjectDataSource needs to know that paging is supported and which parameters the method defined in the SelectMethod uses for paging, as seen in Listing 5.12.

Listing 5.12. An ObjectDataSource Using Custom Paging

<asp:ObjectDataSource  runat="server"   TypeName="Shippers" SelectMethod="GetItems"   EnablePaging="true"   StartRowIndexParameterName="startIndex"   MaximumRowsParameterName="maxRows"   SelectCountMethod="GetShippersCount"> </asp:ObjectDataSource>

The ObjectDataSource in Listing 5.12 has the EnablePaging property set to TRue, indicating that paging is enabled on the underlying class. The other properties are:

• StartRowIndexParameterName, which is set to the name of the parameter in the SelectMethod (GetItems) that identifies the index number to start the current page from.

• MaximumRowsParametername, which is set to the name of the parameter in the SelectMethod (GetItems) that identifies the maximum number of rows to return for a page.

• SelectCountMethod, which is set to the name of the method in the class (Shippers) that returns the total record count.

Use of this ObjectDataSource is exactly the same as when using a data source that doesn't use custom paging, as seen in Listing 5.13. Here the PageSize is set to 2, a value that is passed through to the maxRows parameter on the method that fetches the data. The BoundField columns have their DataField property set to the associated public properties on the business object.

Listing 5.13. A GridView Using a Custom Paged ObjectDataSource

<asp:GridView  runat="server"   AllowPaging="true" PageSize="2"   AutoGenerateColumns="False"   DataSource>   <Columns>    <asp:BoundField DataField="ID" HeaderText="ID" />    <asp:BoundField DataField="CompanyName" HeaderText="CompanyName"/>    <asp:BoundField DataField="Phone" HeaderText="Phone" />   </Columns> </asp:GridView>

To ensure that your business object works with an ObjectDataSource that isn't paging, you should also add a method that accepts no parameters to fetch the items, because the parameterized version will only be called if paging is enabled. You should also ensure that your method can accommodate situations in which paging is enabled on the data source but not on the display control, for example, a grid showing all rows. You can test for this by checking maxRows, which when 0 indicates all remaining rows from the startIndex are to be returned. If the display control isn't paging, startIndex will also be 0.