LINQ with DML Queries

This Post is a continuation of LINQ.
Here the following concepts are explain about LINQ with DML Queries.

DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. They are:

• Select
• Insert
• Update
• Delete

Select

The SELECT statement returns a result set of records from one or more tables. The SELECT statement has many optional clauses: WHERE, ORDER BY, GROUP BY

For Example:

• A Table name as tbl_Student.
• This table contain the following columns: StudentID, Firstname, Lastname, Location, Email.

LINQ SELECT Statement with WHERE Clause:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      from stud in db.tbl_Students
      where db.Location == "Pondy"
      select stud;

LINQ SELECT Statement with ORDER BY:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      from stud in db.tbl_Students
      orderby stud.Location
      select stud;

LINQ SELECT Statement with GROUP BY:

DataClasses1DataContext db = new DataClasses1DataContext();
var student =
      group s by s.tbl_Students into g
      where g.count() >= 1
      select new
      {
          g.Key,
          LocationCount = g.Count()
      };

Insert

To execute a SQL Insert, just add objects to the object model you have created, and call SubmitChanges on the DataContext.

The following example, Let insert  a new Student detail into tbl_Student table. This table contain the following columns: StudentID, Firstname, Lastname, Location & Email.

LINQ INSERT Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
tbl_Student ts = new tbl_Student();
      ts.StudentID = textBox1.Text;
      ts.Firstname = textBox2.Text;
      ts.Lastname = textBox3.Text;
      ts.Location = textBox4.Text;
      ts.Email = textBox5.Text;
      db.tbl_Students.InsertOnSubmit(ts);
      db.SubmitChanges();
MessageBox.Show("Value inserted Successfully");

Update

To Update a database entry, first retrieve the item and edit it directly in the object model. After you have modified the object, call SubmitChanges on the DataContext to update the database.

The following example, Let retrieve a Student detail from tbl_Student table who are all from Location Pondy. Then change Location = “Pondy” to “Puducherry”. Finally SubmitChanges is called to send the changes to database.

LINQ UPDATE Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
var Loc = from s in db.tbl_Student
      where Loc.Location.Contains(”Pondy”)
      select Loc;
foreach (var student in Loc)
{
   if (student.Location == "Pondy")
   {
        student.Location = "Puducherry";
   }
}
db.SubmitChanges();

Delete

To Delete an item, remove the item from the collection to which it belongs, and then call SubmitChanges on the DataContext to commit the change.

The following example, delete a Student detail from tbl_Student table who are all from Location 'Chennai'. Finally SubmitChanges is called to send the changes to database.

LINQ DELETE Statement:

DataClasses1DataContext db = new DataClasses1DataContext();
var delLoc = from s in db.tbl_Student
      where s.Location == "Chennai"
      select s;
   if (delLoc.Count() > 0)
   {
        db.tbl_Student.DeleteOnSubmit(delLoc.First());
        db.SubmitChanges();
   }