| When dealing directly with SQL Server in two-tier architectures, you use the SqlDataSource to manage the flow of data between the database and the ASP.NET pages. When you need to use classes to manage the actual data handling, you can use an ObjectDataSource control. In use, this behaves in a similar manner to its SQL equivalent, but instead of specifying SQL commands or stored procedures, you specify the class and methods. The outline syntax for the ObjectDataSource control is shown in Listing 4.8. Listing 4.7. The Simplified Data Layer Class public class ShipperDataLayer { public static DataTable GetShippers() { return DataLayerHelper.Get("SELECT * FROM Shippers"); } public static DataTable GetShipper(int ShipperID) { return DataLayerHelper.GetByID("SELECT * FROM Shippers WHERE ShipperID=@ShipperID", "ShipperID", ShipperID); } public static int Insert(string CompanyName, string Phone) { SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40), new SqlParameter("@Phone", SqlDbType.NVarChar, 24)}; sqlParams[0].Value = CompanyName; sqlParams[1].Value = Phone; return DataLayerHelper.ExecuteNonQuery("INSERT INTO Shippers(CompanyName, Phone) VALUES(@CompanyName, @Phone)", ref sqlParams); } public static int Update(int ShipperID, string CompanyName, string Phone) { SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@ShipperID", SqlDbType.Int), new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40), new SqlParameter("@Phone", SqlDbType.NVarChar, 24)}; sqlParams[0].Value = ShipperID; sqlParams[1].Value = CompanyName; sqlParams[2].Value = Phone; return DataLayerHelper.ExecuteNonQuery("UPDATE Shippers SET CompanyName=@CompanyName, Phone=@Phone WHERE ShipperID=@ShipperID", ref sqlParams); } public static int Delete(int ShipperID) { SqlParameter param = new SqlParameter("@ShipperID", SqlDbType.Int); param.Value = ShipperID; return DataLayerHelper.ExecuteNonQuery("DELETE FROM Shippers WHERE ShipperID=@ShipperID", param); } }
|
Table 4.1 provides details for the main properties of the ObjectDataSource control. There are others not described here, and some not shown in Listing 4.8, but they will be covered in Chapter 5 when we look at some of the advanced data features. Table 4.1. Properties of the ObjectDataSource ControlProperty | Description |
|---|
TypeName | A String that contains the type name of the object to create. This can be a partially qualified name such as MyClass or a fully qualified name such as MyNamespace.MyClass. | DataObjectTypeName | A String that contains the type name of the object to be used as the parameter type for the Insert, Update, and Delete methods. | SelectMethod | The method of the object to invoke for a SELECT operation. Any parameters required can be defined as the SelectParameters property or in the nested SelectParameters element of the control declaration. | SelectCountMethod | The method of the object to invoke to return a count of the total number of available objects. Selection and filtering can be applied in the usual way with parameters. | UpdateMethod | The method of the object to invoke for an UPDATE operation. Any parameters required can be defined as the UpdateParameters property or in the nested UpdateParameters element of the control declaration. | InsertMethod | The method of the object to invoke for an INSERT operation. Any parameters required can be defined as the InsertParameters property or in the nested InsertParameters element of the control declaration. | DeleteMethod | The method of the object to invoke for a DELETE operation. Any parameters required can be defined as the DeleteParameters property or in the nested DeleteParameters element of the control declaration. | EnablePaging | A Boolean value that specifies whether paging will be enabled within the control. When set to TRue, the StartRowIndexParameterName and MaximumRowsParameterName property values are used to specify the rows or objects retrieved. | StartRowIndexParameter Name | The name of the value in the InputParameters collection that defines the index of the first row or object to return when paging is enabled. | MaximumRowsParameter Name | The name of the value in the InputParameters collection that defines the maximum number of rows or objects to return when paging is enabled. |
Listing 4.8. ObjectDataSource Syntax <asp:ObjectDataSource docEmphasis">String" runat="server" DataObjectTypeName="String" TypeName="String" SelectMethod="String" SelectCountMethod="String" UpdateMethod="String" InsertMethod="String" DeleteMethod="String" EnablePaging="[True|False]" StartRowIndexParameterName="String" MaximumRowsParameterName="String" FilterExpression="String" SortParameterName="String" OldValuesParameterFormatString="String" ConflictDetection="[OverwriteChanges|CompareAllValues]" ConvertNullToDbNull="[True|False]" EnableCaching="[True|False]" CacheDuration="Integer" CacheExpirationPolicy="[Absolute|Sliding]" CacheKeyDependency="String" SqlCacheDependency="String"> <SelectParameters> [<System.Web.UI.WebControls.Parameter ...>] </SelectParameters> <UpdateParameters> [<System.Web.UI.WebControls.Parameter ...>] </UpdateParameters> <DeleteParameters> [<System.Web.UI.WebControls.Parameter ...>] </DeleteParameters> <InsertParameters> [<System.Web.UI.WebControls.Parameter ...>] </InsertParameters> <FilterParameters> [<System.Web.UI.WebControls.Parameter ...>] </FilterParameters> </asp:ObjectDataSource>
|
At its simplest, for displaying data, you only need to set two properties, and only three more for data updates. Using the ObjectDataSource Control The ObjectDataSource control supports configuration via a wizard, accessible from the Smart Tasks. The first screen, shown in Figure 4.2, allows selection of the business objectthe class that contains the methods that will handle the data. Figure 4.2. Choosing a business object 
The next screen, Figure 4.3, allows you to select the methods for each of the four core data access actions: selecting data, updating data, inserting data, and deleting data. Figure 4.3. Defining the data methods 
Once the configuration has been completed, the ObjectDataSource will look like Listing 4.9. You can see that the TypeName contains the class name, and the SelectMethod, DeleteMethod, InsertMethod, and UpdateMethod properties have been set to the method names within the class. You can also see that for each method, an appropriate set of parameters has been created, with a Parameter object for each parameter on the method. There are no SelectParameters because the SelectMethod doesn't have anyit fetches all rows from the data table. Listing 4.9. A Configured ObjectDataSource Control <asp:ObjectDataSource runat="server" TypeName="ShipperDataLayer" SelectMethod="GetShippers" DeleteMethod="Delete" InsertMethod="Insert" UpdateMethod="Update"> <DeleteParameters> <asp:Parameter Name="ShipperID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ShipperID" Type="Int32" /> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="Phone" Type="String" /> </InsertParameters> </asp:ObjectDataSource>
|
If you pick a select method that has parameters, then the parameter definition window will automatically display, allowing you to pick the source for the parameter data, as shown in Figure 4.4. Figure 4.4. Defining the parameters for a Select method 
You use the ObjectDataSource in the same way as a SqlDataSource, by binding a control to it. For example, Listing 4.10 shows a DetailsView bound to the ObjectDataSource. Like the SqlDataSource control, the ObjectDataSource control can be used in master details situations, as shown in Figure 4.5, where the top grid selects data and the bottom allows editing. Figure 4.5. Master Details using ObjectDataSource controls 
Listing 4.10. A DetailsView Bound to the ObjectDataSource <asp:DetailsView runat="server" DataSource DataKeyNames="ShipperID" AllowPaging="True" AutoGenerateRows="False"> <Fields> <asp:BoundField HeaderText="Company Name" DataField="CompanyName" /> <asp:BoundField HeaderText="Phone" DataField="Phone" /> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /> </Fields> </asp:DetailsView>
|
Getting the Identity Value of an Inserted Row A common feature of database tables is that they use an identity column as their unique key, meaning that SQL Server automatically creates the key value when the data is inserted. This key value is required when creating master and detail rows, such as in an order and order details scenario. For example, the order details table would have a foreign key, OrderID, which is the primary key field in the orders table. To create a new order, you first have to insert the details into the orders table and then use the new key value for that order when you insert the details into the order details table. When using a data class to insert the new row in the orders table, there are two ways in which the identity value can be returned: as a return value or as an output parameter. The return value is simplest, but a general rule is that return values are for the actual value returned by the execution method (ExecuteNonQuery). Also, you may want to return more than one value, in which case output parameters are more suitable. To create output parameters, you simply add an additional parameter to the parameter list, but you set its value to -1 and its Direction property to ParameterDirection.Output. This tells the data code that the value is being output from the executing command. Once the command has been executed, the output parameter can be accessed via the parameter array, as seen in Listing 4.11. Listing 4.11. Returning Output Parameters public static int Insert(int CustomerID, int EmployeeID, ... ref int OrderID) { SqlParameter[] sqlParams = new SqlParameter[] { new SqlParameter("@CustomerID", SqlDbType.Int), new SqlParameter("@EmployeeID", SqlDbType.Int), ... new SqlParameter("@OrderID", SqlDbType.Int)}; sqlParams[0].Value = CustomerID; sqlParams[1].Value = EmployeeID; ... sqlParams[n].Value = -1; sqlParams[n].Direction = ParameterDirection.Output; int rv = DataLayerHelper.ExecuteNonQuery("INSERT INTO Orders(CustomerID, EmployeeID, ...) VALUES(@CustomerID, @EmployeeID, ...); SELECT @OrderID = SCOPE_IDENTITY()", ref sqlParams); OrderID = (int)sqlParams[n].Value; return rv; }
|
With stored procedures, the output parameter is declared the same way as input parameters, but with the addition of OUTPUT after the data type. Chapter 5 will cover how to use the data source control's events to manipulate parameters, both before commands are executed and after. |
