How to: Change the Backend

This article is relevant to entity models that utilize the deprecated Visual Studio integration of Telerik Data Access. The current documentation of the Data Access framework is available here.

By default, Telerik Data Access does not support multiple backend mappings with one model. The reason is that Telerik Data Access is keeping information about SQL type for each table (as well as stored procedure parameters). Keeping information about column types is problematic for cross-backend usage because the SQL types from one backend are not always supported in another backend. Additionally, the different backends have different definitions for the same SQL types. For example, Varchar(Max) is a SQL type defining a string with a maximum length supported by MS SQL Server. The same type is defined as CLOB under Oracle. Another example - NVarchar(n) represents a Unicode string with length 'n' under MS SQL Server, but the corresponding type in Oracle is NVarchar2(n).

In this topic:

Configuring Your Fluent Model to Support Multiple Backends

The first step is to modify your FluentMetadataSource class, so that it uses the Backend Independent Mapping API for all string, char, decimal and byte array properties. This will allow you to configure your properties in abstract (backend independent) manner. The code-snippet below shows you a sample FluentMetadataSource configuration for the Product class. The ProductName and Price properties are of type string and decimal, which may have different representations in the different databases. That's why these properties are configured by using the WithFixedLength and IsCurrency methods.

public class FluentModelMetadataSource : FluentMetadataSource
{
   protected override IList<MappingConfiguration> PrepareMapping()
   {
       List<MappingConfiguration> configurations = new List<MappingConfiguration>();
       MappingConfiguration<Product> productConfiguration = new MappingConfiguration<Product>();
       productConfiguration.MapType().ToTable( "Products" );
       productConfiguration.HasProperty( x => x.ID ).IsIdentity( KeyGenerator.Autoinc ).ToColumn( "ProductId" );
       productConfiguration.HasProperty( x => x.ProductName ).WithFixedLength( 25 ).ToColumn( "Name" );
       productConfiguration.HasProperty( x => x.Price ).IsCurrency().ToColumn( "Price" );
       configurations.Add( productConfiguration );
       return configurations;
   }
}
Public Class FluentModelMetadataSource
    Inherits FluentMetadataSource
    Protected Overrides Function PrepareMapping() As _
        System.Collections.Generic.IList(Of Telerik.OpenAccess.Metadata.Fluent.MappingConfiguration)
        Dim configurations As List(Of MappingConfiguration) = New List(Of MappingConfiguration)()
        Dim productConfiguration As New MappingConfiguration(Of Product)()
        productConfiguration.MapType().ToTable("Products")
        productConfiguration.HasProperty(Function(x) x.ID).IsIdentity(KeyGenerator.Autoinc).ToColumn("ProductId")
        productConfiguration.HasProperty(Function(x) x.ProductName).WithFixedLength(25).ToColumn("Name")
        productConfiguration.HasProperty(Function(x) x.Price).IsCurrency().ToColumn("Price")
        productConfiguration.FieldNamingRules.AddPrefix = "_"
        configurations.Add(productConfiguration)
        Return configurations
    End Function
End Class

Changing the Backend

Once you have configured your fluent model in backend independent manner, the next step is to change the backend. Two steps need to be performed:

  1. Modify the BackendConfiguration object.
  2. Modify the connection string.

Modifying the BackendConfiguration Object

You need to modify the Backend and ProviderName properties of the BackendConfiguration object. Usually, the BackendConfiguration object is created and initialized in the context class.
Suppose you are using MSSQL as a backend:

public partial class FluentModelContext : OpenAccessContext
{
   static MetadataContainer metadataContainer = new FluentModelMetadataSource().GetModel();
   static BackendConfiguration backendConfiguration = new BackendConfiguration()
   {
       Backend = "mssql"
   };
   private const string DbConnection = "connectionID";
   public FluentModelContext()
       : base( DbConnection, backendConfiguration, metadataContainer )
   {
   }
}
Partial Public Class FluentModelContext
    Inherits OpenAccessContext
    Shared metadataContainer As MetadataContainer = (New FluentModelMetadataSource()).GetModel()
    Shared backendConfiguration As BackendConfiguration = New BackendConfiguration() With
        {
            .Backend = "mssql"
        }
    Public Const DbConnection As String = "connectionId"
    Public Sub New()
        MyBase.New(DbConnection, backendConfiguration, metadataContainer)
    End Sub
End Class

And you want to use PostgreSQL instead of MSSQL. Here is how the BackendConfiguration object should look like - you need to adjust the Backend and ProviderName properties.

For a full list of backend strings and provider names please refer to the Backend Strings and Provider Names List article.

public partial class FluentModelContext : OpenAccessContext
{
   static MetadataContainer metadataContainer = new FluentModelMetadataSource().GetModel();
   static BackendConfiguration backendConfiguration = new BackendConfiguration()
   {
       Backend = "postgresql",
       ProviderName = "Npgsql"
   };
   private const string DbConnection = "connectionID";
   public FluentModelContext()
       : base( DbConnection, backendConfiguration, metadataContainer )
   {
   }
}
Partial Public Class FluentModelContext
    Inherits OpenAccessContext
    Shared metadataContainer As MetadataContainer = (New FluentModelMetadataSource()).GetModel()
    Shared backendConfiguration As BackendConfiguration = New BackendConfiguration() With
        {
            .Backend = "postgresql",
            .ProviderName = "Npgsql"
        }
    Public Const DbConnection As String = "connectionId"
    Public Sub New()
        MyBase.New(DbConnection, backendConfiguration, metadataContainer)
    End Sub
End Class

Modifying the Connection String

The final step is to modify the connection string in the configuration file. You need to adjust the connectionString and providerName attributes. For example, the old connection string connects to MsSQL server:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
   <add name="connectionId"
        connectionString="data source=.\sqlexpress;
                          initial catalog=FluentMappingDatabase;
                          integrated security=True"
        providerName="System.Data.SqlClient" >
 </connectionStrings>
</configuration>

The new connection string for PostgreSQL will look like:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
 <connectionStrings>
   <add name="connectionId" 
        connectionString="server=localhost;
                          port=5432;
                          database=MyDatabase;
                          userid=postgres;
                          password=milan_85"
        providerName="Npgsql" />
 </connectionStrings>
</configuration>