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;
}
}
public List<PersonalDetails> GetEmployeeList()
{
List<PersonalDetails> employeeList = new List<PersonalDetails>();
try
{
using (var db = new SQLite.SQLiteConnection(_dbName))
{
employeeList = db.Query<PersonalDetails>("select * from PersonalDetails").ToList();
} } catch (Exception ex)
{
} return employeeList;
}
public bool AddEmployee(PersonalDetails employeeInfo)
{
bool result = false;
try
{
using (var db = new SQLite.SQLiteConnection(_dbName))
{
db.RunInTransaction(() =>
{
db.Insert(employeeInfo);
});
} result = true;
} catch (Exception ex)
{
} 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)
{
employee.Address = employeeInfo.Address;
employee.Name = employeeInfo.Name;
db.RunInTransaction(() =>
{
db.Update(employee);
});
}
} result = true;
} catch (Exception ex)
{
} 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)
{
db.RunInTransaction(() =>
{
db.Delete(employee);
});
}
} result = true;
} catch (Exception ex)
{
} return result;
}
}
Use these functions in your app pages.
DataProvider provider = new DataProvider();
provider.AddEmployee(new PersonalDetails
{
Address = "154, Newyork",
EmailId = "ron@ymail.com",
Name = "Ron",
PhoneNo = "082-445434-333"
});
var data = provider.GetEmployeeList();
provider.UpdateEmployee(1,new PersonalDetails
{
Address = "187, Newyork",
Name = "Ron Jan",
});
provider.DeleteEmployee(1);