How to Create a Database with SQL Server Express

 

To create your database, launch your C# .NET software. Start a new Windows project by clicking File > New Project. Call it anything you like, because we won't be using this form. But without SQL Server Management Studio Express installed, you need a new Windows Application to create a SQL Server Express database.

From the menu bars at the top of C# .NET, click Project. From the Project menu, select Add New Item:

When you click on Add New Item, you should see the following dialogue box appear (we've chopped a few templates out for convenience sake):

Select SQL Database, and give your database a name. Call it MyWorkers. We'll create a database of fictitious people who work for us, and give them job descriptions.

Click the Add button, and you'll see (eventually) the following:

Select Tables, and then Finish. (The Dataset is important, and you'll see how they work later).

It may seem as though nothing has happened. But take a look at the Solution Explorer on the right and you'll see that your database has been added to your project:

However, your database is empty at the moment. We need to add a table to it. So right click on MyWorkers.mdf. From the menu that appears, click Open.

You should see the Database Explorer appear:

Right click on Tables to see the following menu:

Select Add New Table from the menu, and a new table will appear in your main window:

The Columns are all the Columns that you want in your table. The idea is that you type a Name for the Column, and then specify what kind of data (Data Type) you want to go in it, such as text, numbers, Yes/No values, etc. Allow Nulls means, "Does this column need to be filled in?" For example, if you had a middle name column then this will often be left blank, because not everybody has a middle name. In which case that particular column can have Nulls. If you need the data, such as an Identifying Number, then you leave the Allow Nulls box unchecked.

We'll created a very simple table with just four columns:

Worker_ID
first_Name
last_Name
job_Title

The first Column, Worker_ID, will be a number. We can let the database itself handle this. Every time a new worker is added, SQL Server Express will then take care of assigning a new number for that worker. This is known as Auto Increment in other databases, such as Access.

So type Worker_ID under Column Name and your screen will look like this:

The next thing to do is tell SQL Server Express what kind of data is going in to the Worker_ID column. Click inside of Data Type and you'll see it's a dropdown list of options:

As you can see, there's an awful lot of them! Select int from the list, though. Leave the Allow Null box unchecked, and your screen will look like this:

One more thing to do with this Column. Have a look at the bottom of your screen and you'll see a list of Properties. The one we want is Identity Specification. Set Is Identity to Yes, and Identity Increment and Identity Seed will appear:

By setting these values, SQL Server Express will add 1 to the Worker_ID column when we add a new worker.

Click under Column Name and type the second of our table fields:

For the Data Type, select nvarchar(50). The varchar is short for variable-length character string. With nvarchar, the n is short for Unicode, and the data will be stored in the UTF-16 format. Use nvarchar if you're going to be storing non-English characters. Otherwise, use varchar. The same is true of all the n's on the list.

Check the Allow Nulls box and your new Column should look like this:

Set the following values for the other two Columns in our table:

Your table should then look like this:

At this stage, you can set a Primary Key for the table. A Primary Key is one that uniquely identifies a particular row in your table. It has to be unique, with no duplicates allowed. You couldn't set the first name column as the Primary Key, for example, because lots of people will have that name. The Worker_ID column is the one column in our table that is unique, so we could use that as the Primary Key. If we had a second table, we could then use Primary Keys and Foreign Keys to link the two tables together. SQL Server is a relational database, and Primary Keys are used a lot for linking purposes.

To set a Primary Key, right click on Worker_ID. From the menu that appears select Primary Key. We're going to leave the table without a Primary Key, however, as we want to keep things simple. If you want to become a database guru, though, you need to get to grips with the relational aspect of SQL Server and SQL Server Express.

But let's continue.

Click File > Save All to save your work. You will be prompted to enter a name for your new table. Call it tblWorkers:

Click OK and you'll be returned to the main screen and the Database Explorer. Expand the Tables section and you should see your new columns appear:

The only thing left to do is to enter some default data into the table.

To add some data to your Table, right click on your Table name. You should see the following menu appear:

Select Show Table Data and you'll see a new tab appear:

All our Column names are there, waiting to be filled in. To enter data, simply click inside a cell and start typing.

Click inside the first_Name column. (The Worker_ID Column will take care of itself.) Type a first name. Click inside of last_Name and type a last name. Click inside of job_Title and enter a job title. Enter the same details as ours, if you prefer (all made up):

Notice the warning symbols in the cells. These appear when the cell data has changed. The Worker_ID is still NULL in the image above. When we click in the next row, however, notice that a number will appear in the first Worker_ID cell:

We have now created one row in our database table. Fill out a few more rows. You can use the same details as in the image below:

Save your work, and you will have created your very first SQL Server Express database! But it's a huge subject, and whole books have been written about SQL Server. We can only touch on the very basics here. What we do have, though, is a database we can open with C# programming code.

<-- SQL Server Express | Connecting to a Database with C# .NET -->

<--Back to the C# .NET Contents Page

View all our Home Study Computer Courses