Friday, May 18, 2012

LINQ to SQL – UPDATE


LINQ to SQL – UPDATE

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.


UPDATE is a regular database operation, involved in any Transaction processing system.
Assume we have the following data in the Employee and Department Tables

Table: Employee
ID
NAME
DOB
DOJ
DepartmentID
Salery
1
Tom
11/11/1967
05/01/2009
1
5000
2
Harry
10/07/1973
05/01/2009
2
6000
3
Peter
01/01/1982
01/01/2002
3
6500
4
John
05/04/1981
05/01/2009
3
7500
5
Charles
12/12/1973
05/01/2009
NULL
6500
6
Robert
10/07/1973
05/01/2009
4
5500

Table: Department
ID
NAME
1
Finance
2
Human Resources
3
IT
4
Sales
5
Marketing

The SQL query to update data in the Employees table will be as follows.

UPDATE Employee SET DOB = ‘01/01/1981, DOJ = ‘01/01/2001’ WHERE ID = 3

Now let us see how the equivalent UPDATE query in LINQ Looks like.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

// Select the Employee whoose details are to be Updated
Employee objEmp = (Employee)(from e in dbContext.Employees where e.ID.Equals(3) select e).First();

// Update the DOB & DOJ of the Employee
objEmp.DOB = "01/01/1981";
objEmp.DOJ = "01/01/2001";

           
//Submit the changes to the Database
dbContext.SubmitChanges();

After this code is executed you can find the details of the employee are updated in the Employees table, as follows.
ID
NAME
DOB
DOJ
DepartmentID
Salery
1
Tom
11/11/1967
05/01/2009
1
5000
2
Harry
10/07/1973
05/01/2009
2
6000
3
Peter
01/01/1981
01/01/2001
3
6500
4
John
05/04/1981
05/01/2009
3
7500
5
Charles
12/12/1973
05/01/2009
NULL
6500
6
Robert
10/07/1973
05/01/2009
4
5500


Search Flipkart Products:
Flipkart.com

1 comment:

Anonymous said...

Hey thanks for such a nice blog.
Can you please tell me how can I write update query for Temporary table in LINQ. I am trying to convert SQL query to LINQ, my sql query is like below..
UPDATE @tempTable1
SET someColumn = 1
FROM @tempTable1 p, @tempTable2 t2, NonTempTable nt
WHERE t1.id = t2.id
AND t1.id = nt.id
AND nt.status = 'abcd'