An example of creating a view in a database of MS SQL Server type using MS Visual Studio. Creating a calculated field
This topic describes the step-by-step detailed process of creating a view using the example of the Education.mdf database, which is located in a local file. The database is designed to work under the control of the Microsoft SQL Server DBMS.
Using this example, you can learn how to create your own data views that are obtained from tables. Based on the views, you can create reports to display the results of the program in a convenient (paper) form.
- 1. Run Microsoft Visual Studio
- 2. Connect the Education.mdf database to the databases list of the Server Explorer utility
- 3. Adding a new view. The command “Add New View”
- 4. Adding the calculated field “Average”
- 5. Running the SQL query to display the view
- 6. Saving the View
- 7. Displaying a view in the Server Explorer window
- 8. Viewing the output of the view. The “Show Results” command
- Related topics
Let the database, which is located in the file Education.mdf, be given. The database contains two linked Student and Session tables.
The tables are linked by the ID_Book field.
Using the tools of Microsoft Visual Studio, you need create a View with the name View1, which will have the following structure:
1. Run Microsoft Visual Studio
2. Connect the Education.mdf database to the databases list of the Server Explorer utility
In order not to waste time developing and linking the tables of the Education.mdf database, you can download the finished database here. After loading and saving in a certain folder, the database must be connected to the databases list of the Server Explorer utility.
Database connection is realized in one of several standard ways:
- by selecting the command “Connect to Database …” from the Tools menu;
- by selecting the “Connect to Database …” button (command) from the Server Explorer utility.
As a result, the wizard window opens, in which you need to configure the database.
Figure 1. Ways of adding/connecting a database
The database configuration process is carried out in sequence with refinements. Windows are opened in which the appropriate settings are set.
For a detailed description of how to connect a Microsoft SQL Server-type database to Microsoft Visual Studio, see the topic:
- An example of creating/connecting a local database of Microsoft SQL Server, which is located in the * .mdf file
After connection, the window of the Server Explorer utility will look like shown in Figure 2.
Figure 2. Server Explorer with the connected Education.mdf database
3. Adding a new view. The command “Add New View”
The Microsoft Visual Studio system allows you to create a data view (views). The views themselves only display the data of the tables in an easy-to-read format. They are not directly data of tables. In our case, we need to create a view in accordance with the task.
The view is created using the “Add New View” command, which is called from the context menu (Figure 3).
Figure 3. Command “Add New View…”
As a result, the “Add Table” window opens, in which you need to select the tables whose data will be used in the view (Figure 4).
Figure 4. Selecting the tables that will be used in the query
For our case, we need to select both tables.
As a result, the Microsoft Visual Studio window will appear as shown in Figure 5.
Figure 5. Microsoft Visual Studio window after creating a view
In the tables, you need to select the fields that will be used in the view. The order of the selection of fields can correspond to their display in the query in accordance with the condition of the task. This means that the fields of the Student table (NumBook, Name) are first selected, and then the fields of the Session table (Mathematics, Informatics, Philosophy) are selected.
For our case, the selection of fields is shown in Figure 6.
Figure 6. Selecting fields for the view
As you can see from Figure 6, at the bottom of the window you can see a view in the SQL language generated by the system
SELECT dbo.Student.Num_Book, dbo.Student.Name, dbo.Session.Mathematics, dbo.Session.Informatics, dbo.Session.Philosophy FROM dbo.Session INNER JOIN dbo.Student ON dbo.Session.ID_Book = dbo.Student.ID_Book
4. Adding the calculated field “Average”
To create a calculated Average field, you need to change the text of the SQL query for the view at the bottom (see Figure 6, bottom). For example:
SELECT dbo.Student.Num_Book, dbo.Student.Name, dbo.Session.Mathematics, dbo.Session.Informatics, dbo.Session.Philosophy, (dbo.Session.Mathematics + dbo.Session.Informatics + dbo.Session.Philosophy)/3.0 AS Average FROM dbo.Session INNER JOIN dbo.Student ON dbo.Session.ID_Book = dbo.Student.ID_Book
The calculated “Average” field is added (Figure 7).
Figure 7. Adding the calculated “Average” field
5. Running the SQL query to display the view
At the bottom of the Figure 7, is shown the result of executing the SQL query for the view. The query is executed by calling the “Execute SQL” command from the “Query Designer” menu or by pressing the ‘!’ Button, as shown in Figure 8.
Figure 8. Calling the execution an SQL query
6. Saving the View
After calling the command
the “Choose Name” window (Figure 9) opens, in which you want to specify the name of the newly created view. In our case, you can leave the name (View1), which is offered by the system by default.
Figure 9. The window of selecting name for the view
7. Displaying a view in the Server Explorer window
After saving the view, the window of the Server Explorer utility will look like shown in Figure 10. As you can see from the figure, the View1 view is displayed in the Server Explorer window.
Figure 10. The Server Explorer window with View1 view
8. Viewing the output of the view. The “Show Results” command
Using the “Show Results” command from the context menu, you can see the result of the View1 view (Figure 11).
Figure 11. Calling the “Show Results” command
After the “Show Results” command is called, a window will appear in which the result of the SQL-query for the View1 view will be displayed.
Figure 12. Result of displaying the view
- An example of creating a query in the database
- An example of creating/connecting local Microsoft SQL Server database located in the “*.mdf” file
- An example of creating the “*.mdf” file of local database Microsoft SQL Server in MS Visual Studio