Annotation: After reading this lecture, you will be able to: create queries using the Query Builder SQL Server Management Studio, extract database information from database system tables, dynamically create simple queries based on user input, format user input and filter complex dynamic queries, parse and reformat data for use in a filter, protect the database from "type attacks" SQL-injection", use the sp_executeSql procedure to pass the query

In the previous lecture, we talked about how to increase performance requests. You now know how to create an efficient query set to provide users with the most useful information from your application using pre-built queries in stored procedures or views.

However, in all but the simplest applications, it is impossible to know in advance all the possible types of information that users might want and how they might want to filter and organize it. Instead of trying to provide for all such possibilities, you can give the user control over the information that the application reports. This lecture explains how to dynamically build queries based on choices the user makes during the workflow.

User interface for building queries

SQL Server Management Studio includes a sophisticated interface for building queries. Let's explore this interface so you can get an idea of ​​how queries can be created dynamically. Your application won't need everything controls provided by SQL Server Management Studio. In fact, you need to think carefully about how best to restrict users' choices.

Creating a Query Using SQL Server Management Studio Query Builder

Retrieving information about database tables

To provide the user with a list of options, the application may need to extract information about the database tables. There are several ways to get this information. The most important of these methods is the use of the INFORMATION_SCHEMA schema. This schema is standard in any database.

Using INFORMATION_SCHEMA

The INFORMATION_SCHEMA schema is a special schema found in every database. It contains definitions of some database objects.

INFORMATION_SCHEMA conforms to the ANSI standard, which is designed to retrieve information from any ANSI-compliant database engine. In SQL Server, INFORMATION_SCHEMA consists of a set of views that query the sys* database tables containing information about the structure of the database. These tables can be directly queried, just like any other database table. However, in most cases, in order to retrieve information from *sys tables, it is better to use the INFORMATION_SCHEMA schema views.

Note. The INFORMATION_SCHEMA schema sometimes queries tables that are not needed, which is detrimental to performance. In the next example of this chapter, this is not particularly important because the application was already waiting for user input. However, this should be considered if speed is an important consideration for your application.

Here is the basic T-SQL code that is used to get information about the columns included in the table:

Note that you must select the TABLE_SCHEMA field to get the schema for the table. This may be of value for making similar queries in the future. To experiment with the methods described in this chapter, create a new project in Visual Studio.

Create a new Visual Studio project

1. Select from the Start menu commands All Programs, Microsoft Visual Studio 2005, Microsoft Visual Studio 2005.
2. From the Visual Studio menu, select the File, New, Project commands (File, Create, Project).
3. In the Project Types panel, expand the Visual Basic node (Visual Basic Solutions) and select the Application template in the Templates panel. Name the project Chapter7 and click OK.
4. The application for this example can be found in the example files in the \Chapter7\DynQuery folder. You can cut and paste the code for the following procedures from the Form1.vb file.

Getting a List of Tables and Views

Typically, you will need to give the user the option to select not only the columns, but also the table for dynamic query, so you have to display a list of tables to the user. The required information can be obtained using the following query:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES

In an application, this query can be used as follows.

Getting a list of tables

The above Visual Basic code initializes a SqlCommand object named com with the SQL string to be executed, and then executes the SqlCommand object. This is the easiest way to execute a T-SQL statement from within an application.

As an exercise, you can place the schemas and tables obtained during the Load procedure of our form in user interface applications in the form of a form so that the user can select a scheme and a table to work with. For the example in this chapter, we assume that the user has selected the Sales schema and the Customer table.

Once the user has selected a table, the list of columns for that table can be retrieved using the same method, using the user's input as the table name in the query. To do this, enter a proxy in the query string, and then replace that proxy with a call to String.Format . In the code below, the placeholder in the query string is (0).

Getting a list of columns

1. Add the following RetrieveColumns procedure to the code below the RetrieveTables procedure:

   Sub RetrieveColumns(ByVal TableName As String) MyConnection As New SqlClient.SqlConnection(_ "Data Source=.\SQLExpress;" & _ "Initial Catalog=AdventureWorks;Trusted_Connection=Yes;") Dim sqlStr As String sqlStr = "SELECT TABLE_SCHEMA, TABLE_NAME , COLUMN_NAME, " + _ "ORDINAL_POSITION, DATA_TYPE " + _ "FROM INFORMATION_SCHEMA.COLUMNS " + _ "WHERE (TABLE_NAME = "(0)")" Dim tableColumns As New DataTable Dim da As New SqlClient.SqlDataAdapter(_ String.Format (sqlStr, TableName), MyConnection) da.Fill(tableColumns) For i As Integer = 0 To tableColumns.Rows.Count - 1 With tableColumns.Rows.Item(i) Console.WriteLine("(0) (1) (2 )", _ .Item(1), .Item(2), .Item(3)) End With Next End Sub

2. In the Form1_Load procedure, add the following call to the RetrieveColumns procedure after the RetrieveTables procedure:

An example of creating a local Microsoft SQL Server database inMS Visual Studio

This topic shows the solution to the problem of creating a database of the SQL Server type using MS Visual Studio. The following questions are being considered:

• working with the Server Explorer window in MS Visual Studio;
• creating a local database of type SQL Server Database ;
• creating tables in the database;
• editing table structures;
• linking database tables to each other;
• entering data into tables using MS Visual Studio .

The task

Using the tools of MS Visual Studio, create a database of the MS SQL Server type with the name Education. The database contains two tables Student and Session. The tables are interconnected by some field.

The structure of the first table "Student".

Structure of the second table “Session”.

Performance

1. Download MS Visual Studio.

2. Activatewindowserver explorer.

To work with databases, Microsoft offers a lightweight database server Microsoft SQL Server. There are different versions of Microsoft SQL Server, for example: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2014 and others.

You can download these versions from the Microsoft website www.msdn.com.

This server is great for working with databases. It is free and has a graphical interface for creating and administering databases using the SQL Server Management Tool.

First of all, before creating a database, you need to activate the Server Explorer utility. To do this, in MS Visual Studio you need to call (Fig. 1)

Rice. 1. Call Server Explorer

After the call, the Server Explorer window will have an approximate appearance, as shown in Figure 2.

Rice. 2. Server Explorer window

3. Creation of the database "Education".

To create a new database based on the Microsoft SQL Server Data Provider, click on the Data Connections node and then select “ Create New SQL Server Database ...” (Fig. 3).

Rice. 3. Calling the command to create a SQL Server database

As a result, the window " Create New SQL Server Database» (Fig. 4).

In the window (in the "Server Name" field) the name of the local server installed on your computer. In our case, this name is “SQLEXPRESS”.

The "New database name:" field specifies the name of the database to be created. In our case, this name is Education.

The Use Windows Autentification option must be left unchanged and click the OK button.

Rice. 4. Creating a new SQL Server 2008 Express database using MS Visual Studio 2010

After the performed actions, the Server Explorer window will take the form as shown in Figure 5. As you can see from Figure 5, the Education database with the name

Rice. 5. Server Explorer window after adding the Education database

4. Education database objects.

If you expand the Education database (the “+” sign), you can see a list of the following main objects:

• Database Diagrams - database diagrams. Diagrams show relationships between database tables, relationships between fields in different tables, etc.;
• Tables - tables in which database data is placed;
• Views - views. The difference between views and tables is that database tables contain data, while data views do not, and the content is selected from other tables or views;
• Stored procedures - stored procedures. They are a group of related SQL statements that provide additional flexibility when working with a database.

5. Creation of the table Student.

At the moment, the Education database is completely empty and does not contain any objects (tables, stored procedures, views, etc.).

To create a table, you need to call the context menu (right-click) and select the command “ Add New Table" (Figure 6).

Rice. 6. The command to add a new table

There is another option for adding a database table using the Data menu commands:

Rice. 7. Alternative option for adding a new table

As a result, a window for adding a table will open, which contains three columns (Figure 8). In the first column “Column Name” you need to enter the name of the corresponding field of the database table. In the second column "Data Type" you need to enter the data type of this field. The third column “ Allow Nulls ” indicates the option of the possibility of the absence of data in the field.

Rice. 8. New table creation window

Using the table editor, you need to create the Student table as shown in Figure 9. The table name must be specified when it is closed.

In the table editor, you can set field properties in the Column Properties window. To set the length of a string (nvchar) in characters, the Column Properties window has a Length property. The default value for this property is 10.

Rice. 9. Student table

The next step is to set the key field. This is done by calling the “Set Primary Key” command from the context menu of the Num_book field. Links between tables will be established using the key field. In our case, the key field is the record book number.

Rice. 10. Setting the key field

After setting the primary key, the table window will look as shown in Figure 11.

Rice. 11. Table Student after the final formation

Now you can close the table. In the window for saving the table, you need to specify its name - Student (Fig. 12).

Rice. 12. Entering the name of the Student table

6. Create the Session table.

Following the pattern of creating the Student table, the Session table is created.

Figure 13 shows the view of the Session table after the final formation. The Primary Key is set in the Num_book field. The table name is given by Session.

Rice. 13. Session table

After the performed actions, two tables Student and Session will be displayed in the Server Explorer window.

Thus, any number of tables can be added to the database.

7. Editing the structure of tables.

There are times when you need to change the structure of a database table.

In order to make changes to database tables in MS Visual Studio, you first need to uncheck the “Prevent Saving changes” option. that require table re-creation” as shown in Figure 14. Otherwise, MS Visual Studio will block changes to the previously created table. The Options window shown in Figure 14 is called from the Tools menu in the following sequence:

Rice. 14. Option “ Prevent Saving changes that require table re-creation”

After customization, you can change the structure of the table. For this, the “Open Table Definition” command (Figure 15) from the context menu is used, which is called for the selected table (right mouse click).

Rice. 15. Calling the “Open Table Definition” command

This command is also located in the Data menu:

The table must first be selected.

8. Establishing links between tables.

In accordance with the condition of the problem, the tables are interconnected by the Num_book field.

To create a relationship between tables, you first need (Figure 16):

• select the Database Diagram object;
• select the Add New Diagram command from the context menu (or from the Data menu).

Rice. 16. Calling the command to add a new diagram

As a result, a window for adding a new chart Add Table will open (Figure 17). In this window, you need to select two tables Session and Student in succession and click the Add button.

Rice. 17. Window for adding tables to the diagram

Rice. 18. Tables Student and Session after adding them to the diagram

To start establishing a relationship between tables, you need to click on the Num_book field of the Student table, and then (without releasing the mouse button) drag it to the Num_book field of the Session table.

As a result, two windows will open sequentially: Tables and Columns (Fig. 19) and Foreign Key Relationship (Fig. 20), in which you need to leave everything as it is and confirm your choice with OK.

In the Tables and Columns window, the name of the relation (FK_Session_Student ) and the names of the parent (Student) and child tables are set.

Rice. 19. Tables and Columns window

Rice. 20. Relationship properties window

After the performed actions, the relationship between the tables will be established (Figure 21).

Rice. 21. Relationship between tables Student and Session

Saving a chart is done in exactly the same way as saving a table. The name of the diagram must be chosen at your discretion (for example, Diagram1).

After setting the diagram name, the Save window will open, in which you need to confirm your choice (Figure 22).

Rice. 22. Confirmation of saving changes in tables

9. Entering data into tables.

The Microsoft Visual Studio system allows you to directly enter data into database tables.

In our case, when establishing a connection (Fig. 19), the primary (Primary Key Table) table Student was selected. Therefore, you first need to enter data into the cells of this particular table. If you try to first enter data into the Session table, the system will block such input with the output of the corresponding message.

To call the data entry mode in the Student table, you need to call the Show Table Data command from the context menu (right-click) or from the Data menu (Fig. 23).

Rice. 23. Show Table Data Command

A window will open in which you need to enter the input data (Fig. 24).

Rice. 24. Entering data in the Student table

After entering data into the Student table, you need to enter data into the Session table.

When entering data in the Num_book field of the Session table, you must enter exactly the same values ​​that are entered in the Num_book field of the Student table (because these fields are related).

For example, if the values ​​entered in the Num_book field of the Student table are “101”, “102”, “103” (see Fig. 24), then these values ​​should be entered in the Num_book field of the Session table. If you try to enter another value, the system will display approximately the following window (Fig. 25).

Rice. 25. Data entry error message related tables Student and Session

The Session table with the entered data is shown in Figure 26.

In this article, you will learn what LINQ is and how to query lists using it.

What is LINQ? Language Integrated Query (LINQ) is an integrated query language, or rather a controversial Microsoft project to add a query language syntax similar to SQL. Specific definition, this tool makes it relatively easy to query tables and lists of data, not necessarily a database.

Microsoft's examples miss one small but important detail. To work with a data set, we need to use the DataContext structure, the definition of which can be obtained using SPMetal.exe for a list or library present on the Sharepoint server. In other words, we first need to define this data type, and then create a query. In general, I recommend using a special utility for working in MVS: Imtech Get SPMetal Definition Extension .

To work, we need some kind of list. Let's use a simple list - "custom list"; title - "Simple appeal"; default fields: Number, Name.

And so, let's create a simple one (see the corresponding page), with the help of which we will observe the result of our manipulations. When creating a project, we will choose an isolated trust model, if there is a need to work with lists or data from other nodes, then it is necessary to create a project for the farm.

Next, we will create a class file "SimpleReversal.cs" using SPMetal.exe or a plugin for the studio. Let's add a reference to the Microsoft.Sharepoint.Linq library. More information can be found on the MSDN site.

Let's create a Render function and my_mess. In the last function, we will form the data for display.

// overload the function
protected override void Render(HtmlTextWriter writer)
{
base .Render(writer);
my_mess(writer);
}

// Our list function
public void my_mess(HtmlTextWriter writer)
{
// Create context
DataContext data = new DataContext("http://localhost" );
// Associate it with the corresponding list on the site at the root
EntityList<Элемент>Simple_Message = data.GetList<Элемент>("Simple handling");
// Execute the query - select all rows from the "Simple access" list
var query = from mess in Simple_Message
select message;
// display all data from the query result
foreach (var element in query)
{
writer.WriteLine( "List item:"+ elem.Name.ToString());
writer.WriteBreak();
}

}

We compile the project and add it to the page. As a result, all lines in the list will be displayed.

To be continued later (article not finished)...

In the Server Explorer window, where the database tables on Microsoft SQL Server are presented in the form of a tree (see Fig. 4.1.), You can open a window for each table by clicking the Show Table Data button. It is in this window that you can edit the table data. This editing window looks approximately as shown in Fig. 4.2. Approximately, because the full volume was not included in the figure due to the large number of columns.

1. Implementing a database mapping

Database mapping (DataSet component) is necessary in order to eliminate unnecessary calls to the database and to the server. This is a very important feature, because if many users work with the database, the load on the server can be quite large - the number of requests is many times greater, which will slow down the execution of requests.

Therefore, due to the DataSet, the client's computer has a display of the database with which it works. Upon completion of the work, the client and server databases are synchronized (or rather, changes made in the client database are made to the server database)

The mapping of our database on MS Visual Studio 2005 looks like this:

All numeric data is of type Int32, string data is String. The sizes of the string data correspond to those given in the physical database model.

The screenshot shows that each table also has TableAdapters. These are kind of bridges between the DataSet and the data source. There are methods like Fill and GetData()

This method is designed to fill the table with the necessary data from the source (database file)

GetData() method

Designed to take data from the display table.

1. Software Implementation

The main feature in the implementation of the software is the fact that all classes responsible for editing database tables are inherited from one base class, which makes it easy to implement. The methods of this base class have been described above. The virtual methods of the base class must be overridden in the descendant class. All these methods allow you to change such parameters in the child as: the name of the form header, filling and saving the required database table (one child class works with only one table, in the sense of editing data; however, when displaying data to the user, it is possible to use several database tables to display data in a user-friendly way).

1. Program interface

The main window is an MDI application that has the following menu:

Each of the tables for editing the database is called by the corresponding menu item "Tables".

A report on the load on teachers is also created by clicking on the corresponding menu item "Reports".

As an additional feature of the software, there is a menu item "Console", which allows you to execute SQL queries to the database entered by the user and display the result in a table (DataTable component). To do this, of course, you need to know the names of the tables and fields, and was mainly created for the purpose of convenience in the implementation of the software, but other "initiated" users can also use it.