Creating an Application with a Local Database for Windows Phone Mango

i was working lately on a new app , todo list ( i know , nothing innovative here -_- ) but then i dropped the work till mango is out,for the very good reason that with mango i can have a local database to store and manage my application’s structured data! clap! clap!

one of the many new features coming with windows phone mango is the possibility of having local databases- microsoft sql server compact edition databases(sql ce)- for your applications.

the database resides in the application’s isolated storage as a file.

in case you are not familiar with the isolated storage,i highly recommend you to read the ” all about wp7 isolated storage” articles on the ” windowsphone geek ” website.

in order to store and retrieve data in the local database, your windows phone application has to use linq to sql.

” linq to sql provides an object-oriented approach to working with data and comprises an object model and a runtime.the linq to sql object model is made up primarily by the system.data.linq.datacontext object, which acts as a proxy for the local database. the linq to sql runtime is responsible for bridging the world of objects (the datacontext object) with the world of data (the local database). this relationship is summarized in the following image.” msdn

one more thing to explain is the data context . data context is the object model that represents the database, each object is a set of entities using the ” plain old clr object ” (poco) with attributes.

• building the application

my todo application looks like this :

as you can see, my main page is a panorama containing :

* hot list item : where you can find the tasks to be done soon

* lists: tasks will be sorted , tasks related to work, family, etc…

the other page is a simple windows phone page that allows you to add a new task.

in this post we will be working with this user interface.by now you should know how to create a new windows phone 7.1 application and how to add pages, so all i have to do is to show you the xaml code :

    <grid x:name=”contentpanel” grid.row=”1″ margin=”12,0″>
                <textblock height=”50″ horizontalalignment=”left” margin=”10,25,0,0″ name=”textblock1″ text=”subject:” verticalalignment=”top” width=”117″ />
                <textbox height=”66″ horizontalalignment=”left” margin=”105,9,0,0″ name=”textbox1″ text=”" verticalalignment=”top” width=”342″ />
                <textblock height=”43″ horizontalalignment=”left” margin=”10,97,0,0″ name=”textblock2″ text=”category:” verticalalignment=”top” width=”103″ />
                <canvas horizontalalignment=”left” verticalalignment=”top” width=”345″ height=”66″ margin=”111,80,0,0″>
                    <toolkit:contextmenuservice.contextmenu>
                        <toolkit:contextmenu>
                            <toolkit:menuitem header=”pin to start menu” click=”menuitem_click” tag=”start_menu” />
                            <toolkit:menuitem header=”delete” click=”menuitem_click” tag=”delete” />
                            <toolkit:menuitem header=”share” click=”menuitem_click” tag=”share” />
                        </toolkit:contextmenu>
                    </toolkit:contextmenuservice.contextmenu>
                    <rectangle fill=”#fff4f4f5″ height=”47″ stroke=”black” width=”319″ canvas.left=”6″ canvas.top=”6″ />
                    <textblock name=”cat” textwrapping=”wrap” text=” “ foreground=”black” canvas.left=”71″ canvas.top=”27″/>
                </canvas>
                <textblock height=”50″ horizontalalignment=”left” margin=”10,179,0,0″ name=”textblock3″ text=”priority:” verticalalignment=”top” width=”95″ />
                <radiobutton content=”low” height=”76″ horizontalalignment=”left” margin=”140,156,0,0″ name=”radiobutton1″ verticalalignment=”top” width=”134″ />
                <radiobutton content=”high” height=”73″ horizontalalignment=”right” margin=”0,156,33,0″ name=”radiobutton2″ verticalalignment=”top” width=”126″ />
                <toolkit:datepicker header=”start date:” margin=”0,234,0,270″ />
                <toolkit:datepicker header=”due date:” margin=”0,331,0,180″ />
                <checkbox content=”completed” height=”76″ horizontalalignment=”left” margin=”6,411,0,0″ name=”checkbox1″ verticalalignment=”top” width=”190″ />
                <toolkit:datepicker header=”date completed:” margin=”2,493,9,21″ />
            </grid>

now that the user interface is ready, we will build the data context.

first thing to do is to add the reference system.data.linq to be able to work with linq to sql.

then add a new class, i’ll call it task.

    [table]
        public class task : inotifypropertychanged, inotifypropertychanging
        {
            private string _subject;
            [column(isprimarykey = true, isdbgenerated = true, dbtype = "int not null identity", canbenull = false, autosync = autosync.oninsert)]
            public string subject
            {
                get
                {
                    return _subject;
                }
                set
                {
                    if (_subject != value)
                    {
                        notifypropertychanging(“subject”);
                        _subject = value;
                        notifypropertychanged(“subject”);
                    }
                }
            }
            private string _category;
            public string category
            {
                get
                {
                    return _category;
                }
                set
                {
                    if (_category != value)
                    {
                        notifypropertychanging(“category”);
                        _category = value;
                        notifypropertychanged(“category”);
                    }
                }
            }
            private bool _completed;
            [column]
            public bool completed
            {
                get
                {
                    return _completed;
                }
                set
                {
                    if (_completed != value)
                    {
                        notifypropertychanging(“completed”);
                        _completed = value;
                        notifypropertychanged(“completed”);
                    }
                }
            }
            private bool _priority;
            [column]
            public bool priority
            {
                get
                {
                    return _priority;
                }
                set
                {
                    if (_priority != value)
                    {
                        notifypropertychanging(“priority”);
                        _priority = value;
                        notifypropertychanged(“priority”);
                    }
                }
            }
            private datetime _startdate;
            [column]
            public datetime startdate
            {
                get
                {
                    return _startdate;
                }
                set
                {
                    if (_startdate != value)
                    {
                        notifypropertychanging(“startdate”);
                        _startdate = value;
                        notifypropertychanged(“startdate”);
                    }
                }
            }
            private datetime _duedate;
            [column]
            public datetime duetdate
            {
                get
                {
                    return _duedate;
                }
                set
                {
                    if (_duedate != value)
                    {
                        notifypropertychanging(“duetdate”);
                        _duedate = value;
                        notifypropertychanged(“duetdate”);
                    }
                }
            }
            private datetime _datecompleted;
            [column]
            public datetime datecompleted
            {
                get
                {
                    return _datecompleted;
                }
                set
                {
                    if (_datecompleted != value)
                    {
                        notifypropertychanging(“datecompleted”);
                        _datecompleted = value;
                        notifypropertychanged(“datecompleted”);
                    }
                }
            }
            public event propertychangedeventhandler propertychanged;
            private void notifypropertychanged(string propertyname)
            {
                if (propertychanged != null)
                {
                    propertychanged(this, new propertychangedeventargs(propertyname));
                }
            }
            public event propertychangingeventhandler propertychanging;
            private void notifypropertychanging(string propertyname)
            {
                if (propertychanging != null)
                {
                    propertychanging(this, new propertychangingeventargs(propertyname));
                }
            }
        }

add another class called taskdatacontext that derives from datacontext:

    namespace task
    {
        public class taskdatacontext: datacontext
        {
            public taskdatacontext(string connectionstring): base(connectionstring)    {    }
            public table<task> tasks { get { return this.gettable<task>(); } }
        }
    }

notice that we have to check if the database exists , if not we need to create it .

in the mainpage.xaml.cs add the following code:

    using (taskdatacontext context = new taskdatacontext(connectionstring))
                {
                    if (!context.databaseexists())
                    {
                    context.createdatabase();
                    }
                }

to sum up, in order to use local database, we need to specify the data context then create the database if it doesn’t exist.