← Back to overview
February 4, 2011 · Entity Framework SQL Compact

DbProviderFactoryRepository: Managing DbProviderFactories in code

Today I started playing with SQL CE (Compact Edition) to see how it could be used in a Client Profile environment. These were my findings:

After creating a small console application with LINQ to SQL and SQL CE 3.5 all worked fine. Based on the How to: Deploy a SQL Server Compact Edition Database with an Application article on MSDN I deployed this small application to Windows XP running on a Virtual Machine with only a few Service Packs installed. Running the application raised an InvalidOperationException: Cannot open 'TestDatabase.sdf'. Provider ‘System.Data.SqlServerCe.3.5′ not installed.

When working with LINQ to SQL it wasn’t sufficient to deploy the assemblies to the applciation folder, the application also required a provider for SQL Server CE. After a quick search I found the following topic on the MSDN forum: Can I XCopy SQL CE 3.5? yes and no, see message. In this topic someone described how you can create the provider using the App.config:

<system.data>  
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.3.5" />
      <add name="Microsoft SQL Server Compact Data Provider"
         invariant="System.Data.SqlServerCe.3.5"
         description=".NET Framework Data Provider for Microsoft SQL Server Compact"
         type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>  

Good solution, but I would have preferred to set this in code without having to depend on the App.config. Since there is no other way to manipulate the DbProviderFactories I decided to create a wrapper around the configuration section that allows you to take the required actions in code.

Description classes

Before jumping right into the wrapper (a repository actually) take a look at the following code:

public class DbProviderFactoryDescription  
{
    /// <summary>
    /// Gets or sets the name.
    /// </summary>
    /// <value>The name.</value>
    public string Name { get; set; }

    /// <summary>
    /// Gets or sets the invariant.
    /// </summary>
    /// <value>The invariant.</value>
    public string Invariant { get; set; }

    /// <summary>
    /// Gets or sets the description.
    /// </summary>
    /// <value>The description.</value>
    public string Description { get; set; }

    /// <summary>
    /// Gets or sets the type.
    /// </summary>
    /// <value>The type.</value>
    public string Type { get; set; }

    /// <summary>
    /// Initialize the description.
    /// </summary>
    public DbProviderFactoryDescription()
    {

    }

    /// <summary>
    /// Initialize the description.
    /// </summary>
    /// <param name="name"></param>
    /// <param name="description"></param>
    /// <param name="invariant"></param>
    /// <param name="type"></param>
    public DbProviderFactoryDescription(string name, string description, string invariant, string type)
    {
        this.Name = name;
        this.Description = description;
        this.Invariant = invariant;
        this.Type = type;
    }

    /// <summary>
    /// Initialize the description based on a row.
    /// </summary>
    /// <param name="row">The row.</param>
    internal DbProviderFactoryDescription(DataRow row)
    {
        this.Name = row[0] != null ? row[0].ToString() : null;
        this.Description = row[1] != null ? row[1].ToString() : null;
        this.Invariant = row[2] != null ? row[2].ToString() : null;
        this.Type = row[3] != null ? row[3].ToString() : null;
    }
}

/// <summary>
/// Db Provider Description for Sql CE 3.5
/// </summary>
public class SqlCe35ProviderFactoryDescription : DbProviderFactoryDescription  
{
    public const string ProviderName = "Microsoft SQL Server Compact Data Provider";
    public const string ProviderInvariant = "System.Data.SqlServerCe.3.5";
    public const string ProviderDescription = ".NET Framework Data Provider for Microsoft SQL Server Compact";
    public const string ProviderType = "System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe";

    /// <summary>
    /// Initialize the description.
    /// </summary>
    public SqlCe35ProviderFactoryDescription()
        : base(ProviderName, ProviderDescription, ProviderInvariant, ProviderType)
    {

    }
}

The DbProviderFactoryDescription class is a POCO representation of the DbProviderFactory you can find in the App.config. And the SqlCe35ProviderFactoryDescription is a specific implementation for SQL CE 3.5. The invariant and type properties are the ones that actually define your provider. The invariant property is the real name or code of your provider, and the type property is the fully qualified type name of the factory that can create the provider.

In the DbProviderFactoryDescription you can also find an internal constructor that accepts a DataRow. This constructor will be used in the repository to convert the DataRow retrieved from the App.config to a DbProviderFactoryDescription object.

The repository

To manage these DbProviderFactories (or actually the DbProviderFactoryDescriptions) I created a repository that allows you to create new providers, delete existing providers, get a list of all providers and find a provider based on the invariant (the actual name of the provider). Note that creating a provider will delete any existing provider with the same invariant.

public class DbProviderFactoryRepository  
{
    /// <summary>
    /// The table containing all the data.
    /// </summary>
    private DataTable dbProviderFactoryTable;

    /// <summary>
    /// Name of the configuration element.
    /// </summary>
    private const string DbProviderFactoriesElement = "DbProviderFactories";

    /// <summary>
    /// Initialize the repository.
    /// </summary>
    public DbProviderFactoryRepository()
    {
        OpenTable();
    }

    /// <summary>
    /// Opens the table.
    /// </summary>
    private void OpenTable()
    {
        // Open the configuration.
        var dataConfiguration = ConfigurationManager.GetSection("system.data") as System.Data.DataSet;
        if (dataConfiguration == null)
            throw new InvalidOperationException("Unable to open 'System.Data' from the configuration");

        // Open the provider table.
        if (dataConfiguration.Tables.Contains(DbProviderFactoriesElement))
            dbProviderFactoryTable = dataConfiguration.Tables[DbProviderFactoriesElement];
        else
            throw new InvalidOperationException("Unable to open the '" + DbProviderFactoriesElement + "' table");
    }

    /// <summary>
    /// Adds the specified provider.
    /// </summary>
    /// <param name="provider">The provider.</param>
    public void Add(DbProviderFactoryDescription provider)
    {
        Delete(provider);
        dbProviderFactoryTable.Rows.Add(provider.Name, provider.Description, provider.Invariant, provider.Type);
    }

    /// <summary>
    /// Deletes the specified provider if present.
    /// </summary>
    /// <param name="provider">The provider.</param>
    public void Delete(DbProviderFactoryDescription provider)
    {
        var row = dbProviderFactoryTable.Rows.Cast<DataRow>()
            .FirstOrDefault(o => o[2] != null && o[2].ToString() == provider.Invariant);
        if (row != null)
        {
            dbProviderFactoryTable.Rows.Remove(row);
        }
    }

    /// <summary>
    /// Gets all providers.
    /// </summary>
    /// <returns></returns>
    public IEnumerable<DbProviderFactoryDescription> GetAll()
    {
        return dbProviderFactoryTable.Rows.Cast<DataRow>()
            .Select(o => new DbProviderFactoryDescription(o));
    }

    /// <summary>
    /// Get provider by invariant.
    /// </summary>
    /// <param name="invariant"></param>
    /// <returns></returns>
    public DbProviderFactoryDescription GetByInvariant(string invariant)
    {
        var row = dbProviderFactoryTable.Rows.Cast<DataRow>()
            .FirstOrDefault(o => o[0] != null && o[0].ToString() == invariant);
        if (row != null)
        {
            return new DbProviderFactoryDescription(row);
        }
        else
        {
            return null;
        }
    }
}

The only important action in this class takes place in the OpenTable method, where the system.data section of the App/Web.config is opened as a DataSet. And from this DataSet we access the DbProviderFactories DataTable which in turn allows us to interact with the factories. Although this code uses a ConfigurationSection from the ConfigurationManager you do not need to add anything to the App.config yourself. We are using this ConfigurationSection because methods like DbProviderFactories.GetFactory() use this ConfigurationSection to find all factories.

Putting the repository to good use

This is an example of how you can create a DbProviderFactory for SQL Server Compact Edition 3.5:

// Initialize the repository.
var repository = new DbProviderFactoryRepository();

// Create a description manually and add it to the repository.
var manualDescription = new DbProviderFactoryDescription();  
manualDescription.Description = ".NET Framework Data Provider for Microsoft SQL Server Compact";  
manualDescription.Invariant = "System.Data.SqlServerCe.3.5";  
manualDescription.Name = "Microsoft SQL Server Compact Data Provider";  
manualDescription.Type = "System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe";

repository.Add(manualDescription);

// Use the default SqlCe35 provider.
repository.Add(new SqlCe35ProviderFactoryDescription());  

Enjoy…

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket
Comments powered by Disqus