Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Populating a business logical layer from Stored Procedures

, 22 Sep 2014 CPOL
A brief look at a technique mapping tables to a basic business layer.
doPersistence-noexe.zip
doPersistence
doPersistence.csproj.user
doPersistence.csproj.vspscc
obj
Debug
Release
Properties
doLogicCode
bin
Debug
doLogicCode.vshost.exe.manifest
doLogicCode.csproj.vspscc
obj
x86
Debug
doLogicCode.frmCodeGenerator.resources
doLogicCode.Properties.Resources.resources
Properties
Settings.settings
doPersistence.zip
Test
doLogic
bin
Debug
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
Release
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
TempPE
Properties
doLogicCode
bin
Debug
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doLogicCode.vshost.exe.manifest
doPersistence.dll
doPersistence.pdb
obj
x86
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.exe
doLogicCode.frmCodeGenerator.resources
doLogicCode.pdb
doLogicCode.Properties.Resources.resources
GenerateResource.read.1.tlog
GenerateResource.write.1.tlog
ResolveAssemblyReference.cache
TempPE
Properties.Resources.Designer.cs.dll
Properties
Settings.settings
doPersistence
bin
Debug
doPersistence.dll
doPersistence.pdb
Release
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
TempPE
Properties
Test.suo
TestSite
Account
App_Data
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
obj
Debug
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
Release
CSAutoParameterize
original
Account
transformed
Account
Database
DesignTimeResolveAssemblyReferencesInput.cache
Package
PackageTmp
Account
bin
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
Scripts
Styles
ResolveAssemblyReference.cache
TempPE
TestSite.dll
TestSite.pdb
TransformWebConfig
original
transformed
Account
Properties
Scripts
Styles
TestSite.csproj.user
dopersistenceexample.zip
doLogic.dll
doPersistence.dll
doPersistence.dll
doLogic.dll
doPersistence.dll
TestSite.dll
Global.asax
TestSite.csproj.user
dopersistencepocodynamicsqlexample.zip
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogic.dll
doLogic.pdb
ResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
Test.suo
doLogic.dll
doLogic.pdb
doPersistence.dll
doPersistence.pdb
TestSite.dll
TestSite.pdb
Global.asax
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
DesignTimeResolveAssemblyReferencesInput.cache
ResolveAssemblyReference.cache
TestSite.dll
TestSite.pdb
TestSite.csproj.user
PersistenceTest20131129-noexe.zip
PersistenceTest_orig
Account
App_Code
App_Data
Bin
Persistence.pdb
Global.asax
Scripts
Styles
Persistence
bin
Debug
doPersistence.pdb
Persistence.pdb
Release
doPersistence.pdb
doPersistence.csproj.vspscc
obj
Debug
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.pdb
Persistence.pdb
TempPE
Release
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.pdb
TempPE
Persistence.csproj.user
Properties
PersistenceTest_orig.v11.suo
CodeGenerator
bin
Debug
doLogicCode.pdb
doLogicCode.vshost.exe.manifest
doPersistence.pdb
Persistence.pdb
CodeGenerator.csproj.vspscc
obj
x86
Debug
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.pdb
TempPE
Properties
Settings.settings
PersistenceTest20131129.zip
AjaxControlToolkit.dll
Persistence.dll
Persistence.pdb
Global.asax
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
doPersistence.dll
doPersistence.pdb
doPersistence.csproj.vspscc
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.csprojResolveAssemblyReference.cache
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
DesignTimeResolveAssemblyReferencesInput.cache
doPersistence.dll
doPersistence.pdb
Persistence.csproj.user
PersistenceTest_orig.v11.suo
doLogicCode.exe
doLogicCode.pdb
doLogicCode.vshost.exe
doLogicCode.vshost.exe.manifest
doPersistence.dll
doPersistence.pdb
Persistence.dll
Persistence.pdb
CodeGenerator.csproj.vspscc
CodeGenerator.csproj.GenerateResource.Cache
CodeGenerator.csprojResolveAssemblyReference.cache
CodeGenerator.frmCodeGenerator.resources
CodeGenerator.Properties.Resources.resources
DesignTimeResolveAssemblyReferences.cache
DesignTimeResolveAssemblyReferencesInput.cache
doLogicCode.csproj.GenerateResource.Cache
doLogicCode.csprojResolveAssemblyReference.cache
doLogicCode.exe
doLogicCode.pdb
Properties.Resources.Designer.cs.dll
Settings.settings
��USE [master]

GO



CREATE DATABASE [PersistenceTest]



ALTER DATABASE [PersistenceTest] SET ANSI_NULL_DEFAULT OFF 

GO



ALTER DATABASE [PersistenceTest] SET ANSI_NULLS OFF 

GO



ALTER DATABASE [PersistenceTest] SET ANSI_PADDING OFF 

GO



ALTER DATABASE [PersistenceTest] SET ANSI_WARNINGS OFF 

GO



ALTER DATABASE [PersistenceTest] SET ARITHABORT OFF 

GO



ALTER DATABASE [PersistenceTest] SET AUTO_CLOSE OFF 

GO



ALTER DATABASE [PersistenceTest] SET AUTO_CREATE_STATISTICS ON 

GO



ALTER DATABASE [PersistenceTest] SET AUTO_SHRINK OFF 

GO



ALTER DATABASE [PersistenceTest] SET AUTO_UPDATE_STATISTICS ON 

GO



ALTER DATABASE [PersistenceTest] SET CURSOR_CLOSE_ON_COMMIT OFF 

GO



ALTER DATABASE [PersistenceTest] SET CURSOR_DEFAULT  GLOBAL 

GO



ALTER DATABASE [PersistenceTest] SET CONCAT_NULL_YIELDS_NULL OFF 

GO



ALTER DATABASE [PersistenceTest] SET NUMERIC_ROUNDABORT OFF 

GO



ALTER DATABASE [PersistenceTest] SET QUOTED_IDENTIFIER OFF 

GO



ALTER DATABASE [PersistenceTest] SET RECURSIVE_TRIGGERS OFF 

GO



ALTER DATABASE [PersistenceTest] SET  DISABLE_BROKER 

GO



ALTER DATABASE [PersistenceTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

GO



ALTER DATABASE [PersistenceTest] SET DATE_CORRELATION_OPTIMIZATION OFF 

GO



ALTER DATABASE [PersistenceTest] SET TRUSTWORTHY OFF 

GO



ALTER DATABASE [PersistenceTest] SET ALLOW_SNAPSHOT_ISOLATION OFF 

GO



ALTER DATABASE [PersistenceTest] SET PARAMETERIZATION SIMPLE 

GO



ALTER DATABASE [PersistenceTest] SET READ_COMMITTED_SNAPSHOT OFF 

GO



ALTER DATABASE [PersistenceTest] SET HONOR_BROKER_PRIORITY OFF 

GO



ALTER DATABASE [PersistenceTest] SET  READ_WRITE 

GO



ALTER DATABASE [PersistenceTest] SET RECOVERY FULL 

GO



ALTER DATABASE [PersistenceTest] SET  MULTI_USER 

GO



ALTER DATABASE [PersistenceTest] SET PAGE_VERIFY CHECKSUM  

GO



ALTER DATABASE [PersistenceTest] SET DB_CHAINING OFF 

GO





CREATE LOGIN [sp_only] WITH PASSWORD='sp_only', DEFAULT_DATABASE=[PersistenceTest], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF



USE [PersistenceTest]

GO



CREATE USER [sp_only] FOR LOGIN [sp_only] WITH DEFAULT_SCHEMA=[dbo]



create role db_spexecutor

grant execute to db_spexecutor



exec sp_addrolemember 'db_spexecutor', 'sp_only'



-- allow dynamic sql and access to system tables for schema information

exec sp_addrolemember 'db_datareader', 'sp_only'

exec sp_addrolemember 'db_datawriter', 'sp_only'





/****** Object:  Table [dbo].[Customer]    Script Date: 04/03/2012 16:58:07 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[Customer](

	[CustomerId] [int] IDENTITY(1,1) NOT NULL,

	[Name] [varchar](50) NULL

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO





USE [PersistenceTest]

GO



/****** Object:  Table [dbo].[Invoice]    Script Date: 04/03/2012 16:58:34 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[Invoice](

	[InvoiceId] [int] IDENTITY(1,1) NOT NULL,

	[CustomerId] [int] NULL,

	[Amount] [money] NULL

) ON [PRIMARY]



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_delete_Customer]    Script Date: 04/03/2012 16:58:51 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[ab_delete_Customer]

	@CustomerId int

as

	delete from

		Customer

	where

		CustomerId = @CustomerId



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_delete_Invoice]    Script Date: 04/03/2012 16:59:04 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[ab_delete_Invoice]

	@InvoiceId int

as

	delete from

		Invoice

	where

		InvoiceId = @InvoiceId



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_list_Customer]    Script Date: 04/03/2012 16:59:17 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE procedure [dbo].[ab_list_Customer]

as

	select

		*

	from

		Customer



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_list_CustomerWithSummary]    Script Date: 04/03/2012 16:59:30 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



/*

select * from Customer

select * from Invoice



exec ab_list_CustomerWithSummary

*/



CREATE procedure [dbo].[ab_list_CustomerWithSummary]

as



;with summary as (

	select

		CustomerId,

		InvoiceCount = count(*),

		InvoiceTotal = sum(Amount)

	from

		Invoice

	group by

		CustomerId

)

select

	c.*,

	s.InvoiceCount,

	s.InvoiceTotal

from

	Customer c

	left join summary s

		on s.CustomerId = c.CustomerId



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_list_Invoice]    Script Date: 04/03/2012 16:59:41 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[ab_list_Invoice]

as

	select

		*

	from

		Invoice



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_list_InvoiceForCustomer]    Script Date: 04/03/2012 16:59:52 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[ab_list_InvoiceForCustomer]

	@CustomerId int

as

	select

		*

	from

		Invoice

	where

		CustomerId = @CustomerId





GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_load_Customer]    Script Date: 04/03/2012 17:00:02 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE procedure [dbo].[ab_load_Customer]

	@CustomerId int

as

	select

		*

	from

		Customer

	where

		CustomerId = @CustomerId



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_load_Invoice]    Script Date: 04/03/2012 17:00:12 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





create procedure [dbo].[ab_load_Invoice]

	@InvoiceId int

as

	select

		*

	from

		Invoice

	where

		InvoiceId = @InvoiceId



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_store_Customer]    Script Date: 04/03/2012 17:00:21 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





/*



select * from Customer

exec ab_store_Customer 3, 'lskdjf'

*/



CREATE procedure [dbo].[ab_store_Customer]

	@CustomerId int output,

	@Name varchar(50)

as

	if exists(select CustomerId from Customer where CustomerId = @CustomerId) begin

		update Customer set

			Name = @Name

		where

			CustomerId = @CustomerId

	end else begin

		insert into Customer (

			Name	

		) values (

			@Name

		)



		set @CustomerId = scope_identity()

	end

	

return @CustomerId

GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[ab_store_Invoice]    Script Date: 04/03/2012 17:00:36 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE procedure [dbo].[ab_store_Invoice]

	@InvoiceId int output,

	@CustomerId int,

	@Amount money

as

	if exists(select 1 from Invoice where InvoiceId = @InvoiceId)

		update Invoice set

			CustomerId = @CustomerId,

			Amount = @Amount

		where

			InvoiceId = @InvoiceId

	else begin

		insert into Invoice (

			CustomerId,

			Amount

		) values (

			@CustomerId,

			@Amount

		)

		

		set @InvoiceId = scope_identity()

	end

	

return @InvoiceId

GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_ParametersForSP]    Script Date: 04/03/2012 17:00:52 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



/*

exec ims_sys_ParametersBySPName 'ims_store_DealerInventory'

*/



CREATE procedure [dbo].[sys_ParametersForSP]

	@SPName varchar(100)

as



Select

	o.Name As SPName,

	SUBSTRING(c.Name, 2, LEN(c.Name)) As Name,

	t.Name As Type,

	c.IsOutParam,

	c.Length,

	c.xprec,

	IsOptional = c.isnullable

From

   SysColumns c, SysObjects o, SysTypes t

Where

   o.Name = @SPName And

   o.id = c.id And

   c.XType = t.XType

   --and IsOutParam = 1

Order By

	c.ColOrder

 

GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_StructureForTable]    Script Date: 04/03/2012 17:01:01 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



/*

exec ims_sys_StructureByTablename 'Dealers'

*/





CREATE procedure [dbo].[sys_StructureForTable]

	@TableName varchar(100)

as



Select

		o.Name As SPName,

		c.Name,

		t.Name As Type,

		c.Length,

		c.xprec,

		c.IsNullable

	From

       SysColumns c, SysObjects o, SysTypes t

    Where

       o.Name = @TableName And

       o.id = c.id And

       c.XType = t.XType

       --and IsOutParam = 1

    Order By c.ColOrder

 

GO





By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

andrewbb@gmail.com
Architect
United States United States
No Biography provided

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 22 Sep 2014
Article Copyright 2012 by andrewbb@gmail.com
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid