Option 1: Blank Database

If you are deciding to create a new SQL Server database (Microsoft database) then Visual Studio gives you the capability to create and manage one without other packages like SQL Server Management Studio (SSMS). It must be said you don't get the same scope, accessing data is slower, and SSMS has the really useful database management tools to help with imports and exports, etc. Nevertheless, for a small-scale project it can be very beneficial to work entirely in Visual Studio.

Your project may require you to start with a blank database, but if you wish to have a login system we recommend starting with the template users database and adapting it to suit your needs. 

  • To build your system around a template user database with user authentication, skip this tutorial and move on to Option 2.
  • If you wish to start with a completely blank database, then you are in the right place. 

We will start by setting up your work space which may well look like this if you have opened Visual Studio 2017 for the first time.

We want to have access to the Server Explorer pane (not to be confused with the Solution Explorer). If its not readily accessible, you can go to 'View' on the menu bar and select 'Server Explorer' (quick keys, 'Ctrl + W', release and press 'L').

 

The typical / default location of the Server Explorer is on the left with the Toolbox.

To create a new database, locate the 'Data Connections' item in the Server Explorer, right-click, and select 'Add Connection'. Counter-intuitively we are not using the 'Create New SQL Server Database...' option.

We will use the 'Microsoft SQL Server Database File' which will have an '.mdf' extension, typical for SQL Server databases. An '.ldf' file will also be created to store log data (both files should be kept together).

If the 'Always use this selection' box remains checked then future runs through these steps will skip this part and assume a SQL Server connection. Not to worry, you can change it easily from the next dialogue box if required.

On the next screen we have the selected database type (you can go back to the last screen using the 'Change' button) and can name the new database appropriately.

If the database already exists, you can browse to find it.

We are creating a new database so simply type your preferred filename in the text box. Our example names this 'AssetManagementSystem'.

Next we have a choice of authentication. Windows appears to have more options for managing users, but works best when the database is being used from the same machine. We are going to choose 'Use SQL Server Authentication' as we may mount our database on a separate machine to the client and access it remotely.

Select a username and password (you may like to make a note of these for later use). 

You cannot test connection yet, as the database does not exist. So click 'OK'.

Assuming the database does not already exist, you will be prompted to create it. Note the directory it will save in as you do not get the option to change it. By default the database saves to your 'Documents' folder. 

Note: You can move the database later, but you must move both the .mdf and .ldf files together, and you should update the data connection in Server Explorer by using the previous 'Browse' button where you previously typed a new database name.

If everything works as expected you should see your database listed under 'Data Connections'. A small green plug (shown below) or a red 'X' will be displayed in the database icon to indicate whether the connection is open or closed.

Well done, we have a new blank database. You can skip the next tutorial (option 2) and move on to creating tables.