Auto Generate Code

 // 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)

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

Tags: Auto Generate, Auto Generate Code
Categories: Auto Generate | Auto Generate Code
comment

@manhng

Auto Generate Code

Auto Generate Code (edit)

Auto generate Stored Procedure CRUD tools:

POCO Generator

ASP.NET Core Web API (Auto Generate)

ApexSQL Complete

SSMS Toolpack

SQL Shack

SQL Book

CRUD with ASP.NET Core

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

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

Template Table

Template Stored Procedure

Drop & Create Stored Procedure

Insert

Update

Delete

Script Table As CRUD

CRUD Stored Procedure

Recent posts

@manhng

Auto Generate Code

Auto Generate Code (edit)

Auto generate Stored Procedure CRUD tools:

POCO Generator

ASP.NET Core Web API (Auto Generate)

ApexSQL Complete

SSMS Toolpack

SQL Shack

SQL Book

CRUD with ASP.NET Core

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

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

Template Table

Template Stored Procedure

Drop & Create Stored Procedure

Insert

Update

Delete

Script Table As CRUD

CRUD Stored Procedure

Categories

Recent posts

Popular tags