Chris Iverson Chris Iverson - 1 month ago 8
C# Question

How to populate a listview from a datatable that is using stored procedures

I took over a project from a former coworker and I am trying to populate a listview from a datatable that pulls from a mysql database.

Here is what I am using to pull from MySQL:

internal static DataTable GetDataSPT3(string StoredProcedure, Dictionary<string, string> Parameters)
{
DataTable _DT = new DataTable();
MySqlCommand cmd = new MySqlCommand(StoredProcedure, new MySqlConnection(Creds.GetConnectionStringT3()));
cmd.CommandType = CommandType.StoredProcedure;
foreach (KeyValuePair<string, string> Parameter in Parameters)
cmd.Parameters.Add(new MySqlParameter(Parameter.Key, Parameter.Value));
cmd.Connection.Open();
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
_DT.Load(dr);
dr.Close();
return _DT;
}


This is what I am using to populate the listview:

private void GetMetrics()
{
string begin = "2015-05-26 00:00:00";
string end = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
Dictionary<string, string> Parameters = GetParameters();
Parameters.Add("p_daily_metrics_date_Date1", begin);
Parameters.Add("p_daily_metrics_date_Date2", end);
DataTable dt = new DataTable();
foreach (DataRow row in RRCStoredProcedures.GetDataSPT3("spGetDailyMetrics", Parameters).Rows)
{
ListViewItem item = new ListViewItem(row[0].ToString());
for (int i = 0; i < row.Table.Rows.Count;i++)
{
item.SubItems.Add(row[i].ToString());
}
lvMetrics.Items.Add(item);
}
}


Watching the process work with breakpoints and the SQL is finding data and returning data, but not in any useful way.

What am I doing wrong?

Answer

This is what I came up with, I'm still learning, to solve my issue. A DatagridView did not work for me since it fires a selectionchanged event when it loads and due to the needs of the form I could not have that.

My solution:

 private void GetMetrics()
    {
        string[] array = new string[11];
        string begin = "2015-05-26 00:00:00";
        string end = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        Dictionary<string, string> Parameters = GetParameters();
        Parameters.Add("p_daily_metrics_date_Date1", begin);
        Parameters.Add("p_daily_metrics_date_Date2", end);
        foreach (DataRow row in (RRCStoredProcedures.GetDataSPT4("spGetDailyMetrics", Parameters)).Rows)
        {
            for (int item = 0; item < row.ItemArray.Count(); item++)
            {
                array[item] = row.ItemArray[item].ToString();
            }

            ListViewItem thing = new ListViewItem
                (new[] { array[0], array[1], array[2], array[3], array[4], array[5], array[6], array[7], array[8], array[9], array[10] });
            Metricslstvw.Items.Insert(0, thing);
            Array.Clear(array, 0, 10);
        }
        foreach(ColumnHeader ch in Metricslstvw.Columns) { ch.Width = -2; }


    }

Since the DataRow exposes ItemArray, I just rolled that into an array I know how to use.