An example of creating/connecting a local Microsoft SQL Server database located in the “*.mdf” file
- Related topics
Search other websites:
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.
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
Figure 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).
Figure 2. The “Connect to Database …” command
Figure 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).
Figure 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”.
Figure 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:
Figure 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.
Figure 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.
Figure 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.
- 001 – An Example of creating the local Microsoft SQL Server (SQLEXPRESS) database in Microsoft Visual Studio
- 002 – An example of creating a table in the local Microsoft SQL Server database located in the “*.mdf” file
- 003 – Creating an autoincrement field (counter) in the MS SQL Server database table, which is located in the “*.mdf” file
- 004 – Creating a “one-to-many” relationship between tables in Microsoft SQL Server database
- 005 – An example of working with “*.mdf” file of local database Microsoft SQL Server in MS Visual Studio
- 006 – The “Connection String” string of connection with database. An example of using in applications