Writing a Helper Class to Populate the DataSet

The DataSet schema that you have just created for the Customer table is a data structure. At runtime, code is required to populate the DataSet structure with data from the database. In this section, you create a helper class that populates the DataSet with data.

To create a helper class to populate the DataSet with data

In the Solution Explorer, right-click the project name that is in bold type, point to Add, and then click Add New Item.
In the Add New Item dialog box, in the Visual Studio installed templates list, select Class.
In the Name field, enter "DataSetConfiguration," and then click Add.
If a dialog box comes up and asks you whether to place your class in a directory named "Code," click Yes.
Above the class signature, add an "Imports"[Visual Basic] or "using"[C#] declaration to the top of the class for the System.Data and System.Data.OleDb namespaces.
[Visual Basic]
```
Imports System.Data
Imports System.Data.OleDb
```
[C#]
```
using System.Data;
using System.Data.OleDb;
```
At the top of the class, create a constant named CONNECTION_STRING to hold the connection string to the Xtreme sample database.
Note If you intend to copy and paste the code, note that the Data Source string is for Visual Studio 2005. To be sure that you have the correct file directory path to the xtreme.mdb database, see Location of Xtreme Sample Database.
[Visual Basic]
```
Private Const CONNECTION_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio 8\Crystal Reports\Samples\En\Database\xtreme.mdb"
```
[C#]
```
private const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Program Files\\Microsoft Visual Studio 8\\Crystal Reports\\Samples\\Database\\xtreme.mdb";
```
Beneath the first constant, create a second constant named QUERY_STRING to hold the database query string.
[Visual Basic]
```
Private Const QUERY_STRING As String = "SELECT * FROM CUSTOMER"
```
[C#]
```
private const string QUERY_STRING = "SELECT * FROM CUSTOMER";
```
Note This simple query string selects all columns, and no criteria. For the Xtreme sample database, this will return only a small amount of data. However, in most cases it is recommended to limit your query by including a WHERE clause and selecting a limited number of columns.
Beneath the second constant, create a third constant named DATATABLE_NAME for the name of the DataTable to be filled in the DataSet.
[Visual Basic]
```
Private Const DATATABLE_NAME As String = "Customer"
```
[C#]
```
private const string DATATABLE_NAME = "Customer";
```
The following step instructs you to create a DIRECTORY_FILE_PATH constant. This constant is only required if you are creating a Web Site in Visual Studio 2005. Otherwise, skip this step.
Beneath the third constant, create a fourth constant named DIRECTORY_FILE_PATH for referring to the directory path location of the xsd file.
Note The code below demonstrates a path for a Web Site.
[Visual Basic]
```
Private Const DIRECTORY_FILE_PATH As String = "C:\WebSites\VB_Web_Data_DataSets\"
```
[C#]
```
private const string DIRECTORY_FILE_PATH =  @"C:\WebSites\CS_Web_Data_DataSets\";
```
The next step procedure explains how to return a populated DataSet from the CustomerDataSet read-only property.

To create a property that populates the DataSet

Create a read-only property named CustomerDataSet that returns an instance of DataSet. Give the method a "Shared"[Visual Basic] or "static"[C#] modifier so that the class and property can be called directly without needing to be instantiated.
[Visual Basic]
```
Public Shared ReadOnly Property CustomerDataSet() As DataSet
    Get

    End Get
End Property
```
[C#]
```
public static DataSet CustomerDataSet
{
    get
    {

    }
}
```
This step has two options: one for use with the strongly-typed DataSet class (that is available with Visual Studio .NET 2002 or 2003 projects and with Visual Studio 2005 Windows projects.) and one for use with the generic DataSet class (that is available with Visual Studio 2005 Web Sites).
- For projects that use the strongly-typed DataSet class, within the get clause of the CustomerDataSet property, declare and instantiate the CustomerDataSetSchema class (the strongly-typed DataSet class that was generated from the DataSet schema in the previous section).
  [Visual Basic]
```
Dim myDataSet As CustomerDataSetSchema = New CustomerDataSetSchema()
```
  [C#]
```
CustomerDataSetSchema dataSet = new CustomerDataSetSchema();
```
- For projects that use the generic DataSet class in Visual Studio 2005 Web Sites, within the get clause of the CustomerDataSet property, declare and instantiate a DataSet class, and then apply the XML Schema to the DataSet instance. That is, pass the directory file path to the CustomerDataSetSchema.xsd as a string parameter to the ReadXmlSchema() method of the DataSet instance.
  [Visual Basic]
```
Dim myDataSet As DataSet = New DataSet()
myDataSet.ReadXmlSchema(DIRECTORY_FILE_PATH & "CustomerDataSetSchema.xsd")
```
  [C#]
```
DataSet dataSet = new DataSet();
dataSet.ReadXmlSchema(DIRECTORY_FILE_PATH + "CustomerDataSetSchema.xsd");
```

Declare and instantiate the OleDbConnection class and pass into it the CONNECTION_STRING constant as a method parameter.

[Visual Basic]

Dim myOleDbConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)

[C#]

OleDbConnection oleDbConnection = new OleDbConnection(CONNECTION_STRING);

Declare and instantiate the OleDbDataAdapter class and pass into it the QUERY_STRING constant and the OleDbConnection instance as method parameters.

[Visual Basic]

Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(QUERY_STRING, myOleDbConnection)

[C#]

OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(QUERY_STRING, oleDbConnection);

Call the Fill() method of the OleDbDataAdapter instance and pass to it the CustomerDataSetSchema instance and the DATATABLE_NAME constant.
[Visual Basic]
```
myOleDbDataAdapter.Fill(myDataSet, DATATABLE_NAME)
```
[C#]
```
oleDbDataAdapter.Fill(dataSet, DATATABLE_NAME);
```
Note The Fill() method populates the specified DataTable, within the DataSet instance, with the data retrieved from the database.
To complete the property, return the DataSet instance.
[Visual Basic]
```
Return myDataSet
```
[C#]
```
return dataSet;
```
The CustomerDataSet property is created and can be called from anywhere in the project.

Continue to Creating a Report that Connects to the DataSet Schema.

Writing a Helper Class to Populate the DataSet

See Also