Financial Reports On Form’s Controls

Sam, you can get a data grid view to do group headers, column totals or anything else, I am providing this code as an example of what can be done even though this is old code and could be made better but I wrote this when I was learning C# fro the first time.  look at LoadData in particular, this is where some of the dirty work is done.


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Drawing.Design;
using System.Collections;

namespace LizardControls
{
    [Serializable]
    [LicenseProvider(typeof(lizardLicenceProvider))]
    public partial class lizardDataGridView : DataGridView
    {
        private License license = null;
        internal bool canDesign;

        struct fieldTotals{
      internal string field;
      internal double total;
      internal int col;
      }
    struct linkFields{
      internal string getFromTable;
      internal string getFromTableField;
      internal string getFieldName;
      internal string tableName;
      internal string fieldName;
      }
    private bool _switchRowColors = false;
    private bool flipColor=false;
    private bool _connect;
    private bool _fill=false;
    internal bool gotFieldValues=false;
    internal bool gotTableValues = false;
    internal bool gotLinkTable = false;
    private int _selectedRow = -1;
    private string _prettyName;
    private string _where= "";
    private string _tableName;
    private string _orderBy=null;
    private bool _wantRecordId = false;
    private string _recordIdFieldName = null;
    private string _lookupFields;       //format  sourceTableName.postId,linkTableName.id;
    private string _totalsFields;
    private Color _rowColor;
    private Color _altRowColor;
    private Color _TotalsRowColor;
    private string format;
    private fieldTotals[] ft= new fieldTotals[0];
    private linkFields[] lf = new linkFields[0];
    private object[] FieldDataType=new object[0];   //hold column field def
    internal bool rowDirty = false;
    internal string cellBuff = null;
    internal int[] dirtyRows = new int[0];
    private lizardSQLSource sq=null;
    private lizardLabel lb =null;
    private int[] recordIds = new int[0];
    private string[] _fieldList = new string[0];
    private string[] colWidths = new string[0];
    private string _colWidths = null;
    private string _fieldListValues="(none)";
    DialogResult result;
    //-----------------------------------------------------------------
        public lizardDataGridView()
      {
      InitializeComponent();
            license = LicenseManager.Validate(typeof(lizardDataGridView), this);
           
            if(license.GetType().Name == "RunTimeLicence")
                canDesign = false;               
            if(license.GetType().Name == "DesignTimeLicence")
                canDesign = true;               

            RowEnter += new DataGridViewCellEventHandler(lizardDataGridView_RowEnter); 
      }
    //-----------------------------------------------------------------
    ~lizardDataGridView()
      {
      base.Dispose(true);
      }   

    //-----------------------------------------------------------------
    void lizardDataGridView_RowEnter(object sender, DataGridViewCellEventArgs e)
      {
      SelectedRow = e.RowIndex;
      }
    //-----------------------------------------------------------------
    void lizardDataGridView_BorderStyleChanged(object sender, EventArgs e)
      {
      if(lb != null){
        ControlLabel.Text = PrettyName;}
      }
    //-----------------------------------------------------------------
      ///

      /// true, sets the connection state and gets table and field names from database
      ///

      [CategoryAttribute("Lizards"),
      DesignerSerializationVisibility(DesignerSerializationVisibility.Content),
      DescriptionAttribute("get length of record id's, ...")]
      public int Numrecords
        {
        get { return (recordIds.Length); }
        }
    //-----------------------------------------------------------------
      ///

      /// true, sets the connection state and gets table and field names from database
      ///

    [CategoryAttribute("Lizards"),
    DesignerSerializationVisibility(DesignerSerializationVisibility.Content),
    DescriptionAttribute("true, sets the connection state and gets table and field names from database, ...")]
    public bool Connect
    {
    get{return _connect;}
    set{_connect = value;
        if(Connect){
//          setServer();
        if(this != null){
          if(this.SqlDataSource == null){
            return;
            }
          if(!this.SqlDataSource.ServerSource.Connect){
            string msg = this.SqlDataSource.ServerSource.GetType().Name +": ";
            msg += "Property for " +  this.SqlDataSource.GetType().Name +": ";
            msg += " Connect is False, activate Y/N?";
            result = MessageBox.Show(msg, "Database Server Not Activated", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
              if(result == DialogResult.Yes){
                this.SqlDataSource.ServerSource.Connect = true;
                }
            }
          if(!this.SqlDataSource.sqlConnect){
            string msg = this.SqlDataSource.GetType().Name +": ";
            msg += "Property SqlConnect is False, connect Y/N?";
            result = MessageBox.Show(msg, "Database Connect", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
              if(result == DialogResult.Yes){
                this.SqlDataSource.sqlConnect = true;
                }
              }
          }
        global.GetTables(SqlDataSource.ServerSource);
        }
        else{
          if(this.Rows != null)
            this.Rows.Clear();
          FillControl = false;
          }
      }   
    }
    //-----------------------------------------------------------------
    ///

    /// true, fills the control with values from the database
    ///

    [CategoryAttribute("Lizards"),
    DesignerSerializationVisibility(DesignerSerializationVisibility.Content),
    DescriptionAttribute("true, fills the control with values from the database, ...")]
    public bool FillControl
      {
      get { return _fill; }
      set { _fill = value;
        if(_fill && _connect)
          if(_fieldList.Length > 0){
            if(sq != null && sq.ServerSource != null)
            LoadData(_fieldList);
            }
         else{
            _fill=false;
            if(this.RowCount > 0){
              this.RowCount = 1;
              }
            }
          }
      }
    //-----------------------------------------------------------------
      ///

      /// Names of the database tables, connect must be true, choose from pick list
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("A list of database table names, SqlDataSource must be set and connect must be true..."),
        TypeConverter(typeof(LizardControls.SourceTypePropertyGridTableEditor.TableConverter))]
    public string TableName
      {
            get{return _tableName;}
      set {_tableName = value;
                    if(_connect){
                        global.GetFields(_tableName, SqlDataSource.ServerSource);
                         }
                    }
      }
      ///

      /// The Name of identity column in table, choose from pick list
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("The Name of identity column within the table selected in thye TableName property..."),
        TypeConverter(typeof(LizardControls.SourceTypePropertyGridTableFieldEditor.FieldConverter))]
    public string RecordIdFieldName
      {
            get{return _recordIdFieldName;}
      set {_recordIdFieldName = value;}
     }
    //-----------------------------------------------------------------
      ///

      /// true, Stores the record id for use when needed
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("The Where part of an sql statement, eg SELECT FROM WHERE = ..."),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    public string WherePart
    {
      get { return _where; }
      set { _where = value; }
    }
    //-----------------------------------------------------------------
    ///

    /// The Order By part of sql statement, eg ORDER BY customerName
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("The Order By part of sql statement, eg SELECT FROM WHERE = ORDER BY "),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    public string OrderByPart
    {
      get { return _orderBy; }
      set { _orderBy = value; }
    }
    //-----------------------------------------------------------------
    ///

    /// he Row in the data grid which is currently selected
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("The Row currently selected row index in the grid.")]
    public int SelectedRow
      {
      get { return _selectedRow; }
      set { _selectedRow = value;}
      }
    //-----------------------------------------------------------------
    ///

    /// Comma seperated list of colum widths in order of field list,
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("A comma seperated list of column widths, the number of column widths must match the number of fields in the FieldList property.\r\nDefault = 100"),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    public string ColWidths
    {
      get { return _colWidths; }
      set { _colWidths = value; }
    }
    //-----------------------------------------------------------------
      ///

      /// Comma seperated list of field names,  tablename.fieldname1,tablename.fieldname2 etc..
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("A comma seperated list of field names for the grid to get from the database table, < tablename.fieldname1,tablename.fieldname2 etc..> do not include <>"),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    public string FieldList
    {
      get { return _fieldListValues; }
      set { _fieldListValues = value;
          if(value.Length > 0){
            Array.Resize(ref _fieldList, 0);
            string s = value;
            string sTmp = "";
            while(s.IndexOf(",") > 0){
              sTmp = s.Substring(0, s.IndexOf(","));
              s = s.Remove(0, s.IndexOf(",")+1);
              Array.Resize(ref _fieldList, _fieldList.Length+1);
              _fieldList[_fieldList.Length-1] = sTmp.Trim();
              }
            if(s.Length > 0){
              Array.Resize(ref _fieldList, _fieldList.Length+1);
              sTmp = s;
              _fieldList[_fieldList.Length-1] = sTmp.Trim();
              }
            }
          }
    }
    //-----------------------------------------------------------------
    ///

    /// The lizardSqlSource to use for executing database requests
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("The lizardSqlSource associated with this control, this will be used to get data from the selected database...")]
    public lizardSQLSource SqlDataSource
    {
      get { return sq; }
        set { sq = value;}
    }
    //-----------------------------------------------------------------
    ///

    /// A stylized version of the control name you give, converts to Upper Lower case
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("A stylized version of the control name, eg. teCustomerName converts to Customer Name, ...")]
    public string PrettyName
      {
      get { return common.prettyName(this.Name); }
      set { _prettyName = common.prettyName(this.Name); }
      }
  //-----------------------------------------------------------------
      ///

      /// A Label asssociated with this control, provides visual prompts for the control
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("A Label asssociated with this control, provides visual prompts for the control...")]
    public lizardLabel ControlLabel
    {
      get { return lb; }
      set {lb=value;
          if(lb != null){
            lizardDataGridView_BorderStyleChanged(this, new EventArgs());}
            }
    }
    //-----------------------------------------------------------------
    ///

    /// true, Stores the record id for use when needed
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("true, Stores the record id for use when needed, ...")]
    public bool WantRecordId
      {
      get {return _wantRecordId;}
      set {_wantRecordId = value;}
      }
  //-----------------------------------------------------------------
      ///

      /// Where to get field data from where fields are links to a look-up table, prototype  sourceTableName.postId,linkTableName.id:name of field to return
      ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("Where to get field value where fields are links in a look-up table, format < sourceTableName.postId,linkTableName.id:; > do not include <>..."),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    //format  sourceTableName.postId,linkTableName.id;
    public string LookUpFields
      {
      get { return _lookupFields; }
      set { _lookupFields = value;
        setLinkFields(value);
        }
      }
  //-----------------------------------------------------------------
      ///

      /// The default row color
      ///

        [CategoryAttribute("Lizards"),
        DescriptionAttribute("The default row color, ...")]
        public Color DefaultRowColor
            {
            get { return _rowColor; }
            set { _rowColor = value; }
            }
  //-----------------------------------------------------------------
    ///

    /// The alternate row color for striping
    ///

        [CategoryAttribute("Lizards"),
        DescriptionAttribute("The alternate row color for banding, ...")]
        public Color AltRowColor
            {
            get { return _altRowColor; }
            set { _altRowColor = value; }
            }
  //-----------------------------------------------------------------
    ///

    /// he color to use on the Totals row
    ///

      [CategoryAttribute("Lizards"),
        DescriptionAttribute("The color to use on the Totals row., ...")]
        public Color TotalsRowColor
            {
            get { return _TotalsRowColor; }
            set { _TotalsRowColor = value; }
            }
  //-----------------------------------------------------------------
    ///

    /// Calculate a totals value for listed fields, prototype tableName.cost,tableName.lineTotal;(###,###,##0.00)
    ///

    [CategoryAttribute("Lizards"),
    DescriptionAttribute("a list of fields to calculate a total value and show in the last row of the data grid, prototype < tableName.cost,tableName.lineTotal;(###,###,##0.00) > do not include <>..."),
    EditorAttribute(typeof(lizardEditor), typeof(UITypeEditor)),]
    //format  tableName.cost;tableName.lineTotal;
    public string TotalFields
      {
      get { return _totalsFields; }
      set { _totalsFields = value;
        setFieldTotalsArray(value);
        }
      }
  //-----------------------------------------------------------------
    ///

    /// true, will alternate row colors using DefaultRowColor and AltRowColour
    ///

      [CategoryAttribute("Lizards"),
        DescriptionAttribute("true, alternate the row colors using the DefaultRowColor and the AltRowColor selected., ...")]
        public bool SwitchRowColors
            {
            get { return _switchRowColors; }
            set { _switchRowColors = value; }
            }
  //-----------------------------------------------------------------
  private void setLinkFields(string value)
    {
    //customer.postId,postcodes.id:postcode;transactions.supplierId,supplier.id:name
    if(value == null || value == "")
      return;
    string s = value;
    while(s.Contains(";")){
      Array.Resize(ref lf, lf.Length+1);
      string a = s.Substring(0, s.IndexOf(";"));                      //get string upto ;
      s = s.Substring(s.IndexOf(";")+1);
      lf[lf.Length-1].getFieldName = a.Substring(a.IndexOf(":")+1);   //get field name to return
      a = a.Substring(0, a.IndexOf(":"));
      lf[lf.Length-1].tableName = a.Substring(0, a.IndexOf("."));     //get table name
      a = a.Substring(a.IndexOf(".")+1);                              //remove table name part + .
      lf[lf.Length-1].fieldName = a.Substring(0, a.IndexOf(","));     //get field name
      a = a.Substring(a.IndexOf(",")+1);                          
      //from link table part
      lf[lf.Length-1].getFromTable = a.Substring(0, a.IndexOf("."));  //get fromTableName
      a = a.Substring(a.IndexOf(".")+1);                              //remove table name part + .
      lf[lf.Length-1].getFromTableField = a;                          //get field name
      }
    if(s != ""){
      Array.Resize(ref lf, lf.Length+1);
      string a = s;                                                   //get remaining string
      lf[lf.Length-1].getFieldName = a.Substring(a.IndexOf(":")+1);   //get field name to return
      a = a.Substring(0, a.IndexOf(":"));
      lf[lf.Length-1].tableName = a.Substring(0, a.IndexOf("."));     //get table name
      a = a.Substring(a.IndexOf(".")+1);                              //remove table name part + .
      lf[lf.Length-1].fieldName = a.Substring(0, a.IndexOf(","));     //get field name
      a = a.Substring(a.IndexOf(",")+1);                          
      lf[lf.Length-1].getFromTable = a.Substring(0, a.IndexOf("."));  //get fromTableName
      a = a.Substring(a.IndexOf(".")+1);                              //remove table name part + .
      lf[lf.Length-1].getFromTableField = a;     //get field name
      s = s.Replace(a, "");
      }
    }
  //-----------------------------------------------------------------
  private void setFieldTotalsArray(string value)
    {
    //transactins.lineTotal,transactions.sellPrice;(###,##0.00)
    if(value == null || value == "")
      return;
    string s = value;
    Array.Resize(ref ft, 0);
    //get the format for the fields
    if(s.Contains(";")){
      format =  s.Substring(s.IndexOf("(")+1);
      format = format.Substring(0, format.IndexOf(")"));
      //remove the format part of string
      s = s.Substring(0, s.IndexOf(";"));
      }
    while(s.Contains(",")){
      Array.Resize(ref ft, ft.Length+1);
      s = s.Substring(s.IndexOf(".")+1);
      ft[ft.Length-1].field = s.Substring(0, s.IndexOf(","));
      s = s.Substring(s.IndexOf(",")+1);
      }
    if(s !=""){
      Array.Resize(ref ft, ft.Length+1);
      s = s.Substring(s.IndexOf(".")+1);
      ft[ft.Length-1].field = s;
      }
    } 
  //-----------------------------------------------------------------
  private void setColumnWidthsArray()
    {
    string s = "";
    //if empty set with to a default value
    if(String.IsNullOrEmpty(ColWidths)){
      Array.Resize(ref colWidths, _fieldList.Length);
      for(int i=0; i< _fieldList.Length; i++){
        colWidths[i] = "100";
        }
      return;
      }
    s = ColWidths;
    Array.Resize(ref colWidths, 0);
    while(s.Contains(",")){
      Array.Resize(ref colWidths, colWidths.Length+1);
      colWidths[colWidths.Length-1] = s.Substring(0, s.IndexOf(","));
      s = s.Remove(0, s.IndexOf(",")+1);
      }
    if(s.Length > 0){
      Array.Resize(ref colWidths, colWidths.Length+1);
      colWidths[colWidths.Length-1] = s;
      }
    
    }
  //-----------------------------------------------------------------
  private void saveRowRecords(int row)
    {
    int col = 0, cellCount=0;
    string s = null, cellValue = null;;
    s = "UPDATE " + this.TableName + " SET ";
    for(col = 0; col < cellCount; col++){
      if(this.Rows[row].Cells[col].Value !=null)
        cellValue = this.Rows[row].Cells[col].Value.ToString();
       else
        cellValue = "";
       
      s += _fieldList[col] + " = " + sqlWorker.getDataValueByType(this.SqlDataSource, _fieldList[col], this.TableName, cellValue, true);
      if(col < cellCount-1){
        s += ",";
        }
      else{
        s += " WHERE " + this.RecordIdFieldName + " = " + recordIds[row];
        s += "\r\n";
        }
      }
    }
  //-----------------------------------------------------------------
    ///

    /// Returns the record id for the selected row.
    ///

  public int GetRecordId(int row)
    {
    int id = -1;
    if (row <= recordIds.Length)
        {
        if (row != -1 && recordIds.Length != 0 && row != recordIds.Length)
            id = recordIds[row];
        }
     return(id);
    }
  //---------------------------------------------------------------------------
  public bool LoadData()
    {
    return(LoadData(GetFieldList()));
    }
  //---------------------------------------------------------------------------
  public bool LoadData(string sql)
    {
    string[] fields = getFieldsFromSql(sql);
    this.SqlDataSource.SqlStatement = sql;
    return(LoadData(fields));
    }
  //---------------------------------------------------------------------------
  private string[] getFieldsFromSql(string sql)
    {
    string stmp   = null;
    string[] fields = new string[0];

    if(sql == null || sql == "")
      return(fields);
    sql = sql.ToLower();
    string s = sql;
   
    //id must be the first after select
    s = s.ToLower();
    s = s.Replace("select", "");
    s = s.Trim();
    this.RecordIdFieldName = s.Substring(0, s.IndexOf(","));
    s = s.Replace(this.RecordIdFieldName + "," , "");
    stmp = s.Substring(0, s.IndexOf("from"));
    s.Trim();
    //get the field names from s
    stmp = s.Substring(0, s.IndexOf("from")+4);
    //get rid of unwanted spaces
    stmp = stmp.Trim();
    s = s.Substring(0, s.IndexOf("from")+4);
    s = s.Trim();
    if(stmp.Contains("from")){
      stmp = stmp.Replace("from", "");
      }
    while(stmp.Contains(",")){
      //for each field add array element
      Array.Resize(ref fields, fields.Length+1);
      fields[fields.Length-1] = stmp.Substring(0, stmp.IndexOf(","));
      stmp = stmp.Remove(0, stmp.IndexOf(",")+1);
      stmp = stmp.Trim();
      }
    //get last field name
    if(stmp != ""){
      Array.Resize(ref fields, fields.Length+1);
      fields[fields.Length-1] = stmp;
      }
    //"select id,name from transactions where "
    //get table name from s
    s = sql.Substring(sql.IndexOf("from")+4);
    s = s.Trim();
    this.TableName = s.Substring(0, s.IndexOf(" "));
    //look for where part
    if(s.Contains("where")){
      s = s.Substring(s.IndexOf("where")+5);
      s = s.Trim();
      if(s.Contains("order by")){
        stmp = s.Substring(0, s.IndexOf("order"));
        stmp = stmp.Trim();
        this.WherePart = stmp;   
        s = s.Remove(0, s.IndexOf("order")+5);
        s = s.Trim();
        if(s.Substring(0,2) == "by"){
          s = s.Remove(0, 2);
          }
        this.OrderByPart = s.Trim();
        }
      else
        this.WherePart = s.Trim();
      }
    return(fields);
    }
  //---------------------------------------------------------------------------
  public bool LoadData(string[] fields)
    {
    string joinPart="", fieldPart;
    int colCount = fields.Length;
    bool hasRecords = false;
    Color c = Color.White;
    if(_rowColor != null)
      c = _rowColor;
     
    //set col widths array
    setColumnWidthsArray();
   
    int i, j;
    //set the column number for the field total
    if(ft.Length > 0){
      for(j=0; j< ft.Length; j++){
        for(i = 0; i < fields.Length; i++){
          if(fields[i] == ft[j].field){
            ft[j].col = i;
            break;
            }
          }
        }
      }
    //prepare for record id's
    Array.Resize(ref recordIds, 0);
   
    object dataType = null;
    int recordId = -1;
    this.ColumnCount = 0;
    this.ColumnCount = colCount;
    if(this.RowCount > 0){
      Rows.Clear();
      }
    if(this.SqlDataSource == null){
      MessageBox.Show("SqlSource not set ... ", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
      return(hasRecords);
      }
    string[] fieldData = new string[colCount];
    string sql= "SELECT ";
    if(WantRecordId)
      sql += _tableName + "." +RecordIdFieldName + ", ";

    //---------------------------------------------------------------------------------------
    //make sql taking into account any field where its data may come from a lookup table
    //replace field from list with field to return from link table
    //primary table               tableName.postId,
    //secondary table             getFromTable.id
    //the field value to return : getFromTableField
    // customer.postid,postcodes.id:postcode
    //----------------------------------------------------------------------------------------
    string a,b;
    if(lf.Length > 0){
      for(i=0; i        for(j=0; j          //is field same as sourceTableFieldName
          a= lf[i].fieldName; b= fields[j];
          a = a.Substring(a.IndexOf(".")+1); b = b.Substring(b.IndexOf(".")+1);
          if(a == b){
            //yes, make this field linktable.returnfield
            fields[j] = lf[i].getFromTable + "." + lf[i].getFieldName;
            //should be, eg. customer.postId <- becomes -> postcodes.postcode when done
            }
          }
        } 
      //make the join part of sql statement
      for(i=0; i< lf.Length; i++){
        //select customer.id,customer.name,postcodes.postcode FROM customer
        //join postcodes on customer.postid = postcodes.id
        joinPart += "\r\nJOIN " + lf[i].getFromTable + " ON " + lf[i].tableName + "." + lf[i].fieldName;
        joinPart += " = " + lf[i].getFromTable + "." + lf[i].getFromTableField;
        }
      }
    //=========================================================================================
    //set the header text, use field name as the header text for each column
    for(i = 0; i      string f = fields[i];
      if(f.Contains(".")){
        f = f.Substring(f.IndexOf(".")+1);}
      this.Columns[i].HeaderText = common.prettyName(f, 0);
      }

    //assemble the fields to be included in sql statement
    for(i = 0; i < colCount; i++){
      if(i < fields.Length-1){
        sql += fields[i] + ",";}
      else
        sql += fields[i] + " FROM ";
      }
    //set the table name part
    sql += this._tableName;
   
    //if there is a joinPart, add to sql
    if(joinPart != "") {sql += joinPart;}   
   
    if(!String.IsNullOrEmpty(_where)) {sql += " WHERE " + this._where;}
   
    //if there is an order by part
    if(!String.IsNullOrEmpty(_orderBy)) {sql += " ORDER BY " + this._orderBy;
      }
    //set SqlDatasource statement
    this.SqlDataSource.SqlStatement = sql;
    //create a new sql command
    sq.Command = sq.newCommand(SqlDataSource.ServerSource, sql);
    //make sure we get an execution, if not abort
    if(sq.Command == null || !sq.ExecuteReader(sq.Command))
      return(hasRecords);
    DataGridViewRow dgr;
    DataGridViewTextBoxCell txt;
    DataGridViewCheckBoxCell chk; 
   
    //build the sql statement
    string sqlBody, sqlWhere, sqlOrderby;
    sqlBody = "SELECT table_name=sysobjects.name, ";
    sqlBody += "column_name=syscolumns.name, ";
    sqlBody += "datatype=systypes.name, ";
    sqlBody += "length=syscolumns.length, ";
    sqlBody += "allownull=syscolumns.isnullable ";
    sqlBody += "FROM sysobjects ";
    sqlBody += "JOIN syscolumns ON sysobjects.id = syscolumns.id ";
    sqlBody += "JOIN systypes ON syscolumns.xtype=systypes.xtype ";
    // --- keep for referance only
    //sqlWhere = "WHERE sysobjects.name = '" + gn[gIndex].TableName + "' AND sysobjects.xtype='U' ";
    sqlOrderby = " ORDER BY sysobjects.name,syscolumns.colid";
   
    while (sq.read())
      {
      //must have at least one record
      hasRecords = true;
      dgr = new DataGridViewRow();
      //switch color if flip is true
      if(SwitchRowColors){
        if(flipColor){
          flipColor = false;
          c = _altRowColor;}
        else{
          flipColor = true;
          c = _rowColor;}
        }

      if(this.WantRecordId){
        Array.Resize(ref recordIds, recordIds.Length+1);
        recordId = int.Parse(sq.get(this.RecordIdFieldName).ToString());
        recordIds[recordIds.Length-1] = recordId;
        }
      string tbl = "";
      for(i = 0; i         //fix this to get data type for fields if link table
        if(fields[i].Contains(".")){
          tbl = fields[i].Substring(0, fields[i].IndexOf("."));}  
        else{
          tbl = TableName;}
         
        sqlWhere = "WHERE sysobjects.name = '" + tbl;
        fieldPart = stringPart(false, ".", fields[i]);
        sqlWhere += "' AND syscolumns.name = '" + fieldPart;
        sqlWhere += "' AND sysobjects.xtype='U' ";
        sql = sqlBody + sqlWhere + sqlOrderby;
        //need a temp SqlDataSource for misc queries on database
        lizardSQLSource sqlSource = new lizardSQLSource();
        sqlSource.ServerSource = sq.ServerSource;
        sqlSource.sqlConnect = true;
        //need to know the data type for the field use tmp SqlDataSource to do this.
        dataType = sqlSource.GetSqlDataType(sql, sqlSource);  
        if(sqlSource !=null)
          sqlSource.terminate();
         
        switch(dataType.ToString())
          {
          case "bit":
            chk = new DataGridViewCheckBoxCell();
            chk.Value = sq.get(fields[i]).ToString();
            dgr.Cells.Add(chk);
            break;
          default:
            txt = new DataGridViewTextBoxCell();
            txt.Value = sqlWorker.setValueByDataType(sq.get(fieldPart).ToString(), dataType);
            //is this column a totals column?
            if(ft.Length > 0){
              for(int x = 0; x < ft.Length; x++){
                if(ft[x].field == fieldPart){  //is a totals column
                  ft[x].col = i;
                  ft[x].total += Convert.ToDouble(txt.Value.ToString());}
                }
              }
            dgr.Cells.Add(txt);
            dgr.Cells[dgr.Cells.Count-1].Style.BackColor = c;
            switch(dataType.ToString())
              {
              case "float":
              case "double":
              case "int":
                dgr.Cells[dgr.Cells.Count-1].Style.Alignment = DataGridViewContentAlignment.MiddleRight;
                break;
              default:
                dgr.Cells[dgr.Cells.Count-1].Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
                break;
              }
            break;
         
          }
        fieldData[i] = sq.get(fieldPart).ToString(); //sq.get(fields[i]).ToString();
        }
    this.Rows.Add(dgr);   
    if((this.RowCount % 10) == 0)
            Application.DoEvents();
    }
    //set column widths
    for(i = 0; i< colCount; i++){
      this.Columns[i].Width = int.Parse(colWidths[i]);
      }
    //include totals fields if any...
    if(ft.Length > 0 && Rows.Count > 1){
      dgr = new DataGridViewRow();
      for(i = 0; i < fields.Length; i++){
        txt = new DataGridViewTextBoxCell();
        txt.Value = "";
        txt.Style.BackColor = TotalsRowColor;
        dgr.Cells.Add(txt);
        }
      //add the row to grid
      if(ft.Length > 0)
        this.Rows.Add(dgr);   
       
      //get totals and set cells
      for(i = 0; i < ft.Length; i++){  //loop through each totals array elements
        for(int x = 0; x < fields.Length; x++){ //column # can be in any order so make sure we gaet all of them
          if(ft[i].col == x){
            dgr.Cells[x].Value = ft[i].total.ToString(format);
            dgr.Cells[x].Style.Font = new Font(Font, FontStyle.Bold);
            dgr.Cells[x].Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            ft[i].total = 0;}
          }
        }       
      for(i = 0; i < dgr.Cells.Count; i++){
        if((string)dgr.Cells[i].Value != ""){
          if(i != 0){
            if((string)dgr.Cells[i-1].Value == ""){
              dgr.Cells[i-1].Value = "Total:";
              dgr.Cells[i-1].Style.Font = new Font(Font, FontStyle.Bold);
              dgr.Cells[i-1].Style.Alignment = DataGridViewContentAlignment.MiddleRight;}
            }
          }
        }
      }
    
    sq.terminate();
    return(hasRecords);
    }   
  //---------------------------------------------------------------------------
  internal string stringPart(bool before, string delim, string value)
    {
    string s;
    //if before, get string from start to where delim is
   
    if(before)
      s = value.Substring(0, value.IndexOf(delim));
    else
      s = value.Substring(value.IndexOf(delim)+1);

    return(s);   
    }
  //---------------------------------------------------------------------------
    ///

    /// Returns the list of fields to be included in database request.
    ///

  public string[] GetFieldList()
    {
    return(_fieldList);
    }
  //-----------------------------------------------------------------
    ///

    /// Fill the datagrid with values from database.
    ///

  public void Fill(bool connect, string wherePart, string orderByPart)
    {
    WherePart = wherePart;
    OrderByPart = orderByPart;
    Connect = connect;
    FillControl = true;
    }
  }
}

MW

mahesh waghela15y agoPosted Aug 20, 2011, 1:13 AM

Yes
Sam

You are right it's has limited feature as well as it is compatible with only operating system winxp, and 2003. So which control will be prefer or have to choose for the same?. what you think about datagridview is it perfect for it?.
 

Sam Hobbs15y agoPosted Aug 19, 2011, 5:09 PM

The Windows ListView control was created when Windows was converted to 32-bits from 16-bits. It is relatively old and not flexible. It was initially created for use in the right side of the Windows Explorer. Either limit your application to doing the things that the Windows ListView control can do, or choose something different.