Database Conversion Script Helper Functions

Sorry it’s been so long since my last post, life has kept me quite busy!

I’m writing a database conversion script for work. I modified an older version of a database for a project and made numerous changes to the schema. Transferring data from the old database to the new database after such changes have been made is not an easy task: renamed columns are trivial, but foreign-key lookup tables are not. Making matters worse is that I can never get the SSMS Copy Database Wizard to work correctly, and when I just use the Script Database with Data option, figuring I could use some regular expressions to convert the generated script to be used with the new schema, SSMS gives me ‘Out of memory’ exceptions. Therefore, I decided to make a database conversion console program that I can run a few times, since it’s not a one-time conversion – I need to test the conversion, then have a basic version of the program for the owner of the company I work for and the end-user to test with, and then the final copy of the data to be transferred to the new database when the end-user starts working with the new software.

I came up with some helper functions I made to ease the creation of the script. Some of the tables from the old database remained unchanged in the new database, which I used _directCopy() to copy the data; for tables with changes, I used _kindaDirectCopy() to make the changes. This was done in a console program, so the static prefixes can be removed depending on your use.

/// <summary>
/// Log to screen and debug
/// </summary>
/// <param name="s"></param>
static void log(string s) {
 string op = string.Format("{0}: {1}", DateTime.Now.ToString().PadLeft(25, ' '), s);
 Console.WriteLine(op);
 Debug.WriteLine("*** " + op);
}

/// <summary> Clones an object to a similar type. The TResult and TSource must have the same properties,
/// but can be from two different namespaces.
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="obj"></param>
/// <returns></returns>
static TResult cloneObjectToAnotherGraph<TSource, TResult>(TSource source) {
 MemoryStream ms = new MemoryStream();
 //BinaryFormatter bf = new BinaryFormatter();
 XmlSerializer xs = new XmlSerializer(typeof(TSource));
 //bf.Serialize(ms, source);
 xs.Serialize(ms, source);
 ms.Position = 0;
 // This part takes anything in the serialized portion that is referencing
 // "OldDB" and converting it to "NewDB" so EF doesn't gripe when I try to 
 // add the serialized object back into the new database.
 byte[] buffer = stringToBuffer(bufferToString(ms.ToArray()).Replace("OldDB", "NewDB"));
 ms.Close();
 xs = new XmlSerializer(typeof(TResult));
 //object obj = bf.Deserialize(ms);
 object obj = xs.Deserialize(new MemoryStream(buffer)); // xs.Deserialize(ms);
 ms.Close();
 TResult t = (TResult)obj;
 // Detach the entity from the old DB and attach to the new entity by creating a new entity key.
 string tn = (typeof(TResult)).ToString();
 tn = tn.Substring(tn.LastIndexOf('.') + 1);
 string key = "NewDBEntities." + tn;
 (t as EntityObject).EntityKey = newDB.CreateEntityKey(key, t);
 return t;
}

/// <summary> Converts a byte array into a string.
/// </summary>
/// <param name="buffer"></param>
/// <returns></returns>
static string bufferToString(byte[] buffer) {
 /*
 StringBuilder sb = new StringBuilder();
 foreach (byte b in buffer) {
 sb.Append(b.ToString());
 }
 return sb.ToString();
 */ 
 return System.Text.Encoding.UTF8.GetString(buffer);
}

/// <summary> Converts a string into a byte array.
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
static byte[] stringToBuffer(string s) {
 return System.Text.Encoding.UTF8.GetBytes(s);
}

/// <summary>  Gets the name of the underlying database table.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="source"></param>
/// <returns></returns>
static string getTableNameFromEntityList<T>(ObjectSet<T> source) where T : class {
 var p = source.EntitySet.ElementType.MetadataProperties.SingleOrDefault(e => e.Name == "Name");
 if (p != null) { return (string)p.Value; } else { return null; }
}

/// <summary> Turns identity insert ON or OFF for a given table in a given data context.
/// </summary>
/// <param name="store"></param>
/// <param name="tableName"></param>
/// <param name="onOff"></param>
static void setTableIdentityInsert(ObjectContext store, string tableName, bool onOff) {
 if (string.IsNullOrWhiteSpace(tableName)) { return; } // Do nothing if the table name is null or empty.
 try {
 store.ExecuteStoreCommand(string.Format("SET IDENTITY_INSERT {0} {1};",
 tableName,
 onOff ? "ON" : "OFF"));
 } catch (SqlException) {
 // Do nothing, it means that the table has no identity column
 } catch (Exception) {
 throw;
 }
}

/// <summary> Performs a direct translation of items in a source list of a type TSource into a
/// destination list of type TResult by serializing each source object and restoring it
/// into a TResult object. TSource and TResult must be the same object structure, or at
/// least able to serialize the contents of TSource and deserialize into TResult.
/// Used for database conversion scripts, where identical entities cannot be interchanged
/// normally because the types still differ (even if it is only the namespace that differs).
/// </summary>
/// <typeparam name="TSource"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="sourceList"></param>
/// <param name="destList"></param>
static void _directCopy<TSource, TResult>(ObjectSet<TSource> sourceList, ObjectSet<TResult> destList) 
 where TSource : class
 where TResult : class
{
 // Get the name of the underlying table, to turn on identity insert
 setTableIdentityInsert(newDB, getTableNameFromEntityList<TResult>(destList), true);
 long count = 0;
 sourceList.ToList().ForEach(x => {
 var y = cloneObjectToAnotherGraph<TSource, TResult>(x);
 destList.AddObject(y);
 count++;
 });
 newDB.SaveChanges(); // this can be taken out, it's just in for persistence.
 // Turns off identity insert.
 setTableIdentityInsert(newDB, getTableNameFromEntityList<TResult>(destList), false);
 log(string.Format("{0}: {1}",
 count.ToString().PadLeft(10, ' '),
 typeof(TResult).Name));
}

/// <summary> Performs a translation of items in a source list of a type TSource into a destination
/// list of type TResult by invoking a delegate/anonymous function that performs the conversion
/// of each object from TSource to a TResult.
/// If identityInsertTableName is not null, then identity insert is turned ON for the duration of
/// the call, and turned off before completing. Leave this as null if you don't want identity insert on.
/// Used for database conversion scripts where the two objects differ.
/// The counter is passed into the delegate, to be used in situations where you need an auto-incrementing
/// ID (example: When a surrogate autonumbering key is added to a table with no key/natural keys).
/// </summary>
static void _kindaDirectCopy<TSource, TResult>(ObjectSet<TSource> sourceList, ObjectSet<TResult> destList, Func<TSource, int, TResult> conversionDelegate)
 where TSource : class
 where TResult : class 
{
 setTableIdentityInsert(newDB, getTableNameFromEntityList<TResult>(destList), true);
 int counter = 0;
 sourceList.ToList().ForEach(x => {
 TResult y = conversionDelegate.Invoke(x, ++counter);
 destList.AddObject(y);
 });
 newDB.SaveChanges();
 setTableIdentityInsert(newDB, getTableNameFromEntityList<TResult>(destList), false);
}

Here’s how to use the code:

// aspnet_Applications
_directCopy<OldDB.aspnet_Applications, NewDB.aspnet_Applications>(oldDB.aspnet_Applications, newDB.aspnet_Applications);

// aspnet_SchemaVersions
_directCopy<OldDB.aspnet_SchemaVersions, NewDB.aspnet_SchemaVersions>(oldDB.aspnet_SchemaVersions, newDB.aspnet_SchemaVersions);

// aspnet_Users
_directCopy<OldDB.aspnet_Users, NewDB.aspnet_Users>(oldDB.aspnet_Users, newDB.aspnet_Users);

// aspnet_WebEvent_Events
_directCopy<OldDB.aspnet_WebEvent_Events, NewDB.aspnet_WebEvent_Events>(oldDB.aspnet_WebEvent_Events, newDB.aspnet_WebEvent_Events);
 
// aspnet_Paths
_directCopy<OldDB.aspnet_Paths, NewDB.aspnet_Paths>(oldDB.aspnet_Paths, newDB.aspnet_Paths);
 
// aspnet_Roles
_directCopy<OldDB.aspnet_Roles, NewDB.aspnet_Roles>(oldDB.aspnet_Roles, newDB.aspnet_Roles);


// Notes
_directCopy<OldDB.Note, NewDB.Note>(oldDB.Notes, newDB.Notes);

// Contact Types
_directCopy<OldDB.ContactType, NewDB.ContactType>(oldDB.ContactTypes, newDB.ContactTypes);

// Contacts - The old table had a natural key and the new table has a surrogate auto-incrementing
//        key, so I used the count parameter to give me an incrementing number to use
_kindaDirectCopy<OldDB.Contact, NewDB.Contact>(oldDB.Contacts, newDB.Contacts, (x, count) => {
 return new NewDB.Contact() {
 ContactID = count,
 ContactInfo = x.ContactInfo,
 ContactTypeID = x.ContactTypeID,
 OrganizationID = _org,
 PersonID = x.PersonID
 };
});

 

Hope this helps.