Click here to close now.

Welcome!

Big Data Journal Authors: Adrian Bridgwater, Esmeralda Swartz, Liz McMillan, Carmen Gonzalez, Elizabeth White

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
The list of ‘new paradigm’ technologies that now surrounds us appears to be at an all time high. From cloud computing and Big Data analytics to Bring Your Own Device (BYOD) and the Internet of Things (IoT), today we have to deal with what the industry likes to call ‘paradigm shifts’ at every level of IT. This is disruption; of course, we understand that – change is almost always disruptive.
SYS-CON Events announced today that Cisco, the worldwide leader in IT that transforms how people connect, communicate and collaborate, has been named “Gold 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. Cisco makes amazing things happen by connecting the unconnected. Cisco has shaped the future of the Internet by becoming the worldwide leader in transforming how people connect, communicate and collaborat...
Docker is an excellent platform for organizations interested in running microservices. It offers portability and consistency between development and production environments, quick provisioning times, and a simple way to isolate services. In his session at DevOps Summit at 16th Cloud Expo, Shannon Williams, co-founder of Rancher Labs, will walk through these and other benefits of using Docker to run microservices, and provide an overview of RancherOS, a minimalist distribution of Linux designed...
Businesses are looking to empower employees and departments to do more, go faster, and streamline their processes. For all workers – but mobile workers especially – utilizing the cloud to reconnect documents and improve processes without destructing existing workflows can have a dramatic impact on productivity. In his session at 16th Cloud Expo, Mark Grilli, vice president of Acrobat Solutions marketing at Adobe Systems Incorporated, will outline new ways that the cloud is changing the way peo...
SYS-CON Events announced today that Aria Systems, the leading innovator in recurring revenue, has been named “Bronze Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 9–11, 2015, at the Javits Center in New York, NY. Proven by the world’s most demanding enterprises, including AAA NCNU, Constant Contact, Falck, Hootsuite, Pitney Bowes, Telekom Denmark, and VMware, Aria helps enterprises grow their recurring revenue businesses. With Aria’s end-to-end active monetization platform, g...
SYS-CON Events announced today that Vitria Technology, Inc. will exhibit at SYS-CON’s @ThingsExpo, which will take place on June 9-11, 2015, at the Javits Center in New York City, NY. Vitria will showcase the company’s new IoT Analytics Platform through live demonstrations at booth #330. Vitria’s IoT Analytics Platform, fully integrated and powered by an operational intelligence engine, enables customers to rapidly build and operationalize advanced analytics to deliver timely business outcomes ...
After making a doctor’s appointment via your mobile device, you receive a calendar invite. The day of your appointment, you get a reminder with the doctor’s location and contact information. As you enter the doctor’s exam room, the medical team is equipped with the latest tablet containing your medical history – he or she makes real time updates to your medical file. At the end of your visit, you receive an electronic prescription to your preferred pharmacy and can schedule your next appointment...
SYS-CON Events announced today that Akana, formerly SOA Software, has been named “Bronze Sponsor” of SYS-CON's 16th International Cloud Expo® New York, which will take place June 9-11, 2015, at the Javits Center in New York City, NY. Akana’s comprehensive suite of API Management, API Security, Integrated SOA Governance, and Cloud Integration solutions helps businesses accelerate digital transformation by securely extending their reach across multiple channels – mobile, cloud and Internet of Thi...
Are your applications getting in the way of your business strategy? It’s time to rethink your IT approach. In his session at 16th Cloud Expo, Madhukar Kumar, Vice President, Product Management at Liaison Technologies, will discuss a new data-centric approach to IT that allows your data, not applications, to inform business strategy. By moving away from an application-centric IT model where data integration and analysis are subservient to the constraints of applications, your organization will b...
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...
SYS-CON Events announced today that Solgenia 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. Solgenia is the global market leader in Cloud Collaboration and Cloud Infrastructure software solutions. Designed to “Bridge the Gap” between Personal and Professional S...
SYS-CON Events announced today that Liaison Technologies, a leading provider of data management and integration cloud services and solutions, has been named "Silver 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, NY. Liaison Technologies is a recognized market leader in providing cloud-enabled data integration and data management solutions to break down complex information barriers, enabling enterprises to make sm...
Cloud is not a commodity. And no matter what you call it, computing doesn’t come out of the sky. It comes from physical hardware inside brick and mortar facilities connected by hundreds of miles of networking cable. And no two clouds are built the same way. SoftLayer gives you the highest performing cloud infrastructure available. One platform that takes data centers around the world that are full of the widest range of cloud computing options, and then integrates and automates everything. J...
The 3rd International Internet of @ThingsExpo, co-located with the 16th International Cloud Expo - to be held June 9-11, 2015, at the Javits Center in New York City, NY - announces that its Call for Papers is open. The Internet of Things (IoT) is the biggest idea since the creation of the Worldwide Web more than 20 years ago.
SYS-CON Events announced today that CommVault 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. A singular vision – a belief in a better way to address current and future data management needs – guides CommVault in the development of Singular In...
SYS-CON Media announced today that 9 out of 10 " most read" DevOps articles are published by @DevOpsSummit Blog. Launched in October 2014, @DevOpsSummit Blog offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. The widespread success of cloud computing is driving the DevOps revolution in enterprise IT. Now as never before, development teams must communicate and collaborate in a dy...
In the midst of the widespread popularity and adoption of cloud computing, it seems like everything is being offered “as a Service” these days: Infrastructure? Check. Platform? You bet. Software? Absolutely. Toaster? It’s only a matter of time. With service providers positioning vastly differing offerings under a generic “cloud” umbrella, it’s all too easy to get confused about what’s actually being offered. In his session at 16th Cloud Expo, Kevin Hazard, Director of Digital Content for SoftL...
The world's leading Cloud event, Cloud Expo has launched Microservices Journal on the SYS-CON.com portal, featuring over 19,000 original articles, news stories, features, and blog entries. DevOps Journal is focused on this critical enterprise IT topic in the world of cloud computing. Microservices Journal offers top articles, news stories, and blog posts from the world's well-known experts and guarantees better exposure for its authors than any other publication. Follow new article posts on T...
SYS-CON Events announced today that Site24x7, the cloud infrastructure monitoring service, 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. Site24x7 is a cloud infrastructure monitoring service that helps monitor the uptime and performance of websites, online applications, servers, mobile websites and custom APIs. The monitoring is done from 50+ locations across the world and from various wireless carr...
Countless business models have spawned from the IaaS industry. Resell Web hosting, blogs, public cloud, and on and on. With the overwhelming amount of tools available to us, it's sometimes easy to overlook that many of them are just new skins of resources we've had for a long time. In his General Session at 16th Cloud Expo, Phil Jackson, Lead Developer Advocate at SoftLayer, will break down what we've got to work with and discuss the benefits and pitfalls to discover how we can best use them t...