Using SQL Server Compact 4.0 from PowerShell is easy and powerful.

Here is an example that shows how to reference the needed libraries, create the database, and add a table to the database – all from PowerShell.

   1: $binpath = "C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\";
   2: [Reflection.Assembly]::LoadFile("$binpath\System.Data.SqlServerCe.dll")
   3: $connectionString = "Data Source='C:\temp\testDB.sdf';"
   4:  
   5: $engine = New-Object "System.Data.SqlServerCe.SqlCeEngine" $connectionString
   6: $engine.CreateDatabase()
   7: $engine.Dispose()
   8:  
   9: $connection = New-Object "System.Data.SqlServerCe.SqlCeConnection" $connectionString
  10: $command = New-Object "System.Data.SqlServerCe.SqlCeCommand"
  11: $command.CommandType = [System.Data.CommandType]"Text"
  12: $command.Connection = $connection
  13:  
  14: $connection.Open()
  15:  
  16: $command.CommandText = "CREATE TABLE [Files] ([Id] int NOT NULL  IDENTITY (1,1), [Name] nvarchar(450) NOT NULL);"
  17: $command.ExecuteNonQuery()        
  18:             
  19: $command.CommandText = "ALTER TABLE [Files] ADD CONSTRAINT [PK_Files] PRIMARY KEY ([Id]);"
  20: $command.ExecuteNonQuery()
  21:             
  22: $command.CommandText = "CREATE UNIQUE INDEX [IX_Files_Name] ON [Files] ([Name] ASC);"
  23: $command.ExecuteNonQuery()
  24:  
  25: $command.Dispose()
  26: $connection.Close();
  27: $connection.Dispose;
  28:  
  29:

Using SQL Server Compact also provides the ability to deploy the scripts without having to install an SQL Server on the target machine.  You can copy the libraries from “C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\bin” and “C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop” to the location of your scripts and the SQL Compact Engine will be able to run anywhere.