In this installation of my blog series I'll cover 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.
The Interface, Data Access and Application objects will be created using the field values from the CodeGeneratorPropertyList. The interface will define the basic property signatures for each field. The Data Access class will perform CRUD and list retrieval based on the stored procedures just created. The Application Object will interact with the Data Access class using primitive values where possible and the generated interface when not. So, when a new item is created the DataAccess would be expecting an instance of a class that implements the interface to be passed to it. The Application Object will pass itself as it is just such a class.
The Application Object will also handle keeping track of whether or not it is dirty (a value has changed since instantiation) and whether or not it is new (the value does not yet exist in the database), s well as check some business rules. It does this by inheriting from a base class that is standard within the code generator. This will reduce unnecessary calls to persist unchanged items to the database or to try and delete items that do not yet exist. The CheckRules method will initially just check that required fields are present and that fields with a given length (varchar, char, etc.) are not longer than allowed.
The Application Object and the Data Access classes will both use partial classes and partial methods to take advantage of the Code Generator's ability to generate new code while at the same time not losing any modified code. The CheckRules method is a partial method. The first time the file is generated the generator creates a method signature in the upper code generated class and then implements the actual method in the custom code section. The generator does not do this if the file already exists so as not to lose any custom code. Unfortunately, this means that any new rules for required fields or field length need to be implemented manually.
This will be changed in the future to make the CheckRules method a standard code method and add a call to a new partial method named CheckCustomRules. This is a great example of one of the enormous advantages in creating and using a custom, code generator -the ability to incrementally improve it as time goes on. This allows the developer to deliver more robust, re-usable code in less time.
The code to generate the interface is very straightforward. It writes the using statements, the namespace and interface name using the CreateNameSpaceHeader method:
private static string CreateNameSpaceHeader(string Namespace, string ClassName, IEnumerable<CodeGeneratorProperty> PropertyList)
{
StringBuilder retVal = new StringBuilder();
retVal.Append(string.Format("using System;{0}", CodeGenerationHelper.GetEndOfLine()));
foreach (CodeGeneratorProperty property in PropertyList)
{
if (property.DataType.EndsWith("List"))
{
retVal.Append(string.Format("using System.Collections.Generic;{0}", CodeGenerationHelper.GetEndOfLine()));
break;
}
}
retVal.Append(CodeGenerationHelper.GetEndOfLine());
retVal.Append(string.Format("namespace {0}{1}", Namespace, CodeGenerationHelper.GetEndOfLine()));
retVal.Append(string.Format("{{{0}", CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}///<summary>{1}", CodeGenerationHelper.GetTabIndent(1),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}///The public interface for the {1} object{2}", CodeGenerationHelper.GetTabIndent(1),
ClassName, CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}///</summary>{1}", CodeGenerationHelper.GetTabIndent(1),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(string.Format("\tpublic interface I{0} " + CodeGenerationHelper.GetEndOfLine(), ClassName));
retVal.Append(string.Format("\t{{{0}", CodeGenerationHelper.GetEndOfLine()));
return retVal.ToString();
}
and then iterates through the CodeGeneratorPropertyList to create the property signatures using the CreateAccessorSignatures method:
private static string CreateAccessorSignatures(IEnumerable<CodeGeneratorProperty> PropertyList)
{
StringBuilder retVal = new StringBuilder();
retVal.Append(
string.Format("{0}#region Property Signatures {1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
foreach (CodeGeneratorProperty property in PropertyList)
{
retVal.Append(
string.Format("{0}/// <summary>{1}",
CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}/// {1}{2}",
CodeGenerationHelper.GetTabIndent(2), property.Description, CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}/// </summary>{1}",
CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));
if (property.DataType.EndsWith("List"))
{
retVal.Append(
string.Format("{0}List<{1}> {2}{3}",
CodeGenerationHelper.GetTabIndent(2),
property.DataType.Substring(0, property.DataType.LastIndexOf("List")),
property.PropertyName,
CodeGenerationHelper.GetEndOfLine()));
}
else
{
retVal.Append(
string.Format("{0} {1} {2}{3}",
CodeGenerationHelper.GetTabIndent(2), property.DataType, property.PropertyName,
CodeGenerationHelper.GetEndOfLine()));
}
retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(2));
if (property.IncludeGet)
{
retVal.Append( string.Format("{0} get;{1}",
CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));
}
if (property.IncludeSet)
{
retVal.Append( string.Format("{0} set;{1}",
CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));
}
retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(2));
retVal.Append( CodeGenerationHelper.GetEndOfLine());
}
retVal.Append(
string.Format("{0}#endregion Property Signatures{1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
return retVal.ToString();
}
This method also handles the use of List classes that are typically foreign key based collections (e.g UserAddresses is a UserAdressList that has the UserId as the Foreign Key).
The Data Access generation is also very straightforward but with a few more moving parts. It also creates the header in the same way but then generates the constants that reference the stored procedures created for the CRUD operations, it then uses the CodeGeneratorPropertyList to create Data Access methods (Find, Fetch, Add, Change and Remove) - these methods will be called by the Application Object methods as follows:
|
Application Object Method |
Data Access Method |
|
Exists |
Find |
|
Select |
Fetch |
|
Save - depending on the IsNew property - Add if IsNew; Change if not IsNew |
Add |
|
Change |
|
Delete |
Remove |
A good example of the code generator method for the Data Access class would be CreateAddMethod:
private static string CreateAddMethod(string ClassName, IEnumerable<CodeGeneratorProperty> PropertyList)
{
StringBuilder retVal = new StringBuilder();
retVal.Append(
string.Format("{0}#region Add Data {1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}// called to add new data into the database{1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}internal static bool Add(ref int insertedId, I{1} {1}Object){2}",
CodeGenerationHelper.GetTabIndent(2), ClassName, CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}using (SqlConnection cn = ConnectionManager.OpenConnection()){1}",
CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}try{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}using (SqlCommand cm = cn.CreateCommand()){1}", CodeGenerationHelper.GetTabIndent(5),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(5), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}cm.CommandType = CommandType.StoredProcedure;{1}",
CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}cm.CommandText = INSERT_SPROC;{1}", CodeGenerationHelper.GetTabIndent(6),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}cm.Parameters.Add(\"returnValue\", SqlDbType.Int);{1}",
CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format(
"{0}cm.Parameters[\"returnValue\"].Direction = ParameterDirection.ReturnValue;{1}",
CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(CreateParameterList(ClassName, PropertyList, 6));
retVal.Append(
string.Format("{0}cm.ExecuteNonQuery();{1}", CodeGenerationHelper.GetTabIndent(6),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}insertedId = SafeData.ConvertInt(cm.Parameters[\"@{1}{2}\"].Value);{3}",
CodeGenerationHelper.GetTabIndent(6),
CommonHelper.GetVariableAbbreviation(CommonHelper.GetPrimaryKey(PropertyList)),
CommonHelper.GetPrimaryKeyName(PropertyList), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}if (Convert.ToInt16(cm.Parameters[\"returnValue\"].Value) != 0){1}",
CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}return false;{1}", CodeGenerationHelper.GetTabIndent(7),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}else{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}return true;{1}", CodeGenerationHelper.GetTabIndent(7),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(6), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(5), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}catch (Exception ex){1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}{{{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}SimpleLogger.LogError(ex.Message);{1}", CodeGenerationHelper.GetTabIndent(5),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}return false;{1}", CodeGenerationHelper.GetTabIndent(5),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(4), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(3), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}}}{1}", CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}#endregion Add Data {1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
return retVal.ToString();
}
Each of the methods works in the same fashion, creating the method and where necessary calling a helper method named CreateParameterList to generate each Property as it is needed. The List class has the same process applied to it but only for the SelectList and SelectListByForeignKey methods. The SelectListByForeignKey are generated for each Foreign Key individually, a future enhancement might be to define multiple key foreign keys in the code generator and have those methods generated as well. Currently this has to be done manually.
Finally the Application Object generator works in a fashion that combines both of the previous generators. It creates The Namespace Header as before, it creates the Property accessors, much like the Interface generator, except this time it implements the actual accessor not just the signature and it implements the corresponding public methods listed in the table above. It implements constructors which as well. Additionally, the Application Object performs a rules check before allowing the object to be persisted. The CheckRules method is generated as follows:
private static string CreateCheckRules(string input, IEnumerable<CodeGeneratorProperty> PropertyList)
{
StringBuilder retVal = new StringBuilder();
retVal.Append(
string.Format("{0}/// <summary>{1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}/// All business rules for this class are checked in this method{1}",
CodeGenerationHelper.GetTabIndent(2), CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}/// </summary>{1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append(
string.Format("{0}public void CheckRules(){1}", CodeGenerationHelper.GetTabIndent(2),
CodeGenerationHelper.GetEndOfLine()));
retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(2));
// Walk through property list and create rules
retVal.Append(CommonHelper.GetRulesForRequiredProperties(3, PropertyList));
retVal.Append(CodeGenerationHelper.GetStandAloneClosingBracket(2));
return (input + retVal);
}
As one can see, the work for this method is mostly performed in the GetRuleForRequiredProperties helper method:
public static string GetRulesForRequiredProperties(int TabIndent, IEnumerable<CodeGeneratorProperty> PropertyList)
{
string retVal = string.Empty;
foreach (CodeGeneratorProperty property in PropertyList)
{
if (property.IsRequired)
{
switch (property.DataType)
{
case "int":
retVal += GetRequiredInteger(TabIndent, property);
break;
case "Boolean":
retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";
break;
case "string":
retVal += GetRequiredString(TabIndent, property);
break;
case "byte[]":
retVal += GetRequiredByteArray(TabIndent, property);
break;
case "Guid":
retVal += GetRequiredGuid(TabIndent, property);
break;
case "DateTime":
retVal += GetRequiredDate(TabIndent, property);
break;
case "Double":
retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";
break;
case "Decimal":
retVal += GetRequiredDecimal(TabIndent, property);
break;
default:
retVal += "**** NOT YET IMPLEMENTED IN METHOD GetRulesForRequiredProperties() **** \n";
break;
}
}
}
return retVal;
}
This in turn uses other helper methods, such as GetRequiredString:
private static string GetRequiredString(int TabIndent, CodeGeneratorProperty property)
{
StringBuilder retVal = new StringBuilder();
retVal.Append(
string.Format("{0}if(String.IsNullOrEmpty(_{1})){2}", CodeGenerationHelper.GetTabIndent(TabIndent),
property.PropertyName,
CodeGenerationHelper.GetEndOfLine()));
retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(TabIndent);
retVal.Append(
string.Format("{0}BrokenRules.Add(\"{1} is a required field and cannot be null or empty.\");{2}",
CodeGenerationHelper.GetTabIndent(TabIndent + 1), property.PropertyName,
CodeGenerationHelper.GetEndOfLine()));
retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(TabIndent));
// Greater than max length
retVal.Append(
string.Format("{0}if(_{1}.Length > {2}){3}", CodeGenerationHelper.GetTabIndent(TabIndent),
property.PropertyName, property.Size,
CodeGenerationHelper.GetEndOfLine()));
retVal.Append( CodeGenerationHelper.GetStandAloneOpeningBracket(TabIndent));
retVal.Append(
string.Format("{0}BrokenRules.Add(\"{1} has exceeded its maximum length of {2} characters.\");{3}",
CodeGenerationHelper.GetTabIndent(TabIndent + 1), property.PropertyName, property.Size,
CodeGenerationHelper.GetEndOfLine()));
retVal.Append( CodeGenerationHelper.GetStandAloneClosingBracket(TabIndent));
return retVal.ToString();
}
As you can see not every DataType is handled, again, this being a personal code generator it can evolve as needed, instead of having to cover every scenario.
That wraps it up for this installation. We now have the ability to generate a table, its related stored procedures, a related interface, a related data access class and an application object class that exposes an API for any user. In the next and final installation of this posting we'll look at pulling it all together and writing a little "calorie counter" application to see how it works.
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.