1 / 48

Data Source and Data Display Controls Part 1

Data Source and Data Display Controls Part 1. Reference: “ASP.NET 2.0 Illustrated” by Alex Homer and Dave Sussman . -ch3 illustrated book http://www.mediafire.com/? 5kgeryodzgx. Databases and Connections.

wayne
Download Presentation

Data Source and Data Display Controls Part 1

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Source and Data Display ControlsPart 1 Reference: “ASP.NET 2.0 Illustrated” by Alex Homer and Dave Sussman. -ch3 illustrated book http://www.mediafire.com/?5kgeryodzgx

  2. Databases and Connections • Most Web sites will have some form of database behind them, whether to store a product catalog or just to manage user logins. • This often requires a full installation of SQL Server on your development machine, but with ASP.NET 2.0 there is another option--SQL Server 2005 Express Edition (SSE). • SSE is a cut-down version of SQL Server 2005, but has some interesting characteristics that make it especially suitable for the development environment. • Two of these characteristics are automatic database attachment and user instancing.

  3. Connection String Placement • Connection strings are typically stored in web.config, and in version 1.x that usually meant the appSettings section. • In ASP.NET 2.0, there is a new connectionStrings section, which provides a similar key/value pairing. • For example: • <connectionStrings> <add name="AW" connectionString="Data Source=.\SQLEXPRESS; . . ." providerName="System.Data.SqlClient" /> </connectionStrings>

  4. Connection String Placement • Although the providerName attribute isn't compulsory, connection strings won't appear in the Configure Data Source dialogs without a provider name being set. • Within applications, you can access these connection strings in two ways. 1. In code, you use the ConnectionStrings property of the ConfigurationManager object. For example: • SqlConnection conn = new SqlConnection(); conn.ConnectionString = ConfigurationManager.ConnectionStrings["AW"].ConnectionString;

  5. Connection String Placement 2. Within the markup of ASP.NET pages, you use an expression builder, which is a new feature of ASP.NET 2.0. Expression builders allow you to declaratively access features such as connection strings, application settings, and resources. For example, consider the following code: • <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AW %> "

  6. Connection String Placement • The expression builder uses a server side <% %> block, but when the first character within that block is a $ sign this indicates an expression builder is to be used. • Each expression builder has a known prefix, and for connection strings this is ConnectionStrings. • In a similar method to code, you use the name attribute from the web.config section to identify the required connection string, using a : to separate the builder prefix from the name. • The beauty of these two methods is that from both code and markup you can use the centrally stored connection strings.

  7. Visual Web Developer and Visual Studio 2005 Database Explorer • Both Visual Studio 2005 and Visual Web Developer have database management features, and although both are reached through different places, they act the same. • In Visual Studio 2005, you use the Server Explorer, which by default is on the left of the screen, as shown in Figure 3.1. • In Visual Web Developer, the Database Explorer is used, which by default is on the right of the screen, as shown in Figure 3.2. • As you can see, their contents are the same, allowing access to the contents of the database from within the development tool.

  8. Figure 3.1. The Visual Studio 2005 Server Explorer

  9. Figure 3.2. The Visual Web Developer Database Explorer

  10. Visual Web Developer and Visual Studio 2005 Database Explorer • Whichever tool you use, working with databases is simple. • You can create and modify tables, stored procedures, views, and functions, and you can run ad-hoc queries. • Throughout the book we'll refer to the Database Explorer because it's more explicit; if you are using Visual Studio 2005, then use the Server Explorer to access the same functionality.

  11. Data Source Controls • Data source controls provide a declarative way to define not only the connection to a data store, but also the commands used to fetch and update data. • Some data source controls don't interact directly with their data source, but interact by way of a provider. • A provider abstracts the functionality of dealing with a data source and provides a consistent API for applications and controls to use • The providers are also pluggable, allowing you to replace the supplied providers with ones of your own.

  12. Data Source Controls • The following four data source controls are supplied as standard with ASP.NET 2.0: • ObjectDataSource, which interfaces to custom classes • SiteMapDataSource, which interfaces to site map data, for site navigation • SqlDataSource, which interfaces to SQL databases • XmlDataSource, which interfaces to XML files

  13. The SqlDataSourceControl • The SqlDataSource control provides a two-tier model for interacting with relational databases. • It abstracts away from the developer the need to explicitly create connections and commands, leaving you free to concentrate on the data statements. • When using the drag and drop functionality, these statements default to explicit SQL, but you can also use stored procedures. • There is no way to have stored procedures automatically created and used in the SqlDataSource, so you'll have to do this yourself. • However, a quick solution is to use the drag-and-drop functionality, and then use the SQL statements as the SQL for your stored procedures.

  14. The SqlDataSource Control • To configure a SqlDataSource control, you can manually edit the properties, or use the smart task Configure Data Source option. • The latter just walks you through selecting the connection (either from web.config, or creating a new one), selecting the tables and columns from which to fetch data, and optionally adding filtering and concurrency to the command. • We'll look at these topics in the Filtering Data and Updating Data sections.

  15. The SqlDataSource Control • The SqlDataSource control has a number of properties that govern its behavior. • The first of these is DataSourceMode, which can be set either to DataSet or DataReader, to identify how the data is to be fetched from the underlying data source. • The default is DataSet, which provides two-way binding, while DataReader provides increased performance, but only allows read-only data.

  16. The SqlDataSource Control • To control how often the data is fetched, there are a number of properties specific to caching, allowing the control to cache data and only re-fetch it if it has changed since the last access. (more in Chapter 6). • The properties that you will use most often relate to the commands used to fetch and modify data. • For each type of data manipulation, there are two properties: a command to run, and a command type identifying if the command is inline SQL or a stored procedure.

  17. Displaying Data with a GridViewControl • At its simplest, the SqlDataSource control only requires that two properties be set to fetch data from a database: the ConnectionString and the SelectCommand. • For example: • <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>" SelectCommand="SELECT * FROM Products" />

  18. Displaying Data with a GridView Control • This simply fetches all rows from the Products table. • To switch to stored procedures, you replace the inline SQL statement with the name of the stored procedure, and add the SelectCommandType attribute, setting its value to StoredProcedure. • <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>" SelectCommand="usp_Products" SelectCommandType="StoredProcedure" />

  19. You can use this data source control by binding another control, such as a GridView, to it. For example: • <asp:GridView ID="GridView1" runat="server" DataSourceID="SqlDataSource1" /> • This uses the DataSourceID property to identify the ID property of the data source control supplying the data, and is a common feature across the data source controls. • By default the GridView displays all columns from the underlying data source, but this can be turned off by setting the Auto-GenerateColumns property to False, and by explicitly defining the columns. • This will be covered in detail in the Customizing the GridView Control section.

  20. Filtering and Selecting Data • One of the greatest features of the new data controls is the support for parameterized queries, which allows you to filter data dependent upon external criteria, such as another control, or a query string. • You can filter data at the database by using SelectParameters, or in the Web page after the data has been fetched, by using FilterParameters. • Selection is useful if the set of data being searched is large, because only a subset of the data is returned from the database to the page. • Filtering is useful when the data is cached, because it reduces the overhead on the database.

  21. Filter and Select Parameters • There are seven sources of data that can be applied to selects and filters. • ControlParameter, which takes its data from an ASP.NET server control • CookieParameter, which takes its data from a Cookie • FormParameter, which takes its data from an HTML FORM control • Parameter, which is the base class for the other parameters, and has no default source of data • ProfileParameter, which takes its data from a Profile property • QueryStringParameter, which takes its data from a query string value • SessionParameter, which takes its data from a session value

  22. Filter and Select Parameters • These parameters provide a great deal of flexibility in filtering data in a declarative manner. • All of these parameters support the properties shown in Table 3.1. • In addition to these properties, each parameter type has its own specific properties, as shown in Table 3.2.

  23. Table 3.1. Common Properties of All Parameter Types

  24. Table 3.2. Specific Parameter Type Properties

  25. Filter and Select Parameters • In action, all of these parameters work in the same way. • The data to be used in the select or filter is taken from the location specified by the parameter. • So for a ControlParameter, the ID identifies the control, and PropertyName identifies the property storing the data. • For example, if a TextBox was used as the source of the parameter data, the Text property would be used as the PropertyName. • Simple controls, such as a TextBox, aren't the only source of parameter data; a GridView could use the SelectedValue as the PropertyName, which would use the key field (from the DataKeyNames property) for the parameter value. • Configuring parameters can be done with the wizard, declaratively in source view (which is what the wizard generates), or in code.

  26. Selecting Data Using the Configuration Wizard • The configuration wizard is extremely easy to use, but it's worth pointing out the key areas where query parameterization is affected by it. • The first place (see Figure 3.3), shows the WHERE button, which will open a window allowing the parameters to be configured.

  27. Figure 3.3. Adding a WHERE clause to a SqlDataSource control

  28. Selecting Data Using the Configuration Wizard • In Figure 3.4, you can see on the left side of the window that three pieces of key information are required: • The Column, which is the column in the set of data that you filtering on. This is the column name added to the WHERE clause in the SQL • The Operator, which gives different options for comparison, such as equals, greater than, and LIKE • The source, which is the source of the value, and matches the parameter types

  29. Figure 3.4. Selecting the parameter type

  30. Selecting Data Using the Configuration Wizard • Figure 3.5 shows how to configure the source of the data for the filter. • In this case, you can see that TextBox1 is selected, and you can see the SQL Expression as it will be added to the code. • Clicking Add will create the WHERE clause.

  31. Figure 3.5. Selecting the source of the parameter data

  32. Selecting Data Using the Configuration Wizard • The wizard doesn't do anything more than provide a way to visually set the SelectParameters on the control, so you can also set these manually if desired.

  33. Selecting Data Declaratively • To select data, you use the SelectParameters of the data source, as shown in Listing 3.1.

  34. Listing 3.1 shows the results of configuring a SqlDataSource control with the wizard. • Here the select command has had the WHERE clauses added. • Notice that the parameter in the WHERE clause is treated just like a standard SQL Server parameter, by preceding it with the @ character. • Also notice that the Name property of the ControlParameter matches the parameter name in the query. • You can use any value for the name of parameters, but it must match the actual parameter, and keeping it the same as the column name makes it easy to read.

  35. Additional parameters can be added if necessary, as shown in Listing 3.2. • Here there are three parameters. • This first is as shown earlier, while the second is matching only rows where the UnitPrice is below a certain value, and the third matches the discontinued column. • The SelectCommand still follows the standard SQL format, but each parameter has an entry in the SelectParameters. • The first two take their values from controls, the first from the Text property of a TextBox and the second from the SelectedValue of a list. • This shows that you can take the value from any property of a control. • The third parameter takes its value from the QueryString, showing that you can mix different types of parameters in the same query.

  36. Filtering is slightly different, in that the SelectCommand is not modified, but a FilterExpression property is added, and FilterParameters is used instead of SelectParameters, as shown in Listing 3.3.

  37. Notice that parameters in the FilterExpression aren't explicitly named they are positional. • So {0} refers to the first parameter in FilterParameters; subsequent parameters would be {1}, {2}, and so on.

  38. Selecting Data in Code • When used declaratively, filtering and selecting requires no code, and provides a simple way to link data-bound controls to other controls, or other sources of filter data. • You cannot directly filter or select from within code, although there are events that allow you to modify parameter values before an action takes place. • The SqlDataSource control does have a Select method, but this only performs sorting or paging.

  39. Updating Data • For data updates, the SqlDataSource control provides a similar model to that of selection, where there is a SelectCommand, a SelectCommandType, and SelectParameters. • For modifying data, we have the properties shown in Table 3.3.

  40. Like the select features, these can be configured manually or via the wizard. • For the latter, you select the Advanced option when selecting your table or query, and the window that pops up (see Figure 3.6) allows you to have the data modification statements automatically added to the data source. • You also have the option of adding optimistic concurrency, which adds a column check to each column being modified. • This ensures that data is only updated if all of the columns are the same, thus preventing you from overwriting data that someone else has updated. • This is covered in Chapter 5 in more detail.

  41. Figure 3.6. Adding data modification to the SqlDataSource control

  42. Once configuration has finished, you'll find the commands and parameters set. • For example, consider Listing 3.4. Here the data source has been configured with only three columns to make things easier to see. • Each of the commands is a normal SQL statement, and you can see how the parameters match up appropriately. • For the DeleteCommand only a single parameter is needed, ProductID, because this uniquely identifies the row. • This information is automatically taken from the key value fields from the database, so if you have a table whose unique key is multiple columns, you will require multiple DeleteParameters.

  43. For the UpdateCommand there are three parameters: ProductName and UnitPrice, which are updated, and ProductID, which identifies the row to update. Notice that even though these take different actions in the command, the parameters within the UpdateParameters are defined in the same way. • For the InsertCommand, and the associated parameters, only two are requiredthe two defining the data to be inserted. • Figure 3.7 shows how the <asp: Parameter/> objects are mapped to the command parameters.

  44. Figure 3.7. Mapping the parameters into the command

More Related