Automate SQL SP And DTO Libraries

by | Nov 15, 2022 | Development, SQL Database

Work smart not hard

By Albert Zakhia, October 7, 2020

Automate SQL SP And DTO Libraries

Anticipating challenges ahead. As I delve into managing a sizable database, the task of creating ‘insert’, ‘select all’, ‘select by identity’, and ‘select by primary key’ stored procedures for each table looms large. It’s a repetitive cycle, requiring meticulous attention to detail. Hence I thought of ways to Automate SQL SP And DTO Libraries.

But that’s not all. Advised to craft a DTO for WebApi use with each table, I foresee a cascade of adjustments every time the table structure evolves—adding, removing, or altering columns mandates corresponding updates to stored procedures and DTOs.

Yet, I embrace my inherent laziness as a catalyst for innovation. It drives me to engineer code that automates the very tasks I’d rather avoid.

So, why not devise a single stored procedure capable of generating all requisite stored procedures and DTOs? While my DTOs are tailored for C#, adapting the code to accommodate Java, JavaScript, Swift, Kotlin, Python, and beyond is well within reach. This approach promises swift adaptability, facilitating seamless modifications across websites, mobile apps, and IoT devices.

While acknowledging existing tools that offer similar functionality, I prefer a solution that aligns with my unique preferences and methodologies. By adhering to my coding standards and principles, I break free from constraints and embrace the freedom to innovate. And best of all, my solution comes at no cost, offering a few days of enjoyable development—a worthwhile endeavor indeed.

In its current iteration, my stored procedure necessitates three parameters:

  1. Tablename: This denotes the name of the table for which code generation is required.
  2. GrantExecTo: An optional parameter, specifying a local user to whom execution permission will be granted. Leave it null to retain the grant as a comment.
  3. DtoNamespace: This refers to the namespace of my C# application, ensuring that the DTO is precisely located where it’s needed. Notably, the DTO output within the final SQL script is commented on to prevent interference with SQL and avoid generating errors. Simply copy the code and paste it into your C# application. While a more efficient approach would involve saving the DTO in a dedicated file and adding it to the project, this step is deferred for now.

Of particular significance, the DTO encompasses the following features:

  • Inclusion of all table columns along with their accurate C# data types. A mapping table from SQL to C# types is embedded within the stored procedure (SP), although it can be relocated if necessary. I’ve maintained it within the SP to ensure streamlined execution.
  • Override of the ToString() method to yield the JSON interpretation of the class, leveraging the Newtonsoft.Json library.
  • Modification of the Equals() method to facilitate comparison based on primary keys or identities. Note that the code supports single-column primary keys; multiple columns may fail.
  • Modification of the GetHashCode() method to return either the primary key (if numerical) or the identity (where applicable).
  • Support for == and != operators.

While the stored procedure could be programmed to directly execute and create other stored procedures, my preference is different. I opt to display the code of the stored procedures, review them, and then copy/paste/execute if they meet the required criteria.

Note: we know that SSMS can generate Stored Procedure templates, but we also know they are useless:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

We also know that SSMS can generate select/insert/update scripts, but also they are useless:

INSERT INTO [dbo].[Stores]
(
	 [Name]
	,[Telephone]
	,[Mobile]
	,[Username]
	,[Password]
	,[Active]
	,[Deleted]
)  
VALUES
(
	 <Name, varchar(100),>
	,<Telephone, varchar(50),>
	,<Mobile, varchar(22),>
	,<Username, varchar(20),>
	,<Password, varchar(20),>
	,<Active, int,>
	,<Deleted, int,>
)
GO

Given that, it is time to prove to Bill [Gates] we can do better!

Let us first create a test table (the listing below is using the same SSMS that we hate :D):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stores](
  [StoreId] [bigint] IDENTITY(1,1) NOT NULL,
  [Name] [varchar](100) NULL,
  [Telephone] [varchar](50) NULL,
  [Mobile] [varchar](22) NULL,
  [Username] [varchar](20) NULL,
  [Password] [varchar](20) NULL,
  [Active] [int] NULL,
  [Deleted] [int] NULL, 
CONSTRAINT [PK_Stores] PRIMARY KEY CLUSTERED 
(
  [StoreId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Stores] ADD  CONSTRAINT [DF_Stores_Active]  DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[Stores] ADD  CONSTRAINT [DF_Stores_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

Now we need to create that magical SP that would generate insert/select stored procedures and will add as a bonus the C# WebApi DTO.

The SP with all its doing is made up of only 400 lines of code 🙂

So let’s start with the good stuff (you can copy/paste the code as is):

-- drop the Stored Procedrue if it exists;
IF exists(SELECT [name] FROM sys.procedures WHERE [name] = 'Admin_CreateInsertProcedure')
	DROP PROC Admin_CreateInsertProcedure;
GO
-- now we can create a new one;
CREATE or alter PROCEDURE Admin_CreateInsertProcedure
	 @Tablename varchar(50)
	,@GrantExecTo varchar(100) = 'admin'
	,@DtoNamespace  varchar(200) = 'DTO'
AS
BEGIN  
	SET NOCOUNT ON;

	DECLARE @comments varchar(max) = '';
	DECLARE @sqlInsert varchar(max) = '';
	DECLARE @sqlSelectAll varchar(max) = '';
	DECLARE @sqlSelectByIdentity varchar(max) = '';
	DECLARE @sqlSelectByPrimaryKey varchar(max) = '';
	DECLARE @DTO varchar(max) = '';
  
	DECLARE @name varchar(100);
	DECLARE @type varchar(100);
	DECLARE @length smallint;
	DECLARE @is_Identity bit;
  
	DECLARE @i int;
	DECLARE @ProcNameInsert varchar(200);
	DECLARE @ProcNameSelectAll varchar(200);
	DECLARE @ProcNameSelectByIdentity varchar(200);
	DECLARE @ProcNameSelectByPrimaryKey varchar(200);
	DECLARE @DropInsert varchar(200);
	DECLARE @DropSelectAll varchar(200);
	DECLARE @DropSelectByIdentity varchar(200);
	DECLARE @DropSelectByPrimaryKey varchar(200);

	DECLARE @csharp varchar(20);
  
	-- let us prepare the DTO
  
	-- first we need to support the type conversion. 
	-- this of course could be moved INTO a separate table
	-- but for the sake of this exercise, we will inject it here
  
	DECLARE @TypeConvert table (mssql varchar(20) primary key, csharp varchar(20));
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('BigInt', 'long');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Binary', 'byte[]');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Image',  'byte[]');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Timestamp',  'byte[]');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('VarBinary', 'byte[]');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Bit', 'bool');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Char','string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('NChar', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('NText', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('NVarChar', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Text', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('VarChar', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Xml', 'string');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('DateTime', 'DateTime');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('SmallDateTime', 'DateTime');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Date', 'DateTime');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Time', 'DateTime');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('DateTime2', 'DateTime');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Decimal', 'decimal');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Money', 'decimal');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('SmallMoney', 'decimal');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Float', 'double');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Int', 'int');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Real', 'float');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('UniqueIdentifier', 'Guid');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('SmallInt', 'short');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('TinyInt', 'byte');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Variant', 'object');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Udt', 'object');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('Structured', 'DataTable');
	INSERT INTO @TypeConvert (mssql,csharp) VALUES ('DateTimeOffset', 'DateTimeOffset');
  
	DECLARE @DtoClass varchar(100) = @Tablename + 'Dto';
	SET @DTO = @DTO + '/*' + char(10);
	SET @DTO = @DTO + 'using System;' + char(10);
	SET @DTO = @DTO + 'using System.Collections.Generic;' + char(10);
	SET @DTO = @DTO + 'using Newtonsoft.Json;' + char(10);
	SET @DTO = @DTO + 'namespace ' + @DtoNamespace  + char(10);
	SET @DTO = @DTO + '{' + char(10);
	SET @DTO = @DTO + char(9) + '[Serializable]' + char(10);
	SET @DTO = @DTO + char(9) + 'public class ' + @DtoClass + char(10);
	SET @DTO = @DTO + char(9) + '{' + char(10);

	-- find primary key, type  AND  length
	-- code FROM https://intellipaat.com/community/20043/how-do-you-list-the-primary-key-of-a-sql-server-table;
	DECLARE @PkColumn varchar(200) = 
	(
		SELECT Col.Column_Name FROM 
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
		INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
		WHERE  
			Col.Constraint_Name = Tab.Constraint_Name   
			AND  Col.Table_Name = Tab.Table_Name
			AND  Constraint_Type = 'PRIMARY KEY'	 
			AND  Col.Table_Name = @Tablename
	);
	
	DECLARE @PkType  varchar(200);
	DECLARE @pkLength varchar(20);
  
	SELECT @PkType = ty.name, @pkLength = c.max_length FROM sys.tables t
		INNER JOIN sys.columns c ON c.object_id = t.object_id
		INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id
	WHERE  t.name = @Tablename  AND  c.name = @PkColumn  AND  ty.name <> 'sysname';
  
	-- let us normalize the primay key type, we can then disregard @pkLength
	IF CHARINDEX('char', @PkType) > 0 or CHARINDEX('binary', @PkType) > 0
		SET @PkType = @PkType + ' ' + iif(@length = -1, 'MAX', cast(@pkLength as varchar)) + ')';
  
	-- find identity  AND  type (type of identity is numeric)
	DECLARE @IdentityColumn varchar(200);
	DECLARE @IdentityType varchar(200);
 
	SELECT @IdentityColumn = c.name, @IdentityType = ty.name FROM sys.tables t
		INNER JOIN sys.columns c ON c.object_id = t.object_id
		INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id
	WHERE  t.name = @Tablename  AND  c.is_identity = 1  AND  ty.name <> 'sysname';

	SET @ProcNameInsert = 'Client_' + @Tablename + 'Insert';
	SET @ProcNameSelectAll = 'Client_' + @Tablename + 'SelectAll';
	SET @ProcNameSelectByIdentity = 'Client_' + @Tablename + 'SelectBy' + @IdentityColumn;
	SET @ProcNameSelectByPrimaryKey = 'Client_' + @Tablename + 'SelectBy' + @PkColumn;

	-- IF procedure exists, we can drop it, or maybe alter it?
	-- it depENDs ON your flavour
 
	-- METHOD 1: drop it like it's hot
	IF exists(SELECT name FROM sys.procedures WHERE  name = @ProcNameInsert)
	BEGIN
		SET @DropInsert = 'DROP PROCEDURE [' + @ProcNameInsert + '];' + char(10);
		SET @DropInsert = @DropInsert  + 'GO' + char(10);
	END;
 
	IF exists(SELECT name FROM sys.procedures WHERE  name = @ProcNameSelectAll)
	BEGIN
		SET @DropSelectAll = 'DROP PROCEDURE [' + @ProcNameSelectAll + '];' + char(10);
		SET @DropSelectAll = @DropSelectAll  + 'GO' + char(10);
	END;
 
	IF exists(SELECT name FROM sys.procedures WHERE  name = @ProcNameSelectByIdentity) 
	BEGIN
		SET @DropSelectByIdentity = 'DROP PROCEDURE [' + @ProcNameSelectByIdentity + '];' + char(10);
		SET @DropSelectByIdentity = @DropSelectByIdentity  + 'GO' + char(10);
	END;
 
	IF exists(SELECT name FROM sys.procedures WHERE  name = @ProcNameSelectByPrimaryKey)
	BEGIN
		SET @DropSelectByPrimaryKey = 'DROP PROCEDURE [' + @ProcNameSelectByPrimaryKey + '];' + char(10);
		SET @DropSelectByPrimaryKey = @DropSelectByPrimaryKey  + 'GO' + char(10);
	END;
 
	SET @Comments = @Comments  + '-- =================================================' + char(10);
	SET @Comments = @Comments  + '-- Template autogenerated by Albert Zakhia'  + char(10);
	SET @Comments = @Comments  + '-- =================================================' + char(10);
	SET @Comments = @Comments  + 'SET ANSI_NULLS ON;' + char(10);
	SET @Comments = @Comments  + 'GO' + char(10);
	SET @Comments = @Comments  + 'SET QUOTED_IDENTIFIER ON;' + char(10);
	SET @Comments = @Comments  + 'GO' + char(10);
	SET @Comments = @Comments  + '-- =============================================' + char(10);
	SET @Comments = @Comments  + '-- Author:Albert Zakhia' + char(10);
	SET @Comments = @Comments  + '-- Create date: ' + CONVERT(varchar, getdate(),121);

	SET @sqlInsert = @sqlInsert  + '-- Description: This procedure allows the insertion of data INTO table [' + @Tablename + ']' + char(10);
	SET @sqlInsert = @sqlInsert  + '-- =============================================' + char(10);
	SET @sqlInsert = @sqlInsert  + 'CREATE PROCEDURE [' + @ProcNameInsert + ']' + char(10);

	SET @sqlSelectAll = @sqlSelectAll  + '-- Description:   This procedure allows the selection of all records FROM table [' + @Tablename + ']' + char(10);
	SET @sqlSelectAll = @sqlSelectAll  + '-- =============================================' + char(10);
	SET @sqlSelectAll = @sqlSelectAll  + 'CREATE PROCEDURE [' + @ProcNameSelectAll + ']' + char(10);

	SET @sqlSelectByIdentity = @sqlSelectByIdentity  + '-- Description: This procedure allows the selection of 1 record FROM table [' + @Tablename + '] based ON identity' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity  + '-- =============================================' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity  + 'CREATE PROCEDURE [' + @ProcNameSelectByIdentity + ']' + char(10);

	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey  + '-- Description: This procedure allows the selection of 1 record FROM table [' + @Tablename + '] based ON primary key' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey  + '-- =============================================' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey  + 'CREATE PROCEDURE [' + @ProcNameSelectByPrimaryKey + ']' + char(10);

	-- METHOD 2: use ALTER PROC instead of drop
	-- IF exists(SELECT name FROM sys.procedures WHERE  name = @ProcNameInsert)
	--   SET @sqlInsert = @sqlInsert  + 'ALTER PROCEDURE [' + @ProcNameInsert + ']' + char(10)
 
	-- build parameter list
	DECLARE c cursor READ_ONLY SCROLL for
		SELECT c.name as [name], ty.name as [type], c.max_length as [length], c.is_identity  FROM sys.tables t
			INNER JOIN sys.columns c ON c.object_id = t.object_id
			INNER JOIN sys.types ty ON ty.system_type_id = c.system_type_id
		WHERE  t.name = @Tablename  AND  ty.name <> 'sysname'
		ORDER BY c.column_id;
	OPEN c;
	SET @i = 0;
	FETCH FIRST FROM c INTO @name, @type, @length, @is_Identity;
	WHILE @@FETCH_STATUS = 0
	BEGIN
	-- insert;
		IF @is_Identity = 0
		BEGIN
			SET @sqlInsert = @sqlInsert + char(9) + iif(@i = 0, ' @' , ',@') + @name + ' ' + @Type;
			SET @i = @i + 1;
			IF CHARINDEX('char', @Type) > 0 or CHARINDEX('binary', @Type) > 0
				SET @sqlInsert = @sqlInsert + '(' + iif(@length = -1, 'MAX', cast(@length as varchar)) + ')';
			SET @sqlInsert = @sqlInsert + char(10);
		END;
		FETCH NEXT FROM c INTO @name, @type, @length, @is_identity;
	END;
 
	-- build insert columns code
	SET @sqlInsert = @sqlInsert + 'AS' + char(10);
	SET @sqlInsert = @sqlInsert + 'BEGIN' + char(10);
 
	SET @sqlInsert = @sqlInsert + char(9) + 'SET NOCOUNT ON;' + char(10);
	SET @sqlInsert = @sqlInsert + char(9) + 'INSERT INTO [' + @Tablename + + ']' + char(10);
	SET @sqlInsert = @sqlInsert + char(9) + '(' + char(10);

	-- sql SELECT all
	SET @sqlSelectAll = @sqlSelectAll + 'AS' + char(10);
	SET @sqlSelectAll = @sqlSelectAll + 'BEGIN' + char(10);
 
	SET @sqlSelectAll = @sqlSelectAll + char(9) + 'SET NOCOUNT ON;' + char(10);
	SET @sqlSelectAll = @sqlSelectAll + char(9) + 'SELECT ' + char(10);

	-- sql SELECT by identity;
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + '@' + @IdentityColumn + ' ' + @IdentityType + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'AS' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'BEGIN' + char(10);

	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + 'SET NOCOUNT ON;' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + 'SELECT ' + char(10);

	-- sql SELECT by primary key;
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + '@' + @PkColumn + ' ' + @PkType + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'AS' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'BEGIN' + char(10);

	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + 'SET NOCOUNT ON;' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + 'SELECT ' + char(10);

	SET @i = 0;

	FETCH FIRST FROM c INTO @name, @type, @length, @is_Identity;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @type <> 'sysname'
		BEGIN
			SET @sqlInsert = @sqlInsert + char(9) + char(9) + iif(@i = 0, ' ' , ',') + '['  + @name + ']' + char(10);
			SET @sqlSelectAll = @sqlSelectAll + char(9) + char(9) + iif(@i = 0, ' ' , ',') + '['  + @name + ']' + char(10);
			SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + char(9) + iif(@i = 0, ' ' , ',') + '['  + @name + ']' + char(10);
			SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + char(9) + iif(@i = 0, ' ' , ',') + '['  + @name + ']' + char(10);

			SET @csharp = (SELECT csharp FROM @TypeConvert WHERE  mssql = @type);
			SET @DTO = @DTO + char(9) + char(9) + 'public ' + @csharp + ' ' + @name + ' { get; set; } ' + char(10);
			SET @i = @i + 1;
		END;
		FETCH NEXT FROM c INTO @name, @type, @length, @is_identity;
	END;

	SET @sqlInsert = @sqlInsert + char(9) + ')' + char(10);

	-- build insert value code;
	SET @sqlInsert = @sqlInsert + char(9) + 'VALUES' + char(10);
	SET @sqlInsert = @sqlInsert + char(9) + '(' + char(10);

	SET @sqlSelectAll = @sqlSelectAll + char(9) + 'FROM ['  + @Tablename + '];' + char(10);
	SET @sqlSelectAll = @sqlSelectAll + 'END;' + char(10);

	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + 'FROM ['  + @Tablename + ']' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(9) + 'WHERE  [' + @IdentityColumn + '] = @' + @IdentityColumn + ';' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'END;' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + 'FROM ['  + @Tablename + ']' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(9) + 'WHERE  [' + @PkColumn + '] = @' + @PkColumn + ';' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'END;' + char(10);

	SET @i = 0;
	FETCH FIRST FROM c INTO @name, @type, @length, @is_Identity;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @type <> 'sysname'
		BEGIN
			SET @sqlInsert = @sqlInsert + char(9) + +char(9) + iif(@i = 0, ' @' , ',@') + @name  + char(10);
			SET @i = @i + 1;
		END;
		FETCH NEXT FROM c INTO @name, @type, @length, @is_identity;
	END;
	CLOSE c;
	DEALLOCATE c;

	SET @sqlInsert = @sqlInsert + char(9) + ');' + char(10);
	SET @sqlInsert = @sqlInsert + char(9) + 'SELECT CAST(SCOPE_IDENTITY() AS bigint) AS ''Identity'';' + char(10);
	SET @sqlInsert = @sqlInsert + 'END;' + char(10);

	SET @sqlInsert = @sqlInsert + 'GO' + char(10);
	IF @GrantExecTo IS NULL
		SET @sqlInsert = @sqlInsert + '-- ';
	SET @sqlInsert = @sqlInsert + 'GRANT EXEC ON [' + @ProcNameInsert + '] TO [' + isnull(@GrantExecTo, 'SOMEBODY') + '] AS DBO;' + char(10);
	SET @sqlInsert = @sqlInsert + 'GO' + char(10);
	SET @sqlInsert = @sqlInsert + char(10) + char(13);

	SET @sqlSelectAll = @sqlSelectAll + 'GO' + char(10);
	IF @GrantExecTo IS NULL
		SET @sqlSelectAll = @sqlSelectAll + '-- ';
	SET @sqlSelectAll = @sqlSelectAll + 'GRANT EXEC ON [' + @ProcNameSelectAll + '] TO [' + isnull(@GrantExecTo, 'SOMEBODY') + '] AS DBO;' + char(10);
	SET @sqlSelectAll = @sqlSelectAll + 'GO' + char(10);
	SET @sqlSelectAll = @sqlSelectAll + char(10) + char(13);

	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'GO' + char(10);
	IF @GrantExecTo IS NULL
		SET @sqlSelectByIdentity = @sqlSelectByIdentity + '-- ';
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'GRANT EXEC ON [' + @ProcNameSelectByIdentity + '] TO [' + isnull(@GrantExecTo, 'SOMEBODY') + '] AS DBO;' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + 'GO' + char(10);
	SET @sqlSelectByIdentity = @sqlSelectByIdentity + char(10) + char(13);

	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'GO' + char(10);
	IF @GrantExecTo IS NULL
		SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + '-- ';
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'GRANT EXEC ON [' + @ProcNameSelectByPrimaryKey + '] TO [' + isnull(@GrantExecTo, 'SOMEBODY') + '] AS DBO;' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + 'GO' + char(10);
	SET @sqlSelectByPrimaryKey = @sqlSelectByPrimaryKey + char(10) + char(13);

	SET @DTO = @DTO + char(9) + char(9) + 'public override string ToString()' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + char(9) + 'return JsonConvert.SerializeObject(this, Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);

	IF not @PkColumn is null
	BEGIN
		SET @DTO = @DTO + char(9) + char(9) + 'public override bool Equals(object obj)' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + char(9) + 'return obj is ' + @DtoClass + ' dto && ' + @PkColumn + ' == dto.' + @PkColumn + ';' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);
	END
	ELSE
		IF not @IdentityColumn is null
		BEGIN
			SET @DTO = @DTO + char(9) + char(9) + 'public override bool Equals(object obj)' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + char(9) + 'return obj is ' + @DtoClass + ' dto && ' + @IdentityColumn + ' == dto.' + @IdentityColumn + ';' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);
		END;

	IF charindex('int', @PkType) > 0
	BEGIN
		SET @DTO = @DTO + char(9) + char(9) + 'public override int GetHashCode()' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + char(9) + 'return (int) ' + @PkColumn + ';' + char(10);
		SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);
	END
	ELSE
	BEGIN
		IF charindex('int', @IdentityType) > 0
		BEGIN
			SET @DTO = @DTO + char(9) + char(9) + 'public override int GetHashCode()' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + char(9) + 'return (int) ' + @IdentityColumn + ';' + char(10);
			SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);
		END;
	END;

	SET @DTO = @DTO + char(9) + char(9) + 'public static bool operator ==(' + @DtoClass + ' left, ' + @DtoClass + ' right)' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + char(9) +  'return EqualityComparer<' + @DtoClass + '>.Default.Equals(left, right);' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);

	SET @DTO = @DTO + char(9) + char(9) + 'public static bool operator !=(' + @DtoClass + ' left, ' + @DtoClass + ' right)' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '{' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + char(9) +  'return !(left == right);' + char(10);
	SET @DTO = @DTO + char(9) + char(9) + '}' + char(10);

	SET @DTO = @DTO + char(9) +  '}' + char(10);
	SET @DTO = @DTO + '}' + char(10);
	SET @DTO = @DTO + '*/' + char(10);

	PRINT @DropInsert;
	PRINT @Comments;
	PRINT @sqlInsert;

	PRINT @DropSelectAll;
	PRINT @Comments;
	PRINT @sqlSelectAll;

	IF @IdentityColumn = @PkColumn
	BEGIN
		PRINT @DropSelectByIdentity;
		PRINT @Comments;
		PRINT @sqlSelectByIdentity;
	END
	ELSE
	BEGIN
		IF NOT @PkColumn IS null
		BEGIN
			PRINT @DropSelectByPrimaryKey;
			PRINT @Comments;
			PRINT @sqlSelectByPrimaryKey;			
		END;
	END;
	print @DTO;
END;

Now to run the procedure using our test table:

exec Admin_CreateInsertProcedure 'Stores', 'admin', 'DTO'

This command will execute our stored procedure with the following parameters:

  • To act on the ‘stores’ table we created earlier (referenced above).
  • To GRANT EXEC permission for use by the ADMIN user.
  • To generate a DTO within the DTO namespace.

And voilà, the magic begins as the stored procedure springs into action.

First, for the autogenerated INSERT INTO stored procedure, this is what we will get:

DROP PROCEDURE [Client_StoresInsert];
GO
-- =================================================
-- Template autogenerated by Albert Zakhia
-- =================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:      Albert Zakhia
-- Create date: 2020-10-07 14:26:08.470
-- Description: This procedure allows the insertion of data INTO table [Stores]
-- =============================================
CREATE PROCEDURE [Client_StoresInsert]
     
	@Name varchar(100)
	,@Telephone varchar(50)
	,@Mobile varchar(22)
	,@Username varchar(20)
	,@Password varchar(20)
	,@Active int
	,@Deleted int
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO [Stores]
	(
		 [StoreId]
		,[Name]
		,[Telephone]
		,[Mobile]
		,[Username]
		,[Password]
		,[Active]
		,[Deleted]
	)
	VALUES		
	(
		 @StoreId
		,@Name
		,@Telephone
		,@Mobile
		,@Username
		,@Password
		,@Active
		,@Deleted
	);
		
	SELECT CAST(SCOPE_IDENTITY() AS bigint) AS 'Identity';
END;
GO
GRANT EXEC ON [Client_StoresInsert] TO [admin] AS DBO;
GO

Then, for the clients’ autogenerated SELECT we get:

-- =================================================
-- Template autogenerated by Albert Zakhia
-- =================================================
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:  Albert Zakhia
-- Create date: 2020-10-07 14:26:08.470  
-- Description: This procedure allows the selection of all records FROM table [Stores]
-- =============================================
CREATE PROCEDURE [Client_StoresSelectAll]
AS
BEGIN
	SET NOCOUNT ON;
	SELECT 
		 [StoreId]
		,[Name]
		,[Telephone]
		,[Mobile]
		,[Username]
		,[Password]
		,[Active]
		,[Deleted]
	FROM [Stores];
END;
GO
GRANT EXEC ON [Client_StoresSelectAll] TO [admin] AS DBO;
GO

For the autogenerated SELECT by identity, in our case it is the StoreId, we get:

-- =================================================
 -- Template autogenerated by Albert Zakhia
 -- =================================================
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 -- =============================================
 -- Author:		Albert Zakhia
 -- Create date: 2020-10-07 14:26:08.470
 -- Description:	This procedure allows the selection of 1 record FROM table [Stores] based ON identity
 -- =============================================
 CREATE PROCEDURE [Client_StoresSelectByStoreId]
 	@StoreId bigint
 AS
 BEGIN
 	SET NOCOUNT ON
 	SELECT 
 		 [StoreId]
 		,[Name]
 		,[Telephone]
 		,[Mobile]
 		,[Username]
 		,[Password]
 		,[Active]
 		,[Deleted]
 	FROM [Stores]
 	WHERE  [StoreId] = @StoreId;
 END;
 GO
 GRANT EXEC ON [Client_StoresSelectByStoreId] TO [admin] AS DBO;
 GO

And finally, this is our autogenerated C# listing for the DTO’s data structure. We could of course enhance it in the future:

using System;
using System.Collections.Generic;
using Newtonsoft.Json;
namespace dto
{
	[Serializable]
	public class HitDto
	{
		public int Id { get; set; } 
		public string IpAddress { get; set; } 
		public string Username { get; set; } 
		public string Password { get; set; } 
		public int PortNumber { get; set; } 
		public override string ToString()
		{
			return JsonConvert.SerializeObject(this, Formatting.None, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });
		}
		public override bool Equals(object obj)
		{
			return obj is HitDto dto && Id == dto.Id;
		}
		public override int GetHashCode()
		{
			return (int) Id;
		}
		public static bool operator ==(HitDto left, HitDto right)
		{
			return EqualityComparer<HitDto>.Default.Equals(left, right);
		}
		public static bool operator !=(HitDto left, HitDto right)
		{
			return !(left == right);
		}
	}
}

And here we are, with just one simple stored procedure that could potentially save hours of work. Imagine if we have numerous tables; we can effortlessly loop through them and automatically generate all the necessary stored procedures in one go. Now, how do we list our tables?

SELECT [name] FROM sys.tables where name <> 'sysdiagrams'

To create the automation required, we modify:

SELECT 'exec Admin_CreateInsertProcedure ''' + [name] + ''', ''' + @GrantExecTo + ''',''' + @DtoNamespace + '''' FROM sys.tables where name <> 'sysdiagrams'
 
 Result:
 exec Admin_CreateInsertProcedure 'Protocols', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'Transactions', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'Connections', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'StaticTablesList', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'Registration', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'Stores', 'admin','DTO'
 exec Admin_CreateInsertProcedure 'Printers', 'admin','DTO'

To end with, we can then simplify the multi-table listing and insert the code into a new stored procedure 🙂

CREATE PROCEDURE Admin_CreateStoredProcedures
 	@GrantExecTo varchar(100) = 'admin',
 	@DtoNamespace  varchar(200) = 'DTO'
 AS
 BEGIN
 	SELECT 'exec Admin_CreateInsertProcedure ''' + [name] + ''', ''' + @GrantExecTo + ''',''' + @DtoNamespace + '''' FROM sys.tables where name <> 'sysdiagrams'
 END

A single execute:

exec Admin_CreateStoredProcedures 'admin', 'DTO'

And here we are, fully operational. It did take two days of research, writing, and debugging to craft the code above and this article. However, it’s all worth it as it saves countless hours of reinventing the wheel for many of you. Moreover, this method ensures error-free implementation, eliminating the need for time-consuming debugging and error hunting.

Hope you find it useful!

Thank you for reading,