SQL框架，完全用SQL写的

无详细内容 无 --Start Customer_SetCREATE PROCEDURE [dbo].[Customer_Set]@Name as varchar(50), @Address as varchar(250), @Mobile as varchar(50)ASBEGINSET NOCOUNT ON--ValidationIF @Name IS NULLBEGINRAISERROR ('Name cannot be empty.',16,1)ENDIF

--Start Customer_Set
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
--End Customer_Set


--Start Supplier_Set
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
--End Supplier_Set

--Start GetValidationConstraint
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
--End GetValidationConstraint


--Start ReturnMessage
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
--End ReturnMessage

--Start MessageHelper
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
--End MessageHelper

--Start Customer_Set
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
--End Customer_Set

--Start Customer_Get
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
--End Customer_Get

--Start Customer_DeleteById
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

--End Customer_DeleteById