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.

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.

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

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.

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

BestProg