Using PowerShell to manage SQL Server Compact database files

I noticed that someone asked if you could manage SQL Server Compact from PowerShell, and yes you can, based on the reply here I put together the following sample:

[Reflection.Assembly]::LoadFile(“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll”)

$connString = "Data Source=C:\data\sqlce\test\roads.sdf"
$cn = new-object "System.Data.SqlServerCe.SqlCeConnection" $connString

# create the command
$cmd = new-object "System.Data.SqlServerCe.SqlCeCommand"
$cmd.CommandType = [System.Data.CommandType]"Text"
$cmd.CommandText = "SELECT TOP (100) * FROM Road"
$cmd.Connection = $cn

#get the data
$dt = new-object "System.Data.DataTable"

$cn.Open()
$rdr = $cmd.ExecuteReader()

$dt.Load($rdr)
$cn.Close()

$dt | Out-Default | Format-Table

Windows Phone / SQL Server Compact resources

This blog post collects links to relevant blog posts and articles about Windows Phone support for SQL Server Compact. You can also follow me on Twitter (@ErikEJ) to get notified of any SQL Server Compact related news.

MSDN

Local Database Overview for Windows Phone

How to: Create a Basic Local Database Application for Windows Phone

How to: Create a Local Database Application with MVVM for Windows Phone

How to: Deploy a Reference Database with a Windows Phone Application

Walkthrough: Updating a Local Database Application for Windows Phone

Local Database Best Practices for Windows Phone

Local Database Connection Strings for Windows Phone

Local Database Migration Overview for Windows Phone

LINQ to SQL Support for Windows Phone

Video: SQL Server Compact and User Data Access in Mango

PowerPoint slides: SQL Server Compact and User Data Access in Mango

Video + PPT: New Data Access Features Coming to Windows Phone

Windows Phone Mango Application Storage Jumpstart PDF

Get to Mango #8 - Using a Local Database (Screencast)

ErikEJ

Populating a Windows Phone “Mango” SQL Server Compact database on desktop

SQL Server Compact Toolbox 2.2–Visual Guide of new features

Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

Windows Phone Local Database tip: Batch INSERT performance

Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

Windows Phone Local Database tip: Initializing the database

SQL Server Compact Toolbox 2.6.1–Visual Guide of new features

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

Windows Phone Local Database tip: Working with encrypted database files

Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”

Generate a Windows Phone 8 Local Database DataContext from an existing database

Jesse Liberty

Coming in Mango–Sql Server CE

Coming In Mango–Local DB Part 2- Relationships

Best Practices For Local Databases

Yet Another Podcast #43–Sean McKenna and Windows Phone Data

Sean McKenna and Windows Phone "Mango" Database Support

Rob Tiffany

New Windows Phone Mango Data Access Features @ Tech Ed North America 2011

Alex Golesh

Windows Phone Mango–What’s New? (“Local Database” - Part 1 of 8)

Andy Wigley

Windows Phone LINQ to SQL and Data Virtualization: How to work with large tables in LINQ to SQL on Windows Phone

Windows Phone Geek

Windows Phone Mango Local Database- mapping and database operations

Using SqlMetal to generate Windows Phone Mango Local Database classes

Performance Best Practices: Windows Phone Mango Local Database

Windows Phone Mango Local Database(SQL CE): Introduction

Windows Phone Mango Local Database(SQL CE): Linq to SQL

Windows Phone Mango Local Database(SQL CE): [Table] attribute

Windows Phone Mango Local Database(SQL CE): [Column] attribute

Windows Phone Mango Local Database(SQL CE): [Association] attribute

Windows Phone Mango Local Database(SQL CE): Database mapping

Windows Phone Mango Local Database(SQL CE): DataContext

Windows Phone Mango Local Database(SQL CE): Connection Strings

Windows Phone Mango Local Database(SQL CE): Creating the Database

Windows Phone Mango Local Database(SQL CE): Database Queries with LINQ

Windows Phone Mango Local Database(SQL CE): How to Insert data

Windows Phone Mango Local Database(SQL CE): How to Update data

Windows Phone Mango Local Database(SQL CE): How to Delete data

C# Corner

Getting Started With Local Database Operations in Windows Phone 7

Arsahnt

Distributing a SQL CE database in a WP7 Mango application

Arsanth Daily – May 25th

Arsanth Daily – May 27th

Windows Phone 7 SQL CE – Column inheritance

Windows Phone 7 SQL CE – DataContext Tables

Working with pre-populated SQL CE databases in WP7

LINQ to SQL CE performance tips for WP7

Arsanth Daily – June 6th

Arsanth – August 16th

Arsanth – August 18th

How To: Log LINQ to SQL activity on WP7

Kunal Chowdhury

Windows Phone 7 (Mango) Tutorial - 22 - Local Database Support, Create DataContext

Windows Phone 7 (Mango) Tutorial - 23 - Local Database Support, Configuring Project

Windows Phone 7 (Mango) Tutorial - 24 - Local Database Support, CRUD operation with Demo

Windows Phone 7 (Mango) Tutorial - 25 - Learn about Database Connection String

Sergey Barskiy

SQL CE on Windows Phone 7.1 (Mango)

SQL CE in Mango–Updating the Schema

Derik Whittaker

Using SQL CE on WP7 Mango–Getting Started

Using SQL CE On WP7 Mango–Working with Associations

Using SQL CE On WP7 Mango–Working with Indexes 

Mark Artega

Windows Phone and Database Support

Rabeb

Mango- Baby Steps: Creating an application with a local Database

JeffCren

WP7 App First Run Logic

Matt Lacey

Simplifying use of SQL CE in Mango

Corrado

Using Local Database in WP7-Mango

Max Paulousky

Windows Phone (Mango) DB Engines Performance Testing

Nick Randolph

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

Windows Phone LINQ to SQL and the INotifyPropertyChanged and INotifyPropertyChanging Interfaces

Understanding LINQ to SQL on Windows Phone 7.5

Chris Sainty

WP7.5 Mango–Compiled Queries

Anton Swanvelder

Writing an Image to SQL CE / Linq to SQL

Rafa Serna

Performance Improvements in SQL CE environments – I

Debug mode

Local Database application for windows Phone

Shazaml Design, LLC

More Complete WP7 Mango Database Update Walkthrough

Jerry Nixon

Mango Sample: Database Part 1:2

Mango Sample: Database Part 2:2

Mango Sample: SQL Toolbox

Mango- Database (screencast)

Bhaskar Shrestha

Using SQL Server Compact in Windows Phone 7.1 apps – Part 1

Using SQL Server Compact in Windows Phone 7.1 apps – Part 2

Using SQL Server Compact in Windows Phone 7.1 apps – Part 3

Using SQL Server Compact in Windows Phone 7.1 apps – Part 4