Click here to close now.

Welcome!

Big Data Journal Authors: Roger Strukhoff, Jnan Dash, Elizabeth White, Kevin Benedict, Louis Evans

Blog Feed Post

Android to Windows Phone 8: Working with a SQL Database

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, click Online, 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 the android.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 the sqlite-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 SQLiteAsyncConnection class 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 the Post class created earlier.

Android tip
In Android you would create a table that extends the SQLiteOpenHelper 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);
}

Additional Resources

Read the original blog entry...

More Stories By Adam Grocholski

Hey there! My name is Adam Grocholski, and I'm a Technical Evangelist at Microsoft where I spend time focusing on Windows, Windows Phone, and Windows Azure. I live in the frozen tundra of Minnesota and run thinkfirstcodelater.com. You can also follow me on twitter at @codel8r.

@BigDataExpo Stories
SYS-CON Events announced today that kintone has been named “Bronze Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY, and the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. kintone promotes cloud-based workgroup productivity, transparency and profitability with a seamless collaboration space, build your own business applic...
of cloud, colocation, managed services and disaster recovery solutions, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. TierPoint, LLC, is a leading national provider of information technology and data center services, including cloud, colocation, disaster recovery and managed IT services, with corporate headquarters in St. Louis, MO. TierPoint was formed through the strategic combination of some of t...
SYS-CON Events announced today that Soha will exhibit at SYS-CON's DevOps Summit New York, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Soha delivers enterprise-grade application security, on any device, as agile as the cloud. This turnkey, cloud-based service enables customers to solve secure application access and delivery challenges that traditional or virtualized network solutions cannot solve because they are too expensive, inflexible and operational...
SYS-CON Events announced today that Vicom Computer Services, Inc., a provider of technology and service solutions, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. They are located at booth #427. Vicom Computer Services, Inc. is a progressive leader in the technology industry for over 30 years. Headquartered in the NY Metropolitan area. Vicom provides products and services based on today’s requirements...
The 17th International Cloud Expo has announced that its Call for Papers is open. 17th International Cloud Expo, to be held November 3-5, 2015, at the Santa Clara Convention Center in Santa Clara, CA, brings together Cloud Computing, APM, APIs, Microservices, Security, Big Data, Internet of Things, DevOps and WebRTC to one location. With cloud computing driving a higher percentage of enterprise IT budgets every year, it becomes increasingly important to plant your flag in this fast-expanding bu...
What exactly is a cognitive application? In her session at 16th Cloud Expo, Ashley Hathaway, Product Manager at IBM Watson, will look at the services being offered by the IBM Watson Developer Cloud and what that means for developers and Big Data. She'll explore how IBM Watson and its partnerships will continue to grow and help define what it means to be a cognitive service, as well as take a look at the offerings on Bluemix. She will also check out how Watson and the Alchemy API team up to off...
SYS-CON Events announced today that Column Technologies, a global technology solutions company, will exhibit at SYS-CON's DevOps Summit 2015 New York, which will take place June 9-11, 2015, at the Javits Center in New York City, NY. Established in 1998, Column Technologies is a leader in application performance and infrastructure management for commercial and federal markets. The company is headquartered in the United States, with a diverse and talented team of more than 350 employees around th...
SYS-CON Events announced today that ProfitBricks, the provider of painless cloud infrastructure, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY., and the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. ProfitBricks is the IaaS provider that offers a painless cloud experience for all IT users, with no learning curve. ...
SYS-CON Media announced today that John Treadway’s blog has exceeded 475,000 page views. John Treadway, Vice President at Cloud Technology Partners, has surpassed 475,000 page views on the SYS-CON family of online magazines, which includes Cloud Computing Journal, Internet of Things Journal, Big Data Journal, Microservices Journal, and several others. His blog home page at SYS-CON can be found at JohnTreadway.SYS-CON.com.
Buzzword alert: Microservices and IoT at a DevOps conference? What could possibly go wrong? Join this panel of experts as they peel away the buzz and discuss the important architectural principles behind implementing IoT solutions for the enterprise. As remote IoT devices and sensors become increasingly intelligent, they become part of our distributed cloud environment, and we must architect and code accordingly. At the very least, you’ll have no problem filling in your buzzword bingo cards.
"Jelastic is focused on getting people to the cloud sooner, easier, without having to go to new APIs or different standards, to give them the full benefit of the cloud right away," explained John Derrick, CEO of Jelastic, in this SYS-CON.tv interview at the 14th International Cloud Expo®, held June 10-12, 2014, at the Javits Center in New York City
SYS-CON Events announced today that Dyn, the worldwide leader in Internet Performance, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Dyn is a cloud-based Internet Performance company. Dyn helps companies monitor, control, and optimize online infrastructure for an exceptional end-user experience. Through a world-class network and unrivaled, objective intelligence into Internet conditions, Dyn ensures...
The only place to be June 9-11 is Cloud Expo & @ThingsExpo 2015 East at the Javits Center in New York City. Join us there as delegates from all over the world come to listen to and engage with speakers & sponsors from the leading Cloud Computing, IoT & Big Data companies. Cloud Expo & @ThingsExpo are the leading events covering the booming market of Cloud Computing, IoT & Big Data for the enterprise. Speakers from all over the world will be hand-picked for their ability to explore the economic...
SYS-CON Events announced today that O'Reilly Media has been named “Media Sponsor” of SYS-CON's 16th International Cloud Expo®, which will take place on June 9–11, 2015, at the Javits Center in New York City, NY. O'Reilly Media spreads the knowledge of innovators through its books, online services, magazines, and conferences. Since 1978, O'Reilly Media has been a chronicler and catalyst of cutting-edge development, homing in on the technology trends that really matter and spurring their adoption...
Learn about the current state of security breaches and what it is costing businesses. One of my recent conversations with a Cloud security expert at a conference opened up doors to some new information for me personally. Our conversation was around the current status of the Cloud Industry, the Security Challenges and how we can make the Cloud more secure and so on. Did you know that security breaches have huge financial implications such as at an average:
One of the hottest areas in cloud right now is DRaaS and related offerings. In his session at 16th Cloud Expo, Dale Levesque, Disaster Recovery Product Manager with Windstream's Cloud and Data Center Marketing team, will discuss the benefits of the cloud model, which far outweigh the traditional approach, and how enterprises need to ensure that their needs are properly being met.
SYS-CON Events announced today the IoT Bootcamp – Jumpstart Your IoT Strategy, being held June 9–10, 2015, in conjunction with 16th Cloud Expo and Internet of @ThingsExpo at the Javits Center in New York City. This is your chance to jumpstart your IoT strategy. Combined with real-world scenarios and use cases, the IoT Bootcamp is not just based on presentations but includes hands-on demos and walkthroughs. We will introduce you to a variety of Do-It-Yourself IoT platforms including Arduino, Ras...
SYS-CON Events announced today that Emcien will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Emcien’s vision is to let anyone use data to know the future. Emcien has built an automated, predictive analysis product that improves the lives of real people. Emcien allows people to automate their data analysis so they can build a better future.
With the arrival of the Big Data revolution, a data professional is expected to master a broad spectrum of complex domains including data processing, mathematics, programming languages, machine learning techniques, and business knowledge. While this mastery is undoubtedly important, this narrow focus on tool usage has divorced many from the imagination required to solve real-world problems. As the demand for analysis increases, the data science community must transform from tool experts to "data...
SYS-CON Events announced today that QTS Realty Trust, one of the nation’s largest and fastest-growing providers of data center facilities and cloud services and a leader in security and compliance, will exhibit at SYS-CON's 16th International Cloud Expo®, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. QTS Realty Trust, Inc. (NYSE: QTS) is a leading national provider of data center solutions and fully managed services, and a leader in security and compliance...