Use existing sqlite database in Windows phone 8.1





5.00/5 (2 votes)
In this article we will learn how to use existing sqlite database in WP 8.1 apps.
Requirements
- Windows OS 8.1 pro or above addition
- Windows Phone 8.1 sdk
- Hyper-V enabled(for emulator)
Table of Contents
- Create sqlite file
- Install SQLite extension for VS 2013
- Setup sqlite in application
- Perform CRUD operations
Description
In many cases we need to use existing database in our application, so here we will see how to use existing sqlite database in WP 8.1 app.
1. Create sqlite file
For creating sqlite database we will use Sqlite Manager, a extension of Mozilla.
Create a table with the name "PersonalDetails". It contains following columns.
- Id - INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
- Name - VARCHAR
- Address - VARCHAR
- EmailId - VARCHAR
- PhoneNo - VARCHAR
2. Install SQLite extension for VS 2013
Now install extension of Sqlite for Windows phone 8.1. You can download it in Visual Studio directly by using option "Extension and Updates". In alternate way you can download frome here.
After installing it, you will find it in installed extensions. It will require only once on fresh setup.
3. Setup sqlite in application
Now take a new blank Windows Phone 8.1 app and install sqlite in it from Nuget. You can install directly by package manager console as well.
After installing it, you will find two new cs file in your project for sqlite.
Now, Add reference of sqlite in app.
When you build the app you will get compilation error.
Right click on Solution and go to Configuration Manager and change its platform from Any CPU to ARM.
4. Perform CRUD operations
Now Create model class to hold this table in object. This model class will be replica of table. Class name must be same as of table in Sqlite db.
//
public class PersonalDetails
{
[SQLite.AutoIncrement, SQLite.PrimaryKey]
public int Id{get;set;}
public string Name { get; set; }
public string Address { get; set; }
public string EmailId { get; set; }
public string PhoneNo { get; set; }
}
//
SQLite.AutoIncrement and SQLite.PrimaryKey are the attributes which shows that property is primary key and Auto incremented.
Paste sqlite file into the project and set its Build Action to Content and Copy to Output Directory to Copy if newer.
Now create CRUD functions.
//
public class DataProvider : IDisposable
{
private bool disposed = false;
private string _dbName = "Employees.sqlite";
public DataProvider()
{
}
~DataProvider()
{
Dispose(false);
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
}
disposed = true;
}
}
/// <summary>
/// Get Employee List
/// </summary>
/// <returns></returns>
public List<PersonalDetails> GetEmployeeList()
{
List<PersonalDetails> employeeList = new List<PersonalDetails>();
try
{
// Initialize the database if necessary
using (var db = new SQLite.SQLiteConnection(_dbName))
{
employeeList = db.Query<PersonalDetails>("select * from PersonalDetails").ToList();
}//using
}//try
catch (Exception ex)
{
}//try
return employeeList;
}//GetEmployeeList
public bool AddEmployee(PersonalDetails employeeInfo)
{
bool result = false;
try
{
using (var db = new SQLite.SQLiteConnection(_dbName))
{
db.RunInTransaction(() =>
{
//Insert new employee
db.Insert(employeeInfo);
});
}//using
result = true;
}//try
catch (Exception ex)
{
}//catch
return result;
}
public bool UpdateEmployee(int id, PersonalDetails employeeInfo)
{
bool result = false;
try
{
using (var db = new SQLite.SQLiteConnection(_dbName))
{
var employee = db.Query<PersonalDetails>("select * from PersonalDetails where Id=" + id).FirstOrDefault();
if (employee != null)
{
//update Name and address
employee.Address = employeeInfo.Address;
employee.Name = employeeInfo.Name;
db.RunInTransaction(() =>
{
db.Update(employee);
});
}
}//using
result = true;
}//try
catch (Exception ex)
{
}//catch
return result;
}
public bool DeleteEmployee(int id)
{
bool result = false;
try
{
using (var db = new SQLite.SQLiteConnection(_dbName))
{
var employee = db.Query<PersonalDetails>("select * from PersonalDetails where Id=" + id).FirstOrDefault();
if (employee != null)
{
//update Name and address
db.RunInTransaction(() =>
{
db.Delete(employee);
});
}
}//using
result = true;
}//try
catch (Exception ex)
{
}//catch
return result;
}
}
//
Use these functions in your app pages.
DataProvider provider = new DataProvider();
//Add employee
provider.AddEmployee(new PersonalDetails
{
Address = "154, Newyork",
EmailId = "ron@ymail.com",
Name = "Ron",
PhoneNo = "082-445434-333"
});
//Get Employee
var data = provider.GetEmployeeList();
//Update Employee
provider.UpdateEmployee(1,new PersonalDetails
{
Address = "187, Newyork",
Name = "Ron Jan",
});
//Delete Employee
provider.DeleteEmployee(1);