Click here to close now.



Welcome!

@BigDataExpo Authors: Scott Allen, Elizabeth White, Tom Kelly, XebiaLabs Blog, Talend Inc.

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
Most people haven’t heard the word, “gamification,” even though they probably, and perhaps unwittingly, participate in it every day. Gamification is “the process of adding games or game-like elements to something (as a task) so as to encourage participation.” Further, gamification is about bringing game mechanics – rules, constructs, processes, and methods – into the real world in an effort to engage people. In his session at @ThingsExpo, Robert Endo, owner and engagement manager of Intrepid D...
SYS-CON Events announced today that Pythian, a global IT services company specializing in helping companies adopt disruptive technologies to optimize revenue-generating systems, has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. Founded in 1997, Pythian is a global IT services company that helps companies compete by adopting disruptive technologies such as cloud, Big Data, advanced analytics, and DevO...
With microservices, SOA and distributed architectures becoming more popular, it is becoming increasingly harder to keep track of where time is spent in a distributed application when trying to diagnose performance problems. Distributed tracing systems attempt to address this problem by following application requests across service boundaries, persisting metadata along the way that provide context for fine-grained performance monitoring.
With an estimated 50 billion devices connected to the Internet by 2020, several industries will begin to expand their capabilities for retaining end point data at the edge to better utilize the range of data types and sheer volume of M2M data generated by the Internet of Things. In his session at @ThingsExpo, Don DeLoach, CEO and President of Infobright, will discuss the infrastructures businesses will need to implement to handle this explosion of data by providing specific use cases for filte...
SYS-CON Events announced today that Fusion, a leading provider of cloud services, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Fusion, a leading provider of integrated cloud solutions to small, medium and large businesses, is the industry's single source for the cloud. Fusion's advanced, proprietary cloud service platform enables the integration of leading edge solutions in the cloud, including clou...
SYS-CON Events announced today that Commvault, a global leader in enterprise data protection and information management, has been named “Bronze Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY, and the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Commvault is a leading provider of data protection and information management...
SYS-CON Events announced today that Alert Logic, Inc., the leading provider of Security-as-a-Service solutions for the cloud, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Alert Logic, Inc., provides Security-as-a-Service for on-premises, cloud, and hybrid infrastructures, delivering deep security insight and continuous protection for customers at a lower cost than traditional security solutions. Ful...
The cloud promises new levels of agility and cost-savings for Big Data, data warehousing and analytics. But it’s challenging to understand all the options – from IaaS and PaaS to newer services like HaaS (Hadoop as a Service) and BDaaS (Big Data as a Service). In her session at @BigDataExpo at @ThingsExpo, Hannah Smalltree, a director at Cazena, will provide an educational overview of emerging “as-a-service” options for Big Data in the cloud. This is critical background for IT and data profes...
SYS-CON Events announced today that VAI, a leading ERP software provider, will exhibit at SYS-CON's 18th International Cloud Expo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. VAI (Vormittag Associates, Inc.) is a leading independent mid-market ERP software developer renowned for its flexible solutions and ability to automate critical business functions for the distribution, manufacturing, specialty retail and service sectors. An IBM Premier Business Part...
SYS-CON Events announced today that Catchpoint Systems, Inc., a provider of innovative web and infrastructure monitoring solutions, has been named “Silver Sponsor” of SYS-CON's DevOps Summit at 18th Cloud Expo New York, which will take place June 7-9, 2016, at the Javits Center in New York City, NY. Catchpoint is a leading Digital Performance Analytics company that provides unparalleled insight into customer-critical services to help consistently deliver an amazing customer experience. Designed...
It's easy to assume that your app will run on a fast and reliable network. The reality for your app's users, though, is often a slow, unreliable network with spotty coverage. What happens when the network doesn't work, or when the device is in airplane mode? You get unhappy, frustrated users. An offline-first app is an app that works, without error, when there is no network connection.
Fortunately, meaningful and tangible business cases for IoT are plentiful in a broad array of industries and vertical markets. These range from simple warranty cost reduction for capital intensive assets, to minimizing downtime for vital business tools, to creating feedback loops improving product design, to improving and enhancing enterprise customer experiences. All of these business cases, which will be briefly explored in this session, hinge on cost effectively extracting relevant data from ...
As enterprises work to take advantage of Big Data technologies, they frequently become distracted by product-level decisions. In most new Big Data builds this approach is completely counter-productive: it presupposes tools that may not be a fit for development teams, forces IT to take on the burden of evaluating and maintaining unfamiliar technology, and represents a major up-front expense. In his session at @BigDataExpo at @ThingsExpo, Andrew Warfield, CTO and Co-Founder of Coho Data, will dis...
With the Apple Watch making its way onto wrists all over the world, it’s only a matter of time before it becomes a staple in the workplace. In fact, Forrester reported that 68 percent of technology and business decision-makers characterize wearables as a top priority for 2015. Recognizing their business value early on, FinancialForce.com was the first to bring ERP to wearables, helping streamline communication across front and back office functions. In his session at @ThingsExpo, Kevin Roberts...
Cognitive Computing is becoming the foundation for a new generation of solutions that have the potential to transform business. Unlike traditional approaches to building solutions, a cognitive computing approach allows the data to help determine the way applications are designed. This contrasts with conventional software development that begins with defining logic based on the current way a business operates. In her session at 18th Cloud Expo, Judith S. Hurwitz, President and CEO of Hurwitz & ...
SYS-CON Events announced today that Interoute, owner-operator of one of Europe's largest networks and a global cloud services platform, has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2015 at the Javits Center in New York, New York. Interoute is the owner-operator of one of Europe's largest networks and a global cloud services platform which encompasses 12 data centers, 14 virtual data centers and 31 colocation centers, with connections to 195 ad...
We all know that data growth is exploding and storage budgets are shrinking. Instead of showing you charts on how much data there is, in his session at 18th Cloud Expo, Scott Cleland, Senior Director of Product Marketing for HGST, will show how to capture all of your data in one place. After you have your data under control, you can then analyze it in one place, saving time and resources. See how HGST has used these solutions to gain more value out of the information we have – and capitalize o...
Data-as-a-Service is the complete package for the transformation of raw data into meaningful data assets and the delivery of those data assets. In her session at 18th Cloud Expo, Lakshmi Randall, an industry expert, analyst and strategist, will address: What is DaaS (Data-as-a-Service)? Challenges addressed by DaaS Vendors that are enabling DaaS Architecture options for DaaS
Eighty percent of a data scientist’s time is spent gathering and cleaning up data, and 80% of all data is unstructured and almost never analyzed. Cognitive computing, in combination with Big Data, is changing the equation by creating data reservoirs and using natural language processing to enable analysis of unstructured data sources. This is impacting every aspect of the analytics profession from how data is mined (and by whom) to how it is delivered. This is not some futuristic vision: it's ha...
The IoT's basic concept of collecting data from as many sources possible to drive better decision making, create process innovation and realize additional revenue has been in use at large enterprises with deep pockets for decades. So what has changed? In his session at @ThingsExpo, Prasanna Sivaramakrishnan, Solutions Architect at Red Hat, discussed the impact commodity hardware, ubiquitous connectivity, and innovations in open source software are having on the connected universe of people, thi...