Working with a Party Model

Party Model Overview

Working with a Party Model implements relationships for parties (entities) such as organisations and people.  The super-type (organisation) is comprised of sub-types, i.e. people.  People may have different roles, e.g. manager (responsible for) or employee (reports to) and the organisation (employs).  Roles declare meaning when read in a direction, viz. left-to-right (Ltr) and right-to-left (Rtl). Party is the super-type entity entry that signifies the identity of an interest to the reference organisation, i.e. the one that implements the model.  Party is viewed relative to this vantage point and is the superior interacted with.  The next inter-actor follows a hierarchical model, i.e. forms a relationship (as party) as first inter-actor, e.g. organisation as employer to the person as employee.  Both are Parties and have a representation in the Party table with PartyId as the unique identifier.  These identity keys are used in a relationship table to create the relation: Acme Corp. (employer) — employs —> Engineer Joe (employee).

Party is a physical table in the database.  The PartyId is the primary key of the table, an identifier that is the foreign key to the party being modelled, e.g. organisation or person.

Party Relationship

Figure 1 – Party Relationship

The Party table with its associated Person and Organisation tables contain the business data, e.g. Figure 1 – Party Relationship. This Party structure is the basic relationship or root of the party model.

The Party Physical Data Model (Figure 2) implements the organisation (as Party) in the Party (primary key) to Organisation (foreign key/ Primary Key) in the relationship.  Also for (Person) Individual, being defined by PartyId (PK) to IndividualPartyId (FK/ PK).  The Primary Key of an Organisation is used in the foreign-key implementations of its reference data, e.g. Organisation Level OrgPartyId (FK/ PK).

Party Physical Data Model

Figure 2 – Party Physical Data Model

The model (Figure 2) has very little meaning other than indicating a very explicit entity, defined as a party; the power of the relationship lies in the association of people (individuals) to the organisation, i.e. to be the bill-of-material (2016 – Jean-Marc Reynaud) of the organisation. An individual who is part of the company has a role as employee, with a designation of function,e.g. Engineer.  Such a person could also be a line-manager, all of which requires the model to accommodate and extensible implementation. The root (internal) Organisation also has relationships with various other (external) Organisations, with their respective classifications and reference data modelled on the OrganisationPartyId as the PartyId foreign key, but primary key for the Organisation.

Two types of data are brought together, viz. the business data (relationship structures [taxonomies] for Party) and the metadata (reference/ lookup) data for the Organisation, People and other entities modelled with all other reference data-structures (objects) that relate.

According to Siebel in their description of The Party Model the model comprises a base table and extension tables, with external tables used to model the relationships between the Party tables.  “Typically, you associate each employee and partner user with one or more positions.  The employee or partner has only one active position at a time and automatically associated to one division and organisation, being associated with the active position.”

The business data leverages metadata, e.g. Role and Party type.  For example, a Party is defined (in the Party type) as either a person or an organisation.  The Role type describes the Peron and/ or the Organisation, e.g. Systems Analyst (role) for Person, or Public Company for Organisation.

For other entities (objects) in a business, e.g. Product, Events, Agreements, or Assets, these are modelled as objects and exposed to Part in a junction-table called: Party<Entity>.  For example, PartyProduct, PartyEvents, PartyAgreements, or PartyAssets, following on from the previous example.  The Party consequently “has a role with” the reciprocal Party<Entity>, e.g. a Product is linked to a Party by PartyId and ProductId (with an associated PartyProductRole) and Product defined in an associated data model that uses ProductId as the unique product identifier.

Building the Organisation and People Party Model


Party is a base entity type relating to the objective organisation modelling its business.  A Party always represents a single person or a group, e.g. a company that can be viewed in a business as a single entity (Ref: Configure Siebel Applications).  These entities (according to Oracle) could be:

  • Person/ Contact
  • User
  • Employee
  • Position
  • Partner User
  • Account
  • Division
  • Organisation
  • Household
  • User Group
  • Access List

These parties are defined by a Party Type field, a code that uniquely identifies the sub-type discriminator for a party, as described in the list above.  Extension tables are associated with the basic Party record to provide differentiation.  Party joins extension tables explicitly.  The business data must be modelled in a Role Type Relationship table to create role-pairs that model first and second inter-actors.  This defines the type of relationships possible between Parties. For example, an Organisation implements many job roles, so the hierarchy of the role-pairs is to create the bill-of-materials of one organisation with its many job profiles.  The organisation also employs an employee to fill a job profile.  All these reference data entities (before relating them) must be created.

Define Party Types

Any Party is defined by its sub-type discriminator, describing the Party, what it is, viz. Organisation, Person, Position, Capbility, etc.  The PartyTypeCode (an Integer number can also be used) is the primary key to the foreign key in the Party table.

Party Role Type

A party requires fulfilling a role within a business context.  Each Party Type (person or organisation) plays a business contextual role in relation to the extensions of a business, e.g. Product, Events, Agreements, or Assets (as described in the overview).  Consequently, in the extension’s junction table, an explicit Role Type Code would join the Role Type to the extension, e.g. Asset to indicate what role the connected Party plays in that context.  For example: a company Product is joined to a Party in the Product Party junction table by relating PartyId and ProductId with a Party Product Role Code and a set of effective-from and effective-to dates to version the relationship.  This could be that Acme Corp (an internal organisation) has an asset called an impact drilling machine in the role of owner.


Reference data for an Organisation can be carried at this level while more business modelling, to extend the configuration such as Address, Name History, SIC History, and Type, can be done in the extension tables.  The Organisation Identifier (company code or some legal registration number) can be incorporated to uniquely identify the entity.  Whatever information is presented at this level should only change or become defunct if the Organisation is terminated or becomes redundant.

Modelling an Organisation requires the Organisation and Division structures. In the Siebel CRM model and Organisation is a Division (2015 Gerhard Hermann), designated as an Organisation and can be hierarchical such as Divisions.  A Division is an internal unit within the Organisation/ Company, e.g. a regional operating division of the company.  The company is modelled directly on a Division and not the Organisation.

Attributes or the Organisation, irrespective of the Organisation Type, can be added to this level (The Data Model Resource Book, Volume 1 – Len Silverston 2001).  It reduces the number of tables (containing business data) modelling the structures of Organisation.  At this

Organisation Type

Before an Organisation can be created, the type should be known.  Basically, an organisation can be Internal (part of the reference organisation) or External, an organisation with whom the reference organisation has dealings and interactions.  This break-down can take on the form Legal and Informal Organisations (Data Structure Patterns Topic 4.3), or whatever the reference Organisation has decreed how an Organisational model should look like.  The hierarchy must be sensible and usable to the organisation.  The delineation between internal and external organisations can also be achieved with a simple boolean flag to simplify the model and attach extension reference data at the Organisation level. According to Teradata: “A well-understood big picture of the organization needs to be captured and communicated in the form of a model.”


Visual Studio 2013 Database Project: Item Templates with VSIX

Visual Studio 2013 Database Project: Database Item Templates with VSIX


Strictly speaking VSIX is not a database project or related in kind, but by virtue of Item Templates.  With VSIX projects and the TemplateBuilder (NuGet) package, templates can be built from the contents of an ItemsTemplates folder.  The latter (if not created by TemplateBuilder) should be manually created as ItemTemplates folder as an item of the VSIX project.

NOTE: SQL Server (database) templates are not supported in Visual Studio 2013 VSIX and requires a manual effort to install on a developer’s IDE.  As indicated by the error item below, if the database files added to a VSIX project are changed to have a vstemplate file extension the compiler (on build) should issue this error saying that only CSharp and VBasic templates. Similarly if a Reference is added in the Asset tab of the manifest, the error The targer “TemplateProjectOuputGroup” does not exist in the project” is presented.

VS Database Template Support

VS Database Template Support

VSIX Template

The VSIX project template is available from the Visual Studio 2013 SDK and found under the menu: [Visual C#] >> Extensibility >> VSIX Project.

Template Builder

The TemplateBuilder package is installed from the Package Manager console or from Visual Studio: Tools >> NuGet Package Manager >> Package Manger Console.  Once installed, template are built to the folder: <Drive:>:\Users\<User>\Documents\Visual Studio 2013\Templates\ItemTemplates and <Drive:>\Users\<User>\Documents\Visual Studio 2013\Templates\ProjectTemplates.

NuGet Package Manager

Download and install the NuGet Package Manager for Visual Studio 2013.  It has a VSIX extension but installs by double-click and run.


SideWaffle is a template pack that adds a selection of project and item templates to Visual Studio, including enabling templates to use for creating new ones. A VSIX project generates an experimental invocation of Visual Studio in which to test these templates so that the real instance is not affected.  To configure templates require a work-flow that is enabled by a separate application (that you create) to populate template and custom parameters when creating a new item.  This article, for now, mentions it but is not using it to create templates.

Create a New VSIX Project for Template Deployment

In order to author customised solutions, projects, code-snippets, and templates, a VSIX project is required.  Depending on the user-environment set-up for Visual Studio, file paths may vary slightly.

Create a new project:

  1. Follow the path: File >> New Project >> [Custom Path] >> [Visual C#] >> extensibility >> VSIX Project
  2. Provide a name for the project and check the solution details
  3. Fill out the VS VSIX Manifest with the appropriate details (reference)
  4. Install the TemplateBuilder package.
VS VSIX Manifest

VS VSIX Manifest

Installing TemplateBuilder

This installation requires the Package Manager Console (NuGet):

  • Follow the path: Tools >> NuGet Package Manager >> Package Manager Console.
  • Type Install-Package TemplateBuilder -Pre at the PM> prompt and hit [Enter].  NOTE: Internet access to is required.
VS Package Manager Console

VS Package Manager Console

The TemplateManager installation responds favourably on completion of the install, if it succeeds.  NOTE: The NuGet package is downloaded into the file location: <Drive>:\Users\<user name>\AppData\Local\NuGet\Cache and in the event of not having the default Internet location ( available, Visual Studio looks here for whatever the package is being installed.

VS NuGet Installation of TemplateBuilder

VS NuGet Installation of TemplateBuilder

The installation process deposits an example template into: <Drive:>\Users\<User>\Documents\Visual Studio 2013\Templates\ProjectTemplates.  NOTE: there may not actually be an example template in this folder.

Create a Template Solution and Project

The Visual Studio Solution should contain the VSIX project as well as other projects, i.e. those used in which to create the templates.  For example, to create database templates a database project is added to the solution and the corresponding templates with parameters created in there.  This article uses the template created in a previous post (Visual Studio 2013 Database Project: Create a Stored Procedure Item Template) to illustrate how the VSIX project functions in this respect.

There are two methods:

  1. Exporting and importing a template (zip file)
  2. Referencing the template source project from the VSIX project where they are in the same solution

The latter (point 2) does not work for database templates

Exporting the Template

The template (or stored procedure created in the templates project) is created by exporting the necessary file (the actual stored procedure and any referenced files in the case of a project or c# type template) to the folder: <Drive:>\Users\<User>\Documents\Visual Studio 2013\My Exported Templates.  Follow the next steps to affect this:

  • Create, for example a stored procedure template (reference the above link on how to do that) an File >> Export Template >> Item Template, select the correct project, i.e. the database project containing the stored procedure created.
  • Select the correct stored procedure and complete the wizard.  The output template is locatable in the folder: <Drive:>\Users\<User>\Documents\Visual Studio 2013\My Exported Templates in a file called: <Template Name>.zip.

NOTE: Edit the MyTemplate.vstemplate file (contained inside the zip file) in Visual Studio and add an element TemplateGroupID (depicted in the code below) between the templateData tags to help with the filtering of template in the New Item dialog (reference).

VS Template Projects and Item Template

VS Template Projects and Item Template


The Item Template (Stored Procedure) is added to the VSIX project.  Use the .vsixmanifest file and add an Itemtemplate item to the Assets tab. Add a:

  • New item,
  • Pick ItemTemplate and
  • Point the File Location to the folder where the copied template zip file has been placed.

NOTE: The path could be: :\Users\\Documents\Visual Studio 2013\Templates\ItemTemplates\\Database\Programmability, but it is up to choice.  However, Visual Studio, on starting up, reads from this location on a user-instance, i.e. local machine.  The project is appended with a new folder, i.e. ItemTemplates and the zip file is appended.

On the  the property of the newly added template (zip) file, set the property Copy to Output Directory is set tot Copy Always.

NOTE: To inspect the XML code of the project file: right-click >> Remove and then right-click >> edit) reveals this setting in its XML structure, as depicted below.

    "packages.config" />
    "Properties\template-builder.props" />

NOTE: Turn off the Build and Deploy options in the Configuration Manager for the VSIX project.

Using a Reference to the Template Project in the same Solution

A reference to the template project (the one where the stored procedure is authored) is added to the Reference of the VSIX project by using the source.extension.vsixmanifest file’s Assets tab, as before.  But, in this case, the reference is to a project in the same solution.  The fields [Type],

[Source], and [Project] are completed, with project being the name of the said template project.  This adds a reference to the VSIX project.

Set the Properties of the reference (see how), especially the VSIX Sub Path that determines where the template is installed on the menu-tree of the New Item menu function.

NOTE: Visual Studio does not provide a project template for creating database objects, so it seems the export method is the only way in which to get the zip file into the VSIX for deployment.  This aspect may be resolvable by creating the zipped files in a database project and let the project zip them like the wizard and place in the appropriate folder.  Not yet sure how to do this.

Build the Project and Install the Templates

The VSIX project is built and it copies the template zip file to the output folder: \Users\\Documents\Visual Studio 2013\Projects\\\bin\Debug as a .vsix (installable) file along with a TemplateBuilder.dll and the folder structure (Visual Studio) that contains the actual template file.

NOTE: For developer projects, this installation file should be made available on a network shared drive and installed as updates are made, or from a Private Gallery reference) that notifies users via RSS feeds of new extensions.  Alternatively the same file can be uploaded to the Visual Studio Gallery for community consumption and addition.

file in the VSIX project to set the [Assets] by creating a new reference to an ItemTemplate of a project (the one containing the stored procedure template) that also adds it to the References of the VSIX project. Here the Properties of the added assembly can be altered to achieve a variety of things. See the screen image (below) for a visual explanation of what is described.

VS VSIX Template Database Item Folder Location Reference

VS VSIX Template Database Item Folder Location Reference

Executing the <Name>.vsix (installable) file invokes all the things that were set in the manifest to install the template(s).  These are available in Visual Studio and can be inspected via: Tools >> Extensions & Updates >> Templates
, illustrated below.

VS Extensions and Updates Templates folder

VS Extensions and Updates Templates folder

Using the newly installed Templates

After installing the VSIX template package and verifying the extension is in Visual Studio, the template can be used from which to create Projects or Items.  During the installation the templates are deployed to a random folder: %LocalAppData%\Microsoft\VisualStudio\12.0\Extensions and by virtue of %LocalAppData% it only applies to the current user.  Alternatively, setting the manifest to AllUsers=true the extension is installed under Common7\IDE\Extensions (where VS is installed) and appears to all users on the computer (reference).

Visual Studio 2013 Database Project: Working the Project LocalDbs

Visual Studio 2013 Database Project: Working the Project LocalDbs


Visual Studio (VS) has evolved much to what it is capable of today for database development and deployment.  With the addition of a much better table create facility Visual Studio is the development tool of choice for the database developer.  SQL Server Management Studio still has its place as a light free tool, but for serious database development with Team Foundation Integration, Visual Studio is the tool of choice.

Using Local Databases

Visual Studio creates local databases when a database project is created, viz.

  1. MSSQLLocalDb — SQL 2014 local db
  2. ProjectsV12 — created by SQL Server Data Tools (SSDT)
  3. V11.0 — SQL 2013 local db
VS SQL Server Object Explorer

VS SQL Server Object Explorer

These local database instances are located on a file location on the local drive of the computer that runs Visual Studio at: C:\Users\hendriekusr\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances.

The server name consequently is: (localdb)\ProjectV12.  Details about these installed databases can be obtained by running the SQlLocalBD.exe (located in C:\Program Files\Microsoft SQL Server\110\Tools\Binn\) against the name of the database, e.g. info ProjectsV12.

VS Local Database Information

VS Local Database Information

Use the Instance pipe name (from above execution) to connect to a local database from SQL Server Management Studio (SSMS), as depicted below.

SSMS Local Db Connection Dialog

SSMS Local Db Connection Dialog

A connection (to view database objects) is possible from VS and SSMS with the latter using the pipe connection string.  The SSMS screen (below) illustrates how the connection is made to the localDB as well as a Stored Procedure created from a SSMS template that was added to the database.  The next article [Create a Stored Procedure Item Template] illustrates how this same SSMS Stored Procedure template is created for Visual Studio.

SSMS Connection to LocalDB with Stored Procedure from Template

SSMS Connection to LocalDB with Stored Procedure from Template

Previous Article (2) [Create Database Project Folders]. . . Next Article (4) [Create a Stored Procedure Item Template]

Visual Studio 2013 Database Project: Create a Stored Procedure Item Template

Visual Studio 2013 Database Project-Create a Stored Procedure Item Template


Using Visual Studio for database development requires that database objects like stored procedures be created, amongst other items such as tables, view, triggers, etc.  It is advantageous if creating an item that the proposed script is a template that already conforms to the company’s standards, by providing a boiler-plate script that speeds up the developer’s options.  Even better would be if these templates are available to all developers as they check items in-and-out of Team Foundation Server.  Bear in mind that a particular organisation might have a single development office or location, but others may be geographically dispersed.  It would be expedient if all of them are singing from the same hymnal.

This article explores the basics of Visual Studio Templates for database development.  Using this phrase as a search term, an Internet search engine divvies up a few articles in support.  One of which is called An Overview of Templates for Database and Server Objects (Microsoft).  It suggests that existing templates can be customised to affect all templates used by the team.  Customisable project item/ stubs accelerate development by providing the developer with a head-start. Templates are comprised of project and item templates.  Every time a user opens a menu item in Visual Studio to create something, a template is invoked, e.g. New Project, or Add New Item.

Templates exist in the user community (How to Add Items Using Online Item Templates) or a VSIX project with SideWaffle (Code4Fun), but these aren’t discussed here.  To Illustrate the concept, a template for a Stored Procedure is created and added as an Item Template to the local developer machine.  This is absorbed into Visual Studio and provides the developer with a new item in the Add New Item option.

Creating the Template

A Stored Procedure (via Add New Item ) in Visual Studio (VS) and for that matter in Microsoft SQL Server Management Studio (SSMS) is pretty uneventful.  Therefore, should a developer create quite a few items, the work required to customise these to the company standard absorbs unnecessary time and effort.  A template should make this much easier.

Either use VS or SSMS to create a stored procedure that conforms to all general company standards.  This one to be used in Visual Studio, so the usage between SSMS and VS is different from a parameterisation perspective.

In SSMS the code-block (below) illustrates how parameter are coded and then on invocation provided by Ctrl+Shift+M.  In Visual Studio this is done through an XML configuration and only what the respective dialog requests on New Item is user-provided, e.g. the project or object name.

CREATE PROCEDURE	[<Schema_Name, sysname, Schema_Name>].[<Procedure_Name, SYSNAME, ProcedureName>] 
					<@Param1, SYSNAME, @p1> <Datatype_For_Param1, , INTEGER> = <Default_Value_For_Param1, , 0>
				,	<@Param2, SYSNAME, @p2> <Datatype_For_Param2, , INTEGER> = <Default_Value_For_Param2, , 0>
				,	<@Param3, SYSNAME, @p3> <Datatype_For_Param2, , INTEGER OUTPUT>
WITH EXECUTE AS [CALLER	| SELF | 'user_name'] -- Default: CALLER				

The flowing code-block represents the Visual Studio equivalent of the obove code for the same purpose.  The $fileinputname$ is the file name provided by the developer when creating a new stored procedure.  All the other parameters are configured in the Item Template’s XML file.  Therefore, the XML configuration should prescribe the purpose of the stored procedure, I.e. suit exactly what it is being created for. 

CREATE PROCEDURE	[$SchemaName$].[$fileinputname$] 
					$ProcParam1$	$ProcParam1DataType$	=	$ProcParam1Value$
				,	$ProcParam2$	$ProcParam2DataType$	=	$ProcParam2Value$
				,	$ProcParam3$	$ProcParam3DataType$		$ProcParam3Type$
WITH EXECUTE AS [CALLER	| SELF | 'user_name'] -- Default: CALLER <-- make a selection in the executing principle context.				

A Visual Studio Item Template for a Custom Stored Procedure

The provided template fast-tracks a developer and provides a consistent standard for every stored procedure created base on this template.  This particular standard is comprised of a few sections, viz.

  • Create with execution principal context
  • Flower box containing the abstract and owner details
  • Initial proc-wide directives, e.g. NOCOUNT, EXACABORT, and TRANSACTION ISOLATION
  • Error-handling variables (for RAISERROR)
  • Local variable declaration & initialisation
  • Main query execution block wrapped in TRY. . . CATCH and transaction
  • CATCH block with detailed error handling with XACT_STATE
  • Return value for application event handling
  • Directive switch to system defaults
  • Metadata section for object details (metadata) when it’s built in the database
CREATE PROCEDURE	[$SchemaName$].[$fileinputname$] 
					$ProcParam1$	$ProcParam1DataType$	=	$ProcParam1Value$
				,	$ProcParam2$	$ProcParam2DataType$	=	$ProcParam2Value$
				,	$ProcParam3$	$ProcParam3DataType$		$ProcParam3Type$
WITH EXECUTE AS [CALLER	| SELF | 'user_name'] -- Default: CALLER <-- make a selection in the executing principle context.				
/* ==================================================================================================================================
	ABSTRACT:	This procedure executes for $projectname$ and. . . 

	OWNER:		$ItemOwner$

	<NOTE: to developer, complete the metadata at the end of this procedure script.>

											<< See bottom of item for details >>
=================================================================================================================================== */
	-- --------------------------------------------------------------------------------------------------
	--	Renders entire txn uncommittable if constraint violation occurs. 
	--	ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/cbcaa433-58f2-4dc3-a077-27273bef65b5.htm
	-- --------------------------------------------------------------------------------------------------
	-- ------------------------------------------------------------------------------------
	-- ------------------------------------------------------------------------------------
	-- None.
	-- -------------------------------------------------------------------------------------
	-- -------------------------------------------------------------------------------------
	-- Error Handling Local Variables
	-- Delete this for SQL 2014 unless required for custom error logging
	DECLARE			@vErrorNumber		INTEGER	=	0
				,	@vErrorMessage		NVARCHAR(2048) = N''
				,	@vErrorSeverity		INTEGER = 0
				,	@vErrorState		TINYINT = 0
				,	@vErrorLine			INTEGER = 0
				,	@vErrorProcedure	NVARCHAR(256) = NULL;
	-- Processing Local Variables
	DECLARE			@LocalVariable1Name	INTEGER
				, 	@LocalVariable2Name	DATETIME;
	-- -------------------------------------------------------------------------------------
	-- -------------------------------------------------------------------------------------
	-- Initialise processing variables
	SET			@LocalVariable1Name	=	0;
	SET			@LocalVariable2Name	=	'2011-01-01';

	/* =========================================================================================
	============================================================================================ */
		-- { sql_statement | statement_block }
		/* ------------------------------------------------------------------------------------------
			Severity 0-10 (information) are not caught by TRY. . . CATCH
			Severity 11-16 (user-correctable)
			Severity 17 (Insufficient Resources)
			Severity 18 (non-fatal error)
			Severity 19 (SQL resource error)
			Severity 20-25 (fatal system error) execution termination with SQL close

			Any sys.messages valid messages can be utilised but must be passed to RAISERROR or THROW.
		-------------------------------------------------------------------------------------------- */

		-- Grab error indicators.
		-- Delete this for SQL 2014 unless required for custom error logging
		SELECT			@vErrorNumber		=	ERROR_NUMBER() 
					,	@vErrorLine			=	ERROR_LINE()
					,	@vErrorSeverity		=	ERROR_SEVERITY()
					,	@vErrorState		=	ERROR_STATE()
					,	@vErrorProcedure	=	ERROR_PROCEDURE()
					,	@vErrorMessage		=	ERROR_MESSAGE()+ CHAR(10)
												+ N'Executing code item name or type ('+ISNULL(@vErrorProcedure,N'AdHoc SQL')
												+ N'), error number ('+ CONVERT(NVARCHAR(20), @vErrorNumber)
												+ N'), severity ('+CONVERT(NVARCHAR(20), @vErrorSeverity)
												+ N'), code error line ('+CONVERT(NVARCHAR(20),@vErrorLine)										
												+ N').'+CHAR(10) +CHAR(13);
		-- -------------------------------------------------------------------------
		--	XACT_STATE =  1 ~ Transaction is committable.
		--	XACT_STATE = -1 ~ Transaction is uncommittable - roll back 
		--	XACT_STATE = 0 ~ No transaction - commit or rollback  results in error.
		--	ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/248df62a-7334-4bca-8262-235a28f4b07f.htm
		--  Any custom error loggin should be implemented here
		-- -------------------------------------------------------------------------
		IF (XACT_STATE() = 1) 
			SET @vErrorMessage	= @vErrorMessage + N'Transaction: ROLLED BACK';

			-- SQL 2000+
			RAISERROR	(	@vErrorMessage		-- { msg_id | msg_str | @local_variable }
						,	@vErrorSeverity
						,	@vErrorState		-- [ ,argument [ ,...n ] ] )  [ WITH option [ ,...n ] ]
			-- SQL Server 2014+

	-- Cleanup/ Housekeeping

/* ==================================================================================================================================
	TEMPLATE AUTHOR: $TemplateAuthor$, COMPANY: $OwnerCompany$


	---------	-----------	-----------------	---------------------------------------------------------------------------------------
	$ItemVersion$		YYYY-MM-DD	Name.Surname		Change details are.....

												<< See bottom of item for details >>
=================================================================================================================================== */
/* ---------------------------------------------------------------------------------------------
	NOTE:	This section has to be edited and run for all attributes after each edit to keep the
			database metadata up to date.
	 Add extended properties to the view (in database objects)
		1. Author name
		2. View naming specifics
		3. View version
		4. Editor name
		5. Edit date
		6. Template version
 ----------------------------------------------------------------------------------------------- */

-- Author details
EXECUTE sys.sp_addextendedproperty	@name = N'Author Name'
				,	@value = N'$ItemAuthor$' --<-- Change this only!
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'	
				,	@level2type = NULL
				,	@level2name = NULL;

-- Object description
EXECUTE sys.sp_addextendedproperty	@name = N'Functional Description'
				,	@value = N'This procedure executes for $projectname$ and. . .' --<-- Change this only!
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'	
				,	@level2type = NULL
				,	@level2name = NULL;

-- This view version
EXECUTE sys.sp_addextendedproperty	@name = N'Version'
				,	@value = N'$ItemVersion$' --<-- Change this only!
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'	
				,	@level2type = NULL
				,	@level2name = NULL;

-- Editor details
EXECUTE sys.sp_addextendedproperty		@name = N'Editor Name'
				,	@value = N'$EditorName$' --<-- Change this only
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'
				,	@level2type = NULL
				,	@level2name = NULL;
-- Edit date
EXECUTE sys.sp_addextendedproperty		@name = N'Edit Date'
				,	@value = N'$EditDate$' --<-- Change this only!
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'
				,	@level2type = NULL
				,	@level2name = NULL;	
-- Template version
EXECUTE sys.sp_addextendedproperty	@name = N'Template version'
				,	@value = N'$TemplateVersion$' --<-- Change this only!
				,	@level0type = N'SCHEMA'
				,	@level0name = N'$SchemaName$'
				,	@level1type = N'PROCEDURE'
				,	@level1name = N'$fileinputname$'
				,	@level2type = NULL
				,	@level2name = NULL;
-- End --------------------------------------------------------------------------------------

Configuring the Stored Procedure Template in Visual Studio

The template can be created in any SQL editor, viz. SSMS or VS.  Once it is created, open Visual Studio and add a Stored Procedure to an existing Project, using the default VS functionality.

VS Add new Item to Project

VS Add new Item to Project

Follow these steps:

  1. Select Stored Procedure and create it by giving it a name that would suit the template Stored Procedure.
  2. Take the newly created template SQL code for Stored Procedure (listed in the code-block above) and paste it over the text of the newly created item.
  3. Save it and then open: File >> Export Template
  4. Select Item Template radio-button from the [Export Template Wizard] and the appropriate project into which the template Stored Procedure has been saved
  5. Click the [Next] button
  6. Select the name of the Stored Procedure just created and saved in the project
  7. There should be no Item References to select so click the [Next] button
  8. Provide a Template Name, description, Icon, Preview Image, and deselect the Automatically import the template into Visual Studio (reference), keep the Display an explorer window on the output files folder selected
  9. Click the [Finish] button, and the output files folder is presented

A zip-file is created that contains three files:

  1. TemplateIcon.ic
  2. MyTemplate.vstemplate
  3. <Stored Procedure>.sql (the template SQL)

The MyTemplate.vstempate file is the one used to configure how the stored procedure is created, i.e. provide the name, description, input parameters, and other functions.

<VSTemplate Version="3.0.0" xmlns="" Type="Item">
    <Description>Default CreateStoredProcedure for Acme Corporation</Description>
    <References />
    <ProjectItem ReplaceParameters="true" TargetFileName="$fileinputname$.sql" >Z Create Stored Procedure.sql</ProjectItem>
	<CustomParameter Name="$projectname$" Value="API"/>
        <CustomParameter Name="$SchemaName$" Value="dbo"/>
	<CustomParameter Name="$ProcParam1$" Value="@InputParameter1"/>
	<CustomParameter Name="$ProcParam1DataType$" Value="INTEGER"/>
	<CustomParameter Name="$ProcParam1Value$" Value="0"/>
	<CustomParameter Name="$ProcParam2$" Value="@InputParameter2"/>
	<CustomParameter Name="$ProcParam2DataType$" Value="INTEGER"/>
	<CustomParameter Name="$ProcParam2Value$" Value="0"/>
	<CustomParameter Name="$ProcParam3$" Value="@OutputParameter"/>
	<CustomParameter Name="$ProcParam3DataType$" Value="INTEGER"/>
	<CustomParameter Name="$ProcParam3Type$" Value="OUTPUT"/>
	<CustomParameter Name="$ItemVersion$" Value="0.0.1"/>
	<CustomParameter Name="$TemplateVersion$" Value="0.0.1"/>
	<CustomParameter Name="$ItemOwner$" Value="c 2015 Acme (Pty) Ltd."/>
	<CustomParameter Name="$OwnerCompany$" Value="Acme (Pty) Ltd."/>
	<CustomParameter Name="$TemplateAuthor$" Value="Hendriekus J.Roux"/>
	<CustomParameter Name="$ItemAuthor$" Value="Hendriekus J.Roux"/>
	<CustomParameter Name="$EditorName$" Value="Hendriekus J.Roux"/>
	<CustomParameter Name="$EditDate$" Value="2015-03-09"/>

The listing of the MyTemplate.vstemplate (above) illustrates two parameter functions, viz. system parameter and custom parameters. These are placed in the template SQL code as place-holders that are filled-out either by providing a name in response to an action in Visual Studio like New >> Project or New >> Item where a name is provided by the developer.  Or, the custom parameters are filled-out in correspondence to the values provided in the MyTemplate.vstemplate file.  The latter should be considered in affinity to the functional intension of the Item, I.e. Stored Procedure in this case.

 Present the Template Available in Visual Studio

Visual Studio present a dialog when a new Stored Procedure is created.

VS Create New Item Dialog Option List

VS Create New Item Dialog Option List

The template should be presented here in a section appropriate to the company and project.  To create a new section the template is added to a folder location on the developer’s machine.  The Visual Studio custom templates are located here: <drive>:\Users\<user name>\Documents\Visual Studio 2013\Templates\ItemTemplates.

VS Custom Templates Folder

VS Custom Templates Folder

By adding templates either into these locations or by creating a new folder, say Acme Items, any new custom templates could be placed there and become available from the New Item dialog.

In this example create a folder here called: ItemTemplates\Acme Corporation\Programmability into which the zip-file (<drive>:\Users\<user>\Documents\Visual Studio 2013\My Exported Templates) is copied.

To use the new template, restart Visual Studio and add a new Stored Procedure to the template using the new template.

VS Custom Template New Item Dialog

VS Custom Template New Item Dialog

Unfortunately, at this point, none of the parameters (except the procedure name) is substituted in the actual SQL code.  These, i.e. the custom parametes must yet be added to the MyTemplate.vstemplate file.

Adding Custom Parameters to a Template

Follow these steps to add these parameters and their values:

  1. Extract the template’s zip file from its location into the [My Exported Templates] location and open the  file in Notepad
  2. Take the parameters between the <CustomParameters> tag from the code (above) and add it to the current template’s file in the appropriate location
  3. Save the file and drag the saved version from the explorer window over the zip file in the template location  and choose the overwrite option to up date the template
  4. Restart Visual Studio and create a new Stored Procedure from the template to see all parameters being populated.

Previous Article (3) [Create a Database Project with Folder Structure]. . . Next Article (4) [Coming Soon]

Visual Studio 2013 and Database Projects: Create a Database Project with Folder Structure

Visual Studio 2013 and Database Projects: Create a Database Project with Folder Structure


Data base projects in Visual Studio is a bit of an after thought on application development.  It started with Gert Drapers (the data dude) and became SSDT (SQL Server Data Tools) that had to be installed (as an add-on) to Visual Studio.  This had some varying function over the years, but was aimed at reducing the difficulty of database development in Visual Studio and keep database changes in synch across a solution (matching database and application versions) and multiple server environments through the application life-cycle.

Creating a Database Project

This is quite easy using Visual Studio 2013.  On the first landing page (can be turned off) a Start section has the option from which to create a new project, or the traditional FILE menu is just as simple.

Visual Studio for Database Development First Screen

Visual Studio for Database Development First Screen

VS Create New Database Project Dialog

VS Create New Database Project Dialog

Be mindful that if a project is created and Team Foundation Server (TFS) is being used to take care of how the solution and projects contained by the solution are demarcated.  It is not in the scope of this article to explain how to do this, but suffice to say that where solutions are stored separately from projects, to first create an empty solution and check it in.  Then the projects that are added back to the solution so that all are checked into TFS in their correct locations.

Further take not of the Visual Studio boxes, viz. Solution Explorer, SQL Server Object Explorer, and Properties for example.  These should be orientated (docked) in positions that make sense, I.e. for easy reference.  Below is an example of an instance that contains a Solution with three projects.

VS Database Project Screen

VS Database Project Screen

The two most important boxes are the Solution Explorer and the SQL Server Object Explorer.  The former depicts the Solutions and the database projects.  Where no database actually exists (this is where you create it) SQL Server Objects can be created/ added to the project, or (where a database already exists) imported by a few means.  The first is using a DACPAC (SQL Server Data-tier application extract file), or connecting directly to a SQL Server database, or create one using a script file containing all the object definitions.

Choosing a Folder Structure

Importing a database allows for the folder structure in the project to be changed.  The project folder structure should allow for ease of maintenance and readability.  The folder structure does not influence deployment.

VS Database Project Import Dialog Folder Structure

VS Database Project Import Dialog Folder Structure

An import of an existing database (where no structure is chosen, or defined dumps all files in the root of the project, which is moderately messy, so it must be carefully considered.  A folder is not created where an imported database does not have objects, e.g. Stored Procedures.  If subsequent objects are required, the folder will first have to be created.

Organising the database advances readability, consequently also maintenance, because developers are able to find things quicker.  Any database objects without any discernable grouping are dumped in the root of the project on import.

NOTE:Things that impact the display of objects and what access rights are differentiated in practice, bears on the configuration of the naming scheme.  Making use of database schemas (to group objects and assign login permissions) is an effective strategy for data architecture over using prefixes on objects like sp or pr for a Stored Procedure (source).  The folder structure in Visual Studio and SQL Server Management Studio already informs the object type and with an added schema denotes an organisational name space for such objects.  SQL Server uses a four-part naming scheme: Server.Database.DatabaseSchema.DatabaseObject.  Objects in a schema are owned by the security principle that owns the schema and a schema can contain objects owned by different users.  Any predefined schema (dbo, sys, etc) can be dropped from the model database if not required consequently preventing them from being created in new databases.  A schema owner can be changed without breaking the code, but if an object owner, say Tommy.Object is removed, an ownership change must first be performed before Tommy’s account can be deleted.  However, any fully qualified references will break in the code, e.g.  Server.Database.[tommy[.DatabaseObject. The default dbo schema is owned by the dbo user. Users assigned to the dbo schema do not inherit dbo user account permissions, but the database objects contained in the schema (source).  Using dbo as the owner of all object can greatly simplify managing objects (source).

Previous article (1) [Installation]. . . Next Article (3) [Working the Project]

Visual Studio 2013 and Database Projects: Installation

Visual Studio 2013 and Database Projects–Installation

This installation is for Visual Studio Premium.  Once the image is mounted, the folder contains the installation file for the install, i.e. vs_premium.

VS Premium Folder

VS Premium Folder

Execute the vs_premium file and agree to the License Terms and Privacy Policy, and the location of the installation. Two options are quite important:

  1. Light Switch
  2. Microsoft SQL Server Data Tools

Light Switch is a technology to shorten the development time on business applications and data services for data-centric business applications.  SQL Server Data Tools are the add-on previously (VS 2012) downloaded and installed separately, but now part of the install.  It provides database schema modelling and support for querying live databases.

The other default option is Blend, a visual user-interface design tool using XAML (web) and WYSIWYG (desktop), before part of the Microsoft Expression Studio toolset.  This is not a database specific tool.

Once installed (it takes a while) the Microsoft Account Login dialog is presented for which you are required to have credentials.  Either use an existing account or create one (reference) and then associate an MSDN subscription (licenses that your company pays for) to this identity. Note: a licence key may also be used if you don’t have an MSDN subscription to activate the software (see how).

Microsoft Account Login

Microsoft Account Login

Once logged in access your account details to find the MSDN section where the Subscriber ID and company email are used to link the MSDN profile to the Microsoft User Identity.

Once the subscription is activated the software is licensed.

MSDN Subscription Activation Dialog

MSDN Subscription Activation Dialog

Visual Studio starts with an environment set-up that can be changed at a later stage also.  Here the Database environment (as that is the purpose of this instance) is selected and the colour scheme of your choice.

VS Environment Set-up Dialog

VS Environment Set-up Dialog

This is the installation process and you have arrived at the first screen.

Visual Studio for Database Development First Screen

Visual Studio for Database Development First Screen

Next article in series. . . 2 [coming soon]

Navigate. . . Next Article (2)

SQL Server Data via Enterprise Library 5.0


This is a very simple Enterprise Library 5.0 example.

Getting started with reading data from SQL Server, via Enterprise Library 5.0, requires that the framework be installed on the developing machine and added to the Project References, and  ‘using’ references, of the development environment, e.g. Visual Studio 2012.

The software is available from MSDN [1]: Enterprise Library 5.0 — May 2011 (Build 5.0.414.0), but it is recommended that you use NuGet [5] to put the Enterprise Library onto the solution in Visual Studio.  This way, the latest version of the Enterprise Library section is downloaded and installed, e.g. 5.0.505.0.  I am not sure why the MSDN link points to Enterprise Library (for a stand-alone Msi download) an older version(5.0.414.0) and NuGet has the latest, i.e. 5.0.505.0.

NOTE: Using NuGet to get the Enterprise Library in the: Tools >> Library Package Manager >> Manage NuGet Packages for Solution >> Online >> All, then type ‘entlib’ into the Search Online (Ctrl+E) box, and hit enter button.

Install the following packages from NuGet:

  • Microsoft.Practices.EnterpirseLibrary.Common
  • Microsoft.Practices.EnterpirseLibrary.Data
  • Microsoft.Practices.EnterpirseLibrary.ServiceLocation
  • Microsoft.Practices.EnterpirseLibrary.Unity
  • Microsoft.Practices.EnterpirseLibrary.Unity.Configuration
  • Microsoft.Practices.EnterpirseLibrary.Unity.Inception
  • Microsoft.Practices.EnterpirseLibrary.Unity.Inception.Configuration

In Visual Studio, using the menu path: Tools >> Library Package Manager >> Package Visualizer, you can get a listing of all the packages installed (see image below) for this solution.

Package Manager Visualiser

Package Manager Visualiser

Using Enterprise Library leverages functionality from a collection of reusable application blocks to assist developers with common development objects.   Amongst many, the common block are Data Access, Exception Handling, Logging, Security and Validation.  To get to the NuGet packages (online), as well as see which ones have been installed, in addition to the above method, use the Visual Studio menu path: Tools >> Manage NuGet Packages for Solution… The image (below) lists all packages (you can type entlib into the search Online field) with a green-tick indication of those you have already installed.

NuGet Package Manager for Solution

NuGet Package Manager for Solution

This article focuses on the Data Access Application Block.


Rewriting common code, for every specialised implementation of application code, is not very optimal because it is error prone, because it does not lend itself to reproducing exactly the same implementation.  This creates a maintenance dependency, as errors may vary in the different code blocks that essentially do the same thing.  A standardised layer of optimised code, substituting common boiler-plate code plumbing, should be used.  This layer is the Microsoft Enterprise Library.

This article looks at standardising database access through the enterprise library.


Enterprise Library Installation

Download and install Microsoft Enterprise Library, or use NuGet (recommended).  Create a Visual Studio 2012 console application project, to test a data retrieval.  Add references of the Enterprise Library to the project, or if you have used NuGet, these are added automatically to the solution.

NOTE: NuGet adds a new config file, i.e. packages.config, to track installed packages and versions, but, this file is not part of the project and should not go into source control.

Configuration & Code

Once a reference has been added to the project, have a look at the Codeplex [2] examples for download.  From this example: [EntLib Dev Guide RC – Code], look in the DataAccess > DataAcess folder for the App.config file.

Two sections are important, viz. configSections, and connectionString.  These have to be added, with the former being configured to point at the installed dataConfiguration, i.e. Enterprise Library.  The latter is the connection string to the actual database.  See the next section on how to use the Enterprise Library Config. tool, to create/ change the app.config file.  This is a whole lot less error prone than rolling your own.

Code (from the Microsoft example):

        <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
<dataConfiguration defaultDatabase="TradeInformation" />
    <add name="TradeInformation" connectionString="Database=TradeInformation;Server=TradeDatabasseServer;Integrated Security=SSPI"
        providerName="System.Data.SqlClient" />
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>

NOTE: Instead of editing the configuration file manually, it can also be done via a tool that provides a user-screen.  Simpy right-click on the configuration file in the project (the one referring to Enterprise Library) and select Edit Configuration File, from the menu list.  For more detail, see the MSDN example [3]: Using the Configuration Tools.  If this option does not show up (right-click) make sure you have the version that targets Visual Studio 2012, in stalled: [4], restart Visual Studio.

EnterpriseLibrary.Config Right-Click

EnterpriseLibrary.Config Right-Click

When using the Enterprise Library.Config (right-click the app.config) application to configure the data access configuration.  The user-interface requires that a Block (Add Data Settings) be added.  In the screen-grab (below) the app.config file can be seen, with the EnterpriseLibrary.Config user-interface, used to create it with below.  Doing it this way eliminates human configuration errors.

Enterprise Library.config

Enterprise Library.config

The defaultDatabase setting is a default connection, chosen by the GetInstance() method, of the connection listed first, below, when no database key is specified for the method.   The second example illustrates a connection, made by specifying the database connection key/ name.

_db = EnterpriseLibraryContainer.Current.GetInstance();
_db = EnterpriseLibraryContainer.Current.GetInstance("TradeInformation");

NOTE: If you get the error “The OutputPath property is not set for this project” when building the solution, make sure that the Configuration Manager (Debug, Release), as well as the Properties of every project in the solution are targeting the same CPU, e.g. x64.  Also refer to [] and [], if this is not the problem.

Code Example

Using the Codeplex example, I changed it a bit to make a simple Enterprise Library database call to defaultDB over which an ExecuteReader loops to print each record (in this case: trade) out to the console window.

I had a few issues with the .dll file references of Enterprise Library files.  The short of it is that when these come into the project via NuGet, they are copied (the packages) into the  c:users<your profile>DocumentsVisual Studio 2012Projects<project name>packagesEnterpriseLibrary.Common.5.0.505.0libNET35<.dll name>.   A copy also resides in the bin folder:

  • Microsoft.Practices.EnterpriseLibrary.Common.dll
  • Microsoft.Practices.EnterpriseLibrary.Data.dll
  • Microsoft.Practices.Unity.Configuration.dll
  • Microsoft.Practices.Unity.dll
  • Microsoft.Practices.Unity.Interception.Configuration.dll
  • Microsoft.Practices.Unity.Interception.dll

The project references point to the “packages” folder (build), but the application references the bin folder, at run-time.  Make sure that you have these references pointing to the correct .dll files for the Enterprise Library.  You could find yourself with endless problems when the defaultDB = EnterpriseLibraryContainer.Current.GetInstance(); attempts to get the database instance, using the Enterprise Library. It will either complain about not being able to load the correct .dll files, or that the database key (referring to the database name of either your named or default database in the app.config file) could not be loaded.

Code (Program.cs):


using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.ComponentModel;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;

    class Program
        static Database defaultDB = null;
        static void Main(string[] args)

            defaultDB = EnterpriseLibraryContainer.Current.GetInstance<Database>();
        private static void ReadTradesToConsole()
            String tSQL = "SELECT TradeID, ReceivedSequenceNumber, TradeDateTime, TradePrice, TradeSize FROM [dbo].[TradeInformation]";
            using (IDataReader reader = defaultDB.ExecuteReader(CommandType.Text, tSQL))
                while (reader.Read())
                    for (int i = 0; i < reader.FieldCount; i++)
                        Console.WriteLine("{0} = {1}", reader.GetName(i), reader[i].ToString());

The Codeplex exmaple provides a list of different usage patterns for the data, e.g. reading data from a stored procedure (with named/ unamed parameters), reading data as objects/ XML/ Scalar value, even doing all of this asynchronously. The asynchronous methods check the Enterprise Library’s public virtual bool SupportsAsync { get; } method, to see if the operation is possible, before attempting it.


No. Author Web Site Name URL Date Authored Date Cited
 1.  Microsoft  MSDN  May 2011  28 January 2013
2.  Microsoft  CodePlex  14 December 2012  28 January 2013
3.  Microsoft  MSDN: Using the Configuration Tools  na  28 January 2013
4.  Microsoft  MSDN: Visual Studio Geleray – EnterpriseLibrary.Config  na  14 March 2013
5.  NuGet Docs  NuGet Frequently Asked Questions  na  15 March 2013

Last Updated: 19 March 2013 09h19

Visual Studio 2010/ 2012: Model and Query Database Objects


Executing a Visual Studio Database Project’s queries against an existing database, is somewhat different between the two versions, viz. Visual Studio (VS.) 2010 and 2012.  It is slight but useful to consider, after updating both versions to the latest version of SQL Server Data Tools, to see what these are.  This article is not an exhaustive account, but endeavours to jump-start a database developer on the differences.


Software Versions

Visual Studio

The version of Visual Studio 2012 is upgraded to Service Pack 1 [1, 2], and all references to functionality is relative to that version.

NOTE: Remember to stop any anti-virus software before running the service pack, otherwise the error: “Access is denied” is presented.  The file name of the update is: vsupdate_KB2707250.exe [2], with the updated (after installation) build number:  04940-004-0038003-02092 .  It takes just under forty five minutes to complete.

NOTE: Ensure that the latest SQL Server Data Tools (for Visual Studio 2012) is installed, after this update [5].  If not, an incompatibility message is displayed by Visual Studio on the SQL Server Object Explorer tab, where database connections are listed.  The update installation takes about ten minutes, with the build number updating to: 11.1.21208.0.  After about seven minutes the installation dialogue hangs for a few minutes, with all the bars green, don’t kill it, it will complete.  The same applies for Visual Studio 2010, with build number: 10.3.21208.0, to download from the same location.

Microsoft SQL Server

The version of the installed 2012 database, is upgraded to Service Pack 1, bringing it to build: 11.0.3000.00.  The name of the image file is: en_sql_server_2012_sp1_x86_x64_dvd_1176976.iso, which is obtainable from your MSDN subscription.  NOTE: Remember to turn any anti-virus software off, as explained above.  The installation stalls for a bit, giving no indication that it is actually still running, however, a process called scenarioengine.exe (Windows Task manager) is active, and associated with the currently running update.  A few minutes later (with scenarioengine.exe resting on a zero percent CPU utilisation), a window suddenly pops up.  The SQL Server 2012 Service Pack 1 pop-up, should execute the update for all installed features, after agreeing to the licensing stop.  It takes about an hour.

For SQL Server 2012, the table below provides a list of updates at the time of writing.  NOTE: See the citation [3] on how to determine the installed version, and get to the latest update(s) on the web.

Build Knowledge Base (KB) Description URL Date Released
 11.0.3000.00  KB2674319  Service Pack 1  09 November 2012
 11.0.2383.00  KB2758687  Cumulative Update 4  15 October 2012
 11.0.2332.00  KB2723749  Cumulative Update 3  31 August 2012
 11.0.2325.00  KB2703275  Cumulative Update 2  18 June 2012
 11.0.2316.00  KB2679368  Cumulative Update 1  12 April 2012
 11.0.2100.60  na  SQL Server 2012 RTM  na  06 March 2012

NOTE: Visual Studio can install a SQL Express database known as LocalDB, so that a separate instance of SQL Server does not have to be maintained by developers on their machines [4].

The Problem

Tool-bars, Commands, and going about functionality differ slightly between Visual Studio versions (for database developers), potentially making the navigation an unfamiliar experience.

The Solution

Provide examples of how a database can be accessed, using Visual Studio 2012, in comparison with the older 2010 version, of the development environment, to accelerate the learning experience.

Model and Query Database Objects in Visual Studio 2012

Because we have just moved from Visual Studio 2010 (or having never used Visual Studio, but SQL Server Management Studio) for database development, it is expedient to know the differences and consequential enhancements, when working with a database.

To model or query database objects, as in SQL Server Management Studio, using Visual Studio, is accomplished in several ways.  A designer or query can be used to create and/ or change database objects.

Visual studio can connect to a database, without having a database project open, to view and change objects on the database.  With a project opened, objects can be maintained (in the project tree) while being connected to the database.

The experienced difference, between just working on a database through Visual Studio, or working on a database through Visual Studio’s Database Project, lies in the connection state to the database.  The latter is implicit, by means of the project’s properties, and the former explicit, by connecting to a server and database for the purpose of executing queries that could perform various tasks.

[SQL] Server [Object] Explorer

This function can be used without having a database project open, and also on the side, when working with an open project.  A database structure-tree, similar to that in SQL Server Management Studio, presents itself in the navigation pane of this function.  Commands can be issued, from this pane, against objects in the database.

[SQL] Server [Object] Explorer is a tab that is accessed from the menu tool-bar, or by short-cut key (for both VS. versions–with square brackets denoting the VS. 2012 variance in the name), as follows:

  • View >> Server Explorer
  • Ctrl+Alt+S

From the [SQL] Server [Object] Explorer tab, a new connection is added:

Visual Studio Server Explorer Comparison

Visual Studio Server Explorer Comparison

Visual Studio 2010
  • Use the menu path (from the right-click menu on Data Connections):  Add Connection, or
  • Use the Visual Studio 2010 Add Connection icon
Visual Studio 2012
  • User the menu path  (from the right-click menu on SQL Server): Add SQL Server, or
  • Use the Visual Studio 2012 Add SQL Server icon

NOTE: The dialogue, by which the connection is facilitated, looks different.  Visual Studio 2012 uses the same connection as is used by SQL Server Management Studio.

Once a connection is made to a database, it is listed in the [SQL] Server [Object] Explorer tab, and can consequently be accessed via Visual Studio.

The functionality, hanging off the navigable database objects-tree, is limited to specific database operations relative to the meaningfulness of the node.  Each node in the tree, for example the Databases or Tables nodes, have different menu options onthe right-click menu.  It makes sense when right-clicking a database node that the New Query option would be presented, as opposed to right-clicking a table name where Data View, Code View, and the likes, are presented.

Visual Studio 2012 SQL Server Object Explorer Query

Visual Studio 2012 SQL Server Object Explorer Query

A database query, just like using SQL Server Management Studio, can be created from the SQL Server Object Explorer’s connection to the database, by selecting the appropriate node, viz. SQL Server or Database, using the right-click menu: New Query… to create a query tab.  This query is a bona fide database query, without any context to an object residing in a project, it could perform any database action.

Doing the same in Visual Studio 2010, having the latest version of data tools installed, produces different results.  The New Query option launches a designer view (something like that of Access, or the Database Diagram option) from which to select tables that are to be included in the TSQL query, and restricts, or boxes the developer into a template ability.

Visual Studio 2010 Data Connections Query

Visual Studio 2010 Data Connections Query

Database Diagrams are not present in the Visual Studio’s SQL Server database hierarchy tree, any-more [6], with 2012.  It can still be found in Visual Studio 2010, but only used on databases up to SQL Server 2008.  This is a bit sad, because in my humble opinion, making Visual Studio a one-stop-database-design-shop fails here, because one would really benefit from being able to model a database in the IDE, along with the other functionality.  There is also no modelling option under the Architecture tool-bar option, which leaves SQL Server Management Studio [7] and Visio, as readily available Microsoft modelling tools.

The New Query, accessed from the right-click menu, on a table node (Visual Studio 2010) also launches the designer for a  selection of tables to participate in the query.  Visual Studio 2012 provides: View Code, Designer and Data options, but no New Query.  It does not enable the data-connection tool-bar, showing the database to which the connection points, but implicitly connects to the database to which the object belongs.  This makes sense, because the table is situated within a database, so it goes without saying, but boxes the developer into a view state, when accessing the database from this angle.  But, don’t lament, because when using the added Script-As option, the table’s DDL TSQL is scripted to a new query window (an explicit connection), with the associated tool-bar indicating the database connection, and execute options.  Visual Studio 2010 only provides the boxed-in (implicit) query window for database objects.

NOTE:  To execute an open query, where the Execute icon is presented in a tool-bar (explicitly connected), the short-cut commands and icons are different between versions.

  • Visual Studio 2010: Visual Studio 2010 Query Execute Icon or the short-cut code Ctrl+R
  • Visual Studio 2012: Visual Studio 2012 Query Execute Icon or the short-cut code Ctrl+Shift+E

The Visual Studio 2012 icon is similar to the familiar: Start Debugging (F5) of 2010, which is Execute in SQL Server Management Studio (haven’t we all done that?) and has been changed with a drop-down listing Execute or Execute with debugger (Alt+F5), in 2012.

NOTE: Ctrl-R does not minimise the results-pane, as in SQL Server Management Studio.  Use the key-combination Ctrl+D, R, lifting finger off the Ctrl, after pressing D, to press R.  This however keeps the results pane hidden on next execution, and is brought up again, by repeating the command, hum-ho.

Visual Studio 2012 Query Execution Options

Visual Studio 2012 Query Execution Options

The Programmability (Stored Procedures and Functions) menu option in Visual Studio 2012, provides the Script-As and View Code options.  The latter, by virtue of the word View, provides a View only (implicit connection), i.e. it does not connect to the database for manipulation.  The former Script-As (to new query window) connects to the database for manipulation (explicit connection), i.e. it provides the ability to CREATE the object, i.e. execute the DDL on the database.  In Visual Studio 2010, stored procedures can’t be executed or scripted in the same manner, but only an Add option is provided for a new object.

Using Database Project Objects, connected to a Database

Selecting objects from the Solution Explorer (Visual Studio 2012) opens up the object’s code and design-view, in a non-connected (implicit) state.  However, the project is bound to a database, as specified in the Project Properties tab >> Target Connection String, i.e. implicitly.  When a project database object is displayed, a connection to this database is achieved by right-clicking the code window and selecting: Connection > Connect.  To consequently run the code, on the database, from the same menu, select: Execute, or use the short-cut keys: Ctrl+Shift+E.  The same functionality exists for Visual Studio 2010’s Database Tools.

For a more detailed overview, on working with Visual Studio 2012 Database Projects, view the article: Visual Studio 2012 Database Project (SSDT): Working with a Database Project.


No. Author Web Site Name URL Date Authored Date Cited
 1.   Kathleen Richards  Visual Studio Magazine  26 November2012  07 January2013
 2.  Microsoft  MSDN  November 2012  07 January2013
 3.  Microsoft  Download Center  November 2012  07 January2013
 4. sqlexpress SQL Server Experess WebLog 12 July 2011 07 January2013
 5. Microsoft Data DeveloperCenter December 2012 07 January2013
 6. MSDN Forum MSDN:Visual Studio 11 Database Diagrams 01 April 2012 07 January2013
 7. MSDN MSDN:Visual Database Tool Designers 2012 07 January2013

Last updated: 07 January 2013 16h50

Visual Studio 2012 Database Project (SSDT): Working with a Database Project


In the past, in the days of Visual Studio and “data-dude” database projects, it was not common for database developers to use Visual Studio through which to create tables, and write database queries.  The data-dude database projects were very clunky, and more often than not, database developers were found using SQL Server Management Studio (SSMS) to develop and then copy the code into the Visual Studio project, for source control.

With the arrival of SQL Server Data Tools (SSDT), also as a plug-in to Visual Studio 2010 and the subsequent release of Visual Studio 2012, things changed.  Database developers can now actually use Visual Studio in which to develop, where as much, and even better facilities are available for development.  The build and deploy functions have also been made much easier.  No longer do we need to fiddle-faff the project’s properties, by setting-up different deployment  environments, connect them to environmental variables etc., before we can deploy to like two places.

Presently, Visual Studio 2012 provides the ability to create your own project folder structures [1], none is being pushed on the project, define deployment (publish) destinations, as well as co-locate CLR and native TSQL objects in the same project.  These are also deployed together without having to firsts whistle a tune through your bottom, while sucking syrup through a metre long straw.

It is pleasant to use Visual Studio 2012 for database development, and the features presented are great, you would wish for a 52″ LED display to get a better view of the IDE.

Creating Database Objects


Visual Studio provides all the facilities to easily define a database table.

The table can be tackled from two ends, viz. the designer and the code division of the window.  If SQL code can be typed out faster than filling in the columns of the designer, simply write the code, and the designer updates itself immediately.

Create a Table in Visual Studio Designer

Create a Table in Visual Studio Designer

From the screen-grab (above), it is quite clear that all the design features are present in the one screen. To the right of the columnar table design-grid, is a list of table attributes, viz. Keys, Check Constraints, Indexes, Foreign Keys, and Triggers.  These options can be right-clicked and the attributes created. Or, they might be added to the T-SQL design tab, below, and the grid updates itself accordingly.

Table Attributes

Because of the folder structure flexibility [1], it becomes a bit of a question whether the table attributes should be added to the table’s create script, flying through the design features of the screen, or should they be added, as per the previous Visual Studio file structures, where keys were put within their own folder, and given a particular name.  The decision to retain keys, such as the primary key in the table’s create script, would be that the designer is used.  If the primary key is scripted to a Key folder, the designer won’t display the primary key on the grid view, which could be counter intuitive, from a visual design perspective.  Table constraints is another attribute that should be kept as part of the table’s create script.  But, again, this depends on how complicated the project is.  Foreign keys may be scripted to a Key folder, because they are inter table attributes, only visible on a designer view where tables are listed with their referential attributes.

Extended Properties

Extended Properties is another neat feature of the designer.  While designing a table, these are added automatically to the create script.  When creating a table constraint, such as a default value, it is done in the Properties pane of the window, the same place where Extended Properties are defined.  For a Default Value or Binding it is specified as a constraint (in the table properties tab), right above another property, I.e. the Description, which is an Extended Property (table metadata) that is easily updated, at the same time.  Completing this  field, adds table metadata to the designer’s T-SQL tab, through [sp_addextendedproperty].  Because the developer is already active in this tab, the Extended Property does not consume any significant additional time, and provides such an extended benefit to the database that it should be part of the table’s design requirement.

NOTE: In replication, extended properties are only written to the replicated database on the initial replication, not after that [2].

Through using a table’s attributes and extended properties, a Data Dictionary (documentation) [3][4][5], with the ability to be exported to Word, Excel, and HTML, can be created quite easily.  This provides an immense informational ability of the database, in addition the actual stored data, hence the defining term: Database Metadata.

Below is a listing of TSQL, illustrating a simple way to obtain table metadata: (listed by permission from Dattatrey Sindol: SQL Server – Capturing Table and Column metadata and description – Building Data Dictionary Part 2) [6].

SELECT          [sCOL].[name]                                  AS  [ColumnName]
            ,   CASE
                   WHEN  [sTYP].[name] IN ('char','varchar','nchar','nvarchar','binary','varbinary')
                   THEN  [sTYP].[name] + '(' + CAST([sCOL].[max_length] AS VARCHAR(10)) + ')'
                   WHEN  [sTYP].[name] IN ('float','decimal','numeric','real')
                   THEN  [sTYP].[name] + '(' + CAST([sCOL].[precision] AS VARCHAR(10)) + ',' + CAST([sCOL].[scale] AS VARCHAR(10)) + ')'
                   ELSE  [sTYP].[name]
                END                                            AS  [DataType]
           ,  CASE    [sCOL].[is_nullable]
                   WHEN  0x1 
                   THEN  'Yes'
                   ELSE  'No'
               END                                             AS  [IsNullable]
           ,  CASE
                    WHEN  [IdxDtls].[column_id] IS NOT NULL
                    THEN  'Yes'
                    ELSE  'No'
          END                                                  AS  [IsPK]
          ,  CASE
                    WHEN  [sFKC].[parent_column_id] IS NOT NULL
                    THEN  'Yes'
                    ELSE  'No'
             END                                               AS  [IsFK]
         ,       [sEXP].[value]                                AS  [ColumnDescription]
FROM             [sys].[objects]                               AS  [sOBJ]
INNER JOIN       [sys].[columns]                               AS  [sCOL]
                 ON  [sOBJ].[object_id] = [sCOL].[object_id]
LEFT OUTER JOIN  [sys].[types]                                 AS  [sTYP]
                 ON [sCOL].[user_type_id] = [sTYP].[user_type_id]
LEFT OUTER JOIN  (  SELECT        [sIDX].[object_id]
                               ,  [sIXC].[column_id]
                    FROM          [sys].[indexes]               AS  [sIDX]
                    INNER JOIN    [sys].[index_columns]         AS  [sIXC]
                                  ON  [sIDX].[object_id] = [sIXC].[object_id]
                                   AND [sIDX].[index_id] = [sIXC].[index_id]
                     WHERE        [sIDX].[is_primary_key] = 0x1
                 )                                              AS  [IdxDtls]
               ON  [sCOL].[object_id] = [IdxDtls].[object_id]
                  AND  [sCOL].[column_id] = [IdxDtls].[column_id]
LEFT OUTER JOIN   [sys].[foreign_key_columns]                  AS [sFKC]
                  ON  [sCOL].[object_id] = [sFKC].[parent_object_id]
                    AND  [sCOL].[column_id] = [sFKC].[parent_column_id]
LEFT OUTER JOIN   [sys].[extended_properties]                  AS [sEXP]
                  ON  [sOBJ].[object_id] = [sEXP].[major_id]
                    AND  [sCOL].[column_id] = [sEXP].[minor_id]
                    AND  [sEXP].[class] = 1
                    AND  [sEXP].[minor_id] > 0
                    AND  [sEXP].[name] = N'MS_Description'
WHERE             [sOBJ].[type] = 'U'
                    AND  SCHEMA_NAME([sOBJ].[schema_id]) = N'dbo'
                    AND  [sOBJ].[name] = N'STA_StagingEAP'
ORDER BY           [ColumnName];

Programmable TSQL Objects

Any SQL object containing TSQL code, e.g. stored procedure, is created in the same way as in any previous Visual Studio release.  Depending on the file structure of the project, the specific hierarchy of the folder-tree may differ, but going by the general rule of thumb, i.e. to follow the SSMS folder structure, would see the created object being put into a folder with a heading, corresponding to the object’s type, e.g. Stored Procedures.

An important thing to consider, when writing TSQL code,  is to assume a coding standard [10].  To a degree, when writing C#, the editor takes care of some of the formatting automatically.  In TSQL it is not as rigid, which means that divergent styles can erupt faster than zits on a teenager’s face.  Nobody wants to spend more time, than they would have to–in the zit phase–and so also, definitely not the company’s SQL code.

The SQL Server Object Explorer snap-in window is used just as you would in SSMS, to navigate the database folder-tree, from which to inspect tables and views to use in the programmable object’s code.


SQL Server Object Explorer snap-in window

There are a few quirks with the SQL Server Object Explorer snap-in window:

  1. A new query, relative to a database, can be created by right-clicking
    1. the server node of the tree: New Query…
    2. the database node of the folder tree:  New Query…
  2. The right-click menu, on the database node of the folder tree, provides an option: Create new Project…
    1. It  launches an Import Database windows that is captioned Create new Project
    2. It imports and existing database, and adds a New Project, to the Solution Explorer snap-in window
    3. Take note that it may not put in under source control, therefore the folder may end up in another location, e.g. the Projects folder for Visual Studio 2012
  3. Dragging an object (table) name, from the SQL Server Object Explorer snap-in window, across to the TSQL query window, seems to indicate that it is actually dragging the name across (cursor with plus sign), but when the mouse-button is released, the name isn’t there.  This works in the SSMS editor of SQL Server 2012.  Alternatively, the F2 (edit) key can be used to copy the object’s name, or the intellisense pop-up
  4. When writing a simple query, Visual Studio displays a tool bar with a drop-down of databases (and the one currently connected to), as indicator of what the script window is connected to.
    1. When writing a stored procedure, this tool bar is missing. It makes it hard to get to the right database, so that intelli-sense picks up the objects that you want to work with, if a cross database query is being designed.  It logs into the project’s database, when using the right-click connect option, which makes sense, in the context of the project.  Without the ability to drag-and drop object names, from other databases, and with intelli-sense pointing to the project’s database, a manual “get” (F2) must be done for object names outside of the project’s database
  5. On some occasions, when right-clicking a project, or other folder, to either Build or Add a new item, the menu option is not present.  When clicking off the project or other folder (to highlight another folder) and then right clicking on the project or other folder, i.e. the one not highlighted, the option is in the menu list.

Cross Database Queries

When performing a cross-database query, using a three-part (fully qualified) naming standard, the source-database’s name has to be substituted by a SQLCMD variable, in the code.  The Warning that gets thrown, is a SQL 71562, i.e. “Procedure <storedprocedure> has an unresolved reference to object <source database object’s name>“.  This warning has to be suppressed, by issuing a Suppress Transact-SQL warnings: value, in the project’s properties window in the Build tab, i.e. the value 71562.  After that the project builds without interruption.

Common Language Runtime Scripts

In Visual Studio 2012, Common Language Runtime (CLR) scripts are created in the same folders, as their TSQL counterparts.  The project is built and published, using the same process.  There is a bit of gymnastics, to be done, when deploying CLR objects that require external data access (where the database is not marked TRUSTWORTHY) but the assembly is signed [11].

Global Classes

In order to retain globally set values, or classes, alive in the SQL CLR, as a common objects, a class can be added to the project by selecting from : Add New Item >> SQL CLR C# >>Class, where it is not a database object, but a script file that is visible to all CLR objects.  Variables can be declared in that class, and made available to other CLR objects, using getter-setter accessors.  NOTE: To create get-set accessors, from a private variable, do the following:

  • right-clicking the variable name >> Re-factor >> Encapsulate Field, or
  • typing prop, followed by 2x tab, or
  • Ctrl k,x >> Visual C# >> prop

These global scoped code files, are part of the assembly, and when published provides managed code objects that are usable by any other CLR created database objects.

However, the assembly must be SAFE, i.e. not have any writeable instance variables.  Also know that CLR object classes are static.  When variables are set in a non-database object class, by a static member of another class, changing or setting the variable’s value is not a safe operation.  For static classes, such variables should be readonly, which defeats the idea of having global variables that are changeable.

To provide the assembly with ease of mind, it has to be able to deem setting of the variables as a SAFE operation.  The options are to create the assembly in UNSAFE Permission level (in the project’s properties: SQLCLR) [making sure the Assembly owner, e.g. dbo,  has UNSAFE ASSEMBLY permission, and the database is set to be TRUSTWORTHY], or the assembly be signed with an asymmetric key with a login with UNSAFE ASSEMBLY permission [11].  NOTE: Much of the latter information is displayed by the SQL72014 error message, on an attempt to deploy the assembly as UNSAFE, with the assembly owner not having the appropriate permission.

Remember to sign the assembly via the Project Settings window, using the [Signing…] button, and set the Permission level: to UNSAFE.  This way the database does not have to be set to TRUSTWORTHY.

To determine which login, on the current database, has UNSAFE ASSEMBLY permissions, use the following TSQL query:

SELECT      [SP].name                AS  [Login]
FROM        sys.server_principals    AS  [SP]
INNER JOIN  sys.server_permissions   AS  [SPE]
            ON [SP].principal_id = [SPE].grantee_principal_id
WHERE      [SPE].[permission_name] = 'UNSAFE ASSEMBLY';


To write functions in the CLR, is a bit more elaborate than stored procedures.  Both object types,allow for attributes in the decoration of the object’s method, e.g. name, data access, etc.  The SqlFunction takes many different attributes [12]:

  • IsDeterministic – Idempotent if true, i.e. returns same results when called with same parameters
  • DataAccess – DataAccessKind.None/ Read, if reading data from context SQL connection
  • SystemDataAccess – Reading system tables, SystemDataAccessKind.None/ Read
  • IsPrecise – Determines if to do floating point operations, true does not use FLOAT
  • FillRowMethodName – Method in CLR code, used to perform table-valued filling of data
  • TableDefinition – The table structure to be returned by the TVF
  • Name – Name of the SQL function, the one Published to the database

The attributes define the actual function that is built to the database, and used by queries.  Most of the attributes are known as things that a normal TSQL function would use, but the real magic of the SqlFunction lies in the FillRowMethodName.  The method specified against this attribute is implicitly called by the SQL-CLR , every time the method that implements IENumerable (Interface, with the MoveNext()), is called.  To a database developer, this might be a little strange.  No matter,  just accept that a magic weaver has sewn a thread between the Interface and the function’s method (FillRowMethodName) that loads the data rows into a data table, i.e. the one specified in the attribute: TableDefinition.

CLR Function Example

For illustrative purposes, an arbitrary CLR function, called CalendarTradingDateParameterFormatterFunction is used; it performs a trivial routine that formats input parameters for calling a TSQL function.  It receives a range of trading dates that are valid business, or office trading dates.  A TSQL function that does the actual database interrogation, is the recipient of the CLR function’s formatted output of these parameters.  The parameters define the input for the TSQL function’s three return types.

The formatting performed by the CLR function CalendarTradingDateParameterFormatterFunction, to suit the TSQL function is as follows:

  • Parameter: (@pRelativeDay > 1) => Business, or trading date range, i.e. all dates between (inclusive of) FromDate and ToDate;
  • Parameter: (@pRelativeDay <=0) => Business or trading date adjacent (smaller than or left of the datum) or the actual input date (equal to, i.e. the datum), i.e. is the current date a business date;
  • Parameter: (@pRelativeDay =1) => Business or trading adjacent (equal to 1) on the right of the datum

The output dates ensure that the system calendar is interrogated correctly for finding the business date(s):

  • where a range of business dates are requested
    • the start and end dates are both populated if one, or both is null
  • where an adjacent date (relative to a datum) is sought, that at least seven days are provided on the side of the adjacent date in question, or that the datum (@pRelativeDay parameteris evaluated as a trading date
SQL CLR Function Illustration

SQL CLR Function Illustration

The illustration (above) depicts the example’s architecture.  When a Requestor asks the database to return a lis, or date (relative to an input parameter range) from the Business Calendar, the TSQL Function queries the database, based upon a set of formatted parameters, returned by the CLR Function.

If either of the two dates are not supplied, they are set to the parameter, containing a date.  Today’s date is used for both parameters, if they both are NULL.  An adjacent call, with cognisance of the above rules, use the ToDate as the datum or relative trading date.  The CLR function only returns a FROM, and TO date, formatted in anticipation of the functionality, which is determined by the @pRelativeDay parameter’s value.

The code snippet, below, illustrates the CLR C# code, for the formatter function.  The method CalendarTradingDateParameterFormatter receives an input parameter (_functionalIndicator) value that determines the function, and bases the route of code to execute on it.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
    #region CalendarTradingDateParameterFormatterFunction
    /// <summary>
    /// This CLR function ensures that calendar query parameters are correctly formatted for the three types of calls:
    ///     1. Date range validation against trading calendar
    ///     2. Current date trading calendar validation (0), i.e. is the date a trading date?
    ///     3. adjacent  trading date validation, relative to a datum (-1) previous trading day
    ///     4. Adjascet trading date validation , relative to a datume (1) next trading day
    /// The parameters are used in a set-based query, against the trading calendar, to return valid trading dates.
    /// </summary>
    /// <param name="_fromdate">first date in the range</param>
    /// <param name="_toDate">last date in the range</param>
    /// <param name="_functionalIndicator">[-1|0|1|2] (adjacent  previous|current|adjacent  next|range]</param>
    /// <returns></returns>
    [SqlFunction(   Name = "STA_CalendarTradingDateParameterFormatter"
                ,   DataAccess = DataAccessKind.None
                ,   IsPrecise = false
                ,   SystemDataAccess = SystemDataAccessKind.None
                ,   TableDefinition = "FromDate DATE, ToDate DATE"
                ,   FillRowMethodName = "FillRow"
    public static IEnumerable CalendarTradingDateParameterFormatter(    SqlDateTime _fromdate
                                                                    ,   SqlDateTime _toDate
                                                                    ,   SqlInt32    _functionalIndicator
        // Where both input param. dates are NULL, assume default values, i.e. today
        DateTime fromDate = DateTime.Today;
        DateTime toDate = fromDate;

        ArrayList _resultTable = new ArrayList();
        ParameterStruct _resultRow = new ParameterStruct();

        /* -------------------------------------------------------------------------------------
        // Ensure that any parameter combination, is returned as an intelligible set
        // First ensure that the from and to dates have values.  If either value is NULL
        // assume the other value for both
         -------------------------------------------------------------------------------------- */
        if (_fromdate.IsNull || _toDate.IsNull)
            if (!(_fromdate.IsNull && _toDate.IsNull))
                if (_fromdate.IsNull)
                    fromDate = (DateTime)_toDate;
                    toDate = fromDate;
                    toDate = (DateTime)_fromdate;
                    fromDate = toDate;
            // Both dates have been supplied by caller
            fromDate = (DateTime)_fromdate;
            toDate = (DateTime)_toDate;

        /* -----------------------------------------------------------------------------------------
           Input prameters are all intialised with meaning values
         * The next step is to allow for previous and successive (adjacent ) trade date selections,
         * relative to a datumm, i.e. the ToDate.
         ------------------------------------------------------------------------------------------ */
        if (_functionalIndicator < 2)
            DateTime _datum = new DateTime();

            // This condition tests current, or previous (adjacent ) day, for trading date
            if (_functionalIndicator <= 0)
                _datum = toDate;
                fromDate = _datum.Subtract(new TimeSpan(7, 0, 0, 0));
                _datum = fromDate;
                toDate = _datum.Add(new TimeSpan(7, 0, 0, 0));

        _resultRow.Fromdate = fromDate;
        _resultRow.Todate = toDate;
        return _resultTable;

    /// <summary>
    /// This method is implicitly called by the SQL/CLR framework each time the MoveNext() method, 
    /// on the returned IEnumerable object, or type that implements the IEnumerable interface, is called
    /// </summary>
    /// <param name="arrayObject">ArrayList used for storing the struct(s)</param>
    /// <param name="fromDate">value of the struct's equivalent item</param>
    /// <param name="toDate">value of the struct's equivalent item</param>
    public static void FillRow(object arrayObject, out DateTime fromDate, out DateTime toDate)
        ParameterStruct pStruct = (ParameterStruct)arrayObject;
        fromDate = pStruct.Fromdate;
        toDate = pStruct.Todate;

#region ExtraCode
/// <summary>
/// This struct stores the conformed parameters of the calendar function call
/// </summary>
struct ParameterStruct
    private DateTime _todate;
    private DateTime _fromdate;

    public ParameterStruct(DateTime _fDate, DateTime _tDate)
        _fromdate = _fDate;
        _todate = _tDate;

    #region Accessors
    /// <summary>
    /// Accessor for the FromDate
    /// </summary>
    public DateTime Fromdate
        get { return _fromdate; }
        set { _fromdate = value; }
    /// <summary>
    /// Accessor for the ToDate
    /// </summary>
    public DateTime Todate
        get { return _todate; }
        set { _todate = value; }

As previously stated, the magic of this code lies in the FillRowMethodName.  In this example, the parameters (values to retur to the calling function) are wrapped in a struct that goes into an ArrayList.  Relating what you see here, to database terminology, consider the struct as a row in a table, and the ArrayList is the actual table, together being the package that holds the data.  The object, of the FillRow method, is returned by the CLR Function as the TableDefinition of the CalendarTradingDateParameterFormatter method that conforms to the table schema defined, to hold the returned data-set.

The TSQL function’s functionality is not important for this example, save to know that the CLR Function is called and the returned values used as input parameters, to query the business calendar in the database.  The TSQL statement, in the calling function, is used to initialise internal variables that are used in the actual queries predicates, to query the database.

  -- Format the input parameters, given the function, i.e. the value of @pRelativeDay parameter.
  SELECT      @vFromDate = FromDate
           ,  @vToDate = ToDate
  FROM      [dbo].[STA_CalendarTradingDateParameterFormatter](@pFromDate, @pToDate, @pRelativeDay);

The function’s main query sends these values to the database, to obtain valid business trading date(s).

Deploy/ Publish a Database

In Visual Studio 2012, to deploy a database into a desired environment, is a much reduced function, over previous versions.  Publish profiles are created, and customised per environment.  As many as required Publish Profiles, can be created, each corresponding to an environment.  Each profile is saved to the project and can be run from within the project’s environment, to the targeted platform.  Publish attributes are also configurable under the [Advanced] button on the: Publish Database <environment>.publish.xml window.  These govern what is done during the Publish, such as dropping the database before publishing, or stopping the publish where data loss might occur.

Creating a Publish Profile

A Visual Studio 2012 database project solution can have multiple database projects, each corresponding to a physical database.  Every project requires a Publish Profile.  This or these are created by right-clicking the project and selecting Publish.  The project build is a prerequisite to a publish, both for the execution, and the creation.

Create a Publish Profile for a Database

Create a Publish Profile for a Database

On a successful build, of the project, the Publish Database window is displayed.  All the required configuration is provided, e.g. database connection, name, and other Advanced properties.  The profile is saved for the purpose of re-execution, or republish.  Environment variables, just like in the previous versions of Visual Studio, can still be added or changed, for the project.

The Database Publish Profile

The Database Publish Profile

The Publish Profile is purposed to target an environment, i.e. a database hosted on a particular server or environment.  The Target database connection field (in the familiar connection window) is configured to connect to the required database.  NOTE: If the database does not yet exist, i.e. this is the first publish, simply provide the desired name of the database, in the Database name field, and leave the rest up to the Publish.  For a non-existing database,  [Test Connection] button, on the connection configuration window, fails, but Visual Studio will obediently create the database during the Publish execution.

The [Advanced] button provides access to database’s particular settings, controlling what actions to perform when publishing.  The settings are reasonably self explanatory.

The [Save Profile As…] button allows the Publish Profile to be saved into a specific a folder location.  It can subsequently be added to the project (placing it at the root), by adding an existing item, like adding any other existing database artefact.

Publish Output Report

Publish Output Report

The [Publish] button publishes the database to the designated server or environment.  The published results are displayed in the Data Tools Operations pane, one of the tabs at the bottom of the Visual Studio 2012 Database Project IDE.

Displayed in the illustration (left), is the Data Tools Operations output, for the Publish Profile of a database called Statistics, created on the Localhost, having been successful.

Published Database in SQL Server Management Studio

Published Database in SQL Server Management Studio

The published database can be inspected, by either opening an instance of SSMS, or using the SQL Server Object Explorer window in Visual Studio, by locating the correct database node and opening Databases folder, to reveal the newly added database.

Publish Profile Added to Project

Publish Profile Added to Project

The Publish Profile, added to Visual Studio’s database project for the respective database in question, is an xml file, available in the root (or wherever it has been saved), from which subsequent deployments can be conducted.  It is as simple as double-clicking the file to pop-up the Publish Database window, from whence to start the publish action.

This action, once again, builds the project, and on a successful build, pauses for the developer to click the [Publish] button.

NOTE:  Using SQLCMD variables, requires that the Publish Profile, and the Visual Studio Configuration (Debug/ Release) be synchronised.  That is, when releasing to a specific environment (in this case: using Visual Studio) then the Publish Profile, and Configuration must match.

When Publishing a database, the Publish Database window presents all Environment Variables in the SQLCMD variables grid.  These have to be completed for the profile.  The profile being a function of the Configuration (Debug/ Release), and the Publish Profile, i.e. that all the variables correspond to the particular environment being released to.  Where the Publish Profile hadn’t been saved, for the Environmental Variables, these are populated, using the set-up default values (defined in the project’s properties), by clicking the [Load values] button, on the Publish window.

In the next section, the post-deployment script execution is discussed, for loading data into new tables, which uses a SQLCMD environment variable to determine whether or not it should be loading the data, depending on the environment, to illustrate how the Publish Profile, and the Environmental Variables, work together.

Post-deployment Script: Populate new Tables with Data

It is often required, especially where a new database is being deployed, or where the Advanced configuration is set to drop the database for each deployment, to load data into tables.  For this purpose, a post-deployment script can be used.

To add a post- (and pre-) deployment script to the database project, is just like adding any other script file.  Right-click the project, select Add new item, and from the Add new Item window, select User Scripts >> Post-Deployment Script.

If data is sourced from an existing database, given no data-masking (obfuscation of client details) is necessary, it is probably best to use a SQL Server Integration Services (SSIS) task (right-click source database, and select Tasks >> Export Data) to put the required data into a delimited flat-file.  Once the file(s) are available, they are added to the Post-deployment script (in reference), and uploaded through a bulk copy script [7].  NOTE: Be careful with BULK COPY, when delimiting the data, because commas are frequently used in text, and they might be columns in the table, for which data is being imported, which means that a comma is present in the data, and is interpreted by the loading statement, as a new column.


For some reason it is an arduous plough in dry earth, to get a nice article on how to write code for a post-deployment script.  Firstly, in order to execute any script that has sqlcmd [8] instructions, it  must be executed in SQLCMD Mode.

Post Deployment SQLCMD Syntax

Post Deployment SQLCMD Syntax: not in SQLCMD Mode

Notice the red squiggle line under the [:r] (in the above illustration) when the script is not set explicitly set  to SQLCMD Mode.  To get the script into SQLCMD Mode,  follow the path (on the main toolbar): SQL >> Transact-SQL Editor >> Execution Settings >> SQLCMD Mode.  The squiggle disappear.

SQLCMD Mode Enabled on Script

SQLCMD Mode Enabled on Script

When the script is in SQLCMD Mode, the sqlcmd specific instructions, are highlighted, as indicated in the snippet on the left.

There is a strong affinity between pre- and post-deployment scripts, sqlcmd, and environmental, or local variables.  These variables are used to substitute globally scoped information that is reused by scripts, e.g. database name, file paths, and the likes, as Environment Variables.

Because a post-deployment script can be used to load data into tables, but it may not be required for the test, or production servers, some kind of switch has to be set, through which to determine the action, corresponding to a particular environment or need.  Visual Studio is able to configure the type of build, in a configuration setting on the project’s properties tab.  Any configuration set-up is possible, with Debug & Release as the defaults.  The Configuration Manager is used through which to configure any additional Configuration types, and this function is a very familiar Visual Studio concept.

Visual Studio Configuration Manager

Visual Studio Configuration Manager

The Configuration, and the Publish Profile must be synchronised to use the appropriate SQLCMD variables that correspond to the environment to which the project should be deployed.  So, when creating the Publish Profile, take care to be cognisant of the Configuration (Debug/ Release) of the environment, and that the variables are affiliated.  The SQLCMD variables are either being populated by the Publish Profile, or manually, using the [Load Values] button, at the point of release.

A deployment script can take advantage of these SQLCMD variables, by providing place-holders for them, on which action is taken.  In the above example, the $(BuildConfiguration) variable is matched to a Configuration; and the specific execution of scripts are consequently chosen, e.g. to load data into specific tables, or any other.   MSDN has an article, speaking about this: “Use sqlcmd with Scripting” [9] providing a few guide lines on using sqlcmd in scripts that tells about built-in variables and how to interact with scripts, from the command-line, using sqlcmd.

Loading Data into Tables

Especially for development and testing, data is required in the data-model, for it to be used in constructing queries, and testing functionality.  Data can be hand-crafted, generated, or loaded from production systems, after applying a data-mask to obfuscate sensitive information.

This data is made available in text documents, either comma, tab, fixed length or pipe delimited.  The fixed length delimited files come from the mainframe era, and there’s good reason for the fixed-length attribute.  If any of the delimitation characters have been used in table data, i.e. in columns, the bulk upload fails, because delimiters indicate columns, not pauses in sentences.  So, the trick is to use a delimiter that no application would allow in the data, of which a comma is probably the most frequently expected culprit, so why not fixed length?

The actual loading of the data is done in the Post-deployment.  A script file, containing the BULK LOAD (example below), is referenced in the Post-deployment script (:r .Trade_dataInsert.sql).

BULK    INSERT [dbo].[Trade]
FROM    '$(DataFileDirectory)Trade.csv'
WITH    (    FIRSTROW = 2
        ,    FIELDTERMINATOR = ','
        ,    ROWTERMINATOR = 'n'

Bulk inserts may also use format files, such as an XML document [13],  generated from command prompt: >bcp .dbo.format nul -c -x -f .xml -t, -T.  After generation, it is retrieved from the folder that the cmd-prompt is set at, when running the bcp command.  The format file contains metadata that depicts the conformance of  the flat data-file (containing the data) to the schema of the table, into which the insert is to take place.

The code snippet (below) depicts a script file, using a format file.

BULK    INSERT [dbo].[CAL_CalendarEvent]
FROM    '$(DataFileDirectory)CAL_CalendarEvent.csv'

WITH    (    FORMATFILE = 'C:<folder path>CalendarEvent_format.xml'

Last Thoughts

Visual Studio 2012 provides a great leap of functionality and ease of use, to the database developer.  It should be the de-facto environment for SQL Server database development.  There are a few niggly things that frustrates an effortless experience, but they seem to be of such a nature that they will be taken care of in the first Service Pack, so there is really no excuse for us not using Visual Studio for database work.


No. Author Name URL Date Authored Date Cited
1.  Drikus Roux  Visual Studio 2012, SSDT: Create your own folder structure  12 Novemebr2012 13 November 2012
2.  MSDN  Using Extended Properties on Database Objects  2008 13 November 2012
3.  Mr. SQL Automatically Generating a data dictionary using extended properties  23 February 2011 13 November 2012
4.  CodePlex  Data Dictionary Creator (software)  15 November 2006 13 November 2012
5.  William Brewer

Towards the self-documenting database: extended properties 07 January 2007 13 November 2012
6.  Dattatrey Sindol  Building a data dictionary  16 April 2012 13 November 2012
7.  Pinal Dave Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server  06 February 2008 13 November 2012
8.  MSDN SQLCMD Utility  2012 14 November 2012
9.  MSDN  Use sqlcmd with scripting  2012 14 November 2012
10.  Drikus Roux  SQL Server Database Coding Standard  22 October 2012 15 November 2012
11.  Drikus Roux  SQL Server Data Tools (SSDT) Database Project, Signed Assembly 31 October 2012 15 November 2012
12.  Valentine Boairkine, Mark Horninger, Herleson Pontes The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design, Table 4.2, p170 2008 16 November 2012
13.  MSDN Create a Format File (SQL Server) 2012 16 November 2012

Last Updated: 20 November 2012: 09h17