PowerShell and SQL Server Compact 4.0: A Happy Mix

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.

Comments

• Anonymous
  December 27, 2014
  The comment has been removed