Creating a Database - Part 1

Before we may proceed with data handling, we need a database to store our sample data. In this section, we will learn how to store data in a SQL Server 2005 Express database.

First, let us make a list of the data that we need to store. We will keep our database simple, so here is what we need to store:

• ISBN - The International Standard Book Number of the book.
• Book Name- The name of the book.
• Author Name - Author of the book
• Publisher Name - Publisher of the book
• Category - Category of the book such as ASP.NET, C#, VB.NET etc.

We also need some sample data. The table shown below contains the details of some books. We will use this data for our application.

ISBN	Title	Author	Publisher	Category
1590593375	ASP.NET 2.0 Revealed	Patrick Lorenz	Apress	ASP.NET
1590594967	Pro ASP.NET 2.0 in C# 2005	Matthew MacDonald	Apress	ASP.NET
1590595122	Pro ADO.NET 2.0	Sahil Malik	Apress	ADO.NET
1590595726	Beginning ASP.NET 2.0 in C# 2005: From Novice to Professional	Matthew MacDonald	Apress	ASP.NET
1590594193	Pro C# 2005 and the .NET 2.0 Platform, Third Edition	Andrew Toelsen	Apress	C#
1590596404	Pro Visual C++/CLI and the .NET 2.0 Platform	Stephen R.G. Fraser	Apress	C++.NET
1590594398	Pro .NET 2.0 Windows Forms and Custom Controls in C#	Matthew MacDonald	Apress	.NET Development
0471788139	Professional VSTO 2005 : Visual Studio 2005 Tools for Office	Alvin Bruney	Wrox	.NET Development
0470042583	Beginning ASP.NET 2.0 with C#	Chris Hart	Wrox	ASP.NET
0764575341	Professional C# 2005	Christian Nagel	Wrox	C#
0764578472	Beginning Visual C# 2005	Karli Watson	Wrox	C#
0764576100	Professional ASP.NET 2.0	Bill Evjen	Wrox	ASP.NET
0764575368	Professional VB 2005	Bill Evjen	Wrox	VB.NET
0764574019	Beginning Visual Basic 2005	Thearon Willis	Wrox	VB.NET
0735622019	Microsoft ASP.NET 2.0 Step By Step	George Shepherd	Microsoft	ASP.NET
0735621292	Microsoft Visual C# 2005 Step by Step	John Sharp	Microsoft	C#
0735621314	Microsoft Visual Basic 2005 Step by Step	Michael Halvorson	Microsoft	VB.NET

Now that we have some sample data, let us store this data in a database. As we have discussed earlier, we will use SQL Server 2005 Express Edition for database. Note that although the database will be stored in SQL Server 2005 Express, we can access and work with the database through the Visual C# IDE.

We will now create a database and add a table in it for storing the data-

• Create a new Windows Application Project. Open the `Database Explorer' tab to make it visible. As shown in the figure below, right click on the Data Connections and select Add Connection... option from the menu.

When we want to work with data stored in a database, we need to create the database as well as establish a connection with the database. This connection is the medium of communication with the data stored in the database.

• Clicking on the Add Connection... option will bring a dialog box:

In this dialog box, we need to fill some information about our database. The first thing is the Data Source. Since we are using SQL Server 2005 Express, we will leave it as default - Microsoft SQL Server (SqlClient). However, we have the option to change the data source and select Oracle, Access or any other data source.

Next is the Database file name (new or existing). Since we are creating a database for the first time, we will enter its name in the text box below (`Books' in our example). However, we can also open an existing database by clicking on the browse button and then selecting the required database.

Log on to the server part asks us how we connect to the SQL Server. Use Windows Authentication option is used to connect using windows username/password. We will select this option. Use SQL Server Authentication option is used when we need a separate username/password for connection to SQL Server. These options are generally set at the time of SQL Server Setup and Installation. Click OK to continue.

• As soon as we click on the OK button, we will get this message:

Since the database name provided by us (Books) does not exist yet, The IDE is asking us if we want to create it. Click on the Yes button.

• That is it! We have created a database named Books. We can see it in the Database Explorer. But we cannot store our data yet. First, we will need to create a table in our database.

In the Database Explorer, expand our database by clicking on the + symbol. This will display a list of folders. Find the folder named `Tables'. Right click on it. From the menu, select `Add New Table' option.

• A new tab will open up in the IDE. This is the design view of the table:

As we can see from the figure above, we need to specify Column Name, Data Type and Allow Nulls information here.

Column Name refers to the name of the columns in our table such as Title, Author, Publisher, etc. Data Type will specify the type of data being stored in these columns. Allow Nulls is used to specify whether we want to allow empty (null) values in the columns.

Let us design our table using the following guideline:

Column Name	Data Type	Allow Null
BookISBN	nchar(13)	Yes (check)
BookTitle	varchar(120)	No (uncheck)
BookAuthor	varchar(60)	Yes (check)
BookPublisher	varchar(50)	Yes (check)
BookCatagory	varchar(20)	Yes (check)
BookSerialNo	int	No (uncheck)

Let us talk about the last column - BookSerialNo. We have created this column to be used as the primary key for the table. It does not have any other purpose. We could have used the BookISBN column as the primary key as no two ISBNs are the same but there are two reasons for not doing so. First, the BookISBN column is of character type. Primary keys work best for integer types (although not a strict requirement). The second and more important reason is that many useful texts such as whitepapers, thesis and other such documents do not have an ISBN number. We may want to include such items in our list and that is why we have allowed null values in the BookISBN column. Since the primary key column never allows null values, we cannot use the BookISBN column as the primary key.

We will now make the BookSerialNo column as the primary key. To do this, click on the column. In the lower pane, the properties of the column are displayed.

Look for the property `Identity Specification'. Expand it and set the (IsIdentity) property to Yes. Leave the other properties as they are. This will make the values in the column start with 1 and increase with 1.

Next, right click on the column name in the upper pane and from the menu, select the Set Primary Key option. This will make the BookSerialNo column the primary key of our table.

The complete table design should look like this:

• Now that the table design is complete, we will save the table. Click on the save button in the toolbar. A dialog box will appear:

Enter a name for the table and click on the OK button.

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 actual data in it. In the following 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.