Implement a Relational Database With ASP.NET Core

In this chapter, I’ll walk you through the process of building a second database in SSMS, just like the first, and populate it with data. After that, I’ll turn you loose to finish getting your database ready for querying.

Create a New Database

To create a new database in SSMS, right-click on the Databases folder in Object Explorer and choose New Database. This brings up the New Database window. Give your new database a name, then click OK. I’m calling mine MyRecipes2.

You should now see a new database in Object Explorer. It doesn’t have any tables yet, but it’s ready for them.

Add Tables

There are a few ways to add tables to your new database. You could write SQL scripts to do the work for you. You could also right-click on the Tables folder and select the Table... item. This brings up the Table Designer window where you can add all the necessary columns for your table. 

You can use this method, but I’m also going to show you a third option, and that’s building the tables directly in the database diagram.

To do this, you’ll need to create an empty diagram first. Right-click on Database Diagrams, select New Database Diagram and install the diagram support (if necessary). The Add Table window will display, but there are no tables to add, so simply click Close.

You now have an empty database diagram. To add a new table, right-click the white background of the empty diagram and choose New Table. In the Choose Name dialog, give your new table a name.

Exercise: Complete the Tables

You now have a version of the Table Designer displaying over your diagram. Go ahead and add all the columns to this table so that it matches your model design from Part 1. (It should match the corresponding table in the database you generated using code-first migrations.) 

 When you have completed the Recipes table, repeat the process for the rest of the tables in your model design. When you’re finished with that, come back here and I’ll show you how to add the relationships.

Add Relationships

At this point, I have all of my tables created and added to the diagram. However, I haven't set up any of the relationships yet, so they are unrelated.

 The next thing I need to do is begin defining how these tables relate to one another. Fortunately, I know how, because I designed the model I used to build these tables, and I have foreign key columns that are easily identifiable by name. For example, I have two foreign keys in the Recipes table: FoodTypeId and FoodEthnicityId. So, let’s connect them to their primary key counterparts in the appropriate tables.

Adding a relationship is easy within the diagram. Click the Primary key in the FoodEthnicities table and drag it to the corresponding Foreign key in the Recipes table. A dotted line follows your cursor as you move between the tables, and when you release the mouse button, the relationship definition windows appear. 

 If the foreign key definition looks correct, click OK. The first window will then disappear, leaving the following: 

If everything looks good, click OK. You now have a relationship defined between these two tables in the diagram. 

You can now repeat the process for the other relationships in the database.

Exercise: Complete the Relationships

You now know how to add relationships between tables using database diagrams in SSMS. Add the remaining relationships, then save your diagram.

Exercise: Populate Your Database

To see if you’ve built your database correctly, you’ll need to import some data. Remember those .csv files you downloaded from the GitHub repository for this course back in Part 1, Chapter 4? Well, you can use them again here, with one small modification. You’ll need to open each file and remove the first row - the one containing the column names. After you have done that, open a new SQL query window in SSMS, and make sure your new database is showing in the dropdown on the left side of the toolbar.

For each of the .csv files and corresponding tables in your database, perform a bulk insert to import the CSV data. Import the three lookup tables first (FoodTypes, FoodEthnicities, and UnitsOfMeasure), followed by Recipes and Ingredients, and RecipeIngredients last. 

BULK INSERT FoodTypes
FROM '[your local path here]\FoodTypes.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

When you’ve completed this task, you’ll be ready to learn how to use database-first integration to import your database as a data model into your MVC application.

Let's Recap!

You’ve got a brand new database! It’s pretty much a duplicate of what you created in Part 1, but you built the entire thing using SSMS. Here’s what you’ve learned in this chapter:

• In addition to using SQL or C# with code-first migrations, you added another way to create relational databases to your knowledge bank: SSMS. 

• You learned how to design and build the tables, and how to assign relationships using the database diagram tool.

• Finally, you learned how to populate your database with data from .csv files using SQL bulk inserts within the SSMS IDE.

In the next chapter, we’re going to take this new database you’ve built and, using a new MVC project, construct an application data model and database context directly from the database itself, without writing a single line of code.