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.
In a recent project I worked on I had the opportunity to help the client troubleshoot memory leaks and performance issues on a call center type application. The application has been released in multiple iterations into production and the memory leak and slow performance are now becoming more and more of an issue as new functionality is added.
My assignment was simple, fix the memory leaks and improve performance. After doing some research online and experimenting with various techniques and products I was finally able to put together a process in place to address the task at hand. I used a profiler named Ants Profiler by Red Gate to aid in resolving memory issues. For the performance problems I used DotTrace. Both products advertised that they can do both but I found that they only excel in one area as mentioned above.
Ants Profiler did a great job at aiding in finding memory leaks but was pretty terrible for troubleshooting performance problems. With Ants I was able to analyze and identify memory leaks mainly by figuring out who is holding on to references to what. Understanding the design of the application was required to know what is supposed to be in memory and what is not supposed to during the life span of an application. The number one culprit that I found were events not probably being cleaned up (set to null). The performance of Ants Profiler is slow, very slow. Especially when code is loop heavy (ex for, while foreach etc), it seems to incur extra overhead during profiling. And because of this and other factors, the performance figures reported were not accurate, which made it not a good choice for performance troubleshooting. Ants Profiler tech support was of little help but the good news is they said a new version is coming that is supposed to resolve the problem. One feature I like about Ants Profiler is the ability to view the actual code in the GUI where the memory leak is occurring; it was pretty handy and convenient feature.
Due to the limited ability of Ants Profiler in troubleshooting performance issues I turned my head to DotTrace. In default configuration DotTrace also reported inaccurate numbers, but after some tweaking in the settings, I was able to get accurate enough numbers to move forward. DotTrace reports performance figures in a tree like structure, I was able to drill down the classes and methods through the hierarchy to pinpoint the source of the performance problem. Each tree node includes the time on how long it took for each method to run. A good feature I found with DotTrace is its filtering functionally, I wanted to located all the web service calls and figure out how long they were taking, with the custom filters I was able to quickly do that. Performance issues found in the client’s application included events snowballing, in correct UI control population, incorrect usage of xml files and other.
After the project was completed, the business and call centers reported very noticeable performance increase in the application and the memory leak issue was eliminated.
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).