[Linq2sql 1] Application of four SQL protection methods in LINQ

I wrote some information on my blog some days ago. net 3.x( C #3.0), which also mentions some query syntaxes (syntaxes) about LINQ ), from today on, let's take a look at LINQ to SQL (hereinafter referred to as linq2sql), which is a query of the database. It was previously called dlinq. I often see what it is about to use LINQ? He is Language Integrated Query Is a programming method similar to SQL statements to operate on objects.

Some previously mentioned features, such as anonymous type and automatic attribute, will also be used when using LINQ ...... so if you have not read C # new features, you can find them below:

[. Net 3.x new feature 1] automatic attributes, object initialization and set Initialization

[. Net 3.x New Feature 2] Extension Method

[. Net 3.x new feature 3] lambda expressions

[. Net 3.x New Feature 4] LINQ query syntax

[. Net 3.x new feature 5] anonymous type

Linq2sql Is An ORM that allows you to write. netProgramTo control or operate relational databases to achieve query results. You can use LINQ to retrieve data in the database, or update and add data through it. At the same time, linq2sql also supports transactions, attempts, and stored procedures. Through the linq2sql designer of vs2008 (beta version, the same below), we can easily define some entity classes, we can add a new item. dbml file, and drag the data table from the database server window. Let's take a look at the specific example:

We define two entity classes, artile and category. We can also drag stored procedures to the method window. Because our stored procedures and data tables may have prefixes or other identifiers, we can view the Properties window and change their name attributes to a name that is easy to recognize. Open designer. check whether several classes are generated in the CS file. One of the most important is the class with the datacontext suffix (the class with the name of the current dbml file name and datacontext ), he is the context of this LINQ, and we can use him to execute our operations. The above two are foreign key relationships, and they also generate a relational class. In the category class, there is a set of articles and an association attribute in articles, the key-value type is also determined when values are assigned.

Next let's take a look at some examples of practical applications (the four major protection methods of SQL <Select/insert/update/delete> ):

1. retrieve data from the database <SELECT>

1 // Three overload (), (string connectionstring), (idbconnection connection)
2 Itlivesnetdatacontext cntx =   New Itlivesnetdatacontext ();
3 // You can also initialize the itlivesnetdatacontext instance directly.
4 Cntx. Connection =   New Sqlconnection (
5 Configurationmanager. connectionstrings [ 0 ]. Connectionstring );
6 VaR articles = From In Cntx. Articles
7 Where a. categoryid =   1
8 Orderby A. postdate descending
9 Select;

The aboveCodeAll data with the type Id 1 in the database articles table will be returned and sorted in descending order according to the release time. Specifically, articles is actually an instance of ienumerable <t>. In this example, T is the article object class.

2. Insert new data into the database <Insert>

1 // Insert new type C ++
2 CATEGORY category =   New CATEGORY {categoryname = " Cplusplus " , Isactived = True , Parentid =   1 };
3
4 // Insert newArticleType: C ++
5 VaR article =   New Article {subject =   " C ++ " , Author = " Cyber soul soldier " };
6
7 Category. Articles. Add (Article );
8
9 Cntx. categories. Add (category );
10
11 Cntx. submitchanges ();

3. Update new data <Update>

CATEGORY category = Cntx. categories. Single (P => P. categoryname. toupper () =   " CSHARP " );

Category. isactived= True;
Category. Description= "Update CSHARP!";

Cntx. submitchanges ();

4. delete data in the database <Delete>

1 VaR delartiles = From S In Cntx. Articles
2 Where S. categoryname =   " Cplusplus "
3 Select S;
4
5 Cntx. Articles. removeall (delartiles );
6 Cntx. submitchanges ();

The above is an example of the four major protection methods of SQL. The example is very simple and can be understood without much explanation. In the database, we often need to use the database retrieval method, that is, querying the database by page. However, when we are dealing with large data, we often use stored procedures to complete this profession. Next, let's take a look at some basic operations on the paging and calling stored procedures of LINQ. Of course, the stored procedure to be used as an example is not a stored procedure for querying the database by page. The Code is as follows:

1. Retrieve articles by PAGE

1 VaR pagedarticles = (From S In Cntx. Articles
2 Where S. categoryname. toupper () =   " CSHARP "
3 Orderby S. postdate descending
4 Select s). Skip ( 100 ). Take ( 20 );

The above code searches for articles with the C # type in the database (sorted in descending order of posting time), and then retrieves 20 rows of records, if you calculate 20 records per page, that is, 6th pages, the SKIP () and take () methods are used.

2. Apply stored procedures in linq2sql

When we create a dbml File above, we drag a stored procedure (getparentcategories) to the method box. The main purpose of writing this stored procedure is to obtain the list of parent types of the current type. The Code is as follows:

1 VaR categories = Cntx. getparentcategories ( 1 );

OK. The main purpose of today is to take a look at linq2sql as a whole. In the following blog post, I will learn more about the fun of linq2sql, although it may not be used in my project. Since these things are converted to standard SQL statements for query through the database engine, I think the efficiency should be affected, but now I only want to learn more. net 3.x( C #3.0) brings us new features.

(Friendly reminder: if you use a prefix or others to name your database table, when creating a dbml file, you must rewrite its name attribute to the name used in C # programming. Otherwise, the code will become ugly during programming !!!)