Create a SQLite Database in Windows Phone 8.1 Class Library

Question

I have a Windows Phone 8.1 Class Library that I want to later add as a reference to a Windows Phone 8.1 App project.

This ClassLibrary should be responsible for creating and managing its own database. I tried creating a new SQLiteConnection in my ClassLibrary, but it throws the following error: A first chance exception of type 'System.InvalidOperationException' occurred in SQLitePCL.DLL however, if I do the same in my MainApp everything works fine.

So, is it possible to create a SQLite database in a ClassLibrary that's responsible for creating and managing it without any support from the MainApp.

Answer 1

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!

Answer 2

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 
            }