play with windows phone local database

When database matters!

Fiyaz Bin HasanFormer MSP (AUST) , NerdCats

When to choose what?

Persist data in my WP 8.1 (Silverlight) app

Local database (SQLCE) saves the dayStore relational dataSits in app’s local folderUse LINQ TO SQL for

CREATE

RETRIEVE

UPDATE

DELETE

What about Windows Phone Apps under WinRT framework?

Third party database support!

SQLite for Windows Phone 8.1World’s most popular database Used in other OS tooFast & ReliableSupport for multiple tables, triggers, views, indicesFollow ACID rules

What you need to know when using database in WP 8.1 (Silverlight)

Your database is in your code

DataContext

Act as a proxy, object that represents the database.

Table objects, represents a table in the database.Made up of entitiesEntity is a “plain old CLR object” (POCO) with attributes.

Behind the scene!

BlogId BlogName

Value Value

Blogs Table

PostId PostName

Value Value

Posts Table

BlogPostDatacontext

Play with data in OOP manners

LINQ TO SQL

ORM capabilities

Use LINQ to speak with the database

Translate LINQ to Transact-SQL and query the database

Took the results and translate it again to LINQ

How it really works!

DataContext and Local database relation

Data Context Local DatabaseLINQ TO SQL

RUNTIME

Define who goes where!

LINQ to SQL mapping attributes

Specify database-specific features such as tables, columns, primary keys, and indexes.DatabaseAttribute (Name)

TableAttribute (Name)

ColumnAttribute (AutoSync, IsForeignKey, DbType, IsVersion)

AssociationAttribute (IsForeignKey, DeleteRule)

DataAttribute (Storage)

Teach LINQ TO SQL what to do

PostId (Pk) PostContent

Value (NOT NULL)(AUTOINCREMENT)(INT)

Value

Post Table

Association (1 to many)

PostId (Pk) PostContent BlogId

Value (NOT NULL)(AUTOINCREMENT)(INT)

Value value

BlogId (Pk) BlogName

Value (NOT NULL)(AUTOINCREMENT)(INT)

Value

Foreign Key

Post table

Blog table 1

M

Association dissection!

Foreign Key

Navigational Property(Blog Reference)

Post list reference

Its time for CRUD

CRUD with LINQ

SELECT/RETRIEVE

INSERT/CREATE

UPDATE

DELETE

After All_blogPostDataContext.SubmitChanges();

DEMO

What you need to know when using database in WP 8.1 (WinRT)