To use the scripting library, add references to ISqlCeScripting.dll and SqlCeScripting.dll (and optionally SqlCeScripting40.dll for version 4.0 support).

The API exposes 3 repository implementations, DBRepository (for 3.5 SQL Server Compact files), DB4Repository (for SQL Server Compact 4.0 files) and ServerDBRepository (for SQL Server 2005 and later databases).

Generate a single table script

using ErikEJ.SqlCeScripting;
....
using (IRepository repository = new DBRepository(@"Data Source=C:\Northwind.sdf"))
{
    Generator generator = new Generator(repository, null);
    foreach (var tableName in repository.GetAllTableNames())
    { 
        generator.GenerateTableScript(tableName);
    }
    System.IO.File.WriteAllText(@"C:\script.sqlce", generator.GeneratedScript);    
}

Generate data (INSERTs) from all tables script

using ErikEJ.SqlCeScripting;
....
using (IRepository repository = new DBRepository(@"Data Source=C:\Northwind.sdf"))
{
    Generator generator = new Generator(repository, null);
    foreach (var tableName in repository.GetAllTableNames())
    { 
        generator.GenerateTableContent(tableName, false);
    }
    System.IO.File.WriteAllText(@"C:\script.sqlce", generator.GeneratedScript);    
}

Generate a diff script

using (IRepository sourceRepository = new DBRepository(@"Data Source=C:\data\source.sdf"))
{
    var generator = new Generator(sourceRepository);
    using (IRepository targetRepository = new DBRepository(@"Data Source=C:\data\target.sdf"))
    {
        SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator);
        //textBox.Text = generator.GeneratedScript;
    }
}
  • Current APIs *

    public interface IRepository : IDisposable
    {
        List<string> GetAllTableNames();
        List<Column> GetColumnsFromTable();
        DataTable GetDataFromTable(string tableName, List<Column> columns);
        IDataReader GetDataFromReader(string tableName);
        List<PrimaryKey> GetAllPrimaryKeys();
        List<Constraint> GetAllForeignKeys();
        List<Constraint> GetAllForeignKeys(string tableName);
        List<Index> GetIndexesFromTable(string tableName);
        List<KeyValuePair<string, string>> GetDatabaseInfo();
        Boolean HasIdentityColumn(string tableName);
        Boolean IsServer();
        Int32 GetRowVersionOrdinal(string tableName);
        Int64 GetRowCount(string tableName);
        void RenameTable(string oldName, string newName);
        /// <summary>
        /// Runs the supplied script
        /// </summary>
        DataSet ExecuteSql(string script);
        /// <summary>
        /// Execute the supplied script, and return the Actual Execution Plan
        /// </summary>
        DataSet ExecuteSql(string script, out string showPlanString);
        /// <summary>
        /// Get the Showplan XML from a SQL statement
        /// </summary>
        string ParseSql(string script);
        /// <summary>
        /// Get the local Datetime for last sync
        /// </summary>
        /// <param name="publication"> Publication id: EEJx:Northwind:NwPubl</param>
        DateTime GetLastSuccessfulSyncTime(string publication);
        /// <summary>
        /// Returns a list of all Merge subscriptions in the database
        /// </summary>
        List<string> GetAllSubscriptionNames();
    }

    public interface IGenerator
    {
        string ScriptDatabaseToFile(Scope scope);
        void GenerateTableScript(string tableName);
        string GenerateTableData(string tableName, bool saveImageFiles);
        void GenerateTableContent(string tableName, bool saveImageFiles);
        string GeneratedScript {get;}
        void GenerateTableSelect(string tableName);
        void GenerateTableInsert(string tableName);
        void GenerateTableUpdate(string tableName);
        void GenerateTableDelete(string tableName);
        void GenerateTableDrop(string tableName);
        void GenerateTableCreate(string tabelName);
        void GenerateTableInsert(string tableName, IList<string> fields, IList<string> values);
        bool ValidColumns(string tableName, IList<string> columns);
        void GenerateSchemaGraph(string connectionString);
        void GeneratePrimaryKeys(string tableName);
        void GenerateForeignKeys(string tableName);
        void GenerateIndexScript(string tableName, string indexName);
        void GenerateIndexDrop(string tableName, string indexName);
        void GenerateIndexStatistics(string tableName, string indexName);
        List<string> GenerateTableColumns(string tableName);

        void GenerateColumnAddScript(Column column);
        void GenerateColumnDropScript(Column column);
        void GenerateColumnAlterScript(Column column);
        void GenerateColumnSetDefaultScript(Column column);
        void GenerateColumnDropDefaultScript(Column column);
        void GeneratePrimaryKeyDrop(PrimaryKey primaryKey, string tableName);
        void GenerateForeignKey(Constraint constraint);
        void GenerateForeignKeyDrop(Constraint constraint);
    }

Last edited Dec 27, 2011 at 9:07 AM by ErikEJ, version 10

Comments

ErikEJ Sep 5, 2012 at 1:05 PM 
It is not implemented for SQL Server Compact, only SQL Server (currently)

theBlaza Sep 5, 2012 at 12:07 PM 
using (IRepository repository = Helper.CreateRepository("Data Source = '" + ResultDir + "db.sdf';"))
{ try {
ResultDir = Environment.CurrentDirectory + @"\Result\";
if (!Directory.Exists(ResultDir))
Directory.CreateDirectory(ResultDir);
repository.ExecuteSqlFile(ResultDir + "script.sql");
}
catch (Exception ex)
{
Logs.Add(ex.Message);
}
}

Looks like repository.ExecuteSqlFile(ResultDir + "script.sql");
wont work, do you have some update about this?
Thank you

ErikEJ Mar 3, 2012 at 1:19 AM 
You nned to add a reference to this and the SqlCeScripting and/or the SqlCeScripting40 .dll

Neda_1982 Mar 2, 2012 at 3:37 PM 
Hi Erik,

I tried to use your code for Generating a single table script, but it gives me an error:

Could not load file or assembly 'ISqlCeScripting, Version=3.5.2.0, Culture=neutral, PublicKeyToken=3681435cabd17ad2' or one of its dependencies. The system cannot find the file specified.

Is there any extra code I should add?

Thanks,

Neda