Creating a Database for our site - Part 3

We have created a database and added a table in it. The table has a structure with column names and data types but it does not have any data in it. In the next steps, we are going to add our sample data into the table:

• In the Database Explorer, right click on the table name. From the menu, select Show Table Data option. A new tab will open up in the IDE. The process here is simple. We need to fill up the columns with our sample data. Use the TAB key to move to the next cell. There is no need to enter anything in the BookSerialNo column. It will start with the value 1 and increase by 1 with every row. Once we fill up all the data, we can close this tab as well as the table design tab.

Now that we have our sample data stored in the table, we can create a page to display this data. This will require the following process:

First, we need to add a Data Source control and configure it so that it can connect to our database and retrieve the data.

Next, we will add a Data Bound control that will display the retrieved data on the page.

• Add a new Web form to the project. Let us call it `Collection.aspx'. Remember to check the `Select master page' checkbox while adding the page.

• Switch to the design view of the page. In the content box, we can type a message about the page. Next, drag the `SqlDataSource' control from the `Data' section of the toolbox and drop it inside the content box, just below the message.

Before proceeding with our project, let us spend a few minutes on the topic of Data Source controls. Data source controls are used to connect to the source of data (where the data is actually stored). Once the data source control is connected to the data source, we can read data from the source or write data to the source. However, the data source controls cannot display the retrieved data on the page. For displaying the data, we use Data Bound controls.

ASP.NET 2.0 provides many Data source controls for connecting to a variety of databases. These are:

SqlDataSource control - used to connect to most relational databases that understand the SQL language. These include Microsoft SQL Server and Oracle. Since we are using SQL Server 2005 Express, we will use this data source control.

AccessDataSource control - specifically used for Microsoft Access databases.

XmlDataSource control - used for connecting to XML data.

ObjectDataSource control - used for connecting to business objects created by the developer in an application.

SiteMapDataSource control - we have already seen that this control is used for creating a navigation system for our site.

As soon as we drop the control on the page, its smart tag menu will open up. Click the Configure Data Source option. We will get a dialog box like this:

The first thing to do is to select the data connection. Our application will use this connection to connect to our database. Expanding the drop down list will display the connection we have created in the Database Explorer. Select that connection and click on the `Next' button.

• The next step will ask us if we want to store the connection string in the application's configuration file. The connection string is actually a text string that contains information about our data connection. It is a property of data source control (remember that controls have properties). While working in visual drag and drop mode like where we are working on now, there is no need to know about the connection string. However, there is one thing we should be careful about. The connection string contains information such as the username and password used to connect to the database. If we save the connection string into the application's configuration file, this information is safe. This makes the application easy to maintain too. Hence, it is recommended that we store the connection string into the application's configuration file:

Accept the default name provided and click on the `Next' button to continue.

• Now comes the interesting part. We need to specify what data we want to use on this page. Our aim is to display a list of all the books and their details to the user. However, we do not want to display the BookSerialNo column as it is of no significance to the user.

Select all the columns except the BookSerialNo column. The equivalent SQL SELECT statement will be generated automatically (we love the VWD Express already!). The SQL statement specifies that we want to use data from the selected columns (BookISBN, BookTitle, BookAuthor, BookPublisher and BookCategory) of the Books table. See how simple the SQL statement is - SELECT [column names] FROM [table name].

Click on the `Next' button.

• Well, we are almost done. The last thing we need to do is to test the query to verify if we are getting the correct result:

Click on the `Test Query' button. A grid will display the details of the books in the table. That is what we need. Click on the `Finish' button to complete the configuration of SqlDataSource control.

We have configured our data source but to display the data on the page, we need one more control - a Data Bound Control.

As we have discussed earlier, the connection to the source of data is made using Data source controls. However, data source controls cannot display the data on the page. Data bound controls are used for displaying the data that is retrieved by the data source control.

ASP.NET 2.0 provides many data bound controls. Some commonly used ones are - GridView control, DataList control, DetailsView control, FormView control and Repeater control. In fact, we can bind simple controls like a textbox to a data source and display the data.

For our purpose, we will use the GridView control to display the data to the user. Let us do it now:

• From the Data section of the toolbox, drag the GridView control and drop it just below the SqlDataSource control. The smart tag menu of the control will open up. The very first task here is to select the data source for this control:

Expand the Choose Data Source drop down list and select SqlDataSource1 option. This will bind the GridView control to the SqlDataSource control and the GridView control will display the data that was configured for the SqlDataSource control (all the details of the books in the table). This is how the data source control and data bound controls work. The data source controls get the required data from the data base but they cannot display the data. The display part is performed by the data bound controls.

Once we choose the data source for the GridView control, we can specify a few more options for the control. If there are too many records in the tale, we can check the `Enable Paging' option to display the data in different pages. Checking the `Enable Sorting' option will allow the users to sort the data on different columns by clicking on the column names (headings).

Finally, we can change the style of the GridView control by clicking on the `Auto Format' option and selection a suitable style from the resulting dialog box:

Now is the time to test whatever we have done so far. Save the project and then run it by pressing the `Ctrl+F5' keys. As you can see, the browser displays the page with the entire data about the books. At the bottom of the page, there is a link for the second page. Clicking on it will display the rest of the books. Clicking on any column header will sort the data on that column. All this with absolutely no code at all!