ASPxGridView Excel style – Extending functionality

Mario Majcica January 2, 2013 0 Comments

Introduction

In this post we will continue adding functionality on our previous example. If you missed my previous post you can get it here: ASPxGridView Excel style – Adding notes to grid cells. The main goal for this time is to add inline editing functionality to the grid, in order to be able to update the imports and manage adding, editing and removing credit cards. However we will add also a couple more enhancements in order to show the potential of the grid and offer benefits to the end users. I will not go in the detail about the environment and developing requirements, if interested jump op to my other posts.

Table of contents

1. The necessary and the result
2. Creating database schema
3. Updating our DataService
4. In-line editing
5. Managing Credit Cards
6. Marking the values in the grid
7. Exporting the data
8. Downloads and the source code
9. 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.2.5 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.

Updating our DataService

As first we will add a couple of new methods in our DataService class. We will write down methods that will allow us to update, add and remove the credit card entities.

public static bool AddNewCreditCard(string creditCardName)
{
    string query = @"INSERT INTO tbl_CreditCards ([Name]) VALUES (@Name)";

    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand(query.ToString(), cn))
        {
            cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = creditCardName;

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

public static bool UpdateCreditCard(int creditCardId, string creditCardName)
{
    string query = @"UPDATE tbl_CreditCards SET [Name] = @Name WHERE Id = @creditCardId";

    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("@Name", SqlDbType.NVarChar).Value = creditCardName;

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

public static string GetCreditCard(int creditCardId)
{
    string query = @"SELECT [Name] FROM tbl_CreditCards WHERE Id = @creditCardId";

    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;

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

public static bool RemoveCreditCard(int creditCardId)
{
    string query = @"UPDATE tbl_CreditCards SET [Active] = 0 WHERE Id = @creditCardId";

    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;

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

This code is pretty straight forward. The only exception is the RemoveCreditCard method, which actually doesn’t delete the item and in cascade the related items in the tbl_Import, but only flags the credit card as inactive. In order to make this thing actually work, we need to modify the stored procedure that we defined in previous post sp_GetCreditCardImports.
I will not waste space in this post so I will show you only what you would need to add.

...
WHERE tbl_CreditCards.Active = 1
...

Now is the turn of writing down methods that will Add, Update and Remove imports.

public static bool RemoveImport(int creditCardId, int year, byte month)
{
    string query = @"DELETE FROM tbl_Imports WHERE CreditCardId = @creditCardId AND [Year] = @year AND [Month] = @month";

    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.Int).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;

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

public static bool AddImport(int creditCardId, int year, byte month, decimal import)
{
    string query = @"INSERT INTO tbl_Imports ([CreditCardId], [Year], [Month], [Import])
                VALUES (@creditCardId, @year, @month, @import)";

    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.Int).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;
            cmd.Parameters.Add("@import", SqlDbType.Money).Value = import;

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

public static bool UpdateImport(int creditCardId, int year, byte month, decimal import)
{
    string query = @"UPDATE tbl_Imports SET [Import] = @import
                WHERE CreditCardId = @creditCardId AND [Year] = @year AND [Month] = @month";

    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.Int).Value = year;
            cmd.Parameters.Add("@month", SqlDbType.SmallInt).Value = month;
            cmd.Parameters.Add("@import", SqlDbType.Money).Value = import;

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

This is it. No more code is necessary for managing data and we can now comfortably start with the interface.

In-line editing

Editing the ASPxGridView is very easy. There are several methods for achieving the same result. If you are interested in more you can check this Grid Editing – Edit Modes
A rich set of editors for different column types allows column values to be modified with ease. Still there are a couple of tricks that we will see in the following lines.
In order to start editing a certain row, we can choose the way we are going to start this action. At example you can add a Command Button in the grid itself or simply start editing let’s say on a row double click. There are also many other modes, you have rich client and server side methods in order to start editing one or more rows. Still I will let you discover them and invent new ones. We are going to make our row editable on double click and in order to achieve that we will write down a function (in JavaScript) that is going to be associated to the grids client side event called RowDblClick. Let’s see how this is done.
At first we will add in our ASPX file, inside the grid definition a new section that is called ClientSideEvents (as we already did for other controls) and it will look like:

Then we will write down the JavaScript method itself:

function gvPrevisions_RowDblClick(s, e) { s.StartEditRow(e.visibleIndex); }

It is that simple! Just call StartEditRow JavaScript method that is defined on the grid (in this case I’m using s and not the ClientInstanceName in order to access the grid methods, but here is basically the same) and pass the visible index of the row that we intend to put in edit mode. Kindly this even will provide us the visible index via the function parameter which will indicate the row that user double clicked. If you run the example right now and double click on a row, you should see something similar to this:

Even if this looks nice and simple, you maybe noticed that the fields are not editable. This is because we didn’t binded our grid to a valid and well defined Data Source. Because of this, we have our edit controls in read only state. You can find on Devexpress support web site several issues that are speaking about this behavior and why of it. The good thing is that we can solve this easily. Define the following server side event:

protected void gvImports_CellEditorInitialize(object sender, ASPxGridViewEditorEventArgs e)
{
    if (e.Column.FieldName != "Name" || e.Column.FieldName != "Total")
    {
        e.Editor.ReadOnly = false;

        ASPxTextBox box = e.Editor as ASPxTextBox;
        box.HorizontalAlign = HorizontalAlign.Right;
        box.MaskSettings.Mask = "$ <0..99999g>.<00..99>";
        box.MaskSettings.IncludeLiterals = MaskIncludeLiteralsMode.DecimalSymbol;
        box.ValidationSettings.ErrorDisplayMode = ErrorDisplayMode.ImageWithTooltip;
        box.ValidationSettings.Display = Display.Dynamic;
        box.ClientSideEvents.KeyDown = "gvImports_Cell_KeyDown";

        if (e.Column.FieldName == "1")
            box.Focus();
    }
}

What we defined here is checking if the cells that are going in edit mode are the one we are aiming to put in edit (not the Name and Total columns) and if so we are setting a couple of properties. As first, we are indicating the cell as read only, after that we are casing the control to an ASPxTextBox (which is the right control for these columns) and setting several parameters in order to make the editing look proper. The most important thing is that we are assigning to each of editing cells a client side event KeyDown, in which we will manage the persistence. Let’s see how this important method is defined.

function gvImports_Cell_KeyDown(s, e) {
    switch (e.htmlEvent.keyCode) {
        case 13:
            ASPxClientUtils.PreventEventAndBubble(e.htmlEvent);
            gvImports.UpdateEdit();
            break;
        case 27:
            gvImports.CancelEdit();
            break;
    }
}

If Enter (13) key is pressed meanwhile we are in one of editing cells, we will prevent the defaults for this action and call UpdateEdit method on our grid. In case the user hits Escape key (27) we will just get out of the edit mode by calling CancelEdit method. Once UpdateEdit is called a callback will be generated and the RowUpdating server side method will be executed, in which we will need to handle the persistence of the data. We need to attach the following method to the RowUpdating event:

protected void gvImports_RowUpdating(object sender, ASPxDataUpdatingEventArgs e)
{
    // copy OrderedDictionaries to Hashtables
    // for easier data manipulations
    Hashtable newValues = new Hashtable();
    Hashtable oldValues = new Hashtable();

    foreach (DictionaryEntry item in e.NewValues)
    {
        // if value == 0 then do not insert any value
        newValues.Add(Convert.ToByte(item.Key), (decimal)item.Value == 0 ? null : (decimal?)item.Value);
    }

    foreach (DictionaryEntry item in e.OldValues)
    {
        // transform DbNull to null
        oldValues.Add(Convert.ToByte(item.Key), item.Value == DBNull.Value ? null : (decimal?)item.Value);
    }

    int creditCardID = (int)e.Keys[0];
    int year = (int)cbYears.SelectedItem.Value;

    for (byte i = 1; i <= newValues.Count; i++)
    {
        if ((decimal?)newValues[i] != (decimal?)oldValues[i])
        {
            if (newValues[i] == null)
            {
                // The value was removed
                // so remove the record from table
                DataService.RemoveImport(creditCardID, year, i);
                continue;
            }

            if (oldValues[i] == null)
            {
                // The value was inexistant
                // add record to the table
                DataService.AddImport(creditCardID, year, i, (decimal)newValues[i]);
                continue;
            }

            // it's a change
            // update the record in the table
            DataService.UpdateImport(creditCardID, year, i, (decimal)newValues[i]);
        }
    }

    e.Cancel = true;
}

What happens in this event is that a request for updating rows is sent. We will receive the old values that (of interested updating columns) and the new one that were chosen by the user, in a form of OrderedDictionaries, exposed by two properties in our event argument. The properties are called logically NewValues and OldValues. As OrderedDictionaries are not so handy for processing, I will create two Hashtables and copy the data for both properties from OrderedDictionaries to Hashtables. Meanwhile I'm copying, I will cast eventual values to a nullable decimals and if the value equals zero, I will just insert null instead of the value. Next is retrieving other necessary data as kredit card ID (stored as editing row key and exposed to as always via the event argument) and the current year, which we will get out of our check box.
Now we will process all of our columns values one by one. If the specific column value in Hashtable that contains old values do not match to the one in the new values Hashtable, it means that the value for that column is changed and we need to determine what kind of change occurred before we decide which action to take. If the new value is null it means that we need to remove this item from database therefor we will invoke the appropriate method that we created earlier and pass all the parameters as requested. If the old value is null, it means that we do not have an entry for this item in our database, therefor we will add a new item via a dedicated method. At last if the value exists in both Hashtables we will need to update the value to the new one.
At the end we will set the Cancel property of our event argument to false. The e.Cancel = true allows us to prevent the ASPxGridView from trying to update data by the control itself. There is one small detail left, after we persist the data, we should indicate to the grid that editing is over so it can get back in the non edit mode. The best thing to achieve this is to call CancelEdit method on server side each time we bind the grid (even id sometimes it is not necessary it is not a big overhead and makes sure that each time we update data we make sure that the grid is not in edit mode). Consequently our gvImports_DataBinding event will change in the following way:

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);
    gvImports.CancelEdit();
}

Congratulations, you can now edit this grid values! Smooth, isn't it? 🙂

Managing Credit Cards

As the next requirement we will add the possibility to add new credit cards, update the name of the existing ones or remove them. As for the notes we will add a new pop-up windows which will help us to perform this operations. Defining the popup window is straight forward.

As for the add note popup window, we defined the similar structure for add credit card popup. The main difference is in the validation of the credit card name. We defined the field as mandatory and client side validation will be performed for that field. If empty an error message will appear. Now I will show you the code for all of the three new scripts that we attached to our client side events.

function PersistCreditCard() {
    var isValid = ASPxClientEdit.ValidateEditorsInContainer(null, 'AddCreditCard');

    if (isValid) {
        var command = 'AddNewCreditCard';

        if (currentIsCreditCardEdit)
            command = 'EditNewCreditCard';

        popupAddCreditCard.Hide();
        gvImports.PerformCallback(command + '|' + currentVisibleIndex);
    }
}

function txtAddCreditCard_KeyDown(s, e) {
    switch (e.htmlEvent.keyCode) {
        case 13:
            ASPxClientUtils.PreventEventAndBubble(e.htmlEvent);
            PersistCreditCard();
            break;
        case 27:
            popupAddCreditCard.Hide(); txtAddCreditCard.SetText('');
            break;
    }
}

function btnAddNewCreditCard_Click(s, e) { PersistCreditCard(); }
function btnCancelNewCreditCard_Click(s, e) { popupAddCreditCard.Hide(); txtAddCreditCard.SetText(''); }

The technique is the same used in the previous example, on editing and adding the imports. In case that the client side validation is performed and user have imputed the valid entries, we will preform a callback on the grid (so it can be also automatically refreshed). In the callback we will specify if the operation we are preforming is an edit or an add and in case of the edit pass the selected row index. What we are missing right now is the server side code for persisting the newly added values. To achieve that we are going to modify previously define gvImports_CustomCallback event (it is defined in the previous blog post that you can find here. It will look like this:

protected void gvImports_CustomCallback(object sender, 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;
        case "AddCreditCard":
            DataService.AddNewCreditCard(txtAddCreditCard.Text);
            break;
        case "EditCreditCard":
            DataService.UpdateCreditCard(creditCardID, txtAddCreditCard.Text);
            break;
        case "DeleteCreditCard":
            DataService.DeleteCreditCard(creditCardID);
            break;
    }

    gvImports.DataBind();
}

Based on the command we received, we will analyse other values passed as parameters and call the service method that is associated with the current action. At the end we will rebind the grid so the newly added values can be visible. Simple and straight forward, isn't it?
I will again mention the same trick I used for the editing, and it is here for the same reason I explained earlier when I was explaining the editing of the note, but this time is made on the Add/Edit Credit Card popup. On edit we will retrieve the selected credit card name and we will populate the text box with that obtained value.

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

    int visibleIndex = int.Parse(values[0]);
    int creditCardID = (int)gvImports.GetRowValues(visibleIndex, "Id");

    txtAddCreditCard.Text = DataService.GetCreditCard(creditCardID);
    txtAddCreditCard.Focus();
}

Marking the values in the grid

Meanwhile I was writing the code for the previous requirements I got an idea. Why not let give to user the possibility to put in evidence the values that are lower than the certain amount? Well it is a simple task and it will maybe give you the inspiration to expand this example in other ways. Let's see how to achieve something similar.
First of all we need to create the necessary interface and in order to accomplish that we will define the following controls in our aspx file. After the definition of the GridView add the following:

As you can see, apart for the table I used to achieve a certain layout, I added a text box in which the user will specify the value that we will used as the upper limit for our cells that will be placed in evidence. Also I used a ASPxColorEdit so the user can choose the color he prefers for marking the cells. A clear and confirm button are also there. Then I associated the following code to the relevant client side events (only button clicks in our case):

function btnMark_Click(s, e) {
    gvImports.PerformCallback('Mark');
}

function btnClear_Click(s, e) {
    txtMarkValue.SetText('');
    ceMark.SetColor('#C0C0C0');
    gvImports.PerformCallback('Mark');
}

In case of clear click, I will reset the values of the color chooser and the value text box to their default, then perform a callback so if any value was previously set, it can be restored to the default value. In case of click on mark button, I will perform a callback on the grid and specify the 'Mark' command. Once the callback is performed and the gvImports_CustomCallback event is triggered, you will see that I do not treat the Mark command. This is because it is not necessary at this point. The only important thing is to rebind the grid. Instead in the gvImports_HtmlDataCellPrepared I will add the following at the end of the method:

protected void gvImports_HtmlDataCellPrepared(object sender, ASPxGridViewTableDataCellEventArgs e)
{
    ...

    decimal markValue = 0;
    decimal.TryParse(txtMarkValue.Text, out markValue);

    if (markValue != 0 && (decimal)e.CellValue <= markValue)
    {
        e.Cell.BackColor = ceMark.Color;
    }
}

Whit this I will check if a mark value is set and if it is a valid number I will then check the cell I'm processing in this moment. In case the cell value is less or equal to the value specified in our text box, I will change the background color of that cell and set it to the color that is indicated in our color chooser.
This shows you how can you approach the grid to add extra functionality with ease.

Exporting the data

It will all be nice but useless in case we could not retrieve our data and persist it locally or/and edit it for a presentation or maybe import it in our accounting software. Together wit the Grid, DevExpress offers us an additional control that will handle the exporting procedures. It is easy to setup and the results are great! Let's check how to do it.
First of all we need to add this control in our page. Drag and drop from the toolbox the ASPxGridViewExporter control. Then edit your aspx code and set it to mach the following:

You will already notice some properties that we specified. In order to indicate the grid from which we would like to export the data we need to specify GridViewID property and as it name suggest, set id to the grid view ID. Also we will specify the PaperKind and Landscape property to get the correct formatting of our export. Now we need some button that will request the export in different formats. There are several formats that ASPxGridViewExporter can manage by default. Check the following link for further details Exporting Data - Exporting to PDF, XLS, XLSX and RTF. We will add them to the same line in which the year selector is collocated. The final code will look like this:

We added three buttons and set some of the default and well known properties as image and text. What you should notice here is the UseSubmitBehavior property set to false. In this way the button will not cause the browser's submit mechanism. However a post-back will still be performed. So for each button we will define the event handler on the server side and it will look like this:

#region Export
protected void btnExportExcel_Click(object sender, EventArgs e)
{
    gridExport.FileName = string.Format("{0} {1}", "Credit card imports for year: ", cbYears.SelectedItem.Value);
    gridExport.WriteXlsToResponse();
}

protected void btnExportPdf_Click(object sender, EventArgs e)
{
    gridExport.FileName = string.Format("{0} {1}", "Credit card imports for year: ", cbYears.SelectedItem.Value);
    gridExport.WritePdfToResponse();
}

protected void btnExportCsv_Click(object sender, EventArgs e)
{
    gridExport.FileName = string.Format("{0} {1}", "Credit card imports for year: ", cbYears.SelectedItem.Value);
    gridExport.WriteCsvToResponse();
}
#endregion

On click of each of the defined buttons we will set the file name on a more meaningful string and we will call the respective method on the ASPxGridViewExporter. That is! Check the result!

The final result is pleasing. We added some great functionality with a little effort thanks to the possibilities offered by DevExpress controls.

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

If I was not clear or I gave for granted some of the explanations feel free to comment and I will do my best in order to bring a proper explanation. I didn't went in detail on all of the code as most of it was covered in the previous post, ASPxGridView Excel style – Adding notes to grid cells. If there will be interest I will further expand this example and introduce you to other nice features that we can easily lay down with DevExpress controls. Stay tuned and happy programming!

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!