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

The examples in this article describe the use of ASP.net 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 XML
LINQ to SQL
LINQ to DataSet
LINQ to Ado.net

The namespace is system.linq;

LINQ Query

Grammar:
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

Cases:

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 c.name== "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.name,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 =c.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 C.sex into D select new {gender =c.age}; D is a new table, C.sex is a grouped field

5. Filter duplicate record

var query= (from C in dc.student select New {c.place}). Distinct ();//distinct () is the role of filtering duplicate records.
var query= (from C in dc.student select new {distribution Area =c.place}). 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 c.name== "Wang Ming" select c). Count ();

7, Fuzzy query

From the C in DC. Student where C.name.contain ("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 c.id== "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.datasource=query;
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
stu.name = 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

Insert
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;
Stu.name = Username;
Stu.sex = sex;
Stu.place = 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 asp.net related content can view the site topics: "asp.net string operation tips Summary", "ASP.net Operation XML Skills summary", "asp.net file Operation skills Summary", "ASP.net Ajax Skills Summary topics" and " Summary of ASP.net caching operation techniques.

I hope this article will help you with ASP.net programming.