Welcome!

@BigDataExpo Authors: Dana Gardner, Pat Romanski, Elizabeth White, Shelly Palmer, David H Deans

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
As cloud adoption continues to transform business, today’s global enterprises are challenged with managing a growing amount of information living outside of the data center. The rapid adoption of IoT and increasingly mobile workforce are exacerbating the problem. Ensuring secure data sharing and efficient backup poses capacity and bandwidth considerations as well as policy and regulatory compliance issues.
Why do your mobile transformations need to happen today? Mobile is the strategy that enterprise transformation centers on to drive customer engagement. In his general session at @ThingsExpo, Roger Woods, Director, Mobile Product & Strategy – Adobe Marketing Cloud, covered key IoT and mobile trends that are forcing mobile transformation, key components of a solid mobile strategy and explored how brands are effectively driving mobile change throughout the enterprise.
Data is an unusual currency; it is not restricted by the same transactional limitations as money or people. In fact, the more that you leverage your data across multiple business use cases, the more valuable it becomes to the organization. And the same can be said about the organization’s analytics. In his session at 19th Cloud Expo, Bill Schmarzo, CTO for the Big Data Practice at EMC, will introduce a methodology for capturing, enriching and sharing data (and analytics) across the organizati...
SYS-CON Events announced today that Pulzze Systems will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Pulzze Systems, Inc. provides infrastructure products for the Internet of Things to enable any connected device and system to carry out matched operations without programming. For more information, visit http://www.pulzzesystems.com.
Developing software for the Internet of Things (IoT) comes with its own set of challenges. Security, privacy, and unified standards are a few key issues. In addition, each IoT product is comprised of (at least) three separate application components: the software embedded in the device, the back-end service, and the mobile application for the end user’s controls. Each component is developed by a different team, using different technologies and practices, and deployed to a different stack/target –...
SYS-CON Events announced today Telecom Reseller has been named “Media Sponsor” of SYS-CON's 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Telecom Reseller reports on Unified Communications, UCaaS, BPaaS for enterprise and SMBs. They report extensively on both customer premises based solutions such as IP-PBX as well as cloud based and hosted platforms.
Traditional on-premises data centers have long been the domain of modern data platforms like Apache Hadoop, meaning companies who build their business on public cloud were challenged to run Big Data processing and analytics at scale. But recent advancements in Hadoop performance, security, and most importantly cloud-native integrations, are giving organizations the ability to truly gain value from all their data. In his session at 19th Cloud Expo, David Tishgart, Director of Product Marketing ...
Almost two-thirds of companies either have or soon will have IoT as the backbone of their business in 2016. However, IoT is far more complex than most firms expected. How can you not get trapped in the pitfalls? In his session at @ThingsExpo, Tony Shan, a renowned visionary and thought leader, will introduce a holistic method of IoTification, which is the process of IoTifying the existing technology and business models to adopt and leverage IoT. He will drill down to the components in this fra...
There is growing need for data-driven applications and the need for digital platforms to build these apps. In his session at 19th Cloud Expo, Muddu Sudhakar, VP and GM of Security & IoT at Splunk, will cover different PaaS solutions and Big Data platforms that are available to build applications. In addition, AI and machine learning are creating new requirements that developers need in the building of next-gen apps. The next-generation digital platforms have some of the past platform needs a...
Extreme Computing is the ability to leverage highly performant infrastructure and software to accelerate Big Data, machine learning, HPC, and Enterprise applications. High IOPS Storage, low-latency networks, in-memory databases, GPUs and other parallel accelerators are being used to achieve faster results and help businesses make better decisions. In his session at 18th Cloud Expo, Michael O'Neill, Strategic Business Development at NVIDIA, focused on some of the unique ways extreme computing is...
With so much going on in this space you could be forgiven for thinking you were always working with yesterday’s technologies. So much change, so quickly. What do you do if you have to build a solution from the ground up that is expected to live in the field for at least 5-10 years? This is the challenge we faced when we looked to refresh our existing 10-year-old custom hardware stack to measure the fullness of trash cans and compactors.
The emerging Internet of Everything creates tremendous new opportunities for customer engagement and business model innovation. However, enterprises must overcome a number of critical challenges to bring these new solutions to market. In his session at @ThingsExpo, Michael Martin, CTO/CIO at nfrastructure, outlined these key challenges and recommended approaches for overcoming them to achieve speed and agility in the design, development and implementation of Internet of Everything solutions wi...
Cloud computing is being adopted in one form or another by 94% of enterprises today. Tens of billions of new devices are being connected to The Internet of Things. And Big Data is driving this bus. An exponential increase is expected in the amount of information being processed, managed, analyzed, and acted upon by enterprise IT. This amazing is not part of some distant future - it is happening today. One report shows a 650% increase in enterprise data by 2020. Other estimates are even higher....
Smart Cities are here to stay, but for their promise to be delivered, the data they produce must not be put in new siloes. In his session at @ThingsExpo, Mathias Herberts, Co-founder and CTO of Cityzen Data, will deep dive into best practices that will ensure a successful smart city journey.
Identity is in everything and customers are looking to their providers to ensure the security of their identities, transactions and data. With the increased reliance on cloud-based services, service providers must build security and trust into their offerings, adding value to customers and improving the user experience. Making identity, security and privacy easy for customers provides a unique advantage over the competition.
SYS-CON Events announced today that 910Telecom will exhibit at the 19th International Cloud Expo, which will take place on November 1–3, 2016, at the Santa Clara Convention Center in Santa Clara, CA. Housed in the classic Denver Gas & Electric Building, 910 15th St., 910Telecom is a carrier-neutral telecom hotel located in the heart of Denver. Adjacent to CenturyLink, AT&T, and Denver Main, 910Telecom offers connectivity to all major carriers, Internet service providers, Internet backbones and ...
Qosmos has announced new milestones in the detection of encrypted traffic and in protocol signature coverage. Qosmos latest software can accurately classify traffic encrypted with SSL/TLS (e.g., Google, Facebook, WhatsApp), P2P traffic (e.g., BitTorrent, MuTorrent, Vuze), and Skype, while preserving the privacy of communication content. These new classification techniques mean that traffic optimization, policy enforcement, and user experience are largely unaffected by encryption. In respect wit...
DevOps at Cloud Expo – being held November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA – announces that its Call for Papers is open. Born out of proven success in agile development, cloud computing, and process automation, DevOps is a macro trend you cannot afford to miss. From showcase success stories from early adopters and web-scale businesses, DevOps is expanding to organizations of all sizes, including the world's largest enterprises – and delivering real results. Am...
Internet of @ThingsExpo, taking place November 1-3, 2016, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with 19th Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. The Internet of Things (IoT) is the most profound change in personal and enterprise IT since the creation of the Worldwide Web more than 20 years ago. All major researchers estimate there will be tens of billions devices - comp...
SYS-CON Events announced today that Adobe has been named “Bronze Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Adobe is changing the world though digital experiences. Adobe helps customers develop and deliver high-impact experiences that differentiate brands, build loyalty, and drive revenue across every screen, including smartphones, computers, tablets and TVs. Adobe content solutions are used daily by millions of co...