Telerik blogs

Migrating data from one database server to another

October 16, 2009 Productivity 0 Comments


In today’s post we are going to discuss a topic which is known to be fairly complex in most cases but it is relevantly easy to be achieved with Telerik OpenAccess ORM.


Let us consider the following scenario: your company decides to move your data access from one database server to another.  So you have your database in a MSSQL Server instance  and want to migrate to MySql. The schema is the same on both servers but the data resides inside the MSSQL Server instance. What follows is the easiest way to move all the data from the MSSQL server database to the MySql one.


The class that is crucial to successfully execute such data transport with Telerik OpenAccess ORM is, not surprisingly, the Object Container. The biggest reason for this fact is that the Object Container is Serializable.
The object container is perfect for holding persistent data in a serialized format. This data can then easily be extracted into an object scope that belongs to a different database with the same schemata. When the object scope and object container exchange data, usually they know about the state of the objects they exchange. This is the reason for the existence of the MarkContentAsNew() method of the container. In order to transfer the objects contained in the ObjectContainer to the second database object scope, the persistent objects must be marked as NEW, which is exactly what this method does to the containers content.


Here is the sample code presenting this part of the operation. We are intending to migrate the content of Northwind database from MSSQL Server to MySql.

using (IObjectScope scope = Database.Get("MSSQLServerConnection").GetObjectScope())
            {
                FetchGroupCollector collector = new FetchGroupCollector(FetchGroupCollector.DeepFetchGroup);
                Type[] types = new Type[]{typeof(Category),typeof(Customer),typeof(Employee),
                    typeof(Order),typeof(OrderDetail),typeof(Product),typeof(Region),
                    typeof(Shipper),typeof(Supplier),typeof(Territory)};
                scope.Transaction.Begin();
                foreach (Type t in types)
                {
                    IQueryResult objects = scope.GetOqlQuery("SELECT x FROM " + t.Name + "Extent as x").Execute();

                    container.CopyFrom(scope, t.Name, objects, collector);
                }
                scope.Transaction.Commit();

            }

            container.MarkContentAsNew();
            Telerik.OpenAccess.ObjectContainer.ChangeSet set = container.GetChanges(ObjectContainer.Verify.All);
            Stream str = new FileStream("C:\\container.bin", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            BinaryFormatter formatter = new BinaryFormatter();
            formatter.Serialize(str, set);

 

 

If this content is then applied to an object scope that represents the empty database on the new server, the object scope is just filling the database with that content as if you were adding it via the normal procedure.

Below is the code performing this:

            using (IObjectScope scope = Database.Get("MySqlConnection").GetObjectScope())
            {
                BinaryFormatter formatter = new BinaryFormatter();
                Stream str = new FileStream("C:\\container.bin", FileMode.OpenOrCreate, FileAccess.ReadWrite);

                Telerik.OpenAccess.ObjectContainer.ChangeSet cs = 
                    (Telerik.OpenAccess.ObjectContainer.ChangeSet)formatter.Deserialize(str);

                ObjectContainer.CommitChanges(cs, ObjectContainer.Verify.All, scope, true, false);

            }

 

So, that is all about it. It has definitely provided useful on our side, I hope some of you will find it beneficial as well.

Telerik Data Access

Comments

Comments are disabled in preview mode.