Jul 28 2007

Direct SQL Execution in DLinq

Category: DLinq | LinqBil@l @ 17:23

A very nice feature I find is to be able to execute a custom SQL statement in case you find the functionalities provided by the DataContext object is limited.

There are two main methods:

ExecuteQuery, which has two flavors of the ExecuteQuery method

1- Without parameters
 IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
  @"select c1.custid as CustomerID, c2.custName as ContactName
   from customer1 as c1, customer2 as c2
   where c1.custid = c2.custid"
 );

We are executing a T-SQL statement by passing the query to the ExecuteQuery method. The return type if a collection of Customer classes.

2- With Parameters
 IEnumerable<Customer> results = db.ExecuteQuery<Customer>(
  "select contactname from customers where city = {0}",
  "London"
 );

As you can see above, we are passing the City parameter to get a parameterized query.

There is important note here which is that, ExecuteQuery is capable of materializing the results into a collection of objects as long as the returned columns match the corresponding
entity class' columns.

Also, you can execute a command against the database using the ExecuteCommand as follows:

    db.ExecuteCommand("UPDATE Products SET UnitPrice = UnitPrice + 1.00");

In the above command, we are updating the UnitPrice of the Products table.


Hope this helps,
Regards

 

Tags: ,

Comments are closed