This is my fourth article on database technology. If I were to order them for someone wanting to learn a little about databases and DBMS (database management systems), I would start with Database Fundamentals, which is an expanded glossary based on the course by the same name, previously offered by Microsoft. In the second article I outlined how to download the sample databases Microsoft has available for testing and education. The third article dealt with displaying Microsoft Access information on a web page and the last article was devoted to displaying MS-SQL data on a web page.

For this article we will take up where we left off and as promised, we'll add formatting to the data we have retrieved and displayed. Keep in mind if you are attempting to do this, the prerequisites necessary are:

• An ASPX web application installed on an ASP server.
• A Microsoft SQL server with Northwind or other database installed.
• Microsoft Visual Studio installed. (I am using VS 2019 Community)

Assuming that you have successfully completed the previous article and are now able to display the Northwind database data, we will start by showing the table on our webpage as we did at the end of that article.

We will modify the following areas:

• Remove any unwanted fields
• Make the column headings more readable
• Change the Supplier ID number to the supplier's name
• Format the Price column to show American currency
• Format the Grid layout with a theme

Remove unwanted fields

1. At the bottom of the code window, click the Design button to open the webpage in design mode. This allows you to easily use many features for the Gridview control that are not intuitive in the Code window. When you become more familiar with this process, you can do everything with code, but using the graphical interface shows you more options.

2. Click on the Gridview to select it. In the upper right corner of the Gridview, click the chevron (>) to open the Gridview Tasks. From the tasks, select Edit Columns to open the fields editor.

3. Removing columns must be done one at a time. Go to the Selected Fields area and select CategoryID column and click the red X to delete it. Do the same for the columns named QuantityPerUnit, UnitsInStock, UnitsOnOrder, ReorderLevel, and Discontinued.

If you click OK and view your webpage in a browser, you should now have only five columns, as shown below.

Rename the column headings

Remember the idea is to make everything more readable for the customer. The column names are designed for the database to read and therefore don't have any spaces. Let's change them to Item ID, Name, Supplier, Price and Category.

1. Repeat the steps above to go back into the Fields Editor. Quick look: Select gridview>click chevron>click Edit Columns.

2. In the Selected Fields area, select the ProductID column. To the right, in the BoundField properties window, under  Appearance, change the HeaderText field to Item ID. Change all the column's HeaderText field as needed.

Your gridview should now have easy to read column names as shown below.

Change the Supplier ID

The Supplier ID number is meaningless to a customer. We want the client to see the supplier's name. In order to do that we have to retrieve that information from the Supplier table. This is probably the most complicated aspect of this exercise because we must modify our data source and build something called an inner join query. This is a great opportunity to get our hands dirty with some actual SQL (Structured Query Language). Because this involves a new query, we will redo most of the items we already completed in this article. Good practice.

1. When adding multiple gridviews on a single page such as this one, Visual Studio will give each of them a different data source name by appending the name with increasing numbers. Always let Visual Studio do the source and connection naming if possible. If you have a page similar to this page, we are now at SqlDataSource4. If you are modifying the same gridview, you will go back to the data source, click the chevron and click Configure Data Source.

2. Make sure you are using the Northwind connection string and click Next.

3. Click the radial button to Specify a custom SQL statement or stored proceedure. Click Next and then click on the Query Builder button.

4. If the Add Table dialog is not open, right-click and open it from the menu. Click the Views tab and add Alphabetical list of products. This is a designed view that was previously setup in the Northwind database.

5. Next click the Tables tab and select the Suppliers table and add it also. You will probably need to drag the edges of the Query Builder window to make it larger. I am not sure why, but it always opens too small to see everything.

6.  From the Alphabetical list of products view, check the boxes for the same fields we previously used except for SupplierID. Then in the Suppliers table check the CompanyName only. To make sure your query is working, click the Execute Query button at the bottom of the window and in the lower pane you should see a preview of your gridview. Then click OK. 

7. In the SQL Statement window you should see your SELECT query. Click Next. If you click Test Query, you will again see your gridview. Click Finish.

Now I will put you to the test! Follow the previous directions to properly name your column headings and put them in the desired order by using the Gridview Tasks and Column Editor. When you are finished, you should have a gridview that looks pretty close to the one below.

Format the price column

We would like the price to be formatted in American currency. We certainly don't need all those zeros and it would be nice to have a dollar sign included. This is a simple change but requires a subtle addition that is somewhat hidden.

1.Again we must open the Gridview Tasks and choose Edit Columns.

2.Select Price and in the Properties scroll down to DataFormatString under Data. For that property type in {0:C}. Click OK.

The C in this data format string tells the gridview it is currency. Using a D would meant date. There is a good explanation of data format strings here.

Give the gridview a theme

The last chore on our to-do list is giving the gridview a theme. We want customers be able to read our products easily and have the items displayed in a pleasing format. Although this may (and probably will) be done using CSS, we will apply a format now using the same tools we have been using. This one is simple.

1.One last time, open the Gridview Tasks.

2.Click on Auto Format and choose a theme for your gridview, click Apply and OK.

I have chosen the Autumn theme as shown below.

This exercise shows how well sophisticated (yet free - for the Community version) software such as Visual Studio can incorporate tools that make a programmer's work faster and easier.

Thanks for viewing. Please check out my other articles on education and technology. I welcome any comments!