Create a SQLite Database in Windows Phone 8.1 Class Library

I have a project in it where the SQLite library is in a class library and then I use another class library for the communication between my app and the SQLite library

Class library: SQLite.Library

1. Make a new class library (in my case I named it SQLite.Library)
2. Right click > Manage NuGet packages > sqlite-net (https://www.nuget.org/packages/sqlite-net/1.0.8)

After adding this NuGet package you see that your class library has 2 new classes: SQLite.cs and SQLiteAsync.cs.

Also there is a known problem with SQLite and threading (NullReferenceException when page Loads), you can fix it by adding a lock in the method TableMapping GetMapping in SQLite.cs:

public TableMapping GetMapping(Type type, CreateFlags createFlags = CreateFlags.None){    if (_mappings == null) {        _mappings = new Dictionary<string, TableMapping> ();    }    lock (_mappings)    {        TableMapping map;        if (!_mappings.TryGetValue(type.FullName, out map))        {            map = new TableMapping(type, createFlags);            _mappings[type.FullName] = map;        }        return map;    }   }

Class library: Solutionname.Lib

1. Make a new class library (in my case I named it Solutionname.Lib)
2. Right click > Add Reference > Solution > SQLite.Library (the class library u just made)

After the reference is set u can use the SQLite library in this class library.

In my project I tried to split my code a bit so I started with making a class named DatabaseHelper.cs:

public class DatabaseHelper    {        private String DB_NAME = "DATABASENAME.db";        public SQLiteAsyncConnection Conn { get; set; }       public DatabaseHelper()        {            Conn = new SQLiteAsyncConnection(DB_NAME);            this.InitDb();        }        public async void InitDb()        {            // Create Db if not exist            bool dbExist = await CheckDbAsync();            if (!dbExist)            {                await CreateDatabaseAsync();            }        }        public async Task<bool> CheckDbAsync()        {            bool dbExist = true;            try            {                StorageFile sf = await ApplicationData.Current.LocalFolder.GetFileAsync(DB_NAME);            }            catch (Exception)            {                dbExist = false;            }            return dbExist;        }        private async Task CreateDatabaseAsync()        {            //add tables here            //example: await Conn.CreateTableAsync<DbComment>();        }    }

After the creation of the DatabaseHelper class u can start by making a datasource class for each table in your database.In my case i have a CommentDataSource.cs:

  public class CommentDataSource{    private DatabaseHelper db;    public CommentDataSource(DatabaseHelper databaseHelper)    {        this.db = databaseHelper;    }    public async Task<long> AddComment(String vat, String comment)    {        long id = 0;        DateTime date = DateTime.Now;        DbComment dbc = new DbComment(vat, comment, date);        await db.Conn.InsertAsync(dbc);        DbComment insertDbc = await db.Conn.Table<DbComment>().ElementAtAsync(await db.Conn.Table<DbComment>().CountAsync() - 1);        if (insertDbc != null)        {            id = insertDbc.Id;        }        return id;    }    public async void RemoveComment(long idComment)    {        DbComment comment = await db.Conn.Table<DbComment>().Where(c => c.Id == idComment).FirstOrDefaultAsync();        if (comment != null)        {            await db.Conn.DeleteAsync(comment);        }    }    public async Task<List<DbComment>> FetchAllComments(String vat)    {        return await db.Conn.Table<DbComment>().Where(x => x.VAT == vat).ToListAsync();    }}

As you can see all the datasources that u will add will make use of the same databasehelper.

Use the Solutionname.Lib in your app

1. Right click > Add Reference > Solution > SQLite.Library (the class library u just made)
2. Right click > Add Reference > Solution > Solutionname.Lib

You still need to add a reference to your sqlite lib otherwise you will get errors.

Now you can start using your datasource classes, like u can see here:

private DatabaseHelper db = new DatabaseHelper();private CommentDataSource commentDataSource; public MainPage()        {            this.InitializeComponent();            commentDataSource = new CommentDataSource(db);        }

Now is every method of the CommentsDataSource available in your app.

Hope this help u a bit!

try this

 public async Task<bool> CheckDbAsync(string dbName)    {        bool dbExist = true;        try        {            StorageFile sf = await ApplicationData.Current.LocalFolder.GetFileAsync(dbName);        }        catch (Exception)        {            dbExist = false;        }        return dbExist;    }    public async Task CreateDatabaseAsync(string dbName)    {        SQLiteAsyncConnection con = new SQLiteAsyncConnection(dbName);        await con.CreateTableAsync<ChatClass>();       // await con.CreateTableAsync<RecentChatManageClass>();        await con.CreateTableAsync<PurchasedGift>();        // await con.CreateTableAsync<AttandanceManagement>();    }   

and use like this

 DataBaseOperation databaseoperation = new DataBaseOperation();            bool existDb = await databaseoperation.CheckDbAsync("sample.db"); // Check Database created or not             if (!existDb)            {                await databaseoperation.CreateDatabaseAsync("sample.db"); // Create Database             }