Use existing sqlite database in Windows phone 8.1

• Download source - 673.1 KB

Requirements

1. Windows OS 8.1 pro or above addition
2. Windows Phone 8.1 sdk
3. Hyper-V enabled(for emulator)

Table of Contents

1. Create sqlite file
2. Install SQLite extension for VS 2013
3. Setup sqlite in application
4. 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.

1. Id - INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL
2. Name - VARCHAR
3. Address - VARCHAR
4. EmailId - VARCHAR
5. 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);