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.
Note
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.
Imports System.Data Imports System.Data.OleDbusing 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]
> <P>The below code uses the sample database location for Crystal Reports for Visual Studio 2005. To be sure that you have the correct file directory path to the xtreme.mdb database, see <A href="ms225530(v=vs.90).md">Location of Xtreme Sample Database</A>.</P>
``` vb
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"
```
``` csharp
private const string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Program Files\\Microsoft Visual Studio 8\\Crystal Reports\\Samples\\En\\Database\\xtreme.mdb";
```
- Beneath the first constant, create a second constant named QUERY_STRING to hold the database query string.
> [!NOTE]
> <P>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.</P>
``` vb
Private Const QUERY_STRING As String = "SELECT * FROM CUSTOMER"
```
``` csharp
private const string QUERY_STRING = "SELECT * FROM CUSTOMER";
```
- Beneath the second constant, create a third constant named DATATABLE_NAME for the name of the DataTable to be filled in the DataSet.
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.
``` vb
Private Const DATATABLE_NAME As String = "Customer"
```
``` csharp
private const string DATATABLE_NAME = "Customer";
```
- Beneath the third constant, create a fourth constant named DIRECTORY_FILE_PATH for referring to the directory path location of the xsd file.
> [!NOTE]
> <P>The code below demonstrates a path for a Web Site.</P>
``` vb
Private Const DIRECTORY_FILE_PATH As String = "C:\WebSites\VB_Web_Data_DataSets\"
```
``` csharp
private const string DIRECTORY_FILE_PATH = @"C:\WebSites\CS_Web_Data_DataSets\";
```
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.
``` vb
Public Shared ReadOnly Property CustomerDataSet() As DataSet
Get
End Get
End Property
```
``` csharp
public static DataSet CustomerDataSet
{
get
{
}
}
```
This step has two options: one for use with the strongly-typed DataSet class (that is available with Windows projects.) and one for use with the generic DataSet class (that is available with Web Sites).
For Windows 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).
Dim myDataSet As CustomerDataSetSchema = New CustomerDataSetSchema()CustomerDataSetSchema dataSet = new CustomerDataSetSchema();For Web Site projects that use the generic DataSet class, 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.
Dim myDataSet As DataSet = New DataSet() myDataSet.ReadXmlSchema(DIRECTORY_FILE_PATH & "XMLSchema.xsd")DataSet dataSet = new DataSet(); dataSet.ReadXmlSchema(DIRECTORY_FILE_PATH + "XMLSchema.xsd");
Declare and instantiate the OleDbConnection class and pass into it the CONNECTION_STRING constant as a method parameter.
Dim myOleDbConnection As OleDbConnection = New OleDbConnection(CONNECTION_STRING)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.
Dim myOleDbDataAdapter As OleDbDataAdapter = New OleDbDataAdapter(QUERY_STRING, myOleDbConnection)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.
> [!NOTE]
> <P>The Fill() method populates the specified DataTable, within the DataSet instance, with the data retrieved from the database.</P>
``` vb
myOleDbDataAdapter.Fill(myDataSet, DATATABLE_NAME)
```
``` csharp
oleDbDataAdapter.Fill(dataSet, DATATABLE_NAME);
```
- To complete the property, return the DataSet instance.
``` vb
Return myDataSet
```
``` csharp
return dataSet;
```
The CustomerDataSet property is created and can be called from anywhere in the project.