sqlite-helper 1.3.0

SQLite Helper is an open source .NETStandard library for easy storage and access of data from SQLite databases.
It uses SQLitePCLRaw to provide platform independent access to sqlite.

!!WARNING!! Library is currently in beta, breaking changes might occur!!

Install-Package sqlite-helper -Version 1.3.0
dotnet add package sqlite-helper --version 1.3.0
<PackageReference Include="sqlite-helper" Version="1.3.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add sqlite-helper --version 1.3.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

SQLite-helper

An SQLite ORM and client for .NET applications.

SQLite-helper is an open source library built using .NETStandard to be cross-platform.

The main goal of the library was to support complex LINQ queries.

Install SQLite Helper from Nuget.

Examples

Classes can be defined to access tables or views in the database.
Optionally they can be decorated with attributes to allow the library make smarter when working with the database.
You don't need to define classes to access data but it makes it a whole lot easier.

[Table("Products")]
public class Product
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

[Table("Purchases")]
public class Purchase
{
    [PrimaryKey]
    public int Id { get; set}
    public string Customer { get; set; }
    public DateTime Date { get; set; }
    [ForeignKey("Products", "Id")]
    public int ProductId { get; set; }
}

public class ProductView
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int PurchaseCount { get; set; }
}

To use the models you extend the SQLiteDatabase class.
If you wish to create tables and/or views there are methods to help.
You can even create views from LINQ queries!

public class SQLiteDb : SQLiteDatabase
{
    public Table<Product> Products { get; set; }
    public Table<Purchase> Purchases { get; set; }
    public View<ProductView> ProductView { get; set; }

    public SQLiteDb() : base(/** Optional path to file. **/)
    {
        if (UserVersion == 0)
        {
            CreateTable("Products", c => new
            {
                Id = c.Column<int>(primaryKey:true),
                Name = c.Column<string>(nullable:false),
                Price = c.Column<decimal>()
            },
            t => new
            {
                UniqueProductNames = t.Unique(p => p.Name)
            });

            CreateTable("Purchases", c => new
            {
                Id = c.Column<int>(primaryKey:true),
                Customer = c.Column<string>(nullable:false),
                Date = c.Column<DateTime>(),
                ProductId = c.Column<int>()
            },
            t => new
            {
                FK = t.ForeignKey(p => p.ProductId, "Products", new[]{ "Id" })
            });

            CreateView(
                "ProductView",
                Products.GroupJoin(Purchases, p => p.Id, p => p.ProductId, (product, purchases) => new ProductView
                {
                    Id = product.Id,
                    Name = product.Name,
                    Price = product.Price,
                    PurchaseCount = purchases.Count()
                })
            );
        }
        UserVersion++;
    }
}

You can insert data into tables like so, using the insert method of tables.

using(var db = new SQLiteDb())
{
    db.Products.Insert(new Product { Name = "Laptop", Price = 1499.99M });
}

You can query the database easily using the IQueryable methods or LINQ.

Most methods are supported for querying, including Where, OrderBy, Join, GroupBy, Select, SelectMany, etc.

NOTE: The database is not actually touched until enumeration happens, like when used in a foreach loop or any of the methods that do not return an IQueryable<...> are called e.g ToList, ToArray, Single, First, Any etc.

using(var db = new SQLiteDb())
{
    var result = db.Products.Where(p => p.Name.Length < 10 && p.Price > 19.99);
    for (var product in result)
    {
        Console.WriteLine($"Name={product.Name}, Price={product.Price}");
    }
}

I don't know why but... If you decide LINQ is not for you and you would rather type raw SQL.
There is the option of using the query method and specifying your own custom projector.

using(var db = new SQLiteDb())
{
    var result = db.Query("SELECT * FROM [Products]", r => new
    {
         Name = r.Get<string>("Name"),
         Price = r.Get<decimal>("Price")
    });
    for (var product in result)
    {
        Console.WriteLine($"Item={item}");
    }
}

If you have a good reason for typing raw sql such as, a query that cannot be easily done with LINQ.
But still want to integrate some LINQ after, you still can.
And the best part is that, it is still sent over to the database so it doesn't execute on the client side.

using (var db = new SQLiteDb())
{
    var result = db.Query("SELECT * FROM [Products]", r => new
    {
         Name = r.Get<string>("Name"),
         Price = r.Get<decimal>("Price")
    })
    .Where(p => p.Name.Length < 10 && p.Price > 19.99);

    for (var product in result)
    {
        Console.WriteLine($"Item={item}");
    }
}

SQLite-helper

An SQLite ORM and client for .NET applications.

SQLite-helper is an open source library built using .NETStandard to be cross-platform.

The main goal of the library was to support complex LINQ queries.

Install SQLite Helper from Nuget.

Examples

Classes can be defined to access tables or views in the database.
Optionally they can be decorated with attributes to allow the library make smarter when working with the database.
You don't need to define classes to access data but it makes it a whole lot easier.

[Table("Products")]
public class Product
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

[Table("Purchases")]
public class Purchase
{
    [PrimaryKey]
    public int Id { get; set}
    public string Customer { get; set; }
    public DateTime Date { get; set; }
    [ForeignKey("Products", "Id")]
    public int ProductId { get; set; }
}

public class ProductView
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int PurchaseCount { get; set; }
}

To use the models you extend the SQLiteDatabase class.
If you wish to create tables and/or views there are methods to help.
You can even create views from LINQ queries!

public class SQLiteDb : SQLiteDatabase
{
    public Table<Product> Products { get; set; }
    public Table<Purchase> Purchases { get; set; }
    public View<ProductView> ProductView { get; set; }

    public SQLiteDb() : base(/** Optional path to file. **/)
    {
        if (UserVersion == 0)
        {
            CreateTable("Products", c => new
            {
                Id = c.Column<int>(primaryKey:true),
                Name = c.Column<string>(nullable:false),
                Price = c.Column<decimal>()
            },
            t => new
            {
                UniqueProductNames = t.Unique(p => p.Name)
            });

            CreateTable("Purchases", c => new
            {
                Id = c.Column<int>(primaryKey:true),
                Customer = c.Column<string>(nullable:false),
                Date = c.Column<DateTime>(),
                ProductId = c.Column<int>()
            },
            t => new
            {
                FK = t.ForeignKey(p => p.ProductId, "Products", new[]{ "Id" })
            });

            CreateView(
                "ProductView",
                Products.GroupJoin(Purchases, p => p.Id, p => p.ProductId, (product, purchases) => new ProductView
                {
                    Id = product.Id,
                    Name = product.Name,
                    Price = product.Price,
                    PurchaseCount = purchases.Count()
                })
            );
        }
        UserVersion++;
    }
}

You can insert data into tables like so, using the insert method of tables.

using(var db = new SQLiteDb())
{
    db.Products.Insert(new Product { Name = "Laptop", Price = 1499.99M });
}

You can query the database easily using the IQueryable methods or LINQ.

Most methods are supported for querying, including Where, OrderBy, Join, GroupBy, Select, SelectMany, etc.

NOTE: The database is not actually touched until enumeration happens, like when used in a foreach loop or any of the methods that do not return an IQueryable<...> are called e.g ToList, ToArray, Single, First, Any etc.

using(var db = new SQLiteDb())
{
    var result = db.Products.Where(p => p.Name.Length < 10 && p.Price > 19.99);
    for (var product in result)
    {
        Console.WriteLine($"Name={product.Name}, Price={product.Price}");
    }
}

I don't know why but... If you decide LINQ is not for you and you would rather type raw SQL.
There is the option of using the query method and specifying your own custom projector.

using(var db = new SQLiteDb())
{
    var result = db.Query("SELECT * FROM [Products]", r => new
    {
         Name = r.Get<string>("Name"),
         Price = r.Get<decimal>("Price")
    });
    for (var product in result)
    {
        Console.WriteLine($"Item={item}");
    }
}

If you have a good reason for typing raw sql such as, a query that cannot be easily done with LINQ.
But still want to integrate some LINQ after, you still can.
And the best part is that, it is still sent over to the database so it doesn't execute on the client side.

using (var db = new SQLiteDb())
{
    var result = db.Query("SELECT * FROM [Products]", r => new
    {
         Name = r.Get<string>("Name"),
         Price = r.Get<decimal>("Price")
    })
    .Where(p => p.Name.Length < 10 && p.Price > 19.99);

    for (var product in result)
    {
        Console.WriteLine($"Item={item}");
    }
}

Release Notes

Can now create table from a class.
Better exception handling.
Added dynamic query method, so can now query without knowing the columns before hand.
Minor bug fixes.

This package is not used by any popular GitHub repositories.

Version History

Version Downloads Last updated
1.3.0 501 9/26/2017
1.2.2 303 8/1/2017
1.2.0 304 7/29/2017
1.1.1 339 7/27/2017
1.1.0 352 7/22/2017
1.0.0 326 7/20/2017