NB: For this project, I worked with ASP.NET/C#, NHibernate as ORM and SQL Server, the database engine. Consequently, I assume you are already familiar with those tools.
- Create a Console Application project.
- Add two configuration files to the project. I call them DataFrom.config and DataTo.config. These config files will only contain the usual NHibernate xml configuration settings, the difference in the two files being the value connection string.node. DataFrom.config should point to the database on the shared hosting server, and DataTo.config to the database on your system you wish to store your backup. (You will create it later... or now, if you like)
- Create a new Class Library project. I'll call it DataMigration. The reason is you'll typically need to use this solution in other settings, so it's best to build it independent (to a great extent) of any specific app.
- Create an Interface, IMigrator and add the following code to it.
using NHibernate; namespace DataMigration { public interface IMigrator { ////// Move data from one DB to another /// /// Session for the DB on the shared server /// Session for the DB on your system void Migrate(ISession fromSession, ISession toSession); } } - Create a class called Initialiser. We'll write the code to initialize session factories for the two databases and return the two sessions we'll use for the job. The code will look like this:
using System; namespace DataMigration { public class Initialiser { ////// I have assumed you have a way to create your session factory as the details of that is /// beyond the scope of this post. The important thing is to create them and return their /// corresponding sessions. My own way is represented by the class NHibernateSessionManager /// /// /// public static void Init(out ISession fromSession, out ISession toSession) { var baseDir = AppDomain.CurrentDomain.BaseDirectory; var fromConfigFile = baseDir + "DataFrom.config"; var toConfigFile = baseDir + "DataTo.config"; var fromDatasourceKey = "fromdatasource"; var toDatasourceKey = "todatasource"; //Sesson Factory - To Console.WriteLine("Initializing 'To' session factory..."); NHibernateSessionManager.Init(toConfigFile, toDatasourceKey); //Sesson Factory - From Console.WriteLine("Initializing 'From' session factory..."); NHibernateSessionManager.Init(fromConfigFile, fromDatasourceKey); Console.WriteLine("Initializing 'From' session..."); fromSession = NHibernateSessionManager.GetSession(fromDatasourceKey); Console.WriteLine("Initializing 'To' session..."); toSession = NHibernateSessionManager.GetSession(toDatasourceKey); Console.WriteLine("Done Initializing!"); } } } - Create a class called Runner. This will host two important methods Run and MoveData. Run initializes our session factories, gets our two sessions for us and then call IMigrator's Migrate method. MoveData is a generic method that does the actual migration. It basically uses fromSession to retrieve all records from a table (represented by the type T) and uses toSession to flush it into the corresponding table in our local database. Here's the code:
using NHibernate; using System; using System.Collections; using System.Collections.Generic; namespace DataMigration { ///Two things to note: (1) As a convention, my table name is the pluralised form of my class name; hence I've "written" an extension method, ToPlural, to convert strings to plural. However, MoveData still allows you to specify custom table names. (2) The implementation of the generic method SqlManipulations.SqlBulkInsert is not shown as it's outside the scope of this post. I may write another post to describe that in detail./// We use this to move data from one DB to another; PARTICULARLY from public shared server where we have the public connectionstring /// to our own system so we can hold a backup of the live data /// public class Runner { ////// This is the method to be called by our Console Application to start the migration job /// public static void Run(IMigrator migrator) { Console.WriteLine("Commencing Migration!"); ISession fromSession, toSession; try { Initialiser.Init(customMappingAssemblies, out fromSession, out toSession, autoPersistenceModel); migrator.Migrate(fromSession, toSession); fromSession.Close(); fromSession.Dispose(); toSession.Close(); toSession.Dispose(); } catch (Exception ex) { Console.WriteLine(ex); Console.WriteLine(); } Console.WriteLine("Done Migration!"); } ////// This method does the actual migration job /// public static void MoveData<T>(ISession fromSession, ISession toSession, string tableName = null, string entityName = null) where T : class { var typeNameInPlural = typeof(T).Name.ToPlural(); IList<T> records; if (!string.IsNullOrWhiteSpace(entityName)) { if (string.IsNullOrWhiteSpace(tableName)) throw new ArgumentNullException("tableName", "This should not be null"); typeNameInPlural = entityName; Console.WriteLine("Migrating {0}...", typeNameInPlural); records = fromSession.QueryOver<T>(entityName).List(); } else { Console.WriteLine("Migrating {0}...", typeNameInPlural); if (string.IsNullOrWhiteSpace(tableName)) tableName = typeNameInPlural; records = fromSession.QueryOver<T>().List(); } if (records != null && records.Count > 0) { Console.WriteLine("{0} retrieved. About to flush them into the new database...", typeNameInPlural); SqlManipulations.SqlBulkInsert<T>(records, toSession.Connection, tableName, entityName); Console.WriteLine("Done flushing {0} into the new database...", typeNameInPlural); } else { Console.WriteLine("No data found for {0}", typeNameInPlural); } } } } - Build this library and reference it in the console app. You will also need to reference the library containing the entity definitions, NHibernate libraries and all other relevant ones you may need.
- In the console app, create a class, called Migrator to implement our IMigrator interface. The implementation of the Migrate method will be merely a call to Runner's MoveData method. Here's the code:
using NHibernate; using DataMigration; namespace DataMigrationConsole { class Migrator : IMigrator { public void Migrate(ISession fromSession, ISession toSession) { // For each of our entities, we'll call MoveData in a similar way // Here, we demonstrate with just two entities Runner.MoveData<UserRole>(fromSession, toSession); //table name left out Console.WriteLine("Done UserRoles \n"); Runner.MoveData<User>(fromSession, toSession, "Users"); //table name supplied Console.WriteLine("Done Users \n"); } } } - Create an empty database and use your ORM's schema update feature to generate the database tables. This way, you are guaranteed to get the exact schema you have on the server.
- Finally we modify the Console App's Main method and run the program. The Main method will look like this:
static void Main(string[] args) { Runner.Run(new Migrator()); //This is all we need to call Console.WriteLine("\n***End of Program***"); Console.ReadKey(); }
Further Improvements
Suppose you have a large number of entities in your project, using the code in No. 8 will quickly become unpleasant. You can solve this problem by modifying Migrator and Runner classes.For Migrator class, provide a way to supply the entities you're interested in - either by listing them directly or by supplying the assembly where they reside. Here's the modified version:
using NHibernate;
using DataMigration;
using System.Reflection;
namespace DataMigrationConsole
{
class Migrator : IMigrator
{
Type[] _relevantEntities;
///
/// Constructor
///
/// The assembly where the Entity Definitions were created
public Migrator(string assemblyName)
{
// This code will load all the classes in this assembly. However, you can filter out your entities by
// either making them implement an interface, then filter out all types that implement that interface;
// or by decorating your entity classes with an Attribute, and filtering by that attribute.
_relevantEntities = Assembly.Load(assemblyName).GetTypes();
}
///
/// Constructor
///
/// The names of the classes you want to migrate their data
public Migrator(string[] entityClassNames)
{
var theTypes = new List<Type>();
foreach (var entityName in entityClassNames)
{
var type = Type.GetType(entityName, false);
if (type != null)
{
theTypes.Add(type);
}
}
_relevantEntities = theTypes.ToArray();
}
public void Migrate(ISession fromSession, ISession toSession)
{
foreach (var entityType in _relevantEntities)
{
var tableName = entityType.Name.ToPlural();
Runner.MoveData(entityType, fromSession, toSession, tableName);
Console.WriteLine("Done {0} \n", tableName);
}
}
}
}
With this, you can now modify Main method to use the constructor that suits you best.But wait: did you notice that I used a different overload of Runner's MoveData? The implementation for this overload is shown below. Notice that I now used NHibernate session's CreateCriteria instead of QueryOver. Notice also that our SqlBulkInsert method had to change:
public static void MoveData(Type entityType, ISession fromSession, ISession toSession, string tableName = null, string entityName = null) where T : class
{
var typeNameInPlural = entityType.Name.ToPlural();
IList records;
if (!string.IsNullOrWhiteSpace(entityName))
{
if (string.IsNullOrWhiteSpace(tableName)) throw new ArgumentNullException("tableName", "This should not be null");
typeNameInPlural = entityName;
Console.WriteLine("Migrating {0}...", typeNameInPlural);
records = fromSession.CreateCriteria(entityName).List();
}
else
{
Console.WriteLine("Migrating {0}...", typeNameInPlural);
if (string.IsNullOrWhiteSpace(tableName)) tableName = typeNameInPlural;
records = fromSession.CreateCriteria(entityType).List();
}
if (records != null && records.Count > 0)
{
Console.WriteLine("{0} retrieved. About to flush them into the new database...", typeNameInPlural);
SqlManipulations.SqlBulkInsert(entityType, records, toSession.Connection, tableName, entityName);
Console.WriteLine("Done flushing {0} into the new database...", typeNameInPlural);
}
else
{
Console.WriteLine("No data found for {0}", typeNameInPlural);
}
}
That's all. Quite easy, isn't it?Share your thoughts and feedback in the Comments section below.
