Building Windows Forms Data Driven App Using UltraGrid

Introduction

Infragistics provides simple and efficient controls for multiple platforms including Angular, JavaScript, ASP.NET, and Windows Forms. Datagrid is one of the controls available on the Windows Forms that makes building data driven Windows applicatins easier. Datagrid is very easy to implement and it provides a very user friendly UI for developers to work with.

In this tutorial, I will show you how to build a complete data-driven Windows Forms app using Visual Studio 2017, C#, and UltraGrid control.

The tutorial includes the following topics:

• Introduction to the UltraGrid control
• Databinding - Connect and load data from a database
• Change themes
• Filter data on various column values
• Advanced data filtering
• Grouping
• Sorting
• CRUD (Create, Read, Update, and Delete) data operations
• Print Preview
• Clickable cell
• Export data to Excel

Setting Initial Layout

The first operations we do on a data-bound control is to bind the control to a data source and load data into the control.

Open Visual Studio and create a new “Windows Froms App” project by selecting File > New > Project >Visual C# > Windows Classic Desktop > Windows Froms App (.NET Framework).

Give your project a name and click OK. My project name is IGDataGridDemo. See Figure 2.

 

Figure 1.

Now, drag and drop a “UltraLiveTileView” control to the Win Forms designer. See Figure 2.

Figure 2.

Now right click on the UltraLiveTileView control and Select “Dock” property, click Fill, so the UltraLiveTileView gets the entire view. See Figure 3 and Figure 4.

Figure 3.

Figure 4.

Now UltraLiveTileView gets the entire design surface area.

Figure 5.

Now click the “UltraLiveTileView Designer” to Add tiles on the Win forms

Figure 6.

Drag and drop the tiles on “UltraLiveTileView” as shown in Figure 7 and Figure 8.

Figure 7.

Figure 8.

To set the Group name click the group and set the text property.

Figure 9.

Figure 10.

To set the content inside the tile, click the pencil icon and go to the property window and follow the instruction in Figure 10 and 11.

Figure 10.

Figure 11.

Now make the tile clickable. For that select the tile and in the property window select the event section and go to the “Click” event and generate the event as shown in Figure 12.

Figure 12.

A click event will get generated in the code behind, add the window and write code for opening new window when clicking on the button.

private void UTVProducts_Click(object sender, EventArgs e)
{
}

 

Figure 12.

Figure 13.

Figure 13.

private void UTVProducts_Click(object sender, EventArgs e)
{
      ProductForm objProductForm = new ProductForm();
      objProductForm.Show();
}

Data Binding

Now drag and drop the UltraGrid on the window from designer surface as shown in Figure 14.

Figure 14.

Now right click on the UltraGrid control and Select “Dock” property, click Fill, so the UltraGrid gets the entire view. See Figure 14 and Figure 15. Also set the name of UltraGrid “ultraGridProducts”

Figure 14.

Figure 15.

DataSource property is used to bind the data with the “UltraGrid”. We can assign the dataset, DataTable, etc., as data source.

Figure 16.

Code Snippet

public ProductForm()
        {
            InitializeComponent();
            Infragistics.Win.AppStyling.StyleManager.Load(
               Application.StartupPath + "\\" + "Nautilus.isl");
        }
        private void PopulateProductDataGrid()
        {
            ultraGridProducts.DataSource = SqlHelper.PopulateDataGridProducts().Tables[0].DefaultView;
        }
        private void ProductForm_Load(object sender, EventArgs e)
        {
            PopulateProductDataGrid();
        }

In this example we are going to use the AdventureWorks database to bind our data control.

Figure 17.

Code Snippet

public static DataSet PopulateDataGridProducts()
      {
          using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString()))
          {
              SqlDataAdapter adap = new SqlDataAdapter("select * from Production.Product", con);
              DataSet ds = new DataSet();
              adap.Fill(ds);

              if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
              {
                  return ds;
              }
              else
                  return null;
          }
      }

Now press F5 to run the application to see the result as Figure 18.

Figure 18.

Theme

UltraGrid also support various themes, and these themes can be set to the ultragrid to change the look and feel.

When we install infragistics, Windows forms controls setup also adds themes in “Styles” directory. This directory can be accessed using the below path.

C:\Users\Public\Documents\Infragistics\2017.2\Windows Forms\AppStylist for Windows Forms\Styles

Figure 19.

Copy your favorite style and paste it in the root of the application as show in Figure 20.

Figure 20.

Now right click on the theme file and go to the property section and set the “Copy to Out Directory” to “Copy Always”.

Figure 21.

And in the constructor set the theme to windows forms, follow figure 22 and load the theme at run time.

Figure 22.

Code Snippet

public ProductForm()
     {
         InitializeComponent();
         Infragistics.Win.AppStyling.StyleManager.Load(
            Application.StartupPath + "\\" + "Nautilus.isl");
     }

Now press F5 to see the result as in Figure 23.

Figure 23.

Data Filtering

UltraGrid also supports the Filtering. To enable filtering go to the “UltraWinGrid Designer” then under the Basic Settings > Presets > Feature picker and on the right hand side you will see the “Allow” option under “Filtering” to enable this feature.

Figure 24.

Allow this option and click ok; now build and run the app to see the result as in Figure 24.

Figure 25.

As you can see in Figure 25, the UltraGrid now has combo box where we can enter value of a column to filter the DataGrid results.

Let’s select the value “Blade” in filter combobox for Name. Now, you will see all matching records are loaded where Name now contains “Blade” in them. See Figure 26.

Figure 26.

Advanced Filtering

The UltraGrid control provides some advanced filtering options. Click on the Custom to use advance filtering options. See Figure 27.

Figure 27.

You will see the advanced filtering options where you can add multiple filtering conditions. See Figure 28. We can add multiple conditions. The Add Condition button is used to add a new condition. See Figure 28.

Figure 28.

Figure 29.

In Figure 30, I add a condition where field Name starts with “ba” . New DataGrid with filtered results looks like Figure 31.

Figure 30.

Result

Figure 31.

Sorting

UltraGrid also supports the sorting and data can be sorted in ascending and descending manner. To enable sorting select and right click on UltraGrid and go to the property windows.

Figure 32.

Then under the event section generate event for “InitializeLayout” i.e. “ultraGridProducts_InitializeLayout” as shown in Figure 33.

Figure 33.

e.Layout.Override.HeaderClickAction = HeaderClickAction.SortSingle;

Inside the “ultraGridProducts_InitializeLayout” event write the above code to enable sorting as shown in Figure 34.

Figure 34.

Code Snippet

private void ultraGridProducts_InitializeLayout(object sender, InitializeLayoutEventArgs e)
      {
          // Grouping
          this.ultraGridProducts.DisplayLayout.ViewStyleBand = ViewStyleBand.OutlookGroupBy;

          // Sorting
          e.Layout.Override.HeaderClickAction = HeaderClickAction.SortSingle;
      }

Result

Figure 35.

Figure 36.

Print Grid

Another cool feature that UltraGrid supports is “Print Preview” before “Print”. To print preview add a button on WinForm as shown in Figure

Figure 37.

Generate the click event for this print preview button and write the code as shown in Figure 38.

Figure 38.

Code Snippet

private void btnPrintPreview_Click(object sender, EventArgs e)
       {
           this.ultraGridProducts.PrintPreview();
       }

Now build and run the application and click print preview button to see the data of UltraGrid as shown in the below figure:

Figure 39.

Grouping

Another UltraGrid feature we are going to discuss is “Grouping”. It also supports the grouping of data inside the ultragrid. The UltraGrid control provides a built-in grouping feature that you can use to group the grid data using one or more than one column header.

For enabling grouping, click on the group by area on the top of the column header. See Figure 40.

Figure 40.

Code Snippet

private void ultraGridProducts_InitializeLayout(object sender, InitializeLayoutEventArgs e)
       {
           // Grouping
           this.ultraGridProducts.DisplayLayout.ViewStyleBand = ViewStyleBand.OutlookGroupBy;
           // Sorting
           e.Layout.Override.HeaderClickAction = HeaderClickAction.SortSingle;
       }

Run and build the application and you will notice a new grouping section gets added just above the header section of the UltraGrid as shown in Figure 41.

Here you can drag and drop the column header to group data by the UltraGrid field name (or column name). See Figure 41.

Figure 41.

Click on the column header and drag and drop it in the grouping section above the column header section

Figure 42.

In  Figure 43 two columns are added for grouping, see the result below

Figure 43.

ClickCell Event

To make the cell clickable, generate the “ClickCell” event, parameter  of type “ClickCellEventArgs” will have the reference of clicked cell and value of the cell can be gotten from this argument as shown in Figure 44 and Figure 45.

Figure 44.

Figure 45.

Code Snippet

Private void ultraGridProducts_ClickCell(object sender, ClickCellEventArgs e)
{
   MessageBox.Show(string.format(“{0}: {1}”, e.Cell.Column.Header.Caption, e.Cell.Value.ToString()));
}

Here is the result.

Figure 46.

Export to Excel

Often, developers have a need to export DataGrid records into an Excel sheet for reporting purposes. The UltraGrid, with the support of other classes, can easily be exported into an Excel sheet.

Now, let’s add “UltraGridExcelExporter” control on win form and add a Button with a click event handler that will be responsible for exporting to Excel. The new UI looks like Figure 47 and Figure 48.

Figure 47.

Figure 48.

Now, on the Button click event handler, we use the UltraGridExcelExporter class and its Export method that is responsible for creating a new Excel document with the UltraGrid records in it.

Figure 49.

Code Snippet

private void ultraButtonExportToExcel_Click(object sender, EventArgs e)
        {
            try
            {
                string fileName = System.IO.Path.GetTempPath() + "TempFile.xlsx";
                this.ultraGridExcelExporter1.Export(this.ultraGridProducts, fileName);
                MessageBox.Show("File save successfully. \n" + fileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

The outlook looks like Figure 50.

Figure 50.

CRUD Operations

One of the best parts of using the UltraGrid is, it supports default CRUD (Create, Read, Update, and Delete) data operations. You only have to write a few lines of code to add a new record, or update and delete existing records in a database via the grid.

Let’s look at how we can implement the add, update, and delete records via a UltraGrid.

The UltraGrid has a property, AllowEdit on FieldSettings that makes UltraGrid rows editable. The other two properties that we will use in this section are AllowAddNew and AddNewRecordLocation.

Adding a new record

Let’s create a new Window and use a UltraDataGrid and bind it to the ContactType table of the AdventureWorks database. On this screen, we will add, update, and delete ContactTypes. See below Figures in Listing

To add a new record, we need to add the below code in “InitializeLayout” event to initialize UltraGrid for adding new row as shown in Figure 51.

var row = ultraGridContactType.DisplayLayout.Bands[0].AddNew();
ultraGridContactType.Rows.Move(row, 0);

 

Figure 51.

Code Snippet

private void ultraGridContactType_InitializeLayout(object sender, Infragistics.Win.UltraWinGrid.InitializeLayoutEventArgs e)
       {
           var row = ultraGridContactType.DisplayLayout.Bands[0].AddNew();
           ultraGridContactType.Rows.Move(row, 0);
       }

In Figure 51 we can see a new empty row got added. It will allow us to add a new row in the UltraGrid.

Figure 52.

When adding a new record, the RecordAdding and RecordAdded events are important.

There are two events that can be used.

AfterRowInsert This occurs after a new row is inserted. The row argument returns a reference to an UltraGridRow object that can be used to set properties of, and invoke methods on, the row that was inserted. You can use this reference to access any of the returned row's properties or methods.

This event is generated after a new row has been inserted, either programmatically, or by user interaction. A new row can be inserted programmatically by invoking the Add New method.

Note that the new row is not necessarily committed to the data source at the time of insert, however, since various factors such as the type of record locking employed by the data source, as well as the value of the UpdateMode property, can affect when the actual update occurs.

The BeforeRowInsert event, which occurs before a row is inserted, is generated before this event.

BeforeRowInsert Occurs before a new row is inserted. The band argument returns a reference to an UltraGridBand object that can be used to set properties of, invoke methods on, the band into which the new row will be inserted. You can use this reference to access any of the returned band's properties or methods.

The parent row argument returns a reference to an UltraGridRow object that can be used to set properties of, invoke methods on, the row that will be the parent of the row to be inserted. You can use this reference to access any of the returned row's properties or methods. If the row being inserted is not a child, parent row will be set to Nothing.

The cancel argument enables you to programmatically prevent the row from being inserted. This argument can be used to prevent the user from inserting a new row unless a certain condition is met.

This event is generated after a new row has been inserted, either programmatically, or by user interaction. A new row can be inserted programmatically by invoking the AddNew method.

The AfterRowInsert event, which occurs after a row is inserted, is generated after this event, provided cancel is not set to True.

The following code in Listing is the code behind event handlers where we can write code that will be executed when these events are fired Figure 53:

Figure 53.

Code Snippet

public partial class ContactTypeForm : Form

   public ContactTypeForm()
       {
           InitializeComponent();
       }

       private void ultraGridContactType_AfterRowInsert(object sender, RowEventArgs e)
       {

       }

       private void ultraGridContactType_BeforeRowsInsert(object sender, Infragistics.Win.UltraWinGrid.BeforeRowsInsertEventArgs e)
       {
        }
   }

Updating and an existing record

You use code in the above event handlers when a new record is being added or updated in the UI. For example, if a new record is updated and you want to notify a customer, this will be the event where you will write the notification functionality.

The RecordUpdating and the RecordUpdated events are fired when a record is being updated and when a record has been updated in the xamDataGrid respectively. This is where you also want to save your changes to the backend database.

RecordUpdating occurs when a DataRecord is about to be updated.

RecordUpdated occurs after a DataRecord has been updated.

The code snippet in Listing in Figure 54 implements these two event handlers and the code for adding and updating backend is written there.

Figure 54.

Code Snippet

int contactTypeId = 0;
  private void ultraGridContactType_AfterRowUpdate(object sender, Infragistics.Win.UltraWinGrid.RowEventArgs e)
        {
            string contactTypeID = e.Row.Cells["ContactTypeID"].Value.ToString();
            string contactTypeName = e.Row.Cells["Name"].Value.ToString();
            string modifiedDate = e.Row.Cells["ModifiedDate"].Value.ToString();

            if (!string.IsNullOrEmpty(contactTypeID) && !string.IsNullOrEmpty(contactTypeName) && !string.IsNullOrEmpty(modifiedDate))
            {
                // Update the record
                SqlHelper.UpdateRecord(contactTypeID, contactTypeName, modifiedDate);
            }
            else if (!string.IsNullOrEmpty(contactTypeName))
            {
                // Add New Record
                SqlHelper.AddRecord(contactTypeName);
                ultraGridContactType.DataSource = SqlHelper.PopulateDataGridContactType().Tables[0].DefaultView;
            }
        }

Let’s insert a new record in the UltraGrid, to do that go to the last row of UltraGrid and insert a record as shown in Figure 55.

Figure 55.

Figure 56.

Figure 57.

Now, if you click on a row and update the value of a cell or multiple cells, and hit Tab and leave the row, the UltraGrid update events will be fired, the code executes, and the data is updated in the data grid as well as in the backend database. See Figure 58 and Figure 59.

Figure 58.

Figure 59.

Deleting a record

The UltraGrid also provides a delete functionality by default, to do that select the row and press delete button and two delete event will fire BeforeRowDeleted it occurs before one or more rows are deleted and AfterRowDeleted occurs after one or more rows are deleted.

The added code shown in Figure 54 implements these two event handlers and the code for deleting backend is written there.

Figure 55.

Code Snippet

private void ultraGridContactType_AfterRowsDeleted(object sender, EventArgs e)
       {
           try
           {
               if (contactTypeId > 0)
               {
                   SqlHelper.DeleteRecord(contactTypeId);
               }
               else
               {
                   MessageBox.Show("Unknown Error!!");
               }
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
       }

Figure 56.

Summary

The UltraGrid is one of the richest data controls available for WinForm. The control provides built-in functionality to sort, filter, group, and find records. In this tutorial, I demonstrated how to connect with a SQL Server database and load data in a UltraGrid. You also saw how to filter, group, and sort data. In this tutorial, we also saw how to add, update, and delete data via the data grid control and export data to a Microsoft Excel document.