Mittwoch, 19. Dezember 2012

Windows Phone 8 Using SQLite

After using a flat file to persists my local data within my WorkTime app and sync them to SkyDrive I asked myself: Why not using a local database to store the data?

So what the database systems that will be supported by Windows Phone 8:

(1) With Windows Phone 7.5 (Mango) Microsoft has introduced an old technology with a new database access layer. SQL CE as database engine and LINQ to SQL to manipulate and define tables using classes.

(2) At the Build Microsoft introduces a new solution for Windows Phone 8: SQLite. That the right decision, because with SQLite you have the chance to share your local application data across several platforms Windows Phone 8, Windows 8, Android and iOS. Microsoft has worked with the SQLite team to provide an easy way to add SQLite to our Windows 8 and Windows Phone 8 projects. The result are two extensions in the  Visual Studio Gallery. One to support Windows 8 projects and an other one to use with Windows Phone 8.

Writing native code for Windows Phone 8?

But what his? These extensions are only the SQLite database engine and written in native code.To use them you have to write native code to interact with the SQLite database engine and perform operations with the database. So what to do: Searching a library which supports a wrapper that you can use C# to use the SQLite database engine.

Peter Torr – who introduces SQLite at the Build - actual posts about the current development process for such an wrapper from Microsoft: http://blogs.msdn.com/b/ptorr/archive/2012/11/27/update-on-sample-code.aspx – so we have to wait for the demoed solution from Microsoft.

So Microsoft gives us a new SQLite database engine but unfortunately no wrapper library to access this by C#.

What’s about csharp-sqlite?

csharp-sqlite is published on Google Code that allows to use the native SQLite engine directly in a C# application. I have worked with this library already on Windows Phone 7 and the library works fine with Windows Phone 8 and can be used as it is.

But if you want to use the same data access layer within Windows Phone 8 and Windows 8 (which should be the main goal for developing apps for these platforms)? The solution: Usage of a additional library sqlite-net which could be found and installed via Nuget:

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="sqlite-net" version="1.0.5" targetFramework="wp80" />
</packages>

Sqlite-net works for my Windows 8 and my Windows Phone 8 test projects and capsulated the data access in a very nice way.

The data class:

public class Contact
    {
        [PrimaryKey]
        public string UnqiqueId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

The CRUD methods:

public async Task CreateContactAsync(Contact contact)
{
    var conn = GetConnection();
    await conn.InsertAsync(contact);
}
 
public async Task UpdateContactAsync(Contact contact)
{
    var conn = GetConnection();
    var existingContact = await conn.FindAsync<Contact>(c => c.UnqiqueId == contact.UnqiqueId);
    if (existingContact != null)
    {
        await conn.UpdateAsync(contact);
    }
}
 
public async Task DeleteContactAsync(Contact contact)
{
    var conn = GetConnection();
    await conn.DeleteAsync(contact);
}
 
private SQLiteAsyncConnection GetConnection()
{
    return new SQLiteAsyncConnection("contacts");
}

Some helper methods:

private async void CreateTableAsync<T>() where T : new()
{
    var conn = GetConnection();
    await conn.CreateTableAsync<T>();
}
 
private bool ExistsTable<T>()
{
    var connStr = new SQLiteConnectionString("contacts", false);
    using (var conn = new SQLiteConnection(connStr.DatabasePath))
    {
        var command = new SQLiteCommand(conn)
        {
            CommandText =
                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='" +
                typeof(T).Name + "'"
        };
        return (command.ExecuteScalar<int>() > 0);
    }
}

 

So in case you are developing a Windows Phone application and you need to use a database, which are the possible solutions?

 

What database solutions for Windows Phone 8?

The first one I mentioned above: still keep using the old SQL CE which works just fine in Windows Phone 8. But then your database could not be used by other apps from different platforms.

A alternative approach is to use SQLIte and the csharp-sqlite library. Together with sqlite-net you can implement the same data access layer for Windows 8 and Windows Phone 8 by changing the underlying SQLite database engine for both platforms.

Kommentare:

  1. Nice one and surely useful for many others, thanks!

    ~ Matthias

    AntwortenLöschen
  2. Thank you Matthias for all the tips using SQLite and the nice ExistsTable method :-)

    AntwortenLöschen
  3. Nice, I've been hoping to find an example using SQLite and code first Entity Framework on the Windows Phone 8... have you had any luck there?

    --Tyler
    http://bleedingedgemachine.blogspot.com/

    AntwortenLöschen
    Antworten
    1. Sorry Tyler, have not tried this combination.

      Löschen
    2. @Tyler: There is no SQLite provider for the Entity Framework, thus you can't use it there (unless you're implementing your own ;-)).

      ~Matthias

      Löschen
  4. Hi,

    Your solution is nice but, I could fine some limitations when i tried to have a property of type List.

    It appears that the attribute on the model properties are ignored.

    For example, I added an IgnoreAttribute (based on the same model that the PrimaryKeyAttribute) and it was not ignored (an exception was thrown).

    If model is moved in DataAccess project, it is working Well ... :-(

    AntwortenLöschen
    Antworten
    1. Hi, have you already trie to move the Attributes from the original SQLite Files to your Model Assembly und uncomment the original Attribute Definition? The should solve your Problem.

      Löschen
  5. Does the windows phone have memory upgrade issue as with Android phone memory upgrade

    AntwortenLöschen
  6. A great quote for the coming new year. Let’s start thinking more of others and less of ourselves. Nice post.

    AntwortenLöschen
  7. Windows Phone Development
    may be the latest smart phone platform, but it has already taken significant market share and poses a big aggressive threat to iPhone, Android, and BlackBerry.

    AntwortenLöschen
  8. Patric Schouler

    Here is a database compatible with Windows Phone 8:
    https://www.kellermansoftware.com/p-49-ninja-winrt-database.aspx

    AntwortenLöschen
  9. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

    AntwortenLöschen
  10. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

    AntwortenLöschen
  11. Your blog is really awesome. Thanks for sharing this blog. If anyone want to get Best PHP Training in Chennai, please visit Fita academy located at Chennai, Velachery.

    AntwortenLöschen
  12. Really Nice Information,Thank You Very Much For Sharing.
    Web Designing Company

    AntwortenLöschen
  13. Hi, I am Martina from Chennai. I am technology freak. I did Android mobile application development course at Fita academy, this is very useful for me to make a bright career in IT industry. So If you looking for best Android Training Institute in Chennai please visit fita academy.

    AntwortenLöschen
  14. I am reading your post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly.
    Oracle Training In Chennai

    AntwortenLöschen
  15. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..
    Oracle Training In Chennai

    AntwortenLöschen
  16. Thanks for sharing such a great information..Its really nice and informative..
    Informatica Training In Chennai

    AntwortenLöschen
  17. This post is really nice and informative. The explanation given is really comprehensive and informative..
    Pega Training In Chennai

    AntwortenLöschen
  18. There are lots of information about latest technology and how to get trained in them, like Hadoop Training in Chennai have spread around the web,but this is a unique one according to me. The strategy you have updated here will make me to get trainedin future technologies Hadoop Training in Chennai By the way you are running a great blog. Thanks for sharing this blogs..
    Hadoop Training In Chennai

    AntwortenLöschen
  19. Whatever we gathered information from the blogs, we should implement that in practically then only we can understand that exact thing clearly, but it’s no need to do it, because you have explained the concepts very well. It was crystal clear, keep sharing..
    QTP Training In Chennai

    AntwortenLöschen
  20. Thanks for sharing this informative blog. I did SAS Certification in Greens Technology at Adyar.
    This is really useful for me to make a bright career..
    SAS Training In Chennai

    AntwortenLöschen
  21. This information is impressive..I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic
    Greens Technologies Training In Chennai

    AntwortenLöschen

  22. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing..
    Greens Technologies Training In Chennai

    AntwortenLöschen
  23. Thanks for sharing amazing information about pega Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with pega. Pega Training in Chennai

    AntwortenLöschen
  24. Who wants to learn Informatica with real-time corporate professionals. We are providing practical oriented best Informatica training institute in Chennai. Informatica Training in chennai

    AntwortenLöschen
  25. QTP is a software Testing Tool which helps in Functional and Regression testing of an application. If you are interested in QTP training, our real time working. QTP Training in Chennai

    AntwortenLöschen
  26. Looking for real-time training institue.Get details now may if share this link visit Oracle Training in chennai

    AntwortenLöschen
  27. Hey, nice site you have here!We provide world-class Oracle certification and placement training course as i wondered Keep up the excellent work experience!Please visit Greens Technologies located at Chennai Adyar Oracle Training in chennai

    AntwortenLöschen
  28. Awesome blog if our training additional way as an SQL and PL/SQL trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog Green Technologies In Chennai

    AntwortenLöschen
  29. Nice site....Please refer this site also Our vision succes!Training are focused on perfect improvement of technical skills for Freshers and working professional. Our Training classes are sure to help the trainee with COMPLETE PRACTICAL TRAINING and Realtime methodologies. Green Technologies In Chennai

    AntwortenLöschen
  30. if share valuable information about hadoop training courses, certification, online resources, and private training for Developers, Administrators, and Data Analysts may visit Hadoop Training in Chennai

    AntwortenLöschen
  31. let's Jump Start Your Career & Get Ahead. Choose sas training method that works for you. This course is designed for professionals looking to move to a role as a business analyst, and students looking to pursue business analytics as a career. SAS Training in Chennai

    AntwortenLöschen
  32. You have stated definite points about the technology that is discussed above. The content published here derives a valuable inspiration to technology geeks like me. Moreover you are running a great blog. Many thanks for sharing this in here.

    Salesforce Training in Chennai
    Salesforce Training
    Salesforce training institutes in chennai

    AntwortenLöschen
  33. It is really very helpful for us and I have gathered some important information from this blog.
    Oracle Training In Chennai

    AntwortenLöschen
  34. Oracle Training in Chennai is one of the best oracle training institute in Chennai which offers complete Oracle training in Chennai by well experienced Oracle Consultants having more than 12+ years of IT experience.

    AntwortenLöschen
  35. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    AntwortenLöschen
  36. Great post and informative blog.it was awesome to read, thanks for sharing this great content to my vision.
    Informatica Training In Chennai

    AntwortenLöschen
  37. A Best Pega Training course that is exclusively designed with Basics through Advanced Pega Concepts.With our Pega Training in Chennai you’ll learn concepts in expert level with practical manner.We help the trainees with guidance for Pega System Architect Certification and also provide guidance to get placed in Pega jobs in the industry.

    AntwortenLöschen
  38. Our HP Quick Test Professional course includes basic to advanced level and our QTP course is designed to get the placement in good MNC companies in chennai as quickly as once you complete the QTP certification training course.

    AntwortenLöschen
  39. Thanks for sharing this nice useful informative post to our knowledge, Actually SAS used in many companies for their day to day business activities it has great scope in future.

    AntwortenLöschen
  40. Greens Technologies Training In Chennai Excellent information with unique content and it is very useful to know about the information based on blogs

    AntwortenLöschen
  41. Greens Technology offer a wide range of training from ASP.NET , SharePoint, Cognos, OBIEE, Websphere, Oracle, DataStage, Datawarehousing, Tibco, SAS, Sap- all Modules, Database Administration, Java and Core Java, C#, VB.NET, SQL Server and Informatica, Bigdata, Unix Shell, Perl scripting, SalesForce , RedHat Linux and Many more.

    AntwortenLöschen
  42. have to learned to lot of information about java Gain the knowledge and hands-on experience you need to successfully design, build and deploy applications with java.
    Java Training in Chennai

    AntwortenLöschen
  43. Looking for real-time training institue.Get details now may if share this link visit
    Spring Training in chennai
    oraclechennai.in:

    AntwortenLöschen
  44. Hybernet is a framework Tool. If you are interested in hybernet training, our real time working.
    Hibernate Training in Chennai,hibernate training in Chennai

    AntwortenLöschen
  45. Awesome blog if our training additional way as an SQL and PL/SQL trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog
    plsql in Chennai
    greenstechnologies.in:

    AntwortenLöschen
  46. Job oriented form_reports training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    forms-reports Training in Chennai

    AntwortenLöschen
  47. if learned in this site.what are the tools using in sql server environment and in warehousing have the solution thank ..Msbi training In Chennai

    AntwortenLöschen
  48. i wondered keep share this sites .if anyone wants realtime training Greens technolog chennai in Adyar visit this blog..performance tuning training In Chennai

    AntwortenLöschen
  49. As your information sybase very nice its more informative and gather new ideas implemnted thanks for sharing this blogsybase training In Chennai

    AntwortenLöschen
  50. i gain the knowledge of Java programs easy to add functionalities play online games, chating with others and industry oriented coaching available from greens technology chennai in Adyar may visit.Core java training In Chennai

    AntwortenLöschen
  51. I have read your blog and I got very useful and knowledgeable information from your blog. It’s really a very nice article Spring training In Chennai

    AntwortenLöschen


  52. hai If you are interested in asp.net training, our real time working.
    asp.net Training in Chennai.
    Asp-Net-training-in-chennai.html

    AntwortenLöschen

  53. Amazing blog if our training additional way as an silverlight training trained as individual, you will be able to understand other applications more quickly and continue to build your skill set which will assist you in getting hi-tech industry jobs as possible in future courese of action..visit this blog
    silverlight-training.html
    greenstechnologies.in:

    AntwortenLöschen


  54. awesome Job oriented sharepoint training in Chennai is offered by our institue is mainly focused on real time and industry oriented. We provide training from beginner’s level to advanced level techniques thought by our experts.
    if you have more details visit this blog.
    SharePoint-training-in-chennai.html

    AntwortenLöschen


  55. if share valuable information about cloud computing training courses, certification, online resources, and private training for Developers, Administrators, and Data Analysts may visit
    Cloud-Computing-course-content.html

    AntwortenLöschen
  56. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog.
    QTP Training in Chennai

    AntwortenLöschen
  57. Excellent information with unique content and it is very useful to know about the information based on blogs.
    Selenium Training in Chennai | QTP Training In Chennai

    AntwortenLöschen
  58. In database computing, Oracle Real Application Clusters (RAC) — an option for the Oracle Database software produced by Oracle Corporation and introduced in 2001 with Oracle9i — provides software for clustering and high availability in Oracle database environments. Oracle Corporation includes RAC with the Standard Edition, provided the nodes are clustered using Oracle Clusterware.
    Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing clustering.

    In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and redo logs located on disk. The instance comprises the collection of Oracle-related memory and operating system processes that run on a computer system.

    Oracle RAC Training in Chennai

    AntwortenLöschen
  59. Wow, brilliant article on dot net training in Chennai that I was searching for. Helps us a lot in referring at my dot net training institutes in Chennai. Thanks a lot. Keep writing more on dot net training Chennai, would love to follow your posts and refer to others in dot net training institute in Chennai.

    AntwortenLöschen
  60. very nice blogs!!! i have to learning for lot of information for this sites...sap abap Training in Chennai Sharing for wonderful information.Thanks for sharing this valuable information to our vision. sap fico Training in Chennai You have posted a trust worthy blog keep sharing. Training in Chennai

    AntwortenLöschen
  61. Wiztech Automation Solutions is the Best Training institute in Chennai,started in the year 2006 and it extended its circle through providing the best Education as per the Global Quality Standards. Hence our Training Center in Chennai was Recognized by IAO and ISO for its inspiring Education Quality Standards. Wiztech Automation Solution, the PLC SCADA Training Academy in Chennai offers both PLC, SCADA, DCS, VFD, Drives, Control Panels, HMI, Pneumatics, Embedded systems, VLSI, IT, Web Designing, AutoCad Training courses in chennai with latest various brands. Wiztech Automation Solutions offers Real Time Training Courses with 100% Placement support in chennai.

    PLC Training in chennai
    SCADA Training in chennai
    PLC Training Institute in chennai
    Embedded System Training in chennai
    VLSI Training in chennai
    Automation Training in chennai
    Industrial Automation Training in chennai
    Process Automation Training in chennai
    DCS Training in chennai
    Inplant Training in chennai
    Placement
    PLC Course in chennai
    Best PLC Training in chennai
    PLC Training in chennai
    Robotics Training in chennai
    Embedded Training in chennai
    IT Training in chennai
    Web designing Training in chennai
    AutoCad Training in chennai

    AntwortenLöschen
  62. Welcome to Wiztech Automation - Embedded System Training in Chennai. We have knowledgeable Team for Embedded Courses handling and we also are after Job Placements offer provide once your Successful Completion of Course. We are Providing on Microcontrollers such as 8051, PIC, AVR, ARM7, ARM9, ARM11 and RTOS. Free Accommodation, Individual Focus, Best Lab facilities, 100% Practical Training and Job opportunities.

    Embedded System Training in chennai
    Embedded System Training Institute in chennai
    Embedded Training in chennai
    Embedded Course in chennai
    Embedded Systems Course in chennai
    Best Embedded System Training Institute in chennai
    Best Embedded System Training Institutes in chennai
    Embedded Training Institute in chennai
    Embedded System Course in chennai
    Best Embedded System Training in chennai
    VLSI Training in chennai

    AntwortenLöschen
  63. great article!!!!!This is very importent information for us.I like all content and information.I have read it.You know more about this please visit again.
    Informatica Training In Chennai
    Hadoop Training In Chennai
    Oracle Training In Chennai
    Pega Training In Chennai

    AntwortenLöschen
  64. MSBI remains for Microsoft Business Intelligence. This apparatus utilizes Visual studio alongside SQL server.do you want to learn a high level language ?then you get job opportunity for all over world.| https://goo.gl/z0JFB7
    https://goo.gl/WzKGr2
    https://goo.gl/pO7P7J
    https://goo.gl/Tshw0J
    https://goo.gl/HZJgxn




    AntwortenLöschen