Introduction:
Microsoft DLINQ is part of the .NET 3.5 framework. DLINQ allows the developer to iterate through data sources which includes SQL SERVER databases. In this article I will demonstrate how you can use DLINQ to perform editing, updating, paging, sorting and deleting operations on the GridView control.
Creating LINQ to SQL File:
The LINQ to SQL file enables you to map your database tables to the classes. Add a new LINQ to SQL file to your project and drag and drop the tables which you would like to be created. In the screenshot below you can see the three tables which are converted to the corresponding classes. Also note that the relationships from the database are also transformed to the relationships between the classes.
Design:
I am using the Products table of the Northwind database. There are also different classes involved in this project. Let’s take a look at the class diagram shown below:
Let me explain the purpose of each class.
CacheRepository: The CacheRepository simply holds the items which are cached. When fetching the items they are first checked in the CacheRepository if they are found then they are returned without accessing the database. If item is not found then a data access is initiated.
SiteConfiguration:
The SiteConfiguration class simply returns an object of the Settings class. The Settings class is used to access the database connection string.
Settings:
The Settings class holds the connection string of the database.
NorthwindRepository:
NorthwindRepository inherits from the the NorthwindDataContext class and provides the methods to persist changes to the database.
GenericComparer<T>:
The GenericComparer<T> is used to compare two fields. This is used to sort the columns of the GridView control.
Populating the GridView With Products:
Let’s first see how we can populate the GridView control with the products from the database using the NorthwindRepository GetProductsTable method.
public Table<Product> GetProductsTable()
{
NorthwindDataContext northwind = null;
// get the products from the cache object
Table<Product> products = CacheRepository.GetTable<Product>("Products");
if (products == null || products.Count() == 0)
{
// fetch the list from the database
northwind = new NorthwindDataContext(SiteConfiguration.GetSettings().NorthwindConnectionString);
products = northwind.Products;
// save the products in the cache object
CacheRepository.Save("Products", products);
}
return products;
}
The method implementation is pretty simple. I am first checking if the Table “Products” is contained inside the cache or not. If it is contained in cache then it is returned from the cache else a database fetch is required.
(Don’t pay attention to any other features yet! We will cover everything during the course of this article).
Implementing GridView Edit, Update and Delete:
Let’s start with implementing the edit and the update feature. First you need to add the ComandField buttons with will represent the edit and update link buttons.
<asp:commandfield ShowEditButton="True">
</asp:commandfield>
<asp:commandfield ShowDeleteButton="True">
</asp:commandfield>
(You can always use designer to add the edit, update and cancel buttons).
Next task is to get the GridView into edit mode. This is pretty simple and can be achieved by using the GridView_RowEditing event.
protected void gvProducts_RowEditing(object sender, GridViewEditEventArgs e)
{
gvProducts.EditIndex = e.NewEditIndex;
PopulateProducts();
}
Next, let’s check out the Update method. The code for the Update is implemented inside the GridView_RowUpdating event of the GridView control.
protected void gvProducts_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
NorthwindRepository repository = new NorthwindRepository();
int productID = (int) ((DataKey) gvProducts.DataKeys[e.RowIndex]).Value;
string productName = (gvProducts.Rows[e.RowIndex].FindControl("txtProductName") as TextBox).Text;
Product product = repository.Products.Single(p => p.ProductID == productID);
product.ProductName = productName;
// call the SaveProduct method which also removes the products from the cache
repository.SaveProduct();
// refresh the grid
gvProducts.EditIndex = -1;
PopulateProducts();
}
First I created a new instance of the NorthwindRepository. Then I get the primary key of the Product which is set as the DataKeyNames property in the GridView control. I get the edited text from the textbox. I get the Product instance from the repository using the productID. And then finally I called the SaveProduct method. You can also call SubmitChanges but in SaveProduct I am removing the Products from the application cache.
public void SaveProduct()
{
// remove the products from the cache
CacheRepository.Remove("Products");
base.SubmitChanges();
}
In the image above you can see that the product name has been updated.
Ok, let’s move on to the Delete functionality. The Delete functionality should delete the products and also the corresponding Order Details.
I am using the GridView_RowDeleting event to handle deleting functionality.
protected void gvProducts_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
NorthwindRepository repository = new NorthwindRepository();
int productID = (int)((DataKey)gvProducts.DataKeys[e.RowIndex]).Value;
Product product = repository.Products.Single(p => p.ProductID == productID);
// remove all the order details
foreach (Order_Detail orderDetail in product.Order_Details.ToList<Order_Detail>())
{
repository.Order_Details.Remove(orderDetail);
}
repository.Products.Remove(product);
repository.SaveProduct();