CODEWITHSUNDEEP
linqwindows-phone-7linq-to-sqlsql-server-cewindows-phone
Ian Newson
Ian Newson

Reputation: 7949

Windows Phone SQL Server CE - Retrieve underlying database schema for upgrade

Windows Phone 7.1 supports SQL Server CE and LINQ to SQL, as well as upgrading the database via DatabaseSchemaUpdater.

On other platforms I would read the database schema tables (e.g. sys.objects) to view the current schema and work out what tables/columns need to be upgraded.

Given that no direct SQL access is allowed on Windows Phone, how can retrieve the current database schema?

Upvotes: 3

Views: 751

Answers (2)

Rowland Shaw
Rowland Shaw

Reputation: 38130

There is a Walkthrough for Updating a Local Database Application for Windows Phone on MSDN which advocates using the DatabaseSchemaVersion on the DatabaseSchemaUpdater - i.e:

// Set the new database version.
DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
dbUpdater.DatabaseSchemaVersion = APP_VERSION;
dbUpdater.Execute();

You can query the version and add the bits that you add in each release, without having to worry about the current schema (after all, it'll be a known configuration as long as you remember to keep the version numbers updated correctly.)

Upvotes: 0

Ian Newson
Ian Newson

Reputation: 7949

SQL Server CE still includes the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS tables, but it is a little tricky to access them as no direct SQL access is allowed.

However, you can create a DataContext which maps to these tables:

public class SchemaContext : DataContext
{
    public SchemaContext()
        : base("Data Source=isostore:/Database.sdf")
    {
        if (!this.DatabaseExists())
        {
            throw new InvalidOperationException("Cannot use the SchemaContext on a database which doesn't exist");
        }
    }

    public Table<Table> Tables;
    public Table<Column> Columns;

    [Table(Name = "INFORMATION_SCHEMA.Columns")]
    public class Column
    {
        [Column(Name = "TABLE_NAME")]
        public string TableName { get; set; }

        [Column(Name = "COLUMN_NAME")]
        public string Name { get; set; }

        [Column(Name = "DATA_TYPE")]
        public string DataType { get; set; }

        [Column(Name = "ORDINAL_POSITION")]
        public int OrdinalPosition { get; set; }

        [Column(Name = "IS_NULLABLE")]
        public string IsNullableString { get; set; }

        public bool IsNullable
        {
            get { return this.IsNullableString == "YES"; }
            set { this.IsNullableString = value ? "YES" : "NO"; }
        }

    }

    [Table(Name = "INFORMATION_SCHEMA.Tables")]
    public class Table
    {
        [Column(Name = "TABLE_NAME")]
        public string Name { get; set; }

        [Column(Name = "TABLE_TYPE")]
        public string Type { get; set; }
    }
}

You can then read the schema with the following code:

using (var schemaContext = new SchemaContext())
{
    foreach (var table in schemaContext.Tables)
    {

    }
}

It's important to create a separate context for these tables, as otherwise the DataContext.CreateDatabase call will attempt to create these schema tables, which will fail.

Upvotes: 1

Related Questions