This article will demonstrate the creation of basic CRUD stored procedures using the SourceMason code generation tool. Using a SourceMason code template I will generate a Get, Insert, Update and Delete stored procedure for a selected table. I will also create a template which will call the stored procedure template for each table in a database with an option to run each of the generated scripts.
SchemaExplorer is an addon for SourceMason (included in the install) that allows you to read the schema for any type of database. In this example I will be using Sql Server 2005 and the AdventureWorks sample database.
Below is the SchemaExplorer interface and table classes:
This template generates the stored procedures for a single table.
The following template directives are used:
<%@ CodeTemplate Language="C#" TargetLanguage="Sql" Description="CRUD Stored Procedures." %>
<%@ Property Name="SourceTable" Type="EquatorIT.SchemaExplorer.TableSchema" Description="Table that should be read." %>
The first directive specifies the language for any code in the template.
The second directive specifies a property of type EquatorIT.SchemaExplorer.TableSchema which will provide the table whose schema is to be read.
<script runat="template">
private string CreateKeyParam(int index)
{
TableColumnSchema primaryKeyColumn = SourceTable.PrimaryKey.Columns[index];
string result = string.Format("@{0}", primaryKeyColumn.Name);
result += " " + GetSQLTypeForColumn(primaryKeyColumn);
if (index < SourceTable.PrimaryKey.Columns.Count-1)
result += ",";
return result;
}
private string CreateWhereForKey(int index)
{
string result = string.Format("[{0}] = @{0}", SourceTable.PrimaryKey.Columns[index].Name);
if (index > 0)
result = "and " + result;
return result;
}
private string CreateColumn(int columnIndex)
{
string result = string.Format("[{0}]", SourceTable.Columns[columnIndex].Name);
if (columnIndex < SourceTable.Columns.Count-1)
result += ",";
return result;
}
private bool IsIdentityColumn(TableColumnSchema column)
{
if ((column.ExtendedProperties[ExtendedPropertyColumnType.IsIdentity.ToString()].Value != null) &&
((bool)column.ExtendedProperties[ExtendedPropertyColumnType.IsIdentity.ToString()].Value))
return true;
else
return false;
}
private string CreateSetIndentityColumn()
{
foreach(TableColumnSchema column in SourceTable.PrimaryKey.Columns)
{
if (IsIdentityColumn(column))
{
return string.Format("SET @{0} = SCOPE_IDENTITY()", column.Name);
}
}
return string.Empty;
}
private string GetSQLTypeForColumn(TableColumnSchema column)
{
string dbFieldType = column.DbType;
if ((column.Type == typeof(System.String)) || (column.Type == typeof(System.Char)))
{
if (column.MaxLength > 0)
dbFieldType += string.Format("({0})", column.MaxLength);
}
return dbFieldType.ToLower();
}
</script>
The following code/markup from the template body generates a Get stored procedure.
I use the SourceTable object to read all primary key columns for the table. The helper function CreateKeyParam does the formatting of each primary key column to create input parameters for the stored procedure.
<% GenerateDocumentHeader();%>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- Get Stored Procedure
-- Drop Existing Procedure
IF OBJECT_ID(N'[<%= SourceTable.Owner%>].[usp<%= SourceTable.Name%>_Get]') IS NOT NULL
DROP PROCEDURE [<%= SourceTable.Owner%>].[usp<%= SourceTable.Name%>_Get]
go
CREATE PROCEDURE [<%= SourceTable.Owner%>].[usp<%= SourceTable.Name%>_Get]
<%for (int i = 0; i < SourceTable.PrimaryKey.Columns.Count; i++) %>
<%{ %>
<%= CreateKeyParam(i)%>
<%} %>
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
<%for (int i = 0; i < SourceTable.Columns.Count; i++) %>
<%{ %>
<%= CreateColumn(i)%>
<%} %>
FROM
[<%= SourceTable.Owner%>].[<%= SourceTable.Name%>]
WHERE
<%for (int i = 0; i < SourceTable.PrimaryKey.Columns.Count; i++) %>
<%{ %>
<%= CreateWhereForKey(i)%>
<%} %>
go
------------------------------------------------------------------------------------------------------------------------
-- Generated By: Brett using SourceMason 1.0.4.0
-- Template: CrudSpSqlServer.smt
-- Date Generated: Friday, 11 May 2007
------------------------------------------------------------------------------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- Get Stored Procedure
-- Drop Existing Procedure
IF OBJECT_ID(N'[Sales].[uspSalesOrderHeader_Get]') IS NOT NULL
DROP PROCEDURE [Sales].[uspSalesOrderHeader_Get]
go
CREATE PROCEDURE [Sales].[uspSalesOrderHeader_Get]
@SalesOrderID int
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT
[SalesOrderID],
[RevisionNumber],
[OrderDate],
[DueDate],
[ShipDate],
[Status],
[OnlineOrderFlag],
[SalesOrderNumber],
[PurchaseOrderNumber],
[AccountNumber],
[CustomerID],
[ContactID],
[SalesPersonID],
[TerritoryID],
[BillToAddressID],
[ShipToAddressID],
[ShipMethodID],
[CreditCardID],
[CreditCardApprovalCode],
[CurrencyRateID],
[SubTotal],
[TaxAmt],
[Freight],
[TotalDue],
[Comment],
[rowguid],
[ModifiedDate]
FROM
[Sales].[SalesOrderHeader]
WHERE
[SalesOrderID] = @SalesOrderID
go
--Update Stored Procedure
-- Drop Existing Procedure
IF OBJECT_ID(N'[Sales].[uspSalesOrderHeader_Update]') IS NOT NULL
DROP PROCEDURE [Sales].[uspSalesOrderHeader_Update]
go
CREATE PROCEDURE [Sales].[uspSalesOrderHeader_Update]
@SalesOrderID int,
@RevisionNumber tinyint,
@OrderDate datetime,
@DueDate datetime,
@ShipDate datetime,
@Status tinyint,
@OnlineOrderFlag bit,
@SalesOrderNumber nvarchar(25) output,
@PurchaseOrderNumber nvarchar(25),
@AccountNumber nvarchar(15),
@CustomerID int,
@ContactID int,
@SalesPersonID int,
@TerritoryID int,
@BillToAddressID int,
@ShipToAddressID int,
@ShipMethodID int,
@CreditCardID int,
@CreditCardApprovalCode varchar(15),
@CurrencyRateID int,
@SubTotal money,
@TaxAmt money,
@Freight money,
@TotalDue money output,
@Comment nvarchar(128),
@rowguid uniqueidentifier,
@ModifiedDate datetime
AS
SET NOCOUNT OFF
UPDATE [Sales].[SalesOrderHeader] SET
[RevisionNumber] = @RevisionNumber,
[OrderDate] = @OrderDate,
[DueDate] = @DueDate,
[ShipDate] = @ShipDate,
[Status] = @Status,
[OnlineOrderFlag] = @OnlineOrderFlag,
[PurchaseOrderNumber] = @PurchaseOrderNumber,
[AccountNumber] = @AccountNumber,
[CustomerID] = @CustomerID,
[ContactID] = @ContactID,
[SalesPersonID] = @SalesPersonID,
[TerritoryID] = @TerritoryID,
[BillToAddressID] = @BillToAddressID,
[ShipToAddressID] = @ShipToAddressID,
[ShipMethodID] = @ShipMethodID,
[CreditCardID] = @CreditCardID,
[CreditCardApprovalCode] = @CreditCardApprovalCode,
[CurrencyRateID] = @CurrencyRateID,
[SubTotal] = @SubTotal,
[TaxAmt] = @TaxAmt,
[Freight] = @Freight,
[Comment] = @Comment,
[rowguid] = @rowguid,
[ModifiedDate] = @ModifiedDate
WHERE
[SalesOrderID] = @SalesOrderID
-- Return computed columns
SELECT
@SalesOrderNumber = [SalesOrderNumber],
@TotalDue = [TotalDue]
FROM
[Sales].[SalesOrderHeader]
WHERE
[SalesOrderID] = @SalesOrderID
go
-- Insert Stored Procedure
-- Drop Existing Procedure
IF OBJECT_ID(N'[Sales].[uspSalesOrderHeader_Insert]') IS NOT NULL
DROP PROCEDURE [Sales].[uspSalesOrderHeader_Insert]
go
CREATE PROCEDURE [Sales].[uspSalesOrderHeader_Insert]
@SalesOrderID int output,
@RevisionNumber tinyint,
@OrderDate datetime,
@DueDate datetime,
@ShipDate datetime,
@Status tinyint,
@OnlineOrderFlag bit,
@SalesOrderNumber nvarchar(25) output,
@PurchaseOrderNumber nvarchar(25),
@AccountNumber nvarchar(15),
@CustomerID int,
@ContactID int,
@SalesPersonID int,
@TerritoryID int,
@BillToAddressID int,
@ShipToAddressID int,
@ShipMethodID int,
@CreditCardID int,
@CreditCardApprovalCode varchar(15),
@CurrencyRateID int,
@SubTotal money,
@TaxAmt money,
@Freight money,
@TotalDue money output,
@Comment nvarchar(128),
@rowguid uniqueidentifier,
@ModifiedDate datetime
AS
SET NOCOUNT OFF
INSERT INTO [Sales].[SalesOrderHeader] (
[RevisionNumber],
[OrderDate],
[DueDate],
[ShipDate],
[Status],
[OnlineOrderFlag],
[PurchaseOrderNumber],
[AccountNumber],
[CustomerID],
[ContactID],
[SalesPersonID],
[TerritoryID],
[BillToAddressID],
[ShipToAddressID],
[ShipMethodID],
[CreditCardID],
[CreditCardApprovalCode],
[CurrencyRateID],
[SubTotal],
[TaxAmt],
[Freight],
[Comment],
[rowguid],
[ModifiedDate]
) VALUES (
@RevisionNumber,
@OrderDate,
@DueDate,
@ShipDate,
@Status,
@OnlineOrderFlag,
@PurchaseOrderNumber,
@AccountNumber,
@CustomerID,
@ContactID,
@SalesPersonID,
@TerritoryID,
@BillToAddressID,
@ShipToAddressID,
@ShipMethodID,
@CreditCardID,
@CreditCardApprovalCode,
@CurrencyRateID,
@SubTotal,
@TaxAmt,
@Freight,
@Comment,
@rowguid,
@ModifiedDate
)
SET @SalesOrderID = SCOPE_IDENTITY()
-- Return computed columns
SELECT
@SalesOrderNumber = [SalesOrderNumber],
@TotalDue = [TotalDue]
FROM
[Sales].[SalesOrderHeader]
WHERE
[SalesOrderID] = @SalesOrderID
go
--Delete Stored Procedure
-- Drop Existing Procedure
IF OBJECT_ID(N'[Sales].[uspSalesOrderHeader_Delete]') IS NOT NULL
DROP PROCEDURE [Sales].[uspSalesOrderHeader_Delete]
go
CREATE PROCEDURE [Sales].[uspSalesOrderHeader_Delete]
@SalesOrderID int
AS
SET NOCOUNT OFF
DELETE FROM [Sales].[SalesOrderHeader]
WHERE
[SalesOrderID] = @SalesOrderID
This template calls the CrudSpSqlServer.smt template for each table in the selected database. It will also exclude any tables in the dbo schema.
The following template directives are used:
<%@ CodeTemplate Language="C#" TargetLanguage="" Description="Create all CRUD stored procedures." %>
<%@ Register Name="CrudSpSqlserver" Template="CrudSpSqlserver.smt" %>
<%@ Property Name="SourceDatabase" Type="EquatorIT.SchemaExplorer.DatabaseSchema" Default="" Optional="False" Category="" Description="" Editor="" EditorBase="" Serializer="" %>
<%@ Property Name="OutputPath" Type="string" Default="" Optional="False" Category="" Description="Output Path for stored procedures" Editor="System.Windows.Forms.Design.FolderNameEditor"%>
<%@ Property Name="RunScript" Type="bool" Default="False" Optional="False" Category="" Description="Run generated script against database" %>
The second directive registers the CrudSpSqlserver template so that it can be called by this template.
The third directive specifies a property of type EquatorIT.SchemaExplorer.DatabaseSchema which will provide the database schema to be read.
The fourth directive specifies the output path for the generated stored procedures.
The fifth directive specifies whether or not the generated stored procedure scripts should be run against the selected database
<script runat="template">
public override void Render(System.IO.TextWriter writer)
{
try
{
writer.WriteLine("Generation for {0} started at {1}", SourceDatabase.Name, DateTime.Now);
foreach(TableSchema table in SourceDatabase.Tables)
{
if (table.Owner.Equals("dbo", StringComparison.CurrentCultureIgnoreCase))
continue;
writer.WriteLine();
writer.WriteLine("Generating stored procedures for {0}", table.FullName);
CrudSpSqlserver crudSpSqlserver = new CrudSpSqlserver();
crudSpSqlserver.SourceTable = table;
string outputPath = Path.Combine(@OutputPath, table.Name + ".sql");
using (StringWriter sw = new StringWriter())
{
crudSpSqlserver.Response.AddTextWriter(sw);
crudSpSqlserver.RenderToFile(outputPath, true);
writer.WriteLine("Generated stored procedures written to file {0}", outputPath);
if (RunScript)
{
ScriptResult scriptResult = SourceDatabase.SchemaProvider.ExecuteScript(SourceDatabase, sw.GetStringBuilder().ToString());
if (scriptResult.ScriptErrors.ErrorCount == 0)
{
writer.WriteLine("Script run succeeded.");
}
else
{
writer.WriteLine("Script failed with the following errors:");
foreach (ScriptError scriptError in scriptResult.ScriptErrors)
{
writer.WriteLine("Line number: {0} {1}", scriptError.LineNumber, scriptError.Message);
}
}
}
crudSpSqlserver.Response.RemoveTextWriter(sw);
}
}
writer.WriteLine();
writer.WriteLine("All stored procedures for {0} generated successfully.", SourceDatabase.Name);
}
catch (Exception e)
{
writer.WriteLine();
writer.WriteLine("Unable to generate CRUD stored procedures for {0} database.", SourceDatabase.Name);
}
}
</script>
As you can see it is simple to call the stored procedure template. I create an instance of it as follows:
CrudSpSqlserver crudSpSqlserver = new CrudSpSqlserver();
In this scenario I want to use the template to create a stored procedure script on disk as follows:
crudSpSqlserver.RenderToFile(outputPath, true);