In this article you will find out what LinQ is and how to make requests for lists with it.

What is LINQ? Language Integrated Query (LINQ) is the language of integrated requests, and more accurately an ambiguous Microsoft project to add requests for SQL similar to SQL. Specific definition, with this tool, you can relatively simply create requests to tables and data lists, not necessarily to the database.

In examples from Microsoft, one small, but important item is overlooked. To work with a dataset, 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 determine this type of data, and then create a request at all recommend to use special utility To work in MVS: Imtech Get SPMetal Definition Extension.

To work, we will need some list. We use a simple list - "Customizable list"; The name is "simple appeal"; Default fields: number, name.

And so, let's create a simple (see the appropriate page), with which we will observe the result of our manipulations. When creating a project, choose an isolated confidence model, if you need to work with lists or data of other nodes, then you need to create a project for a farm.

Next, create a file class "Sorture .cs" using spmetal.exe or plugin for the studio. Add a link to the microsoft.sharepoint.linq library. For more information, please visit MSDN.

Create a Render and My_Mess feature. In the last function, we will generate data to display.

// overload the function
protected Override Void Render (HTMLTEXTWRITER WRITER)
{
base .render (Writer);
My_Mess (Writer);
}

// Our feature for working with a list
public Void My_Mess (HTMLTEXTWRITER WRITER)
{
// Create context
DataContext Data \u003d New Datactext ("http: // localhost");
// We connect it with the appropriate list on the site at the root
EntityList.<Элемент> Simple_Message \u003d Data.getList.<Элемент>("Easy appeal");
// Perform a request - Select all the lines from the list "Simple appeal"
var Query \u003d From Message in Simple_Message
select Mess;
// Display all data from the query result
foreach (Var Elem in Query)
{
Writer.Writeline ( "List element:" + Elem. Nutrition.tostring ());
writer.writebreak ();
}

}

Complete the project and add to the page. As a result, all lines are on the list will be displayed.

Continued later (the article is not finished) ...

In the Server Explorer window, where the database tables are presented on Microsoft SQL Server In the form of a tree (see Fig. 4.1.), For each table, you can open a window by pressing the Show Table Data button. This window provides the ability to edit the table data. It looks like this editing window is approximately as represented in Fig. 4.2. Approximately because the total volume did not enter the drawing in mind the large number of columns.

1. Implementing the database display

Displaying a database (DataSet component) is required in order to exclude extra access to the database and to the server. This is a very important feature, because if there are many users with the database, the server load can be quite large - the number of requests many times larger, which will lead to a slowdown in the execution of requests.

Therefore, at the expense of DataSet on the client's computer, the database is displayed with which it works. Upon completion of the client's base and server are synchronized (or rather, changes made in the client database) are made to the server base)

Displays our database on MS Visual Studio 2005 as follows:

All numeric data have an int32 type, string - String. The dimensions of the string data correspond to those given in the physical model of the database.

It shows that there are also TableAdapters in each table. This is a kind of bridges for the DataSet connection and data source. Here are techniques such as Fill and GetData ()

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

GETDATA ()

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 the database tables are inherited from one basic class, which gives us convenience in implementations. Methods of this basic class have been described above. The basic class virtual methods must be blocked in the descendant class. All these methods allow you to change the parameters such as: the name of the shape header, filling and saving the desired database table (one time-descendant works only with one table, in the sense of data editing; however, when data is displayed, it is possible to use multiple database tables In order to show data in the user-friendly form).

1. Program Interface

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

Each of the tables to edit the database is called by the corresponding menu item "Tables".

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

As an additional software ability, there is a "Console" menu item, which allows you to execute SQL database requests entered by the user and output the result in the table (DATATABLE component). For this, of course, it is necessary to know the names of the tables and fields, and, mostly, it was created for the purpose of convenience in the implementation of the software, but also other "dedicated" users can also be used.

Example of creating a local database Microsoft SQL. Server B.MS Visual Studio.

This topic shows the solution of the SQL Server type database task using MS Visual Studio. The following questions are considered:

• working with the Server Explorer window in MS Visual Studio;
• creating a local SQL Server Database type database;
• creating tables in the database;
• editing table structures;
• binding the database tables between themselves;
• making data into tables MS Visual Studio.

The task

Using MS Visual Studio to create a MS SQL Server type database named Education. The database contains two Student and Session tables. Tables between themselves are connected by some clamp.

The structure of the first table "Student".

Structure of the second table "Session".

Performance

1. Download MS Visual Studio.

2. Activatewindow Server Explorer.

To work with databases, Microsoft offers a lightweight database server microsoft data SQL Server. Exist different versions Microsoft SQL Server, for example: Microsoft SQL Server 2005, Microsoft SQL Server 2008, Microsoft SQL Server 2014 and other versions.

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 to create and administer databases using SQL Server Management Tool.

First of all, before creating a database, you need to activate the SERVER EXPLORER utility. For this, in MS Visual Studio you need to call (Fig. 1)

View -\u003e Server Explorer

Fig. 1. Call Server Explorer

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

Fig. 2. SERVER EXPLORER window

3. Creating a "EDUCATION" database.

To create a new database based on Microsoft SQL Server data provider, you need to click on the Data Connections node, and then choose " Create New SQL Server Database ..."(Fig. 3).

Fig. 3. Calling the SQL Server database creation command

As a result, the window opens CREATE NEW SQL Server Database"(Fig. 4).

In the window (in the Server Name field) indicates the name local Serverinstalled on your computer. In our case, this is the name "sqlexpress".

In the "New Database Name:" field, the name of the generated database is specified. In our case, this is the name of Education.

The USE Windows AUTENTIFICATION option must be left unchanged and press the OK button.

Fig. 4. Creation new Base SQL Server 2008 Express data with MS Visual Studio 2010

After the actions performed, the Server Explorer window takes the view, as shown in Figure 5. As can be seen from Figure 5, the Education database is added to the list of existing databases.

sasha-PC \\ sqlexpress.education.dbo

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

4. EDUCATION database objects.

If you deploy the Education database ("+" sign), you can see a list of the following main objects:

• Database Diagrams - Database Charts. Charts show links between database tables, relations between fields of different tables, etc.;
• Tables - tables in which the database data is placed;
• Views - Presentations. The difference between the views and tables is that the database tables contain data, and data representations do not contain them, and the contents are selected from other tables or representations;
• Stored Procedures - stored procedures. They represent a group of related operators in SQL, which provides additional flexibility when working with the database.

5. Creating a Student Table.

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

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

Fig. 6. A command of adding a new table

There is another option to add a database table using the DATA menu commands:

Data -\u003e Add New -\u003e Table

Fig. 7. Alternative option Additions of a new table

As a result, the Add table window 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. In the third column "Allow Nulls" indicates the option about the absence of data in the field.

Fig. 8. Window create a new table

Using the table editor, you need to form the Student table as depicted in Figure 9. The table name must be set when it is closed.

In the table editor, you can set field properties in the Column Properties window. In order to set the length of the string (NVCHAR) in the characters in the Column Properties window there is a Length property. By default, the values \u200b\u200bof this property are 10.

Fig. 9. Table Student.

The next step needs to set the key field. This is done by calling the "SET PRIMARY KEY" command from context menu Num_Book fields. Using the key fields will be installed communication between the tables. In our case, the key field is the number of the test book.

Fig. 10. Setting the key field

After installing the primary key, the table window will be viewed as depicted in Figure 11.

Fig. 11. Table Student after final formation

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

Fig. 12. Enter the Student Table name

6. Creating a session table.

Sample Student Create Table Session is created.

Figure 13 shows the view of the session table after the final formation. The primary key (Primary Key) is installed in the Num_Book field. The table name specifies session.

Fig. 13. SESSION TABLE

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

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

7. Editing table structure.

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

In order to make changes to the database table in MS Visual Studio, you first need to remove the option "Prevent Saving Changes 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 such a sequence:

Tools -\u003e Options -\u003e Database Tools -\u003e Table and Database Designers

Fig. 14. Option " Prevent Saving Changes That Require Table Re-Creation”

After setting, you can change the structure of the table. To do this, use the "Open Table Definition" command (Figure 15) from the context menu, which is called for the selected table (right click mouse).

Fig. 15. Calling the command "Open Table Definition"

Also, this command is located in the DATA menu:

Data -\u003e Open Table Definition

Pre-table must be highlighted.

8. Establishing connections between tables.

In accordance with the condition of the task, the tables are connected between the Num_Book field.

To create a connection between the 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).

Fig. 16. Calling a team of adding a new chart

As a result, a window for adding a new ADD Table chart will open (Figure 17). In this window, select sequentially two session and student tables and click the Add button.

Fig. 17. Window add tables to chart

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

To start setting the relationship between the tables, you need to click on the Student Num_Book field, and then (without releasing the mouse button) drag it on the SESSION NUM_BOOK field.

As a result, two windows will open: 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 on OK.

The Tables and Columns window sets the relationship (FK_SESSION_STUDENT) and the names of the parent (Student) and child tables.

Fig. 19. Tables and Columns window

Fig. 20. Relationship properties window

After the action performed, the relationship between the tables will be set (Figure 21).

Fig. 21. The relationship between Tables Student and Session

Saving the chart is carried out in the same way as saving the table. The diagram name must be selected at its discretion (for example, Diagram1).

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

Fig. 22. Confirmation of storage of changes in tables

9. Entering data into the table.

The Microsoft Visual Studio system allows you to directly make data in the database table.

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

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

Fig. 23. SHOW TABLE DATA Team

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

Fig. 24. Entering data in Student Table

After making data to the Student table, you need to make data in the session table.

When making data in the Num_Book field, the SESSION table must be entered exactly the same values \u200b\u200bthat are entered in the Student Num_Book field (since these fields are related to each other).

For example, if in the Num_Book field, the Student tables are entered values “101”, “102”, “103” (See Fig. 24), you should enter these values \u200b\u200bin the SESSION NUM_BOOK field. If you try to enter a different value, the system will release approximately the next window (Fig. 25).

Fig. 25. Data entry error message Tables Student and Session

SESSION table with data entered is shown in Figure 26.

Annotation: After reading this lecture, you can: create requests using a designer sQL queries Server Management Studio, Remove the database information from the database system tables, to dynamically create simple requests based on user input, format the user input and filter complex dynamic requests, perform a syntax analysis and reformat the data for use in the filter, protect the database from attacks such as "SQL-INJECTION", use the sp_executesql procedure for sending a request

In the previous lecture, it was described how to increase performance Requests. Now you know how to create an effective set of requests to provide users with the most useful information From your application using pre-created queries in stored procedures or views.

However, in any applications, in addition to the easiest, it is impossible to learn all possible types of information types in advance that users may be needed, and how they want to filter and streamline it. Instead of trying to provide all the features, you can provide the user to the management of the information reported by the application. This lecture tells how to dynamically build queries based on the choice that the user does during the execution of the working cycle.

User Interface for Build Requests

The SQL Server Management Studio environment includes a complex interface to build requests. Let's study this interface so that you have an idea of \u200b\u200bhow you can create queries dynamically. Your application will not need all control elementsproviding sQL environment Server Management Studio. In fact, you need to thoroughly think about how to best limit users to choose from.

Create a request using the SQL Server Management Studio request designer

Removing information about database tables

To provide the user with the list of parameters, the application is likely to be learned information about the database tables. There are several ways to get this information. The most important of these methods is to use the INFORMATION_SCHEMA schema. This scheme is standard in any database.

Application information_schema.

The INFORMATION_SCHEMA scheme is a special scheme that is in each database. It contains definitions of some database objects.

Information_schema complies with the ANSI standard, which is designed to extract information from any ANSI-compatible database kernel. SQL Server Information_schema consists of a set of representations that request SYS * database tables containing information about the database structure. The request to these tables can be performed directly, in the same way as any database tables. However, in most cases, in order to extract information from the * SYS tables, it is better to use the Inform_schema schema views.

Note. The information_schema scheme sometimes requests tables that are not needed, which damages productivity. In the following example of this lecture, this is not particularly important because the application has already expected user input. However, this should be considered if the speed is an important aspect for your application.

Here is the base T-SQL code, which is used to obtain information about columns included in the table:

Please note that you need to select the Table_schema field for the table. This may be important to create similar queries in the future. To experiment with the methods described in this lecture, create a new project in Visual Studio.

Create a new project Visual Studio

1. Select from the START menu of the All Programs commands, Microsoft Visual Studiio 2005, Microsoft Visual Studio 2005.
2. In the Visual Studio menu, select File, New, Project (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. Give the chapter7 name project and click OK,
4. The application for this example can be found in the files of the examples in the \\ chapter7 \\ dynquery folder. You can cut and insert code for the following procedures from the Form1.VB file.

Obtaining a list of tables and representations

As a rule, you will need to provide the user with the ability to select not only columns, but also a table for a dynamic query, so you have to display a list of tables for the user. Necessary information You can get with the following request:

Select table_schema, table_name, table_type from information_schema.tables

In Annex, this request can be used as follows.

We receive a list of tables

The above code on Visual Basic initializes the SQLommand object with the COM name with the SQL string that needs to be done, and then executes the SQLCommand object. This is the easiest way to execute the T-SQL offer from the application.

As an exercise, you can place the schemes and tables obtained during the implementation of the Load procedure of our form, in the user interface in the form of a form so that the user can choose a scheme and table to work. For example, in this lecture, we assume that the user has chosen the Sales scheme and the Customer table.

After the user has selected the table, you can remove the list of columns for this table using the same method using the user input as the table name in the query. To do this, enter the deputy in the query string, and then replace this deputy call to String.Format. In the code below, the substituent in the query line - (0).

We get a list of columns

1. Add the following RETRIEVeColumns procedure below the RETRIEVETABLES procedure:

   Sub RetrieveColumns (ByVal TableName As String) MyConnection As New SqlClient.SqlConnection (_ "Data Source \u003d \\ SQLExpress;." & _ "Initial Catalog \u003d AdventureWorks; Trusted_Connection \u003d Yes;") Dim sqlStr As String sqlStr \u003d "SELECT TABLE_SCHEMA, TABLE_NAME , Column_name, "+ _" ordinal_position, data_type "+ _" from information_schema.columns "+ _" WHERE (Table_Name \u003d "(0)") "Dim TableColumns As New Datatable Dim Da As New SqlClient.SqldataAdapter (_ string.format (SQLSTR, Tablename), MyConnection) DA.Fill (TableColumns) for i as integer \u003d 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 RETRIEVECOLUMNS procedure call after RETRIEVETABLES procedures: