Post
Ask Question

SQLite CRUD Operation In WinJS: Windows Apps

As we know, WinJS applications are built using HTML/JS/CSS. And for a complete application, we will need async operations like sqlite database operations, web-service calls, and other native XML read-write, and File read-write which cannot be done directly in WinJS applications.

So we have to use Windows Runtime Component to access Native C# methods as discussed in my previous article: WinJS application with Windows Runtime Component to access native C# code. 

For SQLite operation, we have to use Class Library.

Step 1

Firstly create a WinJS project.

Step 2

We need to do following things as discussed in my previous blog. Please have a look at my previous article.

  • Add a Windows Runtime Component project and name it ‘WinRuntimes’
  • Delete default class ‘Class1.cs’
  • Add a new class and name it ‘Service’
  • Add WinRuntimes reference in WinJS project
  • Build the project

Step 3

After you are done with adding Runtime Component, we have to add Class Library for SQLite.

Click File, Add, New Project, Visual C#, Windows, Class Library (Windows 8.1). Name it SQLiteLibrary and click OK.



Step 4

Now we have to add a reference ’SQLite for Windows Runtime’ and ‘sqlite-net’ in Class Library. For this, Right Click on Reference and Manage Nuget Packages



From the Nuget package manager, we have to install two things:

  • sqlite-net
  • SQLite for Windows Runtime (Alternatively, you can download this from here.Install it and restart Visual Studio)




Step 5

After downloading and installing sqlite libraries, we have to add reference in Class Library:



Step 6

We need to change the Build Configuration to x86.

Go to Build > Configuration Menu and change Any CPU to x86 inside Active solution platform.



Step 7

In WinRuntimes project, right click on References and Add project SQLiteLibray as reference.



Step 8

Now in the WinJS (SQLiteOperations) project add reference of WinRuntimes. Right Click on References and add WinRuntimes as reference.



Step 9

Add a script.js file and reference it in default.html:

Now in default.html, lets add following things:

  • Button to create a database
  • Button to insert into database
  • Two input fields to insert data into database
  • Button to read from database
  • Button to update
  • Button to delete

Complete HTML code:

  1. <!DOCTYPE html>  
  2. <html>  
  3.     <head>  
  4.         <meta charset="utf-8" />  
  5.         <title>SQLiteOperations</title>  
  6.         <!-- WinJS references -->  
  7.         <link href="//Microsoft.WinJS.2.0/css/ui-dark.css" rel="stylesheet" />  
  8.         <script src="//Microsoft.WinJS.2.0/js/base.js"></script>  
  9.         <script src="//Microsoft.WinJS.2.0/js/ui.js"></script>  
  10.         <!-- SQLiteOperations references -->  
  11.         <link href="/css/default.css" rel="stylesheet" />  
  12.         <script src="/js/default.js"></script>  
  13.         <script src="js/script.js"></script>  
  14.     </head>  
  15.     <body>  
  16.         <p>SQLite Operations</p>  
  17.         <button onclick="createDatabase()">Create Database</button>  
  18.         <br />  
  19.         <br />  
  20.         <input placeholder="Country Name" id="name" />  
  21.         <input placeholder="Capital City" id="city"/>  
  22.         <button onclick="insertRecords()">Insert into Database</button>  
  23.         <br />  
  24.         <br />  
  25.         <button onclick="readDatabase()">Read from Database</button>  
  26.         <br />  
  27.         <div id="content"></div>  
  28.         <br />  
  29.         <br />  
  30.         <button onclick="updateRecord()">Update records</button>  
  31.         <br />  
  32.         <br />  
  33.         <button onclick="deleteRecord()">Delete records from Database</button>  
  34.         <br />  
  35.         <br />  
  36.     </body>  
  37. </html>  

Step 10

We add a Database class in WinRuntimes project and we write all our CRUD operations code where we have respective IAsyncOperation for Create, Read, Update, Insert and Delete operations.

Creating Database

  1. [Table("Countries")]  
  2. public sealed class Country  
  3. {  
  4. [PrimaryKey, AutoIncrement]  
  5. public int id { get; set; }  
  6.   
  7. public string Name { get; set; }  
  8.   
  9. public string CapitalCity { get; set; }  
  10.   
  11. }  
  12. public IAsyncOperation<string> CreateDatabase()  
  13. {  
  14. return CreateDatabaseHelper().AsAsyncOperation();  
  15. }  
  16. private async Task<string> CreateDatabaseHelper()  
  17. {  
  18. try  
  19. {  
  20. SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbName);  
  21. await connection.CreateTableAsync<Country>();  
  22. return "success";  
  23. }  
  24. catch (Exception ex)  
  25. {  
  26. return "fail";  
  27. }  
  28. }  
Inserting into Database
  1. public IAsyncOperation<string> InsertRecords(string countryName, string capitalCity)  
  2. {  
  3. return InsertRecordsHelper(countryName, capitalCity).AsAsyncOperation();  
  4. }  
  5. private async Task<string> InsertRecordsHelper(string countryName, string capitalCity)  
  6. {  
  7. try  
  8. {  
  9. SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbName);  
  10. var Country = new Country()  
  11. {  
  12. Name = countryName,  
  13. CapitalCity = capitalCity  
  14. };  
  15. await connection.InsertAsync(Country);  
  16. return "success";  
  17. }  
  18. catch (Exception ex)  
  19. {  
  20. return "fail";  
  21. }  
  22. }  
Reading from Database
  1. public IAsyncOperation<string> ReadDatabase()  
  2. {  
  3. return ReadDatabaseHelper().AsAsyncOperation();  
  4. }  
  5. private async Task<string> ReadDatabaseHelper()  
  6. {  
  7. try  
  8. {  
  9. string countryName = string.Empty;  
  10. string capitalCity = string.Empty;  
  11. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  12. var result = await dbCon.QueryAsync<Country>("Select * from Countries LIMIT 1");  
  13. foreach (var item in result)  
  14. {  
  15. countryName = item.Name;  
  16. capitalCity = item.CapitalCity;  
  17. }  
  18.   
  19. return countryName + "," + capitalCity;  
  20. }  
  21. catch (Exception ex)  
  22. {  
  23. return "fail";  
  24. }  
  25. }  
Updating records
  1. public IAsyncOperation<string> UpdateDatabase()  
  2. {  
  3. return UpdateDatabaseHelper().AsAsyncOperation();  
  4. }  
  5. private async Task<string> UpdateDatabaseHelper()  
  6. {  
  7. try  
  8. {  
  9. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  10. var Country = await dbCon.Table<Country>().Where(country => country.Name.StartsWith("France")).FirstOrDefaultAsync();  
  11.   
  12. if (Country != null)  
  13. {  
  14. Country.CapitalCity = "Paris";  
  15. await dbCon.UpdateAsync(Country);  
  16. }  
  17. return "success";  
  18. }  
  19. catch (Exception ex)  
  20. {  
  21. return "fail";  
  22. }  
  23. }  
Complete Code Snippet for Database class
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using Windows.Foundation;  
  7. using SQLite;  
  8. using Windows.Storage;  
  9.   
  10. namespace WinRuntimes  
  11. {  
  12. [Table("Countries")]  
  13. public sealed class Country  
  14. {  
  15. [PrimaryKey, AutoIncrement]  
  16. public int id { get; set; }  
  17.   
  18. public string Name { get; set; }  
  19.   
  20. public string CapitalCity { get; set; }  
  21.   
  22. }  
  23. public sealed class Database  
  24. {  
  25. internal static string dbName = "CountryDb.sqlite";  
  26.   
  27. public Database()  
  28. {  
  29. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  30. }  
  31.  
  32. #region IAsyncOperations  
  33. public IAsyncOperation<string> CreateDatabase()  
  34. {  
  35. return CreateDatabaseHelper().AsAsyncOperation();  
  36. }  
  37.   
  38. public IAsyncOperation<string> InsertRecords(string countryName, string capitalCity)  
  39. {  
  40. return InsertRecordsHelper(countryName, capitalCity).AsAsyncOperation();  
  41. }  
  42.   
  43. public IAsyncOperation<string> ReadDatabase()  
  44. {  
  45. return ReadDatabaseHelper().AsAsyncOperation();  
  46. }  
  47.   
  48. public IAsyncOperation<string> UpdateDatabase()  
  49. {  
  50. return UpdateDatabaseHelper().AsAsyncOperation();  
  51. }  
  52.   
  53. public IAsyncOperation<string> DeleteRecord()  
  54. {  
  55. return DeleteRecordHelper().AsAsyncOperation();  
  56. }  
  57. #endregion  
  58.  
  59. #region Helpers  
  60. private async Task<string> CreateDatabaseHelper()  
  61. {  
  62. try  
  63. {  
  64. SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbName);  
  65. await connection.CreateTableAsync<Country>();  
  66. return "success";  
  67. }  
  68. catch (Exception ex)  
  69. {  
  70. return "fail";  
  71. }  
  72. }  
  73.   
  74. private async Task<string> InsertRecordsHelper(string countryName, string capitalCity)  
  75. {  
  76. try  
  77. {  
  78. SQLiteAsyncConnection connection = new SQLiteAsyncConnection(dbName);  
  79. var Country = new Country()  
  80. {  
  81. Name = countryName,  
  82. CapitalCity = capitalCity  
  83. };  
  84. await connection.InsertAsync(Country);  
  85. return "success";  
  86. }  
  87. catch (Exception ex)  
  88. {  
  89. return "fail";  
  90. }  
  91. }  
  92.   
  93. private async Task<string> ReadDatabaseHelper()  
  94. {  
  95. try  
  96. {  
  97. string countryName = string.Empty;  
  98. string capitalCity = string.Empty;  
  99. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  100. var result = await dbCon.QueryAsync<Country>("Select * from Countries LIMIT 1");  
  101. foreach (var item in result)  
  102. {  
  103. countryName = item.Name;  
  104. capitalCity = item.CapitalCity;  
  105. }  
  106.   
  107. return countryName + "," + capitalCity;  
  108. }  
  109. catch (Exception ex)  
  110. {  
  111. return "fail";  
  112. }  
  113. }  
  114.   
  115. private async Task<string> UpdateDatabaseHelper()  
  116. {  
  117. try  
  118. {  
  119. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  120. var Country = await dbCon.Table<Country>().Where(country => country.Name.StartsWith("France")).FirstOrDefaultAsync();  
  121.   
  122. if (Country != null)  
  123. {  
  124. Country.CapitalCity = "Paris";  
  125. await dbCon.UpdateAsync(Country);  
  126. }  
  127. return "success";  
  128. }  
  129. catch (Exception ex)  
  130. {  
  131. return "fail";  
  132. }  
  133. }  
  134.   
  135. private async Task<string> DeleteRecordHelper()  
  136. {  
  137. try  
  138. {  
  139. SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(dbName);  
  140. var Country = await dbCon.Table<Country>().Where(country => country.Name.StartsWith("France")).FirstOrDefaultAsync();  
  141.   
  142. if (Country != null)  
  143. {  
  144. await dbCon.DeleteAsync(Country);  
  145. }  
  146. return "success";  
  147. }  
  148. catch (Exception ex)  
  149. {  
  150. return "fail";  
  151. }  
  152. }  
  153. #endregion  
  154. }  
  155. }  
Step 11

In script.js, we create a database object of Database class and call the respective function for Create, Read, Update, Delete. Here we use the then function to achieve asynchronous programming.

Complete code snippet for script.js is:
  1. var database = new WinRuntimes.Database();  
  2.   
  3. function createDatabase() {  
  4. database.createDatabase().then(function (response) {  
  5. if (response == 'success') {  
  6. var msg = new Windows.UI.Popups.MessageDialog("Successfully created!", "Success");  
  7. msg.showAsync();  
  8. }  
  9. else {  
  10. var msg = new Windows.UI.Popups.MessageDialog("Database creation failed", "Failed");  
  11. msg.showAsync();  
  12. }  
  13. });  
  14. }  
  15.   
  16. function insertRecords() {  
  17. var countryName = document.getElementById("name").value;  
  18. var cityName = document.getElementById("city").value;  
  19. database.insertRecords(countryName, cityName).then(function(response) {  
  20. if (response == 'success') {  
  21. var msg = new Windows.UI.Popups.MessageDialog("Successfully inserted!", "Success");  
  22. msg.showAsync();  
  23. }  
  24. else {  
  25. var msg = new Windows.UI.Popups.MessageDialog("Insert failed", "Failed");  
  26. msg.showAsync();  
  27. }  
  28. });  
  29. }  
  30.   
  31. function readDatabase() {  
  32. database.readDatabase().then(function (response) {  
  33. document.getElementById("content").innerHTML = response;  
  34. });  
  35. }  
  36.   
  37. function updateRecord() {  
  38. database.updateDatabase().then(function (response) {  
  39. if (response == 'success') {  
  40. var msg = new Windows.UI.Popups.MessageDialog("Update success!", "Success");  
  41. msg.showAsync();  
  42. }  
  43. else {  
  44. var msg = new Windows.UI.Popups.MessageDialog("Update failed", "Failed");  
  45. msg.showAsync();  
  46. }  
  47. });  
  48. }  
  49.   
  50. function deleteRecord() {  
  51. database.deleteRecord().then(function (response) {  
  52. if (response == 'success') {  
  53. var msg = new Windows.UI.Popups.MessageDialog("Delete success!", "Success");  
  54. msg.showAsync();  
  55. }  
  56. else {  
  57. var msg = new Windows.UI.Popups.MessageDialog("Delete failed", "Failed");  
  58. msg.showAsync();  
  59. }  
  60. })  
  61. }  
Step 12

Run the application. Your application is ready to use SQLite database as a local storage. This is how we interact with the Class Library and Windows Runtime Component with the WinJS project which is built under HTML/JS/CSS. 


Similar Articles