C# DataGridView Filtering rows by cell value

Question

I want to filter my DataGridView rows by Actionuserid cell values. I only want to see 1 users rows.

My data source is actionsBLL.ActionsList();

DataGridView filling code:

        List<actionsVAL> ActionsList = actionsBLL.ActionsList();

        dataGridView_actions.DataSource = ActionsList;

        dataGridView_actions.RowHeadersVisible = false;
        dataGridView_actions.ReadOnly = true;

        dataGridView_actions.Columns[0].Width = 45;

        dataGridView_actions.Columns[1].Width = 110; 

        dataGridView_actions.Columns[2].Width = 70; 

        dataGridView_actions.Columns[3].Width = 90; 

        dataGridView_actions.Columns[4].Width = 114; 

ActionList:

    public static List<actionsVAL> ActionsList()
    {
        List<actionsVAL> sonuc = new List<actionsVAL>();
        OleDbCommand cmdactionlist = new OleDbCommand("SELECT  * from actions", dbConnection.conn); // tüm faaliyetleri gösteren sql sorgusu
        if (cmdactionlist.Connection.State != ConnectionState.Open) 
        {
            cmdactionlist.Connection.Open(); 
        }

       OleDbDataReader dr = cmdactionlist.ExecuteReader();
        while (dr.Read())
        {
            actionsVAL act = new actionsVAL();
            act.Actionid = int.Parse(dr["actionid"].ToString());
            act.Actionuserid = int.Parse(dr["userid"].ToString());
            act.Actionbookid = int.Parse(dr["bookid"].ToString());
            act.Actiondate = dr["actiondate"].ToString();
            act.Actiontype = dr["actiontype"].ToString();
            sonuc.Add(act);
        }
        dr.Close();
        return sonuc;
    }

ActionVAL:

namespace ValueObjectLayer
{
    public class actionsVAL
    {
        public int Actionid { get; set; }
        public int Actionbookid { get; set; }
        public int Actionuserid { get; set; }
        public string Actiontype { get; set; }
        public string Actiondate { get; set; }
    }
}

How Table Looks

Answer 1

"I only want to see 1 users rows." Do you mean: I only want to see rows from one specific user?

Fetch no more data than your caller wants

First of all, when fetching data from a database, don't fetch all items. This would be a waste of processing power if the caller only wants the FirstOrDefault, or only the first few items.

Furthermore: whenever an object implements IDisposable, use using, so you can be certain that the object is always Disposed

public static IEnumerable<actionsVAL> FetchActions()
{
    using (OleDbConnection dbConnection = new OleDbConnection(...))
    {
        const string sqlText = "SELECT  * from actions";
        using (OleDbCommand cmdactionlist = new OleDbCommand(sqlText, dbConnection.conn))
        {
            dbConnection.Open();
            using (OleDbDataReader dataReader = cmdactionlist.ExecuteReader())
            while (dataReader.Read())
            {
                actionsVal action = new actionsVAL()
                {
                   Actionid = int.Parse(dr["actionid"].ToString());
                   Actionuserid = int.Parse(dr["userid"].ToString());
                   Actionbookid = int.Parse(dr["bookid"].ToString());
                   ...
                };
                yield return action;
            }
        }
    }
}

I also added opening and closing the OleDbConnection. It is not wise to keep this connection open for a lengthy time. If you want, you can remove this here. I'm not sure if you want that the caller creates a dbConnection for you, that you decide to change it (= Open it). If you trust your caller to create a dbConnection, then trust him also that he opens it for you.

All the using statements, make sure that the created objects are properly Closed and Disposed, even after Exceptions.

The yield return, makes sure that the objects are not disposed until the caller has stopped enumerating. Also: if the caller only enumerates the first three objects, the rest is not enumerated (while dataReader.Read()).

Show only the data of user X

Ok, so now you have a method to fetch all actionsVal, one by one. You only want to show the actionsVal of the user with a value of property ActionUserId equal to the Id of user X

The best method would be to create a FetchActions(User X):

IEnumerable<ActionsVal> FetchActions(User X)
{
    const string sqlText = "Select * from actions where actionUserId = ..."

Well you do know SQL better that I.

If you don't want to create a special procedure, you can use FetchActions():

IEnumerable<ActionsVal> FetchActions(User x)
{
    int actionUserId = x.ActionUserId;
    return this.FetchActions()
        .Where(action => actionUserId == actionUserId);
}

The best way to display these values is to use the DataSource of the DataGridView.

In the constructor of your form you have defined which column will show which property of ActionVal:

InitializeComponents();

columnId.DataPropertyName = nameof(ActionsVal.Id);
columnName.DatePropertyName = nameof(ActionsVal.Name);
...

To show the fetched ActionVals:

public BindingList<ActionsVal> DisplayedActionVals
{
    get => (BindingList<ActionsVal>)this.dataGridView1.DataSource;
    set => this.dataGridView1.DataSource = value;
}

So to show only the ActionsVal of User X:

void ShowActionsVal(User x)
{
    IEnumerable<ActionsVal> actionValsToDisplay = this.FetchActions(x);
    this.DisplayedActionsVals = new BindingList<ActionsVal)(actionsValsToDisplay.ToList());
}

That's all, all ActionsVals of user X are displayed. If allowed, the operator can add / remove rows and edit cell. He can even rearrange columns and rows. After a while he indicates he has finished editing by clicking a button:

void OnButtonOk_Clicked(object sender, ...)
{
    this.ProcessEditedActions();
}

void ProcessEditedActions()
{
    ICollection<ActionsVal> editedActions = this.DisplayedActionsVals;

    // find out which ActionsVals are added / removed / changed
    this.ProcessEditedActions(editedActions);
}