Visual Studio 2013 Database Project: Working the Project LocalDbs

Visual Studio 2013 Database Project: Working the Project LocalDbs

Overview

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]

Advertisements

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

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

Overview

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.

1
2
3
4
5
6
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				
AS

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. 

1
2
3
4
5
6
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.				
AS

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
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
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.				
AS
/* ==================================================================================================================================
	ABSTRACT:	This procedure executes for $projectname$ and. . . 
	VERSION:	$ItemVersion$ (MAJOR.MINOR.PATCH)

	OWNER:		$ItemOwner$
	TEMPLATE:	$TemplateVersion$ (MAJOR.MINOR.PATCH)

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

											<< See bottom of item for details >>
=================================================================================================================================== */
BEGIN
	SET NOCOUNT ON;
	--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	--				READ COMMITTED
	--				REPEATABLE READ
	--				SNAPSHOT
	--				SERIALIZABLE;
	
	-- --------------------------------------------------------------------------------------------------
	--	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
	-- --------------------------------------------------------------------------------------------------
	SET XACT_ABORT ON;
	
	-- ------------------------------------------------------------------------------------
	--	VALIDATE INCOMING PARAMETERS
	-- ------------------------------------------------------------------------------------
	-- None.
		
	-- -------------------------------------------------------------------------------------
	--	DECLARE LOCAL VARIABLES
	-- -------------------------------------------------------------------------------------
	-- 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 LOCAL VARIABLES
	-- -------------------------------------------------------------------------------------
	
	-- Initialise processing variables
	SET			@LocalVariable1Name	=	0;
	SET			@LocalVariable2Name	=	'2011-01-01';

	/* =========================================================================================
		MAIN CODE EXECUTION
	============================================================================================ */
	BEGIN TRY
		BEGIN TRANSACTION;
		-- { sql_statement | statement_block }
		COMMIT TRANSACTION;
		RETURN(@vErrorNumber);
	END TRY
	BEGIN CATCH
		/* ------------------------------------------------------------------------------------------
			ERROR HANDLING
			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
			https://msdn.microsoft.com/en-us/library/ms164086.aspx

			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);
		-- -------------------------------------------------------------------------
		--	END TRANSACTION CORRECTLY.		
		--	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
		--	https://msdn.microsoft.com/en-us/library/ms175976.aspx
		--  Any custom error loggin should be implemented here
		-- -------------------------------------------------------------------------
		IF (XACT_STATE() = 1) 
		BEGIN
			COMMIT TRANSACTION;
			PRINT N'Transaction('+ISNULL(ERROR_PROCEDURE(),N'AdHoc SQL')+N'): COMMITTED';
			RETURN(@vErrorNumber);
		END ELSE
		BEGIN
			IF (XACT_STATE() != 0) ROLLBACK TRANSACTION;
			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+
			--THROW; 
			RETURN(@vErrorNumber);
		END
	END CATCH;

	-- Cleanup/ Housekeeping
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Database Default
	SET XACT_ABORT OFF;
	SET NOCOUNT OFF;
END

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

	OBJECT VERSION HISTORY
	AUTHOR:	<Name.Last Name>, ORGANISATION: <Company Name>, AUTHORED DATE <YYYY-MM-DD>

	VERSION		DATE		NAME SURNAME		CHANGE DETAILS
	---------	-----------	-----------------	---------------------------------------------------------------------------------------
	$ItemVersion$		YYYY-MM-DD	Name.Surname		Change details are.....

												<< See bottom of item for details >>
=================================================================================================================================== */
GO
/* ---------------------------------------------------------------------------------------------
	OBJECT METADATA
	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;
GO				

-- 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;
GO

-- 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;
GO

-- 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;
GO					
-- 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;	
GO
-- 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;
GO
-- 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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<VSTemplate Version="3.0.0" xmlns="http://schemas.microsoft.com/developer/vstemplate/2005" Type="Item">
  <TemplateData>
    <DefaultName>ACreateStoredProcedure.sql</DefaultName>
    <Name>ACreateStoredProcedure</Name>
    <Description>Default CreateStoredProcedure for Acme Corporation</Description>
    <ProjectType>SQLDB</ProjectType>
    <SortOrder>10</SortOrder>
    <Icon>__TemplateIcon.ico</Icon>
  </TemplateData>
  <TemplateContent>
    <References />
    <ProjectItem ReplaceParameters="true" TargetFileName="$fileinputname$.sql" >Z Create Stored Procedure.sql</ProjectItem>
    <CustomParameters>
	<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"/>
    </CustomParameters>
  </TemplateContent>
</VSTemplate>

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

Overview

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]

SQL Server Data via Enterprise Library 5.0

Introduction

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.

Problem

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.

Solution

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 Samples.zip], 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):

<configuration>
    <configSections>
        <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" />
    </configSections>
<dataConfiguration defaultDatabase="TradeInformation" />
<connectionStrings>
    <add name="TradeInformation" connectionString="Database=TradeInformation;Server=TradeDatabasseServer;Integrated Security=SSPI"
        providerName="System.Data.SqlClient" />
</connectionStrings>
<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: http://visualstudiogallery.msdn.microsoft.com/029292f0-6e66-424f-8381-3454c8222f9a [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 [http://gabrielmagana.com/2010/04/solution-to-the-outputpath-property-is-not-set-for-this-project/] and [http://connect.microsoft.com/VisualStudio/feedback/details/518181/msbuild-gets-confused-with-build-configuration-and-starts-generating-output-path-errors], 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):

Code:

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;

namespace za.co.acme.DataAccess
{
    class Program
    {
        static Database defaultDB = null;
        static void Main(string[] args)
        {

            defaultDB = EnterpriseLibraryContainer.Current.GetInstance<Database>();
            ReadTradesToConsole();
        }
        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());
                    }
                    Console.ReadLine();
                }
            }
        }
    }
}

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.

Citations

No. Author Web Site Name URL Date Authored Date Cited
 1.  Microsoft  MSDN  http://msdn.microsoft.com/en-us/library/ff632023.aspx  May 2011  28 January 2013
2.  Microsoft  CodePlex  http://entlib.codeplex.com/  14 December 2012  28 January 2013
3.  Microsoft  MSDN: Using the Configuration Tools  http://msdn.microsoft.com/en-us/library/ff664633(v=pandp.50).aspx  na  28 January 2013
4.  Microsoft  MSDN: Visual Studio Geleray – EnterpriseLibrary.Config http://visualstudiogallery.msdn.microsoft.com/029292f0-6e66-424f-8381-3454c8222f9a  na  14 March 2013
5.  NuGet Docs  NuGet Frequently Asked Questions  http://docs.nuget.org/docs/start-here/nuget-faq  na  15 March 2013

Last Updated: 19 March 2013 09h19