Retrieving Data from a SQL Server 2005 Database Using the SQL DataSource Control in ASP.NET 2.0
 
Published: 09 Jan 2007
Abstract
In this article Jayaram demonstrates how to retrieve data from a SQL Server 2005 Database using the SQL DataSource Control in a step-by-step manner.
by Jayaram Krishnaswamy
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 49863/ 79

Introduction

SQL Data source control together with other data controls hide most of the hardwiring needed for connecting to and retrieving data from the server by presenting simplified higher level features. Sql Data Source Control together with the GridView data aware control makes data access possible without writing a single line of code. Smart Tags, a new feature available for ASP.NET 2.0, makes this process all the more easier. This article describes the easy route that Microsoft has charted for its beloved users to connect and get the data from SQL Server 2005 to their applications.

Data Source Controls

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 Connectionc 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 Formatc 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 Sourcec

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 WHEREc button you can impose conditions to filter data that fits your needs. Using the ORDER BYc 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 Columnsc

Edit Columnscis 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 Sourcec 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

Source Code

The following is the source code for the Default.aspx page and it includes all the modifications to the design applied to retrieve the result displayed in Figure 25.

Listing 1

<%@ Page Language="VB" AutoEventWireup="false" 
         CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
<h3>Northwind Employees Data</h3>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" 
        runat="server" 
        AutoGenerateColumns="False"
        DataSourceID="SqlDataSource1" 
        EmptyDataText="There are no data records to display." 
        AllowPaging="True" 
        AllowSorting="True" 
        BackColor="White" 
        BorderColor="#DEDFDE" 
        BorderStyle="None" 
        BorderWidth="1px" 
        CellPadding="4" 
        ForeColor="Black" 
        GridLines="Vertical">
            <FooterStyle BackColor="#CCCC99" />
            <RowStyle BackColor="#F7F7DE" />
            <SelectedRowStyle BackColor="#CE5D5A" 
                              Font-Bold="True" 
                              ForeColor="White" />
            <PagerStyle BackColor="#F7F7DE" 
                        ForeColor="Black" 
                        HorizontalAlign="Right" />
            <HeaderStyle BackColor="#6B696B" 
                         Font-Bold="True" 
                         ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:BoundField DataField="EmployeeID" 
                               HeaderText="EmployeeID" 
                               InsertVisible="False"
                               ReadOnly="True" 
                               SortExpression="EmployeeID" />
                <asp:BoundField DataField="LastName" 
                                HeaderText="LastName" 
                                SortExpression="LastName" />
                <asp:BoundField DataField="FirstName" 
                                HeaderText="FirstName" 
                                SortExpression="FirstName" />
                <asp:BoundField DataField="City" 
                                HeaderText="City" 
                                SortExpression="City" />
                <asp:BoundField DataField="PostalCode" 
                                HeaderText="PostalCode" 
                                SortExpression="PostalCode" />
                <asp:BoundField DataField="Country" 
                                HeaderText="Country" 
                                SortExpression="Country" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource 
            ID="SqlDataSource1" 
            runat="server" 
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString1 %>"
            ProviderName="<%$ ConnectionStrings:NorthwindConnectionString1.ProviderName %>"
            SelectCommand="SELECT [EmployeeID], 
                                  [LastName], [FirstName], [City], 
                                  [PostalCode], [Country], [Photo], 
                                  [PhotoPath] FROM [Employees]">
        </asp:SqlDataSource>
    
    </div>           
    </form>
</body>
</html>
Summary



User Comments

Title: sem   
Name: semtjo
Date: 2012-07-23 1:38:28 PM
Comment:
hai test server
Title: sdds   
Name: asdas
Date: 2012-05-29 8:52:13 AM
Comment:
asdasdas
Title: Retirve Data on Lost Focus in ASP.NET   
Name: jaypal
Date: 2011-12-02 11:16:15 PM
Comment:
I need to retrive data from SQLServer on lost focus of textbox or on Selected index change of Dropdownlist box...plz help
Title: Retirve Data on Lost Focus in ASP.NET   
Name: Shekhar Shete
Date: 2011-03-28 1:53:58 AM
Comment:
I need to retrive data from SQLServer on lost focus of textbox or on Selected index change of Dropdownlist box...plz help
Title: retrive data from sql server 2005 in c#code   
Name: Shwetha
Date: 2011-03-14 12:51:38 AM
Comment:
public void AddBrand()
{
DataServiceProvider dataServiceProvider = DataServiceProvider.Instance;
dataServiceProvider.GetData(..............)
}

this is my code.what do i need to write in...........space.
Title: Very Informative   
Name: Vamsee
Date: 2010-05-07 1:16:59 PM
Comment:
This is an excellent,informative article for Novices.
Title: hi   
Name: vani
Date: 2010-04-09 4:15:16 PM
Comment:
How can i get user details from SQL database to C#.net pages and store the in session varibles
Title: hi iam new 2 .net   
Name: vivek
Date: 2009-11-11 6:47:28 AM
Comment:
can u give me the guidence that how learn .net from basic
Title: how can i make manual drop down list connect to the DB   
Name: waleed
Date: 2009-05-17 2:53:54 AM
Comment:
I have a problem that i want to make send data by the drop down list to the table in data base but i can't do that

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-07-17 4:56:53 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search