ADO.NET Factory Objects in your appconfig.aspx

VistaDB supports the ADO.NET Factory classes to allow you the ability to dynamically load the provider at runtime based upon your connection string. This is a very nice concept, but is has some complexity over using normal methods, and a few quirks when you go to ship your application.

If you are looking for the way to avoid using SqlClient in your code directly (or VistaDBClient) and want to just load the correct DbFactory this is the correct way to do it. Here isanother blogtalking about it as well.

Example App.Config

This is an example app.config that is placed in the directory with your application. It must be named the same name as your exe with a .config appended on the end. So an application names ParentConsumerApp.exe must have a config file of ParentConsumerApp.exe.config or it will not load correctly. These entries below in that config file are easily loaded by the code below.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="VistaDB.NET20"  />
      <add name="VistaDB Data Provider" description=".Net Framework Data Provider for VistaDB"
              invariant="VistaDB.NET20" 
type="VistaDB.Provider.VistaDBProviderFactory,
    VistaDB.NET20, Version=3.4.2.77, Culture=neutral, PublicKeyToken=dfc935afe2125461"/>

    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="myVDBConnection" connectionString="Data Source='|DataDirectory|\database.vdb3'" providerName="VistaDB.NET20"/>
  </connectionStrings>
</configuration>

Note VistaDB.NET20 as the connectionString type. This is then resolved through the DbProviderFactories reference above.

This function loads the VistaDB Assembly only through provder interfaces. There is no direct calling of the VistaDBConnection object.

public static bool TestVistaDB(out string VersionInfo)
{
    for( int i=0; i< System.Configuration.ConfigurationManager.ConnectionStrings.Count; i++ )
    {
        System.Diagnostics.Debug.WriteLine(
                System.Configuration.ConfigurationManager.ConnectionStrings[i].ToString());
    }
        
    string connectionName = "myVDBConnection";
    // Find this connection string in the app.config
    System.Configuration.ConnectionStringSettings connectionString = 
            System.Configuration.ConfigurationManager.ConnectionStrings[connectionName];
    if( connectionString == null )
    {
        VersionInfo = "Failed to load connectionString from config file";
        return (false);
    }

    // Load the factory
    System.Data.Common.DbProviderFactory factory = 
         System.Data.Common.DbProviderFactories.GetFactory(connectionString.ProviderName);

    // After this it looks pretty normal
    using (DbConnection connection = factory.CreateConnection())
    {
        connection.ConnectionString = connectionString.ConnectionString;
        connection.Open();
        using (DbCommand command = connection.CreateCommand())
        {
            command.CommandText = "SELECT @@VERSION;";
            command.CommandType = CommandType.Text;
        
            using (DbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    string result = reader.GetString(0);
                    if (!reader.IsDBNull(0))
                    {
                        VersionInfo = result;
                        return (true);
                    }
                }
            }
        }
    }
    VersionInfo = string.Empty;

    return (false);
}

Sample App

Download this sample factory client using provider factories written in C# to see how it works and play with it yourself.

A couple cautions

Difficulties sometimes arise from subtle differences in how each engine's functions are called. VistaDB attempts to match SQL Server syntax; other engines may not support certain any concepts you need (stored procs, etc). And there is always logic to be moved as well.

Deployment

If you have an EXE named ParentConsumerApp.exe - no ref to the dll app is required. A dll named VistaDBConsumer has a reference to VistaDB namespaces.

The parent app consumes the child VistaDB without caring about VistaDB, except that if it fails to load the DLL load can cause problems.

Update your version numbers!

Don't forget that provider factories are strongly typed and have the complete version number in them. Don't forget to check the version you have installed and update them. You can see the version from the GAC, or from the Data Builder (Bottom left of main dialog at launch, and Help About anytime).