Step 6: Create the SQL_ECMA2 DLL

Creating and configuring the SQL_ECMA2 Extensible Connectivity 2.0 Management Agent for the test lab consists of the following:

  • Create the SQL_ECMA2 DLL

Create the new management agent DLL

Now we will create our SQL_ECMA2 DLL for our SQL_ECMA2 management agent. This will be done using the code provided. A full copy of the code is provided in Appendix B: SQL_ECMA2 Source Code.

To create the new management agent DLL

  1. Log on to FIM1 as CORP\Administrator.

  2. Click Start, select All Programs, select Microsoft Forefront Identity Manager and double-click Synchronization Service.

  3. In the Synchronization Service, at the top, select Management Agents.

  4. At the top, select Actions, select Create Extension Projects, and choose Extensible Connectivity 2.0 Extension. This will open a Create Extension Project dialog box.

  5. In the Create Extension Project dialog box, next to Programming Language: select Visual C#

  6. In the Create Extension Project dialog box, next to Visual Studio Version: select Visual Studio 2010.

  7. In the Create Extension Project dialog box, next to Project Name: enter SQL_ECMA2.

  8. Click OK. This will launch Visual Studio 2010.

    Note

    If this is the first time running Visual Studio you will receive a Choose Default Environmental Settings Dialog box. Select General Development Settings and click Start Visual Studio.

  9. On the right, at the top, under the Solutions Explorer, double-click SQL_ECMA2. This will open the file with code that has already been written.

  10. In the code, at the top, add using System.Collections.Generic, using System.Collections.ObjectModel, using System.Data.SqlClient and using System.Data. The code should look similar to below:

    using System;
    using System.IO;
    using System.Xml;
    using System.Text;
    using System.Collections.Specialized;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using Microsoft.MetadirectoryServices;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace FimSync_Ezma
    
  11. In the code, at the top, un-remark the following interfaces: IMAExtensible2CallImport, IMAExtensible2CallExport, IMAExtensible2GetSchema, IMAExtensible2GetCapabilities, and IMAExtensible2GetParameters. These will be the interfaces that this managmenet agent implements. The code should look similar to the code below:

    namespace FimSync_Ezma
    {
        public class EzmaExtension :
        IMAExtensible2CallExport,
        IMAExtensible2CallImport,
        // IMAExtensible2FileImport,
        //IMAExtensible2FileExport,
        //IMAExtensible2GetHierarchy,
         IMAExtensible2GetSchema,
         IMAExtensible2GetCapabilities,
         IMAExtensible2GetParameters
        //IMAExtensible2GetPartitions
        {
    
  12. Now below our interfaces we will declare some constants that will be used throughout our code. This will allow us to access these from different methods. Add the following code:

        //IMAExtensible2GetPartitions
        {
            private int m_importDefaultPageSize = 12;
            private int m_importMaxPageSize = 50;
            private int m_exportDefaultPageSize = 10;
            private int m_exportMaxPageSize = 20;
            public string myConnection;
            public string myDB;
            public string myServer;
            public string myTable;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataAdapter adapter;
            DataSet da;
            public string myFirst;
            public string myLast; 
            public string myEmail;
            public string myEmpID;
            public string myFull;
            public string myAccount;
    
  13. Now, down below the constructor, add the following code to implement the Management Agents capabilities. This will define what are management agent is capable of. For this MA, we will only have some limited capabilities. For instance, this MA will not support delta imports or object renames.

    //
            // Constructor
            //
            public EzmaExtension()
            {
                //
                // TODO: Add constructor logic here
                //
            }
    
            public MACapabilities Capabilities
            {
                get
                {
                    MACapabilities myCapabilities = new MACapabilities();
    
                    myCapabilities.ConcurrentOperation = true;
                    myCapabilities.ObjectRename = false;
                    myCapabilities.DeleteAddAsReplace = true;
                    myCapabilities.DeltaImport = false;
                    myCapabilities.DistinguishedNameStyle = MADistinguishedNameStyle.None;
                    myCapabilities.ExportType = MAExportType.AttributeUpdate;
                    myCapabilities.NoReferenceValuesInFirstExport = false;
                    myCapabilities.Normalizations = MANormalizations.None;
    
                    return myCapabilities;
                }
            }
    
  14. Now, below the management agent’s capabilities, add the GetConfigParameters method to implement the parameters of our management agent. This will allow us to take input SQL database information at the time the management agent is installed.

    public IList<ConfigParameterDefinition> GetConfigParameters(KeyedCollection<string, ConfigParameter> configParameters,
                                                                ConfigParameterPage page)
            {
                List<ConfigParameterDefinition> configParametersDefinitions = new List<ConfigParameterDefinition>();
    
                switch (page)
                {
                    case ConfigParameterPage.Connectivity:
    
                        configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Server", ""));
                        configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Database", ""));
                        configParametersDefinitions.Add(ConfigParameterDefinition.CreateStringParameter("Table", ""));              
                        break;
    
    
                    case ConfigParameterPage.Global:
    
    
                        break;
    
                    case ConfigParameterPage.Partition:
                        break;
    
                    case ConfigParameterPage.RunStep:
    
                        break;
                }
    
                return configParametersDefinitions;
            }
    
  15. Now, below the GetConfigParameters method, add the following code to implement the ValidateConfigParameters method. This is the method where we would configure code to validate the configuration parameters. For purposes of this test lab we don’t validate the parameters but we include this method because it is required to implement IMAExtensible2GetParameters.

    public ParameterValidationResult ValidateConfigParameters(KeyedCollection<string, ConfigParameter> configParameters,
                                                                       ConfigParameterPage page)
            {
    
    
                ParameterValidationResult myResults = new ParameterValidationResult();
    
                return myResults;
    
            }
    
            public ParameterValidationResult ValidateConfigParameters(KeyedCollection<string, ConfigParameter> configParameters,
                                                                       ConfigParameterPage page)
            {
    
    
                ParameterValidationResult myResults = new ParameterValidationResult();
                return myResults;
    
            }
    
  16. Now below the ValidateConfigParameters method, add the following code to implement the GetSchema method. This will be used to discover our schema while the management agent is being setup. The code below will connect to our HR database and query for the column names. It will set the anchor attribute to EmployeeID and then the other column names will be used as single-valued attributes.

      public Schema GetSchema(KeyedCollection<string, ConfigParameter> configParameters)
            {
    
                Microsoft.MetadirectoryServices.SchemaType personType = Microsoft.MetadirectoryServices.SchemaType.Create("Person", false);
    
                myServer = configParameters["Server"].Value;
                myDB = configParameters["Database"].Value;
                myTable = configParameters["Table"].Value;
    
                DataSet myData = this.SQLSchema(myServer, myDB, myTable);
    
    
                string[] SQLSchema = new string[myData.Tables["Columns"].Rows.Count];
    
    
                for (int i = 0; i <= myData.Tables["Columns"].Rows.Count - 1; i++)
                {
    
                    SQLSchema[i] = myData.Tables["Columns"].Rows[i].ItemArray.GetValue(0).ToString().Trim();
                    string myattrib = SQLSchema[i];
    
                    if (myattrib == "EmployeeID")
                    {
                        personType.Attributes.Add(SchemaAttribute.CreateAnchorAttribute(myattrib, AttributeType.String));
                    }
    
                    else
                    {
                        personType.Attributes.Add(SchemaAttribute.CreateSingleValuedAttribute(myattrib, AttributeType.String));
                    }
    
                }
    
                Schema schema = Schema.Create();
                schema.Types.Add(personType);
    
                return schema;
            }
    
  17. Now, below the GetSchema method, add the following public DataSet. This dataset is used in our schema discovery. It basically reads our SQL table columns and returns these columns as a dataset to the GetSchema method. The GetSchema method then loops through the dataset and creates the attributes for the schema based on the contents of the dataset.

    public DataSet SQLSchema(string server, string database, string table)
            {
                myConnection = ("Server = '" + server + "';Initial Catalog='" + database + "';Integrated Security=True");
                conn = new SqlConnection(myConnection);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                string cmdText = "Select COLUMN_NAME from Information_Schema.Columns where TABLE_Name = '" + table + "'";
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
                adapter = new SqlDataAdapter(cmd);
                da = new DataSet();
                adapter.Fill(da, "Columns");
                return da;
    
            }
    
  18. Now, below the SQLSchema dataset add the following code to implement the OpenImportConnectionResults method. This will initialize our connection information with the SQL database that will be used during imports.

      public OpenImportConnectionResults OpenImportConnection(
                                           KeyedCollection<string, ConfigParameter> configParameters,
                                           Schema types,
                                           OpenImportConnectionRunStep importRunStep)
            {
                myServer = configParameters["Server"].Value;
                myDB = configParameters["Database"].Value;
                myTable = configParameters["Table"].Value;
    
                myConnection = ("Server = '" + myServer + "';Initial Catalog='" + myDB + "';Integrated Security=True");
                conn = new SqlConnection(myConnection);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                string cmdText = "Select * from " + myTable;
                cmd.CommandText = cmdText;
                cmd.Connection = conn;
    
                return new OpenImportConnectionResults();
            }
    
  19. Now, below OpenImportConnectionResults and the following code to implement GetImportEntriesResults. This is the workhorse of a call-based MA with regard to importing. This method creates a dataset and populates it by selecting all of the data from the HR table. Then the code loops through the dataset and sets string values for the information in the dataset. These strings are used to set our attribute values. Once we have these values, we create a new Person object and set the attributes of the Person object to what is contained in our strings. Then the person added to our list of csentries. Once all of the data from SQL is created and added to the list, the list is returned to the Synchronization Service.

    public GetImportEntriesResults GetImportEntries(GetImportEntriesRunStep importRunStep)
            {
    
                adapter = new SqlDataAdapter(cmd);
                da = new DataSet();
                adapter.Fill(da, "Employees");
                GetImportEntriesResults importReturnInfo;
                List<CSEntryChange> csentries = new List<CSEntryChange>();
    
                for (int i = 0; i <= da.Tables["Employees"].Rows.Count - 1; i++)
                {
                    myFirst = da.Tables["Employees"].Rows[i].ItemArray.GetValue(0).ToString().Trim();
                    myLast = da.Tables["Employees"].Rows[i].ItemArray.GetValue(1).ToString().Trim();
                    myEmail = da.Tables["Employees"].Rows[i].ItemArray.GetValue(2).ToString().Trim();
                    myEmpID = da.Tables["Employees"].Rows[i].ItemArray.GetValue(3).ToString().Trim();
                    myFull = da.Tables["Employees"].Rows[i].ItemArray.GetValue(4).ToString().Trim();
                    myAccount = da.Tables["Employees"].Rows[i].ItemArray.GetValue(5).ToString().Trim();
    
                    CSEntryChange csentry1 = CSEntryChange.Create();
    
                    csentry1.ObjectModificationType = ObjectModificationType.Add;
                    csentry1.ObjectType = "Person";
    
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("FirstName", myFirst));
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("LastName", myLast));
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("EMail", myEmail));
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("EmployeeID", myEmpID));
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("FullName", myFull));
                    csentry1.AttributeChanges.Add(AttributeChange.CreateAttributeAdd("AccountName", myAccount));
                    csentries.Add(csentry1);
    
    
                }
    
                importReturnInfo = new GetImportEntriesResults();
                importReturnInfo.MoreToImport = false;
                importReturnInfo.CSEntries = csentries;
                return importReturnInfo;
            }
    
  20. Now, below GetImportEntriesResults add the following code to implement CloseImportConnectionResults. This will close our connection.

      public CloseImportConnectionResults CloseImportConnection(CloseImportConnectionRunStep importRunStepInfo)
            {
                conn.Close();
                return new CloseImportConnectionResults();
            }
    
  21. Now, below CloseImportConnectionResults add the following code to implement ImportMaxPageSize and ImportDefaultPageSize.

        public int ImportMaxPageSize
            {
                get
                {
                    return m_importMaxPageSize;
                }
            }
    
            public int ImportDefaultPageSize
            {
                get
                {
                    return m_importDefaultPageSize;
                }
            }
    
  22. Below the ImportDefaultPageSize add the following code to implement the OpenExportConnection method. This method will initialize our connection to our SQL database to use for exports.

            public void OpenExportConnection(KeyedCollection<string, ConfigParameter> configParameters,
                                Schema types,
                                OpenExportConnectionRunStep exportRunStep)
            {
                myServer = configParameters["Server"].Value;
                myDB = configParameters["Database"].Value;
                myTable = configParameters["Table"].Value;
    
                myConnection = ("Server = '" + myServer + "';Initial Catalog='" + myDB + "';Integrated Security=True");
                conn = new SqlConnection(myConnection);
                cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                conn.Open();
    
            }
    
  23. Now, below OpenExportConnection and the following code to implement PutExportEntriesResults. This is the workhorse of a call-based MA with regard to exporting. This method is responsible for writing our information to SQL. This is done by taking the csentries that are passed in by the Synchronization Service and depending on the type of modification, whether it is object or attribute, a corresponding T-SQL statement is built and then executed on the database.

      public PutExportEntriesResults PutExportEntries(IList<CSEntryChange> csentries)
            {
    
    
                int i = 0;
    
                    foreach (CSEntryChange csentryChange in csentries)
                    {
                        myEmpID = csentries[i].DN.ToString();
    
                        if (csentryChange.ObjectType == "Person")
                        {
    
                            #region Add
    
                            if (csentryChange.ObjectModificationType == ObjectModificationType.Add)
                            {
                                #region a
    
                                foreach (string attrib in csentryChange.ChangedAttributeNames)
                                {
    
                                    switch (attrib)
                                    {
                                        case "FirstName":
    
                                            myFirst = csentryChange.AttributeChanges["FirstName"].ValueChanges[0].Value.ToString();
    
                                            break;
    
                                        case "LastName":
    
                                            myLast = csentryChange.AttributeChanges["LastName"].ValueChanges[0].Value.ToString();
                                            break;
    
                                        case "EMail":
    
                                            myEmail = csentryChange.AttributeChanges["EMail"].ValueChanges[0].Value.ToString();
                                            break;
    
                                        case "EmployeeID":
                                              break;
    
                                        case "AccountName":
                                            myAccount = csentryChange.AttributeChanges["AccountName"].ValueChanges[0].Value.ToString();
                                            break;
    
                                        case "FullName":
                                            myFull = csentryChange.AttributeChanges["FullName"].ValueChanges[0].Value.ToString();
                                            break;
    
                                    }
    
    
                                }
                                #endregion
    
    
    
    
                                string cmdText = "Insert into " + myTable + "(FirstName, LastName, EMail, EmployeeID, FullName, AccountName) VALUES ('" + myFirst + "','" + myLast + "','" + myEmail + "','" + myEmpID + "','" + myFull + "','" + myAccount + "')";
                                cmd.CommandText = cmdText;
                                cmd.Connection = conn;
    
                                cmd.ExecuteNonQuery();
    
                            }
    
                            #endregion
                            #region Delete
                            if (csentryChange.ObjectModificationType == ObjectModificationType.Delete)
                            {
    
                                    myEmpID = csentries[i].DN.ToString();
                                    string cmdText = "Delete from " + myTable + "Where EmployeeID = '" + myEmpID + "'";
                                    cmd.CommandText = cmdText;
                                    cmd.Connection = conn;
                                    cmd.ExecuteNonQuery();
    
    
                            }
                            #endregion
    
                            #region Update
                            if (csentryChange.ObjectModificationType == ObjectModificationType.Update)
                            {
    
                                foreach (string attribName in csentryChange.ChangedAttributeNames)
                                {
    
    
                                    if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Add)
                                    {
                                        myEmpID = csentryChange.AnchorAttributes[0].Value.ToString();
                                        string attribValue = csentryChange.AttributeChanges[attribName].ValueChanges[0].Value.ToString();
                                        string cmdText = "Update" + myTable + "SET" + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'";
                                        cmd.CommandText = cmdText;
                                        cmd.Connection = conn;
                                        cmd.ExecuteNonQuery();
                                    }
                                    else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Delete)
                                    {
    
                                        myEmpID = csentryChange.AnchorAttributes[0].Value.ToString();
                                        string cmdText = "Update " + myTable + " SET " + attribName + " = 'NULL' Where EmployeeID = '" + myEmpID + "'";
                                        cmd.CommandText = cmdText;
                                        cmd.Connection = conn;
                                        cmd.ExecuteNonQuery();
                                    }
                                    else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Replace)
                                    {
                                        myEmpID = csentryChange.AnchorAttributes[0].Value.ToString();
                                        string attribValue = csentryChange.AttributeChanges[attribName].ValueChanges[0].Value.ToString();
                                        string cmdText = "Update " + myTable + " SET " + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'";
                                        cmd.CommandText = cmdText;
                                        cmd.Connection = conn;
                                        cmd.ExecuteNonQuery();
                                    }
                                    else if (csentryChange.AttributeChanges[attribName].ModificationType == AttributeModificationType.Update)
                                    {
                   string attribValue;
                   foreach (ValueChange valueChange in csentryChange.AttributeChanges[attribName].ValueChanges)
                                     {
                                      if (valueChange.ModificationType == ValueModificationType.Add)
                                     {
                                        attribValue = valueChange.Value.ToString();
                                        break;
                                      }
                            } 
    
    myEmpID = csentryChange.AnchorAttributes[0].Value.ToString();
                                    string cmdText = "Update " + myTable + " SET " + attribName + " = '" + attribValue + "' Where EmployeeID = '" + myEmpID + "'";
                                    cmd.CommandText = cmdText;
                                    cmd.Connection = conn;
                                    cmd.ExecuteNonQuery();
                                }
                                    }
    
    
    
    
                                }
    
    
    
    
                            }
    
                            #endregion
    
    
                        }
    
                        i++;
    
    
    
    
    
                    PutExportEntriesResults exportEntriesResults = new PutExportEntriesResults();
    
                    return exportEntriesResults;
                }
    
  24. Now, below PutExportEntriesResults add the following code to implement CloseExportConnection. This will close our connection.

    public void CloseExportConnection(CloseExportConnectionRunStep exportRunStep)
    
            {
                conn.Close();
    
    
            }
    
  25. Now below CloseExportConnection add the following code to implement ExportDefaultPageSize and ExportMaxPageSize.

            public int ExportDefaultPageSize
            {
                get
                {
                    return m_exportDefaultPageSize;
                }
                set
                {
                    m_exportDefaultPageSize = value;
                }
            }
    
            public int ExportMaxPageSize
            {
                get
                {
                    return m_exportMaxPageSize;
                }
                set
                {
                    m_exportMaxPageSize = value;
                }
            }
    
    
        };
    }
    
  26. At the top, select Build and choose Build Solution from the drop-down. At the bottom, in the Output window you should see Build: 1 succeeded.

    Extension Code