Lets look at a brief code by code comparison of how to perform different data access operations using the Linq to Sql framework compared to using the Orasis Generated data access layer from a mapping project.

Download the NorthwindDataAccess project file and open it with the Orasis Mapping Studio. Edit the database connection string by right clicking on the Database node and changing the connection properties to point to your Northwind database.

The Linq to SQL Context is setup by Connecting to the Northwind database and adding the Product table to the dbml designer in Visual Studio. We will name our context NorthwindDataContext.

Let us see some examples of data access operations for the Products table from Linq to Sql and from the Data Access Code generated by the Orasis Mapping Studio.

Selecting a Product from the Northwind Database:

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext(); 
var products = from p in db.Products where p.ProductId = 1
               select p; 

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 
IList<Product> productsList = nwDal.GetProductbyId(1);

The SQL query in the GetProductbyID mapping is :

Select * From Products
	Where Products.ProductID = @productId

The query was written and mapped to the Product object using the Mapping Studio’s mapping wizard.

The result is strongly typed, and the parameters are clearly defined. Also, the caller of the Data Access Library does not need to see the query, he just calls the method which gets him the Objects.

Updating a Product:

The code below shows both approaches of how to retrieve a product by its ID and update its Name and Unit Price.

Linq to SQL: (C#);

NorthwindDataContext nwContext = new NorthwindDataContext ();
Product product = nwContext.Products.Single(p => p.ProductId == 1);

product.ProductName = "New Name";
product.UnitPrice = 23;

nwContext.SubmitChanges();

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 
Product product = nwDal.GetProductbyId(1)[0]; 

product.Name = "New Name"; 
product.UnitPrice = 23; 

int rowsAffected = nwDal.UpdateProducts(new List<Product>{ product });

The query inside the UpdateProducts method is:

Update dbo.Products set ProductName = @ProductName,
UnitPrice = @UnitPrice
where ProductID = @ProductId

Inserting a Product

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext (); 

Product product1 = new Product();
product1.ProductName = "New Product1";
product1.UnitPrice = 35;
product.Category = 1; 

nwContext.Products.Add(product1);
nwContext.SubmitChanges();

Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

Product product = new Product(); 
product.ProductName = "New Product";
product.UnitPrice = 23; 
product.Category = 1;

int rowsAffected = nwDal.InsertProducts(new List<Product>{ product });

The insert Query inside the mapping is :

Insert into dbo.Products(ProductName,UnitPrice,CategoryID) values (@ProductName,@UnitPrice,@CategoryID)

Deleting a Product:

In the code below we will delete a product with the name “New Product”.

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext ();

Product productToDelete = nwContext.Products.Single(p => p.ProductName == "New Product");
nwContext.Products.DeleteOnSubmit(productToDelete );                

nwContext.SubmitChanges();

Calling the Orasis Generated Data Access Layer:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

Product product = new Product(); 
product.ProductName = "New Product"; 

int rowsAffected = nwDal.DeleteProductByName(new List<Product>{ product }); 

The Query inside the DeleteProductByName Mapping is :

Delete from dbo.Products where Products.ProductName = @ProductName

Calling a Stored Procedure

The code below demonstrates calling a stored procedure that we created in the Database to return products by Category called ‘GetProductsByCategory’.

Linq to SQL: (C#)

NorthwindDataContext nwContext = new NorthwindDataContext ();

var products = nwContext.GetProductsByCategory(1);

foreach (Product product in products)
{
	System.Console.WriteLine("Product Name:" + product.ProductName);
}

Orasis Generated Data Access Layer with a Mapping to the same Stored Procedure:

NorthwindDataAccess nwDal = new NorthwindDataAccess(); 

IList<Product> products = mdDal.GetProductsByCategory(1);

foreach (Product product in products) 
{ 
	System.Console.WriteLine("Product Name:" + product.ProductName); 
}

This concludes our side by side code comparison of accessing our Northwind database through Linq to SQL, compared to Mapping the Database through the Orasis Mapping Studio’s mappings. The end results are same for accessing the database through Objects.