asp. net Connecting Database with LINQ to SQL and Usage Analysis of SQL Operation Statement

This article illustrates the use of asp. net LINQ to SQL to connect database and SQL operation statement usage. Share it for your reference, as follows:

Introduction to LINQ

LINQ: Language Integration Query (Language INtegrated Query) is a set of extensions for the c # and Visual Basic languages. It allows you to write C # or Visual Basic code to manipulate memory data in the same way that you query a database.

LINQ is a query language, and SQL1, through a combination of some keywords, the final query.

Classification of LINQ

LINQ to Object
LINQ to XML
LINQ to SQL
LINQ to DataSet
LINQ to ADO.NET

The namespace is System. Linq;

LINQ Query

Syntax:
from Temporary Variable in Collection Object or Database Object
where conditional expression
[orderby Condition]
[group by Condition]
select Queried Values in Temporary Variables

Example:

from c in Student select c;

Suppose Student is one entity class corresponding to one database table

The query statement is:

from c in Student select c; 
// Whole table query 
from c in Student where c.name==" Zhang 3" select c;
// The query name is Zhang 3 All the information of 

C is a temporary variable and can be taken arbitrarily.

Query several fields

1. Query several fields in the student table

var query=from c in student  select new {c.number,c.name,c.age};

2. Query several fields in the student table and reset the column names

var query=from c in student select new { Student number =c.number, Name =c.name,  Annual collar =c.age};

Matters needing attention

An linq query statement must begin with an from clause and end with an select clause.

Linq is a technology that appeared in. NET Framework 3.5, so when creating a new project, you must choose version 3.5 or later, otherwise it will not be used.

3. Sort

var query=from c in student orderby c.age ascending select c;// Ascending order 
var query=from c in studeng orderby c.age descending select c;// Descending order 

4. Grouping

var  query=from c in student group c by c.sex into d select new { Gender =c.age}; //d For the new table, c.sex For grouping fields 

5. Filter duplicate records

var query=(from c in dc.student select new {c.place}).Distinct();//Distinct() Is to filter duplicate records. 
var query=(from c in dc.student select new { Distribution area =c.place}).Distinct();

6. Number of query rows

(1) The total number of rows in the lookup table

int count=student.count();

(2) Query the number of rows that meet the criteria

int count=(from c in student where c.name==" Wang Ming " select c).count();

7. Fuzzy query

from c in dc.Student where c.name.Contain(" Wang ") select c

Inquire all students whose names contain the word Wang

var query=from c in dc.Student where c.number.Contain("2009") select c

Query all students with 2009 characters in their student numbers

Query results

The query result of LINQ may be one object or one dataset, which can be received by var type

Such as:

var query=from c in Student select c;

The input results can be used in foreach loop

Such as:

var query=from c in Student select c;
foreach( var x in query)
{ Response.Write(x.toString());}

Common function

Count (): Count the number of rows of query results
Distinct (): Filter duplicate rows of query results
First (): Gets line 1 of query results
Last (): Gets the last row of the query result
Take (n): Get the first n row of the query result
Skip (n): Skip the previous n line and start with n+1 line
Skip (m). Take (n): Start with m+1 and take the following n line

8. Update operation

Idea: First, query out the rows that need to be updated, and then update them. LINQ only needs to write out the query statement, does not need to write the update statement!

Example: Update the student number 00001 in the student table

1. (from c in Stuent where c. id = = "00001" select c). First ();

Display data query results in data space:

The first two lines are connected to the database, of which 1, often used, can be placed at the beginning, so that you don't have to write every time you use it.

studentDataContext dc = new studentDataContext();
// Note: xxxDataContext Need and .dbml File name of 1 To 
var query=from c in dc.student select c;
GridView1.DataSource=query;
GridView1.DataBind();

Update operation

from c in Student select c; 
// Whole table query 
from c in Student where c.name==" Zhang 3" select c;
// The query name is Zhang 3 All the information of 

0

from c in Student select c; 
// Whole table query 
from c in Student where c.name==" Zhang 3" select c;
// The query name is Zhang 3 All the information of 

1

9. Insertion operation

from c in Student select c; 
// Whole table query 
from c in Student where c.name==" Zhang 3" select c;
// The query name is Zhang 3 All the information of 

2

10. Data deletion

string num = TextBox6.Text.Trim();
student stu =(from c in dc.student where c.number == num select c).First();
dc.student.DeleteOnSubmit(stu);
// Delete the fields in the database, regardless of how to delete them, just call this function. 
dc.SubmitChanges();
bind();

For more readers interested in asp.net, please check the topics of this site: "Summary of String Operation Skills of asp. net", "Summary of Operation Skills of XML of asp. net", "Summary of File Operation Skills of asp. net", "Summary of Skills of asp. ES211ajax" and "Summary of Cache Operation Skills of asp. net".

I hope this article is helpful to everyone's asp. net programming.