I'm learning to create a C# WPF Desktop Application. I wanted to retrieve the data from database and paste into the datagrid. I managed to retrieve some of the data and the error as title said appeared.

This is the XAML code for the datagrid.

<DataGrid Grid.Row="1" x:Name="scenarioDataGrid" CanUserSortColumns="True" IsReadOnly="True" AutoGenerateColumns="True" Grid.ColumnSpan="3" Margin="53,40,53,10" Grid.RowSpan="3">
                <DataGrid.Columns>
                    <DataGridTextColumn x:Name="scenarioNumberColumn" Binding="{Binding scenarioNum}" Header="No." Width="50" IsReadOnly="True"/>
                    <DataGridTextColumn x:Name="scenarioNameColumn" Binding="{Binding scenarioName}" Header="Scenario Name" Width="*" IsReadOnly="True"/>
                    <DataGridTextColumn x:Name="scenarioDateColumn" Binding="{Binding scenarioDate}" Header="Date" Width="*" IsReadOnly="True"/>
                </DataGrid.Columns>
            </DataGrid>

This is the scenario class I created

public class ScenarioData
        {
            public int scenarioNum { get; set; }
            public string? scenarioName { get; set; }
            public string? scenarioDate { get; set; }
        }

Then this is the code that I used to retrieve the data from database and paste it in the datagrid

                conn.Open();
                string sql = "SELECT COUNT(scenarioID) OVER () AS 'Num', scenarioName AS 'Scenario Name', scenarioDate AS 'Scenario Date' FROM tbl_scenario";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlDataReader dr = cmd.ExecuteReader(); 

                if(dr.Read())
                {
                    int dataCount = Convert.ToInt32(dr["Num"].ToString());
                    List<ScenarioData> dataForDatagrid = new List<ScenarioData>();
                    
                    while (dr.Read())
                    {
                        dataForDatagrid.Add(new ScenarioData()
                        {
                            scenarioName = dr["Scenario Name"].ToString(),
                            scenarioDate = dr["Scenario Date"].ToString(),
                        });
                    }

                    for (int j = 0; j < dataCount;   j)
                    {
                        scenarioDataGrid.Items.Add(new ScenarioData()
                        {
                            scenarioNum = j 1,
                            scenarioName = dataForDatagrid[j].scenarioName,
                            scenarioDate = dataForDatagrid[j].scenarioDate,
                        });
                    }

This is the database in SSMS Database View

This is what I got when running the code Executed code result

Much appreciate if someone can point out which part I'm doing it wrongly. Thank you in advance.

CodePudding user response：

This is because

1. the first dr.Read() is consuming the first record from the reader, so you are populating just the second and the third records to dataForDatagrid

2. dataCount value is 3, so inside the for loop you will be asking for dataForDatagrid[2], which is not exist!

Moreover, you can get rid off the dataCount at all..

List<ScenarioData> dataForDatagrid = new List<ScenarioData>();

while (dr.Read())
{
    dataForDatagrid.Add(new ScenarioData()
    {
        scenarioName = dr["Scenario Name"].ToString(),
        scenarioDate = dr["Scenario Date"].ToString(),
    });
}

for (int j = 0; j < dataForDatagrid.Count ;   j)
{
    scenarioDataGrid.Items.Add(new ScenarioData()
    {
        scenarioNum = j 1,
        scenarioName = dataForDatagrid[j].scenarioName,
        scenarioDate = dataForDatagrid[j].scenarioDate,
    });
}

Or you just get rid of the intermediate dataForDatagrid collection:

int j = 0;

while (dr.Read())
{
    scenarioDataGrid.Items.Add(new ScenarioData()
    {
        scenarioNum =   j,
        scenarioName = dr["Scenario Name"].ToString(),
        scenarioDate = dr["Scenario Date"].ToString(),
    });
}

If for any reason you need the dataForDatagrid collection, do this:

List<ScenarioData> dataForDatagrid = new List<ScenarioData>();
int j = 0;

while (dr.Read())
{
    dataForDatagrid.Add(new ScenarioData()
    {
        scenarioNum =   j,
        scenarioName = dr["Scenario Name"].ToString(),
        scenarioDate = dr["Scenario Date"].ToString(),
    });
}

scenarioDataGrid.ItemsSource = dataForDatagrid;