Part 5 - Creating Stored Procedures using SQL Server Management Objects (SMO)
In this installation of my blog series I'll cover defining, creating and persisting SQL scripts for stored procedures based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO). As I stated at the end of my last post, this takes an entirely different approach than table generation. Tables are created and then the script is generated from the execution, stored procedures, on the other hand, require us to create the sql script and then execute it against the server.
The code generator will iterate the CodeGenratorPropertyList to generate the four basic CRUD stored procedures (Create, Retrieve, Update and Delete) as well as a generic Exists stored procedure and a generic SelectList stored procedure that returns all of the records and finally Foreign key based stored procedures. That is, if a field is designated as a foreign key in a table then we will create a stored procedure that retrieves a list based on that Foreign key. So, for example, the UserAddress table might have the UserId as a foreign key, in this case we would like to generate a SelectList_ByUserId stored procedure to use later on in our classes.
All of the stored procedure code will be generated using a standard layout and indenting to make it readable. This includes the following:
- a standard DROP clause with a successful ‘dropped' print message
USE [NAMESPACE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*
* Drop Stored procedure if it exists
*/
IF EXISTS
(
SELECT
*
FROM
sysobjects
WHERE
id = object_id(N'[NAMESPACE_User_Select_ByUserId]')
AND
OBJECTPROPERTY(id, N'IsProcedure') = 1
)
BEGIN
DROP PROCEDURE [NAMESPACE_User_Select_ByUserId]
IF @@ERROR = 0
BEGIN
PRINT '<<NAMESPACE_User_Select_ByUserId stored procedure was dropped successfully>>'
END
END
GO
/*
************************************************************************************************************
*
* Name: NAMESPACE_User_Select_ByUserId
*
* Sample Call:
NAMESPACE_User_Select_ByUserId '10228341-4204-4ddd-9f83-592e4c35cf2b'
*
* ----------------------------------------------------------------------------------------------------------
*
* This Procedure Called by .NET class methods:
* NAMESPACE.User.Fetch()
*
* ----------------------------------------------------------------------------------------------------------
*
* Modification History:
*
* Date Developer Description
* ----------------------------------------------------------------------------------------------------------
* 2/12/2008 John P. Frampton Created
*
************************************************************************************************************
*/
- the body of the procedure with basic error handling
CREATE PROCEDURE
NAMESPACE_User_Select_ByUserId
(
@uidUserId uniqueidentifier
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
SELECT
[UserId]
,[ParentUserId]
,[FirstName]
,[LastName]
,[PersonalUrl]
FROM
[dbo].[NAMESPACE_User]
WITH
(NOLOCK)
WHERE
[UserId] = @uidUserId
/*
* Check for errors and send return value
*/
SELECT @v_intError = @@ERROR
IF @v_intError <> 0
BEGIN
-- DEBUG
--PRINT 'Returned -1 -- Undefined Error'
RETURN -1
END
ELSE
BEGIN
-- DEBUG
--PRINT 'Returned 0 -- Success'
RETURN 0
END
END
GO
- a successful ‘generated' print message.
IF @@ERROR = 0
BEGIN
PRINT '<<NAMESPACE_User_Select_ByUserId stored procedure was created successfully>>'
END
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
GRANT EXECUTE ON [NAMESPACE_User_Select_ByUserId] TO [public]
GO
The generic select list is the easiest stored procedure to generate as it is a simple select with no parameters. The body of that stored procedure would look like this:
CREATE PROCEDURE
NAMESPACE_User_SelectList
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
SELECT
[UserId]
,[UserId]
,[FirstName]
,[LastName]
,[PersonalUrl]
FROM
[dbo].[NAMESPACE_User]
WITH
(NOLOCK)
The Exists, Select and Delete stored procedures are also fairly straightforward as they each are passed the primary key as the lone parameter. The Select statement is a detailed list of all the desired fields (as opposed to the ever-dangerous * method for returning everything). The SELECT stored procedure will look like this:
CREATE PROCEDURE
NAMESPACE_User_Select
(
@intUserId int
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
SELECT
[UserId]
,[ParentUserId]
,[FirstName]
,[LastName]
,[PersonalUrl]
FROM
[dbo].[NAMESPACE_User]
WITH
(NOLOCK)
WHERE
[UserId] = @intUserId
The Exists statement returns the count of records matching the passed in id (0 = false - does not exist, >=1 = true - does exist). It will look like this:
CREATE PROCEDURE
NAMESPACE_User_Exists
(
@intUserId int
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
SELECT
COUNT(*) AS 'ExistsFlag'
FROM
[dbo].[NAMESPACE_User]
WITH
(NOLOCK)
WHERE
[UserId] = @intUserId
The Delete method deletes a record based on the Primary key parameter and it looks like this:
CREATE PROCEDURE
NAMESPACE_User_Delete
(
@intUserId int
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
DELETE
[dbo].[NAMESPACE_User]
WHERE
[UserId] = @intUserId
The insert stored procedure takes in every field in the table as a parameter, including the Primary key as an OUTPUT parameter that will be returned to the caller after it has been populated. Each field that can be null will also have its default value set to null. It will look like this:
CREATE PROCEDURE
NAMESPACE_User_Insert
(
@intUserId int OUTPUT
,@uidParentUserId uniqueidentifier
,@strFirstName nvarchar(50) = NULL
,@strLastName nvarchar(256) = NULL
,@strPersonalUrl nvarchar(256) = NULL
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
INSERT INTO [dbo].[NAMESPACE_User]
(
[ParentUserId]
,[FirstName]
,[LastName]
,[PersonalUrl]
)
VALUES
(
@uidParentUserId
,@strFirstName
,@strLastName
,@strPersonalUrl
)
SET @intUserId = SCOPE_IDENTITY()
The Update stored procedure will also take in all fields in the table in a likewise manner, except that the Primary key will not be an OUTPUT variable as it can't be changed by definition. It looks like this:
CREATE PROCEDURE
NAMESPACE_User_Update
(
@intUserId int
,@uidParentUserId uniqueidentifier
,@strFirstName nvarchar(50) = NULL
,@strLastName nvarchar(256) = NULL
,@strPersonalUrl nvarchar(256) = NULL
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
UPDATE
[dbo].[NAMESPACE_User]
SET
[ParentUserId] = @uidParentUserId
,[FirstName] = @strFirstName
,[LastName] = @strLastName
,[PersonalUrl] = @strPersonalUrl
WHERE
[UserId] = @intUserId
Finally, the Foreign Key Select list stored procedures will follow this pattern:
CREATE PROCEDURE
NAMESPACE_User_SelectList_ByParentUserId
(
@uidParentUserId int
)
AS
SET NOCOUNT ON
BEGIN
DECLARE @v_intError AS int
SELECT
[UserId]
,[ParentUserId]
,[FirstName]
,[LastName]
,[PersonalUrl]
FROM
[dbo].[NAMESPACE_User]
WITH
(NOLOCK)
WHERE
[ParentUserId] = @uidParentUserId
Now that stored procedures have been generated they can be executed against the server using the following code:
private static void CreateProcedure(ProcedureType ProcType, string ClassName, string FolderName, string Prefix,
IEnumerable<CodeGeneratorProperty> PropertyList, string ForeignKeyName,
bool PersistToDatabase, Database SelectedDatabase)
{
// Get the name of the stored procedure and the file to which it iwll be persisted
string sprocName = GetStoredProcedureName(ProcType, Prefix, ClassName, ForeignKeyName);
string fileName = string.Format("{0}\\{1}{2}", FolderName, sprocName, SQL_FILE_EXTENSION);
// Generate all of the different parts of the procedure
string procedureContent = string.Empty;
procedureContent += CreateDrop(sprocName, SelectedDatabase);
procedureContent += CreateHeader(ProcType, Prefix, ClassName, ForeignKeyName);
procedureContent += CreateBody(ProcType, Prefix, ClassName, PropertyList, ForeignKeyName);
procedureContent += CreateClose(ProcType, Prefix, ClassName, ForeignKeyName);
// Persist it to a physical file
CodeGenerationHelper.WriteFile(fileName, procedureContent);
// Execute it against the database if it is to be persisted there as well
if (PersistToDatabase)
{
SelectedDatabase.ExecuteNonQuery(procedureContent);
}
}
That wraps it up for this installation, next we'll look at creating integrated InterfaceObject, DataAccess and ApplicationObject (BusinessObject) classes based on the definition file that use these newly created stored procedures and provide an API to the users of our classes.