LINQ 2 SQL

Linq has been introduced with the release of .Net 3.5 . Its has changed the way how we code against various data sources. It comes with full type-safety, intellisense support, compile-time checking and various other tools like visualizers for expression trees, orm's etc. Linq is fully extensible , beside the datasources that Microsoft lets you to query with linq , linq has many extended versions like Linq to Amazon, Linq to Google etc.

Linq to Xml, Linq to Objects, Linq to Sql, Linq to Ado.Net, Linq to Entities are some of the example of Linq implementation. Linq query are supported by the collections that implement's IEnumerable and IQueryable interfaces. IEnumerable is basically used to work with in-memory data collections like Linq to Objects, Linq to Sql etc.

IQueryable is used where we want to run a query against a data provider.Here we can pass a structure of query expression to data provider over the network which may in turn convert it into Sql or any other language to execute. Example's are Linq to Sql , Linq to Entities.

In this blog I will be focusing on the Linq to Sql and Linq to DataSet. Linq to Sql in Visual Studio is an ORM .It models the relational database and creates the classes based on that.These are the classes that you will be going to run your query against.All the transactions going between the actual database and your modeled classes are wrapped, so you could simply execute and query against these classes. To create Linq to Sql project we have to:

• Create a new Project of a type that you want to implement Linq to Sql into.


• Open the Database that you want to model in the Server explorer window.


• Select Add New Item to open New Item Dialog box. Select linq to Sql classes from dialog.


• Name the class and select ok. A dbml file with three other file will be created in solution explorer.


• A ORM designer will be now opened in Visual Studio.Drag and Drop the tables from database opened in the Server Explorer to the ORM designer.

This is the basic setup for the creation of a Linq to Sql project. Lets see the files created along with .dbml file , they are .Designer.cs , .dbml.layout, .dbml.diagram. Out of these files .Designer.cs files which is a partial class, is most important to us. This is the file that contains the modeled relational database, we can see the main class that inherits the System.Data.Linq.DataContext class and other classes that maps to our tables in the database .These mapped classes implements INotifyPropertyChanged and INotifyPropertyChanging interface for property change tracking. WPF developers will be quite familiar will property changed tracking mechanism.

Lets see how to use these classes for our transaction with DB.
We can query databases for the records with all kind of filters and aggregations .The method here queries the database for the list of customers who ordered a particular item.

public IEnumerable GetCustomerByOrderId(int itemsId)
{
using (DataClasses1DataContext cntx = new DataClasses1DataContext())
{
IEnumerable customerCollection = from cust in cntx.Customers
from ord in cntx.Items
where cust.CustiId == ord.Items
_CustId && ord.Items_OrderNo == itemsId
select cust;
return customerCollection;
}
return null;
}

We can insert records in the database.Here we insert a single customer record in Customers table.

public void AddCustomers(Customer customer)
{
using(DataClasses1DataContext cntx = new DataClasses1DataContext())
{
cntx.Customers.InsertOnSubmit(customer);
cntx.SubmitChanges();
}
}

Delete the record from database .Here we delete the record from the database.

public void DeleteCustomer(int custId)
{
using (DataClasses1DataContext cntx = new DataClasses1DataContext())
{
Customer objCustomer = (from cus in cntx.Customers
where cus.CustiId == custId
select cus).SingleOrDefault();

//We can use Lambda
//objCustomer = cntx.Customers.Where(c => c.CustiId ==
//custId).SingleOrDefault();

cntx.Customers.DeleteOnSubmit(objCustomer);
}
}

Update the database. Here we update the customers table.

public void UpdateCustomer(Customer customer)
{
using (DataClasses1DataContext cntx = new DataClasses1DataContext())
{
Customer objCustomer = (from cus in cntx.Customers
where cus.CustiId == customer.CustiId
select cus).SingleOrDefault();

// We can use Lambda
// objCustomer = cntx.Customers.Where(c => c.CustiId ==
// custId).SingleOrDefault();

//Update Address and the City
objCustomer.CustAddress = customer.CustAddress;
objCustomer.CustCity = customer.CustCity;
cntx.SubmitChanges();
}

}

Besides these all, we can directly execute a command or a query against the database thanks to the ExecuteCommand and ExecuteQuery methods of DataContext class. We can simply pass string command and parameters to these methods.By default the query-string is Sql-Injection proof as it uses the exec_sql on server.

using (DataClasses1DataContext cntx = new DataClasses1DataContext())
{
cntx.ExecuteQuery("Select * from Customers where custId = '{0}'","11");
cntx.ExecuteCommand("Update Customers set CustName = '{0}' where CustId = '{1}'", "MyName","22");
}

In the next post we will look into Linq to DataSet.Happy coding.