The sections below will help guide you through using and configuring various Schema Providers that ship with CodeSmith Generator.

Creating a new connection string

The Data Source window will check to see if a schema provider supports showing Connection String designer. If it doesn't, then you must manually enter a valid connection string into the Connection String text box. You must enter in a valid connection string for the Provider Type you are trying to use.

• If you're using the SqlSchemaProvider, connection strings follow the format used by the .NET SqlConnection.ConnectionString property.
• If you're using the ADOXSchemaProvider, connection strings follow the format used by the ADO ConnectionString property.
• If you're using the OracleSchemaProvider, please be sure to check out the link below for creating a connection string as well as the Connecting to an Oracle database section below.
• If you're using the PostgreSchemaProvider, please make sure the following statement is included in your ConnectionString: Preload Reader = true;

For all SchemaProviders, please be sure to use the proper ConnectionString for the Database Provider you are using. A great resource for building database ConnectionStrings can be found here.

Configuring a Schema Providers behavior

A schema provider may contain additional configurable logic that changes how the schema provider behaves. For example, you may only want to show the various schema objects for the current logged in user or allow CommandResultSchema information to be retrieved.

These settings can be configured in the Generator Options Schema Provider section as shown above. 

Oracle Schema Provider

Connecting to an Oracle database

The first step is to add a new Data Source. Once the Data Source window is open, you will want to select the OracleSchemaProvider from the Provider Type drop down menu.

Next, you will want to click on the ellipse button (...) to build the connection string. 

After you have filled out all of the required information, press the Test Connection Button or the OK button. If you could connect to your Oracle Server, then you are all done. If this doesn't work you may be scratching your head and thinking you are out of luck. The good news is your not. You will need to use a modified connection string that the Connection Properties UI doesn't account for.

"This is another type of Oracle connection string that doesn't rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have one of these files on the client pc" (http://www.connectionstrings.com/oracle).

 You will need to manually configure this connection string using the initial Data Source User Interface's Connection String text box (shown above).  Below is an example of using an TNS-Less (Transparent Network Substrate) connection string.

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

Please see the following website for more information on Oracle Connection Strings.

Installing Oracle Data Access Components (ODAC)

If you are trying to use the Oracle Schema Provider you may be presented with an error message that states a BadImageFormatException was thrown. This can be resolved by installing the ODAC libraries.

Please visit the Oracle .NET Developer Center and download and install the latest versions of ODAC (32-bit, 64-bit). It is recommended that you restart your computer after installing the ODAC libraries. 

MySQL Schema Provider

If you are trying to use the MySQL Schema Provider you may encounter the error message: 'The MySQL.Data.MySQLClient  is not installed on this computer'. This error can occur because the MySQL Connector/Net ( MySql.Data.MySqlClient DbProvider)  hasn't been installed on your machine.

Installing MySQL Connector/Net

 Please download and install the latest MySQL .NET connector which can be found here. It is recommended that you restart your computer after installing the MySQL .NET connector.

Configuring MySQL Connector/Net

The MySQL Schema Provider doesn't require a specific version of the MySQL managed libraries. This is because it uses DbProviderFactories when interacting with MySQL. One must update all .NET Framework 4.0 machine.config's and add the MySQL provider to the DbProviderFactories element:

<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.7.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

The .NET Framework 4.0 machine.config's can be found here:

• %windir%\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
• %windir%\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

SQLite Schema Provider

If you are trying to use the SQLite Schema Provider you may encounter the error message: 'The System.Data.SQLite library is not installed on this computer'. This error can occur for two reasons:

1. The System.Data.SQLite hasn't been installed on your machine.
2. The System.Data.SQLite hasn't been properly configured.

Installing System.Data.SQLite

 The first step is to ensure that you download and install the latest .NET Framework 4.0 version of  System.Data.SQLite.

Configuring System.Data.SQLite

The SQLite Schema Provider doesn't require a specific version of the SQLite managed libraries. This is because it uses DbProviderFactories when interacting with SQLite. One must update all .NET Framework 4.0 machine.config's and add the SQLite provider to the DbProviderFactories element:

<system.data>
  <DbProviderFactories>
    <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" />
  </DbProviderFactories>
</system.data>

The .NET Framework 4.0 machine.config's can be found here:

• %windir%\Microsoft.NET\Framework\v4.0.30319\Config\machine.config
• %windir%\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

System.IO.FileNotFoundException: Could not load file or assembly 'Npgsql, Version=2.0.11.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7' or one of its dependencies. The system cannot find the file specified.

gacutil -i Npgsql.dll
gacutil -i Mono.Security.dll