Adhoc Querying

The same qualities that make #Script great at querying State of a running .NET App also makes it excel at executing adhoc queries against providers which allow free text queries like OrmLite's Database Scripts which enables access to its Dynamic Result Set APIs:

Tip: textDump generates GitHub Flavored Markdown tables which can be embedded in Markdown to generate HTML tables or you can use htmlDump instead to generate HTML tables directly.

Register DB Scripts

To access OrmLite's Database Scripts install the OrmLite NuGet package for your RDBMS then add the DbScriptsAsync to your ScriptContext and register its required IDbConnectionFactory dependency in its IOC, e.g. for SQL Server: 

var context = new ScriptContext {
    ScriptMethods = {
        new DbScriptsAsync(),
    }
};
context.Container.AddSingleton<IDbConnectionFactory>(() => new OrmLiteConnectionFactory(
    connectionString, SqlServer2012Dialect.Provider));
context.Init();
If using ServiceStack's SharpPagesFeature it's Container has already been reassigned to use ServiceStack's Funq IOC so it only needs to be registered once in ServiceStack's IOC.

Always protect free text APIs

If you're exposing script methods enabling a free text API against a production database it should never be accessible by untrusted parties so you'll want to at a minimum ensure Services are protected with the [Authenticate] attribute so it's only available to Authenticated Users and ideally configure it to use a Read Only connection, e.g. for SQLite: 

container.Register<IDbConnectionFactory>(c => new OrmLiteConnectionFactory(
    $"Data Source={filePath};Read Only=true", SqliteDialect.Provider));

Populating the database

The database queried above was populated in the AppHost where it re-uses the LINQ data sources to create and populate an In Memory SQLite database on Startup: 

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(":memory:", SqliteDialect.Provider));

using (var db = container.Resolve<IDbConnectionFactory>().Open())
{
    db.CreateTable<Order>();
    db.CreateTable<Customer>();
    db.CreateTable<Product>();
    TemplateQueryData.Customers.Each(x => db.Save(x, references:true));
    db.InsertAll(TemplateQueryData.Products);
}

Order Report Example

The combination of features in the new Templates makes easy work of typically tedious tasks, e.g. if you were tasked to create a report that contained all information about a Northwind Order displayed on a single page, you can create a new page at:

packed with all Queries you need to run and execute them with a DB Script and display them with a HTML Scripts:

<!--
title: Order Report
-->

{{ `SELECT o.Id, OrderDate, CustomerId, Freight, e.Id as EmployeeId, s.CompanyName as ShipVia, 
           ShipAddress, ShipCity, ShipPostalCode, ShipCountry
      FROM ${sqlQuote("Order")} o
           INNER JOIN
           Employee e ON o.EmployeeId = e.Id
           INNER JOIN
           Shipper s ON o.ShipVia = s.Id
     WHERE o.Id = @id` 
  |> dbSingle({ id }) |> to => order }}

{{#with order}}
  {{ "table table-striped" |> to => className }}
  <style>table {border: 5px solid transparent} th {white-space: nowrap}</style>
  
  <div style="display:flex">
      {{ order |> htmlDump({ caption: 'Order Details', className }) }}
      {{ `SELECT * FROM Customer WHERE Id = @CustomerId` 
         |> dbSingle({ CustomerId }) |> htmlDump({ caption: `Customer Details`, className }) }}
      {{ `SELECT Id,LastName,FirstName,Title,City,Country,Extension FROM Employee WHERE Id = @EmployeeId` 
         |> dbSingle({ EmployeeId }) |> htmlDump({ caption: `Employee Details`, className }) }}
  </div>

  {{ `SELECT p.ProductName, ${sqlCurrency("od.UnitPrice")} UnitPrice, Quantity, Discount
        FROM OrderDetail od
             INNER JOIN
             Product p ON od.ProductId = p.Id
       WHERE OrderId = @id`
      |> dbSelect({ id }) 
      |> htmlDump({ caption: "Line Items", className }) }}
{{else}}
  {{ `There is no Order with id: ${id}` }}
{{/with}}

This will let you view the complete details of any order at the following URL:

SQL Studio Example

To take the ad hoc SQL Query example even further, it also becomes trivial to implement a SQL Viewer to run ad hoc queries on your App's configured database.

The Northwind SQL Viewer above was developed using the 2 #Script Pages below:

/northwind/sql/index.html

A Sharp Page to render the UI, shortcut links to quickly see the last 10 rows of each table, a <textarea/> to capture the SQL Query which is sent to an API on every keystroke where the results are displayed instantly:

<h2>Northwind SQL Viewer</h2>

<textarea name="sql">select * from "Customer" order by Id desc limit 10</textarea>
<ul class="tables">
  <li>Category</li>
  <li>Customer</li>
  <li>Employee</li>
  <li>Order</li>
  <li>Product</li>
  <li>Supplier</li>
</ul>

<div class="preview"></div>
<style>/*...*/</style>

<script>
let textarea = document.querySelector("textarea");
let listItems = document.querySelectorAll('.tables li');
for (let i=0; i<listItems.length; i++) {
  listItems[i].addEventListener('click', function(e){
    var table = e.target.innerHTML;
    textarea.value = 'select * from "' + table + '" order by Id desc limit 10';
    textarea.dispatchEvent(new Event("input", { bubbles: true, cancelable: true }));
  });
}
// Enable Live Preview of SQL
textarea.addEventListener("input", livepreview, false);
livepreview({ target: textarea });
function livepreview(e) {
  let el = e.target;
  let sel = '.preview';
  if (el.value.trim() == "") {
    document.querySelector(sel).innerHTML = "";
    return;
  }
  let formData = new FormData();
  formData.append("sql", el.value);
  fetch("api", {
    method: "post",
    body: formData
  }).then(function(r) { return r.text(); })
    .then(function(r) { document.querySelector(sel).innerHTML = r; });
}
</script>

/northwind/sql/api.html

All that's left is to implement the API which just needs to check to ensure the SQL does not contain any destructive operations using the isUnsafeSql DB filter, if it doesn't execute the SQL with the dbSelect DB Filter, generate a HTML Table with htmlDump and return the partial HTML fragment with return:

{{#if isUnsafeSql(sql) }} 
    {{ `<div class="alert alert-danger">Potentially unsafe SQL detected</div>` |> return }}
{{/if}}

{{ sql |> dbSelect |> htmlDump |> return }}

Live Development Workflow

Thanks to the live development workflow of #Script Pages, this is the quickest way we've previously been able to implement any of this functionality. Where all development can happen at runtime with no compilation or builds, yielding a highly productive iterative workflow to implement common functionality like viewing ad hoc SQL Queries in Excel or even just to rapidly prototype APIs so they can be consumed immediately by Client Applications before formalizing them into Typed ServiceStack Services where they can take advantage of its rich typed metadata and ecosystem.

← Introspect State

made with ❤ by ServiceStack