Telerik OpenAccess Classic

Telerik OpenAccess ORM Send comments on this topic.
How to: Change the schema and preserve the data on the server
Programmer's Guide > OpenAccess ORM Classic (Old API) > OpenAccess Tasks > How to: Change the schema and preserve the data on the server

Glossary Item Box

This documentation article is a legacy resource describing the functionality of the deprecated OpenAccess Classic only. The contemporary documentation of Telerik OpenAccess ORM is available here.

This topic describes how a user can change the database schema without losing the data that is stored in it.
For the following example we will work with a simple class named Person.

C# Copy Code
[Telerik.OpenAccess.Persistent(IdentityField = "id")]
public class Person
private int id;
public int Id
           get {
return id; }
           set { id = value; }        
private int age;
public int Age
           get {
return age; }
           set { age = value; }
VB.NET Copy Code
<Telerik.OpenAccess.Persistent(IdentityField := "id_Renamed")> _
Public Class Person
  Private id_Renamed As Integer
  Public Property Id() As Integer
    Return id_Renamed
   End Get
   Set(ByVal value As Integer)
    id_Renamed = value
   End Set
  End Property
  Private age_Renamed As Integer
  Public Property Age() As Integer
    Return age_Renamed
   End Get
   Set(ByVal value As Integer)
    age_Renamed = value
   End Set
  End Property
End Class

The sample class consists only of two fields: id and age.

The following will work for more complex models as well but we have chosen a simplier model for the purposes of our example.<_o3a_p>

If we change the age field to ages and rebuild in Visual Studio with schema update enabled the sql server will drop the age column and create a new column named ages that will be initialized with the default values. This happens because there is no way for the migration tool to know that a field was renamed instead of having a new one added. The sql script generated for the migration process drops the columns that are not existent in the model (in our case there is no field ‘age’ in the model but there is a new field called ages) and adds those that are in the model but are missing from the sql server (on the sql server there is column named age that is not existing in the model and that’s why the migration tool will drop this column).

A way to prevent the renamed column from being dropped is to change the sql script that is generetad for the migration process. This can be done by using the VSchema tool that is shipped together with the product.
The VSchema tool is used for creating or migrating a relational schema. During the schema creation phase, VSchema reads the assembly file and uses the information to determine which classes have related tables. The information how to build the schema is taken from the Telerik OpenAccess ORM attributes in the assembly (Persistent, ItemType, etc.) and from the application's config file (App.config), which contains the complete mapping definition.
Basically the VSchema tool can be executed for any compiled assembly. To generate the migration script for our new database we will need to rebuild the application after we have set the field name to age.

You need to set the update database property to false in order not to reflect the changes to the database before the migration script has been created.<_o3a_p>

Run the command prompt and navigate to the bin folder of Telerik OpenAccess ORM. The VSchema.exe need to be called with the proper attributes in order for the migration script to be created.

More information on the VSchema.exe usage and options could be found here.

In our case we would need to specify the absolute path to the compiled assembly in the –assembly attribute, the absolute path to the config file of this assembly in the –config attribute, the connection id that is in the configuration file in the –connectionID attribute, the output directory path in the –ouputDir attribute and the –migrate to specify that the VSchema will be creating a migration script.

The following example of executing VSchema tool:

VSchema Copy Code
VSchema.exe -assembly:[absolute path to assembly] -config:[absolute path to the app.config] -outputDir:[output directory for the generated migration file] -migrate -connectionID:[the connection id thats in the app.config]

will produce SQL script like:

SQL Copy Code
-- add column for field ages
ALTER TABLE [person] ADD [ages] INT NULL
[person] SET [ages] = 0
-- dropping unknown column [age]

However if you execute the above DDL script ‘as is’ you will have your data removed from the ‘age’ column. To avoid the data loss in the database you will need to change the script above like removing the dropping and adding of the columns with a simple SQL statement, that just renames them:


SQL Copy Code
EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN'


If you are using SQL server you will need to use the stored procedure for renaming columns(sp_rename).

After changing the script you only need to execute it against the database and your migration will be done.