July 1, 2014 at 07:47

SQLite is now for mobile applications in C # for any platform

    image

    More recently, a new version of the library has been released, which will be useful to C # developers who are developing or planning to develop cross-platform mobile applications.

    SQLitePCL can be used to implement a local database in applications for Windows, Windows Store, Windows Phone, Android ( Xamarin ) and iOS ( Xamarin ). It is free and its code is open to all comers.

    I tested it in action, and I want to share it with you.

    Create a project


    For experiments, we need a universal application project for Windows and Phone.

    image

    And Xamarin projects for Android and iOS applications.

    image

    We connect the necessary libraries


    For each project, install and add the SQLite-net PCL package to References . It will automatically install SQLitePCL.raw . That’s all we need to use.

    image

    We create a database and implement CRUD operations


    We will implement the logic of working with data in the Shared project that we already have, common to all applications . But you can do this in the new Portable Class Library created for this purpose .

    So, add two files to the Shared project. One of them, MessageItem.cs , will contain the structure of the object that we will store in the database, and the second DataBase.cs will implement interaction with the SQLite database.

    image

    Code MessageItem.cs
    using SQLite;
using System;
using System.Collections.Generic;
using System.Text;
namespace CrossplatformApp
{
    public class MessageItem
    {
        [PrimaryKey, AutoIncrement]
        public Guid Id { get; set; }
        public string Text { get; set; }
        public string User { get; set; }
        public string Created { get; set; }
    }
}


    Code DataBase.cs
    using System;
using System.Collections.Generic;
using System.Text;
using SQLitePCL;
using System.Threading.Tasks;
using System.Linq;
using System.IO;
using SQLite;
namespace CrossplatformApp
{
    public class Database
    {
        private const string _db = "CrossplatformApp.db";
        private SQLiteConnection _connection = null;
        public Database(string path)
        {   
            string _dbpath = Path.Combine(path, _db);
            _connection = new SQLiteConnection(_dbpath);
            _connection.CreateTable();
        }       
        ~Database()
        {
            if(_connection!=null)
                _connection.Close();
        }
        public int AddNewItem(MessageItem item)
        {
            var result = _connection.Insert(item);
            return result;
        }
        public int UpdateItem(MessageItem item)
        {
            var result = _connection.Update(item);
            return result;
        }
        public int DeleteItem(MessageItem item)
        {
            var result = _connection.Delete(item);
            return result;
        }
        public IQueryable GetAllItems()
        {
            var result = _connection.Table().OrderByDescending(t => t.Created);
            return result.AsQueryable();
        }
    }
}


    Use for W8, WP, iOS, Android


    Let's see how our database will work on Windows Phone.

    Let's go to the Windows Phone application project and modify the main page.

    1. Replace all XAML code in MainPage.xaml to make the application simple interface and display data from the database to the user.

    image

    MainPage.xaml


    2. We will write the application code. Create a database, fill it with data and display it on the screen.

    image

    MainPage.xaml.cs
    Database db = new Database(ApplicationData.Current.LocalFolder.Path);
db.AddNewItem(new MessageItem { Id = Guid.NewGuid(), Text = "bla-bla", User = "@MissUFO", Created = DateTime.UtcNow.ToString() });
var messages = db.GetAllItems();
SampleListView.DataContext = messages;


    3. Run the project and look at the result. The database was created successfully, data is written and read.

    image

    For Android and iOS, the procedure looks exactly the same. You can connect the Shared project as References and use our classes.

    image

    Conclusion


    The latest version of SQLitePCL has just been released, so if something goes wrong, see the documentation and write your problems and questions to codeplex to its authors.

    useful links


    Download SQLitePCL
    Read SQLite Documentation
    Download Visual Studio 2013 Update 2

    Sitelinks


    Tags:

    Also popular now: