Quick Start Guide

Quick Start

There are three main ways to use SqlSharpener:

1. Create a T4 template that calls a template within SqlSharpener (easiest)
2. Create a T4 template that instantiates a SqlSharpener.MetaBuilder class and then uses the meta data to output code.
3. Create your own class library or console application that references SqlSharpener (coming soon)

Installation

Using NuGet, run the following command to install SqlSharpener from the Package Manager Console:

PM> Install-Package SqlSharpener

This will add SqlSharpener as a solution-level package. That means that the dll's do not get added to any of your projects.

Calling a SqlSharpener template from your T4 template

To use a template in SqlSharpener, such as the StoredProceduresTemplate, add a new T4 template (*.tt) file to your project and set its content as follows:

Tip
Ensure the version number in the dll path matches the version you are using.

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ assembly name="$(SolutionDir)\packages\SqlSharpener.1.0.10\tools\SqlSharpener.dll" #>
<#@ output extension=".cs" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="SqlSharpener" #>
<#
	// Specify paths to your *.sql files. Remember to include your tables as well! We need them to get the data types.
	var sqlPaths = new List<string>();
	sqlPaths.Add(Host.ResolvePath(@"..\SimpleExample.Database\dbo\Tables"));
	sqlPaths.Add(Host.ResolvePath(@"..\SimpleExample.Database\dbo\Stored Procedures"));

	// Set parameters for the template.
	var session = new TextTemplatingSession();
	session["outputNamespace"] = "SimpleExample.DataLayer";
	session["procedurePrefix"] = "usp_";
	session["sqlPaths"] = sqlPaths;

	// Generate the code.
	var t = new SqlSharpener.StoredProceduresTemplate();
    t.Session = session;
	t.Initialize();
	this.Write(t.TransformText());
#>

A working example of this code can be found in the examples folder of this project. As of the writing of this, there is only one template in SqlSharpener, but more will be added.

Using SqlSharpener classes in your T4 template

To use the classes in SqlSharpener directly in your T4 template so you can generate your own classes, such as Entity Framework Code-First Entities, add a new T4 template (*.tt) file to your project and set its content as follows:

<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ assembly name="$(SolutionDir)\packages\SqlSharpener.1.0.10\tools\SqlSharpener.dll" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="SqlSharpener" #>
<#@ output extension=".cs" #>
<#
	// Ensure the SqlSharpener version number above matches the version you installed!

	// Create a new MetaBuilder
	var meta = new MetaBuilder();

	// Specify paths to your *.sql files. Remember to include your tables as well! We need them to get the data types.
	meta.SqlPaths.Add(Host.ResolvePath(@"..\SimpleExample.Database\dbo\Tables"));
	meta.SqlPaths.Add(Host.ResolvePath(@"..\SimpleExample.Database\dbo\Stored Procedures"));
#>
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace SimpleExample.EntityFrameworkCodeFirst
{
	public partial class TaskContext : DbContext
	{
		public TaskContext(): base()
		{
		}
<# foreach(var tbl in meta.Tables){ #>
		public DbSet<<#=tbl.Name#>> <#=tbl.Name#> { get; set; }
<# } #>
	}

<# foreach(var tbl in meta.Tables){ #>

	[Table("<#=tbl.Name#>")]
	public partial class <#=tbl.Name#>
	{
<# foreach(var col in tbl.Columns){ #>

<# if(col.IsPrimaryKey) { WriteLine("		[Key]"); } #>
<# if(!col.IsNullable) { WriteLine("		[Required]"); } #>
<# if(col.Length > 0) { WriteLine("		[MaxLength({0})]", col.Length); } #>
<# if(col.IsIdentity) { WriteLine("		[DatabaseGenerated(DatabaseGeneratedOption.Identity)]"); } #>
		public <#=col.DataTypes[TypeFormat.DotNetFrameworkType]#> <#=col.Name#> { get; set; }
<# foreach( var parent in col.ParentRelationships){ #>
		[ForeignKey("<#=parent.Columns.First()#>")]
		public virtual <#=parent.TableOrView#> <#=parent.TableOrView#> { get; set; }
<# } #>
<# foreach( var child in col.ChildRelationships){ #>
		public virtual ICollection<<#=child.TableOrView#>> <#=child.TableOrView#> { get; set; }
<# }} #>
	}
<# } #>
}

A working example of this code can be found in the examples folder of this project. To learn more about the model that the SqlSharpener MetaBuilder creates, see the API Reference.