ASPxGridView Excel style – Adding notes to grid cells

Mario Majcica October 24, 2012 4 Comments

Introduction

I am continuing with a series of posts about Devexpress controls. You can read the introduction about what are the Devexpress controls, how and where to get them, versioning information and prerequisites in my previous blog post that you can find at this address http://blog.majcica.com/2012/08/05/aspxgridview-master-detail-data-presentation-with-context-menus/.
What we are going to achieve in this post is a simple application that will list the credit cards and the amount spent per card per month. We will be able to add in an easy way a new credit card and edit all of the months imports. More, we will be able to add a note on each month and visually see the months containing notes.
In this example I will use a database and create two simple tables that will contain all or data. Again, this will be a simple example and it is not meant to be functionally correct. My only goal is to show you how to interact with this controls in order to achieve a certain functionality, which in this case is showing, adding and modifying notes in a similar way Excel does it.

Table of contents

1. The necessary and the result
2. Creating database schema
3. Writing the data service
4. Creating the page
5. Defining the ASPxGridView
6. Showing cells with notes
7. Showing notes in a popup screen
8. Adding, editing and removing notes
9. Changing the year
10. Downloads and the source code
11. Final word

The necessary and the result

The attached project is written in Visual Studio 2012. The express version should be enough. Also you will need a Microsoft SQL Server 2012, Express or and more advanced version will be just fine. The database is part of the project and it will be automatically mounted by VS. You can change this easily be creating your own database and changing the connection string in the web config. Last but not least you will need the 12.1.7 version of DevExpress ASP.NET controls installed on your machine. If you do have a more recent version, upgrading the project should be easy. Check this post about how to get the DevExpress controls and on how to eventually upgrade the project http://blog.majcica.com/2012/08/05/aspxgridview-master-detail-data-presentation-with-context-menus/.

At the end the result should be similar to this, however there is much more, so check this post till the end.

You can check it also LIVE. Live version persist the data in the session so each new session will reset all the data you have inserted. Also because of this, it is a bit simplified. If you are interested in the code, I can provide it to you, just ask in comments.

Creating database schema

First of all we will create a table that will store all of our credit cards. We will call it tbl_CreditCards. It will contain the following columns:

Another table that we will add is tbl_Imports in which all of our imports, per card, with note, will be persisted. The table is structured in the following way:

We also need to remember to add the foreign key constrain which we will call FK_tbl_Imports_tbl_CreditCards. Here is the complete SQL script that will do the necessary.

CREATE TABLE [dbo].[tbl_CreditCards]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(50) NOT NULL, 
    [Active] BIT NOT NULL DEFAULT 1
)

CREATE TABLE [dbo].[tbl_Imports]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Year] SMALLINT NOT NULL, 
    [Month] TINYINT NOT NULL, 
    [CreditCardID] INT NOT NULL,
    [Import] MONEY NOT NULL, 
    [Note] NVARCHAR(500) NULL,
    CONSTRAINT [FK_tbl_Imports_tbl_CreditCards] FOREIGN KEY ([CreditCardID]) REFERENCES [tbl_CreditCards](Id)
)

We will also add a stored procedure that will help us retrieving data from the database. One parameter will be required, in relation of which we are going to retrieve all the data and pre-format for presentation. We need to retrieve card name, imports and notes for all the months of the indicated year.

/****** Object:  StoredProcedure [dbo].[sp_GetCreditCardImports]    Script Date: 10/21/2012 23:49:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Mario Majcica
-- Create date: 30-3-2012
-- Description:	Retrieves all the imports per given year
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetCreditCardImports] 
	@YEAR smallint = 0
AS
BEGIN
	SET NOCOUNT ON;

	SELECT DISTINCT tbl_CreditCards.Name, tbl_CreditCards.Id,
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 1  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [1],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 2  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [2],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 3  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [3],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 4  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [4],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 5  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [5],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 6  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [6],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 7  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [7],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 8  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [8],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 9  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [9],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 10 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [10],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 11 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [11],
        (SELECT tbl_Imports.Import FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 12 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [12],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 1  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N1],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 2  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N2],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 3  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N3],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 4  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N4],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 5  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N5],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 6  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N6],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 7  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N7],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 8  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N8],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 9  AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N9],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 10 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N10],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 11 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N11],
        (SELECT tbl_Imports.Note FROM tbl_Imports WHERE tbl_Imports.Year = @YEAR AND tbl_Imports.Month = 12 AND tbl_Imports.CreditCardID = tbl_CreditCards.Id) AS [N12]
    FROM tbl_CreditCards LEFT JOIN tbl_Imports ON tbl_CreditCards.Id = tbl_Imports.CreditCardID
END

In order to test our stored procedure and other queries we will insert some testing data.

SET IDENTITY_INSERT [dbo].[tbl_CreditCards] ON
INSERT INTO [dbo].[tbl_CreditCards] ([Id], [Name], [Active]) VALUES (1, N'ABN Amro Gold', 1)
INSERT INTO [dbo].[tbl_CreditCards] ([Id], [Name], [Active]) VALUES (2, N'ABN Amro Visa', 1)
INSERT INTO [dbo].[tbl_CreditCards] ([Id], [Name], [Active]) VALUES (3, N'ING Mastercard', 1)
INSERT INTO [dbo].[tbl_CreditCards] ([Id], [Name], [Active]) VALUES (4, N'Rabobank American Express', 1)
SET IDENTITY_INSERT [dbo].[tbl_CreditCards] OFF

SET IDENTITY_INSERT [dbo].[tbl_Imports] ON
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (1, 2012, 1, 1, 1200, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (2, 2012, 2, 1, 200, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (3, 2012, 3, 1, 3500, 'Spent too much!')
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (4, 2012, 4, 1, 500, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (5, 2012, 5, 1, 700, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (6, 2012, 6, 1, 100, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (7, 2012, 7, 1, 1700, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (8, 2012, 1, 2, 730, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (9, 2012, 2, 2, 220, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (10, 2012, 3, 2, 800, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (11, 2012, 4, 2, 120, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (12, 2012, 5, 2, 720, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (13, 2012, 6, 2, 190, NULL)
INSERT INTO [dbo].[tbl_Imports] ([Id], [Year], [Month], [CreditCardID], [Import], [Note]) VALUES (14, 2012, 7, 2, 1780, NULL)
SET IDENTITY_INSERT [dbo].[tbl_Imports] OFF

Writing the data service

Our data service will be a simple class that will expose several static methods that will help us performing all the CRUD actions. Let’s define them.

As I would like to have a combo showing all the years present in the database plus the current and subsequent year, in order that the user can filter the list by this values, I will first prepare a method that will retrieve and prepare this data.

/// 

/// Retrives all years from the imports table.
/// Adds if missing the current year and the following year.
/// 
/// An ordered list of years.
public static SortedDictionary GetYears()
{
    SortedDictionary years = new SortedDictionary();
    string query = @"SELECT DISTINCT [Year] FROM [tbl_Imports] ORDER BY [Year]";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query, cn))
        {
            cn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (reader.Read())
                {
                    years.Add((int)reader[0], reader[0].ToString());
                }
            }
        }
    }

    int currentYear = DateTime.Now.Year;
    int followingYear = currentYear + 1;

    if (!years.ContainsKey(currentYear))
        years.Add(currentYear, currentYear.ToString());

    if (!years.ContainsKey(followingYear))
        years.Add(followingYear, followingYear.ToString());

    return years;
}

Now we need to prepare the method that will call our stored procedure and return the data:

/// 

/// Retrives all credit cards imports and notes for the indicated year grouped by month
/// 
/// Accrual Year
/// Datatable contining the requested data.
public static DataTable GetAllByDetail(int year)
{
    DataTable table = new DataTable();

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("sp_GetCreditCardImports", cn))
        {
            cmd.Parameters.Add("@YEAR", SqlDbType.SmallInt).Value = year;
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                table.Load(reader);
            }
        }
    }

    return table;
}

For this first part we also need to prepare the methods that will manage our notes.

public static bool AddNewNote(int creditCardID, int year, byte month, string note)
{
    if (string.IsNullOrEmpty(note))
        return true;

    string query = @"UPDATE tbl_Imports SET [Note] = @note WHERE CreditCardID = @creditCardID AND [MONTH] = @month AND [YEAR] = @year";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToString(), cn))
        {
            cmd.Parameters.Add("@note", SqlDbType.NVarChar, 500).Value = note;
            cmd.Parameters.Add("@creditCardID", SqlDbType.Int).Value = creditCardID;
            cmd.Parameters.Add("@year", SqlDbType.SmallInt).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;

            cn.Open();
            return cmd.ExecuteNonQuery() > 0;
        }
    }
}

public static bool DeleteNote(int creditCardID, int year, byte month)
{
    string query = @"UPDATE tbl_Imports SET [Note] = null WHERE CreditCardID = @creditCardID AND [MONTH] = @month AND [YEAR] = @year";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToString(), cn))
        {
            cmd.Parameters.Add("@creditCardID", SqlDbType.Int).Value = creditCardID;
            cmd.Parameters.Add("@year", SqlDbType.SmallInt).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;

            cn.Open();
            return cmd.ExecuteNonQuery() > 0;
        }
    }
}

public static bool UpdateNote(int creditCardID, int year, byte month, string note)
{
    if (string.IsNullOrEmpty(note))
    {
        return DataService.DeleteNote(creditCardID, year, month);
    }

    string query = @"UPDATE tbl_Imports SET [Note] = @note WHERE CreditCardID = @creditCardID AND [MONTH] = @month AND [YEAR] = @year";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToString(), cn))
        {
            cmd.Parameters.Add("@creditCardID", SqlDbType.Int).Value = creditCardID;
            cmd.Parameters.Add("@year", SqlDbType.SmallInt).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;
            cmd.Parameters.Add("@note", SqlDbType.NVarChar, 500).Value = note;

            cn.Open();
            return cmd.ExecuteNonQuery() > 0;
        }
    }
}

public static string GetNote(int creditCardID, int year, byte month)
{
    string query = @"SELECT [Note] FROM tbl_Imports WHERE CreditCardID = @creditCardID AND [MONTH] = @month AND [YEAR] = @year";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToString(), cn))
        {
            cmd.Parameters.Add("@creditCardID", SqlDbType.Int).Value = creditCardID;
            cmd.Parameters.Add("@year", SqlDbType.SmallInt).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt, 500).Value = month;

            cn.Open();
            return (string)cmd.ExecuteScalar();
        }
    }
}

Now we are ready to start writing our page.

Creating the page

In our site default page we will add an ASPxLabel and ASPxComboBox. First will show the text Selected Year and the second one will list all the years that we will retrieve via GetYears method. Please forgive the usage of the tables for setting up the layout, div’s always bothered me, specially when vertical alignment is in question. Also I will add an ObjectDataSource that will be used for binding.

As you can see, I used the method GetYears as my select method and I set TextField and ValueField to correct names in order to bind to a Dictionary object. For the ASPxComboBox I specified a client side event in which later on I will create a callback to reflect the changes. Right now the code will not run correctly because we have no client side event in our JavaScript file specified, but do not worry, as soon as we specify our grid, we will write down the necessary. Last thing is the definition of server side DataBound event, in which we will make the current year as selected in our ASPxComboBox. This is the missing code.

protected void cbYears_DataBound(object sender, EventArgs e)
{
    // Select current year as default year to show
    cbYears.SelectedIndex = cbYears.Items.IndexOfValue(DateTime.Now.Year);
}

Now if you run your solution you should see something similar:

Let’s now define the grid.

Defining the ASPxGridView

The longest and the most laborious thing is defining the columns. I will show you the complete code of the grid and then discuss all the properties and events I have used.

    
        
            
                
                    
                
                
                    <%# Eval("Name") %>
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
            
            
                
                
                
                
            
        
        
            
            
            
            
            
            
            
            
            
            
            
            
            
        
        
        
        
        
        
        
        
            

            
            
        
    

As you can see, the longest and most tedious part is defining the columns. We defined a column for a card name, one for each month and a column that will show the total per card. Total column is unbound and I we will perform the calculations for this column in CustomUnboundColumnData event. As you can imagine FieldName property is the name of a column in the binded DataTable, Caption is a column header text and ToolTip is well a tool tip for that column header. DisplayFormatString is a formatting string option (N2 is this case formats the value as integral and decimal digits, group separators, and a decimal separator with optional negative sign, with decimal precision of two) and NullDisplayText (when no data is supplied) is set for styling purposes to a slash character.
For the card name and total column, we also specified what should be shown when the row is in edit.
A TotalSummary feature from the ASPxGridView is used, it will show an extra row and the totals for each specified column. By adding ASPxSummaryItem we are specifying the column that needs to be took in consideration (by indicating ShowInColumn property we are positiong in the indicated column the result of our summay), a SummaryType that should be performed (Sum in this case) and a field on which the calculation should be based.
We set some properties on the grid so let’s check what is the behavior of these settings. SettingsPager mode ShowAllRecords basically disables the pager meanwhile the SettingsEditing mode Inline sets the editing mode to inline. We also decided to show a Footer (necessary for Summary row) and to show a title panel, an extra row on top of the grid that will show a specific label, at example the meaning of the data that is shown in the grid, which we later set to “Credit Cards Year Overview” in SettingsText Title property.
Also we disabled the Drag and Drop on columns (re-orderable columns feature of ASPxGridView that is not desired in this case), disable out of the box sorting feature feature and enabled a row visual tracking (EnableRowHotTrack makes the row that is under the mouse cursor highlighted).
In order to perform a custom calculation for our Total column we defined the CustomUnboundColumnData event. This is his server side implementation.

protected void gvImports_CustomUnboundColumnData(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewColumnDataEventArgs e)
{
    if (e.Column.FieldName == "Total")
    {
        e.Value = Convert.ToDecimal(e.GetListSourceFieldValue("1") == DBNull.Value ? 0 : e.GetListSourceFieldValue("1")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("2") == DBNull.Value ? 0 : e.GetListSourceFieldValue("2")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("3") == DBNull.Value ? 0 : e.GetListSourceFieldValue("3")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("4") == DBNull.Value ? 0 : e.GetListSourceFieldValue("4")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("5") == DBNull.Value ? 0 : e.GetListSourceFieldValue("5")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("6") == DBNull.Value ? 0 : e.GetListSourceFieldValue("6")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("7") == DBNull.Value ? 0 : e.GetListSourceFieldValue("7")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("8") == DBNull.Value ? 0 : e.GetListSourceFieldValue("8")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("9") == DBNull.Value ? 0 : e.GetListSourceFieldValue("9")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("10") == DBNull.Value ? 0 : e.GetListSourceFieldValue("10")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("11") == DBNull.Value ? 0 : e.GetListSourceFieldValue("11")) +
                    Convert.ToDecimal(e.GetListSourceFieldValue("12") == DBNull.Value ? 0 : e.GetListSourceFieldValue("12"));
    }
}

The even argument helps us by putting on our disposition the column name that he is processing at that moment, so only if he is processing a desired column we assign that columns row value to a sum of all months columns values. If column value is DBNull.Value then zero is used, else a column value is converted to Decimal and then summed.
It is time to bind our grid and check the result.
I choose to use the DataBinding event in order to assign the right source to the grid.

protected void gvImports_DataBinding(object sender, EventArgs e)
{
    int yearToBind = DateTime.Now.Year;

    if (cbYears.SelectedItem != null)
        yearToBind = Convert.ToInt32(cbYears.SelectedItem.Value);

    gvImports.DataSource = DataService.GetAllByDetail(yearToBind);
}

Before indicating the designed method to the grids DataSource I will check the parameter and if for any reason it is not indicated in the ComboBox I will set it to the current year. The effective bind method will be called from the Page_Load event only on first show (and not on postbacks).

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        gvImports.DataBind();
    }
}

Now we can run our code and check the result.

Pretty neat and nice result for a relatively small effort, isn’t it?

Showing cells with notes

Now the idea is to set for each cell that contains a note, a specific background image so the user on the first sight can understand that there is a note for that import. In order to set dynamically a background on a cell we need to declare HtmlDataCellPrepared on our grid. Don’t forget to set the event in the ASPX file (OnHtmlDataCellPrepared=”gvImports_HtmlDataCellPrepared”) and set the following method in your page:

protected void gvImports_HtmlDataCellPrepared(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewTableDataCellEventArgs e)
{
    if (e.DataColumn.FieldName == "Name" || e.DataColumn.FieldName == "Total" || e.CellValue == DBNull.Value)
    {
        return;
    }

    object note = e.GetValue(string.Format("N{0}", e.DataColumn.FieldName));

    if (note != DBNull.Value && note != null)
    {
        e.Cell.Style.Add("background-image", ResolveUrl("~//images//yellow-triangle.png"));
        e.Cell.Style.Add("background-repeat", "no-repeat");
    }
}

First line of code is a shortcut, which says that if the cell we are processing is in the Name or Total column (columns that can’t contain notes) or if CellValue is empty, we can stop processing it. As our DataSource contains extra columns that contain notes, the name of the column is N plus the number of the month column to which it is associated to, we can easly establish the name of the column by checking the current processing items column and request the note by e.GetValue method in which we will pass the name of the Note column we are searching the value for (at example if we are processing the January column which is called 1, we will search on the current processing row the value of the column N1 which will eventually contain the note.
Now if the note is not DBNull.Value and note is not null, we will add the processing cell style attributes background-image and background-repeat to desired values, for the first one the image we chose and for the second one the repeat behavior (which in this case should not repeat).
The result is that all cells that contains a note do have a different background and they can be quickly spotted by the user.

Showing notes in a popup screen

As till now we are only showing the cells that contains some notes, it will be nice if we add a way to show, add and remove them. Let’s first start with showing them. Showing the notes will be performed by passing the mouse over the cell that contains the note and after a given amount of time a div with the note text will be shown. We are going to achieve this in the following way. At first we will define a window that will show the note itself and we will use ASPxPopupControl to achieve this:

Well most of the properties are self explanatory, I just added an ASPxPopupControl and as his content I added a simple ASPxLabel control. Notice that I assigned a specific client instance name for the ASPxPopupControl control in order to call the methods and properties on client side with ease.
Now we need to define JavaScript functions that will be assigned to each cell that contains a note on the onmouseover and onmouseout client side events.

var reservePopup = false;

function ClosePopup() {
    reservePopup = false;

    if (PopUpNote.IsVisible()) {
        PopUpNote.Hide();
    }
}

function ShowPopup(note, elementID) {
    lblNote.SetText(note);

    ClosePopup();

    reservePopup = true;

    setTimeout(function () {
        if (reservePopup) {
            PopUpNote.ShowAtElementByID(elementID);
        }
    }, 1000);
}

This code maybe seems a bit complicated and tricky, so I will try to explain it. When a mouse gets over the interested cell, the ShowPopup method is executed. We will set the text of the lablel control that we just add in our ASPxPopupControl and close any eventually opened pop-up windows (just to be sure). Then a flag reservPopup is set to true (this is used for controlling the time out, in the case that a mouse cell gets out of the cell, this flag will be set to false, it means that if the showing a popup was requested and the timeout is not yet expired, the popup will not be show, basically if you were quicker than the given timeout triggers the Show function on window) and a function is scheduled for the execution in 1000 milliseconds. This function will check if it should show the pop-up with the note and if so it will call the method ShowAtElementByID of our ASPxPopupControl.
What we are missing now is associating this function to our cells and in order to achieve that, we need to modify our HtmlDataCellPrepared event in the following way:

protected void gvImports_HtmlDataCellPrepared(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewTableDataCellEventArgs e)
{
    if (e.DataColumn.FieldName == "Name" || e.DataColumn.FieldName == "Total" || e.CellValue == DBNull.Value)
    {
        return;
    }

    object note = e.GetValue(string.Format("N{0}", e.DataColumn.FieldName));

    if (note != DBNull.Value && note != null)
    {
        e.Cell.Style.Add("background-image", ResolveUrl("~//images//yellow-triangle.png"));
        e.Cell.Style.Add("background-repeat", "no-repeat");

        string htmlEncoded = Server.HtmlEncode(note.ToString()).Replace("n", "
");

        e.Cell.Attributes.Add("onmouseover", string.Format("ShowPopup('{0}','{1}')", htmlEncoded, e.Cell.ClientID));
        e.Cell.Attributes.Add("onmouseout", "ClosePopup()");
    }
}

As you can see, we added three new lines. In the first one we are preparing our note to be “Html compatible” by encoding any special char to a proper html notation and substituting the new line character with his HTML equivalent (
break row in this case). Also as the ShowPopup functions second parameter we will pass the cells client ID so the pop-up can be shown next to the cell. Now we can run and test our code.

Well nice result!

Adding, editing and removing notes

As another requirement it will be nice to show a popup menu on the right click of any cell and propose to Add, Edit or Remove the note from that cell. It is interesting and we will jump on work straight forward by defining an ASPxPopupMenu with the necessary items.

Inside our ASPxPopupMenu we defined three menu items, one per each specific information we are going to perform. We also assigned a menus ClientInstanceName and a client side event ItemClick implementation of which we will see later in text.
What we now need to prepare is the interface that will be used for Adding and editing notes. We are going to define another ASPxPopupControl controls and define it’s content.

Popup is set in a similar way as a previous one, the content only contains different controls. You will see that I used a ASPxMemo control which is very similar to a multiline text box, and two buttons. Non of these buttons causes validation or generates a post back on click and both have an client side event associated to their client side click event. Let’s check first the the implementation of the ASPxPopupMenu client side events then we will check the rest.

function GridMenu_ItemClick(s, e) {
    switch (e.item.name) {
        case 'AddNote':
            currentIsNoteEdit = false;
            txtNote.SetText('');
            popupAddNote.SetHeaderText('Add Note');
            popupAddNote.ShowAtElement(gvImports.mainElement);
            txtNote.Focus();
            break;
        case 'EditNote':
            currentIsNoteEdit = true;
            txtNote.SetText('');
            popupAddNote.SetHeaderText('Edit Note');
            popupAddNote.ShowAtElement(gvImports.mainElement);
            popupAddNote.PerformCallback(currentVisibleIndex + '|' + currentFieldName);
            break;
        case 'RemoveNote':
            deleteCaller = 'Note';
            popupConfirmDelete.Show();
            break;
    }
}

When an item in the GridMenu is clicked, this is the code that is executed on the client side. We are checking in the switch statement the name of the clicked item and based on that executing the indicated operations. In case of AddNote we are setting a flag currentIsNoteEdit to false. This flag will be used to understand if we are editing a note or we are adding a new onse (since we will use the same popup for both operations). Then we make sure that the text of our ASPxMemo control is set to empty and set a popup control header to ‘Add Note’ (same reason as before, it is a shared control for between different actions). Then we are showing the popup and setting the focus on ASPxMemo control. Similar things for EditNote except that as the last thing we are performing a callback on a popup control. We do that so we can retrieve the note data as we do not have them available on client side (at least not easily available, still the will be html encoded and we will have some trouble re-encoding the same data). If you check popupAddNote definition you will see that we already declared OnWindowCallback event, now let’s check it’s implementation:

protected void popupAddNote_WindowCallback(object source, DevExpress.Web.ASPxPopupControl.PopupWindowCallbackArgs e)
{
    string[] values = e.Parameter.Split('|');

    int visibleIndex = int.Parse(values[0]);
    int creditCardID = (int)gvImports.GetRowValues(visibleIndex, "Id");
    int year = (int)cbYears.SelectedItem.Value;
    byte month = byte.Parse(values[1]);

    txtNote.Text = DataService.GetNote(creditCardID, year, month);
    txtNote.Focus();
}

You could deduce from the JavaScript call that we were passing as arguments to this callback two values, currentVisibleIndex and currentFieldName. First one is self explanatory meanwhile the second one is the column name, which in our case is the related month. I will get back on this second parameter later. Once I receive this data on server side, I will retrieve the necessary and set relevant properties.
Let’s get back on the last item of the ASPxPopupMenu and write down and explain the code. The idea is to show a confirmation dialog before deleting a note. I will now create a dialog that will be reusable even later, for other entities.

    
        
            

                

                    

                         
                    

                        
                        
                    
                
            
            

            
        
    

As earlier we defined a ASPxPopupControl and created the content that should be shown. We have two buttons, one for canceling this action and another for confirming it. Both have no server side events or actions assigned, everything is performed on client side click in for that foreseen event. This events are defined in the following way:

function btnCancelDelete_Click(s, e) { popupConfirmDelete.Hide(); }

function btnDelete_Click(s, e) {
    popupConfirmDelete.Hide();

    if (deleteCaller == 'Note')
        gvImports.PerformCallback('DeleteNote|' + currentVisibleIndex + '|' + currentFieldName);
}

First method is self explanatory, on cancel click call Hide method on popup control. Second method also as first thing hides the popup then for the specific caller executes a callback with in arguments the command that should be performed, selected item’s visible index and column name.
This as all other commands are performed in the grids CustomCallback event. This is the code I used:

protected void gvImports_CustomCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomCallbackEventArgs e)
{
    // if no parameter is passed from cliend side, do no process
    if (string.IsNullOrEmpty(e.Parameters))
        return;

    int year = (int)cbYears.SelectedItem.Value;

    string[] values = e.Parameters.Split('|');
    string command = values[0];
    int visibleIndex = 0;
    int creditCardID = 0;

    if (values.Length > 1)
    {
        visibleIndex = int.Parse(values[1]);
        if (command != "ChangeYear")
            creditCardID = (int)gvImports.GetRowValues(visibleIndex, "Id");
    }

    byte month = 0;

    if (values.Length > 2)
        month = byte.Parse(values[2]);

    switch (command)
    {
        case "AddNote":
            DataService.AddNewNote(creditCardID, year, month, txtNote.Text);
            break;
        case "DeleteNote":
            DataService.DeleteNote(creditCardID, year, month);
            break;
        case "EditNote":
            DataService.UpdateNote(creditCardID, year, month, txtNote.Text);
            break;
        case "ChangeYear":
            gvImports.DataSource = DataService.GetAllByDetail(Convert.ToInt32(values[1]));
            break;
    }

    gvImports.DataBind();
}

If no parameter is specified, ignore the callback. Split the string by the separation character that I used for convection for separating different values in a string. If the retrieved array contains more then one element, aain by my own convection it means that the second parameter indicates a visible index value as long the first value, that indicates the action to perform, isn’t ChangeYear. In this case the second value is indicating the year on which the grid should be bound. If the array contains more than two values then the third value indicates the month and here it is what really the currentFieldName that we were passing is. Now, once I have all the data, I can check the command and perform the desired action. All the calls to the database were already written down on the start, so I do not need to do more than just call a specific method od the DataService and pass just decoded parameters. Easy? Clean at least (I hope so).
Seems that everything is wired up, but we do not have yet anything that will pop up our menu on the grid. In order to achieve that we need to modify our old acquaintance, the HtmlDataCellPrepared event. We will modify it in the following way:

protected void gvImports_HtmlDataCellPrepared(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewTableDataCellEventArgs e)
{
    if (e.DataColumn.FieldName == "Name" || e.DataColumn.FieldName == "Total" || e.CellValue == DBNull.Value)
    {
        e.Cell.Attributes.Add("oncontextmenu", string.Format("OnCellContextMenu(event, {0}, '{1}', false, false)", e.VisibleIndex, e.DataColumn.FieldName));
        return;
    }

    object note = e.GetValue(string.Format("N{0}", e.DataColumn.FieldName));

    if (note != DBNull.Value && note != null)
    {
        e.Cell.Style.Add("background-image", ResolveUrl("~//images//yellow-triangle.png"));
        e.Cell.Style.Add("background-repeat", "no-repeat");

        string htmlEncoded = Server.HtmlEncode(note.ToString()).Replace("n", "
");

        e.Cell.Attributes.Add("onmouseover", string.Format("ShowPopup('{0}','{1}')", htmlEncoded, e.Cell.ClientID));
        e.Cell.Attributes.Add("onmouseout", "ClosePopup()");
        e.Cell.Attributes.Add("oncontextmenu", string.Format("OnCellContextMenu(event, {0}, '{1}', true, true)", e.VisibleIndex, e.DataColumn.FieldName));
    }
    else
    {
        e.Cell.Attributes.Add("oncontextmenu", string.Format("OnCellContextMenu(event, {0}, '{1}', false, true)", e.VisibleIndex, e.DataColumn.FieldName));
    }
}

As you can notice I added a new attribute on the cell, oncontextmenu, and associated to it a client side function OnCellContextMenu. This client side function that we are going to specify, requires several parameters, the event, rows visible index, column name, indication if the field contains note and as the last one if the cell contains a value. All this information will be useful when we are going to decide what to show and later on for having all the necessary data in order to execute the operations.
We are setting this attribute in three different places, for three different types of cells. The first one is for the cells that has no value and on which there should not be show the context menu. We will handle this on client side, however this is necessary for future improvements that you will see in my next blog post. Then we are assigning the same to all the cells that contains a note and all the cells that doesn’t however with different parameters. Client side function looks like this.

function OnCellContextMenu(e, visibleIndex, fieldName, hasNote, hasValue) {
    currentVisibleIndex = visibleIndex;
    currentFieldName = fieldName;

    ShowMenuItem('AddNote', !hasNote && hasValue);
    ShowMenuItem('EditNote', hasNote);
    ShowMenuItem('RemoveNote', hasNote);

    var currentEvent = (window.event) ? window.event : e;

    ASPxClientUtils.PreventEventAndBubble(currentEvent);

    gvImports.SetFocusedRowIndex(visibleIndex);

    if (hasValue)
        GridMenu.ShowAtPos(ASPxClientUtils.GetEventX(currentEvent), ASPxClientUtils.GetEventY(currentEvent));
}

function ShowMenuItem(itemName, enabled) { GridMenu.GetItemByName(itemName).SetVisible(enabled); }

Well we are persisting the visible index and field name in variables that are globally accessible, so we can use them later. Now we will hide or show different menu items base on what are actually the operations that we can perform. If the cell already contains an note, we can modify the note or delete it, otherwise if the cell has a value but doesn’t the note we can only add a new note. This basically explains the next three lines of code as them translate that into the code. Now we will determinate the windows event as it is handled in a specific way on different browsers and then prevent this event and bubble by using a helper method that is available in DevExpress client site framework. This event determination is necessary in order to make the popup menu work properly on all major browsers of today. And the last important thing is showing the menu itself. We will show the menu only on cells that has a value and we will achieve that by calling a ShowAtPos method and calculating the right coordinates again via DevExpress framework helpers. That’s it! Let’s run our example and test it. The following screen is what you should see once you try to add a new note.

Changing the year

Something we almost forgot is to set the client event code that will change the selected year. Initially we only defined the event on client side but actually with no implementation. As the server side code will already manage the “ChangeYear” parameter, we only need to perform the right callback:

function cbYears_SelectedIndexChanged(s, e) { gvImports.PerformCallback('ChangeYear|' + s.GetValue()); }

As you saw in other occasions we are making a callback on the ASPxGridView and passing a command parameter and the newly selected value.

Downloads and the source code

You can find the source code of my project for download here.
You can find a trial version of the requested controls here.

Final word

This is it for the first part, if you have any question, feel free to comment. I will use this example as a base for the following blog post in which I will extend the functionalities by introducing inline editing, CRUD operations on credit cards, export and more, so stay tuned!

ASPxGridView master-detail data presentation with context menus

Mario Majcica August 5, 2012 2 Comments

Introduction

As many companies I worked for are using DevExpress controls I decided to write a couple of posts about some real life situations and ways they can be solved by using DevExpress ASP.NET Suite. In this and following post I will show you a couple of techniques on how to achieve a certain behavior that goes slight further than the demo examples that you can find on DevExpress site.
I will be using ASP.NET suite of controls, more specifically Web Forms.

At the end of the article this is the expected result:

You can also check the LIVE DEMO.

Table of contents

1. What are DevExpress controls?
2. Requirements
3. The project
4. Creating a data source
5. The web page
6. Defining a detail grid
7. Adding a context menu to the detail grid
8. Programmatically disabling menu items
9. Aesthetic changes
10. Downloads and the source code
11. Notes and other resources

What are DevExpress controls?

A set of controls that enrich your toolbox by adding several controls that are not present in the standard ASP.NET controls and some of the controls that are offered as a good substitute to already existing controls. All of the DevExpress controls are rich by the properties, methods and events both on client and server side, giving you the possibility to achieve results that otherwise will require a lot more extra coding.

Requirements

All of my examples are written for .NET 4.0 with Visual Studio 2010 using the version v2012 vol 1.5 of DevExpress controls. You can find a trial version of the requested controls here DevExpress Demo. Any version of Visual Studio is just fine, from Express to Ultimate. Also you can easily migrate this project to .NET 3.5 if needed. In case that the version of DevExpress controls I used is not available anymore, it should be easy to upgrade the project by DXperience Project Converter. For more information’s on project converter check DevExpress web site.

The project

I will start with the default Visual Studio ASP.NET Web Application.
Considering this example just a practice about the UI and the controls itself, I will put no emphases on the data source and just create a super simple data model. We will have two data entities, User and Project. As a cardinality, we have a many-to-many relationship, so one user can be related to many projects as a project can have several users. Also I will create a class called DataService that will create a couple of values that will represent our data.

Let’s start!

Creating a data source

First we will create a Project class. In the default constructor we will set the project status property to new. Except for this, we will have three properties, an ID, project name, and a project status.

public class Project
{
    public Project()
    {
        Status = ProjectStatus.New;
    }

    public int ID { get; set; }
    public string Name { get; set; }
    public ProjectStatus Status { get; set; }
}

As you can see, project status is an enumerator, so let’s define it together with other possible project statuses.

public enum ProjectStatus
{
    New,
    InProgress,
    Failed,
    Done
}

Now we will define the user class. It has several properties and one public method. The method returns the number of associated projects of the current customer instance.

public class User
{
    public User()
    {
        Projects = new List();
    }

    private string m_fullName;

    public int ID { get; set; }
    public string UserName { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List Projects { get; set; }

    public string FullName
    {
        get { return string.Format("{0}, {1}", this.LastName, this.FirstName); }
    }

    public bool HasProjects()
    {
        return Projects.Count > 0;
    }
}

This is a very simple model and probably in a real life situation your model will be richer with properties and methods.
Next to come is a class that will create several instances of the model classes and return them via a method. In this way we can have easily all the data we need for our example. The code I used is following, you can add other data if in search of a particular behavior.

[DataObject(true)]
public class DataService
{
    [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
    public static List GetUsers()
    {
        List users = new List();

        users.Add(new User() { ID = 1, UserName = "JohnDoe", FirstName = "John", LastName = "Doe", Projects = GetSomeProjects() });
        users.Add(new User() { ID = 2, UserName = "JimDoe", FirstName = "Jim", LastName = "Doe", });
        users.Add(new User() { ID = 3, UserName = "RobertDoe", FirstName = "Robert", LastName = "Doe", });
        users.Add(new User() { ID = 4, UserName = "AlisonDoe", FirstName = "Alison", LastName = "Doe", Projects = GetSomeProjects2() });

        return users;
    }

    [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
    private static List GetSomeProjects()
    {
        List projects = new List();

        projects.Add(new Project() { ID = 1, Name = "Test1" });
        projects.Add(new Project() { ID = 2, Name = "Test2", Status = ProjectStatus.Failed });
        projects.Add(new Project() { ID = 3, Name = "Test3" });

        return projects;
    }

    [DataObjectMethodAttribute(DataObjectMethodType.Select, false)]
    private static List GetSomeProjects2()
    {
        List projects = new List();

        projects.Add(new Project() { ID = 4, Name = "Test4" });
        projects.Add(new Project() { ID = 5, Name = "Test5", Status = ProjectStatus.Failed });
        projects.Add(new Project() { ID = 6, Name = "Test6", Status = ProjectStatus.InProgress });

        return projects;
    }
}

Now our data source is ready. The next thing to care of is the web page itself.

The web page

Add the grid by drag dropping the ASPxGridView control in the page. Modify the properties in order to match the following:

What we did is changing the grids ID to gvMaster, indicating the Key field name by setting the KeyFieldName to “ID” and specifying the columns that will be shown together with mapping a column field name to the desired model property. Now we need to bind the grid and we will do it from the code.
In order to be able to show the changes, we will save our data in a session and in order to ease this operation we will create a property that will perform all of this check and operations for us. Get in the page’s code file and declare the following.

public List Users
{
    get
    {
        if (Session["Data"] == null)
            Session["Data"] = DataService.GetUsers();

        return (List)Session["Data"];
    }
    set { Session["Data"] = value; }
}

When the property is requested for the first time, the session item is null, then we will recall the method that we created previously and save the data in the session. This is not a technique that you will use in a real life application, because probably you will recall the data from the database at a certain point and eventually cache it. As explain this is not the goal of this article, I will just mention it.
Now it’s time to bind the grid to this property.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        gvMaster.DataSource = Users;
        gvMaster.DataBind();
    }
}

If you run the code now you should see the following (or a similar screen, don’t worry if the theme that is applied doesn’t look the same, we will came to that later).

Defining a detail grid

In order to add a detail grid to a master grid, we first need to define a template for the detail row. Inside the aspx file get inside the definition of ASPxGridView and open the section then inside the newly created section a new one called . Close properly both sections. In the Detail Row template add a new ASPxGridView and define as for master grid the columns and some properties. Also do not forget to set the ShowDetailRow property to true. At the end your code should look like this.

Now we need to handle the binding of the detail grid. Before that we will check for each row in the master grid if there is the data for the detail grid and if not, hide the plus sign. To achieve that, we need to declare the OnDetailRowGetButtonVisibility event on the master grid. The code of your master grid should look like this

In the server side event code we need to check if there are detail data for each master element.

protected void gvMaster_DetailRowGetButtonVisibility(object sender, ASPxGridViewDetailRowButtonEventArgs e)
{
    User currentUser = Users.Find(u => u.ID == (int)gvMaster.GetRowValues(e.VisibleIndex, "ID"));

    if (!currentUser.HasProjects())
        e.ButtonState = GridViewDetailRowButtonState.Hidden;
}

For the less experienced, I will quickly explain this code. In order to get the row value we will use the argument that is passed to the event which contains the currently processing row visible index and with that information retrieve the value of the ID field of that row. The following code gvMaster.GetRowValues(e.VisibleIndex, "ID")) will give us the the value of ID filed for the current row. Then we will retrieve the User class instance for a given ID and check if it has project. In case that this user is not associated to any project we will hide the plus sign (button) for that row with by setting the argument property ButtonState to hidden.
Now let’s bind detail grid.
Each time the user click’s on the plus sign, a callback will be automatically generated by the grid and on the server side, binding event for the detail grid will be raised. OnBeforePerformDataSelect event in the detail grid is the right one for indicating the data source on which the current detail grid should be bind. Define the previously mentioned event:

And bind the data in that event.

protected void gvDetail_BeforePerformDataSelect(object sender, EventArgs e)
{
    ASPxGridView grid = sender as ASPxGridView;
    int currentUserID = (int)grid.GetMasterRowKeyValue();

    grid.DataSource = Users.Find(u => u.ID == currentUserID).Projects;
}

In order to refer to a proper object we need to cast the sender of the event to a ASPxGridView. Then DevExpress grid comes in our help with GetMasterRowKeyValue() method, which as it’s name says, will return the key value of the master grid in which our current detail grid is defined. Whit that value, which is basically the User ID, we can retrieve the necessary data which we will set as a DataSource of the current detail grid.
That’s it, your master detail grid should work now and this is how it should look like.

If your solution is not looking completely the same do not worry, important is that it compiles and shows the data correctly for now.

You can see that I’m constantly pointing for the detail grid the current fact. This is important, because we can have multiple detail grid’s in the page, so we always need to refer to a proper object. Always think about that when you are working with detail grid.

Adding a context menu to the detail grid

This is a bit more complicated task but as you will see a quite simple way to achieve this.
Start with adding a client side event ContextMenu on the detail grid:

And then defining a menu with couple one item in it (just drag and drop PopupMenu control from the toolbox in the page):

With DevExpress controls we can define a client side (JavaScript) events in the aspx page. As on the server side, the will fire behind a certain event. We need to assign a function name that we are planning to execute on client side once the event is fired. Two parameters will be passed to our function, first is the control that generated the event (sender) and the event arguments. Each event and control has it’s own arguments. DevExpress controls are very function rich on client side, and you can consult the documentation to check all available client side events, functions and properties.

In the following Reference you can check the available client side functionality for the ASPxGridView. If you are interested in the other controls, just browse the interested control namespace that ends with Script.

In the page header (or in a separate .js file) define the following function:

In this function we will check if the context menu event is raised on a grid header or on the grid row. If it is a grid row we should popup a context menu. We can refer to the control on the client side by the ClientInstanceName that we defined for that control in the aspx file, this is done in this example. Each time you put a DevExpress control in your page, you will automatically have at your disposition an utility class called ASPxClientUtils containing several methods that can help you reducing your js code.

Now each time you right click the detail grid row, a context menu that you defined will be shown. What we are missing is the action that needs to be performed once the user chooses a context menu. In order to achieve this we need to add a client side event to our ASPxPopupMenu.

There is some more work to do. As we are going to use a callback method of the detail grid to process the action on the server side, we need to find out the right detail grid that needs to be updated. In order to achieve so, we need to modify our previously defined method OnContextMenu.

What we did here is to save a reference to a detail grid and current visible index (row index on which the mouse was positioned when user right clicked) so we can reused it the Popup ItemClick event that we are going to define:

Once the menu item is chosen we will check if the item is the right one (this is useful if we have several items and we need to perform different actions based on chosen item) then request a callback for the grid on which user is operating right now. We will pass the current visible index as a parameter so we can spot the right element on which we are trying to apply our action. Before we can declare this server side event, we need to specify it in the aspx file:

And than manage this event on server side:

protected void gvDetail_CustomCallback(object sender, ASPxGridViewCustomCallbackEventArgs e)
{
    ASPxGridView grid = sender as ASPxGridView;

    int projectID = (int)grid.GetRowValues(int.Parse(e.Parameters), "ID");
    int currentUserID = (int)grid.GetMasterRowKeyValue();

    List projects = Users.Find(u => u.ID == currentUserID).Projects;
    projects.Find(p => p.ID == projectID).Status = ProjectStatus.New;

    grid.DataSource = projects;
    grid.DataBind();
}

As before, for simplicity we will cast the sender argument to ASPxGridView variable called grid. Then we will retrieve the ID of the project that was selected. The argument we passed before on client side to the PerformCallback function will come handy right now as it will store the necessary data in order to find the interested project (by parsing the e.Parameters property). Next value we need to get is the user ID for which this detail grid is showing the associated projects. We can get it by a handy server side method GetMasterRowKeyValue() which will return a key value of the master grid (as you rememer we defined as a KeyFieldName the ID property of interested entities). Now, once we have the necessary data we can perform the desired actions and rebind the detail grid.

You can now add different actions in the Popup menu and manage them by passing a qualifier in the argument, parsing the argument and performing different operations. You will see this technique in my next blog post, stay tuned.

Programmatically disabling menu items

Unfortunately in this example the user can choose to reset the status of projects that are not in an invalid state. In order to disable the menu item if the state is not “resetable” we will need to make some changes in our code non less passing more information to client side.
Before modifying the JavaScript we will make some considerations. In order to disable an item in the menu, we need to know the condition on which to do it. We can say that the Reset item needs to be disable when the project status is New. This status information we need to pass to the client side somehow. One way to achieve this is to store the status information together with the key value in a custom property. All the DeExpress controls have the possibility to easily add information from server side that will be brought and exposed on client side. This feature is called Custom Properties and you can find more information’s about them here. My technique is to save the Dictionary element to a custom property which will be seen as an array from JavaScript. All what I’m saying may sound confusing, so let’s see an real example.

First of all we will subscribe to OnHtmlRowCreated event. Modify the detail grid in the following way:

Then write down the following code:

protected void gvDetail_HtmlRowCreated(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewTableRowEventArgs e)
{
    if (e.RowType != GridViewRowType.Data) return;

    ProjectStatus status = (ProjectStatus)e.GetValue("Status");
    ASPxGridView grid = sender as ASPxGridView;

    if (grid.JSProperties.ContainsKey("cpStatus"))
    {
        Dictionary values = (Dictionary)grid.JSProperties["cpStatus"];

        if (values.ContainsKey(e.VisibleIndex))
        {
            values[e.VisibleIndex] = status;
        }
        else
        {
            values.Add(e.VisibleIndex, status);
        }

        grid.JSProperties["cpStatus"] = values;
    }
    else
    {
        Dictionary values = new Dictionary();
        values.Add(e.VisibleIndex, status);

        grid.JSProperties.Add("cpStatus", values);
    }
}

This code can seem complex but it isn’t. For each row that is going to be rendered I’m getting it’s visible index value, checking if I already have that value in my Dictionary type variable. If not, I’m adding a new item to my Dictionary together with it’s status. If changed, I’m persisting the new value to a custom JS property of the grid.
This now means that I can easily retrieve this information’s on client side. Modify your OnContextMenu function in the following way:

We first need to retrieve the menu item then set the enable property based on custom JS property value.

That’s all, try your code, it should work.

Aesthetic changes

In order to make your solution look like mine, you will also need to set the theme and a couple of other properties that I will explain here.
First of all the theme. DevExpress control ships with a several themes, check the following web page for more details on how to deploy a theme to your solution. I applied the Acqua theme by importing the necessary files to my solution and changing the web.config in the following way (if not present add the following code inside the system.web section):

I also added to both grids the title panel and the title itself:

In order to make a clicked row visually different I also enabled the AllowFocusedRow property. As it will set focus only on left mouse click, I also modified my JS OnContextMenu function, so it will get focused also on the right mouse click:

The EnableRowHotTrack was also enabled so the grid displays the hot tracked row (a row located under the mouse pointer). You can read more about all these properties on DevExpress site.

Downloads and the source code

You can find the source code of my project for download here.
You can find a trial version of the requested controls here.

Notes and other resources

If a specific version of the controls is not available, you can upgrade this project to the latest version of controls. Use DevXperience Project Converter Tool for upgrading the project. Read more on how to use this tool in the following blog post.
You can find other examples on Master-Detail functionality on DevExpress site. This is Master-Detail – Detail Grid example, and the following is the usage of a tab control inside the detail row template.
In the DevExpress support site you will find several examples with the source code on different techniques this link will show you the solutions for a specific master-detail challenges.

Till the next post!

Cheers!

Natural sort order comparison – Part 1

Mario Majcica January 10, 2012 0 Comments

Introduction

One of the things that are at the origin of my passion for programming is the surprise factor. No matter how many experience and studies you make any new assignment brings something new, something not completely explored.
My excursions to the UI are not so frequent and because of that I do not encounter so often some of classical problems that usually are related to UI, in particular sorting cases.
Recently I worked on a project that in UI lists different versions of one well defined entity. The main property of the entity is the version number and it is type of string. Mentioned property, along with others, is shown inside a ListView control. Sorting the list using the standard string comparison was not an option. By using a standard string comparison, numerical part of the string is not treated as a number, instead is treated as a string, which gives that the string “v1.9” comes after “v1.10”. Actually, if we consider a dot as a decimal separator, this is a correct result. However, in our case we need to consider a dot as a major-minor separator and as this we need to treat the minor part as an integer. In order to make the example easier, we will use only major number as version indicator. The entity we are going to list will contain 13 different versions, from “v1” to “v13”. Using a standard string comparison, once we order our list on version field, the result will be the following:

v1
v10
v11
v12
v13
v2
…
v9

Actually this is correct result if we consider the numbers as ASCII characters, but if we, as usually humans do, would like to consider the numeric part as an integer, this is an undesirable result. What we are expecting to have is the following:

v1
v2
…
v9
v10
v11
v12
v13

You will find a similar behavior in your Windows Explorer each time you order the files by name.

Now how do we solve this task?

Getting started

In the front of a similar problem, I don’t like just to Google a couple of keywords and use the first solution that appears on the search list. Also, the application is flexible enough so we can easily encounter different user settings and the sorting requirements may change. In order to see a bigger picture I decided to explore this area a bit more in detail.
At the end I found two viable approaches to this problem

• Using a StrCmpLogicalW function via P/Invoke
• Writing a custom class that implements IComparer interface and a custom comparison algorithm
  Both methods are valid, each one with its own pros and cons. So let’s see them both in detail.

StrCmpLogicalW via P/Invoke

Inside the shlwapi.dll you will find a method called StrCmpLogicalW, which, as said in method’s description, “Compares two Unicode strings. Digits in the strings are considered as numerical content rather than text.”. This is a pretty handy method, it is easy to implement and it is fast. However it has couple of cons that can make you choose different approach, as example it shows different behavior on Windows XP instead of Windows Vista. Also it is not implemented in Windows 2000 and numerical content is always treated as integer. However, it is very unlikely that you will find yourself in any of listed situations and that the standard functionalities offered by StrCmpLogicalW will be a limitation.

The implementation

For the less experienced programmers, I will show a decent way to create a class that implements IComparer interface and uses the StrCmpLogicalW class in order to calculate the comparison result.
As first we should declare a helper class that will make the calls to the StrCmpLogicalW easier.

[SuppressUnmanagedCodeSecurity]
internal static class SafeNativeMethods
{
    [DllImport("shlwapi.dll", CharSet = CharSet.Unicode)]
    public static extern int StrCmpLogicalW(string psz1, string psz2);
}

Actually there is not a lot to say about this class. It is an internal (the class can be accessed by any code in the same assembly, but not from another assembly) and is declared as static (indicating that it contains only static members). SuppressUnmanagedCodeSecurity attribute is primarily used to increase performance, for further details consult the following page http://msdn.microsoft.com/en-us/library/ms182311(v=vs.80).aspx.
Now we have all the necessary in order to implement the following class

public sealed class NaturalOrderComparerNative : IComparer, IComparer
{
    public int Compare(string a, string b)
    {
        return SafeNativeMethods.StrCmpLogicalW(a, b);
    }
 
    public int Compare(object x, object y)
    {
        string a = x as string;
        string b = y as string;
 
        return Compare(a, b);
    }
}

Implementing the IComparer interface is straight forward as it requires only Compare method to be coded. Compare method has the following signature public int Compare(object x, object y), it compares given objects and returns a value indicating whether first is less than, equal to, or greater than the second one. In case that x is less than y it will return -1, if x equals y will return 0 (zero) and in case x is greater than y will return 1.
I also implemented a generic version of this interface, IComparer. I think that there are no more explanations to do about this small piece of code. If not, just comment under the article and I will try to do my best in order to make this clear.
So let’s summarize:

In the second part of this article (blog post) we will see how to implement a custom algorithm and some usage examples, together with full source code in C# and VB.

Follow us so you can be notified about the new posts.