1
\$\begingroup\$

Goal:

My goal is to filter datagrid by selected month from dropdown options.

January, February, March etc...

I'd like to know if there are easier methods to my current method..

Code:

public Schedule()
{
    InitializeComponent();
    RefreshGrid();

    Dictionary<string, string> allOptions = new Dictionary<string, string>();
    allOptions.Add("1", "All");
    allOptions.Add("2", "January");
    allOptions.Add("3", "February");
    allOptions.Add("4", "March");
    allOptions.Add("5", "April");
    allOptions.Add("6", "May");
    allOptions.Add("7", "June");
    allOptions.Add("8", "July");
    allOptions.Add("9", "August");
    allOptions.Add("10", "September");
    allOptions.Add("11", "October");
    allOptions.Add("12", "November");
    allOptions.Add("13", "December");

    Months.DataSource = new BindingSource(allOptions, null);
    Months.DisplayMember = "Value";
    Months.ValueMember = "Key";

}

public void RefreshGrid()
{
    string value = "";

    // Select All
    if(Months.SelectedIndex == 0)
    {
        value = "";
    }
    // Select * from January
    if(Months.SelectedIndex == 1)
    {
        value = " where month(date_purchased) = 1;";
    }
    // Select * from February
    if (Months.SelectedIndex == 2)
    {
        value = " where month(date_purchased) = 2;";
    }
    //... and son on...

    // MySQL connection string
    using (var conn = new MySqlConnection(ConnectionString.ConnString))
    {
        //Select query
        using (var mySqlDataAdapter = new MySqlDataAdapter("select * from table "+ value +";", conn))
        {
            using (var dataSet = new DataSet())
            {
                DataSet DS = new DataSet();
                mySqlDataAdapter.Fill(DS);

                //Assign headers to Data Grid View
                scheduleData.DataSource = DS.Tables[0];
                scheduleData.Columns[0].HeaderText = "ID";
                scheduleData.Columns[1].HeaderText = "Equipment Name";
                scheduleData.Columns[2].HeaderText = "Date Purchased";


                //Set visibility to false for the columns to work in the background
                scheduleData.Columns[0].Visible = false;
            }
        }
    }
}

private void Months_SelectedIndexChanged(object sender, EventArgs e)
{
    RefreshGrid();
}

This idea does work - but Seems like there is too much unnecessarily code. But I cannot think of an alternative.

\$\endgroup\$
3
  • \$\begingroup\$ value = $" where month(date_purchased) = {Months.SelectedIndex};"; Of use string format but just use the SelectedIndex instead of making it a big if statement. You would still need the zero if as you don't want the filter \$\endgroup\$ Jul 30, 2020 at 16:55
  • \$\begingroup\$ @CharlesNRice Thanks for your comment - noted! :) \$\endgroup\$
    – EdGzi
    Jul 30, 2020 at 20:00
  • 1
    \$\begingroup\$ I recommend to use Dapper instead of ADO.NET. \$\endgroup\$
    – BCdotWEB
    Jul 31, 2020 at 6:59

1 Answer 1

3
\$\begingroup\$

Why not something simple like this:

var filter = Months.SelectedIndex == 0 ? null : 
    $" where month(date_purchased) = {Months.SelectedIndex};"

Also that dictionary initialization code is a lot of copy & paste, you could easily write it like this:

var allOptions = new Dictionary<string, string>()
{
    ["0"] = "All"
};

int idx = 1;
foreach(var month in DateTimeFormatInfo.CurrentInfo.MonthNames)
    allOptions.Add((idx++).ToString(), month);

Edit: also don't use data sets if you're only returning one data table, use the table versions of the database API instead.

\$\endgroup\$
1
  • \$\begingroup\$ I'd also question why the keys are strings. \$\endgroup\$
    – Blindy
    Jul 30, 2020 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.