SQLite Operations In Windows Phone

After successfully including SQLite in our project its time to play with it. Firstly, we need to make foundation so that we can do our operations. Add a class named ContactsTable.cs in your project and write the following code in it.

class ContactsTable
{
    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int id
    {
        get;
        set;
    }
    public string Name
    {
        get;
        set;
    }
    public string Contact
    {
        get;
        set;
    }
    public ContactsTable()
    {}
    public ContactsTable(string name, string ph_number)
    {
        Name = name;
        Contact = ph_number;
    }
}

This is  the code for our table where our table name is contactstable.

Now go to MainPage.cs and add database path “DB_PATH” and make connection with SQLite database.

public sealed partial class MainPage : Page
{
   public static string DB_PATH = Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "CRUD.sqlite"));
   private SQLiteConnection dbConn;
}

You provided a path to your database and create object of SQLiteConnection class.

Write the following code in OnNavigatedto().

protected override void OnNavigatedTo(NavigationEventArgs e)
{
   dbConn = new SQLiteConnection(DB_PATH);
   dbConn.CreateTable<ContactsTable>();
}

The above code makes connection with database and creates a table of name ContactsTable. Now you are good to go foundation is ready.

Let us start with Insert. Here is the code to insert data in SQLite.

Insert Operation

 

private void InsertBtn_Click(object sender, RoutedEventArgs e)
{
    ContactsTable contatsobj = new ContactsTable()
    {
        Name = nameTB.Text,
            Contact = contacttb.Text.ToString()
    };
    var query = dbConn.Insert(contatsobj); // Insert query
    //Check weather the data has been inserted or not
    if (query == null)
    {
        MessageDialog message = new MessageDialog("Failed to Insert data");
        message.ShowAsync();
    }
    else
    {
        MessageDialog message = new MessageDialog("Data has been inserted successfully");
        message.ShowAsync();
    }
}

The data has been added successfully. Now its time to read the inserted data from the database. For this purpose we need this piece of code.

Read Operation

private void read_Click(object sender, RoutedEventArgs e)
{
    var read = dbConn.Query < ContactsTable > ("select * from contactstable where name='" + nameTB.Text + "'").FirstOrDefault();
    if (read == null)
    {
        MessageDialog diag = new MessageDialog("The name you entered does not exists");
        diag.ShowAsync();
    }
    else
    {
        MessageDialog diag = new MessageDialog("ID: " + read.id + "\n" + " Name: " + read.Name);
        diag.ShowAsync();
    }
}

Code above will read data and show you the result in Message Dialog, if result does not match it gives a message of wrong and if name matches it will show you the ID and Name in MessageDialog.

Update

Here is code for update.

private void updatebtn_Click(object sender, RoutedEventArgs e)
{
    string name = nameTB.Text;
    string setName = contacttb.Text;
    var query = dbConn.Query < ContactsTable > ("update contactstable set Name='" + setName + "' where Name='" + name + "'");
    if (query != null)
    {
        MessageDialog message = new MessageDialog("Contact has been updated successfully");
        message.ShowAsync();
    }
    else
    {
        MessageDialog message = new MessageDialog("Contact failed to update");
        message.ShowAsync();
    }
}

The code above is very simple and easy to understand. If you are aware of SQL Update query you will easily understand this.

Delete

The following code is little bit different as I used Delete() method of SQLiteConnection class. I just got the name to delete from the database, checked it using query and then passed that query in Delete() method. Here is the code.

private void deletebtn_Click(object sender, RoutedEventArgs e)
{
    string name_to_del = nameTB.Text;
    try
    {
        var query = dbConn.Query < ContactsTable > ("select* from contactstable where Name='" + name_to_del + "'").FirstOrDefault();
        if (query != null)
        {
            dbConn.Delete(query);
            MessageDialog message = new MessageDialog("Deleted successfully");
            message.ShowAsync();
        }
    }
    catch (Exception ee)
    {
        MessageDialog message = new MessageDialog("" + ee);
        message.ShowAsync();
    }
}

Here comes the end I tried my best to make CRUD operations simple so that programmers of all levels especially beginners can learn quickly and easily. Your suggestions will be helpful for me to write in future and for the readers too. Happy coding!