Welcome!

@BigDataExpo Authors: Pat Romanski, Yeshim Deniz, Elizabeth White, Liz McMillan, Ed Featherston

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 BMC Software has been named "Siver 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. BMC is a global leader in innovative software solutions that help businesses transform into digital enterprises for the ultimate competitive advantage. BMC Digital Enterprise Management is a set of innovative IT solutions designed to make digital business fast, seamless, and optimized from mainframe to mo...
SoftLayer operates a global cloud infrastructure platform built for Internet scale. With a global footprint of data centers and network points of presence, SoftLayer provides infrastructure as a service to leading-edge customers ranging from Web startups to global enterprises. SoftLayer's modular architecture, full-featured API, and sophisticated automation provide unparalleled performance and control. Its flexible unified platform seamlessly spans physical and virtual devices linked via a world...
The IoT is changing the way enterprises conduct business. In his session at @ThingsExpo, Eric Hoffman, Vice President at EastBanc Technologies, discuss how businesses can gain an edge over competitors by empowering consumers to take control through IoT. We'll cite examples such as a Washington, D.C.-based sports club that leveraged IoT and the cloud to develop a comprehensive booking system. He'll also highlight how IoT can revitalize and restore outdated business models, making them profitable...
As organizations shift towards IT-as-a-service models, the need for managing and protecting data residing across physical, virtual, and now cloud environments grows with it. Commvault can ensure protection, access and E-Discovery of your data – whether in a private cloud, a Service Provider delivered public cloud, or a hybrid cloud environment – across the heterogeneous enterprise. In his general session at 18th Cloud Expo, Randy De Meno, Chief Technologist - Windows Products and Microsoft Part...
IoT generates lots of temporal data. But how do you unlock its value? How do you coordinate the diverse moving parts that must come together when developing your IoT product? What are the key challenges addressed by Data as a Service? How does cloud computing underlie and connect the notions of Digital and DevOps What is the impact of the API economy? What is the business imperative for Cognitive Computing? Get all these questions and hundreds more like them answered at the 18th Cloud Expo...
The cloud market growth today is largely in public clouds. While there is a lot of spend in IT departments in virtualization, these aren’t yet translating into a true “cloud” experience within the enterprise. What is stopping the growth of the “private cloud” market? In his general session at 18th Cloud Expo, Nara Rajagopalan, CEO of Accelerite, will explore the challenges in deploying, managing, and getting adoption for a private cloud within an enterprise. What are the key differences betwee...
SYS-CON Events announced today that EastBanc Technologies 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. EastBanc Technologies has been working at the frontier of technology since 1999. Today, the firm provides full-lifecycle software development delivering flexible technology solutions that seamlessly integrate with existing systems – whether on premise or cloud. EastBanc Technologies partners with p...
In his session at 18th Cloud Expo, Andrew Cole, Director of Solutions Engineering at Peak 10, will discuss how the newest technology advances are reducing the cost and complexity of traditional business continuity and disaster recovery solutions. Attendees will: Learn why having a full disaster recovery strategy is more important now than ever before Explore the key drivers of a successful disaster recovery solution Achieve measurable operational and business value from a disaster recovery ...
SYS-CON Events announced today Object Management Group® has been named “Media 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.
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 Hanu Software 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. Leveraging best-in-class people, processes, and technologies, Hanu provides high-quality, high-value software development and business process outsourcing services to independent software vendors (ISVs) and enterprises.
In his session at 18th Cloud Expo, Bruce Swann, Senior Product Marketing Manager at Adobe, will discuss how the Adobe Marketing Cloud can help marketers embrace opportunities for personalized, relevant and real-time customer engagement across offline (direct mail, point of sale, call center) and digital (email, website, SMS, mobile apps, social networks, connected objects). Bruce Swann has more than 15 years of experience working with digital marketing disciplines like web analytics, social med...
SYS-CON Events announced today that Tintri Inc., a leading producer of VM-aware storage (VAS) for virtualization and cloud environments, will exhibit at the 18th International CloudExpo®, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York, 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.
The cloud era has reached the stage where it is no longer a question of whether a company should migrate, but when. Enterprises have embraced the outsourcing of where their various applications are stored and who manages them, saving significant investment along the way. Plus, the cloud has become a defining competitive edge. Companies that fail to successfully adapt risk failure. The media, of course, continues to extol the virtues of the cloud, including how easy it is to get there. Migrating...
The initial debate is over: Any enterprise with a serious commitment to IT is migrating to the cloud. But things are not so simple. There is a complex mix of on-premises, colocated, and public-cloud deployments. In this power panel at 18th Cloud Expo, moderated by Conference Chair Roger Strukhoff, panelists will look at the present state of cloud from the C-level view, and how great companies and rock star executives can use cloud computing to meet their most ambitious and disruptive business ...
SYS-CON Events announced today that ContentMX, the marketing technology and services company with a singular mission to increase engagement and drive more conversations for enterprise, channel and SMB technology marketers, has been named “Sponsor & Exhibitor Lounge Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York City, New York. “CloudExpo is a great opportunity to start a conversation with new prospects, but what happens after the...
The IoTs will challenge the status quo of how IT and development organizations operate. Or will it? Certainly the fog layer of IoT requires special insights about data ontology, security and transactional integrity. But the developmental challenges are the same: People, Process and Platform. In his session at @ThingsExpo, Craig Sproule, CEO of Metavine, will demonstrate how to move beyond today's coding paradigm and share the must-have mindsets for removing complexity from the development proc...
SYS-CON Events announced today that AppNeta, the leader in performance insight for business-critical web applications, will exhibit and present at SYS-CON's @DevOpsSummit at Cloud Expo New York, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. AppNeta is the only application performance monitoring (APM) company to provide solutions for all applications – applications you develop internally, business-critical SaaS applications you use and the networks that deli...
Companies can harness IoT and predictive analytics to sustain business continuity; predict and manage site performance during emergencies; minimize expensive reactive maintenance; and forecast equipment and maintenance budgets and expenditures. Providing cost-effective, uninterrupted service is challenging, particularly for organizations with geographically dispersed operations.
SYS-CON Events announced today that Isomorphic Software will exhibit at SYS-CON's [email protected] at Cloud Expo New York, which will take place on June 7-9, 2016, at the Javits Center in New York City, NY. Isomorphic Software provides the SmartClient HTML5/AJAX platform, the most advanced technology for building rich, high-productivity enterprise web applications for any device. SmartClient couples the industry’s broadest, deepest UI component set with a java server framework to deliver an end-...