Code Samples for the Windows Phone 8 Database webinar

Those are the code samples from my Webinar, if you are a regular reader of this blog and didn't come here from the webinar you should probably ignore this post.

The table class definition - Treasure.cs download.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TreasureApp1.Data
{
    [Table]
    public class Treasure : INotifyPropertyChanged, INotifyPropertyChanging
    {
        private int _id;
        private string _type;
        private int _amount;
        private string _unit;

        [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
        public int Id
        {
            get { return _id; }
            set
            {
                if (_id != value)
                {
                    NotifyPropertyChanging("Id");
                    _id = value;
                    NotifyPropertyChanged("Id");
                }
            }
        }

        [Column]
        public string Type
        {
            get { return _type; }
            set
            {
                if (_type != value)
                {
                    NotifyPropertyChanging("Type");
                    _type = value;
                    NotifyPropertyChanged("Type");
                }
            }
        }

        [Column]
        public int Amount
        {
            get { return _amount; }
            set
            {
                if (_amount != value)
                {
                    NotifyPropertyChanging("Amount");
                    _amount = value;
                    NotifyPropertyChanged("Amount");
                }
            }
        }

        [Column]
        public string Unit
        {
            get { return _unit; }
            set
            {
                if (_unit != value)
                {
                    NotifyPropertyChanging("Unit");
                    _unit = value;
                    NotifyPropertyChanged("Unit");
                }
            }
        }

        [Column(IsVersion = true)]
        private Binary _version;

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged;

        // Used to notify the page that a data context property changed
        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion

        #region INotifyPropertyChanging Members

        public event PropertyChangingEventHandler PropertyChanging;

        // Used to notify the data context that a data context property is about to change
        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

        #endregion

    }
}

The data context class TreasureContext.cs download.

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TreasureApp1.Data
{
    public class TreasureContext : DataContext
    {
        public static string ConnectionString = "Data Source=isostore:/Treasure.sdf";

        public TreasureContext(string conectionString)
            : base(conectionString)
        {
        }

        public Table Treasure;
    }
}

The CRUD operations CRUD.cs download.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TreasureApp1.Data
{
    class CRUD
    {
        public void Create()
        {
            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                var newObj = new Treasure()
                {
                    Type = "Gold",
                    Unit = "KG",
                    Amount = 50,
                };

                session.Treasure.InsertOnSubmit(newObj);

                session.SubmitChanges();
            }
        }

        public void Read1()
        {
            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                List list = (
                    from o in session.Treasure
                    where o.Type == "Gold"
                    select o
                    ).ToList();
            }
        }

        public void Read2()
        {
            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                List list = 
                    session.Treasure.
                    Where( o => o.Type == "Gold" ).
                    ToList();
            }
        }

        public void Update()
        {
            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                Treasure t = session.Treasure.Where(o => o.Type == "Gold").First();

                t.Amount = 500;

                session.SubmitChanges();
            }
        }

        public void Delete()
        {
            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                Treasure t = session.Treasure.Where(o => o.Type == "Gold").First();

                session.Treasure.DeleteOnSubmit(t);

                session.SubmitChanges();
            }

        }
    }
}

The database creation code for the app initialization (partial code) don't forget to add using Microsoft.Phone.Data.Linq; download

            using (var session = new TreasureContext(TreasureContext.ConnectionString))
            {
                if (!session.DatabaseExists())
                {
                    session.CreateDatabase();
                    var updater = session.CreateDatabaseSchemaUpdater();
                    updater.DatabaseSchemaVersion = 1;
                    updater.Execute();
                }
                else
                {
                    var updater = session.CreateDatabaseSchemaUpdater();
                    if (updater.DatabaseSchemaVersion == 0)
                    {
                        updater.DatabaseSchemaVersion = 1;
                        updater.AddColumn("Unit");
                        updater.Execute();
                    }
                }
            }