Goodbye Stored procedures, It’s the time to move on "Linq to Sql"

ORM Vs Stored Procedures. Which is the best approach?

For the last few years, many developers and architects are engage in a series of debates about O/RM Vs Stored Procedures. Many of them argue for ORM and others are argue for stored procedures. The interesting thing is that people with highly object orientation sense, recommends ORM. The J2EE community strongly recommending the ORM approaches instead of using stored procedures. Some .NET developers coming from Visual Basic 6.0 background supports stored procedures. Hibernate and NHibernate (.NET version of java version Hibernate) are the highly successful ORM that using both .NET and J2EE community. Then which is the best approach for data persist? Personally I hate stored procedures and strongly recommend for ORM instead of using the legacy stored procedure programming.

Why I hate stored procedures?

Stored Procedures are written by DB languages such as PL/SQL and T-SQL and this type of languages are not designed for writing business logic and debugging process is really a nightmare. And stored procedures hide the business logic and lacks readability of business process. If you are going to port DB from one RDBMS to another one, you have to re-write your all stored procedures. If you want to run your product on multiple databases, the ORM is the right approach. And ultimately the stored procedure restricts the proper business abstraction. Many people argue that stored procedure provides better performance than dynamic generated Sql from an ORM and people believed that all stored procedure are pre-compiled. According to Microsoft Sql Server documentation, Sql Server does cache the execution plan for stored procedure instead of pre-compiled. Have a look at the MSDN article Execution Plan Caching and Reuse. And I believe that maintainability, scalability and proper abstraction are the key factors of enterprise applications. The ORM approach enables these benefits. If you are a .NET developer, there is happy news for you. A new ORM named Linq to Sql coming from the Redmond campus along with the .Net framework 3.5.

What is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) of the .NET Framework 3.5. It provides you to model a relational database using .NET classes. You can then query the database using LINQ, as well as insert, update and delete data from it. LINQ to SQL supports all types of database objects such as views, and stored procedures and also transactions. It also provides an easy way to integrate data validation and business logic rules into your data model. Visual Studio 2008 provides a Linq to SQL designer that enables to model and visualize a database as a LINQ to SQL object model. You can create the all database representations using the Linq to SQL designer. With the Linq to SQL designer, you can drag and drop the tables into the Linq to SQL designer surface and can represent the relations between tables. Linq to SQL allows you to model classes that map to tables within a database. These classes are known as "Entity Classes" and instances of them are called "Entities". Like other OR/Ms, the Linq to SQL OR/Ms will generate the SQL statements at the runtime when interacting with the Entity Classes.

Lets look at the below business object that mapped the customers table using Linq to Sql.

[Table(Name="Customers")]
public class Customer{
[Column(Id=true)]
public string CustomerID;[Column]
public string CustomerName;[Column]
public string City;[Column]
public string Phone;
}

After modeling the Database with Linq to Sql, we can do all DB operations against it.
The below code is the query against Customer object that represents the Customer table.

DataContext db = new DataContext();
var q = from c in db.Customer where c.City == "Cochin" select c;

In the above query will select customers of city Cochin .The DataContext represent an abstraction of your database.The below code is update existing customer

DataClassesDataContext db=new DataClassesDataContext();
Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);
cust.Phone="919847059589";
db.SubmitChanges();

The below code is add new customer
DataClassesDataContext db=new DataClassesDataContext();
Customer cust=new Customer();
cust.CustomerName=”ABC Ltd”;
cust.City=”Mumbai”;
cust.Phone=”919847059589”;

db.Customer.Add(cust);
db.SubmitChanges();
The below code is delete customer

DataClassesDataContext db=new DataClassesDataContext();
Customer cust=db.Customer.Single(c=> c.CustomerID ==”C101”);
db.Customer.Remove(cust);

The below code is using a join query

var orders =from o in db.Ordersjoin c in db.Customer on o.CustomerID equals c.CustomerID where c.CustomerID == "C101"select new {c.CustomerName, o.ShipName, o.ShipAddress };

Linq to Sql is an exciting ORM tool from Microsoft and I hope that people will use this ORM along with .NET 3.5 applications.