Check dates from a bound data source

Question

I am filling a DGV from a bound data source.  How do I check dates in the data source?  I've tried doing it programmatically by creating a DGV object.  Because I'm not creating the columns programmatically, I'm getting an exception on the column name. 

Answer 1

You should post some code.   Assuming you are using a data table you could use the column number instead of name

Answer 2

Hello,

My recommendation is to utilize a BindingSource when working with data from a database for many reasons starting with never needing to touch the DataGridView to get at the data.

In the following code sample data is read from a database, in this case SQL-Server into a DataTable (works with DataSet too by referencing the DataTable within the DataSet).

When performing data operations use a class rather than write the code in a form e.g.

using System.Data;
using System.Data.SqlClient;

namespace WindowsFormsApp1
{
    public class Operations
    {
        private string ConnectionString = "Data Source=KARENS-PC;" + 
                                          "Initial Catalog=NorthWindAzure1;" + 
                                          "Integrated Security=True";
        public bool HasErrors { get; set; }
        public string ExceptionMessage { get; set; }

        public DataTable Read()
        {
            var dt = new DataTable();

            const string selectStatement = 
                "SELECT TOP 10 o.OrderID,o.CustomerIdentifier," +
                    "o.OrderDate,o.ShippedDate ,o.Freight,o.ShipCountry " + 
                "FROM dbo.Orders AS o";

            using (var cn = new SqlConnection {ConnectionString = ConnectionString})
            {
                using (var cmd = new SqlCommand { Connection = cn, CommandText = selectStatement })
                {
                    cn.Open();
                    dt.Load(cmd.ExecuteReader());
                    dt.Columns["OrderID"].ColumnMapping = MappingType.Hidden;
                    dt.Columns["CustomerIdentifier"].ColumnMapping = MappingType.Hidden;
                }
            }

            return dt;
        }
    }
}

Create an instance of the class form level in a form, read data and set the DataSource of the BindingSource then the BindingSource becomes the data source of the DataGridView.

Notes:

• This means the column names of the DataGridView are the names of the fields in the SELECT statement.
• In the first button I show how to get at the DataRow for the current row.
  
  Or target a specific row var row = ((DataRowView) _bindingSource[0]).Row;
  
• Second button shows an example of checking a condition against the OrderDate column for dates between two dates, this should be enough to get you in the right direction.

using System;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        private readonly Operations _operations = new Operations();
        private readonly BindingSource _bindingSource = new BindingSource();
        public Form1()
        {
            InitializeComponent();
            Shown += Form1_Shown;
        }

        private void Form1_Shown(object sender, EventArgs e)
        {
            _bindingSource.DataSource = _operations.Read();
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.DataSource = _bindingSource;
            dataGridView1.FixColumn();
        }

        private void CurrentRowOrderData_Click(object sender, EventArgs e)
        {
            if (_bindingSource.Current == null) return;

            var row = ((DataRowView) _bindingSource.Current).Row;
            var orderIdentifier = row.Field<int>("OrderID");
            var orderDate = row.Field<DateTime>("OrderDate");

            MessageBox.Show($"Id: {orderIdentifier} Date: {orderDate.ToShortDateString()}");
        }

        private void AllDataButton_Click(object sender, EventArgs e)
        {

            var startDate = new DateTime(2014, 7, 8, 0, 0, 0);
            var endDate = new DateTime(2014, 7, 10, 0, 0, 0);

            var table = (DataTable) _bindingSource.DataSource;

            var results = table.AsEnumerable().Where(row => row.Field<DateTime>("OrderDate").Between(startDate,endDate))
                .Select(row =>  new DataItem
                {
                    Id = row.Field<int>("OrderID"),
                    OrderDate = row.Field<DateTime>("OrderDate")
                })
                .ToList();


            if (results.Count >0)
            {
                var builder = new StringBuilder();
                foreach (var dataItem in results)
                {
                    builder.AppendLine(dataItem.ToString());
                }

                MessageBox.Show(builder.ToString());
            }

        }
    }
}

Data class for second button (optional as an anonymous type may be used in the need is only within the button click event) 

using System;

namespace WindowsFormsApp1
{
    public class DataItem
    {
        public int Id { get; set; }
        public DateTime OrderDate { get; set; }
        public override string ToString()
        {
            return $"ID: {Id} OrderDate: {OrderDate.ToShortDateString()}";
        }
    }
}

Extension methods

using System;
using System.Text.RegularExpressions;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public static class Extensions
    {
        /// <summary>Determines if a value is between two values, inclusive.</summary>
        /// <param name="sender">The source value.</param>
        /// <param name="minimumValue">The minimum value.</param>
        /// <param name="maximumValue">The Maximum value.</param>
        /// <returns><see langword="true"/> if the value is between the two values, inclusive.</returns>
        public static bool Between<T>(this IComparable<T> sender, T minimumValue, T maximumValue)
        {
            return sender.CompareTo(minimumValue) >= 0 && sender.CompareTo(maximumValue) <= 0;
        }
        /// <summary>
        /// Split string on upper case letters e.g. CompanyName will be Company Name
        /// </summary>
        /// <param name="sender"></param>
        /// <returns></returns>
        public static string SplitCamelCase(this string sender)
        {
            return Regex.Replace(Regex.Replace(sender, 
                @"(\P{Ll})(\P{Ll}\p{Ll})", "$1 $2"), @"(\p{Ll})(\P{Ll})", "$1 $2");
        }
        /// <summary>
        /// Split column header text
        /// </summary>
        /// <param name="sender"></param>
        public static void FixColumn(this DataGridView sender)
        {
            foreach (DataGridViewColumn column in sender.Columns)
            {
                column.HeaderText = column.HeaderText.SplitCamelCase();
            }
        }
    }
}

If we were to work off the DataGridView to ask for a column (which is unwise) you can check if a column exists via.

private void CheckColumnExists_Click(object sender, EventArgs e)
{
    Console.WriteLine(dataGridView1.Columns.Contains("OrderID"));
    Console.WriteLine(dataGridView1.Columns.Contains("OrderDate"));
}

Returns, false for OrderId even though it's a column in the DataTable but has been marked as hidden as normally identifiers are indeed not shown while OrderDate is a column.

Bottom line is life is easier with a BindingSource, now run this

private void CheckColumnExists_Click(object sender, EventArgs e)
{
    Console.WriteLine(dataGridView1.Columns.Contains("OrderID"));
    Console.WriteLine(dataGridView1.Columns.Contains("OrderDate"));
    Console.WriteLine(((DataTable)_bindingSource.DataSource).Columns.Contains("OrderID"));
    Console.WriteLine(((DataTable)_bindingSource.DataSource).Columns.Contains("OrderDate"));
}

We get

False
True

True
True

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 3

I can do an sql query on the table, but how do I set the cell Forecolor if I haven't created any DGV columns programmatically as doing so would only replicate the bound columns.  

Answer 4

I can do an sql query on the table, but how do I set the cell Forecolor if I haven't created any DGV columns programmatically as doing so would only replicate the bound columns.  

You never ask in your original post about setting forecolor. Regardless one method is to use the CellFormatting event.

https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.datagridview.cellformatting?view=netframework-4.8

https://docs.microsoft.com/en-us/dotnet/framework/winforms/controls/how-to-customize-data-formatting-in-the-windows-forms-datagridview-control

Follow what I suggested coupled with the CellFormatting which as per the links above use assertion to test which cell is to be formatting so if the column name does not exists nothing is done.

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow

Answer 5

Got it working.  

private void DataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            int column = e.ColumnIndex;
            string columnName = DataGridView.Columns[column].Name;
            int row = e.RowIndex;
            
            try
            {
                if (Convert.ToDateTime(DataGridView.Rows[row].Cells[columnName].Value) < DateTime.Today)
                {
                    DataGridView.Rows[row].Cells[columnName].Style.ForeColor = System.Drawing.Color.Red;
                }
                else if (Convert.ToDateTime(DataGridView.Rows[row].Cells[columnName].Value) >= DateTime.Today)
                {
                    DataGridView.Rows[row].Cells[columnName].Style.ForeColor = System.Drawing.Color.Green;
                }
            }
            catch(Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
        }

 

Answer 6

Got it working.  

private void DataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            int column = e.ColumnIndex;
            string columnName = DataGridView.Columns[column].Name;
            int row = e.RowIndex;
            
            try
            {
                if (Convert.ToDateTime(DataGridView.Rows[row].Cells[columnName].Value) < DateTime.Today)
                {
                    DataGridView.Rows[row].Cells[columnName].Style.ForeColor = System.Drawing.Color.Red;
                }
                else if (Convert.ToDateTime(DataGridView.Rows[row].Cells[columnName].Value) >= DateTime.Today)
                {
                    DataGridView.Rows[row].Cells[columnName].Style.ForeColor = System.Drawing.Color.Green;
                }
            }
            catch(Exception ex)
            {
                System.Console.WriteLine(ex.Message);
            }
        }

 

Your welcome.

---

Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

NuGet BaseConnectionLibrary for database connections.

StackOverFlow