Code Generation Part I – Extract Database Metadata
Overview
This article is the first in a series of code generation articles that will demonstrate the basic concepts of using code and freely available tools to generate code and eliminate hours of tedious and repetitive work. We will in addition, demonstrate how code generation may be used to create consistent code based on architectural patterns. Instead of presenting all of the concepts involved in code generation all at once, we will peacemeal these concepts into major functional areas. These are:
- Extraction of database metadata without the use of SQLDMO or its .NET counterpart SMO
- Using LINQ to XML to format all extracted metadata
- Using XSLT to generate code on the fly
- Using what we've learned with design patterns
Data About Data
Before any code generation can take place, it is very important to obtain as much information about the data store we are trying to generate code for. This descriptive metadata, or "data about data" is at the heart of our code generation process. For the purposes of this article, we will be using Microsoft SQL Server 2008 as our database engine. The concepts discussed in this article could be applied to other database engines as long as specific metadata extraction methodologies, specific to that engine, are used.
Before we go any further however, some friendly advice about naming conventions and style is in order. In this article, as well as any other article or project, Pascal Notation is used, NOT Hungarian Notation. For a comparison and stylistic differences, please refer to Naming Conventions for .NET / C# Projects. Adhering to these conventions will make our resulting code more elegant, descriptive, and natural. The importance of this will become more apparent as we move forward.
First Steps
Now that we have a basic understanding of our requirements, we can now begin by creating our database. For the purpose of this article, a very simple database was created called "CodeGeneration". This is our first step, so please load SQL Server Management Studio and create this database. Use the following SQL script to create the schema by loading a new script editor ensuring that the CodeGeneration database is selected first:
USE [CodeGeneration] GO /****** Object: Table [dbo].[Gender] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Gender]( [GenderID] [int] IDENTITY(1,1) NOT NULL, [Type] [nvarchar](50) NOT NULL, [Created] [datetime] NOT NULL, [Updated] [datetime] NOT NULL, [IsActive] [bit] NOT NULL, [Description] [nvarchar](512) NOT NULL, CONSTRAINT [PK_Gender] PRIMARY KEY CLUSTERED ( [GenderID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Person] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [GenderID] [int] NOT NULL, [FullName] [nvarchar](50) NOT NULL, [Age] [int] NOT NULL, [Created] [datetime] NOT NULL, [Updated] [datetime] NOT NULL, [IsActive] [bit] NOT NULL, [Description] [nvarchar](512) NOT NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [PersonID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Email] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Email]( [EmailID] [int] IDENTITY(1,1) NOT NULL, [PersonID] [int] NOT NULL, [Email] [nvarchar](50) NOT NULL, [Created] [datetime] NOT NULL, [Updated] [datetime] NOT NULL, [IsActive] [bit] NOT NULL, [Description] [nvarchar](512) NOT NULL, CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ( [EmailID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Default [DF_Email_Created] ******/ ALTER TABLE [dbo].[Email] ADD CONSTRAINT [DF_Email_Created] DEFAULT (getdate()) FOR [Created] GO /****** Object: Default [DF_Email_Updated] ******/ ALTER TABLE [dbo].[Email] ADD CONSTRAINT [DF_Email_Updated] DEFAULT (getdate()) FOR [Updated] GO /****** Object: Default [DF_Email_IsActive] ******/ ALTER TABLE [dbo].[Email] ADD CONSTRAINT [DF_Email_IsActive] DEFAULT ((1)) FOR [IsActive] GO /****** Object: Default [DF_Gender_Created] ******/ ALTER TABLE [dbo].[Gender] ADD CONSTRAINT [DF_Gender_Created] DEFAULT (getdate()) FOR [Created] GO /****** Object: Default [DF_Gender_Updated] ******/ ALTER TABLE [dbo].[Gender] ADD CONSTRAINT [DF_Gender_Updated] DEFAULT (getdate()) FOR [Updated] GO /****** Object: Default [DF_Gender_IsActive] ******/ ALTER TABLE [dbo].[Gender] ADD CONSTRAINT [DF_Gender_IsActive] DEFAULT ((1)) FOR [IsActive] GO /****** Object: Default [DF_Person_Created] ******/ ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_Created] DEFAULT (getdate()) FOR [Created] GO /****** Object: Default [DF_Person_Updated] ******/ ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_Updated] DEFAULT (getdate()) FOR [Updated] GO /****** Object: Default [DF_Person_IsActive] ******/ ALTER TABLE [dbo].[Person] ADD CONSTRAINT [DF_Person_IsActive] DEFAULT ((1)) FOR [IsActive] GO /****** Object: ForeignKey [FK_Email_Person] ******/ ALTER TABLE [dbo].[Email] WITH CHECK ADD CONSTRAINT [FK_Email_Person] FOREIGN KEY([PersonID]) REFERENCES [dbo].[Person] ([PersonID]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[Email] CHECK CONSTRAINT [FK_Email_Person] GO /****** Object: ForeignKey [FK_Person_Gender] ******/ ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [FK_Person_Gender] FOREIGN KEY([GenderID]) REFERENCES [dbo].[Gender] ([GenderID]) GO ALTER TABLE [dbo].[Person] CHECK CONSTRAINT [FK_Person_Gender] GO
Once the script is executed against the CodeGeneration database, create a new database diagram and add all of the database tables. When that step is complete, you should end up with a model that is similar to the following:
Code Generation Data Model
The database model was purposely made simple in an effort to maintain focus and march straight to our goal of extracting database metadata and storing it in XML format. Having now completed this step, we are now ready to move forward.
Building Blocks
Now that we have a database to work with, we can now begin to create the code generator application. In keeping with tradition, we will build a simple code generator with a Console Application. This application will in turn make use of an utility assembly that will contain all of the necessary functionality to extract metadata from the database and translate that metadata into useful code objects. This assembly will assume the role of the code generation engine. Let's get started!
CodeGenerator.Core
If you haven't done so already, load Visual Studio and create a new empty solution named "Code Generation Tutorial" or anything that you like. Once we have a solution, add a new C# project of type "Class Library" and name it "CodeGenerator.Core" without the quotes. Once created, delete the "Class1.cs" default class as we will be adding our own classes. Since the code generator will rely on a configuration file at a later point in time, go ahead and add a reference to the System.Configuration assembly to our project. We do this because much of the information the code generator will require at runtime, such as server instance name, connection strings, code decoration information, will be stored in a configuration file that we can modify on the fly. It is important to note that just as any other commercially available code generation utility, the application will need to make use of many different configurations and database targets. Therefore, it is recognized that using a single configuration file is probably not the best approach. Given that we are sticking to simplicity however, this approach will be used instead of a more robust configuration management scheme.
The first coding step we will take will be to create a class that will contain a series of string constants. These constants will represent stored procedure names and configuration key names. We do this to avoid or limit the use of "magic strings" in our code.Let's get started. Add a static class to the CodeGenerator.Core project named LibraryConstants.cs and add the following code:
namespace CodeGenerator.Core
{
/// <summary>
/// LibraryConstants class
/// </summary>
static public class LibraryConstants
{
/// <summary>
/// Master database connection string configuration key
/// </summary>
public const string MASTER_DATABASE_CONNECTION_KEY = "MasterDatabaseConnectionString";
/// <summary>
/// Target database connection string configuration key
/// </summary>
public const string TARGET_DATABASE_CONNECTION_KEY = "CodeGeneration";
/// <summary>
/// Target database configuration key
/// </summary>
public const string TARGET_DATABASE_KEY = "TargetDatabase";
/// <summary>
/// SQL Server instance configuration key
/// </summary>
public const string SQL_SERVER_INSTANCE_KEY = "SqlServerInstance";
/// <summary>
/// Help server stored procedure
/// </summary>
public const string HELP_SERVER_SPROC = "sp_helpserver";
/// <summary>
/// Databases stored procedure
/// </summary>
public const string INSTANCE_DATABASES_SPROC = "sp_databases";
/// <summary>
/// Primary keys procedure
/// </summary>
public const string P_KEYS_SPROC = "sp_pkeys";
/// <summary>
/// Foreign keys procedure
/// </summary>
public const string F_KEYS_SPROC = "sp_fkeys";
/// <summary>
/// Columns procedure
/// </summary>
public const string COLUMNS_SPROC = "sp_columns";
/// <summary>
/// Database tables stored procedure
/// </summary>
public const string DATABASE_TABLES_SPROC = "sp_tables";
/// <summary>
/// Include personalization key
/// </summary>
public const string INCLUDE_PERSONALIZATION_KEY = "IncludePersonalization";
/// <summary>
/// Developer name key
/// </summary>
public const string DEVELOPER_NAME_KEY = "DeveloperName";
/// <summary>
/// Developer company key
/// </summary>
public const string DEVELOPER_COMPANY_KEY = "Company";
/// <summary>
/// Developer position key
/// </summary>
public const string DEVELOPER_POSITION_KEY = "Position";
/// <summary>
/// Developer URL key
/// </summary>
public const string DEVELOPER_URL_KEY = "DeveloperUrl";
/// <summary>
/// Copyright key
/// </summary>
public const string COPYRIGHT_KEY = "Copyright";
}
}
Don't worry about what each of these constans mean for now. While their meaning is somewhat explained by the comments in the code, their usage will become more aparent later in this article. We will next create a base class that will contain some important properties we will need. This base class will essentially wrap "AppSettings" elements from the main configuration file that we will add to the code generator application. Add an anstract class to the project named CoreBase.cs and add the following code to it:
using System.Configuration;
namespace CodeGenerator.Core
{
public abstract class CoreBase
{
#region Properties
/// <summary>
/// Gets MasterDatabaseConnectionString
/// </summary>
protected string MasterDatabaseConnectionString
{
get
{
return ConfigurationManager
.ConnectionStrings[LibraryConstants.MASTER_DATABASE_CONNECTION_KEY]
.ConnectionString;
}
}
/// <summary>
/// Gets TargetDatabase
/// </summary>
protected string TargetDatabaseConnectionString
{
get
{
return ConfigurationManager
.ConnectionStrings[LibraryConstants.TARGET_DATABASE_CONNECTION_KEY]
.ConnectionString;
}
}
/// <summary>
/// Gets SqlServerInstance
/// </summary>
protected string SqlServerInstance
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.SQL_SERVER_INSTANCE_KEY];
}
}
/// <summary>
/// Gets TargetDatabase
/// </summary>
protected string TargetDatabase
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.TARGET_DATABASE_KEY];
}
}
/// <summary>
/// Gets IncludePersonalization
/// </summary>
protected string IncludePersonalization
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.INCLUDE_PERSONALIZATION_KEY];
}
}
/// <summary>
/// Gets DeveloperName
/// </summary>
protected string DeveloperName
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.DEVELOPER_NAME_KEY];
}
}
/// <summary>
/// Gets Company
/// </summary>
protected string Company
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.DEVELOPER_COMPANY_KEY];
}
}
/// <summary>
/// Gets Position
/// </summary>
protected string Position
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.DEVELOPER_POSITION_KEY];
}
}
/// <summary>
/// Gets DeveloperUrl
/// </summary>
protected string DeveloperUrl
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.DEVELOPER_URL_KEY];
}
}
/// <summary>
/// Gets Copyright
/// </summary>
protected string Copyright
{
get
{
return ConfigurationManager.AppSettings[LibraryConstants.COPYRIGHT_KEY];
}
}
#endregion
}
}
As seen in the CoreBase class, we are making use of the constants in the LibraryConstants class to identify sections in a future App.config file. Doing this, we now have a clearer idea as to what this file will look like. The App.config file will contain information that will be used for the identification of the target database, server instance, developer information, etc.
One aspect of metadata retrieval that is of great importance is the ability to validate the availability of not just the SQL Server instance, but the existance of the database itself. We can achieve this in many ways such as
- Using SMO or SQLDMO
- Opening a SQLConnection with a connection string and test for exceptions
- or, query SQL Server itself using system procedures to get at the validation data we need
While the second option might seem as the most natural and quickest of all the mentioned options, it is part of the intention of this article to expose the functionality of these specialized system procedures. Therefore, it is for this reason that the first two options will not be used in this tutorial, though they are indeed worth mentioning and perhaps even explored in future articles.
For the purpose of validating what we shall call the "generation target", we will be creating a class that will inherit CoreBase directly. Indirectly however, it is worth mentioning before hand that every class within the CodeGenerator.Core assembly will directly or indirectly inherit CoreBase; with the notable exception of LibraryConstans. Add a class to the project and call this class DataSourceValidator and have this class inherit from CoreBase. Add the following code to the class:
using System;
using System.Data;
using System.Data.SqlClient;
namespace CodeGenerator.Core
{
/// <summary>
/// DataSourceValidator class
/// </summary>
public class DataSourceValidator : CoreBase
{
#region Constructor
/// <summary>
/// Default constructor
/// </summary>
public DataSourceValidator() { }
#endregion
#region Utility Methods
/// <summary>
/// Indicates whether a data store is valid
/// </summary>
/// <param name="serverInstance"></param>
/// <param name="databaseName"></param>
/// <returns></returns>
public bool IsValidTarget()
{
// Allocate default return value
bool retVal = false;
try
{
retVal = ServerIsValid() && DatabaseIsValid();
}
catch
{
// Do nothing - Simply return default value
}
return retVal;
}
/// <summary>
/// Indicates whether the server instance is valid or not
/// </summary>
/// <returns></returns>
private bool ServerIsValid()
{
// Allocate default return value
bool retVal = false;
// Begin server validation process
using (DataSet availableServers = new DataSet("AvailableServers"))
{
// Connect to the master database
using (SqlConnection connection = new SqlConnection(MasterDatabaseConnectionString))
{
// Fetch all servers
using (SqlCommand retrieveServers = new SqlCommand(LibraryConstants.HELP_SERVER_SPROC, connection))
{
// Set up data adapter
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveServers))
{
// Set command type and fill the dataset
retrieveServers.CommandType = CommandType.StoredProcedure;
adapter.Fill(availableServers);
// Begin server instance validation
if (availableServers.Tables.Count > 0)
{
// Get rows that match the instance name
DataRow[] rows = availableServers.Tables[0].Select(string.Format("name='{0}'", SqlServerInstance));
// Set return value
if (rows.Length > 0)
retVal = rows[0]["name"].ToString().ToLower().Equals(SqlServerInstance.ToLower());
}
}
}
}
}
return retVal;
}
/// <summary>
/// Indicates whether the database is valid or not
/// </summary>
/// <returns></returns>
private bool DatabaseIsValid()
{
// Allocate default return value
bool retVal = false;
// Begin database validation process
using (DataSet availableDatabases = new DataSet("AvailableDatabases"))
{
// Connect to the master database
using (SqlConnection connection = new SqlConnection(MasterDatabaseConnectionString))
{
// Fetch all databases
using (SqlCommand retrieveDatabases = new SqlCommand(LibraryConstants.INSTANCE_DATABASES_SPROC, connection))
{
// Set up data adapter
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveDatabases))
{
// Set command type and fill the dataset
retrieveDatabases.CommandType = CommandType.StoredProcedure;
adapter.Fill(availableDatabases);
// Begin server instance validation
if (availableDatabases.Tables.Count > 0)
{
// Get rows that match the instance name
DataRow[] rows = availableDatabases.Tables[0].Select(string.Format("DATABASE_NAME='{0}'", TargetDatabase));
// Set return value
if (rows.Length > 0)
retVal = rows[0]["DATABASE_NAME"].ToString().ToLower().Equals(TargetDatabase.ToLower());
}
}
}
}
}
return retVal;
}
#endregion
}
}
As seen in the code to this point, the DataSourceValidator class consists of three methods. Two private methods: DatabaseIsValid() and ServerIsValid(); are wrapped inside of the public method IsValidTarget() which will return the combined validation result of the forementioned methods. Again notice how these methods make use of the LibraryConstants constants in order to minimize the usage of "magic strings" throughout our code. Additionally, these methods make use of system procedures to retrieve SQL Server instances and databases in order to validate our future entries in the App.config file later on and do provide a template of sorts of what our calls will look like against the generation target.
The next step will be to create another abstract class named TargetSchemaInfo that will also inherit from CoreBase to our project. This class will contain some very important pieces that will be used by a derived class named MetadataGenerator for the collection of database tables, child tables, columns, primary key columns, connections to the generation target, etc. Once the class has been added and made to inherit from CoreBase, add the following code to it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace CodeGenerator.Core
{
/// <summary>
/// TargetSchemaInfo class
/// </summary>
public abstract class TargetSchemaInfo : CoreBase
{
#region Properties
/// <summary>
/// Gets/Sets Connection
/// </summary>
protected SqlConnection Connection { get; set; }
#endregion
#region Constructor
/// <summary>
/// Default constructor
/// </summary>
public TargetSchemaInfo() : base()
{
// Initialize object
Initialize();
}
#endregion
#region Utility Methods
/// <summary>
/// Handles class initialization logic
/// </summary>
private void Initialize()
{
// Create validator
DataSourceValidator validator = new DataSourceValidator();
if (validator.IsValidTarget())
{
// Initialize and open connection to the target database
Connection = new SqlConnection(TargetDatabaseConnectionString);
Connection.Open();
}
}
/// <summary>
/// Retrieves a list of database tables in the target database
/// </summary>
/// <returns></returns>
protected List<string> DatabaseTables()
{
// Pre-allocate return value
List<string> retVal = new List<string>();
// Begin process of fetching database tables
using (DataSet availableTables = new DataSet("AvailableTables"))
{
using (SqlCommand retrieveTables = new SqlCommand(LibraryConstants.DATABASE_TABLES_SPROC, Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveTables))
{
// Set command type and fill dataset
retrieveTables.CommandType = CommandType.StoredProcedure;
adapter.Fill(availableTables);
// Filter out un-wanted tables such as system and information schema tables.
List<string> temp = new List<string>();
foreach (DataRow row in availableTables.Tables[0].Select("TABLE_TYPE='TABLE' and TABLE_OWNER <> 'sys' AND TABLE_OWNER <> 'INFORMATION_SCHEMA'"))
temp.Add(row["TABLE_NAME"].ToString());
// Get valid table names
retVal = (from x in temp
where !x.Contains("sysdiagrams")
select x).ToList();
}
}
}
return retVal;
}
/// <summary>
/// Retrieves a list of child tables
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
protected List<string> ChildTables(string parentTableName)
{
// Pre-allocate return value
List<string> retVal = new List<string>();
// Begin process of fetching database tables
using (DataSet availableTables = new DataSet("AvailableTables"))
{
using (SqlCommand retrieveTables = new SqlCommand(LibraryConstants.F_KEYS_SPROC, Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveTables))
{
// Set command type and fill dataset
retrieveTables.CommandType = CommandType.StoredProcedure;
retrieveTables.Parameters.AddWithValue("@pktable_name", parentTableName);
adapter.Fill(availableTables);
// Filter out un-wanted tables such as system and information schema tables.
foreach (DataRow row in availableTables.Tables[0].Rows)
{
if (row["fkcolumn_name"].ToString().Contains(parentTableName))
retVal.Add(row["fktable_name"].ToString());
}
}
}
}
return retVal;
}
/// <summary>
/// Retrieves a list of columns from a database table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
protected DataTable Columns(string tableName)
{
// Pre-allocate return value
DataTable retVal = new DataTable();
// Begin process of fetching table columns
using (DataSet availableColumns = new DataSet("AvailableColumns"))
{
using (SqlCommand retrieveColumns = new SqlCommand(LibraryConstants.COLUMNS_SPROC, Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveColumns))
{
// Set command type and fill dataset
retrieveColumns.CommandType = CommandType.StoredProcedure;
retrieveColumns.Parameters.AddWithValue("@table_name", tableName);
adapter.Fill(availableColumns);
// Set return value
if (availableColumns.Tables.Count > 0)
retVal = availableColumns.Tables[0];
}
}
}
return retVal;
}
/// <summary>
/// Returns the promary key status of a given column
/// </summary>
/// <param name="tableName"></param>
/// <param name="columnName"></param>
/// <returns></returns>
protected bool IsPrimaryKeyColumnName(string tableName, string columnName)
{
// Pre-allocate return value
bool retVal = false;
// Begin process of fetching table primary key status
using (DataSet availableKeys = new DataSet())
{
using (SqlCommand retrieveKey = new SqlCommand(LibraryConstants.P_KEYS_SPROC, Connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(retrieveKey))
{
// Set command type and fill dataset
retrieveKey.CommandType = CommandType.StoredProcedure;
retrieveKey.Parameters.AddWithValue("@table_name", tableName);
adapter.Fill(availableKeys);
if (availableKeys.Tables.Count > 0)
{
if (availableKeys.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in availableKeys.Tables[0].Rows)
{
if (availableKeys.Tables[0].Rows[0]["COLUMN_NAME"].ToString().Equals(columnName))
{
retVal = true;
break;
}
}
}
}
}
}
}
return retVal;
}
#endregion
}
}
As seen by the code in the TargetSchemaInfo class, all data access methods are fairly similar with the exception that they make use of different system procedure calls. It is noted that due to the similarity of these methods, these methods could be combined and simply passed the LibraryConstans constant that they should use for whatever stored procedure they should be using. For demonstration purposes however, these methods have been split so as to clearly demonstrate each operation as clearly as possible.
The last step before we complete the CodeGenerator.Core assembly is to add the one class that will feed XML formatted metadata to the code generator. Prior to creating this class, it seems appropriate, without getting into too much detail, show the XML schema of the generated XML. This will give us an ideal view of how we need to generate our metadata, step by step. The following is an extract of the XML metadata we will be producing:
<?xml version="1.0" encoding="utf-8" ?> <metadata> <project server="POSEIDON" database="CodeGeneration" created="5/28/2009" objectnamespace="CodeGeneration" sqlcodeobjectprefix="codegeneration" /> <personalization includepersonaldata="true"> <developername>Ramon E. Tristani</developername> <company>PCSWebLabs</company> <position>Software Architect</position> <developerurl>http://www.pcsweblabs.com</developerurl> <copyright>This stuff is copyrighted by the powers of Hello World and foo vars!</copyright> </personalization> <databasetables count="3"> <table name="Gender" type="table" haschildren="true" objectindex="1"> <columns columncount="6"> <column name="GenderID" datatype="int identity" length="10" primarykeystatus="true" nullable="true" columnindex="0" defaultvalue="nodefault" /> <column name="Type" datatype="nvarchar" length="50" primarykeystatus="false" nullable="true" columnindex="1" defaultvalue="nodefault" /> <column name="Created" datatype="datetime" length="23" primarykeystatus="false" nullable="true" columnindex="2" defaultvalue="getdate" /> <column name="Updated" datatype="datetime" length="23" primarykeystatus="false" nullable="true" columnindex="3" defaultvalue="getdate" /> <column name="IsActive" datatype="bit" length="1" primarykeystatus="false" nullable="true" columnindex="4" defaultvalue="1" /> <column name="Description" datatype="nvarchar" length="512" primarykeystatus="false" nullable="true" columnindex="5" defaultvalue="nodefault" /> </columns> <childtables> <childtable name="Person" type="table"> <columns columncount="8"> <column name="PersonID" datatype="int identity" length="10" primarykeystatus="true" nullable="true" columnindex="0" defaultvalue="nodefault" /> <column name="GenderID" datatype="int" length="10" primarykeystatus="false" nullable="true" columnindex="1" defaultvalue="nodefault" /> <column name="FullName" datatype="nvarchar" length="50" primarykeystatus="false" nullable="true" columnindex="2" defaultvalue="nodefault" /> <column name="Age" datatype="int" length="10" primarykeystatus="false" nullable="true" columnindex="3" defaultvalue="nodefault" /> <column name="Created" datatype="datetime" length="23" primarykeystatus="false" nullable="true" columnindex="4" defaultvalue="getdate" /> <column name="Updated" datatype="datetime" length="23" primarykeystatus="false" nullable="true" columnindex="5" defaultvalue="getdate" /> <column name="IsActive" datatype="bit" length="1" primarykeystatus="false" nullable="true" columnindex="6" defaultvalue="1" /> <column name="Description" datatype="nvarchar" length="512" primarykeystatus="false" nullable="true" columnindex="7" defaultvalue="nodefault" /> </columns> </childtable> </childtables> </table> </databasetables> </metadata>
As you can see, with what we have so far we can get at a lot of information from our generation target without even using a hint of SQLDMO or SMO. We do this by way of the MetadataGenerator class. This class is responsible for creating the entire XML content we will need in future phases of our project. Without further delay, add the MetadataGenerator class to the project and have this new class inherit from our previous class TargetShemaInfo. Add the following code to this class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml.Linq;
using System.Collections;
namespace CodeGenerator.Core
{
/// <summary>
/// MetadataGenerator class
/// </summary>
public class MetadataGenerator : TargetSchemaInfo
{
#region Constructors
/// <summary>
/// Default constructor
/// </summary>
public MetadataGenerator() : base() { }
#endregion
#region Utility Methods
/// <summary>
/// Returns a basic metadata XML document
/// </summary>
/// <param name="tables"></param>
/// <returns></returns>
private XElement CreateBasicDocument(List<string> tables)
{
return new XElement("metadata",
new XElement("project",
new XAttribute("server", SqlServerInstance),
new XAttribute("database", TargetDatabase),
new XAttribute("created", DateTime.Now.ToShortDateString()),
new XAttribute("objectnamespace", TargetDatabase),
new XAttribute("sqlcodeobjectprefix", TargetDatabase.ToLower())),
new XElement("personalization",
new XAttribute("includepersonaldata", IncludePersonalization),
new XElement("developername", DeveloperName),
new XElement("company", Company),
new XElement("position", Position),
new XElement("developerurl", DeveloperUrl),
new XElement("copyright", Copyright)),
new XElement("databasetables",
new XAttribute("count", tables.Count.ToString())));
}
/// <summary>
/// Creates a columns XML element
/// </summary>
/// <param name="tables"></param>
/// <param name="i"></param>
/// <param name="tableColumns"></param>
/// <returns></returns>
private XElement CreateColumnsElement(string tableName, DataTable tableColumns)
{
XElement columnsElement = new XElement("columns",
new XAttribute("columncount", tableColumns.Rows.Count.ToString()));
foreach (DataRow row in tableColumns.Rows)
{
if (!(row["TYPE_NAME"].ToString().Equals("timestamp")))
{
columnsElement.Add(new XElement("column",
new XAttribute("name", row["COLUMN_NAME"].ToString()),
new XAttribute("datatype", row["TYPE_NAME"].ToString()),
new XAttribute("length", row["PRECISION"].ToString()),
new XAttribute("primarykeystatus", IsPrimaryKeyColumnName(tableName, row["COLUMN_NAME"].ToString()) ? "true" : "false"),
new XAttribute("nullable", row["NULLABLE"].ToString().Equals(0) ? "false" : "true"),
new XAttribute("columnindex", (int.Parse(row["ORDINAL_POSITION"].ToString()) - 1).ToString()),
new XAttribute("defaultvalue", row["COLUMN_DEF"].Equals(DBNull.Value) ? "nodefault" :
row["COLUMN_DEF"].ToString().Replace("(", string.Empty).Replace(")", string.Empty).Replace("'", string.Empty))));
}
}
return columnsElement;
}
/// <summary>
/// Creates a child tables element
/// </summary>
/// <param name="childTables"></param>
/// <param name="columnsElement"></param>
/// <returns></returns>
private XElement CreateChildTablesElement(List<string> childTables, ref XElement columnsElement)
{
XElement childTablesElement = new XElement("childtables");
if (childTables.Count > 0)
{
foreach (string childTable in childTables)
{
// Create child table node
XElement childTableElement = new XElement("childtable",
new XAttribute("name", childTable),
new XAttribute("type", "table"));
// Parse columns
columnsElement = CreateColumnsElement(childTable, Columns(childTable));
// Add columns to child table element
childTableElement.Add(columnsElement);
// Add child table to child tables element
childTablesElement.Add(childTableElement);
}
}
return childTablesElement;
}
/// <summary>
/// Returns an XML metadata representation of the target database
/// </summary>
/// <returns></returns>
public string GetMetadataXml()
{
// Pre-allocate return value
string retVal = string.Empty;
if (Connection.State.Equals(ConnectionState.Open))
{
// Fetch tables
List<string> tables = DatabaseTables();
// Process tables
if (tables.Count > 0)
{
// Create basic document
XElement element = CreateBasicDocument(tables);
// Create document from current XML data
XDocument doc = new XDocument(element);
// Reference the "databasetables" node as this node will be heavily modified
XElement databaseTables = doc.Root.Element("databasetables");
for (int i = 0; i < tables.Count; i++)
{
// Retrieve child tables
List<string> childTables = ChildTables(tables[i]);
XElement tableElement = new XElement("table");
tableElement.Add(new XAttribute("name", tables[i]));
tableElement.Add(new XAttribute("type", "table"));
tableElement.Add(new XAttribute("haschildren", childTables.Count > 0 ? "true" : "false"));
tableElement.Add(new XAttribute("objectindex", i.ToString()));
#region Load Columns
// Parse out table columns
XElement columnsElement = CreateColumnsElement(tables[i], Columns(tables[i]));
// Add columns to table element
tableElement.Add(columnsElement);
#endregion
// Load child tables
tableElement.Add(CreateChildTablesElement(childTables, ref columnsElement));
// Add table element to database tables container node
databaseTables.Add(tableElement);
}
// Set return XML
retVal = element.ToString();
}
// Close connection to the database
Connection.Close();
}
return retVal;
}
#endregion
}
}
The one thing to note with this class is that LINQ to XML is used exclusively for the creation of what will be our XML formatted metadata output. Additionally, having followed a clear inheritance path, we have managed to implement an object with the sole purpose or generating metadata from the generation target.Having worked on similar objects that use the XML DOM objects such as XMLWriter objects, I can attest that our code would have been 4 times as long than it would by using LINQ to XML as we have done so here. At this point, our CodeGenerator.Core assembly is complete for all effective purposes of this tutorial and should look like the following diagram:
Code Generation Core Class Diagram
The Code Generator - Testing the Metadata
At this point, we are ready to test our assembly output and we will do so by adding a new Console Application project to the Visual Studio solution called CodeGenerator. After creating the project, two things need to happen and those are:
- Add a reference to the CodeGenerator.Core project
- Add the App.config file to the new project
While the structure of the App.config file should be clear from what we have seen in the CoreBase class, the following configuration presents a sample of what this file must look like:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <!-- TARGET INFORMATION --> <add key="SqlServerInstance" value="YOUR_SERVER_INSTANCE" /> <add key="TargetDatabase" value="CodeGeneration" /> <!-- PERSONALIZATION --> <add key="IncludePersonalization" value="true" /> <add key="DeveloperName" value="YOUR_NAME" /> <add key="Company" value="YOUR_COMPANY" /> <add key="Position" value="YOUR_POSITION" /> <add key="DeveloperUrl" value="YOUR_URL" /> <add key="Copyright" value="YOUR_COPYRIGHT_INFO" /> </appSettings> <connectionStrings> <add name="MasterDatabaseConnectionString" connectionString="Data Source=YOUR_SERVER_INSTANCE;Initial Catalog=master;Integrated Security=True" providerName="System.Data.SqlClient" /> <add name="CodeGeneration" connectionString="Data Source=YOUR_SERVER_INSTANCE;Initial Catalog=CodeGeneration;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration>
The only thing that remains to be done to this configuration is to plug in your own values where the value tokens have been placed. Next, we need to edit the Program class of the CodeGenerator project in order to make use of the CodeGenerator.Core assembly's new metadata extraction capabilities. To do so, add the following code to your Program class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeGenerator.Core;
namespace CodeGenerator
{
class Program
{
static void Main(string[] args)
{
//
MetadataGenerator meta = new MetadataGenerator();
Console.WriteLine(meta.GetMetadataXml());
// Hang the console
Console.Read();
}
}
}
Go ahead, build and take the application for a spin! After doing so, if all goes well, you should be able to see a console window, complete with XML metadata output as seen in the following screen capture:
Metadata Output
The output basically is a complete regurgitation of metadatadata to the screen. As such, this step concludes this tutorial
Conclusion
In this tutorial we have explored the key concepts of metadata extraction from SQL Server for the purpose of creating the building blocks of a code generation utility. While exploring these concepts, we have gained exposure on how to extract database information from SQL Server using system procedures, how to create XML documents with LINQ to SQL, and how to bring these concepts together. Future articles on code generation will build on the concepts and projects covered in this article all the way to the completion of your very own code generator.
Get the source code for this article.
