Simple Link To SQL, linktosql

Simple Link To SQL, linktosql

Linq and Its Extensions

As a Data Query Language (it can query data from multiple data sources), the following extensions are available:

 

Introduction To Linq To SQL

To SQL is. an ORM (Object Relation Model) Entity Framework launched in NET Framework 3.5. in addition to ing data tables into Entity classes, a good object framework should also support the use of oriented ideas to manipulate relational databases. currently, the most popular ORM frameworks include: Linq To SQL \ Entity Framework \ nhib.pdf.

In the process of using Linq To SQL, we can perform common data addition, deletion, modification, and difference operations with few handwritten SQL statements. however, its underlying implementation is still Ado.net. so how can I check the SQL statements generated by Linq To SQL? You can use the Log attribute of DataContext to implement the detection function.

 

Linq To SQL designer

After you drag and drop data tables in the server resource manager and place them in the designer area of the Linq To SQL, VS automatically generates N + 1 (N is the number of data tables) for these data tables) classes. these classes can be roughly divided into two types: one is the database context and the other is the entity class. the database context class is the class whose name ends with DataContext. It inherits from Sytem. data. linq. dataContext.

Their functions are: The DataContext class is used to interact with the database (add, delete, modify, and query). Each time you perform database operations, you should first create such objects;

Each object class corresponds to a data table.

The DataContext feature is marked as Database, indicating that it is related to the Database.

The feature mark of the object class is Table, indicating that it is related to the data Table.

The attribute feature mark of the object class is Column, which indicates that it is related to the data Column.

 

Common attributes and methods of context classes in DataContext Databases

CreateDatabase (): Create a database

DeleteDatabase (): deletes a database.

DatabaseExits (): determines whether the database exists

Log: This attribute is used TO detect and write SQL statements generated by linq to SQL.

 

Common Operations of Linq To SQL

Add data

Void InsertOnSubmit (): adds one record each time.

Void InsertAllOnSubmit (): adds multiple records at a time.

Note: After InsertOnSubmit is called, you must call DataContext. SubmitChanges ();

 

Modify data

First, query the elements to be modified.

Second, change the corresponding attribute of the element.

Finally, call dataContext. SubmitChanges ()

 

Delete data

First, query the elements to be deleted.

Second, call void DeleteOnSubmit () to delete a single element. To delete multiple elements, call void DelteAllOnSubmit ()

Finally, call dataContext. SubmitChanges ();

 

Data Query

There are two common usage methods for data query. One is to use the standard syntax of linq, and the other is to use the Lambda expression extension method.

Single Table query:

 

Multi-Table query:

 

Typical application scenarios

Scenario 1: on a page, add a salary template and multiple wage entries of the template.

 

Practice 1:

 

Practice 2:

 

Both of the above methods can implement functions. first, add a new salary template and then add a new salary entry. the second approach is to add multiple wage entries to the wage template object and then submit the wage template to the database.

It should be noted that in practice 1, the wage template ID is set for the wage entry object, and practice 2 does not set the wage template ID for the wage entry. But why is the final effect the same?

Because when we add a salary entry to a salary template object, the field of the primary-foreign key relationship is automatically assigned a value to the linq to SQL statement.

Scenario 2: Use Linq To SQL for paging

The paging function requires the combination of two functions: Skip () and Take ()

Skip (int count): Skips count data entries

Take (int count): extract count records from the starting position

Paging query: dataContext. XXX. Skip (pageIndex * pageSize). Take (pageSize)

PageIndex is the page record to be extracted. pageIndex> = 0

PageSize is the number of records displayed per page.

 

 

 

 

Others

• EntitySet <T>: indicates the set of data entities.
• System. Data. Linq. Table <T>: This class indicates the Data Table object.
• How can I determine whether data modification, deletion, or addition is successful by using Linq To SQL?

Because the InsertOnSubmit method and DataContext. the SubmitChanges () method does not return any values, so they cannot determine whether the operation is successful. the criterion is: if an exception is not thrown, the operation succeeds. if an exception is thrown, the operation fails.

• For the ID column (automatically generated column), call DataContext. XXX. after InsertOnSubmit (), the value of the ID column cannot be obtained; only when DataContext is called. submitChanges () can be used to obtain the value of the ID column.