Sql Server Management Studio: IronPython Extensible add-in

I wanted to write an add-in that helps me with the complicated tasks in SSMS like navigating large procedures or inserting some text like comments.

You can find the download details at the bottom of this document, but first some more info.

The SSMS add-in environment can be a daunting task to figure out so I decided to use my favourite scripting language IronPython to create a framework that I can explore the SSMS environment.



I created a control Sql Navigator:
 

To refresh the navigator press: CTRL+SHIFT+F5
(Can be changed in the script file, can also be hooked up to automatic changing of the active window)

The navigator will parse all query windows and extract the different objects and line locations so you can quickly jump to any of the objects in the document.

Take the following code:

   1: CREATE TABLE #TempTable1(

   2:     Id INT NOT NULL

   3:  

   4: )

   5: CREATE UNIQUE CLUSTERED INDEX IX_Clustered ON #TempTable1

   6:  

   7: -- #region Region1

   8: -- some block of code here

   9: -- #endregion

  10:  

  11: DECLARE @Variable1 INT = 0

This is represented above in the navigator.

Want to add more support for different parts of the query window, edit the file:

c:\Program Files (x86)\ACSR\IronPythonAddIn\Scripts\Expressions.txt
Add the name and expressions:

SelectStatements,SELECT.*?FROM[\s]+[\[]?([#\w\.]+)[\]]?
InsertStatements,INSERT[\s]+INTO[#\s]+([\w\[\]\.]+)

And the SQL:

  SELECT * FROM SomeTable
  INSERT INTO SomeTable ....

I also created additional supportive windows:

A Logger Window.

But what is the most useful is the Interactive Window

But lets skip over it for a moment.

All scripts are in the scripts folder:



To create a new command, create a new script in this directory:

SSMSSampleCommany.py


Locate the Script SSMSAddin.py

Import your script:

   1: try:

   2:     from SSMSSampleCommand import *

   3:     from SSMSCore import *

Find and create your command:

   1: class DefaultAddIn(SqlAddIn):

   2:     def InternalHookEvents(self, Control):

   3:         

   4:         commandBar = self.CreateCommandBar("IronPython")

   5:         self._sqlNavigator = SqlNavigator(self, commandBar)

   6:         FormattingCommands(self, commandBar)

   7:         sampleCommand = SampleCommand(self, commandBar,  "IronPythonSampleCommand", "Sample Command", "Sample Command ToolTip", 13)       

   8:         sampleCommand.GetCommandInstance().Bindings = "Global::ctrl+Shift+F12";   

Code your command:

   1: from SSMSCore import *

   2: from SSMSUtilityFunctions import *

   3: from System.Windows.Forms import *

   4:  

   5: class SampleCommand(CommandHandler):

   6:     def InternalExecute(self, ExecuteOption, VariantIn, VariantOut, Handled):

   7:         print "MyCommand Executed"

   8:         MessageBox.Show("My Command Executed") 

   9:         self._DTE.ExecuteCommand("File.NewQuery") # opens a new query

  10:         self._DTE.ActiveWindow.Selection.Insert("SELECT GETDATE()")

  11:         self._DTE.ExecuteCommand("Query.Execute")

The sample prints that the command was executed



Shows a dialog



The opens a new query




And Executes it.


Also the Add procedure comment command:





The first time:




Outputs the comments:

Now make sure the comment is selected and run it again:




Regular expressions parses the document and picks up the old history.

Add your comment:




Maintains history by appending each comment.


The initials are hard coded, please see the file:


SSMSFormattingCommands.py


If you’d like to change it.

Exploring the environment:

This is one of the exciting parts where you can explore the IDE for objects and method and write test code for your commands or add ins. Or if you just want to manipulate the windows and text.

Here we can explore the SQL Server DTE object get all the methods members and call them in an explorative way.





This is enough for now, in a later post I will describe how I built it.

The installation package including all source code can be downloaded from GIT Hub:


https://github.com/TheWalkingDev/Projects/tree/master/SQL%20Server/Add-Ins

 Click on download, and find the package file:

 

TCP Port Listener

I was creating a DMZ and needed a tool to test my port forwarding I didn’t feel like downloading a program so I created this very simple piece of code to just listen on a port of my choice and print when I can make a connection. Only tests TCP (not UDP).

 

Use it as follows:

PortListener <Port>

   1: class Program

   2:    {

   3:        

   4:        static void Main(string[] args)

   5:        {

   6:            new Program().Run(args);

   7:           

   8:        }

   9:  

  10:        private void Run(string[] args)

  11:        {

  12:            int port = Int32.Parse(args[0]);

  13:            Console.WriteLine("Started listening on port: {0}", port);

  14:            var server = new TcpListener(IPAddress.Any, port);

  15:            server.Start();

  16:            server.BeginAcceptSocket(new AsyncCallback(server_onAccept), server);

  17:  

  18:            Console.WriteLine("Press any key to exit...");

  19:            Console.ReadKey();

  20:        }

  21:  

  22:        void server_onAccept(IAsyncResult ar)

  23:        {

  24:            var server = ((TcpListener)ar.AsyncState);

  25:            var socket = server.EndAcceptSocket(ar);

  26:            

  27:            Console.WriteLine(string.Format("Socket {0} Connected",socket.RemoteEndPoint.ToString()));

  28:            server.BeginAcceptSocket(new AsyncCallback(server_onAccept), server);

  29:        }

  30:    }

 

Download the source here:

 

https://github.com/TheWalkingDev/Projects/tree/master/DevTools/PortListener/PortListener

IPSwitcher: Quickly switch IPAddress Configuration

Are you like me and use different firewalls and configurations depending on what you are doing,

find it irritating to go through the process of changing your IP, gateway or DNS.

Well I put this tool together to help

It sits in the tray Icon, just right click and select your configuration.

Or enter the configuration where you can make changes,

or configure it directly in the config file:

   1: <string>-Name Gaming -AdapterName RTL8167 -IpAddress 10.0.0.65 -Mask 255.255.255.0 -Gateway 10.0.0.25 -DNS 10.0.0.20</string>

   2: <string>-Name Browsing -AdapterName RTL8167 -IpAddress 10.0.0.65 -Mask 255.255.255.0 -Gateway 10.0.0.20 -DNS 10.0.0.20</string>

   3:  

How I built it:

You need System.Management

 C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Management.dll

Here is how to get a list of adapters on the machine:

   1: public IEnumerable<dynamic> GetAdapters()

   2:         {

   3:             ManagementClass objMC = new ManagementClass(

   4:                 "Win32_NetworkAdapterConfiguration");

   5:             ManagementObjectCollection objMOC = objMC.GetInstances();

   6:             foreach (dynamic objMO in objMOC)

   7:             {

   8:  

   9:                 if (objMO["IPEnabled"])

  10:                 {

  11:                     yield return new Adapter(objMO,

  12:                         objMO["Caption"].ToString(),

  13:                         objMO["ServiceName"].ToString());

  14:                 }

  15:             }

  16:         }

 Then update the adapter:

   1: internal void UpdateAdapter()

   2:         {

   3:             foreach (var adapter in GetAdapters())

   4:             {

   5:                 if (adapter.Name.CompareTo(_adapterName) == 0)

   6:                 {

   7:  

   8:                     ManagementBaseObject objNewIP = null;

   9:                     ManagementBaseObject objSetIP = null;

  10:                     ManagementBaseObject objNewGate = null;

  11:                     objNewIP = adapter.ManagementObject.GetMethodParameters("EnableStatic");

  12:                     objNewGate = adapter.ManagementObject.GetMethodParameters("SetGateways");

  13:  

  14:                     objNewGate["DefaultIPGateway"] = new string[] { _Gateway };

  15:                     objNewGate["GatewayCostMetric"] = new int[] { 1 };

  16:                     //Set IPAddress and Subnet Mask

  17:  

  18:                     objNewIP["IPAddress"] = new string[] { _IpAddress };

  19:                     objNewIP["SubnetMask"] = new string[] { _mask };

  20:  

  21:                     objSetIP = adapter.ManagementObject.InvokeMethod("EnableStatic", objNewIP, null);

  22:                     objSetIP = adapter.ManagementObject.InvokeMethod("SetGateways", objNewGate, null);

  23:                       

  24:                 }

  25:             }

  26:         }

The complete project with source is available here:

https://github.com/TheWalkingDev/Projects/tree/master/Networking/IPSwitcher

PyRun: An advanced process runner with IronPython extension

I have been using mercurial quite a bit for source control, in this article Mercurial is my motivation to do this, Mercurial is a source control system very similar to git in the way it works with only subtle differences, I chose it as it was well supported on Windows but mostly due to TortoiseHg which is an awesome product.

Now I create many repositories and work in different environments. Now it can be really hard to manage so many repositories and no what to check keep current.

Note (This system could work much the same for git)

So I wanted to completely automate this process and end up with this:





To get this to work I needed to execute a few commands and then check the console outputs.

I decided to make a really extensible project that you could change and handle outputs differently with ease.

So I thought about a command like this:

   1: PyRun -script "Mercurial-autoupdate.py"  -command "hg" -args "summary" -workingDirectory <directory> -pushremote1 <remotedir>

This would execute the command “hg” which is the mercurial command and the argument “summary” to report the status of the working directory.



It would then run a few callback methods in the script i specify in the script parameters, where I can then check outputs, handle some events, and spawn sub processes.



So I created a PyRunner class which I start like this:

   1: _runner = new PyRunner(cmd.ParamAfterSwitch("command"),

   2:     cmd.ParamAfterSwitch("script"),

   3:     cmd.ParamAfterSwitch("args"),

   4:     cmd.ParamAfterSwitch("workingDirectory"), cmd);

   5: _runner.Run();

PyRunner will utilise the hosting script controller in an earlier post:

http://thewalkingdev.blogspot.com/2011/05/building-reusable-ironpython-hosting.html

   1: public void Run()

   2:         {

   3:             // var code = new StreamReader(Resources.ResourceManager.GetStream("TextFile1.py")).ReadToEnd();

   4:             _controller = new ScriptController(false);

   5:             _controller.OnMessage += (sender, message) =>

   6:                 {

   7:                     //Console.WriteLine(message);

   8:                    // Console.WriteLine(String.Format("[{0:HH:MM:ss:fff}][SCRIPT]:{1}", DateTime.Now, message.Trim()));

   9:                     Console.WriteLine(String.Format("{0}", message.Trim()));

  10:                    

  11:                 };

  12:             var code = Resources.ResourceManager.GetString("ScriptHeader1");

  13:             dynamic handler = null;

  14:             IScriptContext ctx;

  15:             try

  16:             {

  17:  

  18:                 if (string.IsNullOrEmpty(script))

  19:                 {

  20:                     script = code;

  21:                     ctx = _controller.CreateScriptContextFromString(script);

  22:                     ctx.Execute();

  23:                 }

  24:                 else

  25:                 {

  26:                     ctx = _controller.CreateScriptContextFromFile(script);

  27:                     ctx.ExecuteString(code);

  28:                     ctx.Execute();

  29:                 }

  30:              

  31:            

  32:                

  33:                 

  34:                 handler = ctx.Scope.CreateHandler();

  35:             }

  36:             catch (Exception e)

  37:             {

  38:                 Console.WriteLine(e.Message);

  39:                 return;

  40:             }

  41:         

  42:  

  43:             _mainProcess = new PyProcessContext(ctx, handler,

  44:                 _processFactory,

  45:                 command,

  46:                 CommandArguments,

  47:                 workingDirectory,

  48:                 _commandParameters);

  49:  

  50:             

  51:             _mainProcess.Start();

  52:  

  53:             ctx.FlushBuffer();

  54:            // Console.ReadKey();

  55:         }

It will create a handler that will be passed to the process context:

   1: public class PyProcessContext

   2:     {

   3:         dynamic _processHandler;

   4:         ProcessContext _processContext;

   5:  

   6:         public ProcessContext ProcessContext

   7:         {

   8:             get { return _processContext; }

   9:            

  10:         }

  11:         ProcessFactory _processFactory;

  12:         string _command;

  13:         string _commandArguments;

  14:         string _workingDirectory;

  15:         IScriptContext _scriptContext;

  16:         ICommandParameters _commandParameters;

  17:  

  18:         public PyProcessContext(IScriptContext scriptContext,

  19:             dynamic processHandler, 

  20:             ProcessFactory processFactory, 

  21:             string command,

  22:             string commandArguments,

  23:             string workingDirectory,

  24:             ICommandParameters commandParameters)

  25:         {

  26:             _commandParameters = commandParameters;

  27:             _scriptContext = scriptContext;

  28:             _command = command;

  29:             _commandArguments = commandArguments;

  30:             _workingDirectory = workingDirectory;

  31:             _processFactory = processFactory;

  32:             var processContext = _processFactory.CreateProcessContext(command, 

  33:                 commandArguments, 

  34:                 workingDirectory);

  35:             _processHandler = processHandler;

  36:             processContext.OnMessage += (message) =>

  37:             {

  38:                 _processHandler.OnMessage(message);

  39:                 //  Console.WriteLine(message);

  40:             };

  41:             processContext.OnError += (message) =>

  42:             {

  43:                 _processHandler.OnError(message);

  44:             };

  45:             _processHandler.OnInit(this, _commandParameters);

  46:             _processContext = processContext;

  47:         }

  48:         public void Start()

  49:         {

  50:             _processHandler.OnCommandStarting();

  51:             _processContext.Start();

  52:             _processHandler.OnCommandCompleted();

  53:         }

  54:  

  55:         public PyProcessContext CreateSpawnedProcess(string command,

  56:             string commandArguments)

  57:         {

  58:             return CreateSpawnedProcess(this._command,

  59:                 this._commandArguments,

  60:                 this._workingDirectory,

  61:                 this._processHandler);

  62:         }

  63:  

  64:         public PyProcessContext CreateSpawnedProcess(string command,

  65:             string commandArguments,

  66:             string workingDirectory,

  67:             dynamic processHandler)

  68:         {

  69:             if (workingDirectory == null)

  70:             {

  71:                 workingDirectory = _workingDirectory;

  72:             }

  73:             var cmd = new CmdLineHelper();

  74:             cmd.ParseString(commandArguments);

  75:             return new PyProcessContext(_scriptContext, 

  76:                 processHandler, 

  77:                 this._processFactory,

  78:                 command,

  79:                 commandArguments,

  80:                 workingDirectory,

  81:                 cmd);

  82:         }

  83:     }

Here is how to build the handler that each script must implement:

   1: class BaseHandler:

   2:         

   3:     def OnInit(self, processContext, args):

   4:         self._processContext = processContext

   5:         self._commandArgs = args

   6:         pass

   7:         

   8:     def OnError(self, message):

   9:         pass

  10:         

  11:     def OnMessage(self, message):

  12:         pass        

  13:         

  14:     def OnCommandStarting(self):

  15:         pass        

  16:         

  17:         

  18:     def OnCommandCompleted(self):

  19:         pass        

  20:         

  21:     def Grep(self, input, pattern):

  22:         #print "matching: %s pattern: %s" % (input, pattern)

  23:         return Regex(pattern).Match(input)

  24:     

  25:     def GetStdOut(self):

  26:         return self._processContext.ProcessContext.StandardOutput.GetValue()

  27:  

  28:     def GetErrorOut(self):

  29:         return self._processContext.ProcessContext.ErrorOutput.GetValue()

  30:     

  31:     def GrepStdOut(self, pattern):

  32:         return self.Grep(self._processContext.ProcessContext.StandardOutput.GetValue(), pattern)

  33:  

  34:  

  35:     def GrepStdOut(self, pattern):

  36:         

  37:         return self.Grep(self._processContext.ProcessContext.ErrorOutput.GetValue(), pattern)

  38:  

  39:  

  40: def CreateHandler():

  41:     return BaseHandler()

These events will be called as they occur with the process, you can only use OnCommandCompleted if you want to handle the outputs once completed.



Getting the mercurial summary of the working directory:

Spawn a process and read the output

   1: def HgGetSummary(self):

   2:     h = SubHandler()

   3:     self._processContext.CreateSpawnedProcess("hg", "summary", None, h).Start()

   4:     return h.GetStdOut()

Pull from remote and check if there are changes:

   1: def HgPull(self):

   2:     for remote in self.getPullRemotes():

   3:         print "Pulling remote: " + remote                        

   4:         h = SubHandler()

   5:         self._processContext.CreateSpawnedProcess("hg", 'pull "%s"' % remote, None, h).Start()    

   6:         self.PrintPushPullClean(h, "Pull: CLEAN", "Pull: NOT CLEAN, output:")

Similarly push and see if there are changes:

   1: def HgPush(self):

   2:     for remote in self.getPushRemotes():

   3:         print "Pushing remote: " + remote                        

   4:         h = SubHandler()

   5:         self._processContext.CreateSpawnedProcess("hg", 'push "%s"' % remote, None, h).Start()

   6:         self.PrintPushPullClean(h, "Push: CLEAN", "Push: NOT CLEAN, output:")

The function that check whether the call to the remote had no changes:

   1: def CheckPushPullClean(self, output):        

   2:     match = self.Grep(output, r"searching for changes[\r\n\s]+?no changes found")

   3:     return match.Success

Then check if anything needs to be committed:

   1: def CheckSummaryCommitClean(self, output):        

   2:     match = self.Grep(output, r"commit: \(clean\)")

   3:     return match.Success

Check if an update is required:

   1: def CheckSummaryUpdateClean(self, output):

   2:     #print "CHECKING IF UPDATE CLEAN: [[[[[[%s]]]]]]]] " % output        

   3:     match = self.Grep(output, r"update: \(current\)")

   4:     return match.Success

Here is a function that performs the commit:

   1: def HgCommit(self):

   2:     return self._processContext.CreateSpawnedProcess("thg", "commit", None, SubHandler()).Start()

Here is the function that performs an update:




All commits and updates run the visual version, however I added an update non visual flag, this will only perform hg update which has no UI so it is more automated.

   1: def HgUpdate(self):

   2:     if self.updateNonVisual():

   3:         print "Updating in non visual mode"

   4:         h = SubHandler()

   5:         updateResult = self._processContext.CreateSpawnedProcess("hg", "update", None, h).Start()

   6:         print h.GetStdOut()            

   7:         #self.PrintSummary(self.HgGetSummary())

   8:         return updateResult

   9:     else:

  10:         return self._processContext.CreateSpawnedProcess("thg", "update", None, SubHandler()).Start()

  11:       

Or just fire up the repository explorer:

   1: def HgRepoExplorer(self):

   2:     return self._processContext.CreateSpawnedProcess("thg", "log", None, SubHandler()).Start()

So now lets look at the AutoUpdate function with will apply all this logic to the working directory, to check the remote status, working directory status and push/pull/commit and update if needed.

   1: def AutoUpdate(self):        

   2:     summary = self.GetStdOut()

   3:     #self.PrintSummary(summary)

   4:     commitClean = self.CheckSummaryCommitClean(summary)

   5:     updateClean = self.CheckSummaryUpdateClean(summary)

   6:     if (commitClean):

   7:         print "Commit: CLEAN"           

   8:     else:            

   9:         print "Commit is NOT Clean"

  10:         self.HgCommit()

  11:         if self.hasRemotes():

  12:             if not self.pushNonVisual():

  13:                 print "You specified remote repositores, take a moment and view your changes with the explorer, a push will commence afterwards"

  14:                 self.HgRepoExplorer()

  15:                 self.HgPush()

  16:             else:

  17:                 print "You specified remote repositories to push in background"

  18:                 self.HgPush()

  19:             

  20:     if (not updateClean):

  21:         print "Update needed"

  22:         self.HgUpdate()

  23:         summary = self.HgGetSummary()

  24:         self.checkMustBeClean(summary)        

  25:     else:

  26:         print "Update: CLEAN"                       

  27:     

  28:     if self.hasRemotes():    

  29:         if (not self.CheckSummaryUpdateClean(self.HgGetSummary()) or not self.CheckSummaryCommitClean(self.HgGetSummary())):

  30:             raise Exception("Cannot pull from the remote repository because the working directory is still not clean")

  31:         self.HgPull()

  32:         if (not self.CheckSummaryUpdateClean(self.HgGetSummary())):

  33:             print "New revisions were detected after pull, update required"

  34:             self.HgUpdate()

  35:             summary = self.HgGetSummary()

  36:             self.checkMustBeClean(summary)

  37:         self.HgPush()

  38:     else:

  39:         print "No remotes, skipping pull"

  40:         

  41:     summary = self.HgGetSummary()

  42:     self.checkMustBeClean(summary)        

  43:  

Now I have a fully automated process that will check in, push/pull update in one click.



https://github.com/TheWalkingDev/Projects/tree/master/Processes/PyRun/PyRun



Library files can be found here:



https://github.com/TheWalkingDev/ACSR