Template Help

Mini SQL Query takes advantage of the NVelocity template engine to transform simple scripts into SQL scripts, C#/VB code, HTML mark-up or any other type of text output. The scripts have access to the database model making it a simple yet powerful tool. It is also possible to get user input for variables or import references to plug-ins for execution. The following examples use the Northwind SQL Compact Edition sample database.

Pre-Processor Directives

A Pre-processor directive is Mini SQL Query specific and start with “#@”. They are:
•	#@get <variable> (used to prompt the user for a value, currently just text)
•	#@set <property> <value> (currently just “extension”)
•	#@import-plugin <plugin-type-name>

Directives

Template directives are signalled with a hash (#) and are pure NVelocity syntax. Some directives are:
•	foreach / end
•	set
•	if / ifelse / else / end

Other more advanced directives include:
•	macro
•	include

Built in Variables

Variables are de-referenced with the format ${varable-name}
The following variables are built in.

${Host}
The host variable allows access to several helper functions and data, most notably the “Model”. The model is the database schema of the current connection. The model contains collections of tables and views with their respective columns. Each column has the primary/unique key and type information. If supported by the schema service of that database the column will contain foreign key information.
The “Model” hierarchy is below:
o	Model
§	Tables (list)
·	Schema (e.g. "dbo")
·	Name (e.g. "User")
·	FullName  (e.g. "dbo.User")
·	Columns (list)
•	Name (e.g. "int")
•	Nullable
•	DbType
o	Name
o	Summary (renders the type with respect to Length)
o	Length
o	Precision
o	Scale
o	SystemType (the .Net type, e.g. System.String)
•	IsKey
•	IsUnique
•	IsRowVersion (e.g. a Timestamp column)
•	IsIdentity
•	IsAutoIncrement
•	IsReadOnly (e.g. a auto increment or timestamp column)
•	IsWritable
•	HasFK (true if the column references another table)
•	ForeignKeyReference (applicable if HasFK is true)
o	ConstraintName
o	ReferenceTable
•	FullName etc (as Table)
o	ReferenceColumn
•	Name etc (as Column)

There is also a “Views” list with the same objects available for scripting.

Other “Host” functions are:
•	MachineName
•	UserName
•	Date(format) – where format is something like “d MMM yyyy” or “dd/MM/yy” etc
•	ToPascalCase(text) – converts “user_name” to “UserName” for example
•	ToCamelCase(text) – converts “FirstName” to “firstName” for example
•	(if you want more email me or better yet send a patch for the “TemplateHost” class!)

Sample - All Table Names

#foreach ($table in ${Host.Model.Tables})
Table: ${table.Name}
#end

Sample Output:

Table: Categories
Table: Customers
Table: Employees
Table: Order Details
Table: Orders
Table: Products
Table: Shippers
Table: Suppliers

Sample - CSharp Model.cs

A simple script to produce a C# class for each table:

#foreach ($table in $Host.Model.Tables)
public class ${table.Name}
{
#foreach ($c in $table.Columns)
	public ${c.DbType.SystemType} ${Host.ToPascalCase($c.Name)} { get; set; }
#end
}
#end

Sample Output:

public class Categories
{
	public System.Int32 CategoryID { get; set; }
	public System.String CategoryName { get; set; }
	public System.String Description { get; set; }
	public System.Byte[] Picture { get; set; }
}
public class Customers
{
	public System.String CustomerID { get; set; }
	public System.String CompanyName { get; set; }
	public System.String ContactName { get; set; }
	public System.String ContactTitle { get; set; }
	public System.String Address { get; set; }
	public System.String City { get; set; }
	public System.String Region { get; set; }
	public System.String PostalCode { get; set; }
	public System.String Country { get; set; }
	public System.String Phone { get; set; }
	public System.String Fax { get; set; }
}
...

Sample - Plugin Access.txt

You can import a plug-in to the script using it’s fully qualified type name and access the properties and methods for that plug-in. Note that the name of the plug-in variable in the template has the dots replaced with underscores (i.e. “MiniSqlQuery.PlugIns.CoreApplicationPlugIn” has a variable name of “MiniSqlQuery_PlugIns_CoreApplicationPlugIn”)

#@import-plugin MiniSqlQuery.PlugIns.CoreApplicationPlugIn

\${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginName}:
  "${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginName}"
\${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginDescription}
  "${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginDescription}"

Sample Output:

${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginName}:
  "Mini SQL Query Core"
${MiniSqlQuery_PlugIns_CoreApplicationPlugIn.PluginDescription}
  "Plugin to setup the core features of Mini SQL Query."