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
- CodeSmith Generator - commercial software
- MyGeneration - free
- Metadrone - free
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://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)
CRUD with ASP.NET MVC 5
CRUD with ASP.NET WebForms
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
