Retrieving Data from Microsoft SQL Server 2008 Using ASP.NET 3.5

 Purposely, this article is written for first time developers in ASP.NET and MS SQL server 2008. However it is highly recommended to read the pre-requisite tutorial: "Creating an ASP.NET Database using MS SQL 2008 in Visual Web Developer 2008," because the database that you create in that article will be used in the exercises illustrated in this tutorial.

As a review, the sample database table has four records, or rows, in the database, and six fields:

If you are ready, then let's start the ball rolling.

Introducing: ASP.NET Data Source Controls

Most of the web development work in ASP.NET 3.5 is handled by so-called "web controls," a click-and-drag strategy implemented in Visual Web Developer Express 2008. For example, if you want to include a server-side text box form, simply click and drag Textbox in the toolbox to the Default.aspx source code.

Web controls make web development faster and simpler (because of the simple click-and-drag method, with the source code automatically generated by Visual Web Developer). They also save the developer from needing to write sophisticated and complex ASP.NET scripts.

To retrieve data from the MS SQL 2008 database, you need an ASP.NET 3.5 page and a working database (which is thoroughly discussed in the article linked to above). Once you have these preliminary requirements, though, there is still no way for ASP.NET to communicate, connect and retrieve records from the MS SQL database.

In ASP.NET, a web control set called "Data Source" controls is designed to connect to and interact with the database. Currently there are two important Data Source controls in ASP.NET (which you can see in View à Toolbox à Data):  SqlDataSource and AccessDataSource.

SqlDataSource is primarily used for retrieving records from SQL databases such as MS SQL Server 2008 (or other supported databases, like Oracle). AccessDataSource is used when retrieving records from MS Access database.

In the previous example, since you are using an MS SQL Server 2008 database, you will be using SqlDataSource controls to retrieve data. The following are the steps you need to take:

Step 1: Launch Visual Web Developer Express 2008.

Step 2: Open "firstdatabasetest" projects (containing the database, default.aspx and other files created from the first tutorial, referred to earlier). 

Step 3: You will then see the source code of Default.aspx. Go to View à Toolbox à SqlDataSource, click and drag "SqlDataSource" to between <form id="form1" runat="server"><div> and </div></form>

After the click and drag, the source code will be updated automatically:

    <form id="form1" runat="server">

    <div>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>

    </div>

    </form>

Configuring SqlDataSource Controls

So far, the data source control with the ID "SqlDataSource1" has been added to the ASP.NET 3.5 web page, but this has not been configured. Configuration is necessary because the data source control needs to know two things: first, what database it needs to connect to, and second, what SQL query you are going to execute. Configuring the SqlDataSource controls is quick and easy:

Step 1: Click the Design view (currently Visual Web Developer 2008 Express offers three possible views of a web page being edited: "Source," "Split" and "Design").

Step 2: In the Design view you will see the button labeled "SqlDataSource -SqlDataSource1." Right on that button, select "Configure Data Source."

Step 3: Under "Choose your Data Connection," select the database you created in the previous tutorial (in this case, Firstdatabase.mdf). Since the database is stored under App_Data, you do NOT need to click "New Connection." Finally, click "Next."

Step 4: If you see the message "Do you want to save the connection in the application configuration file?" click "yes." This will automatically save the connection settings to web.config file, which will save time in developing/editing pages since you will not need to add the connection string to every ASP.NET web page. After checking "yes," click next.

Step 5: Since you are going to retrieve database records and display them on the browser, the SqlDataSource1 controls ask you what query you would like to execute.

Say for example that we are going to display all the database table records on the browser, and then we are going to check "Specify columns from a table or view." Under the name, select "movies," since this is the database table you created in the previous tutorial. Under "Columns," check "*" which means you are asking SQL to return ALL table records/rows. Click Next.

Step 6: You will then be presented with a "Test Query." The purpose of this is to ensure that the data source controls are returning the correct and intended query. So click "Test Query." You should see it return all of the database table's rows. When the test's query results are OK, click "Finish."

The configuration is complete. When you go back to "Source" view, the data source control is automatically updated (reflecting the settings configuration that was done):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

            SelectCommand="SELECT * FROM [movies]"></asp:SqlDataSource>

Displaying the Query Results using Data Web Controls

It is important to note that even though data source controls (like SqlDataSource) are used to interact with a database, they CANNOT BE USED to display data back to the browser. Another control is used to grab the data source results and display them back to the browser.

These are called "Data Web controls." In ASP.NET 3.5, there are two important data web controls, GridView and DetailsView. GridView is used to display the records at once, in a table grid format. DetailsView, on the other hand, is used to display records in a page format, one at a time. It can even use the "pagination" method to let the user view other records by clicking the pagination link.

Since you are interested in displaying all of the records to the web browser at once, you are going to use the "GridView" data web control. To add that to the web page, do the following:

Step 1: Add two break lines after the end of </asp:SqlDataSource>

Example:

<br />

<br />

Step 2: Go to View à Toolbox à Data à Grid View, and click and drag "GridView" below the two <br /> tags. So after dragging, the source code will be updated to:

    <form id="form1" runat="server">

    <div>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

            SelectCommand="SELECT * FROM [movies]"></asp:SqlDataSource>

    <br />

    <br />

        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

    </div>

    </form>

Tell GridView the Data Source

Even though you have successfully dragged the GridView web control, it still does not know from which data source to grab data. So you have to go to the "Design" view, right click on the GridView, and then click "Show Smart Tag."

When it asks for the data source, simply select SqlDataSource1. Save all your files, and then when you go to the "Source view," you will see the Grid view codes added automatically in addition to the previous ASP.NET 3.5 web controls:

    <form id="form1" runat="server">

    <div>

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"

            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

            SelectCommand="SELECT * FROM [movies]"></asp:SqlDataSource>

    <br />

    <br />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

            DataKeyNames="movieid" DataSourceID="SqlDataSource1">

            <Columns>

                <asp:BoundField DataField="movieid" HeaderText="movieid" InsertVisible="False"

                    ReadOnly="True" SortExpression="movieid" />

                <asp:BoundField DataField="movietitle" HeaderText="movietitle"

                    SortExpression="movietitle" />

                <asp:BoundField DataField="moviegenre" HeaderText="moviegenre"

                    SortExpression="moviegenre" />

                <asp:BoundField DataField="runningtime" HeaderText="runningtime"

                    SortExpression="runningtime" />

                <asp:BoundField DataField="director" HeaderText="director"

                    SortExpression="director" />

                <asp:BoundField DataField="datereleased" HeaderText="datereleased"

                    SortExpression="datereleased" />

            </Columns>

        </asp:GridView>

    </div>

    </form>

After all files are saved, go to File -> View in Browser and then you'll see all of the database records displayed in the browser:

Of course, the examples illustrated in this and the previous tutorial are very simple, but the concepts are the same as those used to create more complex and sophisticated ASP.NET 3.5 websites using SQL Server 2008.