@manhnguyenv

Welcome to my blog!

Auto Generate Code

May 17, 2020 08:59

Auto Generate Code (edit)

Step 1/ Design Table (SQL Server)

Step 2/ Generate CRUD on Table using Stored Procedure

Step 3/ Design to use the Connection String from Configuration Block (singleton pattern)

Step 4/ Use the POCO Generator to generate the Dapper's code

Step 5/ Dapper code to call Stored Procedure

Auto generate Stored Procedure CRUD tools:

  • ApexSQL Complete
  • SSMS Toolpack
  • SQL Complete

General use

SQL Server specific

https://www.codeproject.com/Articles/19280/Stored-Procedure-Generator

https://www.codeproject.com/Tips/992200/Create-CRUD-Stored-Procedures-Without-Writing-One

https://archive.codeplex.com/?p=spgen

POCO Generator

https://github.com/matzheng/POCOGenerator (HAY HAY HAY)

https://www.codeproject.com/Articles/892233/POCO-Generator (HAY HAY HAY)

https://github.com/shps951023/PocoClassGenerator

https://www.codeproject.com/Articles/4025476/PocoClassGenerator-RDBMS-All-Table-View-Generate-D

ASP.NET Core Web API (Auto Generate)

Generator Entity Framework Core source code models

http://www.loresoft.com/Generate-ASP-NET-Web-API

https://github.com/loresoft/EntityFrameworkCore.Generator (HAY HAY HAY)

ApexSQL Complete

https://blog.apexsql.com/whats-next-in-apexsql-complete-2017-r6-automatic-crud-stored-procedure-generation/

https://www.apexsql.com/sql-tools-complete.aspx

SSMS Toolpack

https://www.ssmstoolspack.com/

SQL Shack

https://www.sqlshack.com/creating-using-crud-stored-procedures/

SQL Book

https://www.sqlbook.com/sql-server/sql-script-to-automatically-generate-crud-stored-procedures/

CRUD with ASP.NET Core

https://dzone.com/articles/crud-operation-with-aspnet-core-mvc-using-adonet-a

CRUD Operation Using ASP.Net MVC 5 (HAY HAY HAY)

http://shashangka.com/2015/10/05/crud-operation-using-asp-net-mvc/

CRUD from an Excel file with System.Data.OleDb (HAY HAY HAY)

https://www.crazygeeksblog.com/2019/02/import-data-from-excel-to-sql-server-using-entity-framework-asp-net-mvc/

CRUD with ASP.NET MVC 5

https://www.compilemode.com/2016/05/crud-operation-in-Asp-Net-mvc-using-ado-net-with-stored-procedure.html

CRUD with ASP.NET WebForms

https://www.aspsnippets.com/Articles/Select-Insert-Edit-Update-and-Delete-CRUD-using-Single-Stored-Procedure-in-ASPNet-MVC.aspx

CRUD Generation from System Views

https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-crud-generation-from-system-views/

Template Dapper

 // GET
using (var conn = new SqlConnection(ConfigurationBlock.ConnectionString))
{
var list = conn.Query<LicenseDto>("GetLicense", commandTimeout: ConfigurationBlock.SQLCommandTimeout(), commandType: CommandType.StoredProcedure).ToList();
return list.Count;
}

// INSERT
 p.Add("@Id", dbType: DbType.Int64, direction: ParameterDirection.Output);

conn.Execute("InsertLicense", p, commandTimeout: ConfigurationBlock.SQLCommandTimeout(), commandType: CommandType.StoredProcedure, transaction: transaction);

returnResult = p.Get<long>("@Id");

if (returnResult <= 0)
{
...
}

// UPDATE
p.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

conn.Execute("UpdateLicense", p, commandTimeout: ConfigurationBlock.SQLCommandTimeout(), commandType: CommandType.StoredProcedure, transaction: transaction
int iResult = p.Get<int>("@Result");

if (iResult <= 0)
{
...
}

// DELETE
p.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

conn.Execute("DeleteLicense", p, commandTimeout: ConfigurationBlock.SQLCommandTimeout(), commandType: CommandType.StoredProcedure, transaction: transaction);

int iDReturn = p.Get<int>("@Result");

if (iDReturn < 0)
{
...
}

Template Table

CREATE TABLE [dbo].[License](
...
[IsActive] [bit] NULL,
[IsDeleted] [bit] NULL,
[Created] [datetime] NULL,
[CreatedBy] [int] NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [int] NULL),
...

Template Stored Procedure

Drop & Create Stored Procedure

GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLicense]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetLicense]
@Id int
AS
BEGIN
END

Insert

 @Id int OUT,

...

DECLARE @Err int
DECLARE @Result int

SET @Result = @@ROWCOUNT
SET @Err = @@ERROR

If @Err <> 0
BEGIN
SET @Id = -1
RETURN @Err
END
ELSE
BEGIN
If @Result = 1
BEGIN
-- Everything is OK
SET @Id = @@IDENTITY
END
ELSE
BEGIN
SET @Id = -1
RETURN 0
END
END

RETURN @Id

Update

 DECLARE @Err int
DECLARE @Result int
SET @Result = @@ROWCOUNT
SET @Err = @@ERROR

If @Err <> 0
BEGIN
SET @Result = -1
END

RETURN @Result

Delete

UPDATE [License]
SET
IsDeleted = 1
WHERE ( ID = @ID )

DECLARE @Err int
DECLARE @Result int

SET @Result = @@ROWCOUNT
SET @Err = @@ERROR

If @Err <> 0
BEGIN
SET @Result = -1
END

RETURN @Result

Script Table As CRUD

https://gist.github.com/atshane253/2f95a591bb77aa21ab2693c3fab2af60 (HAY)

https://blog.devart.com/how-to-generate-and-use-crud-stored-procedures.html (HAY)

https://blog.devart.com/how-to-generate-and-use-crud-stored-procedures.html (HAY)

CRUD Stored Procedure

-- sources: http://dba.stackexchange.com/questions/97188/how-to-output-more-than-4000-characters-in-sqlcmd/97350#97350
-- link: www.sqlbook.com/scripts/AutoGenerateCRUDStoredProcs.txt
-- Do we want to generate the SP definitions for every user defined
-- table in the database or just a single specified table?
-- Assign a blank string - '' for all tables or the table name for
-- a single table.
DECLARE @GenerateProcsFor VARCHAR(128) = 'Orders' -- Change to your table

-- which database do we want to create the procs for?
-- Change both the USE and SET lines below to set the database name
-- to the required database.
USE [Northwind] -- Change to your database
DECLARE @DatabaseName VARCHAR(128) = '[Northwind]'

-- Is there a table name prefix i.e. 'tbl_' which we don't want
-- to include in our stored proc names?
DECLARE @TablePrefix VARCHAR(10) = ''

-- do SELECT * (1) or SELECT [ColumnName,...] (0)
DECLARE @UseSelectWildCard BIT = 0

-- prefix sprocs if you want
DECLARE @SprocPrefix VARCHAR(10) = ''

-- table names followed by operation (1) or GetTable (0)
DECLARE @TableNameFirst BIT = 0

-- ##########################################################
-- END CONFIG
-- ##########################################################
SET NOCOUNT ON
DECLARE TableCol CURSOR FOR
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN
INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

DECLARE @TableSchema VARCHAR(128), @TableName VARCHAR(128), @ColumnName VARCHAR(128), @DataType VARCHAR(30), @CharLength INT, @CharLengthText VARCHAR(10)
DECLARE @ColumnNameCleaned VARCHAR(128), @CurrentTable VARCHAR(128) = '', @FirstColumnName VARCHAR(128), @FirstColumnNameCleaned VARCHAR(128), @FirstColumnIdentity BIT
DECLARE @ObjectName VARCHAR(117), @TablePrefixLength INT = LEN(@TablePrefix)
DECLARE @LIST NVARCHAR(MAX), @ARGS NVARCHAR(MAX), @SELECT NVARCHAR(MAX), @INSERT NVARCHAR(MAX), @VALUES VARCHAR(MAX), @UPDATE NVARCHAR(MAX), @DELETE NVARCHAR(MAX)
DECLARE @CREATELIST NVARCHAR(128), @CREATESELECT NVARCHAR(128), @CREATEINSERT NVARCHAR(128), @CREATEUPDATE NVARCHAR(128), @CREATEDELETE NVARCHAR(128)
DECLARE @CREATE VARCHAR(29) = 'CREATE PROC [dbo].[' + @SprocPrefix
DECLARE @AS VARCHAR(20) = 'AS' + CHAR(10) + 'SET NOCOUNT ON' + CHAR(10) + CHAR(10)
DECLARE @GO VARCHAR(4) = CHAR(10) + 'GO' + CHAR(10)

DECLARE @TextToDisplay NVARCHAR(MAX),
@Buffer NVARCHAR(4000),
@BufferLength INT,
@StartIndex INT = 1,
@NextNewlineIndex INT;

OPEN TableCol
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
WHILE @@FETCH_STATUS = 0 BEGIN
SET @ColumnNameCleaned = REPLACE(@ColumnName, ' ', '')
IF @CharLength = -1
SET @CharLengthText = 'MAX'
ELSE
SET @CharLengthText = CAST(@CharLength AS VARCHAR(10))

IF @TableName <> @CurrentTable BEGIN
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
SET @LIST = @LIST + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)

SET @SELECT = @SELECT + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)

SET @ARGS = @ARGS + CHAR(10)

SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
SET @VALUES = SUBSTRING(@VALUES, 0, LEN(@VALUES) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
SET @INSERT = @INSERT + @VALUES
IF @FirstColumnIdentity = 1
SET @INSERT = @INSERT + 'RETURN SCOPE_IDENTITY()' + CHAR(10)
SET @INSERT = @CREATEINSERT + @ARGS + @AS + @INSERT

SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE) - 1) + CHAR(10) + CHAR(9) + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)
SET @UPDATE = @CREATEUPDATE + @ARGS + @AS + @UPDATE

SET @TextToDisplay = @LIST + @GO + @SELECT + @GO + @INSERT + @GO + @UPDATE + @GO + @DELETE + @GO
WHILE (1 = 1)
BEGIN
SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
SET @BufferLength = (DATALENGTH(@Buffer) / 2);

IF (@BufferLength < 4000)
BEGIN
BREAK;
END;

SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);

IF (@NextNewlineIndex > 0)
BEGIN
PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
SET @StartIndex += @NextNewlineIndex;
END;
ELSE
BEGIN
PRINT @Buffer;
SET @StartIndex += @BufferLength;
END;
END;

IF (DATALENGTH(@Buffer) > 0)
BEGIN
PRINT @Buffer;
END;
END
END

SET @CurrentTable = @TableName
SET @FirstColumnName = @ColumnName
SET @FirstColumnNameCleaned = REPLACE(@FirstColumnName, ' ', '')
SET @FirstColumnIdentity = COLUMNPROPERTY(OBJECT_ID(@TableSchema + '.' + @TableName), @FirstColumnName, 'IsIdentity')

IF @TablePrefixLength > 0
IF SUBSTRING(@CurrentTable, 1, @TablePrefixLength) = @TablePrefix
SET @ObjectName = RIGHT(@CurrentTable, LEN(@CurrentTable) - @TablePrefixLength)
ELSE
SET @ObjectName = @CurrentTable
ELSE
SET @ObjectName = @CurrentTable

IF @TableNameFirst = 1 BEGIN
SET @CREATELIST = @CREATE + @ObjectName + '_Select_All]'
SET @CREATESELECT = @CREATE + @ObjectName + '_Select]'
SET @CREATEINSERT = @CREATE + @ObjectName + '_Insert]'
SET @CREATEUPDATE = @CREATE + @ObjectName + '_Update]'
SET @CREATEDELETE = @CREATE + @ObjectName + '_Delete]'
END ELSE BEGIN
SET @CREATELIST = @CREATE + 'Get' + @ObjectName + 's]'
SET @CREATESELECT = @CREATE + 'Get' + @ObjectName + ']'
SET @CREATEINSERT = @CREATE + 'Add' + @ObjectName + ']'
SET @CREATEUPDATE = @CREATE + 'Update' + @ObjectName + ']'
SET @CREATEDELETE = @CREATE + 'Delete' + @ObjectName + ']'
END

IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
SET @LIST = @CREATELIST + CHAR(10) + @AS
IF @UseSelectWildcard = 1
SET @LIST = @LIST + 'SELECT * '
ELSE
SET @LIST = @LIST + 'SELECT [' + @ColumnName + ']'

SET @SELECT = @CREATESELECT + CHAR(10)
SET @SELECT = @SELECT + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
SET @SELECT = @SELECT + '(' + @CharLengthText + ')'
SET @SELECT = @SELECT + CHAR(10) + @AS
IF @UseSelectWildcard = 1
SET @SELECT = @SELECT + 'SELECT * '
ELSE
SET @SELECT = @SELECT + 'SELECT [' + @ColumnName + ']'

SET @ARGS = CHAR(10) + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
SET @ARGS = @ARGS + '(' + @CharLengthText + ')'

SET @INSERT = 'INSERT INTO [' + @TableName + '] (' + CHAR(10)
SET @VALUES = CHAR(9) + 'VALUES (' + CHAR(10)
IF @FirstColumnIdentity = 0 BEGIN
SET @INSERT = @INSERT + CHAR(9) + CHAR(9) + '[' + @ColumnName + '],' + CHAR(10)
SET @VALUES = @VALUES + CHAR(9) + CHAR(9) + '@' + @ColumnNameCleaned + ',' + CHAR(10)
END

SET @UPDATE = 'UPDATE [' + @TableName + '] SET ' + CHAR(10)

SET @DELETE = @CREATEDELETE + CHAR(10)
SET @DELETE = @DELETE + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR')
SET @DELETE = @DELETE + '(' + @CharLengthText + ')'
SET @DELETE = @DELETE + CHAR(10) + @AS
SET @DELETE = @DELETE + 'DELETE FROM [' + @TableName + ']' + CHAR(10)
SET @DELETE = @DELETE + 'WHERE [' + @ColumnName + '] = @' + @ColumnNameCleaned + CHAR(10)
END
END
ELSE BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
IF @UseSelectWildCard = 0 BEGIN
SET @LIST = @LIST + ', ' + CHAR(10) + CHAR(9) + '[' + @ColumnName + ']'
SET @SELECT = @SELECT + ', ' + CHAR(10) + CHAR(9) + '[' + @ColumnName + ']'
END

SET @ARGS = @ARGS + ',' + CHAR(10) + CHAR(9) + '@' + @ColumnNameCleaned + ' ' + @DataType
IF @DataType IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR', 'VARBINARY', 'BINARY')
SET @ARGS = @ARGS + '(' + @CharLengthText + ')'

SET @INSERT = @INSERT + CHAR(9) + CHAR(9) + '[' + @ColumnName + '],' + CHAR(10)
SET @VALUES = @VALUES + CHAR(9) + CHAR(9) + '@' + @ColumnNameCleaned + ',' + CHAR(10)

SET @UPDATE = @UPDATE + CHAR(9) + CHAR(9) + '[' + @ColumnName + '] = @' + @ColumnNameCleaned + ',' + CHAR(10)
END
END
FETCH NEXT FROM TableCol INTO @TableSchema, @TableName, @ColumnName, @DataType, @CharLength
END
CLOSE TableCol
DEALLOCATE TableCol
IF @CurrentTable <> '' BEGIN
IF @GenerateProcsFor = '' OR @GenerateProcsFor = @CurrentTable BEGIN
SET @LIST = @LIST + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)

SET @SELECT = @SELECT + CHAR(10) + 'FROM [' + @CurrentTable + ']' + CHAR(10)
SET @SELECT = @SELECT + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)

SET @ARGS = @ARGS + CHAR(10)

SET @INSERT = SUBSTRING(@INSERT, 0, LEN(@INSERT) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
SET @VALUES = SUBSTRING(@VALUES, 0, LEN(@VALUES) - 1) + CHAR(10) + CHAR(9) + ')' + CHAR(10)
SET @INSERT = @INSERT + @VALUES
IF @FirstColumnIdentity = 1
SET @INSERT = @INSERT + 'RETURN SCOPE_IDENTITY()' + CHAR(10)
SET @INSERT = @CREATEINSERT + @ARGS + @AS + @INSERT

SET @UPDATE = SUBSTRING(@UPDATE, 0, LEN(@UPDATE) - 1) + CHAR(10) + CHAR(9) + 'WHERE [' + @FirstColumnName + '] = @' + @FirstColumnNameCleaned + CHAR(10)
SET @UPDATE = @CREATEUPDATE + @ARGS + @AS + @UPDATE

SET @TextToDisplay = @LIST + @GO + @SELECT + @GO + @INSERT + @GO + @UPDATE + @GO + @DELETE + @GO
WHILE (1 = 1)
BEGIN
SET @Buffer = SUBSTRING(@TextToDisplay, @StartIndex, 4000);
SET @BufferLength = (DATALENGTH(@Buffer) / 2);

IF (@BufferLength < 4000)
BEGIN
BREAK;
END;

SET @NextNewlineIndex = CHARINDEX(NCHAR(10), @Buffer COLLATE Latin1_General_100_BIN2);

IF (@NextNewlineIndex > 0)
BEGIN
PRINT SUBSTRING(@Buffer, 1, (@NextNewlineIndex - 1));
SET @StartIndex += @NextNewlineIndex;
END;
ELSE
BEGIN
PRINT @Buffer;
SET @StartIndex += @BufferLength;
END;
END;

IF (DATALENGTH(@Buffer) > 0)
BEGIN
PRINT @Buffer;
END;
END
END

Categories

Recent posts