Manipulating data in asp.net 2.0 46: Retrieving Data using SqlDataSource Controls _ self-study process

Introduction

So far, the tutorial we've explored is a hierarchical architecture composed of the presentation layer, the business logic layer, and the data access layer. The data access layer and the business logic layer are mentioned in the first and second chapters of the tutorial respectively. In this tutorial displaying data with the ObjectDataSource, we explored how to use the new control of ASP.net 2.0 to display data in the presentation layer--objectdatasource controls.

This tutorial has so far used this hierarchy to process data. Bypassing this architecture, however, by directly placing data queries and business logic on a Web page, you can also achieve the goal of accessing, inserting, updating, and deleting database data directly on ASP.net pages. For a very large or complex application, using a hierarchy is important to the success and maintainability of the program. However, there is no need to use a hierarchical architecture for very simple programs.

ASP.net 2.0 provides 5 built-in data source controls, SqlDataSource controls, AccessDataSource controls, ObjectDataSource controls, XmlDataSource controls, and SiteMapDataSource controls. The SqlDataSource control can access and update data directly from a relational database, including databases such as Microsoft SQL server,microsoft access, Oracle, MySQL, and so on. In this chapter and the next 3 chapters, we will explore how to use the SqlDataSource control to query and filter database data, including inserts, updates, and deletes.

Figure 1:asp.net 2.0 of 5 built-in data source controls

Comparing ObjectDataSource controls and SqlDataSource controls

Theoretically, both the ObjectDataSource control and the SqlDataSource control are proxies for accessing data. As discussed in the tutorial displaying data with the ObjectDataSource, you can set the data type of the presentation data in the ObjectDataSource control and the methods that are invoked to select, INSERT, UPDATE, and delete data. Once the settings for the ObjectDataSource control are complete, data Web controls such as the GridView, DetailsView, DataList, and so on, can invoke their select () by binding the ObjectDataSource Control, Insert (), Delete (), and Update () methods method.

Although the SqlDataSource control has the same functionality as the SqlDataSource control, when using the SqlDataSource control, we must provide a detailed database connection string and the Ad-hoc to perform the selection, update, insert, and delete the data SQL query or stored procedure. When you call the SqlDataSource control's Select (), Insert (), Update (), and Delete () methods, the SqlDataSource control connects to the database and passes the appropriate SQL query. The following figure shows how these methods connect to the database, pass the query, and return the results.

Figure 2 The SqlDataSource Control acts as a proxy for accessing the database

Note: In this chapter we focus primarily on how to get data from a database, and in later tutorials we'll discuss how to set up SqlDataSource controls to support inserting, updating, and deleting data.

SqlDataSource Controls and AccessDataSource controls

In addition to the SqlDataSource control, asp.net 2.0 also packs AccessDataSource controls. These two different controls make many developers think that the AccessDataSource control is primarily designed to deal with Microsoft Access databases, and that the SqlDataSource controls are designed to deal with Microsoft SQL Server. The reality is that the SqlDataSource control can be with almost all. NET can access the relational database to deal with. Includes any OLE DB, odbc,compliant data stores, such as Microsoft SQL Server, Microsoft Access, Oracle, Informix, MySQL, and PostgreSQL 。

The only difference between the SqlDataSource control and the AccessDataSource control is that the database connection information for the AccessDataSource control requires only the access path to the Access database file. The SqlDataSource control needs to provide a complete connection string.

First step: Create SqlDataSource page

Before we explore using SqlDataSource controls to manipulate database data directly, let's take some time to add the asp.net pages that are needed in this and next sections of our site project. First add a folder named SqlDataSource, add the following pages inside, and configure to use the Site.master master page.

Default.aspx
Querying.aspx
Parameterizedqueries.aspx
Insertupdatedelete.aspx
Optimisticconcurrency.aspx

Figure 3: Adding pages for SqlDataSource related tutorials

Similar to other folders, the Default.aspx in the EditInsertDelete folder will list these tutorial chapters. Remember that the user control provides this functionality. So, drag a user control from Solution Explorer to the Design view of the page to add it to the Default.aspx page

Figure 4: Adding a user control to the Default.aspx page

Finally, add the 4 pages to the site map. Open the Web.sitemap file and add the following code after the "adding Custom Buttons to the DataList and Repeater" SiteMapNode tag:

<sitemapnode url= "~/sqldatasource/default.aspx" title= "Using the" SqlDataSource control "description=" Work directly With the database data using the SqlDataSource control. "  > <sitemapnode url= "~/sqldatasource/querying.aspx" title= "Retrieving Database Data" description= "Examines Query data from a database, that can then is displayed through a data Web control. /> <sitemapnode url= "~/sqldatasource/parameterizedqueries.aspx" title= "parameterized Queries" description= " Learn to specify parameterized WHERE clauses in the SqlDataSource ' s SELECT statement./> <sitemapnode ur L= "~/sqldatasource/insertupdatedelete.aspx" title= "inserting, updating, and deleting Database Data" description= " ow to configure the SqlDataSource to include INSERT, UPDATE, and DELETE statements. "/> <sitemapnode url=" ~/s Qldatasource/optimisticconcurrency.aspx "title=" Using optimistic concurrency "description=" Explore how to augment the SqldatasourCe to include support for optimistic concurrency. "/> </siteMapNode>

 

Figure 5: Update the site map to include the new page

Step two: Add and set the SqlDataSource control

Open the Querying.aspx page in the SqlDataSource folder and switch to the design attempt. Drag a SqlDataSource control from the Toolbox to the designer and set its ID to Productsdatasource. Like ObjectDataSource, SqlDataSource does not produce any declarative markup, so now it looks like a gray square on the page. Click on the smart tag of the SqlDataSource control, click the "Configure Data source" link to enter the DataSource Configuration Wizard.

Figure 6: Click the "Set Data source" link in the smart tag.

The Configuration wizard for the ObjectDataSource control and the SqlDataSource control is somewhat different, but the ultimate goal is the same: a detailed description of how to fetch, INSERT, UPDATE, and delete data from the database. The ObjectDataSource control explicitly specifies the database to be accessed and provides details of the SQL query declaration or stored procedure to be used

The first step in the wizard is to select the database you want to access, include the database that is placed in the App_Data folder in the Drop-down list, and the database that is added to the data Connection node in Server Explorer. Once we add a connection string to the Northwind.mdf database in the App_Data folder to the project's Web.config file, the connection string appears in the Drop-down list option. As shown below, select it and point to "next".

Figure 7: Selecting NorthwindConnectionString from the dropdown list

After you select the database, the wizard goes to the how to retrieve data from the database interface. There are 2 ways: the first is to specify a custom SQL statement or stored procedure, and the second is to specify a column from a table or view.

　　Note: Let's explore the example of using the "Specify columns from table or view" option, and then discuss the example of the "custom SQL statement or stored procedure" option later.

Figure 8 is the picture when we click the "Specify columns from table or view" radio button, where we select the Products table and return to ProductID, ProductName and UnitPrice columns. When the selection is complete, the SQL statement appears in the box at the bottom: SELECT [ProductID], [ProductName], [UnitPrice] from [Products]

Figure 8: Returning data from the table products.

After you complete the above settings, click the Next button to enter the test query interface, where you can test the results of the query set up in the previous step. Click the "Test Query" button to execute the query.

Figure 9: Click "Test" to retrieve the data from the select query.

Finally, click the Finish button to complete the wizard.

As with ObjectDataSource, SqlDataSource's wizard settings only complete the assignment of SqlDataSource Control properties, that is, connectionstring properties and SelectCommand properties. After the setup is complete, our SqlDataSource control code should be similar to the following:

<asp:sqldatasource id= "Productsdatasource" runat= "connectionstring=" <%$ connectionstrings
 : Northwndconnectionstring%> "
 selectcommand=" SELECT [ProductID], [ProductName], [UnitPrice] from [Products] >
</asp:SqlDataSource>

The ConnectionString property provides detailed information on how to connect to the database, can be assigned a value using a full, hard-coded connection string, or can be assigned with a connection string in a Web.config file. When you use a string in a Web.config file, the syntax is: <%$ expressionprefix:expressionvalue%> Expressionprefix, in particular, is connectionstrings. And Expressionvalue is the name of the connection string in the Web.config file <connectionStrings> section. For more information on this syntax please refer to: asp.net Expressions Overview

The SelectCommand property is a detailed statement of the SQL query statement or stored procedure.

Step Three: Add data Web Controls and bind to SqlDataSource

Once you have set the SqlDataSource, you can bind it with data Web controls such as GridView or DetailsView. In this tutorial we use the GridView, drag a GridView control from the toolbox to the page, and select Productsdatasource in the "Choose Data Source" in the smart tag. This binds the GridView control to the SqlDataSource control we set earlier.

Figure 10: Adding the GridView control and binding to SqlDataSource

When the binding is complete, Visual Studio automatically adds a BoundField or CheckBoxField to each column that the GridView returns from the data source control. As far as this article is concerned, since SqlDataSource returns three columns from the database: ProductID, ProductName, and UnitPrice, Visual Studio automatically generates three columns (three fields) in the GridView.

Take a few minutes to set up three BoundFields of the GridView: Set the HeaderText property of the ProductName field to "Product Name", UnitPrice field to "Price", Formatted as currency. After you modify it, your GridView code should look something like the following:

<asp:gridview id= "GridView1" runat= "Server" autogeneratecolumns= "False"
 datakeynames= "ProductID" Datasourceid= "Productsdatasource"
 enableviewstate= "False" >
 <Columns>
  <asp:boundfield Datafield= "ProductID" headertext= "ProductID" insertvisible= "
   False" readonly= "True" sortexpression= "ProductID" />
  <asp:boundfield datafield= "ProductName" headertext= "Product Name" sortexpression=
   "ProductName"/ >
  <asp:boundfield datafield= "UnitPrice" headertext= "Price"
   sortexpression= "UnitPrice" Dataformatstring= ' {0:c} '
   htmlencode= ' False '/>
 </Columns>
</asp:GridView>

Browsing the page in the browser, as shown in Figure 11, the GridView lists the ProductID, ProductName, and UnitPrice for each product.

The ProductID, ProductName, and UnitPrice of each product are listed in Figure 11:gridview.

When the page is tested, the GridView invokes the Select () method of its data source control. If we use the ObjectDataSource control to test the page, it will invoke the GetProducts () method of the logical layer PRODUCTSBLL class. With the SqlDataSource control, the Select () method will link directly to the database to be accessed and pass the SelectCommand (specifically, in this case, the SELECT [ProductID], [ProductName]. UnitPrice] from [Products]). SqlDataSource passes the returned results to the GridView, which produces one row based on each record returned from the database (a row)

Built-in properties for SqlDataSource controls and data Web controls

In general, the properties of pagination, sorting, editing, inserting, and deleting of data Web controls are specified by the data Web control itself, and are not much related to the data source control it uses. That is, the GridView can freely enable its built-in paging, sorting, editing, and deletion functions, regardless of whether it is bound to a SqlDataSource control or a ObjectDataSource control. However, some properties of the data Web control are affected by the data source control and its settings that are bound to it.

For example, as we discussed in the chapter on efficiently paging through Large amounts of data, when paging is enabled, the data Web control retrieves all records by default every time that you jump the page. Although we only need to display a specific few records. This pattern is very inefficient when the amount of data to be retrieved is large. However, the ObjectDataSource control can simply return those records that need to be displayed on the current page by customizing the paging method, unfortunately the SqlDataSource control does not support custom paging functionality.

By default, the data returned by the SqlDataSource control can be paginated and sorted by the GridView control. To make a demonstration, in the Querying.aspx page, enable paging and sorting in the GridView control's smart tag to see if it works as we expect.

The rationale for paging and sorting is that the SqlDataSource control converts the retrieved database data into a "generic DataSet" (loosely-typed DataSet). Each record that is used to page pages contains

In the dataset, in addition, the dataset supports sorting the returned results. The SqlDataSource control completes these tasks automatically when the GridView requests paging or sorting data.

By default, SqlDataSource returns a DataSet, and you can also make it return a DataReader by setting its DataSourceMode property to "DataReader". Setting to DataReader is often preferred when you want to convert DataReader's search results to ready-made code (existing codes). In addition, DataReader is much simpler and more powerful than a dataset. However, when the DataSourceMode property is set to "DataReader", the data Web control cannot enable paging or sorting, because SqlDataSource cannot tell how many records have been returned in total, and DataReader does not support sorting the returned data.

Step Fourth: Use a custom SQL query or stored procedure

As mentioned earlier, there are 2 ways that the SqlDataSource control retrieves data from the database. In the second step we explored the method of returning data from table products, and now we explore the case of using custom SQL queries.

Add a new GridView control in Querying.aspx, select New data source in its smart tag drop-down list, select Database in the Choose Data source type interface, and set the data source ID to Productswithcategoryinfodatasource ".

Figure 12: Create a new SqlDataSource control and name it as Productswithcategoryinfodatasource

Next, then ask which data connection to use, as we did in Figure 7, select NorthwindConnectionString in the Drop-down list, click Next, in the Configure SQL statement interface, select Specify custom SQL statements or stored procedures, and then click Next to enter the Define a custom statement or stored procedure interface that contains SELECT, UPDATE, delete, insert four tabs, and in each tab you can enter a custom SQL statement in the text box or select a stored procedure in the Drop-down list. In this chapter we discuss the importation of custom SQL statements and explore the use of stored procedures in the following tutorial.

Figure 13: Enter the custom SQL statement or select a stored procedure

You can manually enter a custom SQL statement, or you can use the Query Builder to assist in building. Either way, you should use the following query:

SELECT Products.ProductID, Products.productname, categories.categoryname from
Categories
 INNER JOIN Products On
  Categories.CategoryID = Products.CategoryID

Figure 14: Structured query with Query Builder image

Click Next to go to the "Test Query" interface and click "Finish" to end setup. When the settings are complete, the code for the GridView should look like the following:

 <asp:gridview id= "GridView2" runat= "Server" autogeneratecolumns= "False" DataKeyNames = "ProductID" datasourceid= "Productswithcategoryinfodatasource" enableviewstate= "False" > <Columns> <asp : BoundField datafield= "ProductID" headertext= "ProductID" insertvisible= "False" readonly= "True" sortexpression= " ProductID "/> <asp:boundfield datafield= ProductName" headertext= "ProductName" sortexpression= "ProductName"/&
  Gt <asp:boundfield datafield= "CategoryName" headertext= "CategoryName" sortexpression= "CategoryName"/> </
 columns> </asp:GridView> <asp:sqldatasource id= "Productswithcategoryinfodatasource" runat= "Server" connectionstring= "<%$ connectionstrings:northwndconnectionstring%>" selectcommand= "Select Products.ProductID, Products.productname, categories.categoryname from Categories INNER JOIN products on categories.ca Tegoryid = Products.CategoryID "> </asp:SqlDataSource> 

Figure 15:gridview shows the ID of each product, name and category name

Summarize:

In this chapter we discuss how to use the SqlDataSource control to query and display data. As with the ObjectDataSource controls, they are all proxies for accessing the database. In the Properties window or by setting its Data Source wizard, we can specify the database that it is connected to and the SQL select query to execute (SQL query)

The SQL Select query instance discussed in this article returns the records from the specified query, and the SqlDataSource control can actually contain a WHERE clause with parameters. We will explore the query with parameters in the next chapter.

I wish you a happy programming!

Author Introduction

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.