Automating ER/Studio with C# code.

I really enjoy data modelling and I really enjoy using the various modelling tools I’ve come across. All of them have their charms and their drawbacks. But I’ve yet to use one which works the way I want it to work out of the box. Anyway, at my current job I’m using ER/Studio Data Architect 9.7. It’s a fine tool.

But sometimes you need to move fast. Someone wants to see an ERD at a particular meeting and you haven’t made all the changes you need to make. So, you cut some corners. However, as in so many fields and disciplines, there are activities you can add to your work as you are going which cost you almost nothing, but which can be a big pain to go back and update at a later date.

In my current project, I was asked to change a bunch of attribute names in the logical model. No big deal right? Well, I had to push through this operation rather quickly. But then when I generated the physical model and then generated the physical database objects, I noticed that a lot of the column names didn’t match the attribute names in the logical model.

This is because ER/Studio allows you to enter an attribute name and give it a separate column name. This can be quite useful. You might have prefixes and abbreviations in your column names that you might want to hide from the subject matter experts who would be reviewing your logical model.

But if you want those names to stay the same, and you change a whole bunch at once, it might cost you a lot of time to re-open all those dialogue boxes and update the default column names. Had you done it the first go-round, it would have only cost you a few seconds for each attribute. You could just copy and paste the changes you make in one text box into another. The biggest cost in terms of time is opening the entity then selecting the attribute and opening that dialogue box.

When it comes to grunt work like this, I tend to try to script out as much of the work as possible. When I worked with Sybase Power Designer I used Power Shell a lot. (You can see a sample of the work I did with that here and a more efficient version of the same script here.) But for some reason I couldn’t get the ER/Studio assemblies to load in PowerShell. So, I turned to C#.

I decided to post the code not because it’s great C# code,but sometimes when you’re playing around with something like ER/Studio automation, it’s good to have an example to work from – even if it’s not the most elegant example.

Below is my C# code to force the default column names to match the attribute names in an ER/Studio data model. (Don’t forget to add ER/Studio assemblies to your project!)

As always, this code is offered “AS-IS”. Please save your work, backup your files, etc, before you use it. Use it on a test version of your DM1 file and take all the usual precautions.

<pre style="font:inherit;">
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Text;
using System.Data.OleDb;
using ERStudio;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;


namespace er_studio_automation
{
    class er_studio_automator
    {
        private ERStudio.Application app;
        private ERStudio.SubModel smod;
        private ERStudio.Diagram diag;
        private ERStudio.Model mod;


        private void FixDefaultColumnNames()
        {
            ERStudio.Entity ent;
            foreach (ERStudio.EntityDisplay entD in smod.EntityDisplays())
            {
                ent = entD.ParentEntity();
                foreach (ERStudio.AttributeObj attr in ent.Attributes())
                {
                    //Console.WriteLine("attr " + attr.AttributeName + " -- " + attr.ColumnName);

                    if (String.Compare(attr.ColumnName, attr.AttributeName, false) != 0)
                    {
                        attr.ColumnName = attr.AttributeName;
                    }
                     
                }

            }
            diag.SaveFile("");

        }
        public er_studio_automator(string diagram_path, string model_name, string sub_model_name)
        {
            this.app = OpenApp();

            this.diag = OpenDiagram(diagram_path);
            this.mod = OpenModel(model_name);
            this.smod = OpenSubModel(sub_model_name);


        }

        static int Main(string[] args)
        {

            string diagram_path;
            string model_name;
            string sub_model_name;
            string action;
            string file_path;

            

            // Test if input arguments were supplied: 
            if (args.Length < 4)
            {
                System.Console.WriteLine("Please enter a \\-escaped diagram path, a model name and a submodel name");
                return 1;
            }

            diagram_path = args[0];
            model_name = args[1];
            sub_model_name = args[2];
            action = args[3];

            if (args.Length > 4)
            {
                file_path = args[4];
            }
            else
            {
                file_path = "";
            }

            er_studio_automator my_app = new er_studio_automator(diagram_path,model_name,sub_model_name);
            //my_app.OpenDiagram()
            switch (action)
            {

                case "fix_default_column_names":
                    my_app.FixDefaultColumnNames();
                    break;
 
                
                default:
                    System.Console.WriteLine("Command not recognized");
                    break;
            }

            //my_app.ValidateRelationships();
            
            my_app.CloseApp();
            return 0;

        }
        private void CloseApp()
        {
            //this.app.Quit();
            this.app.CloseDiagram(this.diag.FileName);
            this.app.Quit();
        }
        private ERStudio.Application OpenApp()
        {
            ERStudio.Application app = new ERStudio.Application();
            app.HideWindow();
            return app;

        }
        private ERStudio.Diagram OpenDiagram(string FileName) 
        {
            ERStudio.Diagram diag = app.OpenFile(FileName);
            return diag;
        }
        private ERStudio.Model OpenModel(string ModelName)
        {
            ERStudio.Model mod = diag.Models().Item[ModelName];
            return mod;
        }

        private ERStudio.SubModel OpenSubModel(string SubModelName)
        {
            
            
            ERStudio.SubModel smod = mod.SubModels().Item[SubModelName];
            return smod;
            
        }
        
    }
}
</pre>

This code assumes you’re running from the command-line. If you are running in debug mode from the Visual Studio IDE, you can put the appropriate command-line parameters in the project properties by right-clicking on the project in the solution explorer and selecting properties. The parameters are:

1. Path to the DM1 file
2. The model name
3. The sub-model name
4. A command. I’ve only put the code for one of my commands – the one that fixes the default attribute names – in this blog post. The command is fix_default_column_names.
5. An optional file path. I use this with some other commands I’ve written which import Excel spreadsheets or spit them out.

Now, my model name is “Logical” and my sub model is called “Main Model”. My DM1 file is called data_warehouse_v1.DM1 and let’s say its path is G:\DW\data_warehouse_v1.DM1. Assuming your .exe file ends up in G:\DW\bin and is called automation.exe then the syntax to call this program from the command-line is:

G:\DW\bin\automation.exe G:\DW\data_warehouse_v1.DM1 “Logical” “Main Model” fix_default_column_names

You need to be sure that ER/Studio is not running when you execute this code! If ER/Studio is running when you execute this code, the code will crash and you’ll have an invisible instance of ER/Studio running on your machine. You can kill it through the task manager or through this PowerShell one-liner:

get-process | where-object {$_.Name -match "Erstudio"} | foreach-object {stop-process $_.Id}

Play around with it. Let me know what you think. If you can’t get it to work or have some ideas to make it better or just want to point something out, let me know.

So what are y’all automating these days?