How to Configure Additional Drivers (.NET)
Overview
Data Abstract for .NET (DA) provides support for a wide range of drivers out of the box, but it also provides an easy way for users to add additional drivers where needed, allowing them to use virtually any database that has an ADO.NET provider.
Drivers for DA are configured in the .daConfig file. The default .daConfig is included inside of RemObjects.DataAbstract.Server.dll and will be used under normal circumstances, but developers (and end users) can override this by either including their own .daConfig file as resource in the server executable during compilation, or placing a DataAbstract.daConfig file in the .exe folder at runtime.
These custom .daConfig files may contain changes to the existing drivers to override the defaults, or entirely new driver configurations for other database types or ADO.NET providers. An Item Template is provided in Visual Studio to add a new .daConfig to the project easily. Depending on the user's needs, and as discussed above, this file can either be set to Embed as Resource or to Copy to Output to be deployed next to the .exe
Adding a new driver is straight-forward and can best be achieved by copying one of the existing configurations within the file and then modifying it to match the new driver.
Each driver relies on two XML tags within the .daConfig file. The first entry, under <KnownDrivers> defines the database driver and a new entry will be needed here in any case.
The second entry, under <DataBaseProfiles> defines the characteristics of the database type. In some cases (for example when targeting a completely different database type), a new section will be needed. In others (for example when adding a new provider for an already supported database type), an existing profile can be referenced.
Known Drivers
Let's start by looking at driver definitions in the <KnownDrivers> tag. The snippet below shows the definition of the MSSQL.NET driver for Microsoft SQL Server as an example:
<MSSQL.NET>
<ProviderName>System.Data.SqlClient</ProviderName>
<Description Value="Microsoft SQL Server 2000 Data Provider"/>
<Profile Value="MSSQL" DefaultAuxParams="Integrated Security=SSPI" />
<ParameterHandling Type="ByName" SQLSymbol="@" NameSymbol="@" />
<OptionalParameters>
<Parameter Name="Integrated Security" Value="SSPI" />
</OptionalParameters>
<NewConnectionHelpText>Help Text Here</NewConnectionHelpText>
</MSSQL.NET>
Referencing the ADO.NET Provider
The first item of relevance is the reference to the existing ADO.NET provider classes. There are three ways to reference a provider, the simplest being the <ProviderName> tag to point to an ADO.NET provider that is globally registered in the MACHINE.CONFIG file.
The second option is to reference the DbProviderFactory class implemented by the ADO.NET provider, by specifying assembly name and class name like this:
<MSSQLCE.NET>
<AssemblyName Value="System.Data.SqlServerCe, Version=9.0.242.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
<FactoryClassName Value="System.Data.SqlServerCe.SqlCeProviderFactory" />
...
</MSSQLCE.NET>
The advantage of this method is that it does not rely on global registration of the provider in MACHINE.CONFIG, as DA will be able to locate the provider itself. The disadvantage is that it relies on a fully versioned strong name for the provider's assembly.
A third option is available for providers that do not offer a DbProviderFactory. This is often the case for providers based on ADO.NET 1.1 ? which are fully supported by DA. In this case, the following syntax can be used to manually specify the relevant classes needed by DA to communicate with the provider:
<FB.NET>
<AssemblyName Value="FirebirdSql.Data.Firebird, Version=1.7.1.0,
Culture=neutral, PublicKeyToken=fa843d180294369d" />
<ConnectionClassName Value="FirebirdSql.Data.Firebird.FbConnection" />
<DataAdapterClassName Value="FirebirdSql.Data.Firebird.FbDataAdapter" />
<CommandBuilderClassName
Value="FirebirdSql.Data.Firebird.FbCommandBuilder" />
...
</FB.NET>
Once again the fully versioned string name of the assembly will be used to locate the provider's implementation.
Profile Mappings
As mentioned above, in addition to the driver registration, each driver must reference one or more database profiles defined in the <DatabaseProfiles> section of the file. If the driver supports a single database only (such as the MSSQL.NET driver), a single
<Profile Value="MSSQL" ? />
tag will suffice to establish the mapping. If the driver supports multiple back-end databases through the use of AuxDrivers (such as the OLEDB.NET driver), a <ProfileMapping> subsection is used to map each AuxDriver value to the appropriate profile:
<ProfileMappings Match="Provider">
<Mapping Name="sqloledb" Profile="MSSQL" />
<Mapping Name="MySQLProv" Profile="MySQL" />
<Mapping Name="DB2OLEDB" Profile="DB2" />
...
The Name of each mapping corresponds to the AuxDriver value that will be chosen as part of the DA connection string, while Profile references the matching profile. In the above case, setting AuxDriver=sqloledb will, for example, choose the MSSQL profile for Microsoft SQL Server.
The Match value of the ProfileMapping specifies the parameter name that will be used in the underlying connection string to choose the auxdriver. For example, for OLEDB this is Provider, but for ODBC it would be Driver.
An optional Value attribute in the mapping can be used to separate the AuxDriver value from the actual value passed to the underlying provider; the above could, for example, be rewritten as
<Mapping Name="SQL Server" Value="sqloledb" Profile="MSSQL" />
to allow a value of AuxDriver=SQL Server to be specified, but still pass sqloledb on to the back end OLEDB provider.
Connection String Parameter Mapping
The optional <ParamMappings> tag allows you to remap the standard Server and Database parameters of a DA connection string to different names in the underlying database connection string. For example, SQL Server Compact Edition expects the database name to be passed as Data Source, and expects no server name whatsoever. The following ParamMapping accomplishes this:
<ParamMappings Database="Data Source" Server="" />
Parameter mapping can happen at a driver level, or within a specific profile mapping; see, for example, the MSSQLCE.NET and OLEDB.NET/Oracle driver configurations.
SQL Parameter Handling
The <ParameterHandling> tag configures how the driver handles encoding of parameters within SQL queries. Parameters can either be handled sequentially (i.e. based on the order of their occurrence in the query text), or by name. Depending on the driver, different symbols might be used to denote the parameters within the rest of the query text.
The snippet below shows two examples, taken from the ODBC and OLEDB drivers, respectively:
<ParameterHandling Type="Sequential" SQLSymbol="?" />
<ParameterHandling Type="ByName" SQLSymbol="@" />
Descriptions and Design-time Info
Finally, the <Description> tag can specify a human-readable description for the driver, while the <NewConnectionHelpText> tag may contain a lengthy HTML based help text that explains the usage of the driver. These tags are used only for design-time purposes in Schema Modeler and are not mandatory.
An <OptionalParameters> tag can be added to provide default values for AuxParams at design time in schema Modeler. See, for example, the MSSQL.NET and FB.NET providers.
Database Profiles
As mentioned above, you might or might not need to create a new database profile when registering a new driver; if an existing profile matches your requirements, you can simply reference it directly from your driver. If you do need to create your own profile, the best option is, again, to copy and rename one of the existing profiles, and then adjust it to your needs.
The snippet below shows the profile for Microsoft SQL Server 2005 as an example:
<MSSQL2005 QuotingIdentifiers="[]">
<GetDatabaseNames SQL="SELECT name as Name FROM
master.sys.sysdatabases"/>
<GetTableNames SQL="SELECT TABLE_SCHEMA+'.'+TABLE_NAME as Name FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'"/>
<GetStoredProcedureNames SQL="SELECT SPECIFIC_SCHEMA+'.'+SPECIFIC_NAME
as Name FROM INFORMATION_SCHEMA.ROUTINES"/>
<GetViewNames SQL="SELECT TABLE_SCHEMA+'.'+TABLE_NAME as Name FROM
INFORMATION_SCHEMA.VIEWS"/>
<GetLastAutoInc SQL="SELECT IsNull(@@Identity, 0) as LastAutoInc"
SupportsAutoIncs="True"/>
<DatabaseLocation Type="Select" />
<SelectSPSyntax WithParams="EXEC {0} {1}" />
</MSSQL2005>
First, the QuotingIdentifiers attribute specifies the two characters that will be used to quote identifiers that need quoting. These can be two different characters for the start and end of quote (such as the square brackets used by SQL Server), or a single character can be repeated (such as the double-quotes used by most other databases).
Next, the <Get*Names> tags define the SQL code used to obtain a list of specific objects. These queries must all return data in a single column called Name, and are only used at design-time in Schema Modeler.
If a specific type of entity is not supported by the database, no query needs to be provided, the same applies if the database is not capable of providing a list of the said type. For example, SQL Server Compact Edition does not support views or stored procedures, nor does it allow returning a list of available databases.
<GetLastAutoInc> specifies the query to run to obtain the last AutoInc value assigned by the database within the same transaction/connection. Once again, if this feature is not supported, the tag does not have to be provided.
<ExecuteStoredProcedure> specifies the syntax to be used to execute stored procedures on the back end database. Two variations of the syntax can be provided, if needed, for calling a procedure with or without parameters:
<ExecuteStoredProcedure WithParams="CALL {0}({1})" WithoutParams="CALL {0}" />
Finally, <DatabaseLocation> specifies what sort of UI Schema Modeler will use at design-time to let the user select a database. Options include:
Select-<GetDatabaseNames>will be used to obtain a list of databases from the server, and a drop-down list will be presented.File- databases are file based, and a Browse dialog is provided. An optional FileMask attribute can specify the file extension filter to use.Folder- databases are folder-based, and a Browse for Folder dialog will be provided.