void FluidMode(BeanApi api)
{
/// ## Fluid Mode
/// LimeBean is committed to mitigate the common inconvenience associated with relational databases,
/// namely necessity to manually create tables, columns and adjust their data types.
/// In this sense, LimeBean takes SQL databases a little closer to NoSQL ones like MongoDB.
///
/// **Fluid Mode** is optional, turned off by default, and is recommended for use only during early development stages
/// (particularly for prototyping and scaffolding).
/// To enable it, invoke the `EnterFluidMode` method on the `BeanApi` object:
#if CODE
api.EnterFluidMode();
#endif
/// How does it work? When you save the next bean, LimeBean analyzes its fields and compares
/// their names and types to the database schema.
/// If new data cannot be stored to an existing table, schema alteration occurs.
/// LimeBean can create new tables, add missing columns, and widen data types.
/// It will never truncate data or delete unused columns.
///
/// **NOTE:** LimeBean doesn't detect renamings.
///
/// **CAUTION:** Automatically generated schema is usually sub-optimal and lacks indexes which are essential
/// for performance. When most of planned tables are already in place,
/// and only minor changes are expected,
/// it is recommended to turn the Fluid Mode off, audit the database structure, add indexes, and make further schema
/// changes with a dedicated database management tool (like HeidiSQL, SSMS, pgAdmin, etc).
///
}
public static void MyAction(BeanApi bApi, params object[] args)
{
var output = (ITestOutputHelper)args[0];
output.WriteLine($"Database Type: \"{bApi.DbType}\"");
output.WriteLine($"Parameter: {args[1]}");
}
void InternalQueryCache(BeanApi api)
{
/// ## Internal Query Cache
/// Results of all recent read-only SQL queries initiated by
/// [finder](#finding-beans-with-sql) and [generic query](#generic-sql-queries) functions are cached internally
/// on the *least recently used* (LRU) basis. This saves database round trips during repeated reads.
///
/// The number of cached results can be adjusted by setting the `CacheCapacity` property:
#if CODE
// increase
api.CacheCapacity = 500;
// turn off completely
api.CacheCapacity = 0;
#endif
/// Cache is fully invalidated (cleared) on:
///
/// * any non-readonly query (UPDATE, etc)
/// * failed [transaction](#transactions)
///
/// In rare special cases you may need to **bypass** the cache.
/// For this purpose, all query functions provide overloads with the `useCache` argument:
#if CODE
var uid = api.Cell <string>(false, "select hex(randomblob(16))");
#endif
}
private static Pagination PrepareFetchedPagination(BeanApi api, string query, int pageNo, int perPage)
{
var count = api.Cell <long>(Regex.Replace(query,
"SELECT((.|\\n|\\r)*?)FROM", "SELECT COUNT(*) FROM"));
return(new Pagination(count, pageNo, perPage));
}
void CustomizingSqlCommands(BeanApi api, Bean bean)
{
/// ## Customizing SQL Commands
/// In some cases it is necessary to manually adjust parameters of a SQL command which is about to execute.
/// This can be done in the `QueryExecuting` event handler.
///
/// **Example 1.** Force `datetime2` type for all dates (SQL Server):
#if CODE
api.QueryExecuting += cmd => {
foreach (SqlParameter p in cmd.Parameters)
{
if (p.Value is DateTime)
{
p.SqlDbType = SqlDbType.DateTime2;
}
}
};
#endif
/// **Example 2.** Work with `MySqlGeometry` objects (MySQL/MariaDB):
#if CODE
api.QueryExecuting += cmd => {
foreach (MySqlParameter p in cmd.Parameters)
{
if (p.Value is MySqlGeometry)
{
p.MySqlDbType = MySqlDbType.Geometry;
}
}
};
bean["point"] = new MySqlGeometry(34.962, 34.066);
api.Store(bean);
#endif
}
public static int Execute(this SqlBuilder sqlBuilder, BeanApi api, params object[] parameters)
{
var query = sqlBuilder.ToSql();
return(query.StartsWith("SELECT")
? throw NotExecutableException.Create()
: api.Exec(query, parameters));
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense <Book>();
api.Load <Book>(1);
api.Find <Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
void Snippet1(BeanApi api)
{
#if CODE
api.DefaultKey(false); // turn off auto-increment keys
api.AddObserver(new GuidKeyObserver());
// but beware of http://www.informit.com/articles/printerfriendly/25862
#endif
}
public static IEnumerable <T> ToColIterator <T>(this SqlBuilder sqlBuilder, BeanApi api,
params object[] parameters)
{
var query = sqlBuilder.ToSql();
return(query.StartsWith("SELECT")
? api.ColIterator <T>(query, parameters)
: throw NotAnSqlQueryException.Create());
}
public static T FetchScalar <T>(this SqlBuilder sqlBuilder, BeanApi api,
bool useCache = true, params object[] parameters)
{
var query = sqlBuilder.ToSql();
return(query.StartsWith("SELECT")
? api.Cell <T>(useCache, query, parameters)
: throw NotAnSqlQueryException.Create());
}
public SequelQueryBuilderTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("CREATE TABLE Product (id INTEGER NOT NULL PRIMARY KEY, Name)");
_api.Dispense("Product").Put("Name", "MacBook Pro 13").Store();
_api.Dispense("Product").Put("Name", "Microsoft Surface IV").Store();
_api.Dispense("Product").Put("Name", "Lenovo ThinkPad X1").Store();
_api.Dispense("Product").Put("Name", "Dell XPS 13").Store();
_api.Dispense("Product").Put("Name", "Lenovo Yoga").Store();
}
/// ## BeanApi Object Lifetime
/// The `BeanApi` class implements `IDisposable` (it holds the `DbConnection`) and is not thread-safe.
/// Care should be taken to ensure that the same `BeanApi` and `DbConnection` instance is not used from multiple threads without
/// synchronization, and that it is properly disposed. Let's consider some common usage scenarios.
///
/// ### Local Usage
/// If LimeBean is used locally, then it should be enclosed in a `using` block:
void LocalUsage(string connectionString, Type connectionType)
{
#if CODE
using (var api = new BeanApi(connectionString, connectionType)) {
api.EnterFluidMode();
// work with beans
}
#endif
}
static Database()
{
var connection =
new NpgsqlConnection(
"Host=172.16.2.200;Port=5432;Username=book_system;Password=book123;Database=local_book_shop;");
connection.Open();
DbConnection = new BeanApi(connection);
}
public DirtyTrackingTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("create table foo(id, a, b)");
_api.Exec("insert into foo values(1, 'initial', 'initial')");
_bean = _api.Load("foo", 1);
_api.QueryExecuting += cmd => _queryCount++;
}
public DirtyTrackingTests()
{
_api = SQLitePortability.CreateApi();
_api.Exec("create table foo(id, a, b)");
_api.Exec("insert into foo values(1, 'initial', 'initial')");
_bean = _api.Load("foo", 1);
_api.QueryExecuting += cmd => _queryCount++;
}
public DatabaseStorageTests_SQLite()
{
_conn = SQLitePortability.CreateConnection();
_conn.Open();
IDatabaseDetails details = new SQLiteDetails();
_db = new DatabaseAccess(_conn, details);
_keys = new KeyUtil();
_storage = new DatabaseStorage(details, _db, _keys);
_api = new BeanApi(_conn);
}
public DatabaseStorageTests_MsSql(MsSqlConnectionFixture fixture)
{
_fixture = fixture;
_fixture.SetUpDatabase();
var details = new MsSqlDetails();
_db = new DatabaseAccess(_fixture.Connection, details);
_keys = new KeyUtil();
_storage = new DatabaseStorage(details, _db, _keys);
_api = new BeanApi(_fixture.Connection);
}
public void Scenario()
{
using (var api = new BeanApi("data source=:memory:", SQLiteFactory.Instance))
{
api.EnterFluidMode();
api.DefaultKey(false);
api.AddObserver(new GuidKeyObserver());
var bean = api.Dispense("foo");
var key = api.Store(bean);
Console.WriteLine("Key is: " + key);
}
}
void CRUD(BeanApi api)
{
/// ## Getting Started: Basic CRUD (Create/Read/Update/Delete)
///
/// For basic usage, LimeBean requires no configuration or table classes!
///
/// Take a look at some basic CRUD scenarios:
///
/// **Create**
#if CODE
// Create a Bean.
// "Bean" means row, and "Dispense" makes an empty Bean for a table.
var bean = api.Dispense("book");
// Each bean has a "Kind". Kind is a synonym for "table name"
// You give a Bean its Kind when you Dispense it, or query the database
var kind = bean.GetKind();
Console.WriteLine(kind);
// Fill the new Bean with some data
bean["title"] = "Three Comrades";
bean["rating"] = 10;
// You can also chain .Put() to do this
bean.Put("title", "Three Comrades")
.Put("rating", 10);
// Store it
// Store() will Create or Update a record intelligently
var id = api.Store(bean);
// Store also returns the Primary Key for the saved Bean, even for multi-column/compound keys
Console.WriteLine(id);
#endif
/// **Read** and **Update**
#if CODE
// Load a Bean with a known ID
bean = api.Load("book", id);
// Make some edits
bean["release_date"] = new DateTime(2015, 7, 30);
bean["rating"] = 5;
// Update database
api.Store(bean);
#endif
/// **Delete**
#if CODE
api.Trash(bean);
#endif
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col <string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell <int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach (var row in api.RowsIterator("SELECT..."))
{
// do something
}
foreach (var item in api.ColIterator("SELECT..."))
{
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
void FindingBeansWithSql(BeanApi api)
{
/// ## Finding Beans with SQL
/// LimeBean doesn't introduce any custom query language, nor does it implement a LINQ provider.
/// To find beans matching a criteria, use fragments of plain SQL:
///
{
#if CODE
var list = api.Find("book", "WHERE rating > 7");
#endif
}
/// Instead of embedding values into SQL code, it is recommended to use **parameters**:
{
#if CODE
var list = api.Find("book", "WHERE rating > {0}", 7);
#endif
}
/// Usage of parameters looks similar to `String.Format`, but instead of direct interpolation,
/// they are transformed into fair ADO.NET command parameters to protect your queries from SQL-injection attacks.
///
{
#if CODE
var list = api.Find(
"book",
"WHERE release_date BETWEEN {0} and {1} AND author LIKE {2}",
new DateTime(1930, 1, 1), new DateTime(1950, 1, 1), "%remarque%"
);
#endif
}
///
/// You can use any SQL as long as the result maps to a set of beans.
/// For other cases, see [Generic Queries](#generic-sql-queries).
///
/// To find a single bean:
#if CODE
var best = api.FindOne("book", "ORDER BY rating DESC LIMIT 1");
#endif
/// To find out the number of beans without loading them:
#if CODE
var count = api.Count("book", "WHERE rating > {0}", 7);
#endif
/// It is also possible to perform unbuffered (memory-optimized) load for processing in a `foreach` loop.
///
/// Data is 'Lazy Loaded' on each iteration using [C-sharp's IEnumerable Yield](http://programmers.stackexchange.com/a/97350)
#if CODE
foreach (var bean in api.FindIterator("book", "ORDER BY rating"))
{
// do something with bean
}
#endif
}
void BeanOptions(BeanApi api)
{
/// ## Bean Options
/// You can configure the BeanAPI to dispense new Beans with some default options
///
/// **.ValidateGetColumns**
#if CODE
// Sets whether a Bean throws `ColumnNotFoundException` if
// you request a column which isn't stored in the Bean. True by default
api.BeanOptions.ValidateGetColumns = true;
Bean bean = api.Dispense("books");
bean.Put("ColumnOne", 1); // Add a single column
int one = bean.Get <int>("ColumnOne"); // OK
int two = bean.Get <int>("ColumnTwo"); // throws ColumnNotFoundException
#endif
}
public void Api_DetailsSelection()
{
var mariaBeanApi = new BeanApi(new MySql.Data.MySqlClient.MySqlConnection());
Assert.Equal(DatabaseType.Sqlite, new BeanApi(SQLitePortability.CreateConnection()).CreateDetails().DbType);
#if !NO_MSSQL
Assert.Equal(DatabaseType.MsSql, new BeanApi(new System.Data.SqlClient.SqlConnection()).CreateDetails().DbType);
#endif
#if !NO_MARIADB
Assert.Equal(DatabaseType.MariaDb, new BeanApi(new MySql.Data.MySqlClient.MySqlConnection()).CreateDetails().DbType);
#endif
#if !NO_PGSQL
Assert.Equal(DatabaseType.PgSql, new BeanApi(new Npgsql.NpgsqlConnection()).CreateDetails().DbType);
#endif
}
public void Scenario()
{
// Tell about your database
var r = new BeanApi("data source=:memory:", SQLiteFactory.Instance);
// Enable automatic schema update
r.EnterFluidMode();
// create a bean
var bean = r.Dispense("person");
// it's of kind "person"
Console.WriteLine(bean.GetKind());
// give me the Id of the newly stored bean
var id = bean
// fill it
.Put("name", "Alex")
.Put("year", 1984)
.Put("smart", true)
// store it
.Store();
// Database schema will be updated automatically for you
// Now the bean has an id
Console.WriteLine(bean["id"]);
// load a bean
bean = r.Load("person", id);
bean
// change it
.Put("name", "Lexa")
.Put("new_prop", 123)
// commit changes
.Store();
// or delete it
r.Trash(bean);
// close the connection
r.Dispose();
}
void Samples(BeanApi api)
{
#if CODE
// Custom key name for beans of kind "book"
api.Key("book", "book_id");
// Custom key name for custom bean class Book (see Custom Bean Classes)
api.Key <Book>("book_id");
// Custom non-autoincrement key
api.Key("book", "book_id", false);
// Compound key (order_id, product_id) for beans of kind "order_item"
api.Key("order_item", "order_id", "product_id");
// Change defaults for all beans
api.DefaultKey("Oid", false);
#endif
}
public static Pagination FetchLPaginated(this SqlBuilder sqlBuilder, BeanApi api,
int pageNo, int perPage = 10, bool useCache = true, params object[] parameters)
{
var query = sqlBuilder.ToSql();
if (!query.StartsWith("SELECT"))
{
throw NotAnSqlQueryException.Create();
}
var pagination = PrepareFetchedPagination(api, query, pageNo, perPage);
var dbDetails = api.CreateDetails();
pagination.Data = api.Rows(useCache,
$"{query} {dbDetails.Paginate(pagination.CurrentPage, perPage)}", parameters);
return(pagination);
}
void FluidMode(BeanApi api)
{
/// ## Fluid Mode
/// LimeBean mitigates the common inconvenience associated with relational databases,
/// namely necessity to manually create tables, columns and adjust their data types.
/// In this sense, LimeBean takes SQL databases a little closer to NoSQL ones like MongoDB.
///
/// **Fluid Mode** is optional, turned off by default, and is recommended for use only during early development stages
/// (particularly for prototyping and scaffolding).
/// To enable it, invoke the `EnterFluidMode` method on the `BeanApi` object:
#if CODE
api.EnterFluidMode();
// Make a Bean for a table which doesn't yet exist
var bean = api.Dispense("book_types");
// Fill it with some data
// Limebean will automatically detect Types and create columns with the correct Type
bean.Put("name", "War")
.Put("fiction", true);
// Store will automatically create any missing tables (with an auto-incrementing 'id' column) and columns,
// then add the Bean as a new row
var id = api.Store(bean);
// The bean is now available in the database
var savedBean = api.Load("book_types", id);
#endif
/// How does this work? When you save a Bean while in Fluid Mode, LimeBean analyzes its fields and compares
/// their names and types to the database schema.
/// If new data cannot be stored to an existing table, schema alteration occurs.
/// LimeBean can create new tables, add missing columns, and widen data types.
/// It will never truncate data or delete unused columns.
///
/// **NOTE:** LimeBean will not detect renamings.
///
/// **CAUTION:** Automatically generated schema is usually sub-optimal and lacks indexes which are essential
/// for performance. When most planned tables are already in place,
/// it is recommended you turn Fluid Mode off, audit the database structure, add indexes, and make further schema
/// changes with a dedicated database management tool (like HeidiSQL, SSMS, pgAdmin, etc).
///
}
public Auditor(BeanApi api, string auditBlacklist)
{
var exitFluidMode = false;
_auditBlacklist = auditBlacklist == string.Empty
? new List <string>()
: auditBlacklist.ToUpper().Split(';').ToList();
_auditBlacklist.Add("AUDIT");
if ((api.Database == string.Empty && api.Connection.State != ConnectionState.Open) ||
api.IsKnownKind("AUDIT"))
{
return;
}
if (!api.IsFluidMode())
{
api.EnterFluidMode();
exitFluidMode = true;
}
var audit = api.Dispense("AUDIT");
audit
.Put("AuditDate", DateTime.Now)
.Put("Action", new string('X', 16))
.Put("User", new string('X', 64))
.Put("Object", new string('X', 64))
.Put("ObjectId", new string('X', 64))
.Put("Property", new string('X', 64))
.Put("PropertyType", new string('X', 64))
.Put("OldValue", new string('X', 1024))
.Put("NewValue", new string('X', 1024))
.Put("Notes", new string('X', 4096))
.Store();
if (exitFluidMode)
{
api.ExitFluidMode();
}
}
void Transactions(BeanApi api)
{
/// ## Transactions
/// To execute a block of code in a transaction, wrap it in a delegate and pass to the `Transaction` method:
#if CODE
api.Transaction(delegate() {
// do some work
});
#endif
/// Transaction is automatically rolled back if:
///
/// * An unhandled exception is thrown during the execution
/// * The delegate returns `false`
///
/// Otherwise it's committed.
///
/// Transactions can be nested (if the underlying ADO.NET provider allows this):
#if CODE
api.Transaction(delegate() {
// outer transaction
api.Transaction(delegate() {
// nested transaction
});
});
#endif
/// ## Implicit Transactions
/// When you invoke `Store` or `Trash` (see [CRUD]getting-started-basic-crud-create-read-update-delete) outside a transaction, then an implicit transaction
/// is initiated behind the scenes. This is done to enforce database integrity in case of
/// additional modifications performed in
/// [hooks](#lifecycle-hooks) and [observers](#bean-observers) (such as cascading delete, etc).
///
/// There are special cases when you may need to turn this behavior off
/// (for example when using [LOCK TABLES with InnoDB](https://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html)):
#if CODE
api.ImplicitTransactions = false;
#endif
}
/// ## About LimeBean
/// LimeBean provides a simple and concise API for accessing **ADO.NET** data sources.
/// It's a **Hybrid-ORM**... halfway between a micro-ORM and plain old SQL.
///
/// Compatible with:
///
/// * **.NET Framework 4.x**
/// * **.NET Core** (ASP.NET Core, UWP)
/// * **Mono**
///
/// Supported databases include:
///
/// * **SQLite**
/// * **MySQL/MariaDB**
/// * **PostgreSQL**
/// * **SQL Server**
///
/// Limebean uses synonyms for many things, key examples are:
///
/// * **Bean** - A single row of data
/// * **Kind** - The name of a table which a Bean represents
///
/// ## Installation
/// LimeBean is available on [NuGet Gallery](https://www.nuget.org/packages/LimeBean):
///
/// PM> Install-Package LimeBean
///
///
void ConnectToDatabase(DbConnection connection)
{
/// ## Getting started: Connecting
/// LimeBean needs an ADO.NET driver to work with. You can use one of the following:
///
/// * [System.Data.SQLite.Core](https://www.nuget.org/packages/System.Data.SQLite.Core) for SQLite in .NET
/// * [Mono.Data.Sqlite](http://www.mono-project.com/docs/database-access/providers/sqlite/) for SQLite in Mono
/// * [Microsoft.Data.SQLite](https://www.nuget.org/packages/Microsoft.Data.SQLite) for SQLite in .NET Core
/// * [MySql.Data](https://www.nuget.org/packages/MySql.Data/) official connector for MySQL or MariaDB
/// * [MySql.Data](https://github.com/SapientGuardian/mysql-connector-net-netstandard) fork with .Net Core support
/// * [Npgsql](https://www.nuget.org/packages/Npgsql/) for PostgreSQL
/// * [System.Data.SqlClient](https://msdn.microsoft.com/en-us/library/System.Data.SqlClient.aspx) for SQL Server
///
/// To start using LimeBean, create an instance of the `BeanApi` class:
{
#if CODE
// Using a connection string and an ADO.NET provider factory
var api = new BeanApi("server=localhost; database=db1; ...", MySqlClientFactory.Instance);
#endif
}
{
#if CODE
// Using a connection string and a connection type
var api = new BeanApi("data source=/path/to/db", typeof(SQLiteConnection));
#endif
}
{
#if CODE
// Using a shared pre-opened connection
var api = new BeanApi(connection);
#endif
}
/// **NOTE:** `BeanApi` implements `IDisposable`. When created from a connection string (two first cases above),
/// the underlying connection is initiated on the first usage and closed on dispose.
/// Shared connections are used as-is, their state is not changed.
///
/// See also: [BeanApi Object Lifetime](#beanapi-object-lifetime)
}
/// ## About LimeBean
/// LimeBean provides a simple and concise API for accessing **ADO.NET** data sources.
/// It's halfway between a **micro-ORM** and **direct SQL**.
/// With LimeBean, you can treat your data as **objects** or as plain **records**, depending on a situation.
///
/// Compatible with:
///
/// * **.NET Framework 4.x**
/// * **Mono**
/// * **ASP.NET 5** (DNX)
/// * **Windows 10 Universal App Platform** (UAP)
///
/// Supported databases include:
///
/// * **SQLite**
/// * **MySQL/MariaDB**
/// * **PostgreSQL**
/// * **SQL Server**
///
/// The library is inspired by [RedBeanPHP](http://redbeanphp.com).
///
/// ## Installation
/// LimeBean is available on [NuGet Gallery](https://www.nuget.org/packages/LimeBean):
///
/// PM> Install-Package LimeBean
///
/// **For DNX and UAP projects**, add a dependency to the project.json file:
///
/// {
/// "dependencies": {
/// "LimeBean": "..."
/// }
/// }
void ConnectToDatabase(DbConnection connection)
{
/// ## Connect to Database
/// LimeBean needs an ADO.NET driver to work with. Use one of the following:
///
/// * [System.Data.SQLite.Core](https://www.nuget.org/packages/System.Data.SQLite.Core) for SQLite in .NET
/// * [Mono.Data.Sqlite](http://www.mono-project.com/download/) for SQLite in Mono
/// * [Microsoft.Data.Sqlite](https://www.nuget.org/packages/Microsoft.Data.SQLite) for SQLite in ASP.NET 5 and Windows 10 Universal projects
/// * [MySql.Data](https://www.nuget.org/packages/MySql.Data/) for MySQL or MariaDB
/// * [Npgsql](https://www.nuget.org/packages/Npgsql/) for PostgreSQL
/// * [System.Data.SqlClient](https://msdn.microsoft.com/en-us/library/System.Data.SqlClient.aspx) for SQL Server
///
/// To start using LimeBean, create an instance of the `BeanApi` class:
{
#if CODE
// Using a connection string and an ADO.NET provider factory
var api = new BeanApi("data source=/path/to/db", MySqlClientFactory.Instance);
#endif
}
{
#if CODE
// Using a connection string and a connection type
var api = new BeanApi("data source=/path/to/db", typeof(SQLiteConnection));
#endif
}
{
#if CODE
// Using a shared pre-opened connection
var api = new BeanApi(connection);
#endif
}
/// **NOTE:** `BeanApi` is `IDisposable`. When created from a connection string (two first cases above),
/// the underlying connection is initiated on the first usage and closed on dispose.
/// Shared connections are used as-is, their state is not changed.
///
/// See also: [BeanApi Object Lifetime](#beanapi-object-lifetime)
}
void GenericSqlQueries(BeanApi api)
{
/// ## Generic SQL Queries
/// Often it's needed to execute queries which don't map to beans:
/// aggregates, grouping, joins, selecting single column, etc.
///
/// `BeanApi` provides methods for such tasks:
///
{
#if CODE
// Load multiple rows
var rows = api.Rows(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author", 7);
// Load a single row
var row = api.Row(@"SELECT author, COUNT(*)
FROM book
WHERE rating > {0}
GROUP BY author
ORDER BY COUNT(*) DESC
LIMIT 1", 7);
// Load a column
var col = api.Col<string>("SELECT DISTINCT author FROM book ORDER BY author");
// Load a single value
var count = api.Cell<int>("SELECT COUNT(*) FROM book");
#endif
}
/// For `Rows` and `Col`, there are unbuffered (memory-optimized) counterparts:
{
#if CODE
foreach(var row in api.RowsIterator("...")) {
// do something
}
foreach(var item in api.ColIterator("...")) {
// do something
}
#endif
}
/// To execute a non-query SQL command, use the `Exec` method:
#if CODE
api.Exec("SET autocommit = 0");
#endif
/// **NOTE:** all described functions accept parameters in the same form as [finder methods](#finding-beans-with-sql) do.
}
void InternalQueryCache(BeanApi api)
{
/// ## Internal Query Cache
/// Results of all recent read-only SQL queries initiated by
/// [finder](#finding-beans-with-sql) and [generic query](#generic-sql-queries) functions are cached internally
/// on the *least recently used* (LRU) basis. This saves database round trips during repeated reads.
///
/// The number of cached results can be adjusted by setting the `CacheCapacity` property:
#if CODE
// increase
api.CacheCapacity = 500;
// turn off completely
api.CacheCapacity = 0;
#endif
/// Cache is fully invalidated (cleared) on:
///
/// * any non-readonly query (UPDATE, etc)
/// * failed [transaction](#transactions)
///
/// In rare special cases you may need to **bypass** the cache.
/// For this purpose, all query functions provide overloads with the `useCache` argument:
#if CODE
var uid = api.Cell<string>(false, "select hex(randomblob(16))");
#endif
}
void Snippet1(BeanApi api)
{
#if CODE
api.DefaultKey(false); // turn off auto-increment keys
api.AddObserver(new GuidKeyObserver());
// but beware of http://www.informit.com/articles/printerfriendly/25862
#endif
}
void FindingBeansWithSql(BeanApi api)
{
/// ## Finding Beans with SQL
/// LimeBean doesn't introduce any custom query language, nor does it implement a LINQ provider.
/// To find beans matching a criteria, use snippets of plain SQL:
///
{
#if CODE
var list = api.Find("book", "WHERE rating > 7");
#endif
}
/// Instead of embedding values into SQL code, it is recommended to use **parameters**:
{
#if CODE
var list = api.Find("book", "WHERE rating > {0}", 7);
#endif
}
/// Usage of parameters looks similar to `String.Format`, but instead of direct interpolation,
/// they are transformed into fair ADO.NET command parameters to protect your queries from injection-attacks.
///
{
#if CODE
var list = api.Find(
"book",
"WHERE release_date BETWEEN {0} and {1} AND author LIKE {2}",
new DateTime(1930, 1, 1), new DateTime(1950, 1, 1), "%remarque%"
);
#endif
}
///
/// You can use any SQL as long as the result maps to a set of beans.
/// For other cases, see [Generic Queries](#generic-sql-queries).
///
/// To find a single bean:
#if CODE
var best = api.FindOne("book", "ORDER BY rating DESC LIMIT 1");
#endif
/// To find out the number of beans without loading them:
#if CODE
var count = api.Count("book", "WHERE rating > {0}", 7);
#endif
/// It is also possible to perform unbuffered (memory-optimized) load for processing in a foreach-loop:
#if CODE
foreach(var bean in api.FindIterator("book", "ORDER BY rating")) {
// do something with bean
}
#endif
}
static Globals()
{
MyBeanApi = new BeanApi("connection string", SQLiteFactory.Instance);
MyBeanApi.EnterFluidMode();
}
protected void Application_BeginRequest(object sender, EventArgs e)
{
MyBeanApi = new BeanApi("connection string", SQLiteFactory.Instance);
MyBeanApi.EnterFluidMode();
}
public HomeController(MyBeanApi beans)
{
_beans = beans;
}
public CustomKeysTests()
{
_api = SQLitePortability.CreateApi();
}
void FluidMode(BeanApi api)
{
/// ## Fluid Mode
/// LimeBean is committed to mitigate the common inconvenience associated with relational databases,
/// namely necessity to manually create tables, columns and adjust their data types.
/// In this sense, LimeBean takes SQL databases a little closer to NoSQL ones like MongoDB.
///
/// **Fluid Mode** is optional, turned off by default, and is recommended for use only during early development stages
/// (particularly for prototyping and scaffolding).
/// To enable it, invoke the `EnterFluidMode` method on the `BeanApi` object:
#if CODE
api.EnterFluidMode();
#endif
/// How does it work? When you save the next bean, LimeBean analyzes its fields and compares
/// their names and types to the database schema.
/// If new data cannot be stored to an existing table, schema alteration occurs.
/// LimeBean can create new tables, add missing columns, and widen data types.
/// It will never truncate data or delete unused columns.
///
/// **NOTE:** LimeBean doesn't detect renamings.
///
/// **CAUTION:** Automatically generated schema is usually sub-optimal and lacks indexes which are essential
/// for performance. When most of planned tables are already in place,
/// and only minor changes are expected,
/// it is recommended to turn the Fluid Mode off, audit the database structure, add indexes, and make further schema
/// changes with a dedicated database management tool (like HeidiSQL, SSMS, pgAdmin, etc).
///
}
/// ## BeanApi Object Lifetime
/// The `BeanApi` class is `IDisposable` (it holds the `DbConnection`) and is not thread-safe.
/// Care should be taken to ensure that the same `BeanApi` is not used from multiple threads without
/// synchronization, and that it is properly disposed. Let's consider some common usage scenarios.
///
/// ### Local Usage
/// If LimeBean is used locally, then it should be enclosed in a `using` block:
void LocalUsage(string connectionString, Type connectionType)
{
#if CODE
using(var api = new BeanApi(connectionString, connectionType)) {
api.EnterFluidMode();
// work with beans
}
#endif
}
public HomeController(MyBeanApi beans)
{
_beans = beans;
}
void Samples(BeanApi api)
{
#if CODE
// Custom key name for beans of kind "book"
api.Key("book", "book_id");
// Custom key name for custom bean class Book (see Custom Bean Classes)
api.Key<Book>("book_id");
// Custom non-autoincrement key
api.Key("book", "book_id", false);
// Compound key (order_id, product_id) for beans of kind "order_item"
api.Key("order_item", "order_id", "product_id");
// Change defaults for all beans
api.DefaultKey("Oid", false);
#endif
}
void CustomizingSqlCommands(BeanApi api, Bean bean)
{
/// ## Customizing SQL Commands
/// In some cases it is necessary to manually adjust parameters of a SQL command which is about to execute.
/// This can be done in the `QueryExecuting` event handler.
///
/// **Example 1.** Force `datetime2` type for all dates (SQL Server):
#if CODE
api.QueryExecuting += cmd => {
foreach(SqlParameter p in cmd.Parameters)
if(p.Value is DateTime)
p.SqlDbType = SqlDbType.DateTime2;
};
#endif
/// **Example 2.** Work with `MySqlGeometry` objects (MySQL/MariaDB):
#if CODE
api.QueryExecuting += cmd => {
foreach(MySqlParameter p in cmd.Parameters)
if(p.Value is MySqlGeometry)
p.MySqlDbType = MySqlDbType.Geometry;
};
bean["point"] = new MySqlGeometry(34.962, 34.066);
api.Store(bean);
#endif
}
void Transactions(BeanApi api)
{
/// ## Transactions
/// To execute a block of code in a transaction, wrap it in a delegate and pass to the `Transaction` method:
#if CODE
api.Transaction(delegate() {
// do some work
});
#endif
/// Transaction is automatically rolled back if:
///
/// * An unhandled exception is thrown during the execution
/// * The delegate returns `false`
///
/// Otherwise it's committed.
///
/// Transactions can be nested (if the underlying ADO.NET provider allows this):
#if CODE
api.Transaction(delegate() {
// outer transaction
api.Transaction(delegate() {
// nested transaction
});
});
#endif
/// ## Implicit Transactions
/// When you invoke `Store` or `Trash` (see [CRUD](#crud)) outside a transaction, then an implicit transaction
/// is initiated behind the scenes. This is done to enforce database integrity in case of
/// additional modifications performed in
/// [hooks](#lifecycle-hooks) and [observers](#bean-observers) (such as cascading delete, etc).
///
/// There are special cases when you may need to turn this behavior off
/// (for example when using [LOCK TABLES with InnoDB](https://dev.mysql.com/doc/refman/5.0/en/lock-tables-and-transactions.html)):
#if CODE
api.ImplicitTransactions = false;
#endif
}
void CRUD()
{
/// ## CRUD
/// (Create / Read / Update / Delete)
///
/// For basic usage, LimeBean requires zero configuration and no additional code!
/// Database schema is maintained on-the-fly:
/// no need to create tables and columns (see [Fluid Mode](#fluid-mode)).
///
/// Take a look at the following sample scenario:
///
#if CODE
// Use a temporary in-memory SQLite database
var api = new BeanApi("Data Source=:memory:", SQLiteFactory.Instance);
// Enter the "Fluid Mode"
api.EnterFluidMode();
#endif
/// **Create**
#if CODE
// Create a bean. "Bean" means "data record", "dispense" means "instantiate new".
var bean = api.Dispense("book");
// Each bean has a kind. "Kind" is a synonym for "table name"
var kind = bean.GetKind();
Console.WriteLine(kind);
// Fill it with some data
bean["title"] = "Three Comrades";
bean["rating"] = 10;
// Store it
// Table "book" with 2 columns, one string and one integer, will be generated automatically
var id = api.Store(bean);
// Each saved bean has an ID, or primary key
Console.WriteLine(id);
#endif
/// **Read**
#if CODE
// Load by ID
bean = api.Load("book", id);
#endif
/// **Update**
#if CODE
// Make some edits
bean["title"] = "Learn LimeBean";
bean["release_date"] = new DateTime(2015, 7, 30);
bean["rating"] = "good";
// Save updated bean
// One new column ("release_date") will be added
// The type of column "rating" will be expanded from integer to string
api.Store(bean);
#endif
/// **Delete**
#if CODE
api.Trash(bean);
// Don't forget to close the connection
api.Dispose();
#endif
}
/// Doing so has several advantages:
///
/// - All strings prone to typos (bean kind and field names) are encapsulated inside.
/// - You get compile-time checks, IDE assistance and [typed properties](#typed-accessors).
/// - With [Lifecycle Hooks](#lifecycle-hooks), it is easy to implement [data validation](#data-validation) and [relations](#relations).
///
/// For [Custom Beans Classes](#custom-bean-classes), use method overloads with a generic parameter:
///
void Overloads(BeanApi api)
{
#if CODE
api.Dispense<Book>();
api.Load<Book>(1);
api.Find<Book>("WHERE rating > {0}", 7);
// and so on
#endif
}
