database_storedprocedures_mssql.atcs

.net samples

<#@ Imports
using System;
using TwoLKit.nTierBuilder.Api.DbDom;
##>
<#@ InstanceMembers
	private IDatabase Database
	{
		get { return (IDatabase)Environment["Database"]; }
	}

	public override string RelativeFilePath
	{
		get { return Database.CodeName + "_MSSQL.sql"; }
	}
	
	public override bool Enabled
	{
		get { return SharedUtils.GetGenerateMSSqlStoredProcedures(Database); }
	}
	
	private string GetSqlName(string value)
	{
		return "[" + value + "]";
	}
	
	internal static string GetGetAllProcName(ITable table)
	{
		return "_" + table.CodeName + "_GetAll";
	}
	
	internal static string GetGetByKeyProcName(ITable table)
	{
		return "_" + table.CodeName + "_GetByPrimaryKey";
	}
	
	internal static string GetInsertProcName(ITable table)
	{
		return "_" + table.CodeName + "_Insert";
	}

	internal static string GetUpdateProcName(ITable table)
	{
		return "_" + table.CodeName + "_Update";
	}

	internal static string GetDeleteByKeyProcName(ITable table)
	{
		return "_" + table.CodeName + "_DeleteByPrimaryKey";
	}
	
	internal static string GetDeleteAllProcName(ITable table)
	{
		return "_" + table.CodeName + "_DeleteAll";
	}
	
	private bool GetHasPrimaryKey(ITable table)
	{
		return 0 < table.Columns.GetPrimaryColumns().Length;
	}
	
	private string GetSqlParameterName(IColumn column)
	{
		return "@" + column.CodeName;
	}

	private string GetSqlParameterType(IColumn column)
	{
		return SharedUtils.GetMSSqlParameterType(column);
	}
##>
<#	foreach(ITable table in Database.Tables)
	{ ##>
----------------------------------------------------------
-- Stored procedures for the '<#= table.Name #>' table.
----------------------------------------------------------

-- Drop the '<#= GetGetAllProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetGetAllProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetGetAllProcName(table) #>
GO

-- Gets all records from the '<#= table.Name #>' table.
CREATE PROCEDURE <#= GetGetAllProcName(table) #>
AS
	SELECT * FROM <#= GetSqlName(table.Name) #>
GO
<#	if(GetHasPrimaryKey(table))
	{ ##>

-- Drop the '<#= GetGetByKeyProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetGetByKeyProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetGetByKeyProcName(table) #>
GO

-- Gets a record from the '<#= table.Name #>' table using the primary key value.
CREATE PROCEDURE <#= GetGetByKeyProcName(table) #>
<#		bool firstGetByPKItem = true;
		foreach(IColumn column in table.Columns.GetPrimaryColumns())
		{ 
			if(firstGetByPKItem)
				firstGetByPKItem = false;
			else
			{ ##>,
<#			} ##>
	<#= GetSqlParameterName(column) #> <#= GetSqlParameterType(column) ##>
<#		} #>
AS
	SELECT * FROM <#= GetSqlName(table.Name) #> WHERE
<#		firstGetByPKItem = true;
		foreach(IColumn column in table.Columns.GetPrimaryColumns())
		{
			if(firstGetByPKItem)
				firstGetByPKItem = false;
			else
			{ ##> AND
<#			} ##>
		<#= GetSqlName(column.Name) #> = <#= GetSqlParameterName(column) ##>
<#		} #>
GO
<#	} ##>

-- Drop the '<#= GetInsertProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetInsertProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetInsertProcName(table) #>
GO

-- Inserts a new record into the '<#= table.Name #>' table.
CREATE PROCEDURE <#= GetInsertProcName(table) #>
<#	bool firstInsertColumn = true;
	foreach(IColumn column in table.Columns.GetWriteableColumns())
	{
		if(firstInsertColumn)
			firstInsertColumn = false;
		else
		{ ##>,
<#		} ##>
	<#= GetSqlParameterName(column) #> <#= GetSqlParameterType(column) ##>
<#	} #>
AS
	INSERT INTO <#= GetSqlName(table.Name) #>
	(
<#	firstInsertColumn = true;
	foreach(IColumn column in table.Columns.GetWriteableColumns())
	{
		if(firstInsertColumn)
			firstInsertColumn = false;
		else
		{ ##>,
<#		} ##>
		<#= GetSqlName(column.Name) ##>
<#	} #>
	)
	VALUES
	(
<#	firstInsertColumn = true;
	foreach(IColumn column in table.Columns.GetWriteableColumns())
	{
		if(firstInsertColumn)
			firstInsertColumn = false;
		else
		{ ##>,
<#		} ##>
		<#= GetSqlParameterName(column) ##>
<#	} #>
	)
<#	if(null != SharedUtils.GetIdentityColumn(table))
	{ ##>
	SELECT @@IDENTITY
<#	} ##>
GO
<#	if(GetHasPrimaryKey(table))
	{ ##>

-- Drop the '<#= GetUpdateProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetUpdateProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetUpdateProcName(table) #>
GO

-- Updates a record in the '<#= table.Name #>' table.
CREATE PROCEDURE <#= GetUpdateProcName(table) #>
	-- The rest of writeable parameters
<#	foreach(IColumn column in table.Columns.GetWriteableNonPrimaryColumns())
	{ ##>
	<#= GetSqlParameterName(column) #> <#= GetSqlParameterType(column) #>,
<#	} ##>
	-- Primary key parameters
<#	bool firstUpdateColumn = true;
	foreach(IColumn column in table.Columns.GetPrimaryColumns())
	{ 
		if(firstUpdateColumn)
			firstUpdateColumn = false;
		else
		{ ##>,
<#		} ##>
	<#= GetSqlParameterName(column) #> <#= GetSqlParameterType(column) ##>
<#	} #>
AS
	UPDATE <#= GetSqlName(table.Name) #> SET
<#	firstUpdateColumn = true;
	foreach(IColumn column in table.Columns.GetWriteableNonPrimaryColumns())
	{ 
		if(firstUpdateColumn)
			firstUpdateColumn = false;
		else
		{ ##>,
<#		} ##>
		<#= GetSqlName(column.Name) #> = <#= GetSqlParameterName(column) ##>
<#	} #>
	WHERE
<#	firstUpdateColumn = true;
	foreach(IColumn column in table.Columns.GetPrimaryColumns())
	{
		if(firstUpdateColumn)
			firstUpdateColumn = false;
		else
		{ ##> AND
<#		} ##>
		<#= GetSqlName(column.Name) #> = <#= GetSqlParameterName(column) ##>
<#	} #>
GO

-- Drop the '<#= GetDeleteByKeyProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetDeleteByKeyProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetDeleteByKeyProcName(table) #>
GO

-- Deletes a record from the '<#= table.Name #>' table using the primary key value.
CREATE PROCEDURE <#= GetDeleteByKeyProcName(table) #>
<#		bool firstDeleteByPKColumn = true;
		foreach(IColumn column in table.Columns.GetPrimaryColumns())
		{ 
			if(firstDeleteByPKColumn)
				firstDeleteByPKColumn = false;
			else
			{ ##>,
<#			} ##>
	<#= GetSqlParameterName(column) #> <#= GetSqlParameterType(column) ##>
<#		} #>
AS
	DELETE FROM <#= GetSqlName(table.Name) #> WHERE
<#		firstDeleteByPKColumn = true;
		foreach(IColumn column in table.Columns.GetPrimaryColumns())
		{
			if(firstDeleteByPKColumn)
				firstDeleteByPKColumn = false;
			else
			{ ##> AND
<#			} ##>
		<#= GetSqlName(column.Name) #> = <#= GetSqlParameterName(column) ##>
<#		} #>
GO
<#	} // End if(GetHasPrimaryKey(table)) ##>

-- Drop the '<#= GetDeleteAllProcName(table) #>' procedure if it already exists.
IF EXISTS (SELECT name FROM sysobjects 
		WHERE name = '<#= GetDeleteAllProcName(table) #>' AND type = 'P')
	DROP PROCEDURE <#= GetDeleteAllProcName(table) #>
GO

-- Deletes all records from the '<#= table.Name #>' table.
CREATE PROCEDURE <#= GetDeleteAllProcName(table) #>
AS
	DELETE FROM <#= GetSqlName(table.Name) #>
GO

<#	} ##>