WEBINAR:
On-Demand
Building the Right Environment to Support AI, Machine Learning and Deep Learning
Building a Data Provider
To illustrate the points outlined so far in this article, you'll see how to create a data provider that can use SQL Server, OLE DB, or the Oracle native providers based on settings in a configuration file. The advantage of this approach is your user interface layer will make calls only to the DataLayer class for all DataSets, DataReaders, commands, etc. The DataLayer class will ensure that the appropriate provider is used based on settings in the Configuration file (see
Figure 1).
 | |
Figure 1. Structure of the Sample Application: The DataLayer class handles loading and calls to the provider, insulating the application from direct interaction with the data store. |
|
 | |
Figure 2. Provider Sample: Here's a screenshot from the sample application showing retrieved data. |
|
|
To test out this model, create a sample Windows Form application with a GridView control on a form that will load the Customers table from the Northwind database (see
Figure 2).
Loading the Data
In the
Form_Load event procedure you will call a method named
GridLoad, which in turn will be responsible for calling the
GetDataSet method in the DataLayer class:
You can use the System.Activator Class to dynamically create an instance of a class at run time from a string variable.
|
|
// C#
private void frmMain_Load(object sender,
EventArgs e)
{
GridLoad();
}
private void GridLoad()
{
string SQL = "SELECT * FROM Customers";
grdCust.DataSource =
DataLayer.GetDataSet(SQL,
AppConfig.ConnectString).Tables[0];
}
' Visual Basic
Private Sub frmMain_Load( _
ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
GridLoad()
End Sub
Private Sub GridLoad()
Dim SQL As String = "SELECT * FROM Customers"
grdCust.DataSource = _
DataLayer.GetDataSet(SQL, _
AppConfig.ConnectString).Tables(0)
End Sub
The
GridLoad method must read the appropriate connection string for the application from the configuration file. You'll create an AppConfig class for that purpose that returns the appropriate connection string. The code in the UI layer is very generic and does not rely on any specific data provider to retrieve the data.
Configuration Settings
The next code snippet shows the configuration settings that provide not only the connection string, but also the provider class to use for retrieving data. In the
<appSettings> element you will need a key called
ProviderName. The value for the
ProviderName will correspond to another key in the
<appSettings> element that has the fully qualified namespace and class name for the data provider class. In addition, the
ProviderName value will also be the same as the
name key in the
<connectionStrings> element, which stores the appropriate connection string for the data provider:
<configuration>
<appSettings>
<add key="ProviderName"
value="OleDbDataProvider"/>
<add key="SqlDataProvider"
value="DataCommon.SqlDataProvider"/>
<add key="OleDbDataProvider"
value="DataCommon.OleDbDataProvider"/>
</appSettings>
<connectionStrings>
<add name="SqlDataProvider"
connectionString="Server=Localhost;
Database=Northwind;uid=sa;
pwd=sa;Persist Security Info=False"/>
<add name="OleDbDataProvider"
connectionString="Provider=SQLOLEDB.1;
Password=sa;
Persist Security Info=False;User ID=sa;
Initial Catalog=Northwind;
Data Source=(local)"/>
</connectionStrings>
</configuration>
AppConfig Class
To retrieve the appropriate connection string from the configuration file you need to create the following static/Shared property in the AppConfig class. Notice that you have to read from the configuration file twice: once to get the ProviderName
value, and again to retrieve the connection string from the
<connectionString> element:
// C#
public class AppConfig
{
public static string ConnectString
{
get
{
string ProviderName;
// Get Provider Name
ProviderName = ConfigurationManager.
AppSettings["ProviderName"];
// Get Connect String
return ConfigurationManager.ConnectionStrings[
ProviderName]. ConnectionString;
}
}
}
' Visual Basic
Public Class AppConfig
Public Shared ReadOnly Property _
ConnectString() As String
Get
Dim ProviderName As String
' Get Provider Name
ProviderName = ConfigurationManager. _
AppSettings("ProviderName")
' Get Connect String
Return ConfigurationManager.ConnectionStrings( _
ProviderName).ConnectionString
End Get
End Property
End Class
Author's Note: To keep the code simple, this example reads the ProviderName value each time. In a real application you would want to cache the connection string after reading it the first time. |