Data Binding And CRUD Operations In ASP.NET WebDataGrid

Introduction

The WebDataGrid is a flexible and feature-rich ASP.NET control available by Infragistics. The WebDataGrid is a part of Ultimate UI for ASP.NET, a library of ASP.NET controls. The control uses ASP.NET to eliminate the post back process. One of the core features of the control is its built-in out-of-box functionality that simply requires a few simple settings to provide a fully-functional, rich data-driven Web application. The code is also fully optimized and incorporates lightweight markups for performance reasons.

In this article, we will learn the following

Introduction to data binding in a WebDataGrid
Implemention of paging, filter, and sorting functionality
Auto CRUD database operations
Export records to Excel
Implement virtual scrolling
Multiple column fixing

Introduction to WebDataGrid

Infragistics’s product, Ultimate UI for ASP.NET provides rich, fast, modern controls for ASP.NET Web development. The Ultimate UI for ASP.NET consists of dozens of popular ASP.NET controls. Some of the popular controls provide functionality related to charting, data grid, Excel and Word conversion, calendar, date picker, HTML editor, rating, tree, gauge, dialogs, and several other controls.

Once downloaded and installed, the controls are accessible via Visual Studio Toolbox, like any other ASP.NET control. WebDataGrid is one of these controls that provides data grid functionality. Some of the built-in functionality of WebDataGrid control are data binding, CRUD operations, filter, sort, and search.

Data Binding to WebDataGridView

Let’s create a simple ASP.NET Web application and use a WebDataGrid control for data display and data manipulation.

Open Visual Studio and create a new project.

Click on File -> New -> Website.

Select ASP.NET Empty Web Site and give a meaningful name and click OK. See Figure 1.

Figure 1

This action creates an empty ASP.NET Website project, in which we can add functionality to build a complete data-driven web application.

Right click on the Solution name in Solution Explorer, select Add and then select Web From menu item. See Figure 2.

Figure 2

On the next screen, you’ll be asked to provide a Web Form name. After you enter a Web Form name and click OK, you’ll see a blank web form with blank body. Click on design tab to switch to the designer. See Figure 3.

Figure 3

Now open Toolbox (shortcut - Ctl + Alt + x) and search for WebScriptManager. Select and drag WebScriptManager to the form. See Figure 4.

Figure 4

Click on Table -> Insert Table. The Insert Table popup windows requires you to specify data. We just need a couple of records that are added via rows and columns. The first option is to provide, how many rows and columns a table will have. We’re going to add one row with two columns.

On this screen, you may also set other properties of the table such as layout, border, and background. Hit OK when done. See Figure 5.

Figure 5

Select the first column of table and Search for WebExplorerBar in Toolbox. See Figure 6.

Figure 6

Click the arrow on the right corner of WebExplorerBar. It opens a window where click on “Edit Group and Items” opens a new popup, which is blank. Click “add root” then select root and click on “add child”. By using this, you can add as many as items to the static navigation header. You can also add root and child items dynamically in your code.

You can change the Group and child name by changing the text property of each and after adding required root and their child click OK. There are several other properties of root and child which can be set to make the UI look more interactive such as image, tool tip, CSS etc. Once done, select Apply. See Figure 7.

Figure 7

Now let’s add a WebDataGrid control and set its data source.

Add a new aspx page and call it Emplyee.aspx. See Figure 8.

On this page, drag a WebDataGrid control and a WebScriptManager from Toolbox to the designer. Select WebDataGrid, click on the arrow in the top right, and click on Choose Data Source drop down and from the drop down, select <New data source>.

Figure 8

This action will help us create a data source. In our case, we will select a SQL Server database as our data source.

Clicking on “New Connection” will open a dialog box. First enter the MS-SQL Server name (“.” can be used for localhost) if your database is on your local machine. You will also have to provide your SQL Server user id and password. Once selected and authenticated, you will see a list of databases available on the server will be loaded in the Connect to a database drop down. See Figure 9.

Figure 9

Now, select a database where you want your data to be loaded in a WebDataGrid control. In our case, we’re using a database named EmployeeDB. Hit OK.

On the next screen, select your database connection. If you’ve created multiple connections previously, you may see them in this list also. Select your connection and hit Next. See Figure 10.

Figure 10

Next the wizard will ask you to save the connection string to the application Web config file. There is a default name given but you can change it to any name you like. In our case, I named it EmployeeDBConnectionString. See Figure 11.

Figure 11

Selecting Next will save the EmployeeDBConnectionString to the web config file.

On the next screen, the wizard will ask you to select a database table where data will be selected. On this screen, you may also specify what table columns you would like to select in your SQL query. You can use the ‘*’ option to select all. See Figure 12.

Figure 12

At the bottom of the screen, you can see there is a SELECT statement created based on the selections I’ve made on this screen. You can also add WHERE and ORDER BY clause on this screen. Once you done, hit Next.

On this screen, you can see all selected data loaded in a grid. You can also hit the Test Query button to test the database connection. See Figure 13.

Figure 13

Now, hit Finish to finish the data source selection process.

When you click Finish button, a message box will appear to confirm to regenerate WebDataGrid columns. Select Yes. See Figure 14.

Figure 14

Now let’s open the source code of Home Webform and “iframe” HTML tag to second column. I set the height and width of the column. You may want to set it according to your need. The employee data grid will be bind in this iframe. See Figure 15.

Figure 15

Code Snippet

<ig:WebScriptManager ID="WebScriptManager1" runat="server">
</ig:WebScriptManager>
<br />
<table>
<tr>
<td>
<ig:WebExplorerBar ID="WebExplorerBar1" runat="server" GroupContentsHeight="" Width="250px" Height="468px">
<Groups>
<ig:ExplorerBarGroup GroupContentsHeight="" Text="Employee">
<Items>
<ig:ExplorerBarItem Text="Parmanent" NavigateUrl="~/Employee.aspx" Target="EmployeeIFrame">
</ig:ExplorerBarItem>
<ig:ExplorerBarItem Text="Temparory">
</ig:ExplorerBarItem>
</Items>
</ig:ExplorerBarGroup>
<ig:ExplorerBarGroup GroupContentsHeight="" Text="Sales">
<Items>
<ig:ExplorerBarItem Text="1st Qtr">
</ig:ExplorerBarItem>
<ig:ExplorerBarItem Text="2nd Qtr">
</ig:ExplorerBarItem>
</Items>
</ig:ExplorerBarGroup>
</Groups>
</ig:WebExplorerBar>
</td>
<td>
<iframe name="EmployeeIFrame" class="auto-style2"></iframe>
</td>
</tr>
</table>

After adding Iframe, go to design view and click on the arrow and select “Edit Group and Item” then select the child node on which you want to load data (“Permanent” in this case). We need to set two properties to bind the data, Navigate Url and Target. In navigate url select the Employee WebForm and in Target give the iframe name.

Now you are all set to load data. Click on F5 to run the app and you’ll see the Navigation pane and after clicking on Permanent, your data will load in iframe. See Figure 16.

Figure 16

The final web page looks like Figure 17. When you click on Employee -> Permanent, you will see the data is loaded in the table.

Figure 17

Paging, Filtering and Sorting in Infragistics WebDataGrid

Paging, filtering, and sorting are some common operations for a data-drive applications. We just finished loading data from a SQL Server into a WebDataGrid. Now let’s see how we can implement paging, filtering, and sorting functionality in the WebDataGrid records. The good news is, the WebDataGrid control comes with built-in paging, filtering, and sorting functionality. All we need to do is set a few properties and we’ll be all set.

Step 1

Open Employee WebForm by double clicking on it in the Solution Explorer. Now, click on the upper right corner WebDataGrid tasks arrow and then select Edit Behaviors. See Figure 18.

Figure 18

Step 2

On the Edit Behaviors screen, we can alter control’s behavior including paging, filtering, and sorting. On this screen, check Filtering, Paging, and Sorting check boxes to enable these behaviors. Run the application and you’ll see the paging (bottom right corner of WebDataGrid) and filtering row is enabled in all columns. Sorting is also enabled by just clicking on the header of each column. Refer to Figure 19.

Figure 19

All these three options -- paging, filtering, and sorting -- also have advanced properties.

Paging options include:

Page number CSS can be written.
Appearance of page number can be changed (Top or bottom).
Paging mode is available for Friendly UI (Numeric, Next and Previous, Next, Previous, First and Last, Numeric, First and Last).
Page Index and Page Size can be changed according to your need.

Filtering options include

You can write custom CSS for Filter Row.
Dropdown CSS of filter can be written including Hover Effect.
Filter type can be changed to Excel type filter.
Inbuilt Filter dropdown Animation (I.e Animation duration, Type of animation).
Can Enable/Disable Case-Sensitive filtering.

Sorting options include

Ascending and Descending image can be set which will appear in Header section.
You can Switch to Multi-Filtering mode which enables you to filter data on multiple columns basis.

Add, Update, and Delete Data In WebDataGrid

Adding new records, and updating and deleting existing records are very common operations in a DataGrid. Now, let’s see how we can achieve that by merely using a few clicks.

First select the WebDataGrid and click the arrow, select Edit behaviors then Check “Editing Core”, “Cell Editing”, “Row Editing”, “Row Adding” and “Row Deleting”. See Figure 20.

Figure 20

Now we need to make some changes to the SqlDataSource. Go to the Employee WebForm and click on Data Source -> Configure Data Source. See Figure 21.

Figure 21

Select current Database connection string and click “Next”. On the Next screen, select Advanced tab and select “Generate INSERT, UPDATE and DELETE statement”. This will auto generate Insert, update, delete statement for you to use. Click on finish and Refresh Schema. See Figure 22.

Note
Be sure you have primary column in that table.

Figure 22

Select the WebDataGrid and Press F4 to open property tab. Go to Data -> DataKeyFields and select your primary. This DataKeyFields will be now be used to update and delete a particular record.
The last thing we need to do is, add a button on the form and put that in update panel so it’ll use partial postback. The reason we put a button is the update in WebGridView is “DEFERRED” update. This mean you have to explicitly send a command to update Grid item to Database. See Figure 23.

Figure 23

Code Snippet

<asp:UpdatePanel runat="server">
<ContentTemplate>
<asp:Button ID="SaveBtn" runat="server" Text="Save Changes" />
</ContentTemplate>
</asp:UpdatePanel>

Run the application and double click on the last column. It will turn into a textbox so that you can put the values in it. After entering all desired values hit “Enter”. See Figure 24.

Figure 24

Choose any row and click on the first column where arrow shows. This will select the entire row and hit “Delete” from keyboard. See Figure 25.

Figure 25

Select any row and double click on any column of that row. This will make the row editable. When you are finished making changes, click the Done button. See Figure 26.
Figure 26

As you can see “Mark’s” has been deleted and new record named by “Sonia” has been added and “Ben” name changed to “Ben 123”. See Figure 27.
Figure 27

So, we just saw how easy it is to implement insert, update, and delete records using a WebDataGrid control.

Export Grid View Data to Excel

We often require data from a DataGrid control to be export to an Excel sheet. The WebDataGrid data can be exported to an Excel sheet by writing a few lines of code.

Open Employee Webform and add the following controls to the Form by dragging them from the Toolbox.

Select and drag the WebExcelExporter from toolbox. Give it a meaningful name.
Show some text before downloading where the user can give a download file name.
User clicks this button to generate the Excel file.

Step 1

You can setup these controls to your Form below the WebDataGrid control or wherever you like. My final code looks like Figure 28.

Figure 28

Code Snippet

<ig:WebExcelExporter ID="WebExcelExporter1" runat="server" DownloadName="DownDataGrid">
</ig:WebExcelExporter>
<br />
<asp:Label ID="Label1" runat="server" Text="Download this data and save as"></asp:Label>
<asp:TextBox ID="txtSaveAs" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
<br />

Step 2

Now, let’s write the following code in Listing 29 on the button click event handler.

Figure 29

Code Snippet

protected void Button1_Click(object sender, EventArgs e)
{
string fileName = HttpUtility.UrlEncode(txtSaveAs.Text);
fileName = fileName.Replace("+", "%20");
fileName = HttpUtility.UrlDecode(fileName);
this.WebExcelExporter1.DownloadName = fileName;
this.WebExcelExporter1.DataExportMode = DataExportMode.DataInGridOnly;
this.WebExcelExporter1.WorkbookFormat = Infragistics.Documents.Excel.WorkbookFormat.Excel2007;
this.WebExcelExporter1.Export(true, WebDataGrid1);
}

The code is pretty simple. We create a file name, and set WebExcelEXporter control’s properties to Excel and call its Export method.

Step 3

Now, just run it and click the button and the data will be downloaded in an Excel file. The final form looks like Figure 30.

Figure 30

The Excel file look Figure 31.

Figure 31

Virtual Scrolling

A WebDataGrid control bound to a database with millions of records may affect performance of a web page. Virtual scrolling feature of the WebDataGrid control helps load partial data to keep a web page's load time lower and data loading time faster.

To enable Virtual Scrolling, open Web Form and go to Edit Behaviors and select Virtual Scrolling. Change the Row Cache Factor according to your need (2 in this sample). The Row Cache Factor is a multiplier used to determine the total number of fetched rows per request. Let’s Uncheck Paging for now to test Virtual Scrolling. See Figure 31.

Figure 31

Build and Run your project and scroll down in the Grid data, you will notice the data is loaded much faster than bulk loading of data. See Figure 32.

Figure 32.

Multiple Column Fixing

This is another feature of WebDataGrid that allows you to fix one or more columns of the DataGrid when you can’t resize of move them.

Open Employee WebForm and click on the arrow, select Edit Behavior and Check Column fixing. This will ask you to set grid width and default width of all columns. In order to achieve column fixing this needs to be done. See Figure 33.

Figure 33

Now, when you run your application, you will notice the pin button appears in all column headers. Click on this pin button to fix/unfix the column header. You can fix multiple columns as you need. You can also change the position of fixed columns (Left to Right) by going to the Column Fixing Tab Property of the Grid. See Figure 34.

Figure 34

Summary

A WebDataGrid control is a rich data-bound control that provides data binding, insert, update, delete records, sorting, filtering, and paging and many more features. In this article, we saw how to load data in a WebDataGrid control from a SQL Server database. We also saw how to add, update, and delete data. We also implemented paging, filtering, and sorting on data.

To learn more about the WebDataGrid control, check out Ultimate UI for ASP.NET.