Your First Database

In this tutorial we will create a basic database that in the future you can pull and store data in using SQL statements, or built in tools. You will require minimal knowledge of Visual Studio.

Creating the Database

  1. Create a Web Form in Microsoft Visual Studio, use the default name.
  2. In the Solution Explorer there is a folder called App_Data by default. This is where you want to put all of your database information as this helps Microsoft Visual Studio connect and read where your database is at. Right Click on this folder and click Add New Item.
  3. Create a Web Form in Microsoft Visual Studio, use the default name.
  4. In this dialog box we will see a few selections we are going to create a SQL Server Database. Name it FriendsDB and click Add.
  5. On the right side of the screen in you should have your Solution Explorer open by default, at the bottom of that window you will see another tab, Database Explorer. Click this tab.
  6. Once here drill down into the FriendsDB, you will see several folders. The folder we want to focus on at the moment is the Tables folder.
  7. Right click on the Tables folder and select Add New Table. This will open up in your main window with three empty columns. Column Name, Data Type, and Allow Nulls.

Let’s stop for a minute and talk about what these columns mean. The Column Name is where you give each individual row a name, this name is how you will insert into your table, call the data, and delete it. So this is a very important part of the table. Name it something relevant to the data it will hold and do not have any spaces in the name.

Data Type is also very important. This is where you define what kind of data this row can hold. In our example we will be using the int and nvarchar data types. The int data type is short for. It allows any number value to be inserted into this row; if you tried to insert a string of some form, it would not accept it. The nvarchar data type is by far the most flexible of any data type; it can take and hold any data you want into a string. The nvarchar tag is accompanied by a value which dictates how many characters are allotted within it. Nvarchar(MAX) is the most popular.

The Allow Nulls checkbox is fairly self-explanatory. If it is checked, that row will allow null values(empty fields) into it, if not it won’t.

Now that we have that all hammered out let’s build our first table! Start off by entering ID under column name, give it a Data Type of int. and uncheck Allow Nulls.

Every table should have an ID really there are few exceptions to this rule of thumb. It is the easiest and most efficient way to keep track of your data. The reason is because you set the Identity value in the bottom window to start at 1 and increment by 1 for every new row in your database. So it’s an ongoing number that expands with your database.

To make this a true ID we need to tell our table this and set it up to increment. Right click on the box to the left of the column name and select Primary Key. Then we need to move down to the column properties window to where it says Identity Specification, double click the (Is Identity) property and it will, by default enable your identity and it’s increment.

Now we will create the two additional rows that will hold our data that we want to store and access. In the same manner we created the identity create two new fields one named Friend with a data type of nvarchar(MAX) and one named Url which will also have an nvarchar(MAX) data type. Your table should look like this when finished. Click save and save it as Friends.

Filling Your Table

So now we have a table, let’s fill it with a little bit of data, in the Database Explorer right click on the actual table named Friends. Click on Show Table Data.

This view is very much like the create table except now you are inserting data manually into your database. Fill in the Friends with a few familiar names and give them each a URL. Do not fill in the ID field; it automatically enters itself once you are done editing each row.

Success!

That’s it! You now officially have your first database with data in it. This is the start of being able to build powerful dynamic websites. If you want to display your database in an easy way using built in tools the GridView tool is an excellent way to do so. If you have any questions regarding the subject please leave a comment. Have a great day!