Coding Snippets in SSMS (Microsoft SQL Server)

Hi all,

Continuing on with the idea of coding snippets, here’s a follow up showing the (very similar) procedure for creating snippets in SQL Server Management Studio (SSMS).

As before, you’ll need a file with the ‘.snippet’ extension to begin, which contains a snippet in an XML format. A basic example is shown below for reference, encapsulating some code that can quickly search a Stored Procedure definition for a particular search term:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
		<Header>
		<Title>Sys.procedures SQL Check</Title>
			<Shortcut></Shortcut>
		<Description>Creates SQL to check sys.procedures for a literal string.</Description>
		<Author>Lewis Grint</Author>
		<SnippetTypes>
			<SnippetType>Expansion</SnippetType>
		</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
				<Literal>
					<ID>SearchTerm</ID>
					<ToolTip>The term to search for.</ToolTip>
					<Default>SearchTerm</Default>
				</Literal>
			</Declarations>
			<Code Language="SQL">
				<![CDATA[--Check SP content for a literal string
SELECT 
name
FROM sys.procedures 
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%$SearchTerm$%';
	]]>
			</Code>
		</Snippet>
    </CodeSnippet>
</CodeSnippets>

As you can see, this file takes the familiar ‘.snippet’ format, supporting Snippet, Code and Declarations elements. These include the code snippet itself, and placeholder definitions, as before. You will also find the Header element, where pertinent information about the snippet can be stored, such as author and description.

Easily recognisable territory so far, I’ll think you’ll agree.

Within SSMS, much as in Visual Studio, navigate to Tools > Code Snippets Manager (Ctrl + K, Ctrl + B) to get underway:

Image showing how to access the Code Snippets Manager.

Access Code Snippets Manager.

Again, pretty much plain sailing from here on in. You’ll have the same options as in Visual Studio to import a snippet file (just use the Import button):

Image showing the Code Snippets Manager before Import.

Code Snippets Manager before Import.

Navigate to your snippet file and select it. You’ll now have the opportunity to map the snippet to a nominated folder. In this instance, we’ll use the My Code Snippets folder. Click Finish to complete the process.

Image showing the MSSQL Server Code Snippets Manager Import Dialog.

SSMS Code Snippets Manager Import Dialog.

You should then be able to see your snippet under the nominated folder:

Image showing the Code Snippets Manager after Import.

Code Snippets Manager after Import.

That’s it, you’ve imported the snippet! You can now open a new query and use Ctrl + K, Ctrl + X to launch the Code Snippet Picker, whereby you can use the arrow keys to move between folder and snippets:

Image showing a Snippet before being expanded.

Snippet before Expansion.

Hit Enter to select your snippet; you should then see the following:

Image showing a Snippet after being expanded.

Snippet after Expansion.

Then you can start using your code, utilising tab to cycle through any placeholders. Another trick, that I’ve used, is to Add an entire folder of snippets using the Code Snippets Manager (instead of using Import). Using a ‘tactically’ named folder, you can gain faster access to your snippets:

Shows the results of adding a Custom Snippets Folder.

Adding a Custom Snippets Folder.

This type of snippet is an Expansion snippet. You can write other kinds, such as the Surrounds With snippet, an example of which can be found here on MSDN, illustrating a try/catch construct:

Surrounds With Snippet Example

I hope this has been helpful. Please comment below if you have any thoughts!

Cheerio!

Code Snippets in Visual Studio (C#)

Hi all,

This post forms part of a mini-series that I’d like to do surrounding the topic of code snippets. I have, for some time, been using these merrily within Microsoft SQL Server (which I’ll cover) but have neglected to look at creating custom snippets in every other environment I am privy to. Time to put that to a stop :-).

The following is a small demonstration of how to knock up a simple, custom code snippet in C#. Along the way, I’ll discuss a known limitation of creating snippets for this language specifically (as opposed to Visual Basic, for example). With any luck, this will get you to investigate the topic if you haven’t already done so.

Creating Custom Code Snippets

If you’re looking for a simple introduction, this MSDN article is a great place to head:

Walkthrough: Creating a Code Snippet

Once you’re primed and at the start line you should be pushing out snippets in no time.

You’ll need to craft your own file with the ‘.snippet’ extension to begin, where the content takes the form of standard XML. The one I have built for this example is below; I’ll discuss it as we move forward.

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets
    xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <CodeSnippet Format="1.0.0">
		<Header>
			<Title>INotifyPropertyChanged Full Definition Snippet</Title>
			<Author>Lewis Grint</Author>
			<Description>Generates a stub class (including namespace/using statements) implementing INotifyPropertyChanged fully.</Description>
			<Shortcut>inotifyfull</Shortcut>
		</Header>
        <Snippet>
			<Declarations>
			    <Literal>
					<ID>NamespaceName</ID>
					<ToolTip>Replace with the namespace.</ToolTip>
					<Default>NamespaceName</Default>
				</Literal>
			    <Literal>
					<ID>ClassName</ID>
					<ToolTip>Replace with the class name.</ToolTip>
					<Default>ClassName</Default>
				</Literal>				
			</Declarations>
            <Code Language="CSharp">
                <![CDATA[using System.ComponentModel;
using System.Runtime.CompilerServices;

namespace $NamespaceName$
{
    public class $ClassName$ : INotifyPropertyChanged
    {
        #region INotifyPropertyChanged Interface Support

        /// <summary>
        /// PropertyChangedEventHandler for INotifyPropertyChanged support.
        /// </summary>
        public event PropertyChangedEventHandler PropertyChanged;

        /// <summary>
        /// Protected virtual method that should be called when a
        /// supporting properties value in changed (for binding to 
        /// work correctly in the application).
        /// </summary>
        /// <param name="propertyName">The property that has been altered.</param>
        protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = "")
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion INotifyPropertyChanged Interface Support
    }
}]]>
            </Code>
        </Snippet>
    </CodeSnippet>	
    <CodeSnippet Format="1.0.0">
		<Header>
			<Title>INotifyPropertyChanged Class Snippet</Title>
			<Author>Lewis Grint</Author>
			<Description>Generates a stub class implementing INotifyPropertyChanged.</Description>
			<Shortcut>inotify</Shortcut>
		</Header>
        <Snippet>
			<Declarations>
				<Literal>
					<ID>ClassName</ID>
					<ToolTip>Replace with the class name.</ToolTip>
					<Default>ClassName</Default>
				</Literal>
			</Declarations>
            <Code Language="CSharp">
                <![CDATA[    public class $ClassName$ : INotifyPropertyChanged
    {
        #region INotifyPropertyChanged Interface Support

        /// <summary>
        /// PropertyChangedEventHandler for INotifyPropertyChanged support.
        /// </summary>
        public event PropertyChangedEventHandler PropertyChanged;

        /// <summary>
        /// Protected virtual method that should be called when a
        /// supporting properties value in changed (for binding to 
        /// work correctly in the application).
        /// </summary>
        /// <param name="propertyName">The property that has been altered.</param>
        protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = "")
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        #endregion INotifyPropertyChanged Interface Support
    }]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

After a wrapping CodeSnippets element, you construct a CodeSnippet containing a Header and Snippet element, at a bare minimum. Within the Header you are able to express metadata relating to the snippet, such as the Title and Author, alongside the Description (which shows as an IntelliSense tooltip on use) and the actual Shortcut value associated with bringing the snippet into scope, which is typed directly into the IDE.

The Snippet element contains a Code element and is, as you would expect, where you place your physical code ‘slice of goodness’. It’s important to place your code within the square brackets of the CDATA declaration.

A smaller, valid C# snippet, for example could look like:

</CodeSnippet>
    <CodeSnippet Format="1.0.0">
    <Header>
        <Title>MessageBox Show Snippet</Title>
        <Author>Lewis Grint</Author>
        <Description>Generates a full MessageBox Show call.</Description>
        <Shortcut>mb</Shortcut>
    </Header>
    <Snippet>
        <Code Language="CSharp">
            <![CDATA[MessageBox.Show("Hello World");]]>
        </Code>
    </Snippet>
</CodeSnippet>

Not the most useful example, as within Visual Studio you already have the ‘mbox’ snippet covering this (which uses a fully qualified expression for MessageBox.Show), but illustrates the most basic form of a snippet nicely.

If you want placeholders in your code, that can be accessed and changed sequentially by using tab after you fully bring the snippet into scope within the IDE, you need to create a Declarations element under Snippet. Here you can create a Literal, to be altered by the snippet user on-the-fly, with tooltip and default values to boot. Embed these in your code snippets by copying the ID element value into the code wrapped in ‘$’ symbols (again, as shown above in the more detailed example).

That should be enough to run with, as it stands. We have an idea of how to create snippets, but how do you import them into Visual Studio? Let’s find out…

Importing Custom Code Snippets into Visual Studio

Now you have your snippets file in tow it’s time to rock on over to Visual Studio. You can access the Code Snippets Manager by navigating to Tools > Code Snippets Manager (Ctrl + K, Ctrl + B), as illustrated here:

Image showing how to access the Code Snippets Manager within Visual Studio.

Accessing the Code Snippets Manager.

Once inside the Code Snippets Manager alter the language to the one of your choosing, which is ‘CSharp’ in my case, and click the My Code Snippets folder to select it as the repository for your snippets, before clicking Import to proceed:

Shows the Code Snippets Manager Dialog.

Code Snippets Manager Dialog.

You can now browse to your snippet file and, on selection, will be presented with a confirmation screen that should look like this:

Shows the Code Snippets Manager Import Dialog.

Code Snippets Manager Import Dialog.

Your snippet file will be validated at this stage and you will be notified if the markup is malformed/invalid.

After clicking Finish, you’ll find yourself back in the Code Snippets Manager screen and, with any luck, you should be able to spot your individual snippets under the My Code Snippets directory:

Shows custom snippets in the Code Snippets Manager after import.

Code Snippets Manager After Import.

As easy as pie. The snippets are imported, so let’s use them.

Using Imported Custom Code Snippets

My test bed snippets file provides two snippets for generating a stub class definition, implementing the INotifyPropertyChanged interface (which comes into play when constructing MVVM applications in WPF, for example, so that a client can be notified (and adjust) based on an underlying property change (in a binding scenario)).

Firstly, let’s inspect a partial snippet that generates the class code only, required event object and OnPropertyChanged method definition (just start typing and press tab twice to push the whole snippet to the IDE, whereby you can tab to each placeholder):

Shows a code file before using the partial custom snippet.

Before Partial Snippet Usage.

Shows a code file after using the partial custom snippet.

After Partial Snippet Usage.

You can see that some using statements need to be brought into play to resolve some types here. Frustratingly, Visual Basic supports the concept of including References and Import statements in the snippets XML, whereby C# does not. More details can be found here:

How to: Create a New Snippet with Imports and References

In my scenario, this is an entire stub class so I didn’t have a particular issue with placing the using statements, including the namespace definition, into the code snippet itself (with placeholders):

Shows a code file before using the full custom snippet.

Before Full Snippet Usage.

Shows a code file after using the full custom snippet.

After Full Snippet Usage.

Voila!

I’ve seen some murmurings that this Snippet Editor on Codeplex is worth a look if you are looking for a more robust editor to create your snippets. Support is only listed for Visual Studio 2010 and its version date is listed as 2009, so it doesn’t appear to be that ‘current’. Perhaps a better tool is out there (an extension, or some such); worthy of more investigation:

Codeplex Snippet Editor

That’s all for now, thanks for reading through and I’ll catch you next time!