An example of creating / connecting a local Microsoft SQL Server database located in the “*.mdf” file

An example of creating/connecting a local Microsoft SQL Server database located in the “*.mdf” file


Contents


Search other websites:




Task

Using the tools of Microsoft Visual Studio create a local database, which is located in the “*.mdf” file. The name of the database is “MyDataBase.mdf”. The database must be used in the Microsoft SQL Server relational database management system.


Instructions

1. Run MS Visual Studio. ‘Server Explorer’ utility

The Microsoft Visual Studio system has powerful built-in tools for work databases. These tools include the relational database management system Microsoft SQL Server.

To work with databases in the Microsoft Visual Studio system, use the Server Explorer utility (Figure 1).

To access the Server Explorer window, use the command

View -> Server Explorer

Visual Studio 'Server Explorer' windowFigure 1. The ‘Server Explorer’ window

2. Database creation
2.1. The “Connect to Database” command

There are 2 ways to create a new local database, which is located in the “*.mdf” file:

  • сall the command “Connect to Database” from the Tools menu (Figure 2);
  • сlicking the “Connect to Database” button in the Server Explorer menu (Figure 3).

Visual Studio "Connect to Database ..." commandFigure 2. The “Connect to Database …” command

Visual Studio command Server ExplorerFigure 3. The “Connect to Database” command from ‘Server Explorer’ tool

2.2. The window “Add Connection”

After the “Connect to Database” command is opened, the “Add Connection” window will open (Figure 4).

SQL Server "Add Connection" windowFigure 4. The “Add Connection” window

In the “Add Connection” window, you can perform the following operations:

  • select a data source (Data source);
  • create a new one or select an existing database file (Database file name);
  • set the password to enter the database (optional);
  • check database connection (Test Connection);
  • set other parameters.


Change the data source by clicking on the “Change …” button. This will open the “Change Data Source” window (Figure 5), in which you need to set the Data source and the Data provider. The following data sources are proposed:

  • the Microsoft Access database, which is located in the file “*.mdb”;
  • a database that supports access using the ODBC driver;
  • a database of the Microsoft SQL Server type, including the local SQLEXPRESS server;
  • database “Microsoft SQL Server Compact 3.5”, located in files with the extension “*.sdf”;
  • Database “Microsoft SQL Server Database File”, located in files with the extension “*.mdf”;
  • Oracle Database.

In our case (according to the condition of the task), select “Microsoft SQL Server Database File”.

SQL Server data source fileFigure 5. The window for changing the data source. Selecting the Microsoft SQL Server Database File

In the “Database file name (new of existing)” field, enter the name of the new database that can be created. In our case, the name “MyDataBase.mdf” is entered (Figure 6). Using the “Browse …” button, you can specify the folder in which the database file “MyDataBase.mdf” will be placed.

Figure 6 shows the name of the database being created, which is located on the following path:

D:\Programs\C_SHARP\TermPaper1\MyDataBase.mdf

SQL Server database nameFigure 6. Specifying the name of the created database “MyDataBase.mdf”

After clicking on OK, the system displays a window (Figure 7), in which you need to confirm the creation of a new database.

SQL Server creation databaseFigure 7. Confirming the creation of a new database

After the performed actions, a new database with the name “MyDataBase.mdf” will be created. So far, the database does not contain any tables, views, diagrams, etc. (Figure 8). In the database, the programmer can create custom tables, diagrams, views, stored procedures, functions, types, and assemblies. Now the database “MyDataBase.mdf” can be used in own projects.

Visual Studio database Server Explorer windowFigure 8. Displaying the database “MyDataBase.mdf” in the “Server Explorer” window

3. Adding an existing database to the ‘Server Explorer’ database list

The ‘Add Connection …’ window (Figure 4) also allows you to add a previously created database to the list of databases that are displayed in the ‘Server Explorer’ window. In this case, use the “Browse …” button (Figure 4) to select the previously created “*.mdf” database file. As a result, the selected file will be connected to the list of databases that are displayed in Server Explorer. After that, this file can be used in projects on MS Visual Studio.


Related topics