Lesson 3: Retrieving a List of Fields for the Report Definition
Since every report definition should have a list of fields that represent the data in the report, you must generate a fields list from your query.
To generate a fields list
- Replace the code for the GenerateFieldsList() method in your project with the following code:
Public Sub GenerateFieldsList()
Dim command As SqlCommand
Dim reader As SqlDataReader
' Executing a query to retrieve a fields list for the report
command = m_connection.CreateCommand()
m_commandText = "SELECT Person.CountryRegion.Name AS CountryName, Person.StateProvince.Name AS StateProvince " + "FROM Person.StateProvince " + "INNER JOIN Person.CountryRegion ON Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode " + "ORDER BY Person.CountryRegion.Name"
command.CommandText = m_commandText
' Execute and create a reader for the current command
reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
' For each field in the resultset, add the name to an array list
m_fields = New ArrayList()
Dim i As Integer
For i = 0 To reader.FieldCount - 1
m_fields.Add(reader.GetName(i))
Next i
End Sub 'GenerateFieldsList
public void GenerateFieldsList()
{
SqlCommand command;
SqlDataReader reader;
// Executing a query to retrieve a fields list for the report
command = m_connection.CreateCommand();
m_commandText =
"SELECT Person.CountryRegion.Name AS CountryName, Person.StateProvince.Name AS StateProvince " +
"FROM Person.StateProvince " +
"INNER JOIN Person.CountryRegion ON Person.StateProvince.CountryRegionCode = Person.CountryRegion.CountryRegionCode " +
"ORDER BY Person.CountryRegion.Name";
command.CommandText = m_commandText;
// Execute and create a reader for the current command
reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
// For each field in the resultset, add the name to an array list
m_fields = new ArrayList();
for (int i = 0; i <= reader.FieldCount - 1; i++)
{
m_fields.Add(reader.GetName(i));
}
}