SQL Framework
Introduction
Developers who have experienced in large application development know the necessities of framework. In this regards, more experience developer like Team Lead, Technical Lead and Project Manager up to CTO have high respect of it. They know from their experience that large application should develop in very organized way so that newly assigned developers take tiny time to adopt the development structure behind the application and take less costly man hour form Team Lead or other senior developers. They also know that maintenance will be easy and cost effective after development has been completed for the application.
Background
Apart from this I will be going through developing a SQL Framework. I will show the necessity of it with demonstration.
Let’s Get Started
- Open SQL Management Studio
I want to start by designing a table structure in SQL Management Studio like bellow:
Figure 1:
Say you are working in a large software company for a big project and will take long time. You have many programmers working under you in this project. Sometimes programmers are switching from your software company and replace by newly join programmers. So far this is a big project so underlining database size will be accordingly. Say, you have assigned a task to a programmer to create a store procedure for data insertion with proper validation and other necessary command in store procedure.
The bellow procedure has been created by the programmer as his assigned task.
CREATE PROCEDURE [dbo].[Customer_Set]
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50)
AS
BEGIN
SET NOCOUNT ON
--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Name cannot be empty.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Name cannot be less than 3 characters.',16,1)
END
--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (0)
END CATCH
RETURN (1)
END
You also have assigned another developer to write store procedure for supplier table.
Figure 2:
The write store procedure like bellow:
CREATE PROCEDURE [dbo].[Supplier_Set]
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50)
AS
BEGIN
SET NOCOUNT ON
--Validation
IF @Name IS NULL
BEGIN
RAISERROR ('Please enter suppiler name.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
END
--Data Insertion
BEGIN TRY
INSERT INTO [dbo].[Supplier]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
END TRY
BEGIN CATCH
RETURN (-1)
END CATCH
RETURN (1)
END
While reviewing code for above two store procedures written by two programmers you will fine following difference.
IF @Name IS NULL
BEGIN
RAISERROR ('Name cannot be empty.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Name cannot be less than 3 characters.',16,1)
END
BEGIN CATCH
RETURN (0)
END CATCH
RETURN (1)
another programmer
IF @Name IS NULL
BEGIN
RAISERROR ('Please enter suppiler name.',16,1)
END
IF LEN(@Name)<3
BEGIN
RAISERROR ('Supplier name cannot be less than 3 characters.',16,1)
END
BEGIN CATCH
RETURN (-1)
END CATCH
RETURN (1)
so one programmer write
- Name cannot be empty.
- Name cannot be less than 3 characters.
- RETURN (0)
another programmer
- Please enter suppiler name.
- Supplier name cannot be less than 3 characters
- RETURN (-1)
For same kind of work they have not used similar messages and they have also return 0 and -1 which are not understandable. And others developers may write something other than this. So this system is not generic system or not followed by same structure.
It will be very tough to manage if you have big amount of store procedures like above written by many developers.
System should develop followed by a structure that I called framework and all messages should common, return type should be readable and other things regarding a structure should common. That will help us to manage system easily and using less effort.
We can build a framework by write some additional store procedures in this case and you can increase as your require to build your own.
Lets’ create a store procedure to set some constant variables most likely as Enum in .NET. These constants will actually use in other regular procedure for different type of operation. These constants are for common system development.
CRAETE PROCEDURE [dbo].[GetValidationConstraint]
--Output values
@EmptyCheck int OUTPUT,
@LenCheck int =NULL OUTPUT,
@NoDataExist int =NULL OUTPUT,
@True bit =NULL OUTPUT,
@False bit =NULL OUTPUT
AS
BEGIN
SELECT @EmptyCheck=1
SELECT @LenCheck =2
SELECT @NoDataExist =3
SELECT @True=1
SELECT @False=0
END
Another procedure is for common return message for whole application.
CREATE PROCEDURE [dbo].[ReturnMessage]
--Success, Fail is the order of output parameter
@Success int OUTPUT,
@Fail int OUTPUT
AS
SET NOCOUNT ON
BEGIN
SELECT @Fail=0
SELECT @Success=1
END
Bellow procedure is for common messages for the application.
CREATE PROCEDURE [dbo].[MessageHelper]
--Input values
@Field varchar(200) =NULL,
@MinLenght int =NULL,
@ValidationConstraint int,
--Output values
@ValidationMessage varchar(200) OUTPUT
AS
BEGIN
--Variables
DECLARE @EMPTY_MESSAGE varchar(50),
@MINIMUM_LENGHT_MESSAGE varchar(50),
@NO_DATA_EXIST_MESSAGE varchar(50)
DECLARE @EMPTY int,
@LEN int,
@NO_DATA_EXIST int
DECLARE @SUCCESSED int,
@FAILED int
--Message Constraint
SET @EMPTY_MESSAGE = 'cannot be empty.'
SET @MINIMUM_LENGHT_MESSAGE ='cannot be less than'
SET @NO_DATA_EXIST_MESSAGE = 'No record found.'
--Get global values
EXEC ReturnMessage @SUCCESSED output, @FAILED output
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT, @NO_DATA_EXIST OUTPUT
--Set message
IF @ValidationConstraint = @EMPTY
BEGIN
IF LEN(@Field)<=0
BEGIN
RAISERROR('Field name cannot be empty. StoreProcedure/MessageHelper',16,1)
RETURN @FAILED
END
SELECT @ValidationMessage = @Field + ' ' + @EMPTY_MESSAGE
END
IF @ValidationConstraint = @LEN
BEGIN
IF @MinLenght IS NULL OR @MinLenght <=0
BEGIN
RAISERROR('Minimum length cannot be empty. StoreProcedure/MessageHelper',16,1)
RETURN @FAILED
END
ELSE
BEGIN
SELECT @ValidationMessage = @Field + ' ' + @MINIMUM_LENGHT_MESSAGE + ' ' + CONVERT(varchar, @MinLenght)
END
END
IF @ValidationConstraint = @NO_DATA_EXIST
BEGIN
SELECT @ValidationMessage = @NO_DATA_EXIST_MESSAGE
END
END
Write your procedure for insert data into Customer table
CREATE PROCEDURE [dbo].[Customer_Set]
--Input values
@Name as varchar(50),
@Address as varchar(250),
@Mobile as varchar(50),
--Output values
@LASTID bigint OUTPUT,
@MESSAGE varchar(200) =NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN
--Constraint Variables For Readable Return Value
DECLARE @SUCCESSED int,
@FAILED int
--Constraint Variables For Readable Validation Operation
DECLARE @EMPTY int,
@LEN int
BEGIN TRY
--Get constraint value for successed and failed
EXEC ReturnMessage @SUCCESSED output, @FAILED output
--Get constraint value for validation. @EMPTY is for empty check and @LEN is for length check common messaging system.
EXEC GetValidationConstraint @EMPTY output, @LEN output
--Validation
IF LEN(@Name)=0
BEGIN
EXEC MessageHelper 'Name', @EMPTY,@MESSAGE OUTPUT --It will generate a common empty message.
RETURN @FAILED-- Readable Failed Return
END
IF LEN(@Name)<3
BEGIN
EXEC MessageHelper 'Name',3, @LEN,@MESSAGE OUTPUT --It will generate a common length check message.
RETURN @FAILED-- Readable Failed Return
END
--Data insertion
INSERT INTO [dbo].[Customer]
([Name]
,[Address]
,[Mobile])
VALUES
(@Name
,@Address
,@Mobile)
SELECT @LASTID=SCOPE_IDENTITY()
END TRY
BEGIN CATCH -- Error Traping Section
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED -- Readable Failed Return
END CATCH
RETURN @SUCCESSED -- Readable Successed Return
END
In the above way all message in a system will remain common.
Now write store procedure for get all records from Customer table
CREATE PROCEDURE [dbo].[Customer_Get]
--Output values
@TOTAL_ROWS bigint OUTPUT,
@MESSAGE varchar(200) =NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON
--Variables
DECLARE @SUCCESSED int,
@FAILED int
DECLARE @EMPTY int,
@LEN int,
@NO_DATA_EXIST int
BEGIN TRY
--Get constraint value
EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT,@NO_DATA_EXIST OUTPUT
--Validation
IF (SELECT COUNT(CustomerId) FROM Customer )<= 0
BEGIN
EXEC MessageHelper '', @NO_DATA_EXIST,@MESSAGE OUTPUT --It will generate common no data exist message.
SELECT @TOTAL_ROWS=0
RETURN @SUCCESSED
END
--Data retrival
SELECT [CustomerId]
,[Name]
,[Address]
,[Mobile]
FROM [dbo].[Customer]
--Get total rows
SELECT @TOTAL_ROWS=@@ROWCOUNT
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED
END CATCH
RETURN @SUCCESSED
END
Write procedure for delete record from Customer table
CREATE PROCEDURE [dbo].[Customer_DeleteById]
--Input values
@CustomerId bigint,
@MESSAGE varchar(200) =NULL OUTPUT
AS
BEGIN
--Variables
DECLARE @SUCCESSED int,
@FAILED int
DECLARE @EMPTY int,
@LEN int
BEGIN TRY
--Get constraint value
EXEC ReturnMessage @SUCCESSED OUTPUT, @FAILED OUTPUT
EXEC GetValidationConstraint @EMPTY OUTPUT, @LEN OUTPUT
--Validation
IF @@CustomerId <=0
BEGIN
EXEC MessageHelper 'Customer Id', @EMPTY,@MESSAGE OUTPUT
RETURN @FAILED
END
--Data deletion
DELETE FROM [dbo].[Customer]
WHERE (CustomerId = @CustomerId)
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(4000);
DECLARE @ErrorSeverity int;
DECLARE @ErrorState int;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState);
RETURN @FAILED
END CATCH
RETURN @SUCCESSED
END
So by some additional store procedure we can build a common messaging and validation system for web or software application. By building common system or framework we can reduce maintenance time for our software project. And it is easy to understand whole system.