Why use the provider approach, instead of normal object-oriented design?  The power in this design is the flexibility; for instance, a custom provider implementation works with databases, like SQL Server, Oracle, MySql, and other systems; however, it could also work with alternative file types like XML or Text Files, or anything else you can imagine.  And, because it is the framework pattern, the static class can automatically make use of the default provider without having to know anything of it.  This switch comes from the configuration file, or could be applied at runtime (more on this later).

With that said, we are going to look at the complete example of creating a derived newsletter provider using SQL Server that reads/writes this information to a database.  A custom table stores the information, separated by application name.  Below is the layout of the table; stored procedures have been intentionally left out for brevity:

[dbo].[mains_Newsletters]

·         [NewsletterGuid] [uniqueidentifier]

·         [ApplicationName] [nvarchar](256)

·         [NewsletterName] [nvarchar](256)

·         [Description] [nvarchar](max)

[dbo].[mains_NewsletterSubscribers]

·         [NewsletterGuid] [uniqueidentifier]

·         [SubscriberEmail] [nvarchar](256)

Because my code uses the Data Application Block, which is part of Microsoft Enterprise Library January 2006 edition, I can use the same code for multiple databases.  Because you can specify the provider name to work with at the connection string level, the Enterprise Library handles switching between several databases for you.

In this instance, the article uses SQL Server to store newsletter information, as shown below.  The first method to look at is for adding a new newsletter to the database:

Listing 2

public override void AddNewsletter(string newsletterName, 
 string description)
{
      this.ValidateNewsletterName(newsletterName);
      //If the newsletter already exists in the database, throw an error
      if (this.NewsletterExists(newsletterName))
            throw new ArgumentException(@"The newsletter already exists 
                  in the database", "newsletterName");
 
      Database database = 
            DatabaseFactory.CreateDatabase(this.ConnectionStringName);
      DbCommand command = database.GetStoredProcCommand("mains_AddNewsletter");
      database.AddInParameter(command, "ApplicationName", 
            DbType.String, this.ApplicationName);
      database.AddInParameter(command, "NewsletterName", 
            DbType.String, newsletterName);
      database.AddInParameter(command, "Description", DbType.String, description);
      database.AddParameter(command, "RETURN_VALUE", DbType.Int32, 
            ParameterDirection.ReturnValue, null, DataRowVersion.Default, null);
 
      //Get the value returned from the database
      if (database.ExecuteNonQuery(command) == 0)
            throw new DataException("The newsletter could not be added");
}

We won't look at every method, but evaluate the several general categories of methods that there are.  In this insertion example at the beginning, it checks that the newsletter name exists.  If it is null, empty, or already existing, an exception is thrown.  After validation is cleared, the connection is setup, and a command will be run to execute mains_AddNewsletter.  All of the required parameters are provided; the application name is stored at the provider level, and is retrieved through the public property.  At the time of executing the command, if no results have been returned to the caller, an exception is thrown, as at least one entry should have been inserted.  This method is a simple and straightforward approach, using the Data Application Block.

The second category of methods are retrieval methods.  Whereas inserting, updating, and deleting methods all have the same fundamentals, retrieval methods are different slightly.  Examine the GetNewslettersForSubscriber method below:

Listing 3

public override string[] GetNewslettersForSubscriber(string subscriberEmail)
{
      this.ValidateSubscriberEmail(subscriberEmail);
 
      Database database =
             DatabaseFactory.CreateDatabase(this.ConnectionStringName);
      DbCommand command = database.GetStoredProcCommand(
             "mains_GetNewslettersForSubscriber");
      database.AddInParameter(command, "ApplicationName", 
             DbType.String, this.ApplicationName);
      database.AddInParameter(command, "SubscriberEmail", 
             DbType.String, subscriberEmail);
 
      DataSet results = database.ExecuteDataSet(command);
      List<string> newsletters = new List<string>();
      
      if (results != null && results.Tables.Count > 0)
      {
            foreach (DataRow row in results.Tables[0].Rows)
                  newsletters.Add(row["NewsletterName"].ToString());
      }
      else
            throw new DataException("No data was returned from the provider");
 
      return newsletters.ToArray();
}

The differences are with the validation of the results returned, to ensure we have valid a DataTable to work with.  It also uses the ExecuteDataSet method to return a DataSet object with the stored procedure execution results.  If the method executes correctly, even when there are no results returned, a table should exist in that DataSet.  If no table exists or the results are null, then this is an error condition.  Otherwise, we need to get only the name of the newsletter by adding the value in the row to a list, and returning an array form of that list.  The generic List class works very nicely for this, as the ToArray() method handles the conversion to an array very easily.

The last category of methods is the verification methods, which return a boolean value stating whether a specific value exists in the database.  The validation and database connection works the same; however, the boolean result returned is based on whether results were returned from the database.

Listing 4

public override bool SubscriptionExists(string subscriberEmail, 
 string newsletterName)
{
      this.ValidateSubscriberEmail(subscriberEmail);
      this.ValidateNewsletterName(newsletterName);
 
      Database database = 
            DatabaseFactory.CreateDatabase(this.ConnectionStringName);
      DbCommand command = database.GetStoredProcCommand("mains_SubscriptionExists");
      database.AddInParameter(command, "ApplicationName", 
             DbType.String, this.ApplicationName);
      database.AddInParameter(command, "SubscriberEmail", 
            DbType.String, subscriberEmail);
      database.AddInParameter(command, "NewsletterName", 
            DbType.String, newsletterName);
 
      object value = database.ExecuteScalar(command);
      return (value != null && !DBNull.Value.Equals(value));
}

In the example above, a value other than null should have been returned if the value is true.  If not, a false is returned because the value is null or equals DbNull, the .NET representation of null values in the database.

The last method to discuss with this class is the initialization.  Each provider has their own initialization settings.  For instance, database providers need the name of a connection string key in the connectionStrings configuration section.  A text-based provider, such as an XmlProvider, needs the path to an XML file.  The initialization method retrieves the provider-specific configuration values that we need to setup the provider with.  Examine the implementation below:

Listing 5

public override void Initialize(string name, NameValueCollection config)
{
      if (config == null)
            throw new ArgumentNullException("config");
      if (string.IsNullOrEmpty(name))
            name = "AspNetDatabaseNewsletterProvider";
 
      if (string.IsNullOrEmpty(config["description"]))
      {
            config.Remove("description");
            config.Add("description", @"The default Newsletter Provider 
                  using the specified SQL database.");
      }
 
      base.Initialize(name, config);
 
      _connectionStringName = config["connectionStringName"];
      //If the value is null or empty, throw an error
      if (string.IsNullOrEmpty(_connectionStringName))
            throw new ArgumentNullException("config");
      config.Remove("connectionStringName");
      //If it can't be found in the connection strings section
      if (ConfigurationManager.ConnectionStrings[_connectionStringName] == null)
            throw new ArgumentNullException("connectionStringName",
 "The connection string doesn't exist in the configuration file");
 
      //Set the application name to the configuration entry
      base.ApplicationName = config["applicationName"];
      if (string.IsNullOrEmpty(base.ApplicationName)) base.ApplicationName = "/";
      config.Remove("applicationName");
 
      //If any configuration attributes are left, throw an exception
      if (config.Count > 0 && !string.IsNullOrEmpty(config.GetKey(0)))
       throw new ProviderException("There are too many configuration attributes specified");
}

The configuration must exist; however, certain parameters may be missing, and so the Initialize method supplies default values.  In addition, the derived class required additional parameters, such as connectionStringName and applicationName.  If the applicationName attribute is missing, it is defaulted to "/" (or root).  Upon retrieving these values, they are removed from the collection.  At the end, there should be zero configuration properties left.