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

exec sp_addextendedproperty @name='AllowsDirectSql', @value=0

--exec sp_addrolemember 'db_datareader', 'sp_only'

--exec sp_addrolemember 'db_datawriter', 'sp_only'





USE [PersistenceTest]

GO



/****** Object:  Table [dbo].[CustomerType]    Script Date: 05/05/2012 16:57:22 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[CustomerType](

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

	[Name] [varchar](50) NOT NULL,

 CONSTRAINT [PK_CustomerType] PRIMARY KEY CLUSTERED 

(

	[CustomerTypeId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO









/****** 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,

	[CustomerType] [int] NULL,

	[Field1] [bigint] NULL,

	[Field2] [char](10) NULL,

	[Field3] [date] NULL,

	[Field4] [datetime] NULL,

	[Field5] [datetime2](7) NULL,

	[Field6] [datetimeoffset](7) NULL,

	[Field7] [decimal](18, 0) NULL,

	[Field8] [float] NULL,

	[Field9] [money] NULL,

	[Field10] [nchar](10) NULL,

	[Field11] [ntext] NULL,

	[Field12] [numeric](18, 0) NULL,

	[Field13] [nvarchar](50) NULL,

	[Field14] [real] NULL,

	[Field15] [smalldatetime] NULL,

	[Field16] [smallint] NULL,

	[Field17] [smallmoney] NULL,

	[Field18] [text] NULL,

	[Field19] [timestamp] NULL,

	[Field20] [tinyint] NULL,

	[Field21] [uniqueidentifier] NULL,

	[Field22] [varchar](50) NULL,

	[Field23] [xml] NULL,

	[Field24] [varbinary](50) NULL,

	[Field25] [sql_variant] NULL,

	[Picture] [varbinary](max) NULL,

	[Field27] [bit] NULL,

 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 

(

	[CustomerId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO



ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Customer] FOREIGN KEY([CustomerType])

REFERENCES [dbo].[CustomerType] ([CustomerTypeId])

GO



ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Customer]

GO



SET ANSI_PADDING OFF

GO





USE [PersistenceTest]

GO



/****** Object:  Table [dbo].[Invoice]    Script Date: 05/05/2012 16:57:47 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE TABLE [dbo].[Invoice](

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

	[CustomerId] [int] NOT NULL,

	[Amount] [money] NULL,

 CONSTRAINT [PK_Invoice] PRIMARY KEY CLUSTERED 

(

	[InvoiceId] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO



ALTER TABLE [dbo].[Invoice]  WITH CHECK ADD  CONSTRAINT [FK_Invoice_Customer] FOREIGN KEY([CustomerId])

REFERENCES [dbo].[Customer] ([CustomerId])

GO



ALTER TABLE [dbo].[Invoice] CHECK CONSTRAINT [FK_Invoice_Customer]

GO









USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[delete_Customer]

	@CustomerId int

as

	delete from

		Customer

	where

		CustomerId = @CustomerId



GO





USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[delete_Invoice]

	@InvoiceId int

as

	delete from

		Invoice

	where

		InvoiceId = @InvoiceId



GO





USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE procedure [dbo].[list_Customer]

as

	select

		*

	from

		Customer



GO





USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[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 list_CustomerWithSummary

*/



CREATE procedure [dbo].[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].[list_Invoice]    Script Date: 04/03/2012 16:59:41 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[list_Invoice]

as

	select

		*

	from

		Invoice



GO





USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



create procedure [dbo].[list_InvoiceForCustomer]

	@CustomerId int

as

	select

		*

	from

		Invoice

	where

		CustomerId = @CustomerId





GO





USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





CREATE procedure [dbo].[load_Customer]

	@CustomerId int

as

	select

		*

	from

		Customer

	where

		CustomerId = @CustomerId



GO





USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





create procedure [dbo].[load_Invoice]

	@InvoiceId int

as

	select

		*

	from

		Invoice

	where

		InvoiceId = @InvoiceId



GO





USE [PersistenceTest]

GO

/****** Object:  StoredProcedure [dbo].[store_Customer]    Script Date: 05/05/2012 16:58:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO





/*



select * from Customer

exec store_Customer 3, 'lskdjf'

exec sys_CodeForTable 'Customer'

*/



create procedure store_Customer

	@CustomerId int output,

	@Name varchar(50),

	@CustomerType int,

	@Field1 bigint,

	@Field2 char(10),

	@Field3 date,

	@Field4 datetime,

	@Field5 datetime2(7),

	@Field6 datetimeoffset(7),

	@Field7 decimal,

	@Field8 float,

	@Field9 money,

	@Field10 nchar(20),

	@Field11 ntext,

	@Field12 numeric,

	@Field13 nvarchar(100),

	@Field14 real,

	@Field15 smalldatetime,

	@Field16 smallint,

	@Field17 smallmoney,

	@Field18 text,

	@Field19 timestamp,

	@Field20 tinyint,

	@Field21 uniqueidentifier,

	@Field22 varchar(50),

	@Field23 xml,

	@Field24 varbinary(50),

	@Field25 sql_variant,

	@Picture varbinary(max),

	@Field27 bit

as

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

		update Customer set

			Name = @Name,

			CustomerType = @CustomerType,

			Field1 = @Field1,

			Field2 = @Field2,

			Field3 = @Field3,

			Field4 = @Field4,

			Field5 = @Field5,

			Field6 = @Field6,

			Field7 = @Field7,

			Field8 = @Field8,

			Field9 = @Field9,

			Field10 = @Field10,

			Field11 = @Field11,

			Field12 = @Field12,

			Field13 = @Field13,

			Field14 = @Field14,

			Field15 = @Field15,

			Field16 = @Field16,

			Field17 = @Field17,

			Field18 = @Field18,

			Field20 = @Field20,

			Field21 = @Field21,

			Field22 = @Field22,

			Field23 = @Field23,

			Field24 = @Field24,

			Field25 = @Field25,

			Picture = @Picture,

			Field27 = @Field27

		where

			CustomerId = @CustomerId

	end else begin

		insert into Customer (

			Name,

			CustomerType,

			Field1,

			Field2,

			Field3,

			Field4,

			Field5,

			Field6,

			Field7,

			Field8,

			Field9,

			Field10,

			Field11,

			Field12,

			Field13,

			Field14,

			Field15,

			Field16,

			Field17,

			Field18,

			Field20,

			Field21,

			Field22,

			Field23,

			Field24,

			Field25,

			Picture,

			Field27

		) values (

			@Name,

			@CustomerType,

			@Field1,

			@Field2,

			@Field3,

			@Field4,

			@Field5,

			@Field6,

			@Field7,

			@Field8,

			@Field9,

			@Field10,

			@Field11,

			@Field12,

			@Field13,

			@Field14,

			@Field15,

			@Field16,

			@Field17,

			@Field18,

			@Field20,

			@Field21,

			@Field22,

			@Field23,

			@Field24,

			@Field25,

			@Picture,

			@Field27

		)



		set @CustomerId = scope_identity()

	end



return @CustomerId



GO



USE [PersistenceTest]

GO



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

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE procedure [dbo].[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



create function [dbo].[fn_SqlToCSharpTypeName] (

	@SqlType varchar(50)

) returns varchar(50) as

begin



	return

	case @SqlType

	when 'bigint' then 'Int64'

	when 'binary' then 'Byte[]'

	when 'bit' then 'bool'

	when 'char' then 'string'

	when 'date' then 'DateTime'

	when 'datetime' then 'DateTime'

	when 'datetime2' then 'DateTime'

	when 'datetimeoffset' then 'DateTimeOffset'

	when 'decimal' then 'Decimal'

	when 'float' then 'double'

	when 'geography' then 'string'

	when 'geometry' then 'string'

	when 'hierarchyid' then 'int'

	when 'image' then 'Byte[]'

	when 'int' then 'int'

	when 'money' then 'Decimal'

	when 'nchar' then 'string'

	when 'ntext' then 'string'

	when 'numeric' then 'Decimal'

	when 'nvarchar' then 'string'

	when 'real' then 'double'

	when 'smalldatetime' then 'DateTime'

	when 'smallint' then 'Int16'

	when 'smallmoney' then 'Decimal'

	when 'sql_variant' then 'object'

	when 'sysname' then 'string'

	when 'text' then 'string'

	when 'time' then 'DateTime'

	when 'timestamp' then 'Byte[]'

	when 'tinyint' then 'Byte'

	when 'uniqueidentifier' then 'Guid'

	when 'varbinary' then 'Byte[]'

	when 'varchar' then 'string'

	when 'xml' then 'string'

	end

end



GO



USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_StructureForTable]    Script Date: 05/05/2012 16:55:36 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





/*

exec sys_StructureForTable 'Customer'

*/





CREATE procedure [dbo].[sys_StructureForTable]

	@TableName varchar(100)

with execute as owner

as



	Select

		TableName = o.Name,

		c.Name,

		Type = t.Name,

		NETType = dbo.fn_SqlToCSharpTypeName(t.Name),

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end

	From

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'U'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    Where

       o.Name = @TableName

       --and IsOutParam = 1

    Order By c.ColOrder

 

GO





USE [PersistenceTest]

GO

/****** Object:  StoredProcedure [dbo].[sys_ParametersForSP]    Script Date: 05/05/2012 16:55:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



/*

exec sys_ParametersForSP 'store_Customer'

*/



create procedure [dbo].[sys_ParametersForSP]

	@SPName varchar(100)

with execute as owner

as



Select

	SPName = o.Name,

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

	Type = t.Name,

	c.IsOutParam,

	c.Length,

	c.xprec,

	c.IsNullable,

	AutoIncrement = case when t.name = 'timestamp' then 1

					when c.status = 128 then 1 else 0 end



From

	SysColumns c

	join SysObjects o on o.id = c.id and o.xtype = 'P'

	join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

Where

   o.Name = @SPName

   --and IsOutParam = 1

Order By

	c.ColOrder

 

 GO







USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_ExtendedProperty]    Script Date: 05/05/2012 16:56:16 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





/*

exec sys_ExtendedProperty 'AllowsDirectSql'

*/





CREATE procedure [dbo].[sys_ExtendedProperty]

	@PropertyName varchar(100)

with execute as owner

as



	select value from sys.extended_properties where name = @PropertyName



GO









USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_CodeForTable]    Script Date: 05/05/2012 16:56:35 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





/*



exec sys_CodeForTable 'Customer'



*/





CREATE procedure [dbo].[sys_CodeForTable]

	@TableName varchar(100)

with execute as owner

as



	select

		CSharp = 'public ' + dbo.fn_SqlToCSharpTypeName(t.Name) + ' ' + c.Name + ' { get; set; }',

		SqlParms = '@' + c.Name + ' '

			+ case t.Name when 'varchar' then 'varchar(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				when 'nvarchar' then 'nvarchar(' + convert(varchar(5), c.Length) + ')'

				when 'char' then 'char(' + convert(varchar(5), c.Length) + ')'

				when 'nchar' then 'nchar(' + convert(varchar(5), c.Length) + ')'

				when 'datetime2' then 'datetime2(7)'

				when 'datetimeoffset' then 'datetimeoffset(7)'

				when 'varbinary' then 'varbinary(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				else t.Name end

			+ ',',

		FieldList = c.Name + ',',

		ParmList = '@' + c.Name + ',',

		UpdateList = c.Name + ' = @' + c.Name + ',',

		o.Name As TableName,

		c.Name as ColumnName,

		t.Name As Type,

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end,

		*

	from

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'U'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    where

       o.Name = @TableName

       --and IsOutParam = 1

    order by c.ColOrder

 



GO









USE [PersistenceTest]

GO



/****** Object:  StoredProcedure [dbo].[sys_CodeForSP]    Script Date: 05/05/2012 16:56:50 ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



 

 

CREATE procedure [dbo].[sys_CodeForSP]

	@SPName varchar(100)

with execute as owner

as



	select

		CSharp = 'public ' + dbo.fn_SqlToCSharpTypeName(t.Name) + ' ' + substring(c.Name, 2, len(c.Name)) + ' { get; set; }',

		ParmList = c.Name + ' '

			+ case t.Name when 'varchar' then 'varchar(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				when 'nvarchar' then 'nvarchar(' + convert(varchar(5), c.Length) + ')'

				when 'char' then 'char(' + convert(varchar(5), c.Length) + ')'

				when 'nchar' then 'nchar(' + convert(varchar(5), c.Length) + ')'

				when 'datetime2' then 'datetime2(7)'

				when 'datetimeoffset' then 'datetimeoffset(7)'

				when 'varbinary' then 'varbinary(' + convert(varchar(5), case when c.Length = -1 then 'max' else CONVERT(varchar(5), c.Length) end) + ')'

				else t.Name end

			+ ',',

		FieldList = substring(c.Name, 2, len(c.Name)) + ',',

		ValueList = c.Name + ',',

		UpdateList = substring(c.Name, 2, len(c.Name)) + ' = ' + c.Name + ',',

		o.Name As TableName,

		c.Name as ColumnName,

		t.Name As Type,

		c.Length,

		c.xprec,

		c.IsNullable,

		AutoIncrement = case when t.name = 'timestamp' then 1

						when c.status = 128 then 1 else 0 end

	from

		SysColumns c

		join SysObjects o on o.id = c.id and o.xtype = 'P'

		join SysTypes t on c.xtype = t.xtype and c.xusertype = t.xusertype

    where

       o.Name = @SPName

       --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 | Mobile
Web02 | 2.8.141022.2 | Last Updated 22 Sep 2014
Article Copyright 2012 by andrewbb@gmail.com
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid