How to: Change the Database Schema

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.

When you want to change the database schema to a schema different than the schema for the currently connected user, you need to execute an alter session statement on the connection - ALTER SESSION SET CURRENT_SCHEMA="scott";. You can do this by setting the InitSQL property backend configuration object. The InitSQL property specifies an SQL statement to be executed on each newly created connection. This can be used for application role restrictions. This property can be used in a generic way to provide a connection with a customer specific setup. Currently, there are two ways to configure this:

• Using the Connection Pool Settings in the Model Settings Dialog.
• Directly configuring InitSQL in the code by using the ConnectionPool property that is exposed by the BackendConfiguration class.

Using the Model Settings Dialog

To configure the InitSQL property in the Model Settings Dialog:

1. In the Visual Designer, open the Model Settings Dialog.
2. Select the Backend Configuration tab and then go to the Connection Pool settings.

3. Specify the Init SQL setting.

   

Using the BackendConfiguration Class

The second way is to set the InitSQL property by using the BackendConfiguration class:

public static BackendConfiguration GetBackendConfiguration()
{
   BackendConfiguration backend = new BackendConfiguration();
   backend.ConnectionPool.InitSQL.Add( "ALTER SESSION SET CURRENT_SCHEMA=scott" );
   return backend;
}

Public Shared Function GetBackendConfiguration() As BackendConfiguration
 Dim backend As New BackendConfiguration()
 backend.ConnectionPool.InitSQL.Add("ALTER SESSION SET CURRENT_SCHEMA=scott")
 Return backend
End Function