T4 Succinctly®
CHAPTER 6
Working with SQL Server Metadata
Microsoft.SQLServer.SMO
SMO stands for Server Management Objects. SMO is a collection of objects designed to give you programmatic access to managing all aspects of SQL server. We don’t need all of this power, but the library also provides an easy and straightforward way to access the data dictionary for SQL Server.
Note: Using SMO will tie you directly to SQL Server. If you’re using a different RDMS, you can still retrieve this metadata by querying the tables in your database’s data dictionary directly. Refer to your RDMS documentation.
As long as you opted to install the Client Tools SDL when you installed SQL Server, you already have SMO installed. If you did not, then you can download and install the SQL Server Feature Pack. Make sure to install the correct version for your version of SQL Server.
Throughout this chapter we will build up the functionality for a new class named SQLHelper in the T4Utilities project that we started in Chapter 4. We have a few new references that we will need to add to the T4Utilities project:
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
You will also need to add references to these assemblies for any template that will use our SQL Helper
Connect to a Server
The first step in dealing with SMO is to connect to the server. Fortunately, this is very straightforward. For the most part, from a code generation perspective, you will generally be connecting to your local server.
Let’s add a constructor to the SQLHelper object that will accept the name of the server to connect to, and will initialize a connection to this server.
private Server server {get;set;} public SQLHelper(string serverName) { server = new Server(serverName); } |
Constructor for SQLHelper
Connect to a Database
Now that we are connected to a server, you want to get access to the databases on that server. Often we want to connect to a particular database.
public Database GetDatabase(string whichDatabase) { if (!GetAllDatabases() .Any(d => d.Name == whichDatabase)) throw new Exception(whichDatabase + "was not found on the server"); var database = new Database(server, whichDatabase); database.Refresh(); return database; } public IList<Database> GetAllDatabases() { var returnValue = new List<Database>(); foreach (Database db in server.Databases) { returnValue.Add(db); } return returnValue; } |
GetAllDatabases or Get a Specific Database
Here we add in an extra check to ensure that the database requested actually exists on the server.
Get a List of Tables
Once we have access to an existing database, we will often need to get a list of tables in that database:
public IList<Table> GetAllTables (Database database) { var returnValue = new List<Table>(); foreach (Table currentTable in database.Tables) { returnValue.Add(currentTable); } return returnValue; } |
Get all the Tables in a Database
With this data brought back as a List object, we are able to easily filter this by any of the properties exposed by the table. For our purposes, we will most often be interested in filtering by owner.
public IList<Table> GetAllTablesOwnedBy (Database database, string owner) { return GetAllTables(database) .Where(t => t.Owner == owner).ToList(); } |
Filter Tables by Owner
Get Details about a Table
Now that we can readily get the tables that we are interested in, there are a lot of details about the tables that we may care about. For starters, let’s get a list of the columns in a table:
public IList<Column> GetAllColumns (Table whichTable) { var returnValue = new List<Column>(); foreach (Column currentColumn in whichTable.Columns) { returnValue.Add(currentColumn); } return returnValue; } |
Get All the Columns in a Table
Now we can filter this list of columns by any property exposed by Column, and Column provides some useful properties to filter by:
Column Properties | |
|---|---|
Computed | Boolean value indicated if the column is a computer value |
DataType | Details about the data type for the column |
InPrimaryKey | Boolean value indicating if the column is part of the primary key or a unique constraint |
IsForeignKey | Boolean value indicating if the column is a foreign key |
Name | Gets the name of the column |
Generating Stored Procedures
Now that we have this metadata, we can easily do things like create the stored procedures to encapsulate accessing the tables in the database.
Let’s start by creating a new template, and name it StoreProcedures.tt.
<#@ template debug="true" hostspecific="true" language="C#" #> <#@ output extension=".sql" #> <#@ assembly name="System.Core" #> <#@ import namespace="System.Linq" #> <#@ assembly name= "System.Xml"#> <#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" #> <#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll" #> <#@ assembly name="C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll" #> <#@ import namespace="Microsoft.SqlServer.Management.Smo" #> <#@ assembly name="$(SolutionDir)T4Utilities\bin\Debug\T4Utilities.dll" #> <#@ import namespace = "T4Utilities" #> <# var smo = new SQLHelper("localhost"); var database = smo.GetDatabase("Northwind"); var tables = smo.GetAllTables(database).ToList(); foreach (var table in tables) { GenerateUpdateProcedure(table); GenerateDeleteProcedure(table); } #> |
You may need to adjust the path for the SMO assemblies, depending on how you have them installed. You can get the path based on how the reference was added to the T4 Utilities project. You can also change the name of the parameter to the GetDatabase method to refer to any database that you are interested in.
The heart of the code generation takes place in GenerateUpdateProcedure and GenerateDeleteProcedure. Let’s first think about what an stored update procedure should look like.
In its simplest form, an update procedure would look like this:
CREATE PROCEDURE Region_update @RegionID int, @RegionDescription nchar AS BEGIN
UPDATE Region SET RegionDescription = @RegionDescription WHERE RegionID = @RegionID END GO |
Update Procedure
Your naming conventions may vary, but the general structure will be the same. You want a parameter for every column, and then you will set each column not part of the primary key to the value passed in. You will filter to only update the intended records based on the column that makes up the primary key.
So let’s look at the GenerateUpdateProcedure:
<#+ public void GenerateUpdateProcedure(Table table) { if (table.Columns.Cast<Column>().All(p => p.InPrimaryKey)) return; WriteLine(string.Format("CREATE PROCEDURE {0}_update ", table.Name)); var count = 0; PushIndent(" "); foreach (var column in table.Columns.Cast<Column>()) { if (count > 0) this.WriteLine(","); Write("@" + column.Name + " " + column.DataType); count ++; } PopIndent(); WriteLine(String.Format(@" AS BEGIN UPDATE {0} SET", table.Name)); PushIndent(" "); count = 0; foreach (var column in table.Columns.Cast<Column>() .Where(column => !column.InPrimaryKey)) { if (count > 0) Write(" , "); WriteLine(column.Name + " = @" + column.Name); count ++; } PopIndent(); PushIndent(" "); WriteLine("WHERE"); count = 0; foreach (var column in table.Columns.Cast<Column>() .Where(column => column.InPrimaryKey)) { PushIndent(" "); if (count > 0) Write(" and "); WriteLine(column.Name + " = @" + column.Name); count ++; } PopIndent(); PopIndent(); WriteLine("END"); WriteLine("GO"); WriteLine("\n"); } #>
|
General Update Procedure
The first thing we do is make sure that there is at least one updateable column. If every column is part of the primary key, then we really have nothing to update, and need to just return.
We also want to keep track of how many columns we have processed. When dealing with the parameters, we want to add a comma before each column (except the first one).
For the set clause, we want to include every column that is not part of the primary key, and for the where clause, we want to include every column that is part of the primary key.
The GenerateDeleteProcedure will look similar, with the following exceptions: we only need to include parameters for columns in the primary key, and there is no set clause to worry about.
Using the Northwinds training database, the output would look like this:
CREATE PROCEDURE Categories_update @CategoryID int, @CategoryName nvarchar, @Description ntext, @Picture image AS BEGIN UPDATE Categories SET CategoryName = @CategoryName , Description = @Description , Picture = @Picture WHERE CategoryID = @CategoryID END GO CREATE PROCEDURE Categories_delete @CategoryID int AS BEGIN DELETE FROM Categories WHERE CategoryID = @CategoryID END GO CREATE PROCEDURE Categories_update @CategoryID int, @CategoryName nvarchar, @Description ntext, @Picture image AS BEGIN UPDATE Categories SET CategoryName = @CategoryName , Description = @Description , Picture = @Picture WHERE CategoryID = @CategoryID END GO CREATE PROCEDURE Categories_delete @CategoryID int AS BEGIN DELETE FROM Categories WHERE CategoryID = @CategoryID END GO CREATE PROCEDURE CustomerCustomerDemo_delete @CustomerID nchar, @CustomerTypeID nchar AS BEGIN DELETE FROM CustomerCustomerDemo WHERE CustomerID = @CustomerID AND CustomerTypeID = @CustomerTypeID END GO |
Generate Delete Procedure
You can easily modify the template to follow any naming conventions that you like.
You can also easily follow the same pattern to create and insert stored procedures, as well as select stored procedures by the primary key and every foreign key defined.
Run Arbitrary SQL
Easy access to this metadata is very useful for generating code for a variety of scenarios, but sometimes we also need to run SQL statements. SMO provides an easy way to run SQL and return the results in a DataTable. Any time you run SQL like this, you will need to add an assembly directive to include System.Data.
We can use such SQL statements to access custom metadata that we create.
Let’s consider what domain-specific metadata we could gather from the NorthWinds database. For your reference, here is the data model for this training database:
NorthWind Data Model
Create a new class in the T4Utilities project, and call it NorthWindHelper. We will add a few queries to this class to help automate access some of the metadata.
Let’s start with a simple class to hold a region:
public class Region { public string RegionDescription {get;set;} public int RegionId {get;set;} } |
Simple POCO to Hold Data from the Region Table
Now, to query the Region table and bring back an IList<Region>, all we have to do is this:
public IList<Region> GetAllRegions() { var returnValue = new List<Region>(); var sql = @"Select RegionId, RegionDescription from Region"; var smo = new SQLHelper("localhost"); var db = smo.GetDatabase("NorhWind"); var dataset = db.ExecuteWithResults(sql); foreach (DataTable currentTable in dataset.Tables) foreach (DataRow currentRow in currentTable.Rows) returnValue.Add(new Region() { RegionDescription = (string)currentRow[1], RegionId = (int)currentRow[0] }); return returnValue; } |
Query the Region Table Using SMO
Now we can create a template that will read this data and create a class for each region configured in the database. This might be a precursor to creating a hierarchy of objects to handle region-specific business logic without having to revert to case statements sprinkled throughout the code.
Add a new text template to the book project that we have been working in, and name it Region.tt.
Add the following code to Region.tt:
Region.tt
Here we create new folder in the book project, naming it NorthWind. Then we connect to the NorthWind database and get a list of the regions. We then create a class for each region based on the ClassCreator from Chapter 4, adding properties for RegionName and SalesProjection.
Summary
SQL Server provides a wealth of metadata about the database and the structures in that database. We have seen accessing the databases on the server, the tables in a database, and the columns in a table. We have also seen running any arbitrary SQL that we might need to get more domain specific metadata.
In addition to the metadata that we have explored here, SMO exposes a wealth of additional metadata that can be helpful in generating code. Just as we accessed the tables in a database, you can also access the stored procedures and their parameters.
SMO also makes it relatively painless to automate the task of creating any database structures that you might need, making it easier to keep your code in sync with the databases you are working with.
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.