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();

        }

    }

}