use LINQ to SQL connection database and SQL Operation Statement Usage Analysis _ Practical Tips

Source: Internet
Author: User

The examples in this article describe the use of with LINQ to SQL connection databases and SQL action statement usage. Share to everyone for your reference, specific as follows:

Introduction to LINQ

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

LINQ is a query language that, like SQL, implements the final query with some combination of keywords.

Classification of LINQ

LINQ to Object
LINQ to DataSet

The namespace is system.linq;

LINQ Query

From temporary variable in collection object or database object
Where Condition expression
[By condition]
[GROUP BY condition]
The value queried in the Select temporary variable


From C in Student select C;

Suppose student is an entity class corresponding to a database table

The query statement is:

From C in Student select C; 
Whole table query from
C in Student where "John" select C;
Query for all information with name John

where c is a temporary variable, can be arbitrarily taken.

Query several fields

1, query student a few fields in the table

Copy Code code as follows:
var query=from c in student select New {c.number,,c.age};

2, query the Student table several fields, and reset the column name

Copy Code code as follows:
var query=from c in student select New {School Number =c.number, name, year collar =c.age};

Attention Matters

A LINQ query statement must start with a FROM clause and end with a SELECT clause.

LINQ is a technology that appears in the. NET Framework 3.5, so you must choose 3.5 or later when you create a new project, or you cannot use it.

3, sorting

var query=from c in Student order C.age Ascending Select c;//Ascending
var query=from c in Studeng order by C.age Descending Select c;//Descending

4. Group

Copy Code code as follows:
var query=from c in student Group C from into D select new {gender =c.age}; D is a new table, is a grouped field

5. Filter duplicate record

var query= (from C in dc.student select New {}). Distinct ();//distinct () is the role of filtering duplicate records.
var query= (from C in dc.student select new {distribution Area}). Distinct ();

6, Query line number

(1) Total number of rows in the query table

int Count=student.count ();

(2) The number of rows that the query satisfies the condition

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

7, Fuzzy query

From the C in DC. Student where ("King") Select C

Query all students whose names contain the word "Wang"

Copy Code code as follows:
var query=from c in DC. Student where C.number.contain ("2009") Select C

All students with 2009 characters in the query number

Query results

LINQ query results can be either an object or a dataset that can be received with the Var type

Such as:

var query=from c in Student select C;

Input results can be a foreach loop

Such as:

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

Common functions

COUNT (): Calculates the number of rows for a query result
Distinct (): Filter for duplicate rows of query results
First (): Top line to get query results
Last (): Get final line of query results
Take (n): Get the first n rows of the query results
Skip (N): Skip top N lines, start from n+1 line
Skip (M). Take (N): Start with the m+1 line and take the following n rows

8. Update operation

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

Example: Update students with a student-form middle school number of 00001

1. (from C in stuent where "00001" select C). A ();

To display data query results in the data space:

The first two lines are connected to the database, which is used frequently and can be put to the very beginning so that you don't have to write every time you use it.

Studentdatacontext dc = new Studentdatacontext ();
Note: Xxxdatacontext needs to be consistent with the. dbml file name
var query=from c in dc.student select C;
Gridview1.databind ();

Update action

String num = TextBox2.Text.Trim (); Related information about how much to update the learning number
string name = TextBox3.Text.Trim ()//update name
int age = Convert.ToInt32 (TextBox4.Text.Trim ()) //int32 Integer//Updated age
studentdatacontext dc=new studentdatacontext ();
Student stu= (from C in dc.student where C.number==num select C). First ();//variable, and select row One. The where is updated according to the primary key, and the other fields cannot. That is, you update the other fields after you get the primary key.
//In addition to the primary key not modified, other fields can be modified = name;//Assign the newly modified name to the field name in the database name
stu.age = age;//Modify the age
DC. SubmitChanges ()//True to modify the database.
bind ();//A change, on display, and in time to refresh the same.

private void bind ()
Studentdatacontext dc = new Studentdatacontext ();
 var query = (from C in dc.student select C); Full table Query
 = gridview1.datasource = query;
 Gridview1.databind ();

9. Insert operation

String num = TextBox1.Text.Trim ();
String username = TextBox2.Text.Trim ();
string sex = TextBox3.Text.Trim ();
String place = TextBox4.Text.Trim ();
int age = Convert.ToInt32 (TextBox5.Text.Trim ());
Student stu = new Student ()///Create object
//Assignment value
//primary key cannot repeat
stu.number = num; = Username; = sex; = place;
Stu.age = age;
Dc.student.InsertOnSubmit (Stu);
Insert Operations on table Studen table.
//Note that the function must be written correctly.
DC. SubmitChanges ();//Database Save
Bind ()//the same content as above

10. Data deletion

String num = TextBox6.Text.Trim ();
Student Stu = (from C in dc.student where c.number = = num Select c). A ();
Dc.student.DeleteOnSubmit (Stu);
Delete the fields in the database, and how to delete them, just call the function.
DC. SubmitChanges ();
Bind ();

More interested readers of related content can view the site topics: " string operation tips Summary", " Operation XML Skills summary", " file Operation skills Summary", " Ajax Skills Summary topics" and " Summary of caching operation techniques.

I hope this article will help you with programming.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.