Silverlight Knowledge Base

Using the SQLDataSource Control

When you are using Visual Studio or Visual Web Developer Express, open the Designer to edit any ASP.NET page. You will see a Data tab on the Toolbox. This tab contains the SqlDataSource control. Drag and drop it onto the design surface, and its Tasks pane will open (see Figure 4-19).

Figure 4-19. The SqlDataSource control

This pane contains a Configure Data Source link. Selecting this link will launch the SQL Server Connection wizard (see Figure 4-20). The first step in this wizard is to select a database connection, if one already exists.

Figure 4-20. Choosing the database connection

Earlier in this chapter, you saw how to create a connection to a database. If you've already created a connection, you can select it in the SQL Server Connection wizard. Alternatively, you can click the New Connection button to create a connection now. You can also view the connection string for this connection in this dialog box. When you're happy with the connection, click Next to go to the application configuration selection step, where you can specify what to call the connection string in your Web.config file (see Figure 4-21).

Figure 4-21. Saving the connection string

The next step in the wizard is very powerful, enabling you to specify a custom SQL statement or stored procedure, filter an existing table, or drill down to a viewable set of records. You can see this page in Figure 4-22.

Figure 4-22. Specifying which data to use

Because our query is quite simple in this case, select the second option. If you want to build more sophisticated queries or access stored procedures, you can select the first option.

Select the AddressLine1 and PostalCode fields. Then click the WHERE button. This enables you to configure the WHERE clause of your SQL and use it to handle passing the parameter for the ZIP postal code value from the query string. This launches the Add WHERE Clause dialog box (see Figure 4-23).

You can use this dialog box to specify the column operation and source for the WHERE clause. So, for example, if you want to specify that the returned data should be all records that match a postal code passed in on a query string, you would set Column to PostalCode, Operator to " =", and Source to =" QueryString".

You then fill out the properties for the parameter, giving it a name and a default value. You can see the sample version in Figure 4-23, which uses a parameter called ZIP taking a default value of 98011. Clicking Add will add this WHERE filter to the SQL expression.

Figure 4-23. Adding a new WHERE clause

If you now finish the wizard, SqlDataSource will be set up to access the set of records that are defined by this query. This can be seen in the declaration for the SqlDataSource in the Source view of your page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ConnectionStrings:AdventureWorksConnectionString2 %>" SelectCommand="SELECT AddressLine1, PostalCode FROM Person.Address WHERE

(PostalCode = @PostalCode)"> <SelectParameters> <asp:QueryStringParameter DefaultValue="98011" Name="PostalCode"

QueryStringField="ZIP" />

</SelectParameters> </asp:SqlDataSource>

You now have the root functionality that will enable you to bind your page's UI to your data source.

0 0