WatsonORM is a lightweight and easy to use object-relational mapper (ORM) for Sqlite in C# for .NET Core, .NET Framework, and .NET Standard built on top of DatabaseWrapper. Refer to other WatsonORM packages if you need support for other database types.
$ dotnet add package WatsonORM.Sqlite| Library | Version | Downloads |
|---|---|---|
| WatsonORM (all supported database types) |  |  |
| WatsonORM.Mysql |  |  |
| WatsonORM.Postgresql |  |
 |
| WatsonORM.Sqlite |  |  |
| WatsonORM.SqlServer |  |  |
| WatsonORM.Core |  |  |
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:
For a sample app exercising this library, refer to the Test project contained within the solution.
We'd like to give special thanks to those who have contributed or helped make the library better!
@Maclay74 @flo2000ace @MacKey-255
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); 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; }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.
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);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.
In order to use pagination with SQL Server, the SelectMany method containing the ResultOrder[] resultOrder parameter must be used.
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.
Use the MySQL constructor. MySQL constraints apply.
Refer to CHANGELOG.md.