C# Snippets

Access DB ADODB connection.cs

public static void buildCN()
  {
      cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath;
      try { cn.Open(null, null, null, 0); }
      catch (Exception e) { Console.WriteLine(e.ToString()); }
  }
public static void closeCN(){
  try{ cn.Close(); }
  catch (Exception e) { Console.WriteLine(e.ToString()); }
}

Build List of ServiceNow Tickets.cs

public static void buildTickList()
{
    ticket currTick = null;

    httpCon = (HttpWebRequest)WebRequest.Create(string.Format("https://INSTACE.service-now.com/incident_list.do?JSONv2&sysparm_query=active%3Dtrue%5Estate%3D1%5Eassignment_group%3De6c4d1740a0a3c3700cb5345a015d2cd%5EORassignment_group%3Db352672e0dcf914cb9add2f6fa4a1daf&sysparm_view=")); ;
    httpCon.Credentials = new System.Net.NetworkCredential(Environment.GetEnvironmentVariable("UserName"), password);
    httpCon.Method =  "GET";
    httpCon.KeepAlive = true;
    HttpWebResponse response = (HttpWebResponse)httpCon.GetResponse();
    StreamReader resp = new StreamReader(response.GetResponseStream());
    dynamic records = JsonConvert.DeserializeObject(resp.ReadToEnd());
    string sysID = null;
    newTick = false;

    foreach (ticket tick in allTickets) 
    { 
        tick.New_Ticket = false;
        tick.Active = false;
        foreach (dynamic crec in records.records) { if (crec.sys_id == tick.Sys_ID) { tick.Active = true; } }
    }
    foreach (dynamic rec in records.records)
    {
        if (rec.assignment_group == "e6c4d1740a0a3c3700cb5345a015d2cd" || rec.assignment_group == "b352672e0dcf914cb9add2f6fa4a1daf")
        {
            if (rec.state == "1")
            {
                sysID = rec.sys_id;
                if (allTickets.Count(x => x.Sys_ID.Equals(sysID)) < 1)
                {
                    currTick = new ticket();
                    currTick.Sys_ID = rec.sys_id;
                    currTick.Number = rec.number;
                    currTick.Caller_ID = rec.caller_id;
                    currTick.Category = rec.category;
                    currTick.Configuration_Item = rec.cmdb_ci;
                    currTick.Assigned_To = rec.assigned_to;
                    currTick.Assignment_Group = rec.assignment_group;
                    currTick.Opened = rec.opened_at;
                    currTick.State = rec.state;
                    currTick.Short_Description = rec.short_description;
                    currTick.Description = rec.description;
                    currTick.New_Ticket = true;
                    currTick.Active = true;

                    allTickets.Add(currTick);
                    newTick = true;
                }
            }
        }
    }
    allTickets.Distinct();
    allTickets.RemoveAll(x => x.Active == false);
    
    httpCon.Abort();
}

Build Report from Multiple XML files.cs

static public void checkXML()      
{
    XmlReader rdr;
    XmlDocument doc;
    XmlNodeList ndes;
    string fileName;
    user currUser;
    string[] x;
    string SQLstr;
    object nRecAff = Type.Missing;
    object oParams = Type.Missing;
    ADODB.Command cmd = new ADODB.Command();
    cmd.ActiveConnection = cn;
    foreach (hotel htl in allHotels)
    {
        try
        {
            fileName ="XML FILE DIRECTORY" + htl.HotelID + "_Users.xml";
            rdr = XmlReader.Create(fileName);
            doc = new XmlDocument();
            doc.Load(rdr);

            ndes = doc.SelectNodes("/DATA/LIST_G_USER/G_USER");
            foreach (XmlNode oNode in ndes)
            {
                try
                {
                    currUser = allUsers.FirstOrDefault(u => u.FullName.Equals(oNode.SelectSingleNode("FULL_NAME").InnerText.ToString().Trim()));
                    if (currUser != null)
                    {
                        currUser.CashID = oNode.SelectSingleNode("CASHIER_ID").InnerText.ToString().Trim();
                        x = oNode.SelectSingleNode("PASSWORD_LAST_CHANGE").InnerText.ToString().Trim().Split('T');
                        currUser.LastPass = x[0];
                        Array.Clear(x, 0, x.Count());
                        currUser.GroupList = oNode.SelectSingleNode("USER_GROUPS").InnerText.ToString().Trim();
                        currUser.ScriptMessage = currUser.CashID + "|" + currUser.GroupList + "|" + currUser.LastPass;
                        SQLstr = "UPDATE [" + htl.AuditTable().ToString() + "] SET " + htl.HotelID + "='" + currUser.ScriptMessage + "' WHERE Full_Name='" + currUser.FullName + "';";
                        //Console.WriteLine(SQLstr);
                        cmd.CommandText = SQLstr;
                        cmd.Execute(out nRecAff, ref oParams);
                    }
                }
                catch { }
                currUser = null;
                SQLstr = "";
            }
            doc = null;
        }
        catch (Exception e) { Console.WriteLine(htl.HotelID + "-----" + e.ToString()); }
        //Console.WriteLine(htl.HotelID + " Audit Complete!");
    }
}

Change ServiceNow Ticket.cs

public static void markWorkInProgress()
{
    string printString = "";
    string postText = "{";
    foreach (ticket tick in allTickets)
    {
        if (tick.New_Ticket == true)
        {
            httpCon = (HttpWebRequest)WebRequest.Create(string.Format("https://INSTANCE.service-now.com/incident_list.do?JSONv2&sysparm_query=sys_id=" + tick.Sys_ID + "&sysparm_action=update")); ;
            httpCon.Credentials = new System.Net.NetworkCredential(Environment.GetEnvironmentVariable("UserName"), password);
            httpCon.Method = "POST";
            httpCon.KeepAlive = true;

            postText = "{";
            postText += "\"state\":\"2\"";
            postText += "}";

            using (var stream = new StreamWriter(httpCon.GetRequestStream()))
            {
                stream.Write(postText);
                stream.Flush();
                stream.Close();
            }
            HttpWebResponse resp = (HttpWebResponse)httpCon.GetResponse();

            httpCon.Abort();

            printString = tick.Number;
            printString += " | " + tick.Short_Description;
            printString += " | " + tick.State;
            printString += " | " + tick.Opened;
            printString += " | " + tick.Assignment_Group;
            Console.WriteLine(printString);
            Console.WriteLine();
            printString = "";
        }
    }
}

Get user groups from LDAP.cs

DirectoryEntry ldapConn = new DirectoryEntry(@"LDAP://example.com");
ldapConn.AuthenticationType = AuthenticationTypes.Secure;
DirectorySearcher srch = new DirectorySearcher(ldapConn);
srch.Filter = "(&(cn=" + currUser.GlobalID + "))";
string groupName = "";

SearchResult result = srch.FindOne();

foreach (dynamic prop in result.Properties)
{
    if (prop.Key == "objectclass")
    {
        foreach (var group in result.Properties["memberOf"])
        {
            if (group.ToString().IndexOf("MORFC") > 0 || group.ToString().IndexOf("MOORE") > 0)
            {
                groupName = group.ToString().Replace("CN=", "");
                groupName = groupName.Remove(groupName.IndexOf(","));
                groupName = groupName.Substring(10).Replace(".", "-");
                Console.WriteLine("     " + groupName);
            }
        }
    }
}

srch.Dispose();
ldapConn.Close();

hotel class.cs

public class hotel
{
public string HotelID { get; set; }
public string HotelGrid { get; set; }
public string CashGroup { get; set; }
public string BackOGroup { get; set; }
public string HsscGroup { get; set; }
public string RevGroup { get; set; }
public string AuditTable()
{
    string tbl = "";

    switch (HotelGrid)
    {
        case "Grid5":
            tbl = "Grid5_Audit";
            break;
        case "StandAlone":
            tbl = "ExpressSA_Audit";
            break;
        case "Express":
            tbl = "ExpressSA_Audit";
            break;
        case "Select":
            string[] x = { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m" };
            for (int i = 0; i < x.Length; i++)
            {
                if (HotelID.Substring(0, 1).ToLower() == x[i]) { tbl = "Select_Audit"; break; }
                else { tbl = "Select_Audit2"; }
            }
            Array.Clear(x, 0, x.Length);
            break;
        case "SelectSA":
            tbl = "ExpressSA_Audit";
            break;
        default:
            break;
    }
    return tbl;
}

Import LDAP users to SQL Database.cs

static public void UserImport() {
    object nRecAff = Type.Missing;
    object oParams = Type.Missing;

    try
    {
        ADODB.Command cmd = new ADODB.Command();
        cmd.ActiveConnection = cn;
        cmd.CommandText = "DELETE * FROM All_Users;";
        cmd.Execute(out nRecAff, ref oParams);
    }
    catch (Exception e) { Console.WriteLine(e.ToString()); }

    //Open LDAP Connection to Active Directory and Search for Active Users at MORFC

    DirectoryEntry ldapConn = new DirectoryEntry(@"LDAP://example.com");
    ldapConn.AuthenticationType = AuthenticationTypes.Secure;
    DirectorySearcher srch = new DirectorySearcher(ldapConn);

    srch.Filter = "(&(l=MORFC)(employeeStatus=Active))";

    string[] requiredProperties = new string[] { "cn", "displayName","givenName", "sn", "mobile", "telephoneNumber", "mail"};

    foreach (string property in requiredProperties)
    {
        srch.PropertiesToLoad.Add(property);
    }

    SearchResultCollection MooreUsers = srch.FindAll();
   
    string globalID;
    string dName;
    string fName;
    string lName;
    string acd;
    string pNumber;
    string mail;
    string SQLstr;
    user currUser;
    //Loop through users and insert their info into User Admin DB
    foreach (SearchResult result in MooreUsers){
        currUser = new user();
        try { globalID = result.Properties["cn"][0].ToString(); }
        catch { globalID = null; }
        currUser.GlobalID = globalID;
        try { dName = result.Properties["displayName"][0].ToString(); }
        catch { dName = null; }
        try { fName = result.Properties["givenName"][0].ToString().Replace("\'", "(Chr(39))"); }
        catch { fName = null; }
        try { fName = fName.Replace("'", " "); }
        catch { fName = null; }
        try { lName = result.Properties["sn"][0].ToString().Replace("\'", "(Chr(39))"); }
        catch { lName = null; }
        currUser.FullName = fName + " " + lName;
        try { acd = result.Properties["mobile"][0].ToString(); }
        catch { acd = ""; }
        try { pNumber = result.Properties["telephoneNumber"][0].ToString(); }
        catch { pNumber = ""; }
        try { mail = result.Properties["mail"][0].ToString().Replace("'", " "); }
        catch { mail = ""; }

        try
        {
            if (dName.Contains("(MORFC"))
            {
                SQLstr = "INSERT INTO All_Users (GlobalID, First_Name, Last_Name, ACD, Phone, EMail) VALUES('" + globalID + "', '" + fName + "', '" + lName + "', '" + acd + "', '" + pNumber + "', '" + mail + "');";
                try
                {
                    ADODB.Command cmd = new ADODB.Command();
                    cmd.ActiveConnection = cn;
                    cmd.CommandText = SQLstr;
                    cmd.Execute(out nRecAff, ref oParams);
                }
                catch (Exception e) { Console.WriteLine(e.ToString()); }
                allUsers.Add(currUser);
                Console.WriteLine(SQLstr);
            }
        }
        catch (Exception e) { Console.WriteLine(e.ToString()); }
        globalID = null;
        fName = null;
        lName = null;
        acd = null;
        pNumber = null;
        mail = null;
        SQLstr = null;
    }
}

Run Macros in Access DB.cs

static void accessDb(string flPath)
{
    object oMissing = System.Reflection.Missing.Value;
    Access.Application oAccess = new Access.Application();
    oAccess.Visible = true;
    oAccess.OpenCurrentDatabase(flPath, false, "");
    oAccess.Run("MACRO NAME");
    oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
    oAccess = null;
}

Scrape FIM for user Groups.cs

public static void fimGroups(user currUser)
{
    SHDocVw.InternetExplorerMedium browser = new SHDocVw.InternetExplorerMedium();
    browser.Visible = false;
    browser.Navigate(currUser.FimURL);
    Thread.Sleep(7000);
    browser.Document.GetElementById("ctl00_PlaceHolderMain_EditPerson_uoc_navigate_GroupMembership_linkButton").click();
    Thread.Sleep(7000);
    HtmlAgilityPack.HtmlDocument doc = new HtmlAgilityPack.HtmlDocument();
    string inhtml = browser.Document.body.innerHTML;
    inhtml = inhtml.Replace("ms-alternating", "newListViewRow");
    doc.LoadHtml(inhtml);
    browser.Quit();
    browser = null;
    currUser.fimGroups = new List<string>();

    foreach (HtmlNode ele in doc.DocumentNode.SelectNodes("//tr[@class='newListViewRow']"))
    {
        currUser.fimGroups.Add(ele.Attributes[4].Value.ToString());
    }
    doc = null;
}

Ticket Class.cs

public class ticket
{
    public string Sys_ID { get; set; }
    public string Number { get; set; }
    public string Caller_ID { get; set; }
    public string Category { get; set; }
    public string Configuration_Item { get; set; }
    public string Assigned_To { get; set; }
    public string Assignment_Group { get; set; }
    public string Opened { get; set; }
    public string State { get; set; }
    public string Short_Description { get; set; }
    public string Description { get; set; }
    public string Comments { get; set; }
    public string Work_Notes { get; set; }
    public bool New_Ticket { get; set; }
    public bool Active { get; set; }
}

Update SeatingChart HTML file from Recordset.cs

rs.Open("SELECT * FROM All_Users;", cn);
string flPath =  Environment.GetEnvironmentVariable("Temp") + @"\SeatingChart.html";
HtmlDocument doc = new HtmlDocument();
doc.Load(flPath);
foreach (HtmlNode nde in doc.DocumentNode.SelectNodes("//td"))
{
    if (nde.Id.IndexOf("-user") > 0) { nde.InnerHtml = ""; }
}
while(!rs.EOF){
    uName = rs.Fields[2].Value.ToString().Trim();
    wkSp = rs.Fields[8].Value.ToString().Trim();
    if (wkSp != null) { try { doc.GetElementbyId(wkSp.ToLower() + "-user").InnerHtml = uName; } catch { } }
    rs.MoveNext();
}
rs.Close();
doc.Save(flPath);

User Class.cs

public class user
{
    public string GlobalID { get; set; }
    public string FullName { get; set; }
    public string ACD { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
    public string Wifi { get; set; }
    public string PrintID { get; set; }
    public string UPosition { get; set; }
    public string WorkSpace { get; set; }
    public string FimURL { get; set; }
    public string SysID { get; set; }
    public string GroupList { get; set; }
    public string LastPass { get; set; }
    public string CashID { get; set; }
    public bool PmsAccess { get; set; }
    public string CurrentSpirit { get; set; }
    public string TargetGroups { get; set; }
    public List<string> fimGroups { get; set; }
    public string ScriptMessage { get; set; }
}