Microsoft KB Archive/842290

From BetaArchive Wiki

Article ID: 842290

Article Last Modified on 5/17/2007


APPLIES TO

  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Visual C# 2005 Express Edition


For a Microsoft Visual Basic .NET version of this article, see 836672.

SUMMARY

To sum the fields in a Microsoft Windows Forms DataGrid control and to display the calculated totals in a footer, you must first create a user control that inherits from the System.Windows.Forms.DataGrid class. Then you must handle the events that are raised when a cell in this user control is changed.
You must disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To implement custom sorting for your DataGrid control, you must handle the MouseDown event.

You must also disable the footer row of the DataGrid control to prevent users from editing the cells of the footer row. To provide data for the event that prevents users from editing the cells of the DataGrid control, you must define an event arguments class, and then you must define a class that contains methods to paint and to disable the footer row.

To sum the fields and to display the calculated totals in a footer, build the DataGrid control, add an instance of the DataGrid control to a Windows Application project, bind the DataGrid control to the related data, and then build and run the application.


IN THIS TASK

INTRODUCTION

This step-by-step article describes how to sum the fields in a Windows Forms DataGrid control by using Microsoft Visual C# .NET or Visual C# 2005. This article also describes how to customize the Windows Forms DataGrid control to display the calculated totals in a footer.

back to the top

Requirements

This article assumes that you are familiar with the following topics:

  • The Windows Forms DataGrid control
  • Data binding by using Windows Forms and Microsoft ADO.NET
  • Handling and raising events

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

  • Microsoft Windows 2000, Microsoft Windows XP, or Microsoft Windows Server 2003
  • Microsoft Visual Studio .NET or Microsoft Visual Studio 2005

back to the top

Create a Windows Control Library project

  1. Start Visual Studio .NET or Visual Studio 2005.
  2. On the File menu, point to New, and then click Project.
  3. Under Project Types, click Visual C# Projects, and then click Windows Control Library under Templates.


Note In Visual Studio 2005, click Visual C# under Project Types.

  1. In the Name box, type DataGridControl, and then click OK. By default, a user control that is named UserControl1 is created.
  2. On the View menu, click Solution Explorer.
  3. In Solution Explorer, right-click UserControl1.cs, and then click Rename.
  4. Rename the UserControl1.cs file DataGridControlCS.cs.

back to the top

Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods

  1. In Solution Explorer, right-click DataGridControlCS.cs, and then click View Code.

  2. Locate the following code:

    /// Summary description for UserControl1.
///

  3. Declare a delegate for the event that disables the cells of the DataGrid control. To do this, add the following code after the code that you located in step 2:

    // Declare a delegate for the event that disables the cells of the DataGrid control.
public delegate void DataGridDisableCellHandler(object sender, DataGridDisableCellEventArgs e);

  4. Locate the following code:

    public class UserControl1 : System.Windows.Forms.UserControl

  5. Make your Windows Forms DataGrid control inherit from the System.Windows.Forms.DataGrid class. To do this, replace the code that you located in step 4 with the following code:

    public class DataGridControlCS : System.Windows.Forms.DataGrid

  6. Locate the following code in the DataGridControlCS class:

    private System.ComponentModel.Container components = null;

  7. Add the following variable declarations after the code that you located in step 6:

    // Declare private variables for your DataGrid control.
private int RowCount;
private int ColCount;
private int SortedColNum;

private bool Ascending;
private bool CellValueChanged;

private string SourceTable;

private DataView MyDataView;
private DataSet MyDataSet;
private DataRow MyDataRow;
private DataTable MyDataTable;

private ArrayList SummaryCols;
private DataGridCell CurrentDataGridCellLocation;

private static Brush FooterBackColor;
private static Brush FooterForeColor;

  8. Locate the following code in the DataGridControlCS class:

    public UserControl1()
{
    // This call is required by the Windows.Forms Form Designer.
    InitializeComponent();

    // TODO: Add any initialization after the InitComponent call

}

  9. Perform custom initialization in the constructor of your Windows Forms DataGrid control. To do this, replace the code that you located in step 8 with the following code:

    public DataGridControlCS()
{
    InitializeComponent();

    RowCount = 0;
    ColCount = 0;

    CellValueChanged = false;
    Ascending = false;

    MyDataRow = null;

    MyDataTable = new DataTable("NewTable");
    CurrentDataGridCellLocation = new DataGridCell();

    SummaryCols = new ArrayList();
}

  10. Locate the following code in the "Component Designer generated code" area:

    components = new System.ComponentModel.Container();

  11. Associate events with the corresponding event handlers. To do this, replace the code that you located in step 10 with the following code:

    ((System.ComponentModel.ISupportInitialize)(this)).BeginInit();
// Associate events with the corresponding event handlers.
this.MouseDown += new System.Windows.Forms.MouseEventHandler(this.DataGridControlCS_MouseDown);
this.CurrentCellChanged += new System.EventHandler(this.DataGridControlCS_CurrentCellChanged);
((System.ComponentModel.ISupportInitialize)(this)).EndInit();

  12. Locate the following code:

    #endregion

  13. Add the following property definitions after the code that you located in the step 12:

    public DataSet GridDataSet
{
    set
    {
        MyDataSet = value;
    }
}

public ArrayList SummaryColumns
{
    get
    {
        return SummaryCols;
    }
    set
    {
        SummaryCols = value;
    }
}

public string DataSourceTable
{
    get
    {
        return SourceTable;
    }
    set
    {
        SourceTable = value;
    }
}

public static Brush FooterColor
{
    get
    {
        return FooterBackColor;
    }
    set
    {
        FooterBackColor = value;
    }
}

public static Brush FooterFontColor
{
    get
    {
        return FooterForeColor;
    }
    set
    {
        FooterForeColor = value;
    }
}

  14. Disable the default sorting feature of the DataGrid control to prevent the footer row from being sorted. To do this, and to bind the custom DataGrid control to the related data, add the following code after the code that you added in step 13:

    public void BindDataGrid()
{
    MyDataTable = MyDataSet.Tables[0];
    MyDataView = MyDataTable.DefaultView;
    this.DataSource = MyDataView;

    DataGridTableStyle TableStyle = new DataGridTableStyle();
    TableStyle.MappingName = DataSourceTable;

    // Add a Boolean data type column to the DataTable object.
    // You can use this column during your custom sorting.
    MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"));
    MyDataTable.Columns["ID"].DefaultValue = false; 
    MyDataTable.Columns["ID"].ColumnMapping = MappingType.Hidden;
    ColCount = MyDataTable.Columns.Count;

    // Create a footer row for the DataTable object.
    MyDataRow = MyDataTable.NewRow();

    // Set the footer value as an empty string for all columns that contains string values.
    for (int MyIterator = 0; MyIterator < ColCount; MyIterator++)
    {
        if (MyDataTable.Columns[MyIterator].DataType.ToString() == "System.String")
        {
            MyDataRow[MyIterator] = "";
        }
    }

    // Add the footer row to the DataTable object.
    MyDataTable.Rows.Add(MyDataRow);
    RowCount = MyDataTable.Rows.Count;

    // Add a MyDataGridTextBox control to each cell of the DataGrid control.
    MyDataGridTextBox TempDataGridTextBox;   
    for (int MyIterator = 0; MyIterator < ColCount - 1; MyIterator++)
    {
        TempDataGridTextBox = new MyDataGridTextBox(MyIterator);
        TempDataGridTextBox.HeaderText = MyDataTable.Columns[MyIterator].ColumnName;
        TempDataGridTextBox.MappingName = MyDataTable.Columns[MyIterator].ColumnName;
        TempDataGridTextBox.DataGridDisableCell += new DataGridDisableCellHandler(SetEnableValues);
            
        // Disable the default sorting feature of the DataGrid control.
        TableStyle.AllowSorting = false;
        TableStyle.GridColumnStyles.Add(TempDataGridTextBox);
    }

    this.TableStyles.Add(TableStyle);
    this.DataSource = MyDataView;
    MyDataView.ApplyDefaultSort = false;
    MyDataView.AllowNew = false;

    // Set the value of the footer cell.
    DataGridCell MyCell = new DataGridCell();
    MyCell.RowNumber = MyDataTable.Rows.Count - 1;

    // Calculate the value for each of the cells in the footer.
    string[] MyArray = new string[2];
    foreach (String MyString in SummaryCols)
    {
        MyArray = MyString.Split(',');
        MyCell.ColumnNumber = Convert.ToInt32(MyArray[0]);
        this[MyCell] = MyDataTable.Compute(MyArray[1],"ID is null").ToString();
    }

    // Associate the ColumnChanged event of the MyDataTable object with the corresponding event handler.
    this.MyDataTable.ColumnChanged += new DataColumnChangeEventHandler(this.MyDataTable_ColumnChanged);
}

back to the top

Handle the events that are raised when a cell in the DataGrid control is changed

You must handle the ColumnChanged event of the DataTable object, and then you must handle the CurrentCellChanged event of the DataGrid control to track when a cell value in the DataGrid control is changed. To do this, follow these steps:

  1. Handle the ColumnChanged event of the DataTable object. To do this, add the following code after the code that you added in step 14 of the "Inherit from the Windows Forms DataGrid control, and then add variables, properties, and methods" section:

    // Handle the DataTable object's ColumnChanged event
// to track whether the value in a cell has changed.
private void MyDataTable_ColumnChanged(object sender, DataColumnChangeEventArgs e)
{
    int Row, Col;
    Row = 0;
    Col = 0;

    // Determine the row that contains the changed cell.
    foreach (DataRow TempDataRow in MyDataTable.Rows)
    {
        if (TempDataRow.Equals(e.Row))
        {
            CurrentDataGridCellLocation.RowNumber = Row;
            CellValueChanged = true;
            break;
        }
        Row++;
    }

    // Determine the column that contains the changed cell.
    foreach (DataColumn TempDataColumn in MyDataTable.Columns)
    {
        if (TempDataColumn.Equals(e.Column))
        {
            CurrentDataGridCellLocation.ColumnNumber = Col;
            CellValueChanged = true;
        break;
        }
        Col++;
    }
}

  2. Handle the CurrentCellChanged event of the DataGrid control. To do this, add the following code after the code that you added in step 1:

    // Handle the CurrentCellChanged event of the DataGrid control.
private void DataGridControlCS_CurrentCellChanged(object sender, System.EventArgs e)
{
    if (CellValueChanged == true)
    {
        DataGridCell MyCell = new DataGridCell();
        MyCell.RowNumber = MyDataTable.Rows.Count - 1;

        // Calculate the value for each cell in the footer.
        string[] MyArray = new string[2];
        foreach (String MyString in SummaryCols)
        {
            MyArray = MyString.Split(',');
            MyCell.ColumnNumber = Convert.ToInt32(MyArray[0]);
            this[MyCell] = MyDataTable.Compute(MyArray[1],"ID is null").ToString();
        }
    }
    CellValueChanged = false;
}

back to the top

Handle the MouseDown event of the DataGrid control to implement custom sorting

Because you have disabled the default sorting feature of the DataGrid control, you must perform custom sorting when a user clicks a column header. You must handle the MouseDown event of the DataGrid control to implement custom sorting.

To do this, add the following event handler after the code that you added in step 2 of the "Handle the events that are raised when a cell in the DataGrid control is changed" section: 

// Handle the MouseDown event to perform custom sorting.
private void DataGridControlCS_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
{
    DataGrid.HitTestInfo MyHitTestInfo;
    MyHitTestInfo = this.HitTest(e.X, e.Y);

    string ColName;

    if (MyHitTestInfo.Type == DataGrid.HitTestType.ColumnHeader)  
    {
        int ColNum = MyHitTestInfo.Column;

        if (ColNum != -1)
        {
            ColName = MyDataTable.Columns[ColNum].ColumnName;

            // Perform custom sorting. To do this, always sort the Boolean data type column in
            // ascending order so that the footer row stays at the end.
            char[] MyChar = {'↑','↓'};

            string NewString = this.TableStyles[0].GridColumnStyles[SortedColNum].HeaderText.TrimEnd(MyChar).Trim();
            this.TableStyles[0].GridColumnStyles[SortedColNum].HeaderText = NewString;

            if (Ascending == true)
            {
                MyDataView.Sort = "ID Asc," + ColName + " desc";
                Ascending = false;
                this.TableStyles[0].GridColumnStyles[ColNum].HeaderText =
                    this.TableStyles[0].GridColumnStyles[ColNum].HeaderText +  " ↑";
                SortedColNum = ColNum;
            }
            else
            {
                MyDataView.Sort = "ID Asc," + ColName + " asc";
                Ascending = true;
                this.TableStyles[0].GridColumnStyles[ColNum].HeaderText =
                    this.TableStyles[0].GridColumnStyles[ColNum].HeaderText +  " ↓";
                SortedColNum = ColNum;
            }
        }
    }
}

back to the top

Disable the footer row of the DataGrid control

To disable the footer row of the DataGrid control, add the following code after the code that you added in the "Handle the MouseDown event of the DataGrid control to implement custom sorting" section: 

// Disable the footer row of the DataGrid control.
public void SetEnableValues(object sender, DataGridDisableCellEventArgs e)
{
    // Disable a footer cell of the DataGrid control.
    if (e.Row == RowCount-1)
    {
        e.EnableValue = false; 
    }
    else
    {
        e.EnableValue = true;
    }
}

back to the top

Define an event arguments class that provides data for the DataGridDisableCell event

To define an event arguments class that provides data for the DataGridDisableCell event, follow these steps:

  1. Locate the end of the definition of the DataGridControlCS class. To do this, locate the following code:

                e.EnableValue = true;
        }
    }
}

  2. Add the following code after the code that you located in step 1:

    // Define a custom event arguments class that inherits from the EventArgs class.
public class DataGridDisableCellEventArgs : EventArgs
{
    private int MyCol;
    private int MyRow;
    private bool MyEnableValue;

    public DataGridDisableCellEventArgs(int Row, int Col)
    {
        MyRow = Row;
        MyCol = Col;
        MyEnableValue = true;
    }

    public int Column
    {
        get
        {
            return MyCol;
        }
        set
        {
            MyCol = value;
        }
    }

    public int Row
    {
        get
        {
            return MyRow;
        }
        set
        {
            MyRow = value;
        }
    }

    public bool EnableValue
    {
        get
        {
            return MyEnableValue;
        }
        set
        {
            MyEnableValue = value;
        }
    }
}

back to the top

Define a class that contains methods to paint and to disable the footer row

To define a class that contains methods to paint and to disable the footer row, add the following code after the code that you added in step 2 of the "Define an event arguments class that provides data for the DataGridDisableCell event" section: 

public class MyDataGridTextBox : DataGridTextBoxColumn
{
    // Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.
    public event DataGridDisableCellHandler DataGridDisableCell;

    private int MyCol;

    // Save the column number of the column to add the MyDataGridTextBox control to.
    public MyDataGridTextBox(int column)
    {
        MyCol = column;
    }

    // Override the Paint method to set colors for the footer row.
    protected override void Paint(
        System.Drawing.Graphics g,
        System.Drawing.Rectangle bounds,
        System.Windows.Forms.CurrencyManager source,
        int rowNum,
        System.Drawing.Brush backBrush,
        System.Drawing.Brush foreBrush,
        bool alignToRight)
    {
        if (DataGridDisableCell != null)
        {
            // Initialize the event arguments by using the number
            // of the current row and the current column.
            DataGridDisableCellEventArgs e = new DataGridDisableCellEventArgs(rowNum, MyCol);

            // Raise the DataGridDisableCell event.
            DataGridDisableCell(this, e);

            // Set the foreground color and the background color for the footer row.
            if (!e.EnableValue)
            {
                if ((DataGridControlCS.FooterColor == null) || (DataGridControlCS.FooterFontColor == null))
                {
                    backBrush = Brushes.White;
                    foreBrush = Brushes.Black;
                }
                else
                {
                    backBrush = DataGridControlCS.FooterColor;
                    foreBrush = DataGridControlCS.FooterFontColor;
                }
            }
        }

        // Call the Paint event of the DataGridTextBoxColumn class.
        base.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight);
    }

    // Override the Edit method to disable the footer row.
    protected override void Edit(
        System.Windows.Forms.CurrencyManager source,
        int rowNum,
        System.Drawing.Rectangle bounds,
        bool readOnlyFlag,
        string instantText,
        bool cellIsVisible)
    {
        DataGridDisableCellEventArgs e = null;

        if (DataGridDisableCell != null)
        {
            // Initialize the event arguments by using the number
            // of the current row and the current column.
            e = new DataGridDisableCellEventArgs(rowNum, MyCol);

            // Raise the DataGridDisableCell event.
            DataGridDisableCell(this, e);
        }

        // Call the Edit event of the DataGridTextBoxColumn
        // class for all rows other than the footer row.
        if (e.EnableValue)
        {
            base.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible);
        }
    }
}

back to the top

Save the DataGridControlCS.cs file and build the DataGrid control

  1. On the File menu, click Save DataGridControlCS.cs As.
  2. In the Save File As dialog box, click the arrow next to the Save button, and then click Save with Encoding.
  3. You receive a message to replace the existing DataGridControlCS.cs file. Click Yes.
  4. In the Advanced Save Options dialog box, click Unicode (UTF-8 with signature) - Codepage 65001 in the Encoding box, and then click OK.
  5. On the Build menu, click Build DataGridControl to build the DataGridControl.dll assembly.

back to the top

Create a Windows Application project that uses the DataGrid control

  1. In Solution Explorer, right-click the DataGridControl solution, point to Add, and then click New Project.
  2. In the Add New Project dialog box, click Visual C# Projects under Project Types.


Note In Visual Studio 2005, click Visual C# under Project Types.

  1. Under Templates, click Windows Application.
  2. In the Name box, type TestApplication, and then click OK. By default, a Windows Form that is named Form1 is created.
  3. In Solution Explorer, right-click TestApplication, and then click Set as StartUp Project.
  4. On the View menu, click Toolbox.
  5. Use one of the following methods, depending on the version of Visual Studio .NET that you have installed:
    • If you are using Visual Studio 2005, click Choose ToolBox Items on the Tools menu.
    • If you are using Visual Studio .NET 2003, click Add/Remove Toolbox Items on the Tools menu.
    • If you are using Visual Studio .NET 2002, click Customize Toolbox on the Tools menu.


The Customize Toolbox dialog box appears.

  1. On the .NET Framework Components tab, click Browse.
  2. In the Open dialog box, locate, and then click the DataGridControl.dll assembly that you created in step 5 of the "Save the DataGridControlCS.cs file, and then build the DataGrid control" section.
  3. Click Open, and then click OK. The DataGridControlCS control is added to the toolbox.
  4. In the toolbox, double-click the DataGridControlCS control to add the DataGridControlCS1 control to the Form1 Windows form.
  5. Click Form1.
  6. On the View menu, click Properties Window.
  7. Set the Size property to 450, 200.
  8. In Design view of the Form1 Windows form, click the DataGridControlCS1 control.
  9. On the View menu, click Properties Window.
  10. Set the Size property to 420, 115.

back to the top

Bind the custom DataGrid control to the related data

  1. In Solution Explorer, right-click Form1.cs, and then click View Code.

  2. Locate the following code:

    using System.Data;

  3. Use the System.Data.SqlClient namespace. To do this, add the following code after the code that you located in step 1:

    using System.Data.SqlClient;

  4. Locate the following code:

    private System.ComponentModel.Container components = null;

  5. Add the following variable declarations after the code that you located in step 4:

    string MyConnString;
DataSet MyDataSet = null;

SqlDataAdapter MyDataAdapter = null;
SqlConnection MyConn;

  6. Locate the following code in the "Windows Form Designer generated code" area:

    ((System.ComponentModel.ISupportInitialize)(this.dataGridControlCS1)).EndInit();

  7. Associate the Form1_Load event with the corresponding event handler. To do this, add the following code before the code that you located in step 6:

    this.Load += new System.EventHandler(this.Form1_Load);

  8. Locate the following code:

    static void Main()
{
    Application.Run(new Form1());
}

  9. Bind the DataGrid control to the related data. To do this, add the following code after the code that you located in step 8:

    Note In the following code, replace <ServerName> with the appropriate value for an instance of Microsoft SQL Server:

    private void Form1_Load(object sender, System.EventArgs e)
{
    string sqlString = "SELECT * FROM discounts";

    MyConnString = "server=<ServerName>;Integrated Security=SSPI;database=pubs";
    MyConn = new SqlConnection(MyConnString);
    MyDataAdapter = new SqlDataAdapter(sqlString, MyConn);
    MyDataSet = new DataSet();

    try
    {
        MyDataAdapter.Fill(MyDataSet, "discounts");
        // Specify the dataset that you want your DataGrid control to use.
        dataGridControlCS1.GridDataSet = MyDataSet;
        // Specify the source table that you want your DataGrid control to use.
        dataGridControlCS1.DataSourceTable = "discounts";

        // Add the columns that you want to sum to an array list. Use the following format:
        // "<ColumnNumber>,summing expression"
        ArrayList summary = new ArrayList();
        summary.Add("2,sum(lowqty)");
        summary.Add("3,sum(highqty)");
        // Map the array list to the SummaryColumns property of your DataGrid control.
        dataGridControlCS1.SummaryColumns = summary;

        // Set the foreground color and the background color for the footer row.
        DataGridControl.DataGridControlCS.FooterColor = Brushes.BlueViolet;
        DataGridControl.DataGridControlCS.FooterFontColor = Brushes.White;

        // Bind the DataGrid control to the related data.
        dataGridControlCS1.BindDataGrid();
    }
    catch (SqlException DatabaseException)
    {
        MessageBox.Show("Database exception: " + DatabaseException.Message);
    }
    catch (Exception OtherException)
    {
        MessageBox.Show(OtherException.Message);
    }
    finally
    {
        // Dispose the data adapter, and then close the connection.
        MyDataAdapter.Dispose();
        MyConn.Dispose();
    }
}

back to the top

Complete code listing

DataGridControlCS.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Windows.Forms;

namespace DataGridControl
{
   /// <summary>
   /// Summary description for UserControl1.
   /// </summary>
    
   // Declare a delegate for the event that disables the cells of the DataGrid control.
   public delegate void DataGridDisableCellHandler(object sender, DataGridDisableCellEventArgs e);

   public class DataGridControlCS : System.Windows.Forms.DataGrid
   {
      /// <summary>
      /// Required designer variable.
      /// </summary>
      private System.ComponentModel.Container components = null;

      // Declare private variables for your DataGrid control.
      private int RowCount;
      private int ColCount;
      private int SortedColNum;

      private bool Ascending;
      private bool CellValueChanged;

      private string SourceTable;

      private DataView MyDataView;
      private DataSet MyDataSet;
      private DataRow MyDataRow;
      private DataTable MyDataTable;

      private ArrayList SummaryCols;
      private DataGridCell CurrentDataGridCellLocation;

      private static Brush FooterBackColor;
      private static Brush FooterForeColor;

      public DataGridControlCS()
      {
         InitializeComponent();

         RowCount = 0;
         ColCount = 0;

         CellValueChanged = false;
         Ascending = false;

         MyDataRow = null;

         MyDataTable = new DataTable("NewTable");
         CurrentDataGridCellLocation = new DataGridCell();

         SummaryCols = new ArrayList();
      }

      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         if( disposing )
         {
            if( components != null )
               components.Dispose();
         }
         base.Dispose( disposing );
      }

        #region Component Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify 
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent()
      {
         ((System.ComponentModel.ISupportInitialize)(this)).BeginInit();
         // Associate events with the corresponding event handlers.
         this.MouseDown += new System.Windows.Forms.MouseEventHandler(this.DataGridControlCS_MouseDown);
         this.CurrentCellChanged += new System.EventHandler(this.DataGridControlCS_CurrentCellChanged);
         ((System.ComponentModel.ISupportInitialize)(this)).EndInit();
      }
        #endregion

      public DataSet GridDataSet
      {
         set
         {
            MyDataSet = value;
         }
      }

      public ArrayList SummaryColumns
      {
         get
         {
            return SummaryCols;
         }
         set
         {
            SummaryCols = value;
         }
      }

      public string DataSourceTable
      {
         get
         {
            return SourceTable;
         }
         set
         {
            SourceTable = value;
         }
      }

      public static Brush FooterColor
      {
         get
         {
            return FooterBackColor;
         }
         set
         {
            FooterBackColor = value;
         }
      }

      public static Brush FooterFontColor
      {
         get
         {
            return FooterForeColor;
         }
         set
         {
            FooterForeColor = value;
         }
      }

      public void BindDataGrid()
      {
         MyDataTable = MyDataSet.Tables[0];
         MyDataView = MyDataTable.DefaultView;
         this.DataSource = MyDataView;

         DataGridTableStyle TableStyle = new DataGridTableStyle();
         TableStyle.MappingName = DataSourceTable;

         // Add a Boolean data type column to the DataTable object.
         // You can use this column during your custom sorting.
         MyDataTable.Columns.Add("ID", System.Type.GetType("System.Boolean"));
         MyDataTable.Columns["ID"].DefaultValue = false;    
         MyDataTable.Columns["ID"].ColumnMapping = MappingType.Hidden;
         ColCount = MyDataTable.Columns.Count;

         // Create a footer row for the DataTable object.
         MyDataRow = MyDataTable.NewRow();

         // Set the footer value as an empty string for all columns that contains string values.
         for (int MyIterator = 0; MyIterator < ColCount; MyIterator++)
         {
            if (MyDataTable.Columns[MyIterator].DataType.ToString() == "System.String")
            {
               MyDataRow[MyIterator] = "";
            }
         }

         // Add the footer row to the DataTable object.
         MyDataTable.Rows.Add(MyDataRow);
         RowCount = MyDataTable.Rows.Count;

         // Add a MyDataGridTextBox control to each cell of the DataGrid control.
         MyDataGridTextBox TempDataGridTextBox;   
         for (int MyIterator = 0; MyIterator < ColCount - 1; MyIterator++)
         {
            TempDataGridTextBox = new MyDataGridTextBox(MyIterator);
            TempDataGridTextBox.HeaderText = MyDataTable.Columns[MyIterator].ColumnName;
            TempDataGridTextBox.MappingName = MyDataTable.Columns[MyIterator].ColumnName;
            TempDataGridTextBox.DataGridDisableCell += new DataGridDisableCellHandler(SetEnableValues);
            
            // Disable the default sorting feature of the DataGrid control.
            TableStyle.AllowSorting = false;
            TableStyle.GridColumnStyles.Add(TempDataGridTextBox);
         }

         this.TableStyles.Add(TableStyle);
         this.DataSource = MyDataView;
         MyDataView.ApplyDefaultSort = false;
         MyDataView.AllowNew = false;

         // Set the value of the footer cell.
         DataGridCell MyCell = new DataGridCell();
         MyCell.RowNumber = MyDataTable.Rows.Count - 1;

         // Calculate the value for each of the cells in the footer.
         string[] MyArray = new string[2];
         foreach (String MyString in SummaryCols)
         {
            MyArray = MyString.Split(',');
            MyCell.ColumnNumber = Convert.ToInt32(MyArray[0]);
            this[MyCell] = MyDataTable.Compute(MyArray[1],"ID is null").ToString();
         }

         // Associate the ColumnChanged event of the MyDataTable object with the corresponding event handler.
         this.MyDataTable.ColumnChanged += new DataColumnChangeEventHandler(this.MyDataTable_ColumnChanged);
      }

      // Handle the DataTable object's ColumnChanged event
      // to track whether the value in a cell has changed.
      private void MyDataTable_ColumnChanged(object sender, DataColumnChangeEventArgs e)
      {
         int Row, Col;
         Row = 0;
         Col = 0;

         // Determine the row that contains the changed cell.
         foreach (DataRow TempDataRow in MyDataTable.Rows)
         {
            if (TempDataRow.Equals(e.Row))
            {
               CurrentDataGridCellLocation.RowNumber = Row;
               CellValueChanged = true;
               break;
            }
            Row++;
         }

         // Determine the column that contains the changed cell.
         foreach (DataColumn TempDataColumn in MyDataTable.Columns)
         {
            if (TempDataColumn.Equals(e.Column))
            {
               CurrentDataGridCellLocation.ColumnNumber = Col;
               CellValueChanged = true;
               break;
            }
            Col++;
         }
      }

      // Handle the CurrentCellChanged event of the DataGrid control.
      private void DataGridControlCS_CurrentCellChanged(object sender, System.EventArgs e)
      {
         if (CellValueChanged == true)
         {
            DataGridCell MyCell = new DataGridCell();
            MyCell.RowNumber = MyDataTable.Rows.Count - 1;

            // Calculate the value for each cell in the footer.
            string[] MyArray = new string[2];
            foreach (String MyString in SummaryCols)
            {
               MyArray = MyString.Split(',');
               MyCell.ColumnNumber = Convert.ToInt32(MyArray[0]);
               this[MyCell] = MyDataTable.Compute(MyArray[1],"ID is null").ToString();
            }
         }
         CellValueChanged = false;
      }

      // Handle the MouseDown event to perform custom sorting.
      private void DataGridControlCS_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
      {
         DataGrid.HitTestInfo MyHitTestInfo;
         MyHitTestInfo = this.HitTest(e.X, e.Y);

         string ColName;

         if (MyHitTestInfo.Type == DataGrid.HitTestType.ColumnHeader)  
         {
            int ColNum = MyHitTestInfo.Column;

            if (ColNum != -1)
            {
               ColName = MyDataTable.Columns[ColNum].ColumnName;

               // Perform custom sorting. To do this, always sort the Boolean data type column in
               // ascending order so that the footer row stays at the end.
               char[] MyChar = {'↑','↓'};

               string NewString = this.TableStyles[0].GridColumnStyles[SortedColNum].HeaderText.TrimEnd(MyChar).Trim();
               this.TableStyles[0].GridColumnStyles[SortedColNum].HeaderText = NewString;

               if (Ascending == true)
               {
                  MyDataView.Sort = "ID Asc," + ColName + " desc";
                  Ascending = false;
                  this.TableStyles[0].GridColumnStyles[ColNum].HeaderText =
                     this.TableStyles[0].GridColumnStyles[ColNum].HeaderText +  " ↑";
                  SortedColNum = ColNum;
               }
               else
               {
                  MyDataView.Sort = "ID Asc," + ColName + " asc";
                  Ascending = true;
                  this.TableStyles[0].GridColumnStyles[ColNum].HeaderText =
                     this.TableStyles[0].GridColumnStyles[ColNum].HeaderText +  " ↓";
                  SortedColNum = ColNum;
               }
            }
         }
      }

      // Disable the footer row of the DataGrid control.
      public void SetEnableValues(object sender, DataGridDisableCellEventArgs e)
      {
         // Disable a footer cell of the DataGrid control.
         if (e.Row == RowCount-1)
         {
            e.EnableValue = false; 
         }
         else
         {
            e.EnableValue = true;
         }
      }
   }

   // Define a custom event arguments class that inherits from the EventArgs class.
   public class DataGridDisableCellEventArgs : EventArgs
   {
      private int MyCol;
      private int MyRow;
      private bool MyEnableValue;

      public DataGridDisableCellEventArgs(int Row, int Col)
      {
         MyRow = Row;
         MyCol = Col;
         MyEnableValue = true;
      }

      public int Column
      {
         get
         {
            return MyCol;
         }
         set
         {
            MyCol = value;
         }
      }

      public int Row
      {
         get
         {
            return MyRow;
         }
         set
         {
            MyRow = value;
         }
      }

      public bool EnableValue
      {
         get
         {
            return MyEnableValue;
         }
         set
         {
            MyEnableValue = value;
         }
      }
   }

   public class MyDataGridTextBox : DataGridTextBoxColumn
   {
      // Declare an event for the DataGridDisableCellEventHandler delegate that you have defined.
      public event DataGridDisableCellHandler DataGridDisableCell;

      private int MyCol;

      // Save the column number of the column to add the MyDataGridTextBox control to.
      public MyDataGridTextBox(int column)
      {
         MyCol = column;
      }

      // Override the Paint method to set colors for the footer row.
      protected override void Paint(
         System.Drawing.Graphics g,
         System.Drawing.Rectangle bounds,
         System.Windows.Forms.CurrencyManager source,
         int rowNum,
         System.Drawing.Brush backBrush,
         System.Drawing.Brush foreBrush,
         bool alignToRight)
      {
         if (DataGridDisableCell != null)
         {
            // Initialize the event arguments by using the number
            // of the current row and the current column.
            DataGridDisableCellEventArgs e = new DataGridDisableCellEventArgs(rowNum, MyCol);

            // Raise the DataGridDisableCell event.
            DataGridDisableCell(this, e);

            // Set the foreground color and the background color for the footer row.
            if (!e.EnableValue)
            {
               if ((DataGridControlCS.FooterColor == null) || (DataGridControlCS.FooterFontColor == null))
               {
                  backBrush = Brushes.White;
                  foreBrush = Brushes.Black;
               }
               else
               {
                  backBrush = DataGridControlCS.FooterColor;
                  foreBrush = DataGridControlCS.FooterFontColor;
               }
            }
         }

         // Call the Paint event of the DataGridTextBoxColumn class.
         base.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight);
      }

      // Override the Edit method to disable the footer row.
      protected override void Edit(
         System.Windows.Forms.CurrencyManager source,
         int rowNum,
         System.Drawing.Rectangle bounds,
         bool readOnlyFlag,
         string instantText,
         bool cellIsVisible)
      {
         DataGridDisableCellEventArgs e = null;

         if (DataGridDisableCell != null)
         {
            // Initialize the event arguments by using the number
            // of the current row and the current column.
            e = new DataGridDisableCellEventArgs(rowNum, MyCol);

            // Raise the DataGridDisableCell event.
            DataGridDisableCell(this, e);
         }

         // Call the Edit event of the DataGridTextBoxColumn
         // class for all rows other than the footer row.
         if (e.EnableValue)
         {
            base.Edit(source, rowNum, bounds, readOnlyFlag, instantText, cellIsVisible);
         }
      }
   }
}

Form1.cs

Note In the following code, replace <ServerName> with the appropriate value for an instance of SQL Server. 

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace TestApplication
{
   /// <summary>
   /// Summary description for Form1.
   /// </summary>
   public class Form1 : System.Windows.Forms.Form
   {
      private DataGridControl.DataGridControlCS dataGridControlCS1;
      /// <summary>
      /// Required designer variable.
      /// </summary>
      private System.ComponentModel.Container components = null;

      string MyConnString;
      DataSet MyDataSet = null;

      SqlDataAdapter MyDataAdapter = null;
      SqlConnection MyConn;

      public Form1()
      {
         //
         // Required for Windows Form Designer support
         //
         InitializeComponent();

         //
         // TODO: Add any constructor code after InitializeComponent call
         //
      }

      /// <summary>
      /// Clean up any resources being used.
      /// </summary>
      protected override void Dispose( bool disposing )
      {
         if( disposing )
         {
            if (components != null) 
            {
               components.Dispose();
            }
         }
         base.Dispose( disposing );
      }

        #region Windows Form Designer generated code
      /// <summary>
      /// Required method for Designer support - do not modify
      /// the contents of this method with the code editor.
      /// </summary>
      private void InitializeComponent()
      {
         this.dataGridControlCS1 = new DataGridControl.DataGridControlCS();
         ((System.ComponentModel.ISupportInitialize)(this.dataGridControlCS1)).BeginInit();
         this.SuspendLayout();
         // 
         // dataGridControlCS1
         // 
         this.dataGridControlCS1.DataMember = "";
         this.dataGridControlCS1.DataSourceTable = null;
         this.dataGridControlCS1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
         this.dataGridControlCS1.Location = new System.Drawing.Point(0, 0);
         this.dataGridControlCS1.Name = "dataGridControlCS1";
         this.dataGridControlCS1.Size = new System.Drawing.Size(420, 115);
         this.dataGridControlCS1.TabIndex = 0;
         // 
         // Form1
         // 
         this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
         this.ClientSize = new System.Drawing.Size(442, 173);
         this.Controls.Add(this.dataGridControlCS1);
         this.Name = "Form1";
         this.Text = "Form1";
         this.Load += new System.EventHandler(this.Form1_Load);
         ((System.ComponentModel.ISupportInitialize)(this.dataGridControlCS1)).EndInit();
         this.ResumeLayout(false);

      }
        #endregion

      /// <summary>
      /// The main entry point for the application.
      /// </summary>
      [STAThread]
      static void Main() 
      {
         Application.Run(new Form1());
      }

      private void Form1_Load(object sender, System.EventArgs e)
      {
         string sqlString = "SELECT * FROM discounts";

         MyConnString = "server=<ServerName>;Integrated Security=SSPI;database=pubs";
         MyConn = new SqlConnection(MyConnString);
         MyDataAdapter = new SqlDataAdapter(sqlString, MyConn);
         MyDataSet = new DataSet();

         try
         {
            MyDataAdapter.Fill(MyDataSet, "discounts");
            // Specify the dataset that you want your DataGrid control to use.
            dataGridControlCS1.GridDataSet = MyDataSet;
            // Specify the source table that you want your DataGrid control to use.
            dataGridControlCS1.DataSourceTable = "discounts";

            // Add the columns that you want to sum to an array list. Use the following format:
            // "<ColumnNumber>,summing expression"
            ArrayList summary = new ArrayList();
            summary.Add("2,sum(lowqty)");
            summary.Add("3,sum(highqty)");
            // Map the array list to the SummaryColumns property of your DataGrid control.
            dataGridControlCS1.SummaryColumns = summary;

            // Set the foreground color and the background color for the footer row.
            DataGridControl.DataGridControlCS.FooterColor = Brushes.BlueViolet;
            DataGridControl.DataGridControlCS.FooterFontColor = Brushes.White;

            // Bind the DataGrid control to the related data.
            dataGridControlCS1.BindDataGrid();
         }
         catch (SqlException DatabaseException)
         {
            MessageBox.Show("Database exception: " + DatabaseException.Message);
         }
         catch (Exception OtherException)
         {
            MessageBox.Show(OtherException.Message);
         }
         finally
         {
            // Dispose the data adapter, and then close the connection.
            MyDataAdapter.Dispose();
            MyConn.Dispose();
         }
      }
   }
}

back to the top

Build and run your application

  1. On the Build menu, click Build Solution.
  2. On the Debug menu, click Start.


The Form1 Windows form appears. Your custom DataGrid control is present on the Form1 Windows form. The footer row of the DataGrid control contains the sums of the values of the fields that you specified to sum. You cannot edit the footer row. However, when you change the value in any one of the cells, the corresponding footer cell is updated.

back to the top

REFERENCES

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

326339 How to create a summary row for a DataGrid in ASP.NET by using Visual C# .NET


For more information, visit the following Microsoft Developer Network (MSDN) Web sites:

Responding to clicks in the Windows Forms DataGrid control
http://msdn2.microsoft.com/en-us/library/aa983712(VS.71).aspx


DataTable.Compute method
http://msdn2.microsoft.com/en-us/library/system.data.datatable.compute(vs.71).aspx


Data update events
http://msdn2.microsoft.com/en-us/library/aa983597(VS.71).aspx


back to the top

Keywords: kbbrush kbclientserver kbdatabase kbwindowsforms kbdatabinding kbdataadapter kbhowtomaster KB842290



Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.


Retrieved from ‘https://www.betaarchive.com/wiki/index.php?title=Microsoft_KB_Archive/842290&oldid=334443’
the Creative Commons 3.0 ShareAlike (except the Microsoft KB Archive).
Powered by MediaWiki