Appendix B: SQL_ECMA2 Source Code

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
{
    public class EzmaExtension :
    IMAExtensible2CallExport,
    IMAExtensible2CallImport,
    // IMAExtensible2FileImport,
    //IMAExtensible2FileExport,
    //IMAExtensible2GetHierarchy,
     IMAExtensible2GetSchema,
     IMAExtensible2GetCapabilities,
     IMAExtensible2GetParameters
    //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;
        
        //
        // 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;
            }
        }

        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;
        }

        public ParameterValidationResult ValidateConfigParameters(KeyedCollection<string, ConfigParameter> configParameters,
                                                                   ConfigParameterPage page)
        {


            ParameterValidationResult myResults = new ParameterValidationResult();

            return myResults;

        }

        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;
        }

        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;

        }

        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();
        }


        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;
        }

        public CloseImportConnectionResults CloseImportConnection(CloseImportConnectionRunStep importRunStepInfo)
        {
            conn.Close();
            return new CloseImportConnectionResults();
        }

        public int ImportMaxPageSize
        {
            get
            {
                return m_importMaxPageSize;
            }
        }

        public int ImportDefaultPageSize
        {
            get
            {
                return m_importDefaultPageSize;
            }
        }

        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();

        }

        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.ChangedAttributeNames[0].ToString();
                                        myFirst = csentryChange.AttributeChanges["FirstName"].ValueChanges[0].Value.ToString();

                                        break;

                                    case "LastName":
                                        //  myLast = csentryChange.ChangedAttributeNames[0].ToString();
                                        myLast = csentryChange.AttributeChanges["LastName"].ValueChanges[0].Value.ToString();
                                        break;

                                    case "EMail":
                                        // myEmail = csentryChange.ChangedAttributeNames[0].ToString();
                                        myEmail = csentryChange.AttributeChanges["EMail"].ValueChanges[0].Value.ToString();
                                        break;

                                    case "EmployeeID":
                                        //myEmpID = csentryChange.AttributeChanges["EmpoyeeID"].ValueChanges[0].Value.ToString();
                                        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;
            }

        
        public void CloseExportConnection(CloseExportConnectionRunStep exportRunStep)
        
        {
            conn.Close();
            

        }

        public int ExportDefaultPageSize
        {
            get
            {
                return m_exportDefaultPageSize;
            }
            set
            {
                m_exportDefaultPageSize = value;
            }
        }

        public int ExportMaxPageSize
        {
            get
            {
                return m_exportMaxPageSize;
            }
            set
            {
                m_exportMaxPageSize = value;
            }
        }


    };
}