Databases. An example of creating a view in a database of MS SQL Server type using MS Visual Studio. Creating a calculated field

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.


Contents


Search other websites:




Task

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:

Field name Table
Num_Book Student
Name Student
Mathematics Session
Informatics Session
Philosophy Session
Average Вычисляемое поле

 

Instruction

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.

Visual Studio connecting 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:

After connection, the window of the Server Explorer utility will look like shown in Figure 2.

Visual Studio Server Explorer database

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).

Visual Studio command "Add New View..."

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).

SQL Server table query

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.

Visual Studio window view

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.

SQL Server view fields

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).

Visual Studio calculated field SQL Server

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.

Server database SQL query figure

Figure 8. Calling the execution an SQL query

 






6. Saving the View

After calling the command

File->Save All

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.

Visual Studio window view

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.

SQL Server database Server Explorer view

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).

SQL Server database "Show Results" command

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.

Visual Studio displaying view

Figure 12. Result of displaying the view

 


Related topics