GridView Edit, Update, Delete, Paging, Sorting Using DLINQ

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.

Introduction:

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();

PopulateProducts();

}

The delete feature is not as straight forward as the update functionality. The reason is that there is a relationship between the Products table and the Order Details table in the database level and the object level. If I delete a single product then all the order details records containing that product should be deleted.

So, first I iterate through the Order Detail collection depending on the productID and then I delete each Order Details item from the database. When all Order Detail items are deleted I delete the product.

In my opinion DLINQ should provide a way to cascade the delete functionality so that when a parent object is deleted all the child elements are also deleted automatically.

GridView Paging:

Paging is a very important feature of any web application. Although GridView provides the basic paging functionality but advance paging should be handled with care. In this section I am going to show you how to implement a “Custom Paging” feature for the GridView control. This means that instead of fetching all the records from the database we will only fetch the number of records that are required at a particular time.

Another thing I like to mention is that I will not be using the default “AllowPaging = true” attribute for GridView paging. AllowPaging generates the page numbers or the previous next buttons automatically but, it must know the collection before hand. I am going to use the FooterTemplate of the GridView control to display my custom “Next” and “Previous” link buttons.

<asp:LinkButton ID="lbPrevious" Visible='<%# DoesPreviousPageExists() %>' runat="server" CommandName="Previous" Text="Previous" />

<asp:LinkButton ID="lbNext" Visible='<%# DoesNextPageExists() %>' runat="server" CommandName="Next" Text="Next" />

</FooterTemplate>

Let’s check out the code for the NextPage method which fetches the 10 new items from the database.

private void NextPage()

{

NorthwindRepository repository = new NorthwindRepository();

PageIndex++;

int startIndex = PageIndex * 10;

gvProducts.DataSource = (from p in repository.GetProductsTable()

select new { ProductID = p.ProductID, ProductName = p.ProductName }).Skip(startIndex).Take(NO_OF_RECORDS).OrderBy(p => p.ProductName);

gvProducts.DataBind();

}

Let’s take the NextPage method part by part. First I am creating a new instance of the NorthwindRepository. Then I increment the PageIndex property which is defined as follows:

private int PageIndex

{

get

{

return Convert.ToInt32(ViewState["PageIndex"]);

}

set

{

ViewState["PageIndex"] = value;

}

The PageIndex keeps track of the page number of the GridView control. The startIndex represents the new index of the records to fetch. The heart of the paging lies in the following LINQ query.

(from p in repository.GetProductsTable()

select new { ProductID = p.ProductID, ProductName = p.ProductName }).Skip(startIndex).Take(NO_OF_RECORDS).OrderBy(p => p.ProductName);

First, I am getting the Product Table using the repository. I used the Skip and the Take methods to filter the objects from the Table collection. The NO_OF_RECORDS denote the maximum number of objects to fetch from the collection.

Another problem that we need to solve is to display the “Next” and the “Previous” buttons when required. This means when we see the first 10 items then we should not show the “Previous” buttons. Same way when we display the last 10 items then we should not display the “Next” button. This is achieved by the DoesNextPageExists and the DoesPreviousPageExists methods which are called in the GridView FooterTemplate.

protected bool DoesPreviousPageExists()

{

return PageIndex > 0;

}

protected bool DoesNextPageExists()

{

NorthwindRepository repository = new NorthwindRepository();

return PageIndex < (repository.GetProductsTable().Count() / 10);

}

GridView Custom Sorting:

Although the code provided with the article includes the code for custom generic sorting but I recommend that you read my article Sorting GridView Using IComparer.

Conclusion:

In this article I demonstrated how to perform GridView commonly used functionality using LINQ and DLINQ features. There are lots of other features that you can implement using the power of LINQ and DLINQ. I hope to cover many features in the later articles.

I hope you liked the happy coding!