Friday, May 18, 2012

LINQ to SQL – Using ExecuteCommand() to Execute SQL Statements


LINQ to SQL – Using ExecuteCommand() to Execute SQL Statements

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.


ExecuteCommand, can be used when we need to execute commands directly in the database. This method can be used to execute plain SQL statements directly on the database, we can execute the conventional SQL statements using this method. The syntax to use this method is as follows.

public int ExecuteCommand
(
            string sqlCommand,
            params Object[] Parameters
)

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

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

The SQL query to delete data from the Employees table will be as follows.

DELETE Employee WHERE ID = 5

Now let us see, on how to execute this SQL statement using the ExecuteCommand() method .

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();
//
dbContext.ExecuteCommand("DELETE Employee WHERE ID = {0}", 5);
That’s it, after this code is executed you can find the details of the employee is removed from 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


Search Flipkart Products:
Flipkart.com

No comments: