ado.net /asp.net

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 1 of 17

ADO.NET: Retrieving and Displaying SQL Server Data

In this lecture, we are going to create a new Web Forms project that connects to the Demo

database we created in the previous lecture to display the records in the Player table.

Open up Visual Studio and click FILE -> New -> Project….

Make sure that Web is selected under Visual C# templates, call the project DataDemo, and click

OK to continue.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 2 of 17

Choose the Empty template, check off the Web Forms checkbox, and click OK to continue.

In the Solution Explorer window, right click the project, and choose Add -> Web Form as seen

below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 3 of 17

Call the web form view and click OK to continue.

Open view.aspx (if it is not open already) and switch to the Split view as seen below.

In the Toolbox window, expand the Data section and select the SqlDataSource control as seen in

the following image.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 4 of 17

Now drag the SqlDataSource control from the Toolbox and drop it in the Design window of the

Split view. Next click the control so that its smart tag (the arrow on its top right corner) appears.

Click the smart tag and choose Configure Data Source… as seen in the image below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 5 of 17

Since this the first time you are using the SqlDataSource control, there aren’t any saved

connections so you will need to create one by clicking the New Connection… button as seen in

the image below.

In the Choose Data Source dialog box, select Microsoft SQL Server and click Continue as seen

below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 6 of 17

Next, on the Add Connection dialog box, click the Refresh button to propagate the Server name

dropdown list. Then choose your database instance from the dropdown list. In my case, for some

reason, the dialog was only able to locate THINKPAD, which is the machine hosting my

SQLEXPRESS instance. All I had to do was to force it to find the instance by appending

/SQLEXPRESS to THINKPAD. When the Server name is selected, it automatically populates the

Select or enter a database name dropdown list with the databases in that server. In this case, we

are going to choose the Demo database as seen in the image below. The connection can be tested

by clicking the Test Connection button.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 7 of 17

If the connection settings are correct, a success message is shown as seen below. Go ahead and

click OK to dismiss the message dialog. Next, click OK on the Add Connection dialog to

continue.

The newly added data source should now be displayed and selected in the Choose Your Data

Connection dialog box. Click Next to continue.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 8 of 17

On the next dialog box, choose to save the new connection and click Next to continue.

On the next dialog box, check off the Specify columns from a table or view checkbox, pick the

Player table from the dropdown list, check off the * checkbox (which selects all columns and

rows from the table), and click Next to continue.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 9 of 17

On the next dialog page click the Test Query button as seen below. This should populate the

window in the middle with all data from the Player table for preview purposes.

If everything was set up properly, the Player data should now be displayed in the preview panel.

Next click Finish to complete configuring the SqlDataSource.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 10 of 17

Back in the Source window of the Split View tab, you can see that the definition of the

SqlDataSource appears within a div element in the body of the HTML as seen in the image

below.

Now drag a GridView control from under the Data section of the Toolbox window and drop it

within the first div element of form as seen below.

Click the message indicating that the Design view is out of sync with the Source view to

synchronize them as seen in the next image.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 11 of 17

Once the Design view is synced with the Source view, click the GridView to activate its smart

tag. Click the smart tag to open up its context menu. In this context menu, choose the

SqlDataSource you added/configure earlier in the Choose Data Source dropdown.

As seen in the following image, the GridView should now be updated to display a preview of

what it would look like at run time. Here you are going to change the column headers (currently

being the same as table column names) to more display friendly names by clicking the GridView

smart tag and choosing Edit Columns in the context menu.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 12 of 17

Next, in the Fields dialog box, choose player_id in the Selected fields panel on the bottom left

hand side. Then on the right hand side panel, change its HeaderText to ID as seen in the image

below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 13 of 17

Follow the same steps to change the HeaderText for the remaining columns according to the

following table:

Field HeaderText

player_first_name First Name

player_last_name Last Name

player_DOB Birthday

player_gender Gender

player_height Height

player_position Position

player_team Team

Once all columns are updated with their new HeaderText property values, click OK to dismiss

the Fields dialog box.

As seen in the following image, the GridView control should now be updated to display a

preview of what it would look like at run time.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 14 of 17

You are now ready to give the application a test run. Hit F5 to load the project in the browser.

The result should look like what’s shown in the image below.

In the GridView seen above, Birthday values are not in a display friendly format. You are going

to fix that by changing the display format for this column to MM/DD/YYYY. Additionally, you

are going to make a couple of adjustments to the GridView settings.

Close the browser to stop the application from running.

Back in the Design window of the Split view tab, select the GridView to activate its smart tag.

Next, click the smart tag and in the context menu, check off the Enable Paging and Enable

Sorting checkboxes, and click Edit Columns as seen in the image below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 15 of 17

Select the Birthday field in the Selected fields panel on the left and on the right hand side scroll

down to find the DataFormatString property and change it to {0:MM/dd/yyyy}. Next, click OK to

dismiss the dialog box.

Now give the application another test run by hitting F5. The result should look like the following

image. Note the following changes:

 Birthday is now displaying in the MM/DD/YYYY format.

 The column names are sortable. This is the result of checking off the Enable Sorting checkbox earlier.

 If there were more records in the result set, they would have displayed across multiple pages. This is the result of checking off the Enable Paging checkbox earlier.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 16 of 17

If you click on the header of any column, the records are sorted on that column, For example,

clicking First Name, rearranges the records displayed in the GridView on the First Name values

as seen below.

We are now going to wrap up this week’s lecture by formatting the GridView’s appearance.

Close the browser to stop the application from running.

Back in the Design window of the Split view tab, select the GridView to activate its smart tag.

Next, click the smart tag and in the context menu choose Auto Format as seen below.

Northeastern University, ITC 2811 – Advanced Application Development Week 7- Lecture 2

Page 17 of 17

Choose a scheme such as Black & Blue 1 and click OK to dismiss the dialog box.

Run the application one last time by hitting F5 and notice how the GridView is now displayed

using the scheme selected in the previous step.