DataGridView SQL binding Classes with an integrated and flexible Filter and ContextMenu Tool that uses Parameterized SQL Commands

Introduction

Every on who works with DataGridView knows how annoying it is to work with them. Especially with ComboBoxes in it. There is always something making trouble. After some wile working with DataGridView and writing code for every new DataGridView every time from scrap I figure out (in Programming known as DRY – Don’t Repeat Yourself) that it just don´t make sense. During the last weeks and days, I made some Classes for the DataGridView binding and it looks like it make life much easier so I decided to share it with this community. Maybe I make the programming life of someone easier or someone has better solutions to share with us.

All actions in the DataGridView should be made by own code and called from a ContextMenu in it. The most used of them should have and Hotkey.

Also the filter should be called directly from the DataGridView (the DataGridView ContextMenu).

In the Filter you should be able to choose if the filter parameter is added with an AND or an OR, the filtered column should be chosen, the filter condition (=,<,>,!=) and the Control where to enter the filter value should change to an ComboBox if the Filtering Column is an DataGridViewComboBoxColumn or to an CheckBox if the Filtering Column is an DataGridViewChecBoxColumn.

Background

To make the binding as flexible as possible it has to be done in a small number of Classes:

1. DataBaseConnection – a static class that returns sqlconnections used in a project
2. Column – used to save data (from SQL and DataGridView) of an Table Column
3. Columns – an array that stores all columns that an Table contains
4. Parameter – defines a parameter that is used to filter a table
5. Table – used to define the default table settings and methods
6. SQLAdapter – a static class that performs all SQL actions
7. DgvAdapter – finally the class that does all work needed to bind a DataGridView with a Table
8. Filter – a Form for the Filtering actions

Now we will see Cass by Class how all this works together.

DataBaseConnection

Let´s look at this class:

     public static class DatabaseConnection
    {

        public static SqlConnection ICSDB()
        {
            return new SqlConnection(Properties.Settings.Default.ICSDBConnectionString);
        }

        public static SqlConnection Transdat()
        {
            return new SqlConnection(Properties.Settings.Default.TransdatConnectionString);
        }
    }

I don´t thing that it needs lot of explanation. I use two SQL Servers and the connection string is stored in the application settings.

Column

Because this class is a little bit larger, we will look on it part by part. The first one is of course the Fields:

        private string _Name;
        private int _SQLIndex;
        private int _DgvIndex;
        private SqlDbType _SqlDataBaseType;
        private Type _SystemType;
        private bool _DgvVisible;
        private int _DgvWith;
        private object _DefaultValue;
        private Control _Control;
        private string _HeaderText;
        private object _CellType;
        private bool _ReadOnly;
        private int _SettingsID;

Next part are the Properties of the listed fields:

        public int SettingsID
        {
            get { return _SettingsID; }
            set { _SettingsID = value; }
        }

        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        public int SQLIndex
        {
            get { return _SQLIndex; }
            set { _SQLIndex = value; }
        }

        public int DgvIndex
        {
            get { return _DgvIndex; }
            set { _DgvIndex = value; }
        }

        public SqlDbType SqlDataBaseType
        {
            get { return _SqlDataBaseType; }
            set { _SqlDataBaseType = value; }
        }

        public Type SystemType
        {
            get { return _SystemType; }
            set { _SystemType = value; }
        }

        public bool DgvVisible
        {
            get { return _DgvVisible; }
            set { _DgvVisible = value; }
        }

        public int DgvWith
        {
            get { return _DgvWith; }
            set { _DgvWith = value; }
        }

        public object DefaultValue
        {
            get { return _DefaultValue; }
            set { _DefaultValue = value; }
        }

        public Control Control
        {
            get { return _Control; }
            set
            {

                _Control = value;
            }
        }

        public string HeaderText
        {
            get { return _HeaderText; }
            set { _HeaderText = value; }
        }

        public object CellType
        {
            get { return _CellType; }
            set { _CellType = value; }
        }

        public bool ReadOnly
        {
            get { return _ReadOnly; }
            set { _ReadOnly = value; }
        }

Now the Methods. Because they are connected to other classes and without them, it is hard to explain the methods we will just show them for now and explain the first time we have the chance to do it.

   public void Save(Table table, int identifier, object newValue, string identifierName = "ID")
        {
            string command = "UPDATE {0} SET {1}=@{1} WHERE [{2}]=@{2} ";
            if (table.TableConnection != null && table.TableConnection.State == ConnectionState.Closed)
                table.TableConnection.Open();
            command = String.Format(command, table.TableName, Name, identifierName);
            SqlCommand cmd = new SqlCommand(command, table.TableConnection);
            cmd.Parameters.Add("@" + Name, SqlDataBaseType).Value = newValue;
            cmd.Parameters.Add("@" + identifierName, SqlDbType.Int).Value = identifier;
            cmd.ExecuteNonQuery();
        }

        public void SetControl(Control control, DataGridView dataGridView)
        {
            Control = control;
            control.Validated += new EventHandler(delegate(object sender, EventArgs e)
            {
                DataGridViewRow row = dataGridView.CurrentRow;
                if (row != null)
                {
                    DataGridViewCell cell = row.Cells[Name];

                    if (Control is TextBox)
                    {
                        if (SystemType == typeof(DateTime))
                        {
                            if (control.Text == string.Empty)
                            {
                                cell.Value = null;
                                return;
                            }
                        }
                        cell.Value = Control.Text;
                    }
                    else if (Control is ComboBox)
                    {
                        ((DataGridViewComboBoxCell)cell).Value = ((ComboBox)Control).SelectedValue;
                    }
                    else if (Control is CheckBox)
                    {
                        ((DataGridViewCheckBoxCell)cell).Value = ((CheckBox)Control).Checked;
                    }
                }
            }); ;

        }

Columns

This Class is an Array representing all Columns of a Table that we want to bind to an DataGridView. The main parts are those where we can find a Column by the array index, column name or the used control for a column. For my purposes I set the columnNumber to 200. It can be set to any number without performance degrees because the loops are "breaked" by the first Columns item that contains a null value. The code below shows the methods that just finds a searched column object.

  public class Columns
    {
        public static int columnNumber = 200;
        private Column[] columns = new Column[columnNumber];

        public Column this[int index]
        {
            get { return columns[index]; }
            set
            {

                if (columns[index] == null)
                    columns[index] = value;
                else

                    for (int i = 0; i < columnNumber; i++)
                        if (columns[i] == null)
                        {
                            columns[i] = value;
                            return;
                        }

            }
        }

        public Column this[string name]
        {
            get
            {
                for (int index = 0; index < columnNumber; index++)
                {
                    if (columns[index] != null && columns[index].Name == name)
                        return columns[index];
                }
                return null;
            }
        }

        public Column this[Control control]
        {
            get
            {
                for (int index = 0; index < columnNumber; index++)
                {
                    if (columns[index] != null && columns[index].Control == control)
                        return columns[index];
                }
                return null;
            } 
         }
<pre> }

I set default values for some arguments used to call the method. They of course can be changes for all needs. The next methods are those who add the columns to a DatagrdiView. The first one is used to add just one column to the DataGridView and the second to add all columns of an Table to the DataGridView.

  public void AddColumnsToDataGridView(DataGridView dataGrdiView)
        {
            for (int i = 0; i < Columns.columnNumber; i++)
            {
                if (this[i] == null) break;
                AddColumnToDataGridView(i, dataGrdiView);
            }

            SetDataGridViewColumnOrder(dataGrdiView);
        }

        private void AddColumnToDataGridView(int columnIndex, DataGridView dataGridView)
        {
            int i = columnIndex;
            if (this[i].CellType is DataGridViewTextBoxColumn)
            {
                DataGridViewTextBoxColumn tempColumn = (DataGridViewTextBoxColumn)this[i].CellType;
                tempColumn.DataPropertyName = this[i].Name;
                tempColumn.HeaderText = this[i].HeaderText;
                tempColumn.Name = this[i].Name;
                tempColumn.DisplayIndex = this[i].DgvIndex;
                tempColumn.Visible = this[i].DgvVisible;
                tempColumn.Width = this[i].DgvWith;
                tempColumn.ReadOnly = this[i].ReadOnly;
                tempColumn.SortMode = DataGridViewColumnSortMode.NotSortable;
                dataGridView.Columns.Add(tempColumn);

            }
            else if (this[i].CellType is DataGridViewCheckBoxColumn)
            {
                DataGridViewCheckBoxColumn tempColumn = (DataGridViewCheckBoxColumn)this[i].CellType;
                tempColumn.DataPropertyName = this[i].Name;
                tempColumn.HeaderText = this[i].HeaderText;
                tempColumn.Name = this[i].Name;
                tempColumn.Visible = this[i].DgvVisible;
                tempColumn.Width = this[i].DgvWith;
                tempColumn.ReadOnly = this[i].ReadOnly;
                tempColumn.SortMode = DataGridViewColumnSortMode.NotSortable;
                dataGridView.Columns.Add(tempColumn);
            }
            else if (this[i].CellType is DataGridViewComboBoxColumn && (ComboBox)this[i].Control != null)
            {
                DataGridViewComboBoxColumn tempColumn = (DataGridViewComboBoxColumn)this[i].CellType;
                tempColumn.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing;
                tempColumn.DataPropertyName = this[i].Name;
                tempColumn.HeaderText = this[i].HeaderText;
                tempColumn.Name = this[i].Name;
                tempColumn.Visible = this[i].DgvVisible;
                tempColumn.Width = this[i].DgvWith;
                tempColumn.ReadOnly = this[i].ReadOnly;
                tempColumn.SortMode = DataGridViewColumnSortMode.NotSortable;
                tempColumn.ValueMember = ((ComboBox)this[i].Control).ValueMember;
                tempColumn.DisplayMember = ((ComboBox)this[i].Control).DisplayMember;
                tempColumn.DataSource = ((ComboBox)this[i].Control).DataSource;
                dataGridView.Columns.Add(tempColumn);
            }
        }

The last one is used to count the number of columns that a table has. It´s just a simple loopt trough the array.

  private int Count()
        {
            for (int i = 0; i < Columns.columnNumber; i++)
            {
                if (this[i] == null) return i;

            }
            return 0;
        }

Parameter

It´s a simple class that has only fields and properties witch doesnt need loots of explanation.

 public class Parameter
    {
        private string _ParameterName;
        private Operator.Conditional _ConditionalOperator;
        private Operator.Rational _RationalOperator;
        private Column _Column;
        private object _Value;
        private string _DisplayValue;

        public string DisplayValue
        {
            get { return _DisplayValue; }
            set { _DisplayValue = value; }
        }

        public Column Column
        {
            get { return _Column; }
            set { _Column = value; }
        }

        public Operator.Rational RationalOperator
        {
            get { return _RationalOperator; }
            set { _RationalOperator = value; }
        }

        public Operator.Conditional ConditionalOperator
        {
            get { return _ConditionalOperator; }
            set { _ConditionalOperator = value; }
        }

        public string ParameterName
        {
            get { return _ParameterName; }
            set { _ParameterName = value; }
        }

        public object Value
        {
            get { return _Value; }
            set
            {
                if (RationalOperator == Operator.Rational.like)
                {
                    _Value = "%" + value + "%";
                }
                else
                {
                    _Value = value;
                }
            }
        }

    } 

Table

Because the Class is large, I´ll show it from A to Z and explain the methods after that.

 public class Table: DgvAdapter
    {
        #region Fields

        private string _TableName;
        private SqlConnection _TableConnection;
        private Dictionary<int, Parameter> _Parameters = new Dictionary<int, Parameter>();
        private BindingSource _BindingSource = new BindingSource();
        private SqlDataAdapter _SqlDataAdapter = new SqlDataAdapter();
        private DataTable _DataTable = new DataTable();
        private DataSet _DataSet = new DataSet();
        private DataGridView _DataGridView;
        private Columns _Columns = new Columns();
        private int _SelectNumber;
        private Selectors _Selectors = new Selectors();
        private string _Orderer;
        private string _Command;
        private int _CurrentID;
        private int _CurrentRowID;
        private int _CurrentColumnID;
        private bool _AllowUserToAddRow;
        private bool _AllowUserToDeleteRow;
        private bool _AllowUserToFilter;
        private bool _AllowUserSettings;
        private bool _AllowUserToSaveOrder;
        private Form _OwningForm;

        #endregion

        #region Properties

        public string TableName
        {
            get { return _TableName; }
            set { _TableName = value; }
        }

        public Form OwningForm
        {
            get { return _OwningForm; }
            set { _OwningForm = value; }
        }

        public bool AllowUserToSaveOrder
        {
            get { return _AllowUserToSaveOrder; }
            set { _AllowUserToSaveOrder = value; }
        }

        public bool AllowUserSettings
        {
            get { return _AllowUserSettings; }
            set { _AllowUserSettings = value; }
        }

        public bool AllowUserToFilter
        {
            get { return _AllowUserToFilter; }
            set { _AllowUserToFilter = value; }
        }

        public bool AllowUserToDeleteRow
        {
            get { return _AllowUserToDeleteRow; }
            set { _AllowUserToDeleteRow = value; }
        }

        public bool AllowUserToAddRow
        {
            get { return _AllowUserToAddRow; }
            set { _AllowUserToAddRow = value; }
        }

        public int CurrentColumnID
        {
            get { return _CurrentColumnID; }
            set { _CurrentColumnID = value; }
        }

        public int CurrentRowID
        {
            get { return _CurrentRowID; }
            set { _CurrentRowID = value; }
        }

        public SqlConnection TableConnection
        {
            get
            {

                if (_TableConnection != null && _TableConnection.State == ConnectionState.Open)
                {
                    _TableConnection.Close();
                    _TableConnection.Open();
                }


                return _TableConnection;
            }
            set { _TableConnection = value; }
        }

        public Dictionary<int, Parameter> Parameters
        {
            get { return _Parameters; }
            set { _Parameters = value; }
        }

        public BindingSource BindingSource
        {
            get {
                _BindingSource = SQLAdapter.GetBindingSource(this);
                return _BindingSource; }
            set { _BindingSource = value; }
        }

        public SqlDataAdapter SqlDataAdapter
        {
            get
            {
                _SqlDataAdapter = SQLAdapter.GetDataAdapter(this);
                return _SqlDataAdapter;
            }
            set { _SqlDataAdapter = value; }
        }

        public DataTable DataTable
        {
            get
            {
                _DataTable = SQLAdapter.GetDataTable(this);
                return _DataTable;
            }
            set { _DataTable = value; }
        }

        public DataSet DataSet
        {
            get
            {
                _DataSet = SQLAdapter.GetDataSet(this);
                return _DataSet;
            }
            set { _DataSet = value; }
        }

        public DataGridView DataGridView
        {
            get { return _DataGridView; }
            set
            {
                _DataGridView = value;
                SetDataGridView(this);
            }
        }

        public Columns Columns
        {
            get { return _Columns; }
            set { _Columns = value; }
        }

        public int SelectNumber
        {
            get { return _SelectNumber; }
            set { _SelectNumber = value; }
        }

        internal Selectors Selectors
        {
            get { return _Selectors; }
            set { _Selectors = value; }
        }

        public string Orderer
        {
            get { return _Orderer; }
            set { _Orderer = value; }
        }

        public string Command
        {
            get { return _Command; }
            set { _Command = value; }
        }

        public int CurrentID
        {
            get
            {
                if (DataGridView != null && DataGridView.CurrentRow != null)
                {
                    _CurrentID = DataGridView.CurrentRow.Index;
                    return _CurrentID;
                }
                return 0;
            }

        }

        #endregion

        #region Methods

        #region Parameter Methods

        public void AddParameter(Column column, object value,string displayValue="",
    Operator.Conditional conditional = Operator.Conditional.AND,
    Operator.Rational rational = Operator.Rational.equal)
        {
            for (int i = 0; i <= 50; i++)
            {
                if (!Parameters.ContainsKey(i))
                {
                    Parameter parameter = new Parameter();
                    parameter.Column = column;
                    parameter.ConditionalOperator = conditional;
                    parameter.RationalOperator = rational;
                    parameter.ParameterName = column.Name + i;
                    parameter.Value = value;
                    if (displayValue == "")
                    {
                        if (value == null)
                        {
                            displayValue = string.Empty;
                        }
                        else
                        {
                            displayValue = value.ToString();
                        }
                    }
                    parameter.DisplayValue = displayValue;
                    Parameters.Add(i, parameter);
                    break;
                }
            }
        }

        public void AddParameter(string columnName, object value,string displayValue="", Operator.Conditional conditional = Operator.Conditional.AND,
    Operator.Rational rational = Operator.Rational.equal)
        {
            AddParameter(Columns[columnName], value,displayValue, conditional, rational);

        }

        public void RemoveParameter(int index)
        {
            if (Parameters.ContainsKey(index))
                Parameters.Remove(index);
        }

        public void RemoveParameter(string columnName)
        {
            foreach (KeyValuePair<int, Parameter> tempParameter in Parameters)
            {
                if (columnName == tempParameter.Value.Column.Name)
                {
                    RemoveParameter(tempParameter.Key);
                    return;
                }
            }
        }

        public void ChangeParameter(Column column, object newValue)
        {
            foreach (KeyValuePair<int, Parameter> tempParameter in Parameters)
            {
                int i = 0;
                if (column.Name == tempParameter.Value.Column.Name)
                {
                    tempParameter.Value.Value = newValue;
                    if (i > 0) RemoveParameter(tempParameter.Key);
                    i++;
                }
            }
        }

        public void ChangeParameter(string columnName, object newValue)
        {
            foreach (KeyValuePair<int, Parameter> tempParameter in Parameters)
            {
                int i = 0;
                if (columnName == tempParameter.Value.Column.Name)
                {
                    tempParameter.Value.Value = newValue;
                    if (i > 0) RemoveParameter(tempParameter.Key);
                    i++;
                }
            }
        }

        public void ChangeParameter(int key, object newValue)
        {
            if (Parameters.ContainsKey(key))
                Parameters[key].Value = newValue;
        }

        public bool ContainsParameter(string columnName)
        {
            foreach (KeyValuePair<int, Parameter> tempParameter in Parameters)
            {
                if (columnName == tempParameter.Value.Column.Name)
                {
                    return true;
                }
            }
            return false;
        }

        public int GetParameterKey(string columnName)
        {
            int key = 99;
            foreach (KeyValuePair<int, Parameter> tempParameter in Parameters)
            {
                if (columnName == tempParameter.Value.Column.Name)
                {
                    key = tempParameter.Key;
                    break;
                }
            }

            return key;
        }

        public virtual void SetDefaultParameters()
        {
            Parameters.Clear();
        }

        #endregion

        public void Save(object sender, int rowIndex, int columnIndex, object value)
        {
            string identifierName = "ID";
            DataGridViewColumn column = DataGridView.Columns[columnIndex];
            if (column != null)
            {
                string columnName = column.Name;
                Type type = Columns[columnName].SystemType;
                int indexer = (int)DataGridView.Rows[rowIndex].Cells[identifierName].Value;
                Columns[columnName].Save(this, indexer, value, identifierName);
                Control control = Columns[columnName].Control;
                DataGridViewCell cell = DataGridView.Rows[rowIndex].Cells[columnIndex];
                if (sender is DataGridView) CellValueToControlValue(control, cell, type);
                //if(sender is Control) cell.Value = value.ToString();             
            }
        }

        #endregion
    } 

As we se it heredits the DgvAdapter class. The main methods are those witch works with the Parameters dictionary. They search for an parameter, add one, change one etc.

Importand is that the Properti TableConnection opens the connection only if its closed. Other wise it uses always the same one.

Also interesting is that the SetDefaultParameters method is virtual so it clears all parameters but it can be called from any Table Class so we will never forget to clean the parameters before we set the default ones.

The Save() method uses methods from the DgvAdapter class that are explained afterwards. Importand is to say that the method defines properties and values that are needed to accomplish a save action.

NOTICE: in this DataGridView binding every Cell change is saved immediately. Changes are not made by Rows only Cells are saved one by one.

SQLAdapter

This class is static so we can call it without initialization. This one I would like also tho show first and then go trough the methods.

public static class SQLAdapter
    {
        //BASIS
        //{0}-number
        //{1} -tablename
        //{2} - parameter
        //{3} - orderer
        //{4} - selector

        //PARAMETERBASIS
        //{0} - Conditional operator
        //{1} - FieldName
        //{2} - Rational operator
        //{3} - ParameterName

        //private static string simpleSelectBais = "SELECT TOP {0} {4} FROM {1}  {2}";

        public static string numberSelectBasis =
            "WITH TempTable AS (SELECT TOP {0} {4} FROM {1}  {2} ORDER BY [{3}] DESC) SELECT {4} FROM TempTable ORDER BY [{3}] ASC";
        public static string simpleSelectBasis =
            "SELECT TOP {0} {4} FROM {1}  {2}";
        public static string countBasiss =
            "WITH TempTable AS (SELECT TOP {0} {4} FROM {1}  {2} ORDER BY [{3}] DESC) SELECT COUNT ({4}) FROM TempTable";
        public static string groupSimpleSelect =
            "SELECT {4} FROM {1}  {2} GROUP BY [{3}]";
        public static string insertBasis = "INSERT INTO {0} ({1}) VALUES ({2})";

        private static string parameterBasis = " {0} [{1}] {2} @{3} ";

        //Operator _Operator = new Operator();

        public static string CommandString(object table, string basis)
        {
            //if(table is Table)
            Table tempTable = (Table)table;

            string tempCommandString = string.Empty;
            string tempParameterString = string.Empty;

            int i = 0;
            foreach (KeyValuePair<int, Parameter> par in tempTable.Parameters)
            {
                string conditionalOperator = Operator.ConditionalToString(par.Value.ConditionalOperator);
                if (i == 0) conditionalOperator = "WHERE ";
                tempParameterString += String.Format(parameterBasis,
                    conditionalOperator,
                    par.Value.Column.Name,
                    Operator.RationalToString(par.Value.RationalOperator),
                    par.Value.ParameterName);
                i++;
            }

            string tempSelectors = string.Empty;

            for (int index = 0; index < 100; index++)
            {
                string comma = ",";
                if (index == 0) comma = string.Empty;
                if (tempTable.Selectors[index] == null) break;
                tempSelectors += tempTable.Selectors[index];

            }

            tempCommandString = String.Format(basis,
                tempTable.SelectNumber,
                tempTable.TableName,
                tempParameterString,
                tempTable.Orderer,
                tempSelectors);

            return tempCommandString;
        }

        public static SqlDataAdapter GetDataAdapter(Table table)
        {
            SqlDataAdapter adapter = new SqlDataAdapter();

            string basis = simpleSelectBasis;
            if (table.SelectNumber != 0) basis = numberSelectBasis;
            string command = CommandString(table, basis);

            adapter.SelectCommand = new SqlCommand(command, table.TableConnection);
            foreach (KeyValuePair<int, Parameter> tempParameter in table.Parameters)
            {
                adapter.SelectCommand.Parameters.Add("@" +
                    tempParameter.Value.ParameterName,
                    tempParameter.Value.Column.SqlDataBaseType).Value = tempParameter.Value.Value;
            }
            return adapter;

        }

        public static DataTable GetDataTable(Table table)
        {
            DataTable resultDataTable = new DataTable();
            GetDataAdapter(table).Fill(resultDataTable);
            return resultDataTable;
        }

        public static DataSet GetDataSet(Table table)
        {
            DataSet dataSet = new DataSet();
            GetDataAdapter(table).Fill(dataSet,table.TableName);
            return dataSet;
        }

        public static BindingSource GetBindingSource(Table table)
        {
            BindingSource bindingSource = new BindingSource();
            bindingSource.DataSource = GetDataSet(table);
            bindingSource.DataMember = table.TableName;
            return bindingSource;
        }

        public static SqlDataReader GetDataReader(Table table)
        {
            SqlDataReader dr;

            if (table.TableConnection != null && table.TableConnection.State == ConnectionState.Closed)
                table.TableConnection.Open();

            SqlCommand cmd = new SqlCommand(CommandString(table, numberSelectBasis), table.TableConnection);
            foreach (KeyValuePair<int, Parameter> tempParameter in table.Parameters)
            {
                cmd.Parameters.Add("@" +
                    tempParameter.Value.ParameterName,
                    tempParameter.Value.Column.SqlDataBaseType).Value = tempParameter.Value.Value;
            }
            dr = cmd.ExecuteReader();
            return dr;
        }

        public static void NewRow(Table table)
        {
            if (table.TableConnection != null && table.TableConnection.State == ConnectionState.Closed)
                table.TableConnection.Open();

            string columns = string.Empty;
            string values = string.Empty;
            string command = string.Empty;

            int i = 0;
            List<int> parametersToDeleted = new List<int>();
            foreach (KeyValuePair<int, Parameter> tempParameter in table.Parameters)
            {
                if (tempParameter.Value.RationalOperator == Operator.Rational.equal)
                {
                    string comma = ",";
                    if (i == 0) comma = string.Empty;
                    columns +=comma+ tempParameter.Value.Column.Name;
                    values += comma + "@"+tempParameter.Value.ParameterName;
                    i++;
                }
                else
                {
                    parametersToDeleted.Add(tempParameter.Key);
                }
            }

            foreach (int index in parametersToDeleted)
            {
                table.Parameters.Remove(index);
            }

            command = String.Format(insertBasis, table.TableName, columns, values);

            SqlCommand cmd = new SqlCommand(command, table.TableConnection);
            foreach (KeyValuePair<int, Parameter> tempParameter in table.Parameters)
            {
                cmd.Parameters.Add("@" +
                    tempParameter.Value.ParameterName,
                    tempParameter.Value.Column.SqlDataBaseType).Value = tempParameter.Value.Value;
            }
            cmd.ExecuteNonQuery();
          
        }

        public static int Count(Table table)
        {
            int count = 0;

            if (table.TableConnection != null && table.TableConnection.State == ConnectionState.Closed)
                table.TableConnection.Open();

            SqlCommand cmd = new SqlCommand(CommandString(table, countBasiss), table.TableConnection);

            foreach (KeyValuePair<int, Parameter> tempParameter in table.Parameters)
            {
                cmd.Parameters.Add("@" +
                    tempParameter.Value.ParameterName,
                    tempParameter.Value.Column.SqlDataBaseType).Value = tempParameter.Value.Value;
            }
            count = (int)cmd.ExecuteScalar();

            return count;
        }

        public static void SaveCommand(string tableName, string columnName,
            SqlConnection connection, SqlDbType sqlType, int identifier, object newValue, string identifierName = "ID")
        {
            string command = "UPDATE {0} SET {1}=@{1} WHERE [{2}]=@{2} ";
            command = String.Format(command, tableName, columnName, identifierName);

            if (connection != null && connection.State == ConnectionState.Closed)
                connection.Open();
            SqlCommand cmd = new SqlCommand(command, connection);
            cmd.Parameters.Add("@" + columnName, sqlType).Value = newValue;
            cmd.Parameters.Add("@" + identifierName, SqlDbType.Int).Value = identifier;
            cmd.ExecuteNonQuery();
        }

        private static string[] GetAutoCompleteSourceWithoutDelete(Table inputTable, string autocompleteColumnName)
        {
            SqlDataReader tableReader = GetDataReader(inputTable);

            string[] sourceValues = new string[tableReader.FieldCount];

            int i = 0;
            while (tableReader.Read())
            {
                sourceValues[i] = tableReader[autocompleteColumnName].ToString();
                i++;
            }

            return sourceValues;
        }

        public static AutoCompleteStringCollection GetAutocompleteStringCollection(Table inputTable, string columnName, List<string> notIncludedParameters)
        {
            AutoCompleteStringCollection stringCollection = new AutoCompleteStringCollection();
            inputTable.Parameters.Clear();

            if (notIncludedParameters != null)
            {
                foreach (string column in notIncludedParameters)
                {
                    inputTable.AddParameter(inputTable.Columns[column], false);
                }
            }
            

            SqlDataReader tableReader = GetDataReader(inputTable);

            while (tableReader.Read())
            {
                stringCollection.Add(tableReader[columnName].ToString());
            }

            return stringCollection;
        }

        public static AutoCompleteStringCollection GetAutocompleteStringCollection(Table inputTable, string columnName)
        {
            AutoCompleteStringCollection stringCollection = new AutoCompleteStringCollection();
            inputTable.Parameters.Clear();

            SqlDataReader tableReader = GetDataReader(inputTable);

            while (tableReader.Read())
            {
                stringCollection.Add(tableReader[columnName].ToString());
            }

            return stringCollection;
        }


    }

In the first method CommandString we generate the command string for a specific table using an commandstring Basis definet at the beginning of the class. The Method uses the Table Parameters Dictionary to get all needed data for the command string.

The next methods are used to get Objects like DataTable, SQlDataAdapter etc. Most importand of the is the GetDataAdapter method in witch we perform the connection of the SQL command string and the SQL command parameters.

The NewRow method is used to add a new row to the table. Important is that it uses the filter parameters as default values. If we won’t use it like this we could have this scenario: We are working in the DataGridView. A filter is on. We add a new row but we cant see it. This is because the new row doesn’t fit in the filter. Because of that we always use the Filter properties as default values for the new row. In that case in our scenario the added rows will always fit in the filter we are currently in.

The Count Method is used just to get the number of rows in a table and the AutoCompleteStringCollection Methos ar getting a string collection of a specific column of the table for a autocompletecustom source. A nice method we could always use.

DgvAdapter

The largest Class in which we do all the hard work

public class DgvAdapter
    {
        #region Fields

        private Table _ParentTable;
        private DataGridView _ParentDataGrdiView;
        private ContextMenuStrip _ContextMenuStrip; 

        #endregion

        #region Properties

        public ContextMenuStrip ContextMenuStrip
        {
            get { return _ContextMenuStrip; }
            set { _ContextMenuStrip = value; }
        }

        public DataGridView ParentDataGrdiView
        {
            get { return _ParentDataGrdiView; }
            set { _ParentDataGrdiView = value; }
        }

        public Table ParentTable
        {
            get { return _ParentTable; }
            set { _ParentTable = value; }
        } 

        #endregion

        #region Methods

        public void SetDataGridView(Table parentTable)
        {
            if (parentTable != null)
                ParentTable = parentTable;

            if (parentTable.DataGridView != null)
            {
                ParentDataGrdiView = ParentTable.DataGridView;

                ParentDataGrdiView.VirtualMode = true;
                ParentDataGrdiView.AllowUserToAddRows = false;
                ParentDataGrdiView.AllowUserToDeleteRows = false;
                ParentDataGrdiView.AutoGenerateColumns = false;
                ParentDataGrdiView.RowHeadersVisible = false;
                ParentDataGrdiView.AllowUserToResizeRows = false;
                ParentDataGrdiView.AllowUserToOrderColumns = true;
                ParentDataGrdiView.EditMode = DataGridViewEditMode.EditOnEnter;
                ParentDataGrdiView.DataError += new System.Windows.Forms.DataGridViewDataErrorEventHandler(delegate(Object sender, DataGridViewDataErrorEventArgs e)
                {
                });
                SettContextMenu();
                ParentDataGrdiView.EditingControlShowing += EditinCotrolShowing;
                ParentDataGrdiView.RowEnter += RowEnter;
                ParentDataGrdiView.CellEnter += CellEnter;
                ParentDataGrdiView.CellValueChanged += Save;
                ParentDataGrdiView.MouseClick += MouseClick;
                ParentDataGrdiView.ColumnHeaderMouseClick += ColumnHeaderMouseClick;
                ParentDataGrdiView.CellFormatting += CellFormatting;
                ParentDataGrdiView.CellValidated += CellValidated;
                ParentDataGrdiView.CellValidating += CellValidating;
                ParentDataGrdiView.PreviewKeyDown += ContextMenuHotKeyEvent;
            }

            AddColumns();
        }


        #region ContextMenu

        private void SettContextMenu()
        {
            ContextMenuStrip = new ContextMenuStrip();
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Filtern", " (Strg+Alt)"), null, FilterClick);
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Filter entfehrnen", " (Strg+Leerzeichen)"), null, RemoveFilterClick);
            ContextMenuStrip.Items.Add("-");
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Neu", " (Strg+X)"), null, NewClick);
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Kopieren", " (Strg+C)"), null, CopyClick);
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Löschen", " (Entf)"), null, DeleteClick);
            ContextMenuStrip.Items.Add("-");
            ContextMenuStrip.Items.Add("Zum Anfang", null, ToFirstRow);
            ContextMenuStrip.Items.Add("Zum Ende", null, ToLastRow);
            ContextMenuStrip.Items.Add("-");
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Anordnung speichern", " (Strg+A)"), null, SaveColumnOrder);
            ContextMenuStrip.Items.Add(String.Format("{0,-1}{1}", "Einstellungen", " (Strg+S)"), null, OpenColumnSettings);
            ContextMenuStrip.Items.Add("-");
            ContextMenuStrip.Items.Add("Aktualisieren", null, Refresh);

            ParentTable.DataGridView.ContextMenuStrip = ContextMenuStrip;
            SetContextMenuStripSettings();
        }

        private void SetContextMenuStripSettings()
        {
            if (ParentTable.AllowUserToFilter)
            {
                ContextMenuStrip.Items[0].Enabled = true;
                ContextMenuStrip.Items[1].Enabled = true;
            }
            else
            {
                ContextMenuStrip.Items[0].Enabled = false;
                ContextMenuStrip.Items[1].Enabled = false;
            }

            if (ParentTable.AllowUserToAddRow)
            {
                ContextMenuStrip.Items[3].Enabled = true;
                ContextMenuStrip.Items[4].Enabled = true;
            }
            else
            {
                ContextMenuStrip.Items[3].Enabled = false;
                ContextMenuStrip.Items[4].Enabled = false;
            }

            if (ParentTable.AllowUserToDeleteRow) ContextMenuStrip.Items[5].Enabled = true;
            else ContextMenuStrip.Items[5].Enabled = false;

            if (ParentTable.AllowUserToSaveOrder) ContextMenuStrip.Items[10].Enabled = true;
            else ContextMenuStrip.Items[10].Enabled = false;

            if (ParentTable.AllowUserSettings) ContextMenuStrip.Items[11].Enabled = true;
            else ContextMenuStrip.Items[11].Enabled = false;
        }

        private void MouseClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                var r = ParentTable.DataGridView.HitTest(e.X, e.Y);
                ParentTable.CurrentRowID = r.RowIndex;
                ParentTable.CurrentColumnID = r.ColumnIndex;

                foreach (DataGridViewRow row in ParentTable.DataGridView.SelectedRows)
                {
                    row.Selected = false;
                }
                ParentTable.DataGridView.Rows[ParentTable.CurrentRowID].Selected = true;
                ParentTable.DataGridView.CurrentCell = ParentTable.DataGridView.Rows[ParentTable.CurrentRowID].Cells[ParentTable.CurrentColumnID];
                ContextMenuStrip.Show(ParentTable.DataGridView, new Point(e.X, e.Y));
            }
        }

        #region ContextMenuMouseClick Events

        private void FilterClick(Object sender, EventArgs e)
        {
            Column column = ParentTable.Columns[ParentTable.DataGridView.Columns[ParentTable.CurrentColumnID].Name];
            Filter frm = new Filter(ParentTable, column, false);
            frm.ShowDialog();

            if (frm.DialogResult == DialogResult.OK)
            {
                RefreshDataGridView();
            }
        }

        private void RemoveFilterClick(Object sender, EventArgs e)
        {
            ParentTable.SetDefaultParameters();
            ParentTable.RefreshDataGridView();
        }

        private void NewClick(Object sender, EventArgs e)
        {
            SQLAdapter.NewRow(ParentTable);
            RefreshDataGridView();
        }

        private void CopyClick(Object sender, EventArgs e)
        {
            Copy();
            RefreshDataGridView();
        }

        private void Copy()
        {

            List<string> CurrentParameters = new List<string>();
            //Add Parameter that Can´t be Added
            CurrentParameters.Add("ID");

            List<int> ParametersToDelete = new List<int>();
            
            //Set Current Parameters
            foreach(KeyValuePair<int,Parameter> par in ParentTable.Parameters)
            {
                CurrentParameters.Add(par.Value.Column.Name);
            }

            //Set New Parameters
            for (int index = 0; index <ParentTable.DataGridView.ColumnCount; index++)
            {
                if (ParentTable.Columns[index] != null)
                {
                    Column column= ParentTable.Columns[index];
                    var value = ParentTable.DataGridView.Rows[ParentTable.CurrentRowID].Cells[index].Value;
                    if(!CurrentParameters.Contains(column.Name))
                    ParentTable.AddParameter(column, value);

                }
            }

            //Add new Row
            SQLAdapter.NewRow(ParentTable);

            //List Parameters that should be deleted
            foreach (KeyValuePair<int, Parameter> par in ParentTable.Parameters)
            {
                int key = par.Key;
                string name = par.Value.Column.Name;
                if (!CurrentParameters.Contains(name))
                    ParametersToDelete.Add(key);
            }

            //Delete Parameters in List
            foreach (int i in ParametersToDelete)
            {
                ParentTable.Parameters.Remove(i);
            }


        }

        private void DeleteClick(Object sender, EventArgs e)
        {
            Delete(ParentTable.CurrentRowID);
            RefreshDataGridView();
        }

        private void ToFirstRow(Object sender, EventArgs e)
        {
            MoveToRow(0);
        }

        private void ToLastRow(Object sender, EventArgs e)
        {
            int rowCount = ParentTable.DataGridView.RowCount;
            if (rowCount > 0)
                MoveToRow(rowCount - 1);
        }

        private void MoveToRow(int rowIndex)
        {
            ParentTable.DataGridView.FirstDisplayedScrollingRowIndex = rowIndex;
            if(rowIndex<ParentTable.DataGridView.Rows.Count)
            ParentTable.DataGridView.Rows[rowIndex].Selected = true;
            if (ParentTable.DataGridView.CurrentCell != null)
            {
                ParentTable.DataGridView.CurrentCell = ParentTable.DataGridView.Rows[rowIndex].Cells[ParentTable.DataGridView.CurrentCell.OwningColumn.Index];
            }
        }

        private void SaveColumnOrder(Object sender, EventArgs e)
        {
            if (ParentTable.OwningForm != null)
                ParentTable.Columns.SaveSettings(ParentTable);
        }

        private void OpenColumnSettings(Object sender, EventArgs e)
        {
            if (ParentTable.OwningForm != null)
            {
                ParentTable.OwningForm.Cursor = Cursors.WaitCursor;
                TableSettingsForm frm = new TableSettingsForm(ParentTable.OwningForm, ParentTable);
                frm.Show();
                ParentTable.OwningForm.Cursor = Cursors.Default;
            }
        }

        private void Refresh(Object sender, EventArgs e)
        {
            RefreshDataGridView();
        }

        private void ContextMenuHotKeyEvent(object sender, PreviewKeyDownEventArgs e)
        {

            if (e.Control && e.Alt && ParentTable.AllowUserToFilter )
            {
                FilterClick(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.Space && ParentTable.AllowUserToFilter)
            {
                RemoveFilterClick(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.X && ParentTable.AllowUserToAddRow)
            {
                NewClick(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.C && ParentTable.AllowUserToAddRow)
            {
                CopyClick(sender, e);
            }
            else if (e.KeyCode == Keys.Delete && ParentTable.AllowUserToDeleteRow)
            {
                DeleteClick(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.A && ParentTable.AllowUserToSaveOrder)
            {
                SaveColumnOrder(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.S && ParentTable.AllowUserSettings)
            {
                OpenColumnSettings(sender, e);
            }
            else if (e.Control && e.KeyCode == Keys.Enter)
            {
                Refresh(sender, e);
            }
            

        }

        #endregion

        #endregion

        public void RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            ParentTable.CurrentRowID = e.RowIndex;
            ParentTable.DataGridView.Rows[ParentTable.CurrentRowID].Selected = true;
            foreach (DataGridViewColumn column in ParentTable.DataGridView.Columns)
            {
                Control control = ParentTable.Columns[column.Name].Control;
                Type type=ParentTable.Columns[column.Name].SystemType;
                DataGridViewRow row = ParentTable.DataGridView.Rows[e.RowIndex];
                DataGridViewCell cell = row.Cells[ParentTable.Columns[column.Name].Name];
                CellValueToControlValue(control, cell, type);
            }
            VirtualRowEnter(sender, e);
        }

        public virtual void VirtualRowEnter(object sender, DataGridViewCellEventArgs e)
        {

        }

        public void CellEnter(object sender, DataGridViewCellEventArgs e)
        {
            ParentTable.CurrentColumnID = e.ColumnIndex;
        }

        public void EditinCotrolShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            string name = ((DataGridView)sender).CurrentCell.OwningColumn.Name;
            Control formControl = ParentTable.Columns[name].Control;
            if (formControl != null)
            {
                if (formControl is ComboBox && e.Control is DataGridViewComboBoxEditingControl)
                {
                    ((ComboBox)e.Control).DropDownStyle = ((ComboBox)formControl).DropDownStyle;
                    ((ComboBox)e.Control).AutoCompleteSource = ((ComboBox)formControl).AutoCompleteSource;
                    ((ComboBox)e.Control).AutoCompleteMode = ((ComboBox)formControl).AutoCompleteMode;
                    ((ComboBox)e.Control).AutoCompleteCustomSource = ((ComboBox)formControl).AutoCompleteCustomSource;
                }

                if (formControl is TextBox && e.Control is DataGridViewTextBoxEditingControl)
                {
                    ((TextBox)e.Control).AutoCompleteSource = ((TextBox)formControl).AutoCompleteSource;
                    ((TextBox)e.Control).AutoCompleteMode = ((TextBox)formControl).AutoCompleteMode;
                    ((TextBox)e.Control).AutoCompleteCustomSource = ((TextBox)formControl).AutoCompleteCustomSource;
                }
            }

            e.Control.PreviewKeyDown -= ContextMenuHotKeyEvent;
            e.Control.PreviewKeyDown += ContextMenuHotKeyEvent;
        }

        private void ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left && ParentTable.AllowUserToFilter)
            {
                DataGridViewColumn dgvColumn = ((DataGridView)sender).Columns[e.ColumnIndex];
                Column column = ParentTable.Columns[dgvColumn.Name];
                Filter frm = new Filter(this, column,true);
                frm.ShowDialog();

                if (frm.DialogResult == DialogResult.OK)
                {
                    RefreshDataGridView();
                }
            }
        }

        public void Save(object sender, DataGridViewCellEventArgs e)
        {

            if (sender is DataGridView && e.RowIndex >= 0)
            {
                int rowIndex = e.RowIndex;
                int columnIndex = e.ColumnIndex;
                object value = ((DataGridView)sender).Rows[rowIndex].Cells[columnIndex].Value;
                ParentTable.Save(sender,rowIndex, columnIndex, value);
            }
        }

        public void Delete(int rowIndex)
        {
            DialogResult result = MessageBox.Show("Sicher das Sie diesen Eintrag löschen wollen?", "Löschbestätigung", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
            {
                int columnIndex = ParentTable.DataGridView.Columns["Deleted"].Index;
                ParentTable.Save(ParentDataGrdiView,rowIndex, columnIndex, true);
            }
        }

        public void CellValueToControlValue(Control control, DataGridViewCell cell, Type type)
        {
            if (control != null && cell != null)
            {
                if (control is TextBox)
                {

                    control.Text = cell.Value.ToString();

                    if(type==typeof(DateTime))
                    {
                        DateTime date;
                        if(DateTime.TryParse(cell.Value.ToString(),out date))
                        {
                            control.Text = date.ToShortDateString();
                        }
                    }
                    

                }
                else if (control is ComboBox)
                {
                    if(((DataGridViewComboBoxCell)cell).Value!=null)
                    ((ComboBox)control).SelectedValue = ((DataGridViewComboBoxCell)cell).Value;
                }
                else if (control is CheckBox)
                {
                    ((CheckBox)control).Checked = (bool)((DataGridViewCheckBoxCell)cell).Value;
                }
            }
        }

        public void RefreshDataGridView()
        {
            ParentTable.DataGridView.DataSource = ParentTable.BindingSource;
            ToLastRow(null, null);
        }

        public void FirstRefresh()
        {
            ParentTable.SetDefaultParameters();
            ParentTable.DataGridView.DataSource = ParentTable.BindingSource;

            ParentTable.Columns.LoadSettings(ParentTable);
            ParentTable.DataGridView.Columns.Clear();
            ParentTable.Columns.AddColumnsToDataGridView(ParentTable.DataGridView);
            ParentTable.Columns.SetDataGridViewColumnOrder(ParentDataGrdiView);

            ToLastRow(null, null);
        }

        public virtual void AddColumns()
        {

        }

        public static string GetSenderValue(object sender)
        {
            string columnValue = string.Empty;

            if (sender is DataGridViewCell)
            {
                DataGridViewCell cell = sender as DataGridViewCell;
                return cell.Value.ToString();
            }
            else if (sender is TextBox)
            {
                TextBox txtBox = sender as TextBox;
                return txtBox.Text;
            }

            return columnValue;
        }

        public static void SetSenderValue(object sender,string value)
        {
            string columnValue = string.Empty;

            if (sender is DataGridViewCell)
            {
                DataGridViewCell cell = sender as DataGridViewCell;
                cell.Value = value;
            }
            else if (sender is TextBox)
            {
                TextBox txtBox = sender as TextBox;
                txtBox.Text=value;
            }

        }

        public virtual void CellFormatting(object sender,DataGridViewCellFormattingEventArgs e)
        {
            //FOR LATER USAGE:
            //DataGridView dgv=sender as DataGridView;
            //DataGridViewRow row = dgv.Rows[e.RowIndex];
            //DataGridViewColumn column = dgv.Columns[e.ColumnIndex];
            //DataGridViewCell cell = row.Cells[e.ColumnIndex];
        }

        public virtual void CellValidated(object sender, DataGridViewCellEventArgs e)
        {

        }

        public virtual void CellValidating(object sender, DataGridViewCellValidatingEventArgs e)
        {

        }




        #endregion

    } 

The main method of this Class is the SetDatagridView Method in witch we sett all DatagridView settings and needed events. Most of the methots are self explained. Some of them are also virtual for specific settings in each table.

Filter

The Filter Class is actually a WinForm that we use to Filter in the DataGridView.

public partial class Filter : Form
    {

        #region Fields
        
        private Column _UsedColumn;
        private Control _UsedControl;
        private Table _UsedTable;
        private int _ResultCount;
        private bool _ChangesMade;
        private string _ColumnNameToFilter;
        private string _FilterValue;
        private string _FilterDisplayValue;
        private bool _DeleteInputColumn;
        private DataTable _ColumnsDataTable;

        #endregion

        #region Properties

        public DataTable ColumnsDataTable
        {
            get
            {

                if (_ColumnsDataTable == null)
                {
                    _ColumnsDataTable = new DataTable();
                    _ColumnsDataTable.Columns.Add("ValueM", typeof(string));
                    _ColumnsDataTable.Columns.Add("DisplayM", typeof(string));


                    for (int i = 0; i < Columns.columnNumber; i++)
                    {
                        if (UsedTable.Columns[i] == null) break;
                        DataRow row;
                        row = _ColumnsDataTable.NewRow();
                        row["ValueM"] = UsedTable.Columns[i].Name;
                        row["DisplayM"] = UsedTable.Columns[i].HeaderText;
                        _ColumnsDataTable.Rows.Add(row);

                    }


                }

                return _ColumnsDataTable;
            }
            set { _ColumnsDataTable = value; }
        }

        public string FilterDisplayValue
        {
            get { 

                if (UsedControl is ComboBox)
                {
                    _FilterDisplayValue = cboNewValue.Text;
                }
                
                return _FilterDisplayValue; }
            set { _FilterDisplayValue = value; }
        }

        public bool DeleteInputColumn
        {
            get { return _DeleteInputColumn; }
            set { _DeleteInputColumn = value; }
        }

        public string FilterValue
        {
            get
            {

                if (UsedControl is TextBox || UsedControl == null || UsedControl is MaskedTextBox)
                {
                    _FilterValue = txtNewValue.Text;
                    FilterDisplayValue = _FilterValue;
                }
                else if (UsedControl is ComboBox)
                {
                    _FilterValue = cboNewValue.SelectedValue.ToString();
                    FilterDisplayValue = cboNewValue.Text;
                }
                else if (UsedControl is CheckBox)
                {
                    _FilterValue = chkNewValue.Checked.ToString();
                    FilterDisplayValue = _FilterValue;
                }
                return _FilterValue;
            }
            set { _FilterValue = value; }
        }

        public string ColumnNameToFilter
        {
            get
            {
                _ColumnNameToFilter = columns.SelectedValue.ToString();
                return _ColumnNameToFilter;
            }
            set { _ColumnNameToFilter = value; }
        }

        public bool ChangesMade
        {
            get { return _ChangesMade; }
            set { _ChangesMade = value; }
        }

        public int ResultCount
        {
            get {

                _ResultCount = SQLAdapter.Count(UsedTable);
                return _ResultCount; }
            set { _ResultCount = value; }
        }

        public Table UsedTable
        {
            get { return _UsedTable; }
            set { _UsedTable = value; }
        }

        public Column UsedColumn
        {
            get {
                _UsedColumn = UsedTable.Columns[columns.SelectedValue.ToString()];
                return _UsedColumn; }
            set { _UsedColumn = value; }
        }

        public Control UsedControl
        {
            get {
                _UsedControl = UsedTable.Columns[columns.SelectedValue.ToString()].Control;
                return _UsedControl; }
            set
            {
                _UsedControl = value;
                SelectUsedControl();
            }
        }

        #endregion

        #region Constructors

        public Filter(object sender, Column inputColumn, bool deleteInputColumn)
        {
            //Set Input Values
            if (sender is Table)
                UsedTable = (Table)sender;
            //UsedColumn = inputColumn;
            DeleteInputColumn = deleteInputColumn;

            //Default Initialisation
            InitializeComponent();

           
            SetComboBoxSourcesForParameterProperties();
            columns.SelectedValue = inputColumn.Name;

            //Delete Parmeter of he Column if the Filter Form is called by DataGridViewMouseHeaderClick
            if (DeleteInputColumn)
            {
                UsedTable.RemoveParameter(ColumnNameToFilter);
                ChangesMade = true;
                this.DialogResult = DialogResult.OK;
            }

            CreateDataGridViewColumns();
            LoadDataGridViewRows();

            UsedControl = UsedColumn.Control;

            this.DialogResult = DialogResult.Cancel;

            SetFormText();
        }
        
        #endregion

        #region Methods

        private void SetFormText()
        {
            this.Text = "Filter - " + columns.Text + " - Ergebnise: " + ResultCount;
        }

        private void SetComboBoxSourcesForParameterProperties()
        {
            foreach (var value in Enum.GetValues(typeof(Operator.Conditional)))
            {
                conditionalOperator.Items.Add(value.ToString());
            }
            conditionalOperator.SelectedIndex = 0;

            foreach (var value in Enum.GetValues(typeof(Operator.Rational)))
            {
                rationalOperator.Items.Add(value.ToString());
            }
            rationalOperator.SelectedIndex = 0;


            columns.ValueMember = "ValueM";
            columns.DisplayMember = "DisplayM";
            columns.DataSource = ColumnsDataTable;
            columns.Text = UsedColumn.HeaderText;
            columns.AutoCompleteSource = AutoCompleteSource.ListItems;
            columns.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
        }

        private void SelectUsedControl()
        {
            //Hide all NewValue Control
            txtNewValue.Visible = false;
            cboNewValue.Visible = false;
            chkNewValue.Visible = false;

            //Check what the UsedControl is and unhide its Parend control on the Filter Form and set it´s DataSource and AutocompleteSource
            if (UsedControl == null || UsedControl is TextBox || UsedControl is MaskedTextBox)
            {
                SetAutocompleteToTextBox();
                txtNewValue.Visible = true;
                txtNewValue.Select();
            }
            else if (UsedControl is ComboBox)
            {
                SetAutocompleteToComboBox();
                cboNewValue.Visible = true;
                cboNewValue.Select();
            }
            else if (UsedControl is CheckBox)
            {
                SetAutocompeteCheckBox();
                chkNewValue.Visible = true;
                chkNewValue.Select();
            }
        }

        private void SetAutocompleteToTextBox()
        {
            if (UsedControl is TextBox)
            {
                TextBox usedTextBox = UsedControl as TextBox;
                txtNewValue.AutoCompleteCustomSource = usedTextBox.AutoCompleteCustomSource;
                txtNewValue.AutoCompleteMode = usedTextBox.AutoCompleteMode;
                txtNewValue.AutoCompleteSource = usedTextBox.AutoCompleteSource;
            }
        }

        private void SetAutocompleteToComboBox()
        {
            ComboBox usedComboBox = UsedControl as ComboBox;

            cboNewValue.DataSource = usedComboBox.DataSource;
            cboNewValue.ValueMember = usedComboBox.ValueMember;
            cboNewValue.DisplayMember = usedComboBox.DisplayMember;

            cboNewValue.AutoCompleteCustomSource = usedComboBox.AutoCompleteCustomSource;
            cboNewValue.AutoCompleteMode = usedComboBox.AutoCompleteMode;
            cboNewValue.AutoCompleteSource = usedComboBox.AutoCompleteSource;
        }

        private void SetAutocompeteCheckBox()
        {
            chkNewValue.Text = ColumnNameToFilter;
        }

        private void columns_Validated(object sender, EventArgs e)
        {
            SetFormText();
            //UsedControl = UsedTable.Columns[ColumnNameToFilter].Control;
            SelectUsedControl();
        }

        private void CreateDataGridViewColumns()
        {
            DataGridViewTextBoxColumn ID = new DataGridViewTextBoxColumn();
            ID.DataPropertyName = "ID";
            ID.HeaderText = "ID";
            ID.Visible = false;
            dgvFilter.Columns.Add(ID);

            DataGridViewTextBoxColumn conditional = new DataGridViewTextBoxColumn();
            conditional.HeaderText = "AND/OR";
            conditional.Name = "Conditional";
            conditional.DataPropertyName = "Conditional";
            conditional.ReadOnly = true;
            conditional.Width = conditionalOperator.Width + 5;
            dgvFilter.Columns.Add(conditional);


            DataGridViewTextBoxColumn fields = new DataGridViewTextBoxColumn();
            fields.HeaderText = "Feld";
            fields.Name = "Feld";
            fields.DataPropertyName = "Feld";
            fields.ReadOnly = true;
            fields.Visible = false;
            fields.Width = columns.Width + 5;
            dgvFilter.Columns.Add(fields);

            DataGridViewTextBoxColumn fieldsDescription = new DataGridViewTextBoxColumn();
            fieldsDescription.HeaderText = "Feld";
            fieldsDescription.Name = "FeldDescription";
            fieldsDescription.DataPropertyName = "FeldDescription";
            fieldsDescription.ReadOnly = true;
            fieldsDescription.Visible = true;
            fieldsDescription.Width = columns.Width + 5;
            dgvFilter.Columns.Add(fieldsDescription);

            DataGridViewTextBoxColumn rational = new DataGridViewTextBoxColumn();
            rational.HeaderText = "Bedingung";
            rational.Name = "Rational";
            rational.DataPropertyName = "Rational";
            rational.ReadOnly = true;
            rational.Width = rationalOperator.Width + 5;
            dgvFilter.Columns.Add(rational);

            DataGridViewTextBoxColumn filterValue = new DataGridViewTextBoxColumn();
            filterValue.HeaderText = "Filter";
            filterValue.Name = "Filter";
            filterValue.ReadOnly = true;
            filterValue.Visible = false;
            filterValue.DataPropertyName = "Filter";
            filterValue.Width = cboNewValue.Width + 5;
            dgvFilter.Columns.Add(filterValue);

            DataGridViewTextBoxColumn filterDisplayValue = new DataGridViewTextBoxColumn();
            filterDisplayValue.HeaderText = "DisplayFilter";
            filterDisplayValue.Name = "DisplayFilter";
            filterDisplayValue.ReadOnly = true;
            filterDisplayValue.DataPropertyName = "DisplayFilter";
            filterDisplayValue.Width = cboNewValue.Width + 5;
            dgvFilter.Columns.Add(filterDisplayValue);


            DataGridViewButtonColumn buttonColumn = new DataGridViewButtonColumn();
            buttonColumn.Name = "Löschen";
            buttonColumn.HeaderText = "Löschen";
            buttonColumn.Text = "Löschen";
            buttonColumn.Width = 150;
            buttonColumn.UseColumnTextForButtonValue = true;
            dgvFilter.Columns.Add(buttonColumn);
            dgvFilter.CellContentClick += CellContentClick;
        }

        private void LoadDataGridViewRows()
        {
            dgvFilter.Rows.Clear();
            foreach (KeyValuePair<int, Parameter> param in UsedTable.Parameters)
            {
                string value = param.Value.Value.ToString();
                string name = param.Value.Column.Name;
                string descriptionName = param.Value.Column.HeaderText;
                string displayValue = value.ToString();
                if (param.Value.DisplayValue != null) displayValue = param.Value.DisplayValue;

                dgvFilter.Rows.Add(param.Key, param.Value.ConditionalOperator.ToString(),
                    name, descriptionName, param.Value.RationalOperator.ToString(),
                    value, displayValue);
            }
        }

        private void CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == dgvFilter.Columns["Löschen"].Index)
            {
                UsedTable.Parameters.Remove((int)dgvFilter.CurrentRow.Cells[0].Value);
                int count = SQLAdapter.Count(UsedTable);
                ResultCount = count;
                SetFormText();
                LoadDataGridViewRows();
                ChangesMade = true;
            }

        }

        private void dgvFilter_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void FinishFiltering()
        {
            if (ChangesMade)
            {
                this.DialogResult = DialogResult.OK;
            }
            else
            {
                this.DialogResult = DialogResult.Cancel;
            }
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            FinishFiltering();
        }

        private void btnADD_Click(object sender, EventArgs e)
        {
            if (!FilterValueValidated())
                return;
            
            //Add the Parameter and Count the Result table
            AddParameter();
            //Also load the Prameters to the DataGridView - the ID to delete if count is 0 is taken from the DataGridView
            LoadDataGridViewRows();
            int count = SQLAdapter.Count(UsedTable);

            if (count == 0)
            {
                MessageBox.Show("Keine Ergebnise für diese Eingabe!", "Filter", MessageBoxButtons.OK, MessageBoxIcon.Information);
                int lastRow = 0;
                if (dgvFilter.Rows.Count > 0)
                    lastRow = dgvFilter.Rows.Count - 1;
                UsedTable.Parameters.Remove((int)dgvFilter.Rows[lastRow].Cells[0].Value);
                LoadDataGridViewRows();
            }
            else
            {
                ResultCount = count;
                SetFormText();
                ChangesMade = true;
                LoadDataGridViewRows();
            }
        }

        private bool FilterValueValidated()
        {
            if (UsedControl == null || UsedControl is TextBox || UsedControl is MaskedTextBox)
            {
                string newValue = txtNewValue.Text;

                if (UsedTable.Columns[ColumnNameToFilter].SystemType == typeof(DateTime))
                {
                    DateTime time = DateTime.MinValue;
                    if (DateTime.TryParse(newValue, out time) && time != DateTime.MinValue)
                    {
                        txtNewValue.Text = time.ToShortDateString();
                        return true;
                    }

                    txtNewValue.Text = string.Empty;
                    MessageBox.Show("Wert kann nicht in ein Datum formatiert werden!", "Datum", MessageBoxButtons.OK, MessageBoxIcon.Warning);          
                }

                if (UsedTable.Columns[ColumnNameToFilter].SystemType == typeof(int))
                {
                    int i = 0;
                    if (Int32.TryParse(newValue, out i))
                    {
                        txtNewValue.Text = i.ToString();
                        return true;
                    }
                    txtNewValue.Text = "0";
                    MessageBox.Show("Wert kann nicht in ein Integer formatiert werden!", "Integer", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }

                if (UsedTable.Columns[ColumnNameToFilter].SystemType == typeof(bool))
                {
                    bool b = false;
                    if (Boolean.TryParse(newValue, out b))
                    {
                        txtNewValue.Text = b.ToString();
                        return true;
                    }
                    b = false;
                    txtNewValue.Text = b.ToString();
                    MessageBox.Show("Wert kann nicht in ein Boolean formatiert werden!", "Boolean", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                return false;
            }
            else if (UsedControl is CheckBox)
            {
                return true;
            }
            else if (UsedControl is ComboBox)
            {
                return true;
            }


            return false;
        }

        private void AddParameter()
        {
            Operator.Conditional conditional = Operator.Conditional.AND;
            if (conditionalOperator.SelectedIndex == 1) conditional = Operator.Conditional.OR;

            Operator.Rational rational = Operator.Rational.equal;
            switch (rationalOperator.SelectedIndex)
            {
                case 1:
                    rational = Operator.Rational.notequal;
                    break;
                case 2:
                    rational = Operator.Rational.bigger;
                    break;
                case 3:
                    rational = Operator.Rational.smaller;
                    break;
                case 4:
                    rational = Operator.Rational.like;
                    break;
                default:
                    rational = Operator.Rational.equal;
                    break;
            }
            UsedTable.AddParameter(ColumnNameToFilter, FilterValue,FilterDisplayValue, conditional, rational);
        }

        private void NewValue_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                btnADD_Click(null, null);
                if (ChangesMade)
                    btnOK_Click(null, null);
            }
        }

        private void Filter_FormClosed(object sender, FormClosedEventArgs e)
        {
            FinishFiltering();
        }

    }

Importand is that the NewValue Controls are owerlaping in the Form Desinger and we show or hide the one we want to use.

Using the code

Now after we have all needed Classes we´ll se how to use them.

Create New Table

To create a new table with the table specific settings we can use a code like this.

 public class Users: Table
    {
        
        public Users(Form owningForm, DataGridView owningDataGridView)
        {
            OwningForm = owningForm;

            TableConnection = DatabaseConnection.ICSDB();
            TableName = "dbo.Users";
            SelectNumber = 100;
            Orderer = "ID";
            Selectors.Add("*");

            AllowUserToAddRow = true;
            AllowUserToDeleteRow=true;
            DataGridView = owningDataGridView;
        }

        public override void AddColumns()
        {
            Columns.Add("ID", typeof(int), SqlDbType.Int,true,"Nr",40,null,true);
            Columns.Add("Name", typeof(string),SqlDbType.NVarChar,true,null,200);
            Columns.Add("Password", typeof(string));
            Columns.Add("GroupID", typeof(int),SqlDbType.Int,true,"Berechtigung",350,new DataGridViewComboBoxColumn(),false);
            Columns.Add("CSUsername", typeof(string),SqlDbType.NVarChar,true,"CS Benutzername",350);
            Columns.Add("Deleted", typeof(bool),SqlDbType.Bit,false);
        }

        public override void SetDefaultParameters()
        {
            base.SetDefaultParameters();
            AddParameter(Columns["Deleted"], false);
        }


    }

NOTICE: In all my SQL Tables I never realy delete Rows. In every one there is just a bool value that defines if the row is deleted or not. Because of that in every of my Tables I set the default value Deleted=False. And that is all. The table is created and ready to use J

For Adding a CheckBox to a Table we use instead of "new DataGridViewComboBoxClumn()" just "new DataGridViewCheckBoxColumn()".

Bind Table to DataGridView

Now komes the best part. Lets see how small the code is in the Form with the DataGridview we want to bind to a table.

 public partial class UsersForm : Form
    {
        private Users usersTable;
        private PermissionGroups permissionaGroupsTable;
        private ComboBox permissionGroup = new ComboBox();

        public UsersForm()
        {
            InitializeComponent();
            usersTable = new Users(this, dgvUsers);
            permissionaGroupsTable = new PermissionGroups(this, null);

            AddComboBox();
            usersTable.Columns["GroupID"].SetControl(permissionGroup, usersTable.DataGridView);
            usersTable.FirstRefresh();
        }

        private void AddComboBox()
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter = SQLAdapter.GetDataAdapter(permissionaGroupsTable);
            DataTable dataTableItem = new DataTable();
            adapter.Fill(dataTableItem);
            permissionGroup.DataSource = dataTableItem;
            permissionGroup.ValueMember = "ID";
            permissionGroup.DisplayMember = "Name";
            permissionGroup.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            permissionGroup.AutoCompleteSource = AutoCompleteSource.ListItems;
        }
    } 

In this example we also see how to add an ComboBox to the DataGridView and set the ComboBox Source. In this case we are using a combobox that is not shown in the Form but a column can also be bound to an combobox like this that is also shown in the win form.

NOTICE: If a Form Control is bound to a DataGridView column with this code all autocomplete sourcers and settings are automatically reused in the DataGridView Cells of that Column.

Points of Interest

The code can also be expandet to save DataGridView Column Settings like if the Column is visible or not, if it is readonly or not, its with, index etc. for each user of the application. But adding this also to this article would be to mutch. Maby in a new article or on request.

The code snippets abowe I just copied from my current project. Maby there are things that aren’t nesecery for the functionality that I didn´t saw by writing this article. Also because of that I have no DemoProject of this code but if someone realy can´t use it or rewrite it I will make one.

History