Home > Using C# and .Net to access Data Models

Using C# and .Net to access Data Models

In my previous blog post, I shared a PowerShell Script which uses the ER/Studio Data Architect COM automation interface.

During this post, we’ll create an application, using the Microsoft .NET Framework with the language C#, which can also connect to ER/Studio Data Architect.

System requirements

  • A Microsoft .Net Framework SDK and the Targeting Pack, also known as .NET Framework for developers:
    Microsoft .NET Framework SDK & Targeting PackWe’ll need the Type Library Importer tool which is included with the .NET Framework for developers and the Microsoft Visual C# Compiler.
  • ER/Studio Data Architect must be installed as the script interacts with it.

Preparation

Firstly, to get access to the ER/Studio Data Architect COM automation interface, we need to import the definitions of the available objects.

  1. Create a new folder. I named it CSharp.
  2. Copy the file TlbImp.exe in this new folder:
    Copy TlbImp.exe
    TlbImp.exe copied
    Depending on the version of the .Net Framework, the file TlbImp.exe is usually in a folder like: C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.8.1 Tools
  3. Copy the file Er1.tlb from the folder where ER/Studio Data Architect has been installed:
    Er1.tlb copied
  4. Open a Command Prompt: Windows key + R, type cmd
    Run Command Prompt
  5. Navigate to the previously created folder with the copied files:
    Navigate to the new folder
  6. Import the Type Library: TlbImp.exe Er1.tlb /out:ERStudio.dll
    Type library imported
  7. Create a new Text Document and rename it with an extension .cs. I renamed it ConnectToDataArchitect.cs:
    New Text Document
  8. Confirm the new extension:
    Change file name extension
  9. Edit this newly created file with your preferred C# editor. I’m using Notepad in this example:
    Edit with Notepad

Script

Look at the following script and copy it to your file.

using System;
using System.IO;
// Use the DLL generated with TlbImp.exe
using ERStudio;

namespace wERDA {

    class ERDA {

        static void Main(string[] args) {

            // Check the number of parameters
            if (args.Length != 1) {

                // Get the ER/Studio Application object
                ERStudio.Application ers = new ERStudio.Application();

                // Create a new Diagram
                ERStudio.Diagram MyDiagram = ers.NewDiagram();

                // Set some Diagram properties
                MyDiagram.Author = "W.";
                MyDiagram.Company = "IDERA";
                MyDiagram.CopyrightYear = "" + DateTime.Now.Year;

                Console.WriteLine("New diagram created; Adding an Entity.");

                // Get the current model
                ERStudio.Model MyModel = (ERStudio.Model)(MyDiagram.ActiveModel());

                // Add a new Entity
                ERStudio.Entity MyEntity = MyModel.Entities().Add(100, 100);

                // Set the Entity name
                MyEntity.EntityName = "Hello";

                // Add a new Attribute
                ERStudio.AttributeObj MyAttribute = MyEntity.Attributes().Add("World!", true);

                // Set the Notes of the Attribute
                MyAttribute.Notes = "From C#";

            }
            else {

                // Check if the first parameter is a valid filepath
                if (!File.Exists(args[0])) {

                    Console.WriteLine("The DM1 file does not exist!");
                    return;

                }

                Console.WriteLine("Launching ERDA...");

                // Get the ER/Studio Application object
                ERStudio.Application er = new ERStudio.Application();

                // Close the diagram we want to open if it's already opened
                er.CloseDiagram(Path.GetFileName(args[0]));
                // Open the diagram: First parameter of this application should be a path to a DM1 file
                ERStudio.Diagram diag = er.OpenFile(args[0]);

                // Check if diagram has been "opened"
                if (diag != null) {

                    // Get the list of Models
                    ERStudio.Models mdls = diag.Models();

                    // Loop on each model
                    foreach (ERStudio.Model mdl in mdls) {

                        // Display the Model Name
                        Console.WriteLine(mdl.Name);

                        // Get the SubModels
                        ERStudio.SubModels submdls = mdl.SubModels();

                        if (submdls != null) {

                            foreach (ERStudio.SubModel submdl in submdls) {

                                // Display the SubModel ID, SubModel Name and SubModel Parent ID
                                Console.WriteLine("--" + " {" + submdl.ID + "}  " + submdl.Name + "  [" + submdl.ParentSubmodelID + "]");

                                // Get the Entity Display Objects
                                ERStudio.EntityDisplays entds = submdl.EntityDisplays();

                                foreach (ERStudio.EntityDisplay entd in entds) {

                                    // Get the Entity from its Entity Display
                                    ERStudio.Entity ent = (ERStudio.Entity)(entd.ParentEntity());
                                    // Get the Attributes
                                    ERStudio.Attributes atts = ent.Attributes();
                                    // Display the Name of the Entity and its number of attributes
                                    Console.WriteLine("  --  " + entd.Name + " [" + atts.Count + "]");

                                }

                            }

                        }
                        else {

                            Console.WriteLine("submdls is null");

                        }

                    }

                    Console.WriteLine("Closing " + Path.GetFileName(args[0]));
                    // Close the diagram we opened
                    er.CloseDiagram(Path.GetFileName(args[0]));

                }
                else {

                    Console.WriteLine("Diagram can not be opened. Already opened?");

                }

            }

        }
    }
}

Save the file.

Script saved in file

Now, we can compile the script to generate an application: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /reference:ERStudio.dll .\ConnectToDataArchitect.cs

Application created

If you move your application, you will need to copy the ERStudio.dll too as it is referenced by the application.
You can also embed the DLL inside your application to only copy your executable.
To do so, just replace the option /reference with the option /link: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /link:ERStudio.dll .\ConnectToDataArchitect.cs

Run the application

If we look at the script we can see 2 different parts:

  • one which creates a new Diagram, a new Entity and a new Attribute.
    Call the application with no parameter.
    Call with no parameter
  • the other opens an existing Diagram, and displays the Models, SubModels and Entities.
    Call the application with a parameter which is equal to the full path of the DM1 file you want to open:
    Call with DM1 file full path

Summary

In this blog post, we used:

  • the ER/Studio Data Architect COM automation interface
    • to create some objects
    • to open a project and get some metadata that we can use in our own application written with the language C#.
  • the Microsoft .NET Framework to develop and compile our own application.

Bonus

I shared a video (4′) to support this blog post! 👍

Aligning complex data environments with business goals for over 30 years.
Copyright © 2024 Idera, Inc.