Database Scripts

OrmLite's database methods gives your scripts database connectivity to the most popular RDBMS's. To enable install the OrmLite NuGet package for your RDBMS and register it in your IOC:

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

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

var context = new ScriptContext { 
    ScriptMethods = {
        new DbScriptsAsync()
    }
}.Init();

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

• dbSelect - returns multiple rows
• dbSingle - returns a single row
• dbScalar - returns a single field value

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.

Opening different connections

By default the DbScripts will use the registered IDbConnectionFactory in the IOC or when Multi tenancy is configured it will use the connection configured for that request.

You can also specify to use a different DB connection using the namedConnection and connectionString arguments:

{{ sql |> dbSelect({ namedConnection }) }}
{{ sql |> dbSelect({ connectionString:sqlServerConnString, provider:"mssql" }) }}

dbFactory.RegisterConnection(namedConnection, connString, SqlServer2012Dialect.Provider);
dbFactory.RegisterProvider("mssql", SqlServer2017Dialect.Provider);

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 Sharp APIs. The URL The Source code link on the right shows the source code used to generate the Web Page or API Response:

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:

x web.sqlserver.settings

Populate local RDBMS with Northwind database

If you want to run this Sharp 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 Sharp App it doesn't need any compilation so after running the Rockwind Sharp 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:

x web.postgres.settings

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