This tutorial will show you how to create new database tables using Visual Studio. We build on the ASP template database (see previous tutorial), but you could equally start from a blank database.
We will guide you through the creation of the initial tables to an asset management system, but you can implement your own data model if you prefer.
If you have just followed either of the previous tutorials you will have created a new data connection and this will be available in the server explorer.
If you created a blank database then you can expand the 'Tables' folder in Server Explorer but there will be none.
If you created a users database then expanding the 'Tables' folder will provide the items shown below ('AspNetUsers' has been expanded for illustration purposes).
Note the data connection (database in server explorer) has a database icon next to it, usually this has either a red cross or a green plug (see images below). The red cross simply indicates the connection is closed, expected when we are not directly manipulating it. The green plug therefore shows a connection is open and we can manipulate the structure or content directly from Visual Studio.
Expanding the data connection in Server Explorer attempts to open it and you should see the icon change from the one shown on the left to that on the right.
The images below show a Conceptual Data Model (top row of 4 entities) and a Physical Data Model (4 tables with attributes and data types) of the initial tables we will be creating.
Start by right-clicking on the 'Tables' folder in the Server Explorer, and select 'Add New Table'.
A tab should open up with a 'design' pane where you can add field names, select data types, choose whether they are required or optional (allow nulls) and whether you want a default value.
A T-SQL pane will also be available to the bottom that will contain the Data Definition Language (DDL) script for the creation of the table. You can choose to use this instead of the designer to create your table.
Whether you decide to use the designer or write the script yourself, create a table with fields to match those shown in the picture below.
IMPORTANT: We must name the table in the T-SQL pane before we run the script, find the first line and change the word 'Table' to your chosen name (in our example this is 'Site').
We do not want to save the file, instead we will click 'Update' to run the script on our database and create the table.
If the table already exists, it will be overwritten with the structure you have defined.
If you change the name and click 'Update', you will get an additional table with the same structure.
If there are no syntax or connection issues you should be presented with a preview with no errors listed.
Click 'Update Database' to confirm the creation of the table.
There is a pane called 'Data Tools Operations' that should show a green circle with a check indicating everything was successful. If there are errors, a red 'X' is shown instead with details of the issue.
Your table will not show up in the 'Tables' folder without a refresh. Right-click on the 'Tables' folder and select 'Refresh'.
You should see the new table appear in alphabetical order (below, right).
Perform the same steps again to create another table named 'LocationTypes':
Well done on creating the first two tables. Next we will build two more tables and look at auto-incrementing primary keys, and how to establish relationships using foreign keys.