ado.net /asp.net

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

Page 1 of 9

Creating the Database Structure to Work with ADO.NET

This week and next week we are going to utilize ADO.NET, a set of class libraries that provide

data access services to .NET languages such as C#, to connect to a SQL Server database to read

table records as well as to insert new records, update existing records, and delete them. This

week, we are going to read data from the database and display them in a GridView control in an

ASP.NET Web Forms project and next week we are going to modify the project to add data

update, delete, and insert capabilities to it.

Before we can start, we need to create the database we are going to use in the project, which

has two tables with some initial data. In lecture 2 of this week we are going to connect to this

database from within a Web Forms project and display the data in one of its tables.

 Open up SQL Server Management Studio and connect to the instance you created

previously (in my case the instance is called THINKPAD\SQLEXPRESS).

 Right click the Databases node in the Object Explorer and choose New Database… as

seen in the image below.

 Enter Demo for Database name and click OK to create the database.

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

Page 2 of 9

 Back in the Object Explorer, right click the Demo database under the Databases node

and choose New Query as seen below. Opening up a new query window this way ensure

that it is using the Demo database. If the new database does not appear on the list, right

click the Databases node and choose Refresh to force it to appear.

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

Page 3 of 9

 Create a table called Player as seen in the following image. Please note that although

the script for the rest of this lecture is included in the lecture files of this week, I strongly

recommend that you type in all the statements shown in the remaining of this lecture

yourself as it will greatly help you with improving your SQL skills.

 Now create another table called Team according to the settings shown in the following

image.

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

Page 4 of 9

 The player_team column in the Player table is meant to reference the team_id column

in the Team table and as such you will need to create a foreign key constraint on the

Player table that establishes this relationship. The following image shows how this

foreign key is created.

 Next, back in the Object Explorer, expand the Tables node under the Demo database,

right click the dbo.Player table, and choose Design in the context menu as shown below.

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

Page 5 of 9

 A window showing the structure of the Player table is next displayed. It shows the

column names along with their data types as well as a checkbox indicating whether or

not they accept NULL values. Note the golden key icon appearing to the left of the

player_id column- as you may have guessed this indicates that player_id is the table’s

primary key. Here you can make changes to the column names, remove the primary key

by right clicking the key icon, and make data type and NULL acceptance adjustments.

Since the table is currently empty, you can make all sorts of adjustments but if there

were rows in the tables, you would be limited in terms of what you could change. For

example, if the player_gender is already populated for some records with values Male

and Female, you won’t be able to change the data type of the column to INT because

these existing VARCHAR values are not compatible with a numeric value that an INT

column can store. Now close the window without saving any of the changes that you

may have made.

 Now, expand the dbo.Player node under the Tables node. Then expand the Keys node

under it to view the keys defined on the table. As you can see in the following image,

the golden key shows the table’s primary key and the grey key shows the table’s foreign

key that we defined earlier.

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

Page 6 of 9

 Now open up a new query window but this time by clicking the New Query button on

the main toolbar as seen below.

 To ensure that you are working with the correct database, check the database

dropdown list on the main toolbar. If Demo is not currently selected, go ahead and

select as shown below. Alternatively, you can add the USE Demo; statement as seen last

week in the beginning of the script to make sure that that any statement after that is

run against the Demo database.

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

Page 7 of 9

 In the new query window, insert three records to the Team table as shown in the image

below.

 To verify that the records were successfully added, select all rows from the Team table

as seen below. Note that the records just inserted are now displayed in the Results

window.

 Next, insert seven records to the Player table as shown in the following image. Note that

since values in text columns (VARCHAR, NVARCHAR, and etc.) and DATETIME

columns must be enclosed within single quotes in queries, if a single quote character

appears in the actual text in the query, you will need to escape it by adding an additional

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

Page 8 of 9

single quote before the single quote in the value. For example, to insert the value 5’ 11”

into the player_height which is a VARCHAR field, you will need to use '5'' 11"'.

 Finally, select all rows from the Player table to verify that the records just added show

up in the Results window.

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

Page 9 of 9

Congratulations! You have now created the Demo database with two tables called Player and

Team and have populated them with initial data. In lecture 2 we are going to access and display

this data in a Web Forms application.