Export/Import subset of data from SQL-Server

As a feature for our application, I am looking to find the best way to export a subset of related data from a database from SQL-Server 2008 Express database out to disk. The exported content is required to be imported keeping identities into another like database and server.

I have a medium sized data model of 143 tables, on which I use the Entity Framework 3.5 for data access in our applications. From the model I want to extract rows from nearly all tables based on given criteria.

New to C# and the entity-framework, but not new to SQL-Server, I've taken over this project from another developer who began by writing ToXML() methods for each entity to be extracted (20% done). Clearly a maintenance nightmare. Worse would be the FromXML() to entities, where there are complexities having to do with unique keys etc...

I experimented with SELECT ... FOR XML AUTO, XMLSCHEMA combined with Microsoft SQLXML Bulk Loading. While at first it went well with plain vanilla tables, I soon ran into a dead-end because it appears not to support XML data types without schemas or at least without a good deal of manual intervention. We have several tables with XML data type columns. Also there are yet unknown complexities with bulk-loading in general having to do with triggers, nulls, unique keys, and contraints, and more that I haven't encountered yet I presume.

One idea I had was to write a matching XSD schema for our database (or generate one) and use XSD.exe to generate a class model as a collection of DTOs. Then use a mapping library like AutoMapper to populate the DTOs and serialize them to disk. Then do the reverse on import.

I know this is big and broad question, but can anyone provide some guidance or ideas with this? Are there ways this can be done wi开发者_StackOverflow中文版th options in the entity-framework? Are there open-source libraries that can do this?

I don't know about your object model, but could you possibly just use XML serialization?

    public static string ObjectToXML(object Object)
    {
        if (Object == null)
            throw new ArgumentException("Object can not be null");
        using (MemoryStream Stream = new MemoryStream())
        {
            XmlSerializer Serializer = new XmlSerializer(Object.GetType());
            Serializer.Serialize(Stream, Object);
            Stream.Flush();
            return UTF8Encoding.UTF8.GetString(Stream.GetBuffer(), 0, (int)Stream.Position);
        }
    }

    public static T XMLToObject<T>(string XML)
    {
        if (string.IsNullOrEmpty(XML))
            throw new ArgumentException("XML can not be null/empty");
        using (MemoryStream Stream = new MemoryStream(UTF8Encoding.UTF8.GetBytes(XML)))
        {
            XmlSerializer Serializer = new XmlSerializer(typeof(T));
            return (T)Serializer.Deserialize(Stream);
        }
    }

The main issue that I see is the XML data though (possibly wrap it in a CDATA prior to serialization?).

What about using the SQL Server Compact to save your exports. Then you can use Microsoft Sync Framework to export/import all needed data including identities.

See Microsoft Sync Framework and Synchronizing Databases