Search results matching tag 'RAD'

Part 6 - Generating Some Classes (and an Interface)

JohnF — Mon, 12 May 2008 14:51:00 GMT

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.

Part 5 - Creating Stored Procedures using SQL Server Management Objects (SMO)

JohnF — Wed, 30 Apr 2008 00:55:00 GMT

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]

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

* 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

A header

************************************************************************************************************

* 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

)

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

a successful ‘generated' print message.

IF @@ERROR = 0

BEGIN

PRINT '<<NAMESPACE_User_Select_ByUserId stored procedure was created successfully>>'

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS OFF

GRANT EXECUTE ON [NAMESPACE_User_Select_ByUserId] TO [public]

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

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

)

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

)

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

)

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

)

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

)

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

)

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.

Part 3 - Saving and Loading Definition Files

JohnF — Mon, 14 Apr 2008 02:29:00 GMT

In this installation of my blog series I’ll cover saving and loading definition files to and from XML.

To enable the code generator to do its work and be re-usable across any application development we need to do a few things:

1. Define Code Generation Property class that will hold the data we need to generate our SQL and class code. For the sake of simplicity I’ll call this class CodeGeneratorProperty.

2. Create interface to create our definition, that is, we need some UI to easily enter our properties and the data associated with them. It has to be very easy to understand and it has to cover all of the data that we need to enter.

3. Persist the definition, that is, be able to save and load a configuration as it is needed.

1. The definition of the CodeGeneratorProperty class will include the following fields and corresponding properties:

private string _propertyName; private bool _isPrimaryKey; private bool _isForeignKey; private bool _includeGet; private bool _includeSet; private string _dataType; private bool _isRequired; private string _size; private string _description;

Within the same physical file I’ll add a CodeGeneratorPropertyList class, like so:

CodeGeneratorPropertyList: IEnumerable<CodeGeneratorProperty>

As you can see this class implements the IEnumerable<T> of Type CodeGeneratorProperty. This will allow us to store multiple Code Generator Properties and then iterate that list as necessary.

2. A very easy and intuitive interface for our Code Generator Properties is the data grid view. If we add a data grid view to contain our properties, a couple of text fields to define a namespace and class name, and some buttons to Load and Save our definition files, we would end up with something like the following:

3. Finally, persisting and consequently retrieving our definition can be accomplished using some straight-forward xml. Our Save Definition code would be something like the following:

const string CODE_GENERATION_DEFINITION_FOLDER_NAME_KEY = "CodeGeneratorDefinitionFilePath"; const string CODE_GENERATION_DEFINITION_FOLDER_NAME = "CodeGeneratorDefinition"; private CodeGeneratorPropertyList _propertyList; private void btnSaveDefinition_Click(object sender, EventArgs e) { try { CreatePropertyList(); // Create an xml file holding all of the values on the screen // Filename is Namespace underscore Class name. xml string fileDirectory = ConfigValues.GetString(CODE_GENERATION_DEFINITION_FOLDER_NAME_KEY); if (fileDirectory.Length == 0) { fileDirectory = string.Format("C:\\Temp\\{0}\\", CODE_GENERATION_DEFINITION_FOLDER_NAME); } string filename = string.Format("{0}{1}_{2}.xml", fileDirectory, txtNamespace.Text, txtClassName.Text); XmlTextWriter writer = new XmlTextWriter(filename, null); writer.Formatting = Formatting.Indented; writer.WriteStartDocument(true); writer.WriteStartElement("Contents"); writer.WriteElementString("Namespace", txtNamespace.Text); writer.WriteElementString("ClassName", txtClassName.Text); writer.WriteStartElement("Properties"); foreach (CodeGeneratorProperty property in _propertyList) { writer.WriteStartElement("Property"); writer.WriteElementString("PropertyName", property.PropertyName); writer.WriteElementString("IsPrimaryKey", property.IsPrimaryKey.ToString()); writer.WriteElementString("IsForeignKey", property.IsForeignKey.ToString()); writer.WriteElementString("IsRequired", property.IsRequired.ToString()); writer.WriteElementString("IncludeGet", property.IncludeGet.ToString()); writer.WriteElementString("IncludeSet", property.IncludeSet.ToString()); writer.WriteElementString("DataType", property.DataType); writer.WriteElementString("Size", property.Size); writer.WriteElementString("Description", property.Description); writer.WriteEndElement(); // Property } writer.WriteEndElement(); // Properties writer.WriteStartElement("ForeignKeys"); writer.WriteEndElement(); // ForeignKeys writer.WriteEndElement(); // Contents writer.WriteEndDocument(); writer.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Code Generator Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } private void CreatePropertyList() { _propertyList.Clear(); for (int i = 0; i < (dgvProperties.Rows.Count - 1); i++) { DataGridViewRow row = dgvProperties.Rows[i]; CodeGeneratorProperty property = new CodeGeneratorProperty(); if (row.Cells["PropertyName"].Value == null) { throw new Exception("Property Name is required"); } else { property.PropertyName = SafeData.ConvertString(row.Cells["PropertyName"].Value.ToString()); } if (!(row.Cells["IsPrimaryKey"].Value == null)) { property.IsPrimaryKey = SafeData.ConvertBoolean(row.Cells["IsPrimaryKey"].Value.ToString()); } if (!(row.Cells["IsForeignKey"].Value == null)) { property.IsForeignKey = SafeData.ConvertBoolean(row.Cells["IsForeignKey"].Value.ToString()); } if (!(row.Cells["IsRequired"].Value == null)) { property.IsRequired = SafeData.ConvertBoolean(row.Cells["IsRequired"].Value.ToString()); } if (!(row.Cells["IncludeGet"].Value == null)) { property.IncludeGet = SafeData.ConvertBoolean(row.Cells["IncludeGet"].Value.ToString()); } if (!(row.Cells["IncludeSet"].Value == null)) { property.IncludeSet = SafeData.ConvertBoolean(row.Cells["IncludeSet"].Value.ToString()); } if (!(row.Cells["DataType"].Value == null)) { property.DataType = SafeData.ConvertString(row.Cells["DataType"].Value.ToString()); } if (!(row.Cells["Size"].Value == null)) { property.Size = SafeData.ConvertString(row.Cells["Size"].Value.ToString()); } if (!(row.Cells["Description"].Value == null)) { property.Description = SafeData.ConvertString(row.Cells["Description"].Value.ToString()); } _propertyList.Add(property); } }

The corresponding Load Definition code would then be this:

private void btnLoadDefinition_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "xml files (*.xml)|*.xml"; string fileDirectory = ConfigValues.GetString(CODE_GENERATION_DEFINITION_FOLDER_NAME_KEY); if (fileDirectory.Length == 0) { fileDirectory = string.Format("C:\\Temp\\{0}\\", CODE_GENERATION_DEFINITION_FOLDER_NAME); } dialog.InitialDirectory = fileDirectory; dialog.Title = "Select definition file"; string fileToOpen = string.Empty; if (dialog.ShowDialog() == DialogResult.OK) { fileToOpen = dialog.FileName; } if (fileToOpen.Length > 0) { dgvProperties.Rows.Clear(); // Read a chosen xml file and populate the screen controls XmlDocument doc = new XmlDocument(); doc.Load(fileToOpen); txtNamespace.Text = doc.SelectSingleNode("/Contents/Namespace").InnerText; txtClassName.Text = doc.SelectSingleNode("/Contents/ClassName").InnerText; XmlNodeList propertiesNodeList = doc.SelectNodes("/Contents/Properties/Property"); dgvProperties.Rows.Add(propertiesNodeList.Count); int i = 0; foreach (XmlNode node in propertiesNodeList) { DataGridViewRow row = dgvProperties.Rows[i++]; foreach (XmlNode child in node.ChildNodes) { row.Cells[child.Name].Value = child.InnerText; } } } }

These methods then allow us to enter values, save them in a definition file and re-load them as needed, as seen here:

That wraps it up for this installation, next we’ll look at defining, creating and persisting SQL scripts for tables based on our CodeGeneratorPropertyList using SQL Server Management Objects (SMO).

Part 2 - Modify app.config On-The-Fly

JohnF — Fri, 21 Mar 2008 16:28:00 GMT

In this installation of my blog series I’ll cover the first concrete item listed in the introduction. Implement a WinForm that modifies its own Config file.

Let’s start by creating a Main form for the UI that will be our work area for the Code Generator. We add a button “Modify Config” that executes the following code to launch the frmAppConfig form:

private void btnModifyConfig_Click(object sender, EventArgs e)
{
            frmAppConfig dlgAppconfig = new frmAppConfig();
            dlgAppconfig.ShowDialog();
}

The form itself looks like this at this point:

The frmAppConfig executes the following LoadConfigValues code called from its constructor:

        private void LoadConfigValues()
        {
            try
            {
                NameValueCollection appSettings
                    = ConfigurationManager.AppSettings;
                dgvAppConfigSettings.Rows.Clear();
                dgvAppConfigSettings.Rows.Add(appSettings.Count);
                int i = 0;
                foreach (string key in appSettings)
                {
                    string value = appSettings[key];
                    DataGridViewRow row = dgvAppConfigSettings.Rows[i++];
                    row.Cells["Key"].Value = key;
                    row.Cells["Value"].Value = value;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message,
                                "Setting Application Configuration Error",
                                MessageBoxButtons.OK,
                                MessageBoxIcon.Stop);
            }
        }

As you can see the method uses the System.Configuration.ConfigurationManager.AppSettings property to get a System.Collections.Specialized.NameValueCollection of the app settings in the app.config file. If we open the app.config file we’ll see the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
    <clear />
    <add key="CodeGeneratorDefinitionFilePath" value="C:\Syrinx\Syrinx.Architecture\DefinitionFiles\" />
    <add key="DataAccessFilePath" value="C:\Syrinx\Syrinx.Consumer\DataAccess\" />
    <add key="ApplicationObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\ApplicationObject\" />
    <add key="DeveloperName" value="John P. Frampton" />
    <add key="InterfaceObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\InterfaceObject\" />
    <add key="StoredProcedureFilePath" value="C:\Syrinx\Syrinx.DB\StoredProcedures\" />
    <add key="TableDefinitionFilePath" value="C:\Syrinx\Syrinx.DB\Tables\" />
</appSettings>
<connectionStrings>
    <add
      name="FakeConnectionString"
      connectionString="NotReally"/>
</connectionStrings>
</configuration>

Note that there is a connectionStrings section in the app.config with which we are not concerned and which will not be modified. If we run our application, not in debug mode, but with the actual compiled .exe, the frmAppConfig form will display the following:

As expected the DataGridView is populated with values from the appSettings section of the Config file. The only thing we have left to do is persist any changes back into the app.config file. That is accomplished with the following code:

    private void PersistAppSettings()
    {
            Hashtable htAppConfigValuesInGrid = new Hashtable();
            // Load the existing Grid values into a hashtable
            htAppConfigValuesInGrid.Clear();
            foreach (DataGridViewRow row in dgvAppConfigSettings.Rows)
            {
                if (row.Cells["Key"].Value != null)
                {
                    htAppConfigValuesInGrid.Add(row.Cells["Key"].Value, row.Cells["Value"].Value);
                }
            }
            // Open App.Config of executable and get AppSettingsSection
            Configuration config
                = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            AppSettingsSection appSettings = (AppSettingsSection)config.GetSection("appSettings");
            appSettings.Settings.Clear();
            // Go through the hashtable and see if the value exists in the config file
            // if so update it
            // if not, add it
            foreach (DictionaryEntry entry in htAppConfigValuesInGrid)
            {
                string key = entry.Key.ToString();
                KeyValueConfigurationElement kvcElement = appSettings.Settings[key];
                // exists in the grid but not in the appSettings, add the missing value
                if (kvcElement == null)
                {
                    appSettings.Settings.Add(key, (string)htAppConfigValuesInGrid[key]);
                }
                else
                {
                    // only update the value if it has changed
                    if (kvcElement.Value != (string)htAppConfigValuesInGrid[key])
                    {
                        appSettings.Settings[key].Value = (string)htAppConfigValuesInGrid[key];
                    }
                }
            }
             // Go through the settings and remove any that are not in the grid hashtable
            foreach (KeyValueConfigurationElement element in config.AppSettings.Settings)
            {
                string keyVal = (string)htAppConfigValuesInGrid[element.Key];
                if (keyVal == null)
                {
                    appSettings.Settings.Remove(element.Key);
                }
            }
            // Save the configuration file.
            //config.Save(ConfigurationSaveMode.Modified, true);
            config.Save();
            // Force a reload of a changed section.
            ConfigurationManager.RefreshSection("appSettings");
        }
    }

If we add a new setting and modify an existing one, the UI will show this:

And the executables app.config (SyrinxCodeGenerator.exe.config) will reflect the addition and update like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
    <clear />
    <add key="DeveloperName" value="John P. Frampton" />
    <add key="StoredProcedureFilePath" value="C:\Syrinx\Syrinx.DB\StoredProcedures\" />
    <add key="ApplicationObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\ApplicationObject\" />
    <add key="InterfaceObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\InterfaceObject\" />
    <add key="TableDefinitionFilePath _I_HAVE_CHANGED" value="C:\Syrinx\Syrinx.DB\Tables\" />
    <add key="NEW_APP_SETTING" value="THIS_IS_NEW" />
    <add key="DataAccessFilePath" value="C:\Syrinx\Syrinx.Consumer\DataAccess\" />
    <add key="CodeGeneratorDefinitionFilePath" value="C:\Syrinx\Syrinx.Architecture\DefinitionFiles\" />
</appSettings>
<connectionStrings>
    <add
      name="FakeConnectionString"
      connectionString="NotReally"/>
</connectionStrings>
</configuration>

If we remove the new field from the UI we see that the app.config file reflects it like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
    <clear />
    <add key="TableDefinitionFilePath _I_HAVE_CHANGED" value="C:\Syrinx\Syrinx.DB\Tables\" />
    <add key="DataAccessFilePath" value="C:\Syrinx\Syrinx.Consumer\DataAccess\" />
    <add key="ApplicationObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\ApplicationObject\" />
    <add key="DeveloperName" value="John P. Frampton" />
    <add key="InterfaceObjectFilePath" value="C:\Syrinx\Syrinx.Consumer\InterfaceObject\" />
    <add key="CodeGeneratorDefinitionFilePath" value="C:\Syrinx\Syrinx.Architecture\DefinitionFiles\" />
    <add key="StoredProcedureFilePath" value="C:\Syrinx\Syrinx.DB\StoredProcedures\" />
</appSettings>
<connectionStrings>
    <add
      name="FakeConnectionString"
      connectionString="NotReally"/>
</connectionStrings>
</configuration>

That wraps it up for this installation, next we’ll look at saving and loading definition files to and from XML.

Part 1 - RAD and Custom Code Generation

JohnF — Tue, 08 Jan 2008 15:27:00 GMT

I'm going to write a blog series about Rapid Application Development (RAD) and the advantages of using a custom code generator. Today I'm going to review what I'm going to cover in the coming weeks.

The most obvious question to ask on this topic is this: With all of Visual Studio's drag-and-drop development ability isn't it pointless to develop a custom code generator to begin with? The answer to that question really lies in understanding the difference between developing a working application and developing a production application.

A consultant's job isn't just developing an application that satisfies a client's requirements within a given time and budget (a working application), but in also leaving behind a code base that can be enhanced and extended with just a modicum of effort (a production application). There should be at the very least some basic documentation and the concept of an n-tiered architecture, with each tier easily distinguished from another. Good consultants don't just develop for their client, they also develop for the next developer -- who may very well be themselves if they capture more of the client's business.

Microsoft's RAD tools offer a good number of positives. The Enterprise Libraries, for instance, give current best practices with good examples and documentation. They are easy to add as references in an application and easy to use out of the box. The biggest disadvantage to the Enterprise libraries are that as best practices evolve the library used earlier may be obsolete, so a very necessary first step is to wrap all of the libraries you intend to use into an assembly of your own. This way you can guarantee the interface and change the implementation details as newer methodologies and best practices appear.

Without subclassing in this way upgrades to the libraries may require wholesale updates in your application to keep it current. This isn't just theoretical pondering, the move from the .NET 1.1 framework to the 2.0 framework incorporated the Cryptography and Membership libraries into actual framework namespaces, without having the libraries wrapped switching to the newer (and better) implementations in the framework proved to be very, very painful.

Another problem with using the libraries is that you are stuck with what you get - which in some cases may be more than you really need. This is also the case for many of the drag-and-drop techniques used in Visual Studio - you tend to get everything whether you need it or not. It tends to be hard to read and harder to update and support.

A custom code generator avoids many of these aggravations. You can develop in the way you typically develop. You can implement automatic documenting to a large degree. You can get most of the rapid development pluses (i.e. get up and running fast) with few of the minuses (lack of supportability). You can design it to end up with a readable, easy-to-support and enhance code base.

To do this we'll need to tackle the following areas, which we'll do over the coming weeks.

Seperate the UI from the Business implmentation, but decide what type of UI we want to use so that we can develop a concrete example. (I'll use a WinForm)
Be able to modify the app.config file from within the application
Load and save definition files so we only have to define our objects once
Use SQL SMO to do the following:

Define a table and have it created in the selected database
Create table DDL and persist it to a directory for backup into a code library
Create the formatted T-SQL for the CRUD stored procedures based on the definition file
Execute formatted T-SQL to create the CRUD stored procedures in the selected database

Create integrated InterfaceObject, DataAccess and ApplicationObject (BusinessObject) based on the defintiion file
Create a simple application using the code generator to show it in action

Define the application so the steps will relate to a concrete implementation instead of an abstract idea

Calorie counter (perfect to help with my New Year's resolution)

Create a list of features that we'd like to see implemented for future versions