Wednesday, April 29, 2009

SQL Server CE Export

I ran into what is probably not a unique problem. I designed a CE database thinking I could export the structure as a SQL script as you do for SQL Server. I googled and didn't manage to find any exporters (I found a few that export from SQL Server to SQL Server Compact Edition). I ended up writing my own. The enclosed version is far from complete and will only export tables with standard fields and primary keys. This is fine for what I have done to date but I might add some more features later.
The program is a console progam.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;

namespace ScriptCEDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            if (args.GetUpperBound(0) <>
            {
                Console.WriteLine("Usage:ScriptCEDatabase {Dbfile} {sqlfilepath} [password]");
                return;
            }
                System.Data.SqlServerCe.SqlCeConnection conn = new SqlCeConnection();
                conn.ConnectionString = "Data Source=" + args[0] + "; Persist Security Info=True";
                if (args.GetUpperBound(0) == 2)
                    conn.ConnectionString = conn.ConnectionString + ";password=" + args[2];
                conn.Open();
                SqlCeCommand TableList = new SqlCeCommand("select * from information_schema.tables", conn);
                SqlCeDataReader TableReader = TableList.ExecuteReader();

                string filename = args[1];

                if (filename.EndsWith("\\")) filename += "\\";
                filename += System.Guid.NewGuid().ToString() + ".sql";
                System.IO.StreamWriter sw = new System.IO.StreamWriter(filename, false);
 
                while (TableReader.Read())
                {
                    Console.WriteLine("CREATE TABLE [" + TableReader["TABLE_NAME"].ToString() + "] (");
                    sw.WriteLine("CREATE TABLE [" + TableReader["TABLE_NAME"].ToString() + "] (");
                    SqlCeCommand FieldList = new SqlCeCommand("select * from information_schema.columns where TABLE_NAME = '" + TableReader["TABLE_NAME"].ToString() + "'"
                        + " order by ordinal_position",conn );
                    SqlCeDataReader ColumnReader = FieldList.ExecuteReader();
                    bool isFirst = true;
                    while (ColumnReader.Read())
                    {
                        if (!isFirst)
                            sw.Write(",");
                        else
                            isFirst = false;

                        Console.Write( "[" + ColumnReader["Column_name"] + "] " + ColumnReader["DATA_TYPE"]);
                        if (ColumnReader["DATA_TYPE"].ToString() == "nchar" || ColumnReader["DATA_TYPE"].ToString() == "nvarchar")
                            Console.Write("(" + ColumnReader["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")");
                        if (ColumnReader["IS_NULLABLE"].ToString() == "YES")
                            Console.WriteLine(" NULL,");
                        else
                            Console.WriteLine(" NOT NULL,");
                            sw.Write(ColumnReader["Column_name"] + " " + ColumnReader["DATA_TYPE"]);
                        if (ColumnReader["DATA_TYPE"].ToString() == "nchar" || ColumnReader["DATA_TYPE"].ToString() == "nvarchar")
                            sw.Write("(" + ColumnReader["CHARACTER_MAXIMUM_LENGTH"].ToString() + ")");
                        if (ColumnReader["IS_NULLABLE"].ToString() == "YES")
                            sw.WriteLine(" NULL");
                        else
                            sw.WriteLine(" NOT NULL");
                    }
                    ColumnReader.Close();
                    Console.WriteLine(");");
                    sw.WriteLine(");");
                    sw.WriteLine();

                }
                TableReader.Close();
            // Indexes
                
                SqlCeCommand cmdIndex = new SqlCeCommand("SELECT * FROM INFORMATION_SCHEMA.INDEXES", conn);
                SqlCeDataReader rdrIndex = cmdIndex.ExecuteReader();
                while(rdrIndex.Read()){
                    if  ( rdrIndex["PRIMARY_KEY"].ToString() == "True")
                    {
                        Console.WriteLine ("ALTER TABLE " + rdrIndex["TABLE_NAME"] + " ADD CONSTRAINT " + rdrIndex["INDEX_NAME"] + " PRIMARY KEY (" + rdrIndex["COLUMN_NAME"] + ");");
                        sw.WriteLine();
                        sw.WriteLine ("ALTER TABLE [" + rdrIndex["TABLE_NAME"] + "] ADD CONSTRAINT " + rdrIndex["INDEX_NAME"] + " PRIMARY KEY ( [" + rdrIndex["COLUMN_NAME"] + "] );"); 
                    }
                }

                sw.Close();
        }
    }
}
Post a Comment