Perfoming calulations and edits on a partial read-only DXGrid
Today’s writeup is was triggered by a question from a reader. While its something that probaly everyone archieved at some point using the standard Microsoft grid control, the sheer amount of features avaiable on the Devexpress DXGrid make things sometimes less obvious for those starting out with the the suite.
So here is a complete working sample, that does the following:
- Read the starter data from a table, and present it in a grid control
- Allow edits to only one column
- We want to highlight the editable column with a color to draw the user’s attention to it.
- We want the pricing and line total column to use a currency layout
- We want the pricing and line total column to be right aligned
- Perform a calculation everytime an edit has been made
The final result will look like this:
Preparing the data
The data we wil be using is a simple product table from a local sql server data source. We want to offer the user the description, and the price for each product. The user will enter the amount. The line total will be calculated by the grid. The table design and some sample data is shown below:
USE [DxGridDemo]
GO
/****** Object: Table [dbo].[DxgridDemoData] Script Date: 10/16/2011 14:07:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DxgridDemoData](
[id] [int] IDENTITY(1,1) NOT NULL,
[description] [nvarchar](150) NOT NULL,
[amount] [int] NOT NULL,
[price] [decimal](18, 2) NOT NULL,
[linetotal] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_DxgridDemoData] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DxgridDemoData] ADD CONSTRAINT [DF_DxgridDemoData_description] DEFAULT ('') FOR [description]
GO
ALTER TABLE [dbo].[DxgridDemoData] ADD CONSTRAINT [DF_DxgridDemoData_amount] DEFAULT ((0)) FOR [amount]
GO
ALTER TABLE [dbo].[DxgridDemoData] ADD CONSTRAINT [DF_DxgridDemoData_price] DEFAULT ((0)) FOR [price]
GO
ALTER TABLE [dbo].[DxgridDemoData] ADD CONSTRAINT [DF_DxgridDemoData_linetotal] DEFAULT ((0)) FOR [linetotal]
GO
Getting data
To get the data from the database we use a little helper function that retrieves anything specified by an sql command, and returns it as a datatable.
The part for handling teh data is:
/// <summary>
/// Run the passed SQL Command against the database, and return any results as a datatable object
/// </summary>
/// <param name="oCmd"></param>
/// <param name="sConString"></param>
/// <returns></returns>
/// <remarks></remarks>
private static DataTable GetDataTableFromCommand(SqlCommand oCmd, string sConString)
{
using (var oCon = new SqlConnection(sConString))
{
oCon.Open();
oCmd.Connection = oCon;
using (var oDa = new SqlDataAdapter(oCmd))
{
using (var oDs = new DataSet())
{
oDa.Fill(oDs);
return oDs.Tables[0].Copy();
}
}
}
}
Layout
The layout of the grid is done from code to demontrate how to manipulate the grid. Altough it often may be quicker to use the designer doing it from code is fgar more transparant when visiting old code, bacuase you will not have to inspect each grid or view for design time settings that may no longer be obvious.
The layouting part done is:
// Hide the column "id" for the user gridViewDemo.Columns["id"].Visible = false; // layout the line total and price columns as currency gridViewDemo.Columns["price"].DisplayFormat.FormatString = "c"; gridViewDemo.Columns["price"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Custom; gridViewDemo.Columns["linetotal"].DisplayFormat.FormatString = "c"; gridViewDemo.Columns["linetotal"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Custom; // Right align (this is the default for out column type, but the point is demonstrating the technique) gridViewDemo.Columns["price"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far; // Make all columns except the amount column read only gridViewDemo.Columns["price"].OptionsColumn.ReadOnly = true; gridViewDemo.Columns["description"].OptionsColumn.ReadOnly = true; gridViewDemo.Columns["linetotal"].OptionsColumn.ReadOnly = true; // Size the columns gridViewDemo.OptionsView.ColumnAutoWidth = false; gridViewDemo.BestFitColumns(); // Color the amount column for the user gridViewDemo.Columns["amount"].AppearanceCell.BackColor = Color.LightGreen; // Supress the top bar offering grouping gridViewDemo.OptionsView.ShowGroupPanel = false;
Handling the users input
What we now have left is reacting to the users input. This is handled by hooking into a grid event, and then checking for the edit that has been made.
// Get the row being changed var oView = (GridView)sender; var nPrice = Decimal.Parse(oView.GetRowCellValue(e.RowHandle, "price").ToString()); // preloaded var nAmount = int.Parse(oView.GetRowCellValue(e.RowHandle, "amount").ToString()); // changed by user var nLineTotal = nPrice * nAmount; // The new total we want to display // Update the grid oView.SetRowCellValue(e.RowHandle, "linetotal", nLineTotal);
Putting it all together
The full source for the little project is shown below;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;
using DevExpress.XtraGrid.Views.Grid;
namespace DxGridDemo
{
public partial class Form1 : Form
{
private const string SConstring = "Data Source=(local);Initial Catalog=DxGridDemo;Integrated Security=True";
public Form1()
{
InitializeComponent();
}
///
<summary> /// When the user clicks this button, we want to read the data from the database and
/// layout it in the grid. This part of the code does all the layout related work.
/// </summary>
//////private void ButtonGetDataClick(object sender, EventArgs e)
{
// Bind data to the grid
DataTable oTable;
using (var oCmd = new SqlCommand("select * from DxGridDemoData"))
{
oTable = GetDataTableFromCommand(oCmd, SConstring);
}
gridControlDemo.DataSource = oTable;
// Hide the column "id" for the user
gridViewDemo.Columns["id"].Visible = false;
// layout the line total and price columns as currency
gridViewDemo.Columns["price"].DisplayFormat.FormatString = "c";
gridViewDemo.Columns["price"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Custom;
gridViewDemo.Columns["linetotal"].DisplayFormat.FormatString = "c";
gridViewDemo.Columns["linetotal"].DisplayFormat.FormatType = DevExpress.Utils.FormatType.Custom;
// Right align (this is the default for out column type, but the point is demonstrating the technique)
gridViewDemo.Columns["price"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Far;
// Make all columns except the amount column read only
gridViewDemo.Columns["price"].OptionsColumn.ReadOnly = true;
gridViewDemo.Columns["description"].OptionsColumn.ReadOnly = true;
gridViewDemo.Columns["linetotal"].OptionsColumn.ReadOnly = true;
// Size the columns
gridViewDemo.OptionsView.ColumnAutoWidth = false;
gridViewDemo.BestFitColumns();
// Color the amount column for the user
gridViewDemo.Columns["amount"].AppearanceCell.BackColor = Color.LightGreen;
// Supress the top bar offering grouping
gridViewDemo.OptionsView.ShowGroupPanel = false;
}
///
<summary> /// Run the passed SQL Command against the database, and return any results as a datatable object
/// </summary>
/////////
///
private static DataTable GetDataTableFromCommand(SqlCommand oCmd, string sConString)
{
using (var oCon = new SqlConnection(sConString))
{
oCon.Open();
oCmd.Connection = oCon;
using (var oDa = new SqlDataAdapter(oCmd))
{
using (var oDs = new DataSet())
{
oDa.Fill(oDs);
return oDs.Tables[0].Copy();
}
}
}
}
///
<summary> /// Hook into the view's CellValueChanged event, to detect the user making changes to our "amount" column
/// </summary>
//////private void gridViewDemo_CellValueChanged(object sender, DevExpress.XtraGrid.Views.Base.CellValueChangedEventArgs e)
{
// Is the event in the amount column, if not: exit
if (e.Column.FieldName != "amount")
{
return;
}
// Get the row being changed
var oView = (GridView)sender;
var nPrice = Decimal.Parse(oView.GetRowCellValue(e.RowHandle, "price").ToString()); // preloaded
var nAmount = int.Parse(oView.GetRowCellValue(e.RowHandle, "amount").ToString()); // changed by user
var nLineTotal = nPrice * nAmount; // The new total we want to display
// Update the grid
oView.SetRowCellValue(e.RowHandle, "linetotal", nLineTotal);
}
}
}
The working solution can be downloaded below. It has been created using Visual Studio 2010 and the 11.1.8 suite of DevExpress Components.
DxGridDemo