Doing CRUD Operations with Infragistics UltraGrid and Entity Framework

[Infragistics] Mihail Mateev / Monday, March 2, 2015

The Infragistics Windows Form controls are some of the best, most extensible third party controls that I have ever used for UI development. The set of controls is quite comprehensive and the API is exhaustive. You can do anything with these controls, in terms of overriding their function and form.

Based on my experience often very good components are designed to be used with some frameworks in the best way, but because IT industry is very dynamic often other new technologies stays most popular and developers look for the best practices to use existing good controls with latest frameworks.

Since I use (and tweak) the UltraGrid control most often, I have decided to create a short article sharing my experience with Infragistics UltraGrid API and probably the most popular ORM for .Net applications – Microsoft Entity Framework. Samples in this post use Infragistics Windows Forms Controls (part of Infragistics Ultimate vol.14.2), EF 6.1.2 / Database First Approach (you can download this version from this link) It is also possible to use NuGet package manager. The data source is based on the popular Northwind sample database. Database engine is SQLServer 2014 Express.

Considerations:

As it was mentioned above, UltraGrid is popular well designed and tested component, released for the first time several years ago. Natively it is designed to work easily with ADO.Net framework and uses data sources like ADO.Net DataTable.

If you want to use Entity Framework there are several specific things that you need to consider first to implement the basic :CRUD (Create, read, update and delete) operations. Most notably: Entities generated from Entity Framework are not native data sources, so it is not possible to bind them directly.

Sample and most important snippets:

For this sample, let's use a simple Windows Forms solution created with Visual Studio 2013 Update 4, including UltraGrid and Entity Framework model, based on Northwind using only a Products table/entity. When we use tables with relations we need to consider integrity rules when trying to update the data source.

The structure of the Product entity is shown below:

You need to initialize our EF model:

#region members

  

ObservableCollection data; 

static NORTHWNDEntities model = new NORTHWNDEntities();

  

#endregion members

Read operation:

ObservableCollection or List are some of the collections that you can use as a DataSource for UltraGrid.

public Form1()

{

    InitializeComponent();

    data = new ObservableCollection(model.Products.ToList());

    ultraGrid1.DataSource = data;

}

Now you can see records in your Infragistics grid. Actually, you cannot apply other CRUD operations on this data source and update database out of the box – you need to add some code.

Update operation:

Update operation works with the grid and binds as a data source collection (you just need to set your Windows Forms grid in edit mode), but because we're using Entity Framework, this data source is decoupled. So you need to add code where you want to update the entities from the model and to set their EntityState to Modified. The current sample demonstrates how to update all entities. If you have many records, it is recommended to add additional logic to update only the entities which are changed.

/// 

/// Handles the Click event of the btnUpdateData control.

/// 

///The source of the event.

///The  instance containing the event data.

private void btnUpdateData_Click(object sender, EventArgs e)

{

    var product = data.FirstOrDefault();

    var id = product.ProductID;

  

    foreach (Product newproduct in data)

    {

        var oldProduct = model.Products.Where(x => x.ProductID == newproduct.ProductID).FirstOrDefault();

        if (oldProduct != null)

        {

            oldProduct = newproduct;

            model.Entry(oldProduct).State = EntityState.Modified;

        }

        else

        {

            model.Products.Add(newproduct);

        }

    }

}

Finally you need to save changes in your database using SaveChanges() method.

The screenshot below demonstrates how to update data source using this sample application.

Create operation:

You can create a new record using the "add new record" option of the UltraGrid (it offers a button for this out of the box). Unfortunately this approach doesn't work with most data sources, so the best approach is programmatically to add a new element in the collection that you are using as a data source and to rebind it to the grid (see the snippet below):

/// 

/// Handles the Click event of the btnAddRow control.

/// 

///The source of the event.

///The  instance containing the event data.

private void btnAddRow_Click(object sender, EventArgs e)

{

  

    var newid = data.Select(x => x.ProductID).Max();

    data.Add(new Product { ProductID = newid + 1 });

    ultraGrid1.DataBind();

}

The screens below demonstrate how to do that via UI of the sample application.

Delete operation:

The Delete operation is not offered out of the box from the UI. When you need to delete the selection, you need to get the selected records using Selected.Rows, get the entity from each entity using the ListObect propery of the row, and you need to set the EnttyState.Deleted value. At the end you need to save the changes to the database.

/// 

/// Handles the Click event of the btnDelete control.

/// 

///The source of the event.

///The  instance containing the event data.

private void btnDelete_Click(object sender, EventArgs e)

{

    if (this.ultraGrid1.Selected.Rows.Count > 0)

    {

        // Delete the selected rows by calling DeleteSelectedRows.

        Product currProduct = null;

        

        foreach(var curr in this.ultraGrid1.Selected.Rows){

            currProduct = curr.ListObject as Product;

  

            if (currProduct != null)

            {

                var oldProduct = model.Products.Where(x => x.ProductID == currProduct.ProductID).FirstOrDefault();

                if (oldProduct != null)

                {

                    model.Entry(oldProduct).State = EntityState.Deleted;

                    model.Products.Remove(oldProduct);

                }

            }

        }

  

        this.ultraGrid1.DeleteSelectedRows();

        model.SaveChanges();                  

    }

    else

    {

        // Show a message if there are no selected rows.

        MessageBox.Show("There are no rows selected. Select rows first.");

    }

}

You can use T-SQL expressions using ExecureSQLCommand() method, which affects the database directly. It is possible to apply it without making changes in the model and saving it. This approach is faster, but you need to be very careful here because it doesn’t keep the consistency between the database and the entity model.

if (oldProduct != null)

{

    model.Entry(oldProduct).State = EntityState.Deleted;

    model.Database.ExecuteSqlCommand("delete from dbo.Products where ProductId="+oldProduct.ProductID);

}

The screenshots below demonstrates how to delete records/entities using the sample application, related to this post.

This article covers the basic CRUD operations, realized with Infragistics WinForms grid and Entity Framework - and to make the most of this post, be sure to download the source code of this sample here.

You'll also need the Northwind sample database, which you can get in SQLServer 2008 or newer through the CodePlex website. It is also possible also to download a version that's compatible with older versions of Microsoft SQL server from the Microsoft download center.

To play around with the Infragistics Windows Forms dashboards on your own, be sure to get Infragistics Ultimate and see the chart in action in our latest sample application by clicking the banner below!