Lesson 3 - Create the database
Before we look at the program for this course, we need to create and populate the database.
1 - Download SQL scripts
Start by downloading the attached zip file (DatabaseScripts.zip) that contains four SQL script files to create the database and populate it with sample data.
2 - Start SQL Server Management Studio
Start SQL Server Management Studio.
For "Server name", enter "(local)" - without the quotes.
For "Authentication", select "Windows Authentication". This will connect to SQL Server with your current Windows User Account, which should have all rights to the database server.
Click the "Connect" button.
3 - Open a query window
On the left, you should see the "Object Explorer" panel. This lets you work with your SQL Server instance - create databases, view data, run SQL commands, etc.
Right-click on the "(local) SQL Server" and select "New Query". This will open a tab in the main workspace of SSMS where you can run SQL statements.
4 - Run the SQL script to create the database
Paste in the text from "01 - CreateDatabase.sql". These are the DDL statements to create the empty database. See the Notes section below, for more information about DDL and DML statements.
Either press F5, or click the green "Execute" arrow, to run the SQL statements and create the AccountManager database.
After a second or two, you should see a message in SSMS telling you "Commands completed successfully".
If you don't see this, leave a comment.
If the command was successful, you should be able to expand the "Database" section of the Object Explorer and see your AccountManager database.
If you don't see the database, right-click on "Databases" in Object Explorer, select "Refresh" from the menu. If you still don't see the database, leave a comment for help.
5 – Run the SQL script to configure the database
Run the SQL script "02 – ConfigureDatabase.sql".
If you’re not using Microsoft SQL Server, this script might not work. Different SQL engines might have different configuration options.
6 – Run the SQL script to create the tables
When we ran the script to create the database, we ran it within the context of the SQL Server's "master" database. Now that we have our AccountManager database, we want to run all our other scripts for this project while in the context of our AccountManager database.
A single SQL Server instance can hold many different databases. It's common to forget to set the database to the one you want to work with. If you see an error while running a SQL statement from these lessons, check that the AccountManager database is selected.
You can check which database your current query is running in by looking at the dropdown above the Object Explorer.
Right-click on the AccountManager database and select "New Query".
Open the SQL script "03 - CreateTablesAndIndexes.sql" and paste it into the query window.
Run the script (press F5 or click the green "Execute" arrow) to add the tables and indexes to the database.
7 - Run the SQL script to populate the tables with sample data
Open the file "04 - PopulateAccountManagerDatabase.sql" in your query window and execute it.
You should see a message below the query window that shows several "1 row affected" lines and a "Completion time". If you don't see this, leave a comment. We need this script to succeed, to populate the lessons' sample data into the database.
8 - Verify the scripts ran successfully
In Object Explorer, right-click on the AccountManager database and create a new query window.
Run these three SQL statements (copy them into the query window and execute them) and confirm you see the results shown below. If they look like that, your database is ready for the lessons. If you don't see that, please leave a comment so I can help you.
SELECT * FROM [Account]
SELECT * FROM [AppUser]
SELECT * FROM [Transaction]
Results
About the database
Here's some information about the database objects, especially the things we'll use in the program's database access functions.
Creating the tables
We created the tables using scripts. You can build tables, stored procedures, etc. using the SSMS UI.
We won't go deep into how to do that in these lessons. Writing scripts is generally a better choice, because you can reliably re-run them to recreate the database - instead of depending on someone manually typing in all the tables/columns/stored procedures/etc. But you would start by right-clicking on the database's "Tables" folder (in Object Explorer) and selecting "New" -> "Table…". That will take you to a screen where you can enter the information about the table you want to build.
Expand the "AccountManager" -> "Tables" folder. You'll see the three tables we have in this database: Account, AppUser, and Transaction. These are where we store the data for our program.
Schemas
Notice that all the table names are prefixed with "dbo." That means they are objects in the "dbo" schema - the default schema for MS SQL Server.
A schema is like a namespace in C#. In a namespace, functions exist in different namespaces. To use the functions, you need to include "using" directives at the top of your class for the different namespaces.
It's a similar thing with database schemas. If we create a schema named "abc", we could create an "Account" table within that schema. Then "Tables" would show that we have both "dbo.Account" and "abc.Account".
However, it's generally not a good idea to have the same table name in different schemas within a database. Future developers may not notice the tables are in different schemas.
There are some specific reasons to repeat table names under different schemas. A common one is if you want to have a multi-tenant database for a website. That will keep each user's data in separate tables, to help prevent accidentally exposing one user's data to another user.
Tables
In case you aren't familiar with SQL tables, and what they do, think of them as a way to persist your program's business objects - even if the program isn't running.
It's common to have your database table and column names match your application's business objects and property names. That's how ORMs (Object-Relational Mapping libraries) work by default.
Notice that the table names are singular: "Account", instead of "Accounts". This is the most-common way to name tables. However, Entity Framework "code first" will create the tables with pluralized names.
SQL Server and C# don't care how you name your tables - either way will work. But, like most things in programming, you want to be consistent and it's easier for other developers to understand if you follow common practices.
Here are the tables we're using in the AccountManager sample program.
Reserved SQL words
One of the tables is named "Transaction", for credits and debits of our accounts.
"Transaction" is a reserved word in SQL. We'll cover what it does in a later lesson. So, we shouldn't be able to name a table "Transaction". That would be like naming a C# variable "if".
It would be better to give this table a name that isn't a reserved SQL word, but I left it as a reserved word so I can show you how to get around this when we write our C# database code.
Columns
This is just a quick summary on columns, especially the features we'll use in the sample program.
Tables columns usually relate to object properties. Just like class properties, they have datatypes determining what type of data you can store in them. You also can set the size/length of the column and if it can, or cannot, be null.
Like tables, column names can be reserved SQL words. We'd handle this the same way we handle reserved SQL words for tables.
Keys/Indexes
To help you find data in a table faster, you can create indexes. These are updated whenever a row is added or updated in a table.
Most tables will have a "primary key", which is the unique value that identifies one specific record. This is often an "Id" column. With some tables, you need to combine more than one column to have a unique key for the row. You'd do this with a "composite key" - a key that combines two or more columns.
You can also create indexes that sort the data in ways your program will typically use it.
For example, an order entry system would probably have a primary key for OrderId. You might also create indexes on the CustomerId and OrderDate since your program will probably want to retrieve data for a specific customer or were created on a specific date. The indexes will let SQL Server find the required subset of rows faster.
You can also create foreign keys that define how parent-child tables relate to each other.
In our sample AccountManager program, we'll have a foreign key that relates each account record (parent) with its transactions (children).
Special column attributes
We can also set special attributes for the columns.
For this program, we have a default value for the Account table's Id column. When we add a new row to this table, SQL Server will create a new "uniqueidentifier" (SQL Server's name for a GUID) value for the row.
You can also have the database create a sequential integer value or a sequential GUID value for the default value.
There are positives and negatives to which datatype you use for ID columns in SQL tables. In the AccountManager, I show both integers and GUIDs, so you can see how they both work. In your programs, select one and use it for all the tables. It's confusing when a database isn't consistent.
Stored Procedures (SPROCs)
In Object Explorer, expand "Programmability" -> "Stored Procedures" to see the one stored procedure we created: "InsertTransaction"
A stored procedure is the SQL database equivalent to a function in C#. It's a function containing SQL statements. You call the stored procedure and can pass in parameters (or not) and get back results (or not).
For the InsertTransaction stored procedure, we pass in parameters, run some SQL statements, and return a value to our C# code that called the stored procedure.
Lesson Notes
DDL is the abbreviation for "Data Definition Language". These are the SQL statements you use to create or modify the schema (structure) of a SQL database. These statements let you create a database, create a table to hold data, create indexes, etc. These statements are used to "define" the structure of the database.
DML "Data Manipulation Language" are the SQL statements that let you add, edit, delete, and view data in the tables, run stored procedures, views, etc. These statements "manipulate" the data in the database.
Links to additional resources
0 comments