Data Source Controls, new in ASP.NET, are server controls
that interact with data bound controls such as GridView, FormView and
DetailsView and hide the details of data binding. They not only feed data to
the controls, but also assist in other data manipulation chores such as
insertions, updates and deletions. Each of them works with a particular type of
data provider. The location of these controls in the Toolbox is shown in Figure
1.
The SqlDataSource, AccessDataSource and ObjectDataSource Controls are examples of tabular data
source controls suited for tabular data where as XMLDataSource
and SiteMapDataSource controls are of
the hierarchical type suited for site maps and xml data. Each of the controls
is specified by the provider name and the connection string.
Figure 1
Step 1
Create a web site project with Visual Studio 2005 as shown
in Figure 2. The New Web Site project was named SQLData.
Figure 2
Step 2
In this step a connection will be established with the SQL Server
2005 from where the data is obtained for this application. In order to access
all the available servers, open Server Explorer from Viewà ServerExplorer
drop-down menu as shown in Figure 3.
Figure 3
In the Server Explorer window you will be able to see all
existing connections as shown in Figure 4. If you right click the Data Connections' node from the drop-down that follows you
may establish new connections if you wish. All connections in Figure 4 are in
the disabled state. In order to use them they need to be refreshed.
Figure 4
Step 3
Refresh your server connection making a right click on the particular
source which is shown highlighted here. From the drop-down menu item that
follows shown in Figure 5, click on Refresh. If
needed, you may also modify the connection from the same drop-down menu.
Figure 5
Step3b
In Step 3 an existing connection was refreshed. If this is
the first time a connection is to be made, then the following procedure will
help you in getting the connection set up.
a. View -->Server Explorer will
bring up the Data Connection node in the Server Explorer
window as shown in Figure 4 above.
b. Right click the Data Connection
node to bring up the window shown in Figure 6.
Figure 6
c. Click on the Add Connectionc
short cut. This brings up the Add Connection window
shown in Figure 7. It comes up with the default data source, Microsoft SQL
Server (SqlClient). With this data source you access a data stored on a SQL
Server 2005. You may also access an Oracle server, but the provider will be an
OLEDB provider. This is the new data source in the Microsoft's data access
strategy. The Server name: has the syntax, [machine name/SQL server 2005
instance name]. The authentication used is the SQL Server authentication which
requires the User name and password. These have to be entered. If the
information entered thus far is correct then it is possible to view all the
databases on the server as shown in Figure 7. From this list the Northwind
database is chosen.
Figure 7
d. Now, the Test Connection button
when clicked should bring up the Microsoft Visual Studio
Message window confirming the success of establishing the connection. This adds
the new connection to the Data Connections' node.
Step4
The above connection is made to the Northwind database on
the SQL Server 2005. After the connection is established you should be able to
see your database objects as shown in Figure 8 with all the nodes expanded. In
this tutorial we will be using the Employees table.
Figure 8
Step5
Drag and drop the Employees table
from the Server Explorer window onto the design view of the Default.aspx
page. This brings with it a GridView control
populated by all the columns in the Employees table
as shown Figure 9. In fact, it would come with its smart tags menu open. Figure
9 shows the GridView control that was brought into
the Default.aspx page. The column names of the table
are all represented in the GridView. The SqlDataSource1, which is an instance
of the SqlDataSource class [System.Web.UI.WebControls
Namespace], is added to the control tray.
Figure 9
When clicked, the smart tag, which appears as a small arrow
head in a little box at the top-right of the GridView (see Figure 10), will open
up to reveal the GridView Tasks list. The choice of
data source has already been made.
Figure 10
When the GridView is added to the Default.aspx page the near
blank Default.aspx is filled with the source data for the GridView as shown in
Figure 11 (only few columns are shown). All the columns from the Employees data will have representative column headers in
this code. The code listing is not presented since it is going to be modified
in the process of configuring the data source and will be presented in Listing
1. The source code at that point is what is going to be used by the GridView.
Figure 11
The key feature of drag and drop action is the addition of
the SqlDataControl mentioned earlier. The SqlDataControl adds the following
code to the Default.aspx page (shown in Figure 12).
The picture does not show the complete code, but if you have a copy of Visual Studio
2005 and SQL Server 2005 with Northwind database you can easily recreate this.
The Insert Parameters and Update parameters are in a collapsed state and they
contain all the permitted inserts and updates to the columns.
Figure 12
Step 6
Working with Smart Tags
The tasks are laid out very clearly as shown in Figure 10.
Auto-Formatting:
Click on the menu item Auto Formatc
and from the window that pops-up choose a style from the Select
a Scheme list.
Figure 13
The formatting becomes immediately effective as shown below.
Figure 14
Choose data Source:
Since The DataSource is already chosen this step may be
skipped.
Configure Data Sourcec
When you click on the above menu item the window, which is a
part of the wizard, opens up as shown in Figure 15. In this step the data
connection is to be chosen. Since the drag and drop action created the source,
it is already showing the connection string, NorthwindConnectionString1.
It is also possible to create a new connection from here.
Figure 15
Click on the Next button in Figure
15. This opens up the next step of the wizard, Configure the
Select Statement, as shown in Figure 16. The data may be retrieved from
the data source using a custom Select statement, a named stored procedure on
the database, or by selecting a set of columns from a table that exists in the
database. The first option is the default which has been changed to the Specify columns from a table or view. When this choice is
made you will be able to see all the tables and views in the database. In
Figure 16 the Employees table has been chosen from
the list of tables.
In the area under Columns:,
further filtering of the columns may be made. It is seldom that one needs to
retrieve all columns from a database. Also using the WHEREc
button you can impose conditions to filter data that fits your needs. Using the
ORDER BYc button you may also sort the retrieved data
in various ways. The selection you made appears as a SQL SELECT statement at
the bottom of this screen.
Figure 16
Clicking on the Next button takes
you to the next step in the data source configuration process as shown in
Figure 17. In this stage you will be testing the Select
query from the previous screen in Figure 16. In Figure 17 the screen opens with
the central area empty. When you click the Test Query
button, the retrieved data will fill the empty space as shown. The data from
the selections you made are all in this view. This looks good.
Figure 17
Clicking on the Finish button
brings up the Microsoft Visual Studio warning
message. Since the original fill had all the columns and the selection process
reduced the selection by limiting to a few, the fields have to be refreshed.
Figure 18
The GridView now gets filled up with just the selected
columns as shown in Figure 19. The heading, Northwind
Employees Data was added to the Default.aspx page. SqlDataSource1
control sits in the control tray. It has no graphic user interface, but has a property
window which can be used for modifications.
Figure 19
Edit Columnsc
Edit Columnscis the next item on
the tasks list. Clicking on this item brings up the Fields
window. This window has three main areas and a lot of functionality and the
reader intending to use the other functionalities not covered here should
consult the online help.
The available fields' area displays the data bound fields
chosen in the previous step as well as several other kinds of fields, such as CheckBoxField, ButtonField, etc.
You may select from the available fields by highlighting the field and clicking
the Add button. This brings the added field to the
selected fields' area. This way you may add, remove, and prioritize using the
up /down buttons. Once it gets into the selected fields' area, highlighting the
element would show all the properties of the selected field in the Field
properties' area.
Figure 20
The window shown in Figure 21 shows the properties of field,
LastName. You can make a number of changes to
this field using the property editor such as the header text, its visibility,
editable or not, etc.
Figure 21
After adding all the bound fields in the available fields'
area click on the button, OK. This applies the
changes to the design view of the GridView control as shown in Figure 22.
If needed you may add other columns or make changes to the
design by clicking the Configure Data Sourcec item
and going over the fields list again. The IDE
supports making changes in a very user-friendly, flexible manner.
Figure 22
When you have a large set of data which may exceed the page
length you may want the ability to distribute the data on several pages. You
may also want the ability to sort any of the columns. The Enable
Paging and Enable Sorting
check boxes provide this ability if they are chosen by placing checkmarks. The
effect on the GridView design is immediately visible as shown in Figure 23. The
page numbers appear at the bottom and the column heading gets changed to hyperlinks.
Choosing the Enable Selection check box allows you to
select the individual rows in the display. For this tutorial only the paging
and sorting options were chosen.
Figure 23
At this point you will be able to see the page as designed
up to this point. Click on the Build menu item and
from the drop-down choose to build the page.
Figure 24
After the page is built you may view the page on the web
browser by making a right click on the Default.aspx
page and clicking on the View in Browser item in the
drop-down list. This brings up the Default.aspx page
in the default browser as shown in Figure 25. Here, clicking the column header
text which has the hyperlink would sort that column.
Figure 25
The data retrieved from the data base had only 9 rows and
the default page size is 10. The page size may be changed as well as other
features of the GridView from its properties. The properties of GridView are accessed by right clicking the GridView in the
Design view of the Default.aspx page. The Page Size
property can be set to a suitable value so that the data will appear in several
pages. Moving from page to page will be possible with the hyperlinked page
numbering at the bottom of the GridView.
Figure 26
