Performing CRUD and Grouping Operations with the LinqDataSource Control

Performing CRUD and Grouping Operations with the LinqDataSource Control

n a previous DevX article, you saw the basic process for using the LinqDataSource control to retrieve and display data. That article discussed functionality such as passing parameters to a LINQ query and executing a stored procedure. This article builds on that foundation by discussing more sophisticated features for creating fully-editable pages, such as adding Create, Read, Update, and Delete (CRUD) capabilities, and handling CRUD events generated by the LinqDataSource control. In addition, this installment also demonstrates how to aggregate and group data using the LinqDataSource control.

CRUD with LinqDataSource
To begin, create a new Visual C# web site named LinqDataSourceExample. After creating the web site, select Website ? Add New Item from the menu. In the Add New Item dialog box, select the “LINQ to SQL Classes” template, and name it AdventureWorksDatabase.dbml. You’ll see the Object Relational Designer view of the newly created class. From that view, select View ? Server Explorer from the menu, and add a new data connection to the AdventureWorks database. Finally, to create the corresponding entity classes, drag and drop the Production.ProductCategory, Production.ProductSubcategory, and Production.Product tables onto the Object Relational Designer from the Tables node in Server Explorer.

The next step is to create an ASP.NET page that uses the LinqDataSource control to create CRUD capabilities on top of the Product table in the AdventureWorks database. Create a new ASP.NET page named CRUD_Example.aspx and modify its code as follows:

   <%@ Page Language="C#" %>          Using LinqDataSource Control for Create, Read, Update and      Delete Operations          

The LinqDataSource control in the preceding code has three properties: EnableInsert, EnableUpdate, and EnableDelete. All three are set to true to enable automatic insert, update, and delete support. You need to enable the same functionality at the data-bound control, DetailsView in this case. To accomplish this, you set the AutoGenerateInsertButton, AutoGenerateEditButton, and AutoGenerateDeleteButton of the DetailsView control to true. That’s all you need to do to enable CRUD operations with the LinqDataSource control.

Figure 1 shows the output produced by navigating to the page in the browser.

?
Figure 1. CRUD-Enabled LinqDataSource Control: After enabling CRUD operations for the LinqDataSource Control data bound to the DetailsView control, the Edit, Delete, and New buttons appear at the bottom of the control.
?
Figure 2. Editing Data: To enable editing, the control replaces the read-only fields with text boxes where you can enter the product details and adds an Update button that saves changes back to the database.

If you click the Edit button in Figure 1, you’ll see the output shown in Figure 2.

When you click the Update button, the LinqDataSource control automatically retrieves the values from the text boxes and invokes the appropriate methods of the Entity class to update the data in the Product table.

Handling the Updating Event
So, it’s easy to set up a LinqDataSource control to update data automatically, but you probably want to pre-process the data before submitting it to the database. You can accomplish this by handling the LinqDataSource control’s Updating event. The code example shown below checks the user-entered data to ensure its validity:

   <%@ Page Language="C#" %>             Using LinqDataSource to handle Update event          

The code sets the LinqDataSource control’s OnUpdating attribute to the name of the event handler. This event handler (named productSource_Updating) gets a LinqDataSourceUpdateEventArgs argument that contains various details about the updating event, as shown in Table 1.

Table 1. LinqDataSourceUpdateEventArgs Object: The LinqDataSourceUpdateEventArgs object exposes these properties.
Property Description
Cancel Allows you to cancel the update operation
Exception Allows you to get the generated exception before the update operation
NewObject Allows you to retrieve the data that will be saved in the data source
OriginalObject Allows you to retrieve the data that was originally retrieved from the data source

The preceding code typecasts the values of the OriginalObject and NewObject properties to the Product type. Next, it compares the values of the original and new object ProductID properties; if they differ, it cancels the update operation by setting the Cancel property to true. As the example shows, you have access to both the original and any changed data in this event before committing the changes to the database.

Handling the Deleted Event
In addition to pre-processing events such as Updating, you can also handle post-processing events with the LinqDataSource control. For example, you can display confirmation message to the user after successfully completing a Delete operation. The following page shows an example:

   <%@ Page Language="C#" %>   <%@ Import Namespace="System.Collections.Generic" %>             Using LinqDataSource to handle Deleted event          


The preceding page code handles the LinqDataSource control’s Deleted event. As the name suggests, the Deleted event fires after the delete operation completes, so you could use this event to check whether the deletion was successful and/or perform cleanup activities. Again, the control receives data about the deleted object in a special argument type, the LinqDataSourceStatusEventArgs object. Table 2 summarizes its key properties.

Table 2. LinqDataSourceStatusEventArgs Object: The LinqDataSourceStatusEventArgs object exposes these properties.
Property Description
Exception Allows you to get any exception generated during the operation
ExceptionHandled Lets you indicate whether the exception was handled so that the exception is not thrown back to the client
Result Allows you to get the object that represents the result of the operation

The preceding code first checks to see if any exception occurred during the delete operation, and if so, displays the exception message.

   if (e.Exception != null)   lblMessage.Text = e.Exception.Message;   

Next, it typecasts the e.Result value to a ProductCategory object, and displays its ProductCategoryID value on the label control (see Figure 3).

    ProductCategory category = (ProductCategory)e.Result;    lblMessage.Text += "The Deleted Product category id is: " +    category.ProductCategoryID;        
?
Figure 3. Handling the Deleted Event: By capturing the Deleted event of the LinqDataSource control, you can provide status information to users or display any exception messages generated during the deletion

Grouping and Aggregating Data
In addition to its extensible event-driven architecture, the LinqDataSource control also exposes attributes through which you can declaratively group and aggregate data. This section shows a simple example that groups all the products by category, and displays the category, the products’ average price, and the number of products in that category.

   <%@ Page Language="C#" %>          Using LinqDataSource Control to group and aggregate      data          
Product Category Average List Price Record Count
<%# Eval("Key.Name") %> <%# Eval("AverageListPrice") %> <%# Eval("RecordCount") %>

The LinqDataSource control defined toward the end of the preceding code has a new attribute, named GroupBy, which is set to the ProductSubcategory object. Having set the GroupBy property, you can then retrieve the value using the Key property, which contains an object that exposes whatever value you use to group the data?in this case, the product subcategory name.

?
Figure 4. Grouping and Aggregating Data: The LinqDataSource control lets you group and aggregate data by declaratively setting the GroupBy property.

Using the ProductSubcategory value as the Key property, you can calculate the average list price of all products that share a ProductSubcategory value. The query also returns a count of the number of records associated with each ProductSubcategory value.

   Select="new(Key, Average(ListPrice) As     AverageListPrice, Count() As RecordCount)"

Note that the aggregate functions have aliases so you can reference them from the data bound control. Here’s the snippet of code that references the previously-defined aggregate functions from the ListView control:

        <%# Eval("Key.Name") %>                          <%# Eval("AverageListPrice") %>        <%# Eval("RecordCount") %>  

Again, note that the Key.Name property contains the same value as the ProductSubcategory.Name property. Figure 4 shows the resulting page.

Using the advanced LinqDataSource control features you’ve seen here, you can easily create fully-functional editable data displays that use CRUD functionality to modify data. You can handle the CRUD events that the LinqDataSource control generates, and you can provide high-level displays such as dashboards by grouping and aggregating data. The LinqDataSource control’s extensible nature lets you customize the runtime behavior as much as you like, simplifying the process of creating rich data-driven web applications.

devx-admin

devx-admin

Share the Post:
Chinese 5G Limitation

Germany Considers Limiting Chinese 5G Tech

A recent report has put forth the possibility that Germany’s Federal Ministry of the Interior and Community may consider limiting the use of Chinese 5G

Modern Warfare

The Barak Tank is Transforming Modern Warfare

The Barak tank is a groundbreaking addition to the Israeli Defense Forces’ arsenal, significantly enhancing their combat capabilities. This AI-powered military vehicle is expected to

AI Cheating Growth

AI Plagiarism Challenges Shake Academic Integrity

As generative AI technologies like ChatGPT become increasingly prevalent among students and raise concerns about widespread cheating, prominent universities have halted their use of AI

US Commitment

US Approves Sustainable Battery Research

The US Department of Energy has revealed a $325 million commitment in the research of innovative battery types, designed to enable solar and wind power

Netanyahu Musk AI

Netanyahu and Musk Discuss AI Future

On September 22, 2023, Israeli Prime Minister Benjamin Netanyahu met with entrepreneur Elon Musk in San Francisco prior to attending the United Nations. In a

Chinese 5G Limitation

Germany Considers Limiting Chinese 5G Tech

A recent report has put forth the possibility that Germany’s Federal Ministry of the Interior and Community may consider limiting the use of Chinese 5G technology by local network providers

Modern Warfare

The Barak Tank is Transforming Modern Warfare

The Barak tank is a groundbreaking addition to the Israeli Defense Forces’ arsenal, significantly enhancing their combat capabilities. This AI-powered military vehicle is expected to transform the way modern warfare

AI Cheating Growth

AI Plagiarism Challenges Shake Academic Integrity

As generative AI technologies like ChatGPT become increasingly prevalent among students and raise concerns about widespread cheating, prominent universities have halted their use of AI detection software, such as Turnitin’s

US Commitment

US Approves Sustainable Battery Research

The US Department of Energy has revealed a $325 million commitment in the research of innovative battery types, designed to enable solar and wind power as continuous, 24-hour energy sources.

Netanyahu Musk AI

Netanyahu and Musk Discuss AI Future

On September 22, 2023, Israeli Prime Minister Benjamin Netanyahu met with entrepreneur Elon Musk in San Francisco prior to attending the United Nations. In a live-streamed discussion, Netanyahu lauded Musk

Urban Gardening

Creating Thriving Cities Through Urban Gardening

The rising popularity of urban gardening is receiving increased recognition for its numerous advantages, as demonstrated in a recent study featured in the Environmental Research Letters journal. Carried out by

What You Need to Know About Cloud Security Strategies

What You Need to Know About Cloud Security Strategies

Today, many businesses are adopting cloud computing services. As a result, it’s important to recognize that security measures for data in the cloud are different from those in traditional on-premises

Romanian Energy Security

Eastern Europe is Achieving Energy Security

Canada and Romania have solidified their commitment to energy security and independence from Russian energy exports by signing a $3-billion export development agreement. The deal is centered on constructing two

Seamless Integration

Unlocking Seamless Smart Home Integration

The vision of an intelligently organized and interconnected smart home that conserves time, energy, and resources has long been desired by many homeowners. However, this aspiration has often been hindered

New Algorithm

MicroAlgo’s Groundbreaking Algorithm

MicroAlgo Inc. has revealed the creation of a knowledge-augmented backtracking search algorithm, developed through extensive research in evolutionary computational techniques. The algorithm is designed to boost problem-solving effectiveness, precision, and

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses