WatsonORM.Sqlite 3.0.5

There is a newer version of this package available.
See the version list below for details.
dotnet add package WatsonORM.Sqlite --version 3.0.5                
NuGet\Install-Package WatsonORM.Sqlite -Version 3.0.5                
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="WatsonORM.Sqlite" Version="3.0.5" />                
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add WatsonORM.Sqlite --version 3.0.5                
#r "nuget: WatsonORM.Sqlite, 3.0.5"                
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install WatsonORM.Sqlite as a Cake Addin
#addin nuget:?package=WatsonORM.Sqlite&version=3.0.5

// Install WatsonORM.Sqlite as a Cake Tool
#tool nuget:?package=WatsonORM.Sqlite&version=3.0.5                

alt tag

WatsonORM

Library Version Downloads
WatsonORM (all supported database types) NuGet Version NuGet
WatsonORM.Mysql NuGet Version NuGet
WatsonORM.Postgresql NuGet Version NuGet
WatsonORM.Sqlite NuGet Version NuGet
WatsonORM.SqlServer NuGet Version NuGet
WatsonORM.Core NuGet Version NuGet

Description

WatsonORM is a lightweight and easy to use object-relational mapper (ORM) in C# for .NET Core built on top of DatabaseWrapper. WatsonORM supports Microsoft SQL Server, Mysql, MariaDB, PostgreSQL, and Sqlite databases, both on-premises and in the cloud.

Core features:

  • Annotate classes and automatically create database tables
  • Quickly create, read, update, or delete database records using your own objects
  • Reduce time-to-production and time spent building scaffolding code
  • Programmatic table creation and removal

For a sample app exercising this library, refer to the Test project contained within the solution.

New in v3.0.x

  • Dependency update
  • Minor breaking changes
  • Async API support
  • Better support for updating multiple records

Special Thanks

We'd like to give special thanks to those who have contributed or helped make the library better!

@Maclay74 @flo2000ace @MacKey-255

Simple Example

This example uses Sqlite. For SqlServer, Mysql, or Postgresql, you must make sure the database exists. Tables will be automatically created in this example. Refer to the Test project for a complete example.

using ExpressionTree;
using DatabaseWrapper.Core;
using Watson.ORM;
using Watson.ORM.Core;

// Apply attributes to your class
[Table("person")]
public class Person
{
  [Column("id", true, DataTypes.Int, false)]
  public int Id { get; set; }

  [Column("firstname", false, DataTypes.Nvarchar, 64, false)]
  public string FirstName { get; set; }

  // Parameter-less constructor is required
  public Person()
  {
  }
}

// Initialize
DatabaseSettings settings = new DatabaseSettings("./WatsonORM.db");
WatsonORM orm = new WatsonORM(settings);
orm.InitializeDatabase();
orm.InitializeTable(typeof(Person)); // initialize one table
orm.InitializeTables(new List<Type> { typeof(Person) }); // initialize multiple tables

// Insert 
Person person = new Person { FirstName = "Joel" };
Person inserted = orm.Insert<Person>(person);

// Select
Person selected = orm.SelectByPrimaryKey<Person>(1); 

// Select all records
List<Person> people = orm.SelectMany<Person>();

// Select many by column name
Expr e1 = new Expr("id", OperatorEnum.GreaterThan, 0);
people = orm.SelectMany<Person>(e1);

// Select many by property
Expr e2 = new Expr(
  orm.GetColumnName<Person>(nameof(Person.Id)),
  DbOperators.GreaterThan,
  0);
people = orm.SelectMany<Person>(e2);

// Select many by property with pagination
// Retrieve 50 records starting at record number 10
people = orm.SelectMany<Person>(10, 50, e2);

// Select many with descending order
ResultOrder[] resultOrder = new ResultOrder[1];
resultOrder[0] = new ResultOrder("id", OrderDirectionEnum.Descending);
people = orm.SelectMany<Person>(null, null, e2, resultOrder);

// Update
inserted.FirstName = "Jason";
Person updated = orm.Update<Person>(inserted);

// Delete
orm.Delete<Person>(updated); 

Column Naming

Columns can be named explicitly by specifying the colum name in the Column attribute constructor. Alternatively, constructors that don't include a name can be used, in which case the name of the property will be used as the column name.

Example with explicit naming:

[Column("id", true, DataTypes.Int, false)] // column name "id"
public int Id { get; set; }

[Column("firstname", false, DataTypes.Nvarchar, 64, false)] // column name "firstname"
public string FirstName { get; set; }

Example without explicit naming:

[Column(true, DataTypes.Int, false)] // column name "Id"
public int Id { get; set; }

[Column(DataTypes.Nvarchar, 64, false)] // column name "FirstName"
public string FirstName { get; set; }

Pagination with SelectMany

SelectMany can be paginated by using the method with either signature (int? indexStart, int? maxResults, Expr expr) or (int? indexStart, int? maxResults, Expr expr, ResultOrder[] resultOrder). indexStart is the number of records to skip, and maxResults is the number of records to retrieve.

Paginated results are always ordered by the primary key column value in ascending order, i.e. ORDER BY id ASC in the Person example above.

Validating One or More Tables

If you wish to determine if there are any errors or warnings associated with a given Type, use either the ValidateTable or ValidateTables API:

List<string> errors = new List<string>();
List<string> warnings = new List<string>();

// validate a single table
bool success = orm.ValidateTable(typeof(Person), out errors, out warnings);

// validate multiple tables
bool success = orm.ValidateTables(new List<Type> 
  {
    typeof(Person),
    typeof(Order),
    typeof(Inventory)
  },
  out errors,
  out warnings);

if (errors.Count > 0) 
  foreach (string error in errors) Console.WriteLine(error);

if (warnings.Count > 0) 
  foreach (string warning in warnings) Console.WriteLine(warning);

Using Sqlite

Sqlite may not work out of the box with .NET Framework. In order to use Sqlite with .NET Framework, you'll need to manually copy the runtimes folder into your project output directory. This directory is automatically created when building for .NET Core. To get this folder, build the Test.Sqlite project and navigate to the bin/[debug||release]/[netcoreapp*||net5.0||net6.0] directory. Then copy the runtimes folder into the project output directory of your .NET Framework application.

Using SQL Server

In order to use pagination with SQL Server, the SelectMany method containing the ResultOrder[] resultOrder parameter must be used.

Using MySQL

While the DateTimeOffset type can be used in objects, with MySQL the offset is not persisted. It is recommended that you store UTC timestamps using the DateTime type instead.

Using MariaDB

Use the MySQL constructor. MySQL constraints apply.

Version history

Refer to CHANGELOG.md.

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 is compatible.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.1 is compatible. 
.NET Framework net461 is compatible.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 is compatible.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (5)

Showing the top 5 NuGet packages that depend on WatsonORM.Sqlite:

Package Downloads
WatsonORM

WatsonORM is a lightweight and easy to use object-relational mapper (ORM) in C# for .NET Core, .NET Framework, and .NET Standard built on top of DatabaseWrapper. WatsonORM supports Microsoft SQL Server, MySQL, PostgreSQL, and Sqlite databases. Refer to other WatsonORM packages if you only need support for a single database type.

IndexEngine

Index engine is a simple indexer written in C# using Sqlite as a storage repository.

WatsonDedupe

Standalone C# library for deduplication of data.

GateKeeper

Lightweight library for implementing roles-based access control (RBAC). With Gatekeeper, you can define users, roles, resources, and permissions, and authorize requests.

OpenAuditLog

Simple C# event library providing event persistence and allowing you to write your own emitters

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
3.0.9 225 9/19/2024
3.0.8 2,376 10/4/2023
3.0.6 500 9/30/2023
3.0.5 298 9/30/2023
3.0.3 976 8/29/2023
3.0.0 2,911 7/11/2023
2.1.2 1,931 2/3/2023
2.1.0 3,030 10/25/2022
2.0.2.3 1,212 10/4/2022
2.0.1.2 2,061 9/22/2022
2.0.1.1 2,977 9/4/2022
2.0.1 2,930 6/21/2022
2.0.0.5 2,535 5/27/2022
2.0.0.1 3,786 1/3/2022
1.3.5.4 1,798 11/20/2021
1.3.5.3 2,615 11/12/2021
1.3.5.1 2,302 10/14/2021
1.3.5 3,881 6/16/2021
1.3.4 754 6/16/2021
1.3.3 838 6/15/2021
1.3.2 2,419 4/15/2021
1.3.1 1,619 3/2/2021
1.3.0.23 1,829 2/9/2021
1.3.0.22 5,678 12/29/2020
1.3.0.21 3,100 11/28/2020
1.3.0.20 719 11/28/2020
1.3.0.19 4,395 11/15/2020
1.3.0.17 1,625 11/10/2020
1.3.0.16 921 11/9/2020
1.3.0.14 10,671 10/15/2020
1.3.0.13 982 10/6/2020
1.3.0.12 940 10/5/2020
1.3.0.10 984 9/19/2020
1.3.0.9 1,797 9/16/2020
1.3.0.8 1,045 9/16/2020
1.3.0.7 968 9/15/2020
1.3.0.5 4,261 9/8/2020
1.3.0.4 1,019 9/8/2020
1.3.0.3 1,218 9/8/2020
1.3.0.2 3,998 8/17/2020
1.3.0.1 8,505 7/28/2020
1.3.0 13,980 7/10/2020
1.2.3.1 2,265 6/19/2020
1.2.3 1,382 6/15/2020
1.2.2 1,000 6/15/2020
1.2.1 945 6/15/2020
1.2.0.4 704 6/15/2020
1.2.0 28,163 6/11/2020
1.1.3.1 1,097 6/10/2020
1.1.3 2,018 6/5/2020
1.1.2.1 1,012 6/4/2020
1.1.2 1,078 6/3/2020
1.1.1.1 1,821 5/28/2020
1.1.1 981 5/28/2020
1.1.0.7 2,313 5/22/2020
1.1.0.5 1,281 5/21/2020
1.1.0.4 1,368 5/21/2020

Better support for updating multiple objects