easyobjectsquickstarts.sql

EasyObjects 是ORM的典型应用的例子是学习研究的很好的范例

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetCategories') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetCategories];
GO

CREATE PROCEDURE [daab_GetCategories]
(
	@CategoryID int
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CategoryID],
		[CategoryName],
		[Description],
		[Picture]
	FROM [Categories]
	WHERE
		([CategoryID] = @CategoryID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetCategories Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetCategories Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllCategories') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllCategories];
GO

CREATE PROCEDURE [daab_GetAllCategories]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CategoryID],
		[CategoryName],
		[Description],
		[Picture]
	FROM [Categories]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllCategories Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllCategories Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateCategories') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateCategories];
GO

CREATE PROCEDURE [daab_UpdateCategories]
(
	@CategoryID int,
	@CategoryName nvarchar(15),
	@Description ntext = NULL,
	@Picture image = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Categories]
	SET
		[CategoryName] = @CategoryName,
		[Description] = @Description,
		[Picture] = @Picture
	WHERE
		[CategoryID] = @CategoryID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateCategories Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateCategories Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddCategories') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddCategories];
GO

CREATE PROCEDURE [daab_AddCategories]
(
	@CategoryID int = NULL OUTPUT,
	@CategoryName nvarchar(15),
	@Description ntext = NULL,
	@Picture image = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [Categories]
	(
		[CategoryName],
		[Description],
		[Picture]
	)
	VALUES
	(
		@CategoryName,
		@Description,
		@Picture
	)

	SET @Err = @@Error
	SELECT @CategoryID = SCOPE_IDENTITY()

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddCategories Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddCategories Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteCategories') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteCategories];
GO

CREATE PROCEDURE [daab_DeleteCategories]
(
	@CategoryID int
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [Categories]
	WHERE
		[CategoryID] = @CategoryID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteCategories Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteCategories Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetCustomerCustomerDemo') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetCustomerCustomerDemo];
GO

CREATE PROCEDURE [daab_GetCustomerCustomerDemo]
(
	@CustomerID nchar(5),
	@CustomerTypeID nchar(10)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[CustomerTypeID]
	FROM [CustomerCustomerDemo]
	WHERE
		([CustomerID] = @CustomerID) AND
		([CustomerTypeID] = @CustomerTypeID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetCustomerCustomerDemo Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetCustomerCustomerDemo Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllCustomerCustomerDemo') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllCustomerCustomerDemo];
GO

CREATE PROCEDURE [daab_GetAllCustomerCustomerDemo]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[CustomerTypeID]
	FROM [CustomerCustomerDemo]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllCustomerCustomerDemo Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllCustomerCustomerDemo Error on Creation'
GO

-------------------------------------------
-- NO UPDATE Stored Procedure Generated    
-- All Columns are part of the Primary key 
-------------------------------------------


IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddCustomerCustomerDemo') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddCustomerCustomerDemo];
GO

CREATE PROCEDURE [daab_AddCustomerCustomerDemo]
(
	@CustomerID nchar(5),
	@CustomerTypeID nchar(10)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [CustomerCustomerDemo]
	(
		[CustomerID],
		[CustomerTypeID]
	)
	VALUES
	(
		@CustomerID,
		@CustomerTypeID
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddCustomerCustomerDemo Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddCustomerCustomerDemo Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteCustomerCustomerDemo') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteCustomerCustomerDemo];
GO

CREATE PROCEDURE [daab_DeleteCustomerCustomerDemo]
(
	@CustomerID nchar(5),
	@CustomerTypeID nchar(10)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [CustomerCustomerDemo]
	WHERE
		[CustomerID] = @CustomerID AND
		[CustomerTypeID] = @CustomerTypeID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteCustomerCustomerDemo Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteCustomerCustomerDemo Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetCustomerDemographics') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetCustomerDemographics];
GO

CREATE PROCEDURE [daab_GetCustomerDemographics]
(
	@CustomerTypeID nchar(10)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerTypeID],
		[CustomerDesc]
	FROM [CustomerDemographics]
	WHERE
		([CustomerTypeID] = @CustomerTypeID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetCustomerDemographics Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetCustomerDemographics Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllCustomerDemographics') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllCustomerDemographics];
GO

CREATE PROCEDURE [daab_GetAllCustomerDemographics]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerTypeID],
		[CustomerDesc]
	FROM [CustomerDemographics]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllCustomerDemographics Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllCustomerDemographics Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateCustomerDemographics') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateCustomerDemographics];
GO

CREATE PROCEDURE [daab_UpdateCustomerDemographics]
(
	@CustomerTypeID nchar(10),
	@CustomerDesc ntext = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [CustomerDemographics]
	SET
		[CustomerDesc] = @CustomerDesc
	WHERE
		[CustomerTypeID] = @CustomerTypeID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateCustomerDemographics Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateCustomerDemographics Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddCustomerDemographics') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddCustomerDemographics];
GO

CREATE PROCEDURE [daab_AddCustomerDemographics]
(
	@CustomerTypeID nchar(10),
	@CustomerDesc ntext = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	INSERT
	INTO [CustomerDemographics]
	(
		[CustomerTypeID],
		[CustomerDesc]
	)
	VALUES
	(
		@CustomerTypeID,
		@CustomerDesc
	)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_AddCustomerDemographics Succeeded'
ELSE PRINT 'Procedure Creation: daab_AddCustomerDemographics Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_DeleteCustomerDemographics') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_DeleteCustomerDemographics];
GO

CREATE PROCEDURE [daab_DeleteCustomerDemographics]
(
	@CustomerTypeID nchar(10)
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	DELETE
	FROM [CustomerDemographics]
	WHERE
		[CustomerTypeID] = @CustomerTypeID
	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_DeleteCustomerDemographics Succeeded'
ELSE PRINT 'Procedure Creation: daab_DeleteCustomerDemographics Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetCustomers') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetCustomers];
GO

CREATE PROCEDURE [daab_GetCustomers]
(
	@CustomerID nchar(5)
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[CompanyName],
		[ContactName],
		[ContactTitle],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[Phone],
		[Fax]
	FROM [Customers]
	WHERE
		([CustomerID] = @CustomerID)

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetCustomers Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetCustomers Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_GetAllCustomers') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_GetAllCustomers];
GO

CREATE PROCEDURE [daab_GetAllCustomers]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	SELECT
		[CustomerID],
		[CompanyName],
		[ContactName],
		[ContactTitle],
		[Address],
		[City],
		[Region],
		[PostalCode],
		[Country],
		[Phone],
		[Fax]
	FROM [Customers]

	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_GetAllCustomers Succeeded'
ELSE PRINT 'Procedure Creation: daab_GetAllCustomers Error on Creation'
GO

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_UpdateCustomers') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_UpdateCustomers];
GO

CREATE PROCEDURE [daab_UpdateCustomers]
(
	@CustomerID nchar(5),
	@CompanyName nvarchar(40),
	@ContactName nvarchar(30) = NULL,
	@ContactTitle nvarchar(30) = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@Phone nvarchar(24) = NULL,
	@Fax nvarchar(24) = NULL
)
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @Err int

	UPDATE [Customers]
	SET
		[CompanyName] = @CompanyName,
		[ContactName] = @ContactName,
		[ContactTitle] = @ContactTitle,
		[Address] = @Address,
		[City] = @City,
		[Region] = @Region,
		[PostalCode] = @PostalCode,
		[Country] = @Country,
		[Phone] = @Phone,
		[Fax] = @Fax
	WHERE
		[CustomerID] = @CustomerID


	SET @Err = @@Error

	RETURN @Err
END
GO


-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: daab_UpdateCustomers Succeeded'
ELSE PRINT 'Procedure Creation: daab_UpdateCustomers Error on Creation'
GO




IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('daab_AddCustomers') AND sysstat & 0xf = 4)
    DROP PROCEDURE [daab_AddCustomers];
GO

CREATE PROCEDURE [daab_AddCustomers]
(
	@CustomerID nchar(5),
	@CompanyName nvarchar(40),
	@ContactName nvarchar(30) = NULL,
	@ContactTitle nvarchar(30) = NULL,
	@Address nvarchar(60) = NULL,
	@City nvarchar(15) = NULL,
	@Region nvarchar(15) = NULL,
	@PostalCode nvarchar(10) = NULL,
	@Country nvarchar(15) = NULL,
	@Phone nvarchar(24) = NULL,
	@Fax nvarchar(24) = NULL