16.1. Working with Data Source Controls
ASP.NET 2.0 provides new data source controls that expose data from a number of data sources to data display controls on Web pages. Data source controls aren't actually shown on the Web pages at browse time and are back-end providers of data to data display controls. But they're very powerful and provide you with capabilities to perform a lot of data retrieval and manipulation operations, such as inserting, updating, deleting, sorting, and filtering, without having to write any code.
By default, the following data source controls are provided by ASP.NET 2.0:
SQLDataSource: Allows for connection to an ADO.NET SQL database provider, such as SQL Server, OLEDB, ODBC, Oracle, etc. The properties exposed by this data source control allow you to specify queries to the back-end database, which are then stored directly on the Web page code.
ObjectDataSource: Not available in SharePoint Designer, this data source control is aimed at three-tier architecture for data retrieval, where the business logic for querying data is implemented separately rather than having it directly in the Web page code (as in the case of SQLDataSource).
XMLDataSource: Allows you to expose data from XML files
AccessDataSource: Derived from the SQLDataSource, this control provides properties that allow you to specify the MS-Access database filename directly while creating the data source control.
SiteMapDataSource: Allows you to retrieve data from the ASP.NET site navigation hierarchy and then bind it to data display controls for navigation
This section takes you through a number of exercises to help you understand how data source controls are configured by using SharePoint Designer. I focus on non-SharePoint data sources first; later, the same concepts can be applied when working on SharePoint sites.
16.1.1. Using ASP.NET connections
After you create your ASP.NET connections by using the Data Source Library task pane, you can use them in your data source controls. The most commonly used data source control is the SQLDataSource control because of its capabilities to connect to a large number of back-end database servers. The next exercise walks you through the steps needed to set up the SQLDataSource to use an ASP.NET connection string. It's assumed that you already have an ASP.NET connection string to a SQL database created with the Data Source Library task pane. You can set the SQLDataSource control properties to retrieve a set of records from a table in a database by following these steps:
Create a new ASPX page inside your Web site by using the New dialog box and then open it in the Design view.
In the Toolbox task pane, expand the Data section in the ASP.NET controls to view the list of available data controls, as shown in Figure 16.1.
Figure 16.1. The list of available data controls in SharePoint Designer
Ensure that the cursor is placed in the Design view and then double-click the SQLDataSource control to insert it on the Web page. Alternatively, you can drag and drop the control from the Toolbox task pane to the Web page. As shown in Figure 16.2, when the control is inserted on the page, the Common SqlDataSource Tasks menu appears in the Design view. You can use the small arrow on the SQLDataSource control to show and hide the Common SqlDataSource Tasks menu.
Figure 16.2. The Common SqlDataSource Tasks menu
Click the Configure Data Source link in the Common SqlDataSource Tasks menu to open the Configure Data Source dialog box, as shown in Figure 16.3.
Figure 16.3. The Configure Data Source dialog box
From the dropdown menu in the Data Source Library task pane, choose the ASP.NET connection you created. You can also click New Connection to quickly create a new ASP.NET connection. If you want to view the connection string in the ASP.NET connection, click the plus (+) sign next to Connection string.
Click Next. This takes you to the Configure the Select Statement screen in the Configure Data Source dialog box. As shown in Figure 16.4, you can use this screen to either specify a custom SQL statement or choose a table or view in the database exposed by the ASP.NET connection.
For this exercise, choose a table from the Name dropdown menu. This shows the column names in the table.
Choose the columns you want to retrieve for display by using the Columns list.
Click the Return only unique rows check box to show only distinct records. The SQL SELECT statement is shown in the SELECT statement box at the bottom of the Configure Data Source dialog box.
Click Next. The Test Query screen in the Configure Data Source dialog box opens. You can quickly test the select query by clicking the Test Query button. If the query succeeds, you see the list of records that the SQLDataSource retrieves from the table.
Click Finish to complete the configuration of the SQLDataSource control.
Figure 16.4. The Configure the Select Statement screen
After you insert the control on the Web page, click the Code view tab in SharePoint Designer to view the code that was inserted and configured using the previous steps. The following is a sample code for the SQLDataSource configured to retrieve a set of records by using a SELECT statement:
<asp:SqlDataSource runat="server" id="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Custom_Connection %>" SelectCommand="SELECT DISTINCT [EmployeeKey], [FirstName], [LastName], [BirthDate] FROM [DimEmployee]"> </asp:SqlDataSource>
SharePoint Designer automatically gives the control a unique ID. The previous steps set the ConnectionString and the SelectCommand properties of the SQLDataSource control.
16.1.2. Setting data source control properties
As shown in Figure 16.5, you can use the Tag Properties task pane to view and modify the properties of the data control using a Visual Studio-like interface.
For example, if you want to use a provider name other than the one specified in the ASP.NET connection that the SQLDataSource is using, you can change the ProviderName property by using the Tag Properties task pane. As shown in Figure 16.6, the properties of the controls that have been manually set are displayed in blue.
Figure 16.5. The Tag Properties task pane for the SQLDataSource control
Figure 16.6. Changing control properties in the Tag Properties task pane
After you change the property by using the Tag Properties task pane, a new property in the HTML code for the control appears in the Web page:
<asp:SqlDataSource runat="server" id="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Custom_Connection %>" SelectCommand="SELECT DISTINCT [EmployeeKey], [FirstName], [LastName], [BirthDate] FROM [DimEmployee]" ProviderName="System. Data.OleDb"> </asp:SqlDataSource>
While this code is a simple SELECT statement retrieving a number of columns from a table, you can use the SharePoint Designer interface of the SQLDataSource control to create advanced queries. In the next exercise, you create an advanced query for data retrieval that receives inputs via a query string to filter records from a table in a database. Follow these steps:
NOTE
A query string is a mechanism offered in HTTP to pass information in a name-value pair format in the URL to a Web server to facilitate logical processing. For example, in the URL www.servername.com/default.aspx?ID=1&Format=string, the query string is the portion of the URL after the question mark and has two name-value pairs (ID=1 and Format=string) separated by an ampersand.
Click Configure Data Source in the Common SqlDataSource Tasks to open the Configure Data Source dialog box.
Specify the ASP.NET connection and then click Next.
Choose the table from which you want to get the records and the columns that need to be shown in the results.
Click Where to open the Add WHERE Clause dialog box. You use this dialog box to specify the parameters that you want to use to filter the records returned. Additionally, as the criteria that you're filtering the records on are received from a query string, you set those parameters by using this dialog box. As shown in Figure 16.7, the Add WHERE Clause dialog box allows you to choose the columns that become part of your filter criteria.
Using the Column dropdown menu, choose the column that's part of your filter criteria.
Specify the operator you want to use for the comparison by using the Operation dropdown menu. Depending on the field type, the number of operators that can be used changes. For example, you can't use the CONTAINS operator for columns of field type Number.
In the Source dropdown menu, choose QueryString as the source from where the value for the comparison is received. You can receive values for comparison from controls, cookies, forms, profiles, and sessions. When you choose Query String, the Parameter Properties section, as shown in Figure 16.8, opens, allowing you to type the name and default value for the QueryString parameter.
Type the name of the parameter in the QueryString field text field and then specify a default value in the Default value text field. The dialog box builds the SQL expression and the value with the inputs you type.
Click Add to complete the creation of the expression of comparison by using inputs from the Query String.
Repeat the previous steps to create multiple expressions by using a query string as source. Once done, the Add WHERE Clause dialog box should look like the one in Figure 16.9.
Click OK to complete the creation of the SELECT statement and then click Next.
Specify the values for the filtering and then click OK. If the query succeeds, you see the filtered results in the Test Query screen.
Click Finish to complete the configuration of the SQLDataSource control.
Figure 16.7. The Add WHERE Clause dialog box
Figure 16.8. Specifying QueryString parameters in the Add WHERE Clause dialog box
To view the changes that were made to the SQLDataSource control's code using the previous steps, click the Code view tab. Here's a sample code:
<asp:SqlDataSource runat="server" id="SqlDataSource1"
ConnectionString="<%$ ConnectionStrings:Custom_Connection %>"
SelectCommand="SELECT DISTINCT [CustomerKey], [Title],
[FirstName], [LastName], [BirthDate], [Gender], [EmailAddress]
FROM [DimCustomer] WHERE (([NumberCarsOwned] = @NumberCarsOwned)
AND ([TotalChildren] = @TotalChildren) AND ([Gender] = @
Gender))">
<SelectParameters>
<asp:querystringparameter
QueryStringField="CarsOwned" DefaultValue="1"
Name="NumberCarsOwned" Type="Byte" />
<asp:querystringparameter
QueryStringField="Children" DefaultValue="2" Name="TotalChildren"
Type="Byte" />
<asp:querystringparameter QueryStringField="Gender"
DefaultValue="M" Name="Gender" Type="String" />
</SelectParameters>
</asp:SqlDataSource>Figure 16.9. SQL expressions built by using the Add WHERE Clause dialog box
As highlighted in the previous code, you now have a WHERE clause on the SELECT statement, which specifies how the records from the table should be filtered. Because we're receiving the values for filtering from query strings, the asp:querystringparameter tag inside the SelectParameters tag specifies the name of the query string variable, the default value, and the field type.
If you want to change the parameters associated with the filter criteria, you can either edit the SQLDataSource control by using the Configure Data Source dialog box or you can use the Tag Properties task pane to quickly jump to the Command and Parameter Editor dialog box.
16.1.3. Editing commands and parameters
As shown in Figure 16.10, the Command and Parameter Editor dialog box allows you to modify the SQL command and associated parameters. You can open this dialog box by clicking on the ellipsis next to the query properties (for example, SelectQuery, InsertQuery, etc.) of the SQLDataSource control in the Tag Properties dialog box.
Figure 16.10. The Command and Parameter Editor dialog box
Besides adding, removing, and modifying the parameters, you also get an advanced view similar to the Tag Properties task pane for changing the advanced properties associated with a parameter. In this view, the properties that have already been set are displayed in bold.
In the next exercise, you specify the SQLDataSource control's properties to enable inserting, updating, and deleting operations on a table inside a database. Follow these steps:
Insert an SQLDataSource control on a Web page by using the Toolbox task pane and then open the Configure Data Source dialog box.
Specify the ASP.NET connection and then choose the table inside the database and columns to be used on the SELECT statement. Based on the understanding from the previous steps, you can also create an advanced WHERE clause for the SELECT statement. Also, notice the Order button on the Configure the Select Statement screen. You can use this button to specify ordering of the records to three levels via the interface.
Click Advanced to open the Advanced SQL Generation Options dialog box. As shown in Figure 16.11, you can use this dialog box to allow automatic generation of the INSERT, UPDATE, and DELETE commands for your SQLDataSource control. As indicated in the interface, you must have a primary key defined in your table to be able to make use of this option.
Click Next and then Finish to complete the configuration of the SQLDataSource data control.
Figure 16.11. The Advanced SQL Generation Options dialog box
As shown in the following code, the interface has automatically generated the INSERT, UPDATE, and DELETE commands for your table:
<asp:SqlDataSource runat="server" id="SqlDataSource1"
DeleteCommand="DELETE FROM [DimCustomer] WHERE [CustomerKey] = @
original_CustomerKey AND [FirstName] = @original_FirstName AND
[LastName] = @original_LastName AND [BirthDate] = @original_
BirthDate AND [Title] = @original_Title AND [EmailAddress] = @
original_EmailAddress" InsertCommand="INSERT INTO [DimCustomer]
([FirstName], [LastName], [BirthDate], [Title], [EmailAddress])
VALUES (@FirstName, @LastName, @BirthDate, @Title, @
EmailAddress)" UpdateCommand="UPDATE [DimCustomer] SET
[FirstName] = @FirstName, [LastName] = @LastName, [BirthDate] = @
BirthDate, [Title] = @Title, [EmailAddress] = @EmailAddress WHERE
[CustomerKey] = @original_CustomerKey AND [FirstName] = @original_
FirstName AND [LastName] = @original_LastName AND [BirthDate] = @
original_BirthDate AND [Title] = @original_Title AND
[EmailAddress] = @original_EmailAddress" OldValuesParameterFormat
String="original_{0}" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:Custom_Connection %>"
SelectCommand="SELECT [CustomerKey], [FirstName], [LastName],
[BirthDate], [Title], [EmailAddress] FROM [DimCustomer]">
<DeleteParameters>
<asp:parameter Name="original_CustomerKey"
Type="Int32" />
<asp:parameter Name="original_FirstName"
Type="String" />
<asp:parameter Name="original_LastName"
Type="String" />
<asp:parameter Name="original_BirthDate"
Type="DateTime" />
<asp:parameter Name="original_Title" Type="String"
/>
<asp:parameter Name="original_EmailAddress"
Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:parameter Name="BirthDate" Type="DateTime" />
<asp:parameter Name="Title" Type="String" />
<asp:parameter Name="EmailAddress" Type="String" />
<asp:parameter Name="original_CustomerKey"
Type="Int32" />
<asp:parameter Name="original_FirstName"
Type="String" />
<asp:parameter Name="original_LastName"
Type="String" /><asp:parameter Name="original_BirthDate"
Type="DateTime" />
<asp:parameter Name="original_Title" Type="String"
/>
<asp:parameter Name="original_EmailAddress"
Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:parameter Name="FirstName" Type="String" />
<asp:parameter Name="LastName" Type="String" />
<asp:parameter Name="BirthDate" Type="DateTime" />
<asp:parameter Name="Title" Type="String" />
<asp:parameter Name="EmailAddress" Type="String" />
</InsertParameters>
</asp:SqlDataSource>A number of parameters are automatically generated to facilitate the insert, update, and delete operations. Also, the ConflictDetection property was set as a result of clicking the Use optimistic concurrency check box to ensure that the DataSet on which the INSERT, UPDATE, and DELETE commands run reflect the most current state of the data inside the database. Now you can use the Command and Parameter Editor dialog box to modify the INSERT, UPDATE, and DELETE queries.
|
Another data source control that might generate interest is the XMLDataSource control. As mentioned earlier, the XMLDataSource control is useful for interfacing with XML files as back-end data sources. To configure an XMLDataSource in SharePoint Designer, follow these steps:
Using the Toolbox task pane, insert the XMLDataSource control on a Web page.
Click Configure Data Source in the Common XMLDataSource Tasks menu to open the Configure Data Source dialog box for the XMLDataSource. As shown in Figure 16.12, you can use this dialog box to specify properties for the XMLDataSource control.
In the Data File text field, type the name of the XML file to be used as the data source. You can also optionally specify a transform file to transform the structure of your XML file and an XPATH file to filter the XML file and return only a subset of the data exposed.
Figure 16.12. Specifying properties for the XMLDataSource control
Once the properties are set, you can bind the control with the available data display controls to display the data exposed in the XML file on Web pages. The XMLDataSource is particularly useful in displaying the hierarchy of nodes inside an XML file by using the ASP.NET TreeView and Menu controls.