How to fill one column using list and other by datatable in datagridview?

debugcn Published at Dev

lss mesy

I want to display two columns 'Flowers_Date' and 'rate' in datagridview when date changes in datepicker. I am fetching data for Flowers_Date using List and wants to fetch data for 'Rate' from datatable. How can i do that? is it possible? thanks ! here is the snippet of my code.

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
    {

        String month = dateTimePicker1.Value.ToString("MM-yyyy");
        List<String> dates = new List<string>();

        String date1;
        cmd.Connection = conn;
        conn.Open();
        cmd.CommandText = ("SELECT Lilie_Date,Rate FROM lilie_master WHERE Lilie_Date LIKE '%" + month + "%'");
        OleDbDataReader rd = cmd.ExecuteReader();
        while (rd.Read())
        {

            date1 = rd["Lilie_Date"].ToString();
            if (dates.Contains(date1))
            {
                continue;
            }
            else
            {
                dates.Add(date1);

            }
        }

        conn.Close();

        DataTable dt = ListToDataTable(dates);
        dataGridView1.DataSource = dt;
        dataGridView1.Refresh();

    }

    private static DataTable ListToDataTable(List<String> list)
    {

        DataTable table = new DataTable();
        // DateTime dtime;

        table.Columns.Add("Lilie_Date");
        table.Columns.Add("Rate");

        table.Columns["Lilie_Date"].ReadOnly = true;

        int columns = 0;
        foreach (var array in list)
        {
            if (array.Length > columns)
            {
                columns = array.Length;
            }
        }
        foreach (var array in list)
        {
            table.Rows.Add(array);
        }
        return table;
    }

Hoa Nguyen

Firstly, you should create a strongly-type class to store Lilie_Date and Rate properties. It makes the code more readable and clean.

public class DateRatePair
{
     public String Date { get; set; }
     public String Rate { get; set; }
}

Then your code should be modified as below (able to fetch both of 2 properties):

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{

    String month = dateTimePicker1.Value.ToString("MM-yyyy");
    // List<String> dates = new List<string>();
    List<DateRatePair> lstPairs = new List<DateRatePair>();
    String date1;
    cmd.Connection = conn;
    conn.Open();
    cmd.CommandText = ("SELECT Lilie_Date,Rate FROM lilie_master WHERE Lilie_Date LIKE '%" + month + "%'");
    OleDbDataReader rd = cmd.ExecuteReader();
    while (rd.Read())
    {

        date1 = rd["Lilie_Date"].ToString();
        if (dates.Contains(date1))
        {
            continue;
        }
        else
        {
            // dates.Add(date1);
            DateRatePair aPair = new DateRatePair();
            aPair.Date = date1;
            aPair.Rate = rd["Rate"].ToString();
            lstPairs.Add(aPair);

        }
    }

    conn.Close();

    DataTable dt = ListToDataTable(lstPairs);
    dataGridView1.DataSource = dt;
    dataGridView1.Refresh();

}

private static DataTable ListToDataTable(List<DateRatePair> list)
{

    DataTable table = new DataTable();
    // DateTime dtime;

    table.Columns.Add("Lilie_Date");
    table.Columns.Add("Rate");

    table.Columns["Lilie_Date"].ReadOnly = true;

    int columns = 0;
    foreach (var array in list)
    {
        if (array.Length > columns)
        {
            columns = array.Length;
        }
    }
    foreach (var array in list)
    {
        DataRow dr = table.NewRow();
        dr["Lilie_Date"] = array.Date;
        dr["Rate"] = array.Rate;
        table.Rows.Add(dr);
    }
    return table;
}

Response if any problems arises.

Collected from the Internet

Please contact [email protected] to delete if infringement.