DatabaseWrapper.Sqlite
6.1.0
dotnet add package DatabaseWrapper.Sqlite --version 6.1.0
NuGet\Install-Package DatabaseWrapper.Sqlite -Version 6.1.0
<PackageReference Include="DatabaseWrapper.Sqlite" Version="6.1.0" />
paket add DatabaseWrapper.Sqlite --version 6.1.0
#r "nuget: DatabaseWrapper.Sqlite, 6.1.0"
// Install DatabaseWrapper.Sqlite as a Cake Addin #addin nuget:?package=DatabaseWrapper.Sqlite&version=6.1.0 // Install DatabaseWrapper.Sqlite as a Cake Tool #tool nuget:?package=DatabaseWrapper.Sqlite&version=6.1.0
DatabaseWrapper
DatabaseWrapper is the EASIEST and FASTEST way to get a data-driven application up and running using SQL Server, MySQL, PostgreSQL, or Sqlite.
For a sample app exercising this library, refer to the numerous Test
projects contained within the solution.
Core features:
- Dynamic query building
- Hierarchical Boolean logic using Expression objects
- Support for SQL server native vs Windows authentication
- Support for SELECT, INSERT, UPDATE, DELETE, TRUNCATE, CREATE, DROP or raw queries
- Programmatic table creation and removal (drop)
- Built-in sanitization
- Support for .NET Standard, .NET Core, and .NET Framework
- Support for SQL Server, Sqlite, PostgreSQL, MySQL, MariaDB, both on-premises and in the cloud
- Both synchronous and asynchronous APIs
New in v6.0.x
- Minor breaking changes (API name changes)
- Asynchronous APIs
- Updated dependencies
- Bugfixes
Special Thanks
Special thanks to those who have helped contribute or otherwise improve the library!
@shawty @constantinje @thedarklort @l-404-l @igrgurina @Vaelek @treyhay31 @daoye @twobytescy @rinkusahu1
A Note on Sanitization
Use of parameterized queries vs building queries dynamically is a sensitive subject. Proponents of parameterized queries have data on their side - that parameterization does the right thing to prevent SQL injection and other issues. I do not disagree with them. However, it is worth noting that with proper care, you CAN build systems that allow you to dynamically build queries, and you SHOULD do so as long as you build in the appropriate safeguards.
If you find an injection attack that will defeat the sanitization layer built into this project, please let me know!
Simple Example
Refer to the test project for a more complete example with sample table setup scripts.
using DatabaseWrapper;
using DatabaseWrapper.Core;
using ExpressionTree;
DatabaseClient client = null;
// Sqlite
client = new DatabaseClient("[databasefilename]");
// SQL Server, MySQL, or PostgreSQL
client = new DatabaseClient(DbTypes.SqlServer, "[hostname]", [port], "[user]", "[password]", "[databasename]");
client = new DatabaseClient(DbTypes.Mysql, "[hostname]", [port], "[user]", "[password]", "[databasename]");
client = new DatabaseClient(DbTypes.Postgresql, "[hostname]", [port], "[user]", "[password]", "[databasename]");
// SQL Express
client = new DatabaseClient(DbTypes.SqlServer, "[hostname]", [port], "[user]", "[password]", "[instance]", "[databasename]");
// some variables we'll be using
Dictionary<string, object> d;
Expr e;
List<string> fields;
DataTable result;
// add a record
d = new Dictionary<string, object>();
d.Add("firstName", "Joel");
d.Add("lastName", "Christner");
d.Add("notes", "Author");
result = client.Insert("person", d);
result = await client.InsertAsync("person", d);
// update a record
d = new Dictionary<string, object>();
d.Add("notes", "The author :)");
e = new Expr("firstName", OperatorEnum.Equals, "Joel");
client.Update("person", d, e);
await client.UpdateAsync("person", d, e);
// retrieve 10 records
fields = new List<string> { "firstName", "lastName" }; // leave null for *
e = new Expr("lastName", OperatorEnum.Equals, "Christner");
ResultOrder[] order = new ResultOrder[1];
order = new ResultOrder("firstName", OrderDirectionEnum.Ascending)
result = client.Select("person", 0, 10, fields, e, order);
result = await client.SelectAsync("person", 0, 10, fields, e, order);
// delete a record
e = new Expr("firstName", Operators.Equals, "Joel");
client.Delete("person", e);
await client.DeleteAsync("person", e);
// execute a raw query
result = client.Query("SELECT customer_id FROM customer WHERE customer_id > 10");
result = await client.QueryAsync("SELECT customer_id FROM customer WHERE customer_id > 10");
Sample Compound Expression
Expressions, i.e. the Expr
class from ExpressionTree
, can be nested in either the Left
or Right
properties. Conversion from Expr
to a WHERE clause uses recursion, so you have a good degree of flexibility in building your expressions in terms of depth.
Expr e = new Expr {
Left = new Expr("age", OperatorEnum.GreaterThan, 30),
Operator = Operators.And,
Right = new Expr("height", OperatorEnum.LessThan, 74)
};
Select with Pagination
Use IndexStart
, MaxResults
, and ResultOrder[]
to retrieve paginated results. The query will retrieve maxResults records starting at row number indexStart using an ordering based on orderByClause. See the example in the DatabaseWrapperTest project.
IMPORTANT: When doing pagination with SQL Server, you MUST specify an ResultOrder[]
.
ResultOrder[] order = new ResultOrder[1];
order = new ResultOrder("firstName", OrderDirectionEnum.Ascending);
DataTable result = client.Select("person", 5, 10, null, e, order);
Need a Timestamp?
We added a simple static method for this which you can use when building expressions (or elsewhere). An object method exists as well.
string ts = client.Timestamp(DateTime.Now);
// 08/23/2016 05:34:32.4349034 PM
string tso = client.Timestamp(DateTime.Now);
// 2016-08-23 17:34:32.446913
Other Notes
General
When using database-specific classes DatabaseWrapper.Mysql
, DatabaseWrapper.Postgresql
, DatabaseWrapper.SqlServer
, or DatabaseWrapper.Sqlite
, the constructor is simplified from what is shown above.
For Sqlite:
DatabaseClient client = new DatabaseClient("[databasefilename]");
For SQL Server, MySQL, or PostgreSQL:
DatabaseClient client = new DatabaseClient(DbTypes.SqlServer, "[hostname]", [port], "[user]", "[password]", "[databasename]");
DatabaseClient client = new DatabaseClient(DbTypes.Mysql, "[hostname]", [port], "[user]", "[password]", "[databasename]");
DatabaseClient client = new DatabaseClient(DbTypes.Postgresql, "[hostname]", [port], "[user]", "[password]", "[databasename]");
For SQL Server Express:
DatabaseClient client = new DatabaseClient(DbTypes.SqlServer, "[hostname]", [port], "[user]", "[password]", "[instance]", "[databasename]");
MySQL
- MySQL does not like to return updated rows. I thought about making the UPDATE clause require that you supply the ID field and the ID value so that I could retrieve it after the fact, but that approach is just too limiting.
MariaDB
- Use the MySQL constructor. MySQL constraints remain.
PostgreSQL
- Cleansing of strings in PostgreSQL uses the dollar-quote style. Fieldnames are always encapsulated in double-quotes for PostgreSQL.
SQL Server
- Pagination where
IndexStart
andMaxResults
are supplied demands use ofResultOrder[]
.
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 theTest.Sqlite
project and navigate to thebin/debug/netcoreapp*
directory. Then copy theruntimes
folder into the project output directory of your .NET Framework application.
Version history
Refer to CHANGELOG.md.
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | 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 is compatible. 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 Framework | net48 is compatible. net481 was computed. |
-
.NETFramework 4.8
- DatabaseWrapper.Core (>= 6.1.0)
- Microsoft.Data.Sqlite (>= 8.0.8)
-
net6.0
- DatabaseWrapper.Core (>= 6.1.0)
- Microsoft.Data.Sqlite (>= 8.0.8)
-
net7.0
- DatabaseWrapper.Core (>= 6.1.0)
- Microsoft.Data.Sqlite (>= 8.0.8)
-
net8.0
- DatabaseWrapper.Core (>= 6.1.0)
- Microsoft.Data.Sqlite (>= 8.0.8)
NuGet packages (3)
Showing the top 3 NuGet packages that depend on DatabaseWrapper.Sqlite:
Package | Downloads |
---|---|
DatabaseWrapper
Simple database wrapper for Microsoft SQL Server, MySQL, PostgreSQL, and Sqlite. written in C# supporting dynamic query building and nesting using expressions. Refer to other DatabaseWrapper packages if you only need support for one database type. |
|
WatsonORM.Sqlite
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. |
|
DisCatSharp.Extensions.TwoFactorCommands
DisCatSharp.Extensions.TwoFactorCommands Extension allowing to require two factor authentication for commands. |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
6.1.0 | 380 | 9/19/2024 |
6.0.7 | 1,278 | 3/27/2024 |
6.0.6 | 4,235 | 10/4/2023 |
6.0.5 | 2,581 | 8/29/2023 |
6.0.3 | 4,347 | 7/11/2023 |
6.0.2 | 1,340 | 7/11/2023 |
6.0.1 | 1,242 | 7/11/2023 |
6.0.0 | 1,317 | 7/11/2023 |
5.0.2 | 3,914 | 2/2/2023 |
5.0.1 | 766 | 2/2/2023 |
5.0.0 | 4,650 | 10/25/2022 |
4.1.5 | 7,561 | 8/29/2022 |
4.1.3 | 4,326 | 6/21/2022 |
4.1.2 | 4,079 | 5/27/2022 |
4.1.1 | 900 | 5/27/2022 |
4.0.0.1 | 5,571 | 12/31/2021 |
4.0.0 | 1,504 | 12/31/2021 |
3.3.2.3 | 1,646 | 12/22/2021 |
3.3.2.1 | 3,127 | 11/20/2021 |
3.3.2 | 2,054 | 11/19/2021 |
3.3.1.5 | 3,577 | 11/12/2021 |
3.3.1.1 | 11,209 | 4/14/2021 |
3.3.1 | 1,695 | 4/14/2021 |
3.3.0 | 3,171 | 3/2/2021 |
3.2.2.18 | 3,427 | 2/9/2021 |
3.2.2.17 | 7,511 | 12/26/2020 |
3.2.2.16 | 5,761 | 11/28/2020 |
3.2.2.15 | 1,850 | 11/26/2020 |
3.2.2.12 | 1,856 | 11/25/2020 |
3.2.2.10 | 7,659 | 11/15/2020 |
3.2.2.9 | 3,350 | 11/10/2020 |
3.2.2.8 | 2,742 | 11/9/2020 |
3.2.2.6 | 12,702 | 10/15/2020 |
3.2.2.3 | 4,338 | 10/5/2020 |
3.2.2 | 5,924 | 9/18/2020 |
3.2.1.5 | 3,648 | 9/16/2020 |
3.2.1.4 | 2,741 | 9/16/2020 |
3.2.1.3 | 7,752 | 9/8/2020 |
3.2.1.2 | 3,032 | 9/8/2020 |
3.2.1.1 | 5,677 | 8/17/2020 |
3.2.1 | 9,759 | 7/28/2020 |
3.2.0 | 14,746 | 7/10/2020 |
3.1.0 | 35,015 | 6/11/2020 |
3.0.1.3 | 1,013 | 6/11/2020 |
3.0.1.2 | 1,766 | 6/10/2020 |
3.0.1.1 | 5,208 | 6/4/2020 |
3.0.1 | 2,925 | 6/3/2020 |
3.0.0.3 | 5,898 | 5/21/2020 |
3.0.0.2 | 4,902 | 5/20/2020 |
3.0.0 | 2,193 | 5/20/2020 |
Oracle support (thank you @Skimmenthal13!), bugfixes, dependency updates.