Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Android to Windows Phone 8: Working with a SQL Database

DZone's Guide to

Android to Windows Phone 8: Working with a SQL Database

· Cloud Zone
Free Resource

Download the Essential Cloud Buyer’s Guide to learn important factors to consider before selecting a provider as well as buying criteria to help you make the best decision for your infrastructure needs, brought to you in partnership with Internap.

Over the next several posts I’ll show you how to work with local data on the Windows Phone 8 platform and compare it to working with data on the Android platform.

There are times when key-value pairs and/or files won’t meet your need for data storage. Specifically, when you’re dealing with structured data that is repeated, such as events on a calendar. For this type of information you’ll want to use a relational store. This relational store is typically a SQL database. Both Android and Windows Phone 8 support using the SQLite relational database engine. This section assumes you have familiarity working with SQLite on Android.

Installing SQLite

The first thing you’ll need to do is install the SQLite for Windows Phone apps. This can be done by downloading the SQLite for Windows Phone package

  1. In Visual Studio, click the  Tools menu, then click  Extensions and Updates

  2. In the tree on the left of the Extensions and Updates window, clickOnline, then click Visual Studio Gallery.
  3. Next, type sqlite in the search box in the upper right hand corner and press Enter.
  4. The  SQLite for Windows Phone package should appear. Click Download.

  5. You will then be prompted to click  Install. Do so.

  6. Once the package is installed you will need to restart Visual Studio
ANDROID HINT
The SQLite for Windows Package package is similar to theandroid.database.sqlite package.

Adding a Reference to SQLite

Now that SQLite is installed you need to add a reference to it from you project.

  1. Right click the  References folder in your Windows Phone project and click  Add Reference…

  2. In the tree on the left hand side of the Reference Manager windows, expand the Windows Phone and the Extensions nodes.
  3. Then select both the  SQLite for Windows Phone and click  OK.

  4. You should now see the extension appear under the References folder for you project.

Getting Helper Classes

The last thing you’ll want to do is obtain some helper classes that make working with SQLite a bit easier. There are a number available for Windows Phone applications. The ones I prefer to use come from thesqlite-net library.

The sqlite-net library can be obtained from NuGet via the following steps

VISUAL STUDIO TIP
NuGet is a free and open source package manager for the .NET Framework.
  1. Right click on the  References folder in you Windows Phone project and click  Manage NuGet Packages…

  2. Expand the Online node in the left hand side of the Window.
  3. Enter sqlite in the search box in the upper right hand side of Window and press Enter.
  4. Select  sqlite-net and click  Install.

  5. Two source files will be added to your project:  SQLite.cs and SQLiteAsync.cs.

  6. If you look in your  Error List you’ll see a number of errors. This is due the fact that  sqlite-net is dependent on  csharp-sqlite which has not been ported to  Windows Phone 8.

  7. To work around this you’ll need to use the  sqlite-net-wp8 native C++project. You’ll first need to go to the  project’s repository on github and download the zip version of the repository.

  8. Right-click the downloaded zip file, click  Properties, click  Unblock, and click  OK.

  9. Unzip the content.
  10. In the Solution Explorer in Visual Studio, right-click the solution and choose  Add, then choose  Existing Project.

  11. In the Add Existing Project dialog, brose to the location where you unzipped the content in step, select the  Sqlite.vcxproj file, and click Open.

  12. You should now see the  Sqlite project in your solution.

  13. You now need to add a reference to the  Sqlite project to your Windows Phone project. Right-click the  References folder of your Windows Phone project and click  Add

  14. In the Reference Manager dialog select Solution from the tree on the left-had side, select Projects.
  15. Check the box next to the  Sqlite project and click  OK.

  16. The last step is to add a compiler directive to the Windows Phone project. Right-click the Windows Phone project in Solution Explorer and click  Properties

  17. Click  Build and add the following to the conditional compilation symbols text box:  ;USE_WP8_NATIVE_SQLITE

  18. Build your solution by pressing  F6. You should now see a Build succeeded message and no errors in the Error List.


    Using SQLite

    In the last part of this section we’ll look at how to perform some basic tasks with SQLite in your Windows Phone application.

    Creating a Table

    The first step you’ll need to take is to create a table that your application will use. For the sake of example, let’s say your application is storing blog posts in a SQLite table. Using the sqlite-net package you obtained in the last section, you can define the table by simply writing a class.

    public class Post
    {
     [PrimaryKey]
     public int Id { get; set; }
     public string Title { get; set; }
     public string Text { get; set; }
    }

    The PrimaryKey attributes come from the sqlite-net package. There are a number of attributes that the package provides that allow you to define the table’s schema.

    Once the table is defined it needs to be created, which can be done like this:

    private async void CreateTable()
    {
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     await conn.CreateTableAsync<Post>();
    }

    The “blog” parameter in the constructor for the SQLiteAsyncConnectionclass simply specifies the path to the SQLite database.

    The Post type specified in the call to the CreateTableAsync method specifies the type of table that should be created. This maps back to thePost class created earlier.

    Android tip
    In Android you would create a table that extends theSQLiteOpenHelper class that contains the following method:
    public void onCreate(SQLiteDatabase db) {  
     db.execSQL("CREATE TABLE Post ( Id INTEGER PRIMARY KEY, Title TEXT, Text TEXT )");
    }
    Inserting a Record

    Now that the table is created, records can be added to it with the following code:

    public async void InsertPost(Post post)
    {
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     await conn.InsertAsync(post);
    }
    ANDROID HINT
    In Android you could insert the record with the following code:
    public void insertPost(SQLiteDatabase db, String title, String text ) { 
     ContentValues values = new ContentValues();
     values.put("Title", title);
     values.put("Text", text);
     long newRowId;
     newRowId = db.insert("Post", null, values);
    }
    Retrieving Records

    Retrieve all records from the table with the following:

    public async Task<List<Post>> GetPosts()
    {
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     var query = conn.Table<Post>();
     var result = await query.ToListAsync();
     return result;
    }
    ANDROID HINT
    In Android you could return a Cursor object containing all records using the following:
    public Cursor getPosts(SQLiteDatabase db){
     String[] projection = {"Id", "Title", "Text" };
     Cursor c = db.query("Post", projection, null, null, null, null, null);
     return c;
    }

    Retrieve a single record from the table with the following:

    public async Task<Post> GetPost(int id)
    { 
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     var query = conn.Table<Post>().Where(x => x.Id == id);
     var result = await query.ToListAsync();
     return result.FirstOrDefault();
    }
    ANDROID HINT
    The following will retrieve a single record in Android:
    public Cursor getPost(SQLiteDatabase db, Integer id){
     String[] projection = {"Id", "Title", "Text" };
     String selection = "Id LIKE ?";
     String[] selelectionArgs = { String.valueOf(id) };
     Cursor c = db.query( "Post", projection, selection, selectionArgs, null, null, null);
     return c;
    }
    Updating a Record

    Updating a record requires the following code:

    public async void UpdatePost(Post post)
    {
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     await conn.UpdateAsync(post);
    }
    ANDROID HINT
    In Android you could update the record with the following code:
    public void updatePost(SQLiteDatabase db, Integer id, String title, String text ) {
     ContentValues values = new ContentValues();
     values.put("Title", title);
     values.put("Text", text);
     String selection = "Id LIKE ?";
     String[] selelectionArgs = { String.valueOf(id) };
     int count = db.update("Post, values, selection, selectionArgs);
    }
    Deleting a Record

    A record can be delete with the following:

    public async void DeletePost(Post post)
    {
     SQLiteAsyncConnection conn = new SQLiteAsyncConnection("blog");
     await conn.DeleteAsync(post);
    }
    ANDROID HINT
    In Android you could delete the record with the following code:
    public void deletePost(SQLiteDatabase db, Integer id ) {  
     String selection = "Id LIKE ?";
     String[] selelectionArgs = { String.valueOf(id) };
     db.delete("Post", selection, selectionArgs);
    }




The Cloud Zone is brought to you in partnership with Internap. Read Bare-Metal Cloud 101 to learn about bare-metal cloud and how it has emerged as a way to complement virtualized services.

Topics:

Published at DZone with permission of Eric Genesky. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}