I’ve spoken about SSMS Boost in this post and now I’m going to apply one of its feature in order to improve the productivity of my team. Speaking about the database side, a table that contains static data can be used as a foreign key referenced table. In my experience those kind of tables are often mapped to enums in our application layer. Creating that enum can be a tedious operation and sometimes it can be very uncomfortable. With SSMS Boost we can enhance our development experience.

Scenario

We have a static table on a SQL Server database with this structure and sample data:

USE tempdb;
GO 

CREATE TABLE dbo.StaticData
(
   Code tinyint
 , Value varchar(30)
 , PRIMARY KEY
 (
   Code
 )
);
GO

INSERT INTO dbo.StaticData (Code, Value) VALUES (0, 'Unknown');
INSERT INTO dbo.StaticData (Code, Value) VALUES (1, 'Enabled');
INSERT INTO dbo.StaticData (Code, Value) VALUES (2, 'Disabled');
INSERT INTO dbo.StaticData (Code, Value) VALUES (3, 'Confirmed');
INSERT INTO dbo.StaticData (Code, Value) VALUES (4, 'Deleted');
INSERT INTO dbo.StaticData (Code, Value) VALUES (5, 'Replied');
INSERT INTO dbo.StaticData (Code, Value) VALUES (6, 'Asked');
INSERT INTO dbo.StaticData (Code, Value) VALUES (7, 'ToBeAsked');
INSERT INTO dbo.StaticData (Code, Value) VALUES (8, 'ToBeConfirmed');
INSERT INTO dbo.StaticData (Code, Value) VALUES (9, 'ToBeReplied');
GO

Suppose that we want to retrieve the C# enum syntax, like the following one:

public enum Something
{
  option1 = 0,
  option2 = 1,
  optionx = 3
}

Creating a new template

In the SSMS Boost Extras tool bar there is a Settings button. Press it:

1. Choose the ResultsGrid: Scripter Templates tab
2. Then, create a new template with the plus button (name: “C# Enum“)
3. A new template is created

Now it’s time to design our template. Let’s focus on Script Template section of the property grid.

Set the template parameters

First of all we need to understand what we really need for scripting an enum:

• type (optional, int by default) and the access modifier (we will use public)
• name (this the first dynamic value, it can change for every script)
• elements, in the NameWithoutSpaces = IntValue form
• additional constant strings (like comments, brackets)

The enum type will be the default. The enum name will be a SQL Server Template placeholder. It is a simple <parameter name, parameter type, default value> string. For more information about template syntax, read this link. We’ll use <enum_name, string, myEnum>. About the elements, we need to get every row in the resultset(s) we want to template. SSMS Boost gives us a set of useful scoped placeholders. We will use the {Rows} one, which gets the list of rows of the grid we’re scripting. As we say, the parameter is scoped. This means that it is available on the properties which supports the rows management. One of that property is the “Selection” one: As we can see, we’ve created the new template using the c# enum common syntax and the SQL Server placeholders for template. We need those placeholders in order to rename the enum after or before the composition of the template (pressing Ctrl-Shift-M for replacing parameters in case of the lazy edit). Let’s have a look on the “Selection” property value:

public enum <enum_name, string, myEnum> //SQL Server template parameter
{
  {Rows} //SSMS Boost built-in parameter
}

As written in the plugin documentation, we can use also {SelectionN} (position number of the resultset which we’re scripting in case of multi grid script) or {HeadersRow} (the list of columns, if specified in the ColumnName property). How can we format each row during the template render? Setting the row template: {Values} is the list of {Value} data and ValueSeparator is the character used to split each {Value}. We set the ValueSeparator property to ” = ” (space-equal-space), and Row property to “{Values},” (placeholder-comma) in order to get the pattern “value1 = value2 = value3 = valuex … ,”. ColumnName, ColumnNamesSeparator, Header and HeadersSeparator should be blank (it is not mandatory since the Selection property is not using columns). The Document property must be set to {Selections}. The last property which sould be considered in this example is the Replace Template Parameters BEFORE Scripting property. It indicates whether the SQL Server template parameters will be replaced before creating the template (with prompt)  or manually after the template is created. We set it to True, so we will be prompted for the name of the enum.

Source query and additional considerations

We will script a resultset. In this example we need to write down a query for our sample table. Since the pattern is “value1 = value2 = value3 = valuex … ,” we can write a query which returns just value1 and value2, like:

SELECT
    Value --> This should be the first field (enum item name part)
  , Code
FROM
  dbo.StaticData;

The Code field, which is the column which contains the int part of the enum item, is the second one in the select list. The Value field should not contain any white space (or brackets, slash, dash, etc.) since it is not supported in the enum item name. In some cases you’ll need to REPLACE special chars with another one (i.e. an underscore). The number of the fields into the select list must be obviously two 🙂

Run the template

Let’s run the template with a right click on the result grid of the query described above: With the Replace Template Parameters BEFORE Scripting property set to True, we will be prompted for the enum name (otherwise we have to replace the parameters pressing CRTL-Shift-M): Paste the code after generation and then, the cute result:

public enum myEnum
{
  Unknown = 0,
  Enabled = 1,
  Disabled = 2,
  Confirmed = 3,
  Deleted = 4,
  Replied = 5,
  Asked = 6,
  ToBeAsked = 7,
  ToBeConfirmed = 8,
  ToBeReplied = 9,
}

 

Conclusions

That’s it! We’ve just created a simple C# enum template. However I encourage you to try to find out additional useful templates that you can use and share with us. A c# enum is mostly useful when a project starts, it’s not a daily needs for sure. There are lots of built-in templates within SSMS Boost and you can customize it or simply create new ones. You can import the C# Enum template to your SSMS Boost copying the following XML and pasting it with the “Import from clipboard” button:

<!--?xml version="1.0" encoding="utf-16"?-->
  
    C# Enum
    1
    
    
    {Selections}
    public enum <enum_name, string, myEnum>
    {
      {Rows}
    }
    
    \r\n-------------------\r\n
    {Values},
    \r\n
    {Value}
     = 
    
    
      D
    
    
      G
    
    
      G
    
    
      yyyy-MM-dd HH:mm:ss.fff
    
    
      yyyy-MM-dd
    
    
      HH:mm:ss.fff
    
    
    
    
    
    
    true
    true
  

The buttons (also the import from file one) below:   Stay Tuned! 😉