Database Filters

OrmLite's database filters gives your templates database connectivity to the most popular RDBMS's. To enable install the OrmLite NuGet package for your RDBMS and register it in your IOC, e.g:

container.AddSingleton<IDbConnectionFactory>(() => 
    new OrmLiteConnectionFactory(connectionString, SqlServer2012Dialect.Provider));

Then to enable register either TemplateDbFiltersAsync if you're using either SQL Server, PostgreSQL or MySql, otherwise register the sync TemplateDbFilters to avoid the pseudo async overhead of wrapping synchronous results in Task results.

var context = new TemplateContext { 
    TemplateFilters = {
        new TemplateDbFiltersAsync()

Now your templates will have access to the available DB Filters where you can use the db* filters to execute sql queries:

The sql* filters are used to enable cross-platform RDBMS support where it encapsulates the differences behind each RDBMS and returns the appropriate SQL for the RDBMS that's registered.

DB Filter Examples

For an interactive example that lets you explore DB Filters checkout the Adhoc Querying use-case.

To explore a complete data-driven Web App built using Templates and DB Filters, checkout the Rockwind Website below which uses DB Filters to implement both its Web Pages and Web APIs. The URL The Source code link on the right shows the source code used to generate the Web Page or API Response:

Rockwind Website
Description URL Source Code
Customers /northwind/customers /northwind/customers.html
Employees /northwind/employees /northwind/employees.html
Products /northwind/products /northwind/products.html
Categories /northwind/categories /northwind/categories.html
Suppliers /northwind/suppliers /northwind/suppliers.html
Shipppers /northwind/shippers /northwind/shippers.html
Page Queries
Customers in Germany /northwind/customers?country=Germany /northwind/customers.html
Customers in London /northwind/customers?city=London
Alfreds Futterkiste Details /northwind/customer?id=ALFKI /northwind/customer.html
Order #10643 /northwind/order?id=10643 /northwind/order.html
Employee Nancy Davolio Details /northwind/employee?id=1 /northwind/employee.html
Chai Product Details /northwind/products?id=1 /northwind/products.html
Beverage Products /northwind/products?category=Beverages
Products from Bigfoot Breweries /northwind/products?supplier=Bigfoot+Breweries
Products containing Tofu /northwind/products?nameContains=Tofu
API Queries
All Customers
Accept HTTP Header also supported
Alfreds Futterkiste Details
As List
Customers in Germany
Customers in London
All Products /api/products.html
Chai Product Details
As List
Beverage Products
Products from Bigfoot Breweries
Products containing Tofu

Run Rockwind against your Local RDBMS

You can run the Rockwind Website against either an SQLite, SQL Server or MySql database by just changing which app.settings the App is run with, e.g:

dotnet web/app.dll ../rockwind/web.sqlserver.settings
Populate local RDBMS with Northwind database

If you want to run this Web App against your own Database run the northwind-data project against your database, e.g:

dotnet run sqlserver "Server=localhost;Database=northwind;User Id=test;Password=test;"

As Rockwind is a Web Template Web App it doesn't need any compilation so after running the Rockwind Web App you can modify the source code and see changes in real-time thanks to its built-in Hot Reloading support.

PostgreSQL Support

Due to PostgreSQL's automatic conversion of unquoted tables and fields to lower case and MySql not supporting double quotes for quoting symbols, it's not feasible to develop the same website that runs in both MySql and PostgreSQL unless you use sqlQuote to quote every column or table or are willing to use lowercase or snake_case for all table and column names. As a result we've developed an alternate version of Rockwind website called Rockwind VFS which quotes every Table and Column in double quotes so PostgreSQL preserves casing. The Rockwind VFS Project can be run against either PostgreSQL, SQL Server or SQLite by changing the configuration it's run with, e.g:

dotnet web/app.dll ../rockwind-vfs/web.postgres.settings

See the Filters API Reference for the full list of DB filters available.

made with by ServiceStack