static void Main(string[] args)
        {
            Arguments CommandLine = new Arguments(args);
            string operation = "";
            string file = "";
             if (CommandLine["F"] != null)
            {
                file = CommandLine["F"];
            }
            else Console.WriteLine("File not defined full path needed -F=c:\\xxx\\yyy\\zzz\\file.ldap warning there is no sanity check on the path");

            operation = CommandLine["O"];
            if (CommandLine["O"] != null)
            {
                if (CommandLine["O"] != "users" || CommandLine["O"] != "groups" || CommandLine["O"] != "OUmap" || CommandLine["O"] != "gmail")
                {
                    Console.WriteLine("Operation not defined -O=users -O=groups -O=OUmap -O=gmail");
                }
                else
                {
                    operation = CommandLine["O"];
                }
            }

             // Check for tests first
             // If tests are desired, skip normal opretaions
             if( CommandLine["T"] != null ){

            // Collection of Class objects
            // Each class needs to have a runTests() method
            GroupSynch groupconfig = new GroupSynch();
            UserSynch userconfig = new UserSynch();
            GmailUsers guserconfig = new GmailUsers();
            executionOrder execution = new executionOrder();
            UserStateChange usermapping = new UserStateChange();
            ConfigSettings settingsconfig = new ConfigSettings();
            utils.ToolSet tools = new ToolSet();
            LogFile log = new LogFile();
            ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
            ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
            StopWatch timer = new StopWatch();
            log.initiateTrn();

            // Sift through are different tests
            switch(CommandLine["T"])
            {
               // Run tests specific to users sync
               case "users":
                  // userconfig.runTests();
                  break;

               // Run tests specific to group sync
               case "groups":
                  // groupconfig.runTests();
                  break;

               // Run tests specific to OUmap sync
               case "OUmap":
                  // Not sure how this operation is used
                  break;

               // Run tests specifc to gmail sync
               case "gmail":
                  // guserconfig.runTests();
                  break;

               // Run all tests
               default:
               case "all":
                  // userconfig.runTests();
                  // groupconfig.runTests();
                  // guserconfig.runTests();
                  break;
               }
            }
            // MessageBox.Show("operation is " + operation + " file is " + file);
            else if (file != "" && operation != "")
            {

                // woot halleluijah we have input from the user time to execute
                //duplicate the gui fucntionality in cmd line
                // we won't check this input cause its from a really smart system administrator
                // just in case file expects a full path
                // c:\blah\blah\blah.ext
                // valid oprations are
                // users	groups	 OUmap	 gmail
                // create objects to hold save data

                GroupSynch groupconfig = new GroupSynch();
                UserSynch userconfig = new UserSynch();
                GmailUsers guserconfig = new GmailUsers();
                executionOrder execution = new executionOrder();
                UserStateChange usermapping = new UserStateChange();
                ConfigSettings settingsconfig = new ConfigSettings();
                utils.ToolSet tools = new ToolSet();
                LogFile log = new LogFile();
                ObjectADSqlsyncGroup groupSyncr = new ObjectADSqlsyncGroup();
                ObjectADGoogleSync gmailSyncr = new ObjectADGoogleSync();
                StopWatch timer = new StopWatch();
                log.initiateTrn();

                // perform operations based on the data input from the user fro groups users, OU's and gmail
                if (operation == "group")
                {
                    Dictionary<string, string> properties = new Dictionary<string, string>();
                    try
                    {
                        StreamReader re = File.OpenText(file);
                        string input = null;
                        while ((input = re.ReadLine()) != null && input != "<config>")
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }
                        // Load values into text boxes
                        // reload properties each time as they are overwritten with the combo object trigger events
                        groupconfig.Load(properties);

                        //load config settings
                        properties.Clear();
                        while ((input = re.ReadLine()) != null)
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }
                        re.Close();
                        settingsconfig.Load(properties);

                        log.addTrn("Start Groups Syncs", "Info");
                        timer.Start();
                        groupSyncr.ExecuteGroupSync(groupconfig, settingsconfig, tools, log);
                        timer.Stop();
                        log.addTrn("Groups " + groupconfig.Group_Append + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
                        tools.savelog(log, settingsconfig);
                    }
                    catch
                    {
                        log.errors.Add("Failed to load save file");
                    }

                    //// save log to disk
                    //SaveFileDialog saveFileDialog1 = new SaveFileDialog();
                    //saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
                    //saveFileDialog1.FilterIndex = 2;
                    //saveFileDialog1.RestoreDirectory = true;
                    //if (saveFileDialog1.ShowDialog() == DialogResult.OK)
                    //{
                    //    // create a file stream, where "c:\\testing.txt" is the file path
                    //    System.IO.FileStream fs = new System.IO.FileStream(saveFileDialog1.FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite);

                    //    // create a stream writer
                    //    System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);

                    //    // write to file (buffer), where textbox1 is your text box
                    //    sw.WriteLine("{0}", result2);
                    //    sw.WriteLine("{0}", result);

                    //    // flush buffer (so the text really goes into the file)
                    //    sw.Flush();

                    //    // close stream writer and file
                    //    sw.Close();
                    //    fs.Close();
                    //}

                }
                if (operation == "users")
                {
                    Dictionary<string, string> properties = new Dictionary<string, string>();
                    DataTable customs = new DataTable();
                    BindingSource bs = new BindingSource();

                    //OpenFileDialog openFileDialog1 = new OpenFileDialog();
                    //openFileDialog1.InitialDirectory = "c:\\";
                    //openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
                    //openFileDialog1.FilterIndex = 2;
                    //openFileDialog1.RestoreDirectory = true;
                    //if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    //{
                    try
                    {
                        StreamReader re = File.OpenText(file);

                        string input = null;
                        while ((input = re.ReadLine()) != null && input != "<config>")
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }
                        userconfig.Load(properties);

                        //load config settings
                        properties.Clear();
                        while ((input = re.ReadLine()) != null)
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }
                        re.Close();
                        settingsconfig.Load(properties);
                        log.addTrn("Start User Synch", "Info");
                        timer.Start();
                        groupSyncr.ExecuteUserSync(userconfig, settingsconfig, tools, log);
                        timer.Stop();
                        log.addTrn("Users " + userconfig.BaseUserOU + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
                        tools.savelog(log, settingsconfig);
                    }
                    catch
                    {
                        Console.Write("Failed to load save file");
                    }

                }
                if (operation == "gmail")
                {
                    Dictionary<string, string> properties = new Dictionary<string, string>();
                    BindingSource bs = new BindingSource();
                    try
                    {
                        StreamReader re = File.OpenText(file);

                        string input = null;
                        while ((input = re.ReadLine()) != null && input != "<config>")
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }

                        guserconfig.Load(properties);
                        //load config settings
                        properties.Clear();
                        while ((input = re.ReadLine()) != null)
                        {
                            string[] parts = input.Split('|');
                            properties.Add(parts[0].Trim(), parts[1].Trim());
                        }
                        re.Close();
                        settingsconfig.Load(properties);
                        log.addTrn("Start Gmail Synch", "Info");
                        timer.Start();
                        gmailSyncr.EmailUsersSync(guserconfig, settingsconfig, tools, log);
                        timer.Stop();
                        log.addTrn("Gmail " + guserconfig.Admin_domain + " Setup Completion time :" + timer.GetElapsedTimeSecs().ToString(), "Transaction");
                        tools.savelog(log, settingsconfig);
                    }
                    catch
                    {
                        Console.Write("Failed to load save file");
                    }

                }

            }
            else
            {
                Application.EnableVisualStyles();
                Application.SetCompatibleTextRenderingDefault(false);
                Application.Run(new Form1());
            }
        }
        // Log file utilities
        public void savelog(LogFile log, ConfigSettings settingsConfig)
        {
            // create a file stream, where "c:\\testing.txt" is the file path
            if (settingsConfig.LogType == "Text File")
            {
                string datetimeappend = DateTime.Today.Date.ToString() + DateTime.Today.TimeOfDay.ToString();
                System.IO.FileStream fs = new System.IO.FileStream(settingsConfig.LogDirectory + datetimeappend, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.ReadWrite);

                // create a stream writer
                System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.ASCII);

                StringBuilder result = new StringBuilder();

                foreach (DataColumn dc in log.logTrns.Columns)
                {
                    result.AppendFormat("{0}\t\t\t\t", dc.ColumnName);
                }

                result.Append("\r\n");

                foreach (DataRow dr in log.logTrns.Rows)
                {
                    foreach (DataColumn dc in log.logTrns.Columns)
                    {
                        result.AppendFormat("{0}\t\t\t\t", (Convert.IsDBNull(dr[dc.ColumnName]) ? string.Empty : dr[dc.ColumnName].ToString()));
                    }

                    result.Append("\r\n");
                }

                sw.Write(result.ToString());

                // flush buffer (so the text really goes into the file)
                sw.Flush();

                // close stream writer and file
                sw.Close();
                fs.Close();
            }
            if (settingsConfig.LogType == "Database")
            {
                //create sql for log file if it does not exist
                // sqlConn must be an open connection
                string table = "FHC_LOG_ldap_magic";
                DataTable data = new DataTable();
                log.initiateTrn();

                StringBuilder sqlstring = new StringBuilder();
                SqlConnection sqlConn = new SqlConnection("Data Source=" + settingsConfig.LogDB + ";Initial Catalog=" + settingsConfig.LogCatalog + ";Integrated Security=SSPI;Connect Timeout=360;");
                sqlConn.Open();
                SqlCommand sqlComm;

                sqlstring.Append("CREATE TABLE [" + table + "]([Message] [text], [Type] [varchar](50), [Timestamp] [datetime] NULL) ON [PRIMARY]");
                sqlComm = new SqlCommand(sqlstring.ToString(), sqlConn);
                try
                {
                    sqlComm.CommandTimeout = 360;
                    sqlComm.ExecuteNonQuery();
                    log.addTrn(sqlComm.CommandText.ToString(), "Query");
                    log.addTrn("table created " + table, "Transaction");
                }
                catch (Exception ex)
                {
                    log.addTrn("Table already exists or Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                    //log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString());
                }

                // copy data into table
                try
                {
                    SqlBulkCopy sbc = new SqlBulkCopy(sqlConn);
                    sbc.DestinationTableName = table;
                    sbc.WriteToServer(log.logTrns);
                    sbc.Close();
                }
                catch (Exception ex)
                {
                    log.addTrn("Failed SQL bulk copy " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                }
                //create datatable with log information
                //use append table to sql bulk copy to table
                //optionally create blank table and use copy into/merge to get records into existing table
            }
        }
        public void ExecuteGroupSync(GroupSynch groupsyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
        {
            StopWatch time = new StopWatch();

            string groupapp = groupsyn.Group_Append;
            string groupOU = groupsyn.BaseGroupOU;
            string sAMAccountName = "";
            string description = "";
            int count = 0;
            string sqlgroupsTable = "#FHC_GROUPS_SQLgroupsTable";
            string adGroupsTable = "#FHC_GROUPS_ADgroupsTable";
            if (settingsConfig.TempTables == true)
            {
                sqlgroupsTable = "#FHC_GROUPS_SQLgroupsTable";
                adGroupsTable = "#FHC_GROUPS_ADgroupsTable";
            }
            else
            {
                sqlgroupsTable = "FHC_GROUPS_SQLgroupsTable" + groupsyn.Group_Append;
                adGroupsTable = "FHC_GROUPS_ADgroupsTable" + groupsyn.Group_Append;
            }
            string dc = groupOU.Substring(groupOU.IndexOf("DC"));
            string groupDN;
            string groupsTable;
            SqlDataReader add;
            SqlDataReader delete;
            SqlDataReader update;
            ArrayList adUpdateKeys = new ArrayList();
            ArrayList sqlUpdateKeys = new ArrayList();
            ArrayList fields = new ArrayList();
            DataTable groupsDataTable = new DataTable();
            Dictionary<string, string> groupObject = new Dictionary<string, string>();
            SqlConnection sqlConn = new SqlConnection("Data Source=" + groupsyn.DataServer + ";Initial Catalog=" + groupsyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360");

            sqlConn.Open();
            // Setup the OU for the program
            log.addTrn("Setup OU for the groups", "Info");
            tools.CreateOURecursive("OU=" + groupapp + "," + groupOU, log);

            // A little house cleaning to empty out tables

            if (settingsConfig.TempTables == false)
            {
                log.addTrn("Clear out tables for use", "Info");
                tools.DropTable(adGroupsTable, sqlConn, log);
                tools.DropTable(sqlgroupsTable, sqlConn, log);
            }

            // grab list of groups from SQL insert into a temp table
            log.addTrn("Get groups from SQL", "Info");
            SqlCommand sqlComm = new SqlCommand();
            if (groupsyn.Group_where == "")
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + groupsyn.Group_sAMAccount + ") AS " + groupsyn.Group_sAMAccount + ", RTRIM(" + groupsyn.Group_CN + ") + '" + groupapp + "' AS " + groupsyn.Group_CN + " INTO " + sqlgroupsTable + " FROM " + groupsyn.Group_dbTable + " WHERE " + groupsyn.Group_sAMAccount + " IS NOT NULL ORDER BY " + groupsyn.Group_CN, sqlConn);
            }
            else
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + groupsyn.Group_sAMAccount + ") AS " + groupsyn.Group_sAMAccount + ", RTRIM(" + groupsyn.Group_CN + ") + '" + groupapp + "' AS " + groupsyn.Group_CN + " INTO " + sqlgroupsTable + " FROM " + groupsyn.Group_dbTable + " WHERE " + groupsyn.Group_sAMAccount + " IS NOT NULL AND " + groupsyn.Group_where + " ORDER BY " + groupsyn.Group_CN, sqlConn);
            }

            try
            {
                sqlComm.CommandTimeout = 360;
                sqlComm.ExecuteNonQuery();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");
            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                throw;
            }

            // generate a list of fields to ask from AD
            adUpdateKeys.Add("description");
            adUpdateKeys.Add("CN");

            // grab groups from AD
            log.addTrn("Get groups from AD", "Info");
            groupsDataTable = tools.EnumerateGroupsInOUDataTable("OU=" + groupapp + "," + groupOU, adUpdateKeys, adGroupsTable, log);

            // insert groups from AD into a temp table
            if (groupsDataTable.Rows.Count > 0)
            {

                groupsTable = tools.Create_Table(groupsDataTable, adGroupsTable, sqlConn, log);

                //Find groups that we need to create
                log.addTrn("Query to find groups that need to be created", "Info");
                add = tools.QueryNotExistsAllFields(sqlgroupsTable, groupsTable, sqlConn, groupsyn.Group_CN, adUpdateKeys[1].ToString(), log);

                // Create groups
                log.addTrn("Creating groups", "Info");
                while (add.Read())
                {
                    //i++;
                    sAMAccountName = (string)add[1].ToString().Trim();
                    description = (string)add[0].ToString().Trim();
                    groupObject.Add("sAMAccountName", sAMAccountName);
                    groupObject.Add("CN", sAMAccountName);
                    groupObject.Add("description", description);
                    tools.CreateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
                    groupObject.Clear();
                }

                add.Close();

                //time.Start();
                log.addTrn("Query to find groups to delete", "Info");
                delete = tools.QueryNotExistsAllFields(groupsTable, sqlgroupsTable, sqlConn, adUpdateKeys[1].ToString(), groupsyn.Group_CN, log);
                // delete groups in AD
                // i = 0;
                log.addTrn("Deleting groups", "Info");
                while (delete.Read())
                {
                    tools.DeleteGroup("OU=" + groupapp + "," + groupOU, (string)delete[adUpdateKeys[1].ToString()].ToString().Trim(), log);
                }
                delete.Close();

                // Get columns from sqlgroupsTable temp table in database get columns deprcated in favor of manual building due to cannot figure out how to get the columns of a temporary table
                // SQLupdateKeys = tools.GetColumns(groupsyn.DataServer, groupsyn.DBCatalog, sqlgroupsTable);
                // make the list of fields for the sql to check when updating note these fields must be in the same order as the AD update keys
                sqlUpdateKeys.Add(groupsyn.Group_sAMAccount);
                sqlUpdateKeys.Add(groupsyn.Group_CN);

                // update assumes the both ADupdateKeys and SQLupdateKeys have the same fields, listed in the same order check  call to EnumerateGroupsInOU if this is wrong should be sAMAccountName, CN matching the SQL order
                log.addTrn("Query to find groups which need to be updated", "Info");
                update = tools.CheckUpdate(sqlgroupsTable, groupsTable, groupsyn.Group_CN, adUpdateKeys[1].ToString(), sqlUpdateKeys, adUpdateKeys, sqlConn, log);

                // update groups in ad
                // last record which matches the primary key is the one which gets inserted into the database
                log.addTrn("Updating groups", "Info");
                while (update.Read())
                {
                    // any duplicate records will attempt to be updated if slow runtimes are a problem this might be an issue
                    // i++;
                    sAMAccountName = (string)update[1].ToString().Trim();
                    description = (string)update[0].ToString().Trim();
                    groupObject.Add("sAMAccountName", sAMAccountName);
                    groupObject.Add("CN", sAMAccountName);
                    groupObject.Add("description", description);

                    if (tools.Exists("CN=" + groupObject["CN"] + ", OU=" + groupapp + "," + groupOU) == true)
                    {
                        // group exists in place just needs updating
                        tools.UpdateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
                        // log.addTrn("Group update ; " + sAMAccountName + ",OU=" + groupapp + "," + groupOU + ";" + description);
                    }
                    else
                    {
                        // find it its on the server somewhere we will log the exception
                        groupDN = tools.GetObjectDistinguishedName(objectClass.group, returnType.distinguishedName, groupObject["CN"], dc, log);
                        // what if user is disabled will user mapping handle it?
                        // groups needs to be moved and updated
                        // tools.MoveADObject(groupDN, "LDAP://OU=" + groupapp + ',' + groupOU);
                        // tools.UpdateGroup("OU=" + groupapp + "," + groupOU, groupObject);
                        log.addTrn("Group cannot be updated user probabally should be in ; " + "OU=" + groupapp + "," + groupOU + " ; but was found in ; " + groupDN, "Error");
                    }
                    groupObject.Clear();
                }
                update.Close();
            }
            // we didn't find any records in AD so there is no need for the Update or delete logic to run
            else
            {
                log.addTrn("Query to get list of groups to add", "Info");
                sqlComm = new SqlCommand("SELECT * FROM " + sqlgroupsTable, sqlConn);
                try
                {
                    sqlComm.CommandTimeout = 360;
                    add = sqlComm.ExecuteReader();
                    log.addTrn(sqlComm.CommandText.ToString(), "Query");
                    log.addTrn("Adding groups", "Info");
                    while (add.Read())
                    {
                        //i++;
                        groupObject.Add("sAMAccountName", (string)add[1]);
                        groupObject.Add("CN", (string)add[1]);
                        groupObject.Add("description", (string)add[0]);
                        tools.CreateGroup("OU=" + groupapp + "," + groupOU, groupObject, log);
                        groupObject.Clear();
                    }
                    add.Close();
                }
                catch (Exception ex)
                {
                    log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                }

               }

            // users section
            string sqlgroupMembersTable = "#FHC_GROUPS_sqlusersTable";
            string ADgroupMembersTable = "#FHC_GROUPS_ADusersTable";
            if (settingsConfig.TempTables == true)
            {
                sqlgroupMembersTable = "#FCH_GROUPS_sqlusersTable";
                ADgroupMembersTable = "#FHC_GROUPS_ADusersTable";
            }
            else
            {
                sqlgroupMembersTable = "FHC_GROUPS_sqlusersTable";
                ADgroupMembersTable = "FHC_GROUPS_ADusersTable";
            }
            SqlDataReader sqlgroups;
            DataTable ADusers = new DataTable();

            // A little house cleaning to empty out tables
            log.addTrn("Clear out tables for usage", "Info");
            if (settingsConfig.TempTables == false)
            {
                tools.DropTable(sqlgroupMembersTable, sqlConn, log);
                tools.DropTable(ADgroupMembersTable, sqlConn, log);
            }

            // grab users data from sql
            log.addTrn("Get users from SQL", "Info");
            if (groupsyn.User_where == "")
            {
                sqlComm = new SqlCommand("SELECT DISTINCT 'CN=' + RTRIM(" + groupsyn.User_sAMAccount + ") + '," + groupsyn.BaseUserOU + "' AS " + groupsyn.User_sAMAccount + ", RTRIM(" + groupsyn.User_Group_Reference + ") + '" + groupapp + "' AS " + groupsyn.User_Group_Reference + " INTO " + sqlgroupMembersTable + " FROM " + groupsyn.User_dbTable, sqlConn);
            }
            else
            {
                sqlComm = new SqlCommand("SELECT DISTINCT 'CN=' + RTRIM(" + groupsyn.User_sAMAccount + ") + '," + groupsyn.BaseUserOU + "' AS " + groupsyn.User_sAMAccount + ", RTRIM(" + groupsyn.User_Group_Reference + ") + '" + groupapp + "' AS " + groupsyn.User_Group_Reference + " INTO " + sqlgroupMembersTable + " FROM " + groupsyn.User_dbTable + " WHERE " + groupsyn.User_where, sqlConn);
            }
            try
            {
                sqlComm.CommandTimeout = 360;
                sqlComm.ExecuteNonQuery();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");
            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
            }

            // populate datatable with users from AD groups by looping thru the list of groups from SQL and loading the cross referenced AD group members
            log.addTrn("Get users from AD by looping through groups from SQL and appending results to a table", "Info");
            sqlComm = new SqlCommand("SELECT " + groupsyn.Group_CN + " FROM " + sqlgroupsTable, sqlConn);
            try
            {
                ArrayList sqlgroupsStr = new ArrayList();
                sqlComm.CommandTimeout = 360;
                sqlgroups = sqlComm.ExecuteReader();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");
                DataTable currentOu = new DataTable();
                while (sqlgroups.Read())
                {
                    sqlgroupsStr.Add((string)sqlgroups[0]);
                }
                sqlgroups.Close();
                count = 0;
                foreach (string key in sqlgroupsStr)
                {
                    ADusers = tools.EnumerateUsersInGroupDataTable(key, ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log);
                    if (count == 0)
                    {
                        // make the temp table for ou comparisons the datatable must have somethign in it to make it
                        tools.Create_Table(ADusers, ADgroupMembersTable, sqlConn, log);
                    }
                    else
                    {
                        // table is already made now we need to only add to it
                        tools.Append_to_Table(ADusers, ADgroupMembersTable, sqlConn, log);
                    }
                    count++;
                }
                // hopefully merge acts as an append
                // ADusers.Merge(tools.EnumerateUsersInGroupDataTable((string)sqlgroups[0], ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log));
                // currentOu = tools.EnumerateUsersInGroupDataTable(  , ",OU=" + groupapp + "," + groupOU, groupsyn.User_sAMAccount, groupsyn.User_Group_Reference, ADgroupMembersTable, log);
                // tools.Append_to_Table(currentOu, ADgroupMembersTable, sqlConn, log);
            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
            }

            // compare and add/remove
            log.addTrn("Query to find the users to add ", "Info");
            add = tools.QueryNotExistsAllFields(sqlgroupMembersTable, ADgroupMembersTable, sqlConn, groupsyn.User_sAMAccount, ADusers.Columns[0].ColumnName, log);
            try
            {
                while (add.Read())
                {
                    tools.AddUserToGroup((string)add[0], "CN=" + (string)add[1] + ",OU=" + groupapp + "," + groupOU, false, dc, log);
                }
            }
            catch (Exception ex)
            {
                log.addTrn("Issue adding group datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
            }
            groupObject.Clear();
            add.Close();
            SqlCommand sqlComm2 = new SqlCommand();
            string recordCount = "";
            sqlComm2 = new SqlCommand("select count(" + groupsyn.Group_CN + ") FROM " + sqlgroupMembersTable, sqlConn);
            sqlComm2.CommandTimeout = 360;
            recordCount = sqlComm2.ExecuteScalar().ToString();
            sqlComm2.Dispose();

            if (recordCount != "0")
            {
                // setup update keys
                adUpdateKeys.Clear();
                sqlUpdateKeys.Clear();
                adUpdateKeys.Add(ADusers.Columns[1].ColumnName);
                adUpdateKeys.Add(ADusers.Columns[0].ColumnName);
                sqlUpdateKeys.Add(ADusers.Columns[1].ColumnName);
                sqlUpdateKeys.Add(ADusers.Columns[0].ColumnName);

                // get list of keys which have differed. We will delete them and then next time they will be readded as the correct key\
                // users which need to be updated just get deleted and recreadted later where they need to be
                log.addTrn("Query to see which users need to be deleted", "Info");
                delete = tools.CheckUpdate( ADgroupMembersTable, sqlgroupMembersTable, ADusers.Columns[0].ColumnName,  groupsyn.User_sAMAccount, adUpdateKeys, sqlUpdateKeys, sqlConn, log);
                // delete = tools.QueryNotExistsAllFields(ADgroupMembersTable, sqlgroupMembersTable, sqlConn, ADusers.Columns[1].ColumnName, groupsyn.User_Group_Reference, log);
                // delete groups in AD
                log.addTrn("Deleteing users", "Info");
                while (delete.Read())
                {
                    tools.RemoveUserFromGroup((string)delete[1], "CN=" + (string)delete[0] + ",OU=" + groupapp + "," + groupOU, log);
                }
                delete.Close();
            }
            sqlConn.Close();
        }
        public void ExecuteUserSync(UserSynch usersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
        {
            int i;
            ArrayList debugList = new ArrayList();
            StopWatch time = new StopWatch();

            string baseOU = usersyn.BaseUserOU;
            string DC = baseOU.Substring(baseOU.IndexOf("DC"));
            string sqlForCustomFields = "";

            // Table string place holders
            string sqlUsersTable = "#FHC_USERS_SQLusersTable";
            string adUsersTable = "#FHC_USERS_ADusersTable";

            SqlDataReader add;
            SqlDataReader delete;
            SqlDataReader update;

            SearchScope scope = SearchScope.OneLevel;
            ArrayList completeSqlKeys = new ArrayList();
            ArrayList completeADKeys = new ArrayList();
            ArrayList adUpdateKeys = new ArrayList();
            ArrayList sqlUpdateKeys = new ArrayList();
            ArrayList extraFieldsToReturn = new ArrayList();
            ArrayList fields = new ArrayList();
            Dictionary<string, string> userObject = new Dictionary<string, string>();
            SqlConnection sqlConn = new SqlConnection("Data Source=" + usersyn.DataServer + ";Initial Catalog=" + usersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360");

            if (settingsConfig.TempTables == true)
            {
                sqlUsersTable = "#FHC_USERS_SQLusersTable";
                adUsersTable = "#FHC_USERS_ADusersTable";
            }
            else
            {
                sqlUsersTable = "FHC_USERS_SQLusersTable";
                adUsersTable = "FHC_USERS_ADusersTable";

            }

            //SqlDataReader sqlusers;
            SqlCommand sqlComm;
            SqlCommand sqlComm2;
            string recordCount = "";
            DataTable adUsers = new DataTable();

            sqlConn.Open();
            //housecleaning
            log.addTrn("Cleaning out tables", "Info");
            if (settingsConfig.TempTables == false)
            {
                tools.DropTable(sqlUsersTable, sqlConn, log);
                tools.DropTable(adUsersTable, sqlConn, log);
            }

            //if were only updating it doesnt matter where we want ot put new users
            if (usersyn.UpdateOnly == false)
            {
                log.addTrn("Initial setup of OUs and Groups", "Info");
                // create initial ou's; will log a warning out if they already exist
                tools.CreateOURecursive(usersyn.BaseUserOU, log);
                tools.CreateOURecursive(usersyn.UserHoldingTank, log);

                // setup extentions for the user accounts to go in to the right ou's
                userObject.Add("sAMAccountName", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
                userObject.Add("CN", usersyn.UniversalGroup.Remove(0, 3).Remove(usersyn.UniversalGroup.IndexOf(",") - 3));
                userObject.Add("description", "Universal Group For Users");
                // creates the group if it does not exist
                tools.CreateGroup(usersyn.UniversalGroup.Remove(0, usersyn.UniversalGroup.IndexOf(",") + 1), userObject, log);
            }

            // need to add this field first to use as a primary key when checking for existance in AD
            completeSqlKeys.Add("sAMAccountName");
            completeSqlKeys.Add("CN");
            completeSqlKeys.Add("sn");
            completeSqlKeys.Add("givenName");
            completeSqlKeys.Add("homePhone");
            completeSqlKeys.Add("st");
            completeSqlKeys.Add("streetAddress");
            completeSqlKeys.Add("l");
            completeSqlKeys.Add("postalCode");
            // ?????? MIGHT NOT BE USED

            // Lets make the SQL fields to check for update
            sqlUpdateKeys.Add("sn");
            sqlUpdateKeys.Add("givenName");
            sqlUpdateKeys.Add("homePhone");
            sqlUpdateKeys.Add("st");
            sqlUpdateKeys.Add("streetAddress");
            sqlUpdateKeys.Add("l");
            sqlUpdateKeys.Add("postalCode");

            // Lets make the Active Directory Keys as well
            completeADKeys.Add("sAMAccountName");
            completeADKeys.Add("CN");
            completeADKeys.Add("sn");
            completeADKeys.Add("givenName");
            completeADKeys.Add("homePhone");
            completeADKeys.Add("st");
            completeADKeys.Add("streetAddress");
            completeADKeys.Add("l");
            completeADKeys.Add("postalCode");
            completeADKeys.Add("distinguishedName");

            // Lets make the Active Directory fields to check for update
            adUpdateKeys.Add("sn");
            adUpdateKeys.Add("givenName");
            adUpdateKeys.Add("homePhone");
            adUpdateKeys.Add("st");
            adUpdateKeys.Add("streetAddress");
            adUpdateKeys.Add("l");
            adUpdateKeys.Add("postalCode");

            //build custom keys
            for (i = 0; i < usersyn.UserCustoms.Rows.Count; i++)
            {
                // build keys to pull back from SQL
                // as well keys to check if these fields need updating
                completeSqlKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
                sqlUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());

                // build keys to pull back from AD
                // as well keys to check if these fields need updating
                completeADKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());
                adUpdateKeys.Add(usersyn.UserCustoms.Rows[i][0].ToString());

                // build fields to pull back from SQL
                //create props from rows in usercustoms datatable our column names match the appropriate fields in AD and SQL
                if (usersyn.UserCustoms.Rows[i][1].ToString() != "Static Value")
                {
                    sqlForCustomFields += ", RTRIM(" + usersyn.UserCustoms.Rows[i][1].ToString() + ") AS " + usersyn.UserCustoms.Rows[i][0].ToString();
                }
                // static fields get static values for the table to get updated
                else
                {
                    sqlForCustomFields += ", '" + usersyn.UserCustoms.Rows[i][2].ToString() + "' AS " + usersyn.UserCustoms.Rows[i][0].ToString();
                }
            }

            // grab users data from sql
            log.addTrn("Get users from SQL tables", "Info");
            if (usersyn.User_where == "")
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
                    ", RTRIM(" + usersyn.User_CN + ") AS CN" +
                    ", RTRIM(" + usersyn.User_Lname + ") AS sn" +
                    ", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
                    ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
                    ", RTRIM(" + usersyn.User_State + ") AS st" +
                    ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
                    //", RTRIM(" + usersyn.User_mail + ") AS mail" +
                    ", RTRIM(" + usersyn.User_city + ") AS l" +
                    ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
                    ", RTRIM(" + usersyn.User_password + ") AS password" +
                    sqlForCustomFields +
                    " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable, sqlConn);
            }
            else
            {
                sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + usersyn.User_sAMAccount + ") AS sAMAccountName" +
                    ", RTRIM(" + usersyn.User_CN + ") AS CN" +
                    ", RTRIM(" + usersyn.User_Lname + ") AS sn" +
                    ", RTRIM(" + usersyn.User_Fname + ") AS givenName" +
                    ", RTRIM(" + usersyn.User_Mobile + ") AS homePhone" +
                    ", RTRIM(" + usersyn.User_State + ") AS st" +
                    ", RTRIM(" + usersyn.User_Address + ") AS streetAddress" +
                    //", RTRIM(" + usersyn.User_mail + ") AS mail" +
                    ", RTRIM(" + usersyn.User_city + ") AS l" +
                    ", RTRIM(" + usersyn.User_Zip + ") AS postalCode" +
                    ", RTRIM(" + usersyn.User_password + ") AS password" +
                    sqlForCustomFields +
                    " INTO " + sqlUsersTable + " FROM " + usersyn.User_dbTable +
                    " WHERE " + usersyn.User_where, sqlConn);
            }
            try
            {
                sqlComm.CommandTimeout = 360;
                sqlComm.ExecuteNonQuery();
                log.addTrn(sqlComm.CommandText.ToString(), "Query");

            }
            catch (Exception ex)
            {
                log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                throw;
            }
            if (usersyn.SearchScope == "Subtree")
            {
                scope = SearchScope.Subtree;
            }

            // go grab all the users from AD
            log.addTrn("Get users from active directory", "Info");
            adUsers = tools.EnumerateUsersInOUDataTable(usersyn.BaseUserOU, completeADKeys, adUsersTable, scope, log);
            if (adUsers.Rows.Count > 0)
            {
                // make the temp table for ou comparisons

                tools.Create_Table(adUsers, adUsersTable, sqlConn, log);

                // Quick check to stop adding if the update only box is checked
                if (usersyn.UpdateOnly == false)
                {
                    // compare query for the add/remove
                    log.addTrn("Query to find users to add", "Info");
                    add = tools.QueryNotExistsByPkey(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);

                    // actual add stuff
                    log.addTrn("Adding users", "Info");
                    tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
                    add.Close();

                    sqlComm2 = new SqlCommand("select count(sAMAccountName) FROM " + sqlUsersTable, sqlConn);
                    sqlComm2.CommandTimeout = 360;
                    recordCount = sqlComm2.ExecuteScalar().ToString();
                    sqlComm2.Dispose();

                    if (recordCount != "0")
                    {
                        // compare query to find records which need deletion
                        log.addTrn("Query to find users to delete", "Info");
                        delete = tools.QueryNotExistsByPkey(adUsersTable, sqlUsersTable, sqlConn, usersyn.User_sAMAccount, completeADKeys[0].ToString(), log);

                        // delete users in AD
                        log.addTrn("Deleting users", "Info");
                        try
                        {
                            while (delete.Read())
                            {

                                tools.DeleteUserAccount((string)delete["distinguishedname"], log);
                                // log.addTrn("User removed ;" + (string)delete[adUpdateKeys[1].ToString()].ToString().Trim());
                            }
                        }
                        catch (Exception ex)
                        {
                            log.addTrn("Issue deleting AD users datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                        }
                        delete.Close();
                    }
                }

                // add the extra fields in form ".field ,"
                extraFieldsToReturn.Add(adUsersTable + ".distinguishedname ,");

                log.addTrn("Query to find users to update", "Info");
                update = tools.CheckUpdate(sqlUsersTable, adUsersTable, "sAMAccountName", "sAMAccountName", sqlUpdateKeys, adUpdateKeys, extraFieldsToReturn, 1, sqlConn, log);

                // update users in ad
                // last record which matches the primary key is the one which gets inserted into the database
                log.addTrn("Updating users", "Info");
                tools.UpdateUsers(update, DC, usersyn, log);

                update.Close();
            }
            // did not find any records in AD we are only adding users
            else
            {
                // and we are not updating users
                if (usersyn.UpdateOnly == false)
                {
                    // add the users without doing additional checks
                    tools.Create_Table(adUsers, adUsersTable, sqlConn, log);
                    log.addTrn("Query to find users to add", "Info");
                    add = tools.QueryNotExistsAllFields(sqlUsersTable, adUsersTable, sqlConn, "sAMAccountName", adUsers.Columns[0].ColumnName, log);
                    log.addTrn("Add all users", "Info");
                    tools.CreateUsersAccounts(usersyn.UserHoldingTank, add, usersyn.UniversalGroup, DC, usersyn, log);
                    add.Close();
                }
            }
            sqlConn.Close();
        }
        public void EmailUsersSync(GmailUsers gusersyn, ConfigSettings settingsConfig, ToolSet tools, LogFile log)
        {
            // MessageBox.Show("gmail " + gusersyn.Admin_password + " " + gusersyn.Admin_domain + " " + gusersyn.Admin_user + " " + gusersyn.DataServer + " " + gusersyn.DBCatalog + " " + gusersyn.User_ad_OU + " " + gusersyn.User_Datasource + " " + gusersyn.User_dbTable + " " + gusersyn.User_Fname + " " + gusersyn.User_Lname + " " + gusersyn.User_Mname + " " + gusersyn.User_password + " " + gusersyn.User_password_short_fix_checkbox.ToString() + " " + gusersyn.User_password_generate_checkbox.ToString() + " " + gusersyn.User_StuID + " " + gusersyn.User_table_view + " " + gusersyn.User_where + " " + gusersyn.Writeback_AD_checkbox.ToString() + " " + gusersyn.Writeback_ad_OU + " " + gusersyn.Writeback_DB_checkbox.ToString() + " " + gusersyn.Writeback_email_field + " " + gusersyn.Writeback_primary_key + " " + gusersyn.Writeback_secondary_email_field + " " + gusersyn.Writeback_table + " " + gusersyn.Writeback_transfer_email_checkbox.ToString() + " " + gusersyn.Writeback_where_clause);
            // Email addresses are static so only the names can be updated. passwords will be ignored
            // appservice variables will come from a config designed ot hold its data (sql and Gmail login)
            string userDN = "";
            AppsService service = new AppsService(gusersyn.Admin_domain, gusersyn.Admin_user + "@" + gusersyn.Admin_domain, gusersyn.Admin_password);
            ArrayList completeSqlKeys = new ArrayList();
            ArrayList completeGmailKeys = new ArrayList();
            ArrayList gmailUpdateKeys = new ArrayList();
            ArrayList sqlUpdateKeys = new ArrayList();
            ArrayList adUpdateKeys = new ArrayList();
            ArrayList additionalKeys = new ArrayList();

            // Table place holders
            string sqlUsersTable = "#FHC_LDAP_sqlusersTable";
            string gmailUsersTable = "#FHC_LDAP_gmailusersTable";
            string nicknamesFromGmailTable = "#FHC_LDAP_gmailNicknamesTable";
            string loginWithoutNicknamesTable = "#FHC_LDAP_loginsWONicknamesTable";
            string adNicknamesTable = "#FHC_LDAP_adNicknamesTable";
            string sqlNicknamesTable = "#FHC_LDAP_sqlNicknamesTable";
            string nicknamesToUpdateDBTable = "#FHC_LDAP_nicknamesToUpdateDB";
            string nicknamesFilteredForDuplicatesTable = "#FHC_LDAP_nicknamesFilteredDuplicates";
            string nicknamesFromGmailTable2 = "#FHC_LDAP_gmailNicknamesTable2";
            string gmailUsersTableWB = "#FHC_LDAP_gmailusersTableWB";

            SqlDataReader add;
            //SqlDataReader delete;
            SqlDataReader update;
            SqlConnection sqlConn = new SqlConnection("Data Source=" + gusersyn.DataServer + ";Initial Catalog=" + gusersyn.DBCatalog + ";Integrated Security=SSPI;Connect Timeout=360;");

            if (settingsConfig.TempTables == true)
            {
                sqlUsersTable = "#FHC_LDAP_sqlusersTable";
                gmailUsersTable = "#FHC_LDAP_gmailusersTable";
            }
            else
            {
                sqlUsersTable = "FHC_LDAP_sqlusersTable";
                gmailUsersTable = "FHC_LDAP_gmailusersTable";
            }

            //SqlDataReader sqlusers;
            SqlCommand sqlComm;
            DataTable gmailUsers = new DataTable();
            DataTable adUsers = new DataTable();
            DataTable writeback = new DataTable();

            // set up fields to pull back from SQL or AD if the flag is checked both must contain the same data
            completeSqlKeys.Add(gusersyn.User_StuID);
            completeSqlKeys.Add(gusersyn.User_Fname);
            completeSqlKeys.Add(gusersyn.User_Lname);
            completeSqlKeys.Add(gusersyn.User_Mname);
            completeSqlKeys.Add(gusersyn.User_password);

            // Lets make the SQL fields to check for update
            sqlUpdateKeys.Add(gusersyn.User_Fname);
            sqlUpdateKeys.Add(gusersyn.User_Lname);

            // Lets make the gmail Keys as well
            completeGmailKeys.Add(gusersyn.User_StuID);
            completeGmailKeys.Add(gusersyn.User_Fname);
            completeGmailKeys.Add(gusersyn.User_Mname);
            completeGmailKeys.Add(gusersyn.User_Lname);
            completeGmailKeys.Add(gusersyn.User_password);

            // Lets make the gmail fields to check for update
            gmailUpdateKeys.Add(gusersyn.User_Fname);
            gmailUpdateKeys.Add(gusersyn.User_Lname);

            // List of extra fields to pull when dealing with accounts for update since we only compare first and last name we need to repull ID and middle name
            additionalKeys.Add(sqlUsersTable + "." + gusersyn.User_StuID + ", ");
            additionalKeys.Add(sqlUsersTable + "." + gusersyn.User_Mname + ", ");

            sqlConn.Open();

            //housecleaning
            log.addTrn("Clear out tables for use", "Info");
            if (settingsConfig.TempTables == false)
            {
                tools.DropTable(sqlUsersTable, sqlConn, log);
                tools.DropTable(gmailUsersTable, sqlConn, log);
            }

            // this statement picks the datasource SQL vs AD and sets up the temp table

            if (gusersyn.User_Datasource == "database")
            {
                // grab users data from sql
                log.addTrn("Get users from SQL", "Info");
                if (gusersyn.User_where == "")
                {
                    sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.User_StuID + ")" +
                        ", RTRIM(" + gusersyn.User_Fname + ") AS " + gusersyn.User_Fname +
                        ", RTRIM(" + gusersyn.User_Lname + ") AS " + gusersyn.User_Lname +
                        ", RTRIM(" + gusersyn.User_Mname + ") AS " + gusersyn.User_Mname +
                        ", RTRIM(" + gusersyn.User_password + ") AS " + gusersyn.User_password +
                        " INTO " + sqlUsersTable + " FROM " + gusersyn.User_dbTable, sqlConn);
                }
                else
                {
                    sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.User_StuID +
                        ", RTRIM(" + gusersyn.User_Fname + ")" + gusersyn.User_Fname +
                        ", RTRIM(" + gusersyn.User_Lname + ")" + gusersyn.User_Lname +
                        ", RTRIM(" + gusersyn.User_Mname + ")" + gusersyn.User_Mname +
                        ", RTRIM(" + gusersyn.User_password + ")" + gusersyn.User_password +
                        " INTO " + sqlUsersTable + " FROM " + gusersyn.User_dbTable +
                        " WHERE " + gusersyn.User_where, sqlConn);
                }
                try
                {
                    sqlComm.CommandTimeout = 360;
                    sqlComm.ExecuteNonQuery();
                    log.addTrn(sqlComm.CommandText.ToString(), "Query");
                }
                catch (Exception ex)
                {
                    log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                    // If we add deletion we need to fail out if this is blank
                    //throw;
                }
            }
            else
            {
                log.addTrn("Get users from AD", "Info");
                adUsers = tools.EnumerateUsersInOUDataTable(gusersyn.User_ad_OU, completeSqlKeys, sqlUsersTable, SearchScope.OneLevel, log);
                // build the database temp table from the users retrieved into adUsers
                tools.Create_Table(adUsers, sqlUsersTable, sqlConn, log);
            }

            // go grab all the users from Gmail from the database
            log.addTrn("Get users from Gmail for comparison", "Info");
            gmailUsers = tools.Get_Gmail_Users(service, gusersyn, gmailUsersTable, log);
            // make the temp table for ou comparisons
            tools.Create_Table(gmailUsers, gmailUsersTable, sqlConn, log);

            // compare and add/remove
            log.addTrn("Query to find the users who need to be created", "Info");
            add = tools.QueryNotExistsByPkey(sqlUsersTable, gmailUsersTable, sqlConn, gusersyn.User_StuID, gmailUsers.Columns[0].ColumnName, log);

            log.addTrn("Adding users", "Info");
            tools.Create_Gmail_Users(service, gusersyn, add, log);
            add.Close();

            //delete = tools.QueryNotExistsAllFields(sqlUsersTable, gmailUsersTable, sqlConn, gusersyn.User_StuID, completeGmailKeys[0].ToString());
            // delete groups in AD
            //while (delete.Read())
            //{
            //tools.DeleteGmailUserAccount((string)delete[0], (string)delete[1], log);
            //
            //}
            //delete.Close();

            log.addTrn("Query to find users to update", "Info");
            update = tools.CheckUpdate(sqlUsersTable, gmailUsersTable, gusersyn.User_StuID, gmailUsers.Columns[0].ColumnName, sqlUpdateKeys, gmailUpdateKeys, additionalKeys, 0, sqlConn, log);

            log.addTrn("Updating users", "Info");
            tools.UpdateGmailUser(service, gusersyn, update, log);
            update.Close();

            // ***********************************
            // ** Start writeback features
            // ***********************************

            if (settingsConfig.TempTables == true)
            {
                nicknamesFromGmailTable = "#FHC_LDAP_gmailNicknamesTable";
                loginWithoutNicknamesTable = "#FHC_LDAP_loginsWONicknamesTable";
                adNicknamesTable = "#FHC_LDAP_adNicknamesTable";
                sqlNicknamesTable = "#FHC_LDAP_sqlNicknamesTable";
                nicknamesToUpdateDBTable = "#FHC_LDAP_nicknamesToUpdateDB";
                nicknamesFilteredForDuplicatesTable = "#FHC_LDAP_nicknamesFilteredDuplicates";
                nicknamesFromGmailTable2 = "#FHC_LDAP_gmailNicknamesTable2";
                gmailUsersTableWB = "#FHC_LDAP_gmailusersTableWB";
            }
            else
            {
                nicknamesFromGmailTable = "FHC_LDAP_gmailNicknamesTable";
                loginWithoutNicknamesTable = "FHC_LDAP_loginsWONicknamesTable";
                adNicknamesTable = "FHC_LDAP_adNicknamesTable";
                sqlNicknamesTable = "FHC_LDAP_sqlNicknamesTable";
                nicknamesToUpdateDBTable = "FHC_LDAP_nicknamesToUpdateDB";
                nicknamesFilteredForDuplicatesTable = "FHC_LDAP_nicknamesFilteredDuplicates";
                nicknamesFromGmailTable2 = "FHC_LDAP_gmailNicknamesTable2";
                gmailUsersTableWB = "FHC_LDAP_gmailusersTableWB";
            }

            string dc = gusersyn.Writeback_ad_OU.Substring(gusersyn.Writeback_ad_OU.IndexOf("DC"));
            string userNickName = "";
            SqlDataReader nicknamesToAddToAD;
            SqlDataReader lostNicknames;
            ArrayList nicknameKeys = new ArrayList();
            ArrayList sqlkeys = new ArrayList();
            ArrayList adPullKeys = new ArrayList();
            ArrayList adMailUpdateKeys = new ArrayList();
            ArrayList nicknameKeysAndTable = new ArrayList();
            ArrayList sqlkeysAndTable = new ArrayList();
            ArrayList keywordFields = new ArrayList(); //fields for checking against nickname to see how close it is to the real data
            ArrayList selectFields = new ArrayList(); //fields from nicknamesFromGmailTable to bring back
            DataTable nicknames = new DataTable();
            SqlDataReader sendAsAliases;

            // housecleaning
            log.addTrn("Clear out tables for use", "Info");
            //            if (settingsConfig.TempTables == false)
            //            {
                tools.DropTable(nicknamesFromGmailTable, sqlConn, log);
                tools.DropTable(loginWithoutNicknamesTable, sqlConn, log);
                tools.DropTable(adNicknamesTable, sqlConn, log);
                tools.DropTable(sqlNicknamesTable, sqlConn, log);
                tools.DropTable(nicknamesToUpdateDBTable, sqlConn, log);
                tools.DropTable(nicknamesFilteredForDuplicatesTable, sqlConn, log);
                tools.DropTable(nicknamesFromGmailTable2, sqlConn, log);
                tools.DropTable(gmailUsersTableWB, sqlConn, log);
            //            }

            // install levenstein if bulid nicknames checked
            log.addTrn("Add levenshtein", "Info");
            if (gusersyn.Levenshtein == true)
            {
                string sqlcmd = "CREATE function LEVENSHTEIN( @s varchar(50), @t varchar(50) ) \n --Returns the Levenshtein Distance between strings s1 and s2. \n " +
                              "--Original developer: Michael Gilleland    http://www.merriampark.com/ld.htm \n --Translated to TSQL by Joseph Gama \n returns varchar(50) \n " +
                              "as \n BEGIN \n DECLARE @d varchar(2500), @LD int, @m int, @n int, @i int, @j int, \n @s_i char(1), @t_j char(1),@cost int \n --Step 1 \n SET @n=LEN(@s) \n" +
                              " SET @m=LEN(@t) \n SET @d=replicate(CHAR(0),2500) \n If @n = 0 \n BEGIN \n SET @LD = @m \n GOTO done \n END \n If @m = 0 \n BEGIN \n	SET @LD = @n \n" +
                              "	GOTO done \n END \n --Step 2 \n SET @i=0 \n WHILE @i<=@n \n	BEGIN \n SET @d=STUFF(@d,@i+1,1,CHAR(@i))--d(i, 0) = i \n SET @i=@i+1 \n END \n" +
                              " SET @i=0 \n WHILE @i<=@m \n BEGIN \n SET @d=STUFF(@d,@i*(@n+1)+1,1,CHAR(@i))--d(0, j) = j \n SET @i=@i+1 \n	END \n --goto done \n --Step 3 \n" +
                              " SET @i=1 \n WHILE @i<=@n \n BEGIN \n SET @s_i=(substring(@s,@i,1)) \n --Step 4 \n SET @j=1 \n	WHILE @j<=@m \n	BEGIN \n SET @t_j=(substring(@t,@j,1)) \n" +
                              " --Step 5 \n If @s_i = @t_j \n	SET @cost=0 \n ELSE \n SET @cost=1 \n --Step 6 \n SET @d=STUFF(@d,@j*(@n+1)+@i+1,1,CHAR(dbo.MIN3( \n" +
                              " ASCII(substring(@d,@j*(@n+1)+@i-1+1,1))+1, \n ASCII(substring(@d,(@j-1)*(@n+1)+@i+1,1))+1, \n ASCII(substring(@d,(@j-1)*(@n+1)+@i-1+1,1))+@cost) \n )) \n" +
                              " SET @j=@j+1 \n END \n SET @i=@i+1 \n END \n --Step 7 \n SET @LD = ASCII(substring(@d,@n*(@m+1)+@m+1,1)) \n done: \n --RETURN @LD \n" +
                              " --I kept this code that can be used to display the matrix with all calculated values \n --From Query Analyser it provides a nice way to check the algorithm in action \n" +
                              " -- \n RETURN @LD \n --declare @z varchar(8000) \n --set @z='' \n --SET @i=0 \n --WHILE @i<=@n \n --	BEGIN \n --	SET @j=0 \n --	WHILE @j<=@m \n --		BEGIN \n" +
                              " --		set @z=@z+CONVERT(char(3),ASCII(substring(@d,@i*(@m+1 )+@j+1 ,1))) \n --		SET @j=@j+1  \n --		END \n --	SET @i=@i+1 \n --	END \n --print dbo.wrap(@z,3*(@n+1)) \n END \n";
                sqlComm = new SqlCommand(sqlcmd, sqlConn);

                try
                {
                    sqlComm.CommandTimeout = 360;
                    // sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlComm.ExecuteNonQuery();
                    log.addTrn(sqlComm.CommandText.ToString(), "Query");
                }
                catch (Exception ex)
                {
                    log.addTrn("Failed to install levenstein SQL command or levenstein already installed" + sqlComm.CommandText.ToString() + " error " + ex + "\n" + ex.StackTrace.ToString(), "Error");
                }
                // install min3
                sqlcmd = "CREATE function MIN3(@a int,@b int,@c int ) \n --Returns the smallest of 3 numbers. \n" +
                    "returns int \n as \n BEGIN \n declare @temp int \n if (@a < @b)  AND (@a < @c) \n select @temp=@a \n else \n if (@b < @a)  AND (@b < @c) \n select @temp=@b \n else \n" +
                    "select @temp=@c \n return @temp \n END";
                sqlComm = new SqlCommand(sqlcmd, sqlConn);
                try
                {
                    //sqlComm.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlComm.CommandTimeout = 360;
                    sqlComm.ExecuteNonQuery();
                    log.addTrn(sqlComm.CommandText.ToString(), "Query");
                }
                catch (Exception ex)
                {
                    log.addTrn("Failed to install min3 SQL command or min3 already installed" + sqlComm.CommandText.ToString() + " error " + ex + "\n" + ex.StackTrace.ToString(), "Error");
                }
            }

            if (gusersyn.Writeback_AD_checkbox == true || gusersyn.Writeback_DB_checkbox == true)
            {
                // DATABASE writeback

                // Make preperations to pull all data into seperate tables
                // build sql to run to get gmail user nicknames
                // execute data pull for SQL nicknames used in writeback to SQL database
                log.addTrn("Pull SQL data fro writeback of emails", "Info");
                if (gusersyn.Writeback_DB_checkbox == true)
                {
                    if (gusersyn.Writeback_where_clause == "")
                    {
                        sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.Writeback_primary_key + ") AS " + gusersyn.Writeback_primary_key +
                            ", RTRIM(" + gusersyn.Writeback_email_field + ") AS " + gusersyn.Writeback_email_field +
                            " INTO " + sqlNicknamesTable + " FROM " + gusersyn.Writeback_table, sqlConn);
                    }
                    else
                    {
                        sqlComm = new SqlCommand("SELECT DISTINCT RTRIM(" + gusersyn.Writeback_primary_key + ") AS " + gusersyn.Writeback_primary_key +
                            ", RTRIM(" + gusersyn.Writeback_email_field + ") AS " + gusersyn.Writeback_email_field +
                            " INTO " + sqlNicknamesTable + " FROM " + gusersyn.Writeback_table +
                            " WHERE " + gusersyn.Writeback_where_clause, sqlConn);
                    }
                    try
                    {
                        sqlComm.CommandTimeout = 360;
                        sqlComm.ExecuteNonQuery();
                        log.addTrn(sqlComm.CommandText.ToString(), "Query");
                    }
                    catch (Exception ex)
                    {
                        log.addTrn("Failed SQL command " + sqlComm.CommandText.ToString() + " error " + ex + "\n" + ex.StackTrace.ToString(), "Error");
                    }
                }

                // TABLE pulled by command from gmail
                // sqlNicknamesTable::  #sqlNicknamesTable
                //---------------------------------------------
                // gusersyn.writebackPrimaryKey::  soc_sec           xx111111
                // gusersyn.writebackEmailField::  gmail             first.last@domain

                // TABLE pulled by command from AD
                // adNicknamesTable::  #adNicknamesTable
                //---------------------------------------------
                // sAMAccountName                                    xx111111
                // givenName                                         first
                // middleName                                        middle
                // sn                                                last
                // mail                                              first.last@domain

                // TABLE pulled by command from gamil
                // nicknamesFromGmailTable:: #nicknamesFromGmailTable
                //---------------------------------------------
                // gusersyn.Writeback_primary_key:: soc_sec          xx111111
                // nicknames.column[1]:: nickname                    first.last
                // nicknames.column[2]:: email                       first.last@domain

                // TABLE pulled by command from gmail
                // gmailUsersTable:: #gmailUsersTable
                //---------------------------------------------
                // gusersyn.User_StuID:: sAMAccountName              xx111111
                // gusersyn.Fname:: givenname                        first
                // gusersyn.Lname:: sn                               last

                // TABLE generated by sql query from #nicknamesFromGmailTable and #gmailUsersTable
                // loginWithoutNicknamesTable:: #loginWithoutNicknamesTable
                //---------------------------------------------
                // gusersyn.User_StuID:: sAMAccountName              xx111111
                // gusersyn.Fname:: givenname                        first
                // gusersyn.Lname:: sn                               last

                // TABLE created by query to database
                // sqlUsersTable:: #sqlUsersTable
                //---------------------------------------------
                // gusersyn.User_StuID:: SQL or AD                   xx111111
                // gusersyn.User_Fname:: SQL or AD                   first
                // gusersyn.User_Lname:: SQL or AD                   middle
                // gusersyn.User_Mname:: SQL or AD                   last
                // gusersyn.User_password:: SQL or AD                *******

                // TABLE created by query to database
                // nicknamesToUpdateDBTable:: #nicknamesToUpdateDBTable
                //---------------------------------------------
                //nicknames.Columns[0].ColumnName:: gusersyn.Writeback_primary_key :: soc_sec           xx111111
                //nicknames.Columns[2].ColumnName:: email                                               first.last@domain

                // were adding the mail field to the pull fields not so we need to repull
                // keys to pull from database pull are most likely wrong need to hard code appropriate keys
                adPullKeys.Add("sAMAccountName");
                adPullKeys.Add("givenName");
                adPullKeys.Add("middleName");
                adPullKeys.Add("sn");
                adPullKeys.Add("mail");

                // clear our previous data
                log.addTrn("Get AD user data for nickname comparison", "Info");
                adUsers.Clear();
                adUsers = tools.EnumerateUsersInOUDataTable(gusersyn.User_ad_OU, adPullKeys, adNicknamesTable, SearchScope.OneLevel, log);
                tools.Create_Table(adUsers, adNicknamesTable, sqlConn, log);

                // get list of users from gmail this may have changed when we ran the update to test to see if anyone is missing nicknames
                log.addTrn("Get Gmail user data for user comparison", "Info");
                gmailUsers.Clear();
                gmailUsers = tools.Get_Gmail_Users(service, gusersyn, gmailUsersTableWB, log);
                tools.Create_Table(gmailUsers, gmailUsersTableWB, sqlConn, log);
                if (gmailUsers.Rows.Count > 0)
                {
                    // get list of nicknames from gmail
                    log.addTrn("Get Gmail nickname data for nickname comparison", "Info");
                    nicknames.Clear();
                    nicknames = tools.Get_Gmail_Nicknames(service, gusersyn, nicknamesFromGmailTable, log);
                    tools.Create_Table(nicknames, nicknamesFromGmailTable, sqlConn, log);

                    // if we did not get any nicknames there will be problems
                    if (nicknames.Rows.Count > 0)
                    {
                        // check which user do not have a an associated nickname with them
                        // cross reference for null userID's in nickname service.RetrieveAllNicknames table with list of all userlogin userID's from gmail service.RetrieveAllUsers
                        log.addTrn("Query to find users which do not have a nickname", "Info");
                        tools.QueryNotExistsIntoNewTable(gmailUsersTableWB, nicknamesFromGmailTable, loginWithoutNicknamesTable, sqlConn, gusersyn.User_StuID, nicknames.Columns[0].ColumnName, log);

                        // use retrieved list of users without nicknames and check for updates against list of users in main datasource
                        // we dont want ot add nicknames to people wo aren't in the primary datasource
                        // use the datatable from the view/table as the primary data source
                        // this table is generated above during the user account addition and update section
                        // sqlUsersTable will have AD or SQL no matter which we checked, there was only on variable used
                        // Pulls back the account infromation from the add/update section for users without a nickname
                        // loginWithoutNicknamesTable inherits key from from #gmailuserstable during QueryNotExistsIntoNewTable due to only pulling data from the gmailuserstable
                        log.addTrn("Query to cross reference the primary datasource to find the users who need a nickname", "Info");
                        lostNicknames = tools.QueryInnerJoin(sqlUsersTable, loginWithoutNicknamesTable, gusersyn.User_StuID, gusersyn.User_StuID, sqlConn, log);
                        // iterate lostnicknames and create nicknames
                        try
                        {
                            log.addTrn("Creating new nicknames", "Info");
                            while (lostNicknames.Read())
                            {
                                userNickName = tools.GetNewUserNickname(service, lostNicknames[gusersyn.User_StuID.ToString()].ToString(), lostNicknames[gusersyn.User_Fname.ToString()].ToString(), lostNicknames[gusersyn.User_Mname.ToString()].ToString(), lostNicknames[gusersyn.User_Lname.ToString()].ToString(), 0, false);
                                log.addTrn("Added Gmail user " + lostNicknames[gusersyn.User_StuID.ToString()].ToString() + "@" + gusersyn.Admin_domain + " Aliased as " + userNickName + "@" + gusersyn.Admin_domain, "Transaction");
                            }
                        }
                        catch (Exception ex)
                        {
                            log.addTrn("Issue creating new nicknames datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                        }

                        lostNicknames.Close();

                    }
                    // get list of nicknames from gmail it may have changed in the last update.
                    log.addTrn("Get nicknames to compare for who need new nicknames", "Info");
                    nicknames = tools.Get_Gmail_Nicknames(service, gusersyn, nicknamesFromGmailTable2, log);
                    tools.Create_Table(nicknames, nicknamesFromGmailTable2, sqlConn, log);
                    // check if we got nicknames or else it will be a problem
                    if (nicknames.Rows.Count > 0)
                    {

                        // create array lists of fields which match for updating
                        // ID field
                        nicknameKeys.Add(nicknames.Columns[0].ColumnName);
                        nicknameKeys.Add(nicknames.Columns[2].ColumnName);
                        sqlkeys.Add(gusersyn.Writeback_primary_key);
                        sqlkeys.Add(gusersyn.Writeback_email_field);

                        // check against list of nicknames in database
                        if (gusersyn.Writeback_DB_checkbox == true)
                        {

                            // check the table to see if the nicknames match the ones we have there can be more than one nickname per user

                            //select FHC_test2_gmailNicknamesTable.soc_sec, FHC_test2_gmailNicknamesTable.Email
                            //FROM FHC_test2_gmailNicknamesTable INNER JOIN FHC_test2_sqlNicknamesTable
                            //ON FHC_test2_gmailNicknamesTable.soc_sec = FHC_test2_sqlNicknamesTable.soc_sec
                            //where email not in (select email from FHC_test2_gmailnicknamestable)

                            //Filter out the good nicknames

                            // gusersyn.User_Fname:: SQL or AD                   first
                            // gusersyn.User_Lname:: SQL or AD                   middle
                            // gusersyn.User_Mname:: SQL or AD                   last
                            keywordFields.Add(gusersyn.User_Lname);
                            keywordFields.Add(gusersyn.User_Fname);
                            keywordFields.Add(gusersyn.User_Mname);

                            selectFields.Add(nicknames.Columns[2].ColumnName);

                          /*  if (gusersyn.Levenshtein == true)
                            {
                                tools.SelectNicknamesClosestToActualNameIntoNewTable(nicknamesFromGmailTable, sqlUsersTable, nicknames.Columns[0].ColumnName, gusersyn.User_StuID, nicknamesFilteredForDuplicatesTable, selectFields, nicknames.Columns[1].ColumnName, keywordFields, sqlConn, log);
                                if (gusersyn.Levenshtein == true)
                                {
                                    //correct nicknames in Gmail
                                   // tools.setnames(nicknamesFromGmailTable, sqlConn, log);

                                }
                            }
                            else
                            {
                                // Filter nicknames for duplicates into new table */
                                log.addTrn("Query to find most likely nickname", "Info");
                                tools.SelectNicknamesClosestToActualNameIntoNewTable(nicknamesFromGmailTable2, sqlUsersTable, nicknames.Columns[0].ColumnName, gusersyn.User_StuID, nicknamesFilteredForDuplicatesTable, selectFields, nicknames.Columns[1].ColumnName, keywordFields, sqlConn, log);
                            // }
                            // Check filtered nicknames against the sql data to see which emails need updating and put into a table for the next operation
                            log.addTrn("Query to see if most likely nickname matches the primary datasource", "Info");
                            tools.CheckEmailUpdateIntoNewTable(nicknames.Columns[2].ColumnName, gusersyn.Writeback_email_field, nicknamesFilteredForDuplicatesTable, sqlNicknamesTable, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknamesToUpdateDBTable, nicknameKeys, sqlkeys, sqlConn, log);

                            // reset arraylists with just the data fields we don't to be updating the  primary key
                            nicknameKeys.Clear();
                            sqlkeys.Clear();
                            nicknameKeys.Add(nicknames.Columns[2].ColumnName);
                            sqlkeys.Add(gusersyn.Writeback_email_field);

                            adMailUpdateKeys.Add("mail");
                            if (gusersyn.Writeback_transfer_email_checkbox == true)
                            {
                                //UPDATE a1 SET a1.e_mail = a2.gmail FROM address as a1 INNER JOIN address as a2 ON a1.soc_sec = a2.soc_sec where a2.gmail <> ' '

                                nicknameKeysAndTable.Add(gusersyn.Writeback_table + "." + gusersyn.Writeback_email_field); // source table and column
                                sqlkeysAndTable.Add(gusersyn.Writeback_table + "." + gusersyn.Writeback_secondary_email_field); // target table and column

                                //UNTESTED WILL MOST LIKELY FAIL
                                log.addTrn("Query to mass move the primary email field to secondary email field in primary datasource", "Info");
                                tools.Mass_Email_Shift(gusersyn, nicknamesToUpdateDBTable, gusersyn.Writeback_table, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknameKeysAndTable, sqlkeysAndTable, gusersyn.Writeback_where_clause, sqlConn, log);
                            }
                            log.addTrn("Query to mass populate the email field with new aliases", "Info");
                            tools.Mass_Table_update(nicknamesToUpdateDBTable, gusersyn.Writeback_table, nicknames.Columns[0].ColumnName, gusersyn.Writeback_primary_key, nicknameKeys, sqlkeys, gusersyn.Writeback_where_clause, sqlConn, log);
                        }

                        // reset arraylists  for new update check
                        nicknameKeys.Clear();
                        nicknameKeys.Add(nicknames.Columns[0].ColumnName);
                        nicknameKeys.Add(nicknames.Columns[2].ColumnName);
                        // AD fields hard code due to not giving options in interface
                        adMailUpdateKeys.Clear();
                        adMailUpdateKeys.Add("sAMAccountName");
                        adMailUpdateKeys.Add("mail");

                        if (gusersyn.Writeback_AD_checkbox == true)
                        {
                            // find nicknames missing in AD
                            log.addTrn("Query to find AD users that need their nickname updated", "Info");
                            nicknamesToAddToAD = tools.CheckUpdate(nicknamesFilteredForDuplicatesTable, adNicknamesTable, nicknames.Columns[0].ColumnName, adPullKeys[0].ToString(), nicknameKeys, adMailUpdateKeys, sqlConn, log);
                            //nicknamesToAddToAD = tools.QueryNotExistsAllFields(nicknamesFromGmailTable, adNicknamesTable, sqlConn, nicknames.Columns[0].ColumnName, adPullKeys[0].ToString(), log);
                            // interate and update mail fields
                            log.addTrn("Updating nicknames in AD", "Info");
                            try
                            {
                                while (nicknamesToAddToAD.Read())
                                {
                                    userDN = tools.GetObjectDistinguishedName(objectClass.user, returnType.distinguishedName, nicknamesToAddToAD[nicknames.Columns[0].ColumnName].ToString(), dc, log);
                                    tools.SetAttributeValuesSingleString("mail", nicknamesToAddToAD[nicknames.Columns[2].ColumnName.ToString()].ToString(), userDN, log);
                                }
                            }
                            catch (Exception ex)
                            {
                                log.addTrn("Issue writing nickname to AD datareader is null " + ex.Message.ToString() + "\n" + ex.StackTrace.ToString(), "Error");
                            }
                            nicknamesToAddToAD.Close();
                        }

                        // create send as aliases need to get data for users

                        additionalKeys.Clear();
                        additionalKeys.Add(nicknamesFilteredForDuplicatesTable + "." + nicknames.Columns[2].ColumnName + ", ");
                        log.addTrn("Query to find new send as aliases", "Info");
                        sendAsAliases = tools.QueryInnerJoin(sqlUsersTable, nicknamesFilteredForDuplicatesTable, gusersyn.User_StuID, nicknames.Columns[0].ColumnName, additionalKeys, sqlConn, log);
                        tools.CreateSendAs(gusersyn, sendAsAliases, nicknames.Columns[2].ColumnName, nicknames.Columns[2].ColumnName, log);

                    }
                }
            }

            sqlConn.Close();
        }