public Customer getCustomer(String customerId) { eConnectMethods eConnect = new eConnectMethods(); String address1 = ""; String address2 = ""; Customer result = new Customer(); result.CustomerID = customerId; try { eConnectType myEConnectType = new eConnectType(); RQeConnectOutType myReqType = new RQeConnectOutType(); eConnectOut myeConnectOut = new eConnectOut(); myeConnectOut.ACTION = 1; myeConnectOut.DOCTYPE = "Customer"; myeConnectOut.OUTPUTTYPE = 2; myeConnectOut.INDEX1FROM = customerId; myeConnectOut.INDEX1TO = customerId; myeConnectOut.FORLIST = 1; // Add the eConnectOut XML node object to the RQeConnectOutType schema object myReqType.eConnectOut = myeConnectOut; // Add the RQeConnectOutType schema object to the eConnect document object RQeConnectOutType[] myReqOutType = { myReqType }; myEConnectType.RQeConnectOutType = myReqOutType; // Serialize the eConnect document object to a memory stream MemoryStream myMemStream = new MemoryStream(); XmlSerializer mySerializer = new XmlSerializer(myEConnectType.GetType()); mySerializer.Serialize(myMemStream, myEConnectType); myMemStream.Position = 0; // Load the serialized eConnect document object into an XML document object XmlTextReader xmlreader = new XmlTextReader(myMemStream); XmlDocument myXmlDocument = new XmlDocument(); myXmlDocument.Load(xmlreader); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = eConnect.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, myXmlDocument.OuterXml); XmlDocument resultDocument = new XmlDocument(); resultDocument.LoadXml(reqDoc); XmlNodeList customerNodeList = resultDocument.GetElementsByTagName("Customer"); if (customerNodeList.Count == 0) return null; else { //Here we have retrieved the customer document foreach (XmlNode node in customerNodeList[0]) { if (node.Name.Equals("ADDRESS1")) address1 = node.InnerText; if (node.Name.Equals("ADDRESS2")) address2 = node.InnerText; else if (node.Name.Equals("ADRSCODE")) result.CustomerAddress.AddressCode = node.InnerText; else if (node.Name.Equals("CITY")) result.CustomerAddress.City = node.InnerText; else if (node.Name.Equals("CNTCPRSN")) result.CustomerAddress.AddressContact = node.InnerText; else if (node.Name.Equals("COUNTRY")) result.CustomerAddress.Country = node.InnerText; else if (node.Name.Equals("CUSTCLAS")) result.CustomerClass = node.InnerText; else if (node.Name.Equals("CUSTNAME")) result.CustomerName = node.InnerText; else if (node.Name.Equals("PHONE1")) result.CustomerAddress.PhoneNumber1 = node.InnerText; else if (node.Name.Equals("PHONE2")) result.CustomerAddress.PhoneNumber2 = node.InnerText; else if (node.Name.Equals("FAX")) result.CustomerAddress.FaxNumber = node.InnerText; else if (node.Name.Equals("STATE")) result.CustomerAddress.State = node.InnerText; else if (node.Name.Equals("ZIP")) result.CustomerAddress.Zipcode = node.InnerText; else if (node.Name.Equals("STMTNAME")) result.StatementName = node.InnerText; else if (node.Name.Equals("SHRTNAME")) result.ShortName = node.InnerText; else if (node.Name.Equals("PRBTADCD")) result.BillTo = node.InnerText; else if (node.Name.Equals("PRSTADCD")) result.ShipTo = node.InnerText; else if (node.Name.Equals("STADDRCD")) result.StatementTo = node.InnerText; else if (node.Name.Equals("USERDEF1")) result.Type = node.InnerText; else if (node.Name.Equals("USERDEF2")) result.StudentStatus = node.InnerText; else if (node.Name.Equals("INACTIVE")) if (node.InnerText.Equals("1")) result.Inactive = true; } } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } result.CustomerAddress.AddressString = address1 + address2; return result; }
private void btnSend_Click(object sender, System.EventArgs e) { eConnectMethods eConnect = new eConnectMethods(); XmlDocument myXmlDoc = new XmlDocument(); XmlNode eConnectProcessInfoOutgoing; try { checkBox1.Checked = false; myXmlDoc.LoadXml(txtXml.Text); eConnectProcessInfoOutgoing = myXmlDoc.SelectSingleNode("//Outgoing"); if ((eConnectProcessInfoOutgoing == null) || (string.IsNullOrEmpty(eConnectProcessInfoOutgoing.InnerText) == true)) { eConnect.eConnect_EntryPoint(txtConnString.Text, EnumTypes.ConnectionStringType.SqlClient, myXmlDoc.OuterXml, EnumTypes.SchemaValidationType.None, string.Empty); this.txtResults.Text = "Message successfully entered into the BackOffice."; } else { if (eConnectProcessInfoOutgoing.InnerText == "TRUE") { this.txtResults.Text = eConnect.eConnect_Requester(txtConnString.Text, EnumTypes.ConnectionStringType.SqlClient, myXmlDoc.OuterXml); } } } catch (Exception ex) { this.txtResults.Text = ex.ToString(); } finally { eConnect.Dispose(); } }
static void Main() { try { // Create an eConnect document type object eConnectType myEConnectType = new eConnectType(); // Create a RQeConnectOutType schema object RQeConnectOutType myReqType = new RQeConnectOutType(); // Create an eConnectOut XML node object eConnectOut myeConnectOut = new eConnectOut(); // Populate the eConnectOut XML node elements myeConnectOut.ACTION = 1; myeConnectOut.DOCTYPE = "Customer"; myeConnectOut.OUTPUTTYPE = 2; myeConnectOut.INDEX1FROM = "AARONFIT0001"; myeConnectOut.INDEX1TO = "AARONFIT0001"; myeConnectOut.FORLIST = 1; // Add the eConnectOut XML node object to the RQeConnectOutType schema object myReqType.eConnectOut = myeConnectOut; // Add the RQeConnectOutType schema object to the eConnect document object RQeConnectOutType [] myReqOutType = {myReqType}; myEConnectType.RQeConnectOutType = myReqOutType; // Serialize the eConnect document object to a memory stream MemoryStream myMemStream = new MemoryStream(); XmlSerializer mySerializer = new XmlSerializer(myEConnectType.GetType()); mySerializer.Serialize(myMemStream, myEConnectType); myMemStream.Position = 0; // Load the serialized eConnect document object into an XML document object XmlTextReader xmlreader = new XmlTextReader(myMemStream); XmlDocument myXmlDocument = new XmlDocument(); myXmlDocument.Load(xmlreader); // Create a connection string to specify the Microsoft Dynamics GP server and database // Change the data source and initial catalog to specify your server and database string sConnectionString = @"data source=MYSERVER;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = requester.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, myXmlDocument.OuterXml); // Display the result of the eConnect_Requester method call Console.Write(reqDoc); } catch (Exception ex) {// Dislay any errors that occur to the console Console.Write(ex.ToString()); } }
/////// //// GPQueOverLoad to Just get One Employee public XElement GPQue(string employeeID) { string pasheet; var xpasheet = new XElement("eConnect", new XElement("RQeConnectOutType", new XElement("eConnectProcessInfo", new XElement("Outgoing", "TRUE"), new XElement("MessageID", "Employee")), new XElement("eConnectOut", new XElement("DOCTYPE", "Employee"), new XElement("INDEX1FROM", employeeID), new XElement("INDEX1TO", employeeID), new XElement("OUTPUTTYPE", "2"), new XElement("FORLOAD", "0"), new XElement("FORLIST", "1"), new XElement("ACTION", "0"), new XElement("ROWCOUNT", "1"), new XElement("REMOVE", "0")))); pasheet = xpasheet.ToString(); // Create a connection string to specify the Microsoft Dynamics GP server and database // Change the data source and initial catalog to specify your server and database string sConnectionString = @"data source=sc-gpd;initial catalog=TMI;integrated security=SSPI;persist security info=False;packet size=4096"; // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = requester.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, pasheet); // Display the result of the eConnect_Requester method call //Console.Write(reqDoc); XElement blob = XElement.Parse(reqDoc); return(blob); }
/// <summary> /// Function to Retreive Data from GP Tables using eConnect, Return a string in XML Format /// </summary> /// <param name="DocumentType">Purchase_Order_Transaction, Sales_Order_Transaction</param> /// <param name="fromShadowTable">Direct From GP Tables or From EConnect Out</param> /// <param name="OutputType">0=List,1=Master document, 2=Complete document,3=Remove only</param> /// <param name="Action">Use this element when you request data from the shadow table. The value controls the type of data that is returned. /// 0=All documents /// 1=Insertions /// 2=Updates /// 3=Deletions /// 4=Returns all insertions and updates as separate documents for each item. /// 5=Combines all insertions and updates into one document for each item </param> /// <param name="WhereCondition">Custom Condition</param> /// <returns></returns> public static string RetreiveData(string DocumentType, bool fromShadowTable, int OutputType, int Action, string WhereCondition, bool RemoveShadow) { // Create a connection string to specify the Microsoft Dynamics GP server and database //string cnnString = "data source=(local);initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = "data source=WEBMASTERNT02;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = "Data Source=192.168.1.4;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = ConfigurationManager.AppSettings["GPCnnString"]; try { // Create an eConnect document type object eConnectType myEConnectType = new eConnectType(); // Create a RQeConnectOutType schema object RQeConnectOutType myReqType = new RQeConnectOutType(); // Create an eConnectOut XML node object eConnectOut myeConnectOut = new eConnectOut(); // Populate the eConnectOut XML node elements myeConnectOut.ACTION = Action; myeConnectOut.DOCTYPE = DocumentType; myeConnectOut.OUTPUTTYPE = OutputType; myeConnectOut.FORLIST = fromShadowTable ? 0 : 1; myeConnectOut.REMOVE = RemoveShadow ? 1 : 0; myeConnectOut.WhereClause = WhereCondition; // Add the eConnectOut XML node object to the RQeConnectOutType schema object myReqType.eConnectOut = myeConnectOut; // Add the RQeConnectOutType schema object to the eConnect document object RQeConnectOutType[] myReqOutType = { myReqType }; myEConnectType.RQeConnectOutType = myReqOutType; // Serialize the eConnect document object to a memory stream MemoryStream myMemStream = new MemoryStream(); XmlSerializer mySerializer = new XmlSerializer(myEConnectType.GetType()); mySerializer.Serialize(myMemStream, myEConnectType); myMemStream.Position = 0; // Load the serialized eConnect document object into an XML document object XmlTextReader xmlreader = new XmlTextReader(myMemStream); XmlDocument myXmlDocument = new XmlDocument(); myXmlDocument.Load(xmlreader); // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); //string outerXml = myXmlDocument.OuterXml; //int rem = 0x02; //outerXml = outerXml.Replace((char)rem, ' '); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data return(requester.eConnect_Requester(FactoryCompany.ErpConnection.CnnString, EnumTypes.ConnectionStringType.SqlClient, myXmlDocument.OuterXml)); // outerXml); } catch (eConnectException ex) { // Dislay any errors that occur to the console throw new Exception(ex.Message); } catch (Exception e) { // Dislay any errors that occur to the console throw new Exception(e.Message); } }
/// <summary> /// Function to Retreive Data from GP Tables using eConnect, Return a string in XML Format /// </summary> /// <param name="DocumentType">Purchase_Order_Transaction, Sales_Order_Transaction</param> /// <param name="fromShadowTable">Direct From GP Tables or From EConnect Out</param> /// <param name="OutputType">0=List,1=Master document, 2=Complete document,3=Remove only</param> /// <param name="Action">Use this element when you request data from the shadow table. The value controls the type of data that is returned. /// 0=All documents /// 1=Insertions /// 2=Updates /// 3=Deletions /// 4=Returns all insertions and updates as separate documents for each item. /// 5=Combines all insertions and updates into one document for each item </param> /// <param name="WhereCondition">Custom Condition</param> /// <returns></returns> public static string RetreiveData(string DocumentType, bool fromShadowTable, int OutputType, int Action, string WhereCondition, bool RemoveShadow) { // Create a connection string to specify the Microsoft Dynamics GP server and database //string cnnString = "data source=(local);initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = "data source=WEBMASTERNT02;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = "Data Source=192.168.1.4;initial catalog=TWO;integrated security=SSPI;persist security info=False;packet size=4096"; //string cnnString = ConfigurationManager.AppSettings["GPCnnString"]; try { // Create an eConnect document type object eConnectType myEConnectType = new eConnectType(); // Create a RQeConnectOutType schema object RQeConnectOutType myReqType = new RQeConnectOutType(); // Create an eConnectOut XML node object eConnectOut myeConnectOut = new eConnectOut(); // Populate the eConnectOut XML node elements myeConnectOut.ACTION = Action; myeConnectOut.DOCTYPE = DocumentType; myeConnectOut.OUTPUTTYPE = OutputType; myeConnectOut.FORLIST = fromShadowTable ? 0 : 1; myeConnectOut.REMOVE = RemoveShadow ? 1 : 0; myeConnectOut.WhereClause = WhereCondition; // Add the eConnectOut XML node object to the RQeConnectOutType schema object myReqType.eConnectOut = myeConnectOut; // Add the RQeConnectOutType schema object to the eConnect document object RQeConnectOutType[] myReqOutType = { myReqType }; myEConnectType.RQeConnectOutType = myReqOutType; // Serialize the eConnect document object to a memory stream MemoryStream myMemStream = new MemoryStream(); XmlSerializer mySerializer = new XmlSerializer(myEConnectType.GetType()); mySerializer.Serialize(myMemStream, myEConnectType); myMemStream.Position = 0; // Load the serialized eConnect document object into an XML document object XmlTextReader xmlreader = new XmlTextReader(myMemStream); XmlDocument myXmlDocument = new XmlDocument(); myXmlDocument.Load(xmlreader); // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); //string outerXml = myXmlDocument.OuterXml; //int rem = 0x02; //outerXml = outerXml.Replace((char)rem, ' '); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data return requester.eConnect_Requester(FactoryCompany.ErpConnection.CnnString, EnumTypes.ConnectionStringType.SqlClient, myXmlDocument.OuterXml); // outerXml); } catch (eConnectException ex) { // Dislay any errors that occur to the console throw new Exception(ex.Message); } catch (Exception e) { // Dislay any errors that occur to the console throw new Exception(e.Message); } }
public Customer getCustomer(String customerId) { eConnectMethods eConnect = new eConnectMethods(); String address1 = ""; String address2 = ""; Customer result = new Customer(); result.CustomerID = customerId; try { eConnectType myEConnectType = new eConnectType(); RQeConnectOutType myReqType = new RQeConnectOutType(); eConnectOut myeConnectOut = new eConnectOut(); myeConnectOut.ACTION = 1; myeConnectOut.DOCTYPE = "Customer"; myeConnectOut.OUTPUTTYPE = 2; myeConnectOut.INDEX1FROM = customerId; myeConnectOut.INDEX1TO = customerId; myeConnectOut.FORLIST = 1; // Add the eConnectOut XML node object to the RQeConnectOutType schema object myReqType.eConnectOut = myeConnectOut; // Add the RQeConnectOutType schema object to the eConnect document object RQeConnectOutType[] myReqOutType = { myReqType }; myEConnectType.RQeConnectOutType = myReqOutType; // Serialize the eConnect document object to a memory stream MemoryStream myMemStream = new MemoryStream(); XmlSerializer mySerializer = new XmlSerializer(myEConnectType.GetType()); mySerializer.Serialize(myMemStream, myEConnectType); myMemStream.Position = 0; // Load the serialized eConnect document object into an XML document object XmlTextReader xmlreader = new XmlTextReader(myMemStream); XmlDocument myXmlDocument = new XmlDocument(); myXmlDocument.Load(xmlreader); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = eConnect.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, myXmlDocument.OuterXml); XmlDocument resultDocument = new XmlDocument(); resultDocument.LoadXml(reqDoc); XmlNodeList customerNodeList = resultDocument.GetElementsByTagName("Customer"); if (customerNodeList.Count == 0) { return(null); } else { //Here we have retrieved the customer document foreach (XmlNode node in customerNodeList[0]) { if (node.Name.Equals("ADDRESS1")) { address1 = node.InnerText; } if (node.Name.Equals("ADDRESS2")) { address2 = node.InnerText; } else if (node.Name.Equals("ADRSCODE")) { result.CustomerAddress.AddressCode = node.InnerText; } else if (node.Name.Equals("CITY")) { result.CustomerAddress.City = node.InnerText; } else if (node.Name.Equals("CNTCPRSN")) { result.CustomerAddress.AddressContact = node.InnerText; } else if (node.Name.Equals("COUNTRY")) { result.CustomerAddress.Country = node.InnerText; } else if (node.Name.Equals("CUSTCLAS")) { result.CustomerClass = node.InnerText; } else if (node.Name.Equals("CUSTNAME")) { result.CustomerName = node.InnerText; } else if (node.Name.Equals("PHONE1")) { result.CustomerAddress.PhoneNumber1 = node.InnerText; } else if (node.Name.Equals("PHONE2")) { result.CustomerAddress.PhoneNumber2 = node.InnerText; } else if (node.Name.Equals("FAX")) { result.CustomerAddress.FaxNumber = node.InnerText; } else if (node.Name.Equals("STATE")) { result.CustomerAddress.State = node.InnerText; } else if (node.Name.Equals("ZIP")) { result.CustomerAddress.Zipcode = node.InnerText; } else if (node.Name.Equals("STMTNAME")) { result.StatementName = node.InnerText; } else if (node.Name.Equals("SHRTNAME")) { result.ShortName = node.InnerText; } else if (node.Name.Equals("PRBTADCD")) { result.BillTo = node.InnerText; } else if (node.Name.Equals("PRSTADCD")) { result.ShipTo = node.InnerText; } else if (node.Name.Equals("STADDRCD")) { result.StatementTo = node.InnerText; } else if (node.Name.Equals("USERDEF1")) { result.Type = node.InnerText; } else if (node.Name.Equals("USERDEF2")) { result.StudentStatus = node.InnerText; } else if (node.Name.Equals("INACTIVE")) { if (node.InnerText.Equals("1")) { result.Inactive = true; } } } } } catch (Exception ex) { Console.WriteLine(ex.ToString()); } result.CustomerAddress.AddressString = address1 + address2; return(result); }
private void button1_Click(object sender, EventArgs e) { internalCode = textBox1.Text; externallCode = textBox2.Text; // MessageBox.Show(socialSecurityRequest.ToString()); try { string pasheet; var xpasheet = new XElement("eConnect", new XElement("RQeConnectOutType", new XElement("eConnectProcessInfo", new XElement("Outgoing", "TRUE"), new XElement("MessageID", "Employee")), new XElement("eConnectOut", new XElement("DOCTYPE", "Employee"), new XElement("OUTPUTTYPE", "2"), new XElement("FORLOAD", "0"), new XElement("FORLIST", "1"), new XElement("ACTION", "0"), new XElement("ROWCOUNT", "4000"), new XElement("REMOVE", "0")))); pasheet = xpasheet.ToString(); // Create a connection string to specify the Microsoft Dynamics GP server and database // Change the data source and initial catalog to specify your server and database string sConnectionString = @"data source=VM-GPD01;initial catalog=TMI;integrated security=SSPI;persist security info=False;packet size=4096"; // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = requester.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, pasheet); // Display the result of the eConnect_Requester method call //Console.Write(reqDoc); XElement blob = XElement.Parse(reqDoc); //Make Dictionary List of All Departments; var deptDictionary = new Dictionary<string, string>() { {"ACCT","25-" + internalCode +"-10-110-01"}, {"ADMIN","25-" + internalCode +"-10-140-01"}, {"AMBO","25-" + internalCode +"-40-210-06"}, {"AMID","25-" + internalCode +"-30-210-01"}, {"AMSC","25-" + internalCode +"-20-210-01"}, {"ARCMGT","25-" + externallCode +"-80-900-01"}, {"CNTADM","25-" + internalCode +"-10-135-01"}, {"CNTAVD","25-" + externallCode +"-20-800-01"}, {"CNTRBO","25-" + externallCode +"-40-500-06"}, {"CNTRCO","25-" + externallCode +"-20-500-08"}, {"CNTRCT","25-" + externallCode +"-20-500-01"}, {"CNTRID","25-" + externallCode +"-20-500-06"}, {"CNTRMN","25-" + externallCode +"-20-500-07"}, {"CNTRNC","25-" + externallCode +"-40-500-03"}, {"CNTRSF","25-" + externallCode +"-20-500-05"}, {"CNTRTX","25-" + externallCode +"-20-500-03"}, {"DMBO","25-" + internalCode +"-20-215-06"}, {"EXEC","25-" + internalCode +"-10-100-01"}, {"EXECEN","25-" + internalCode +"-10-100-04"}, {"FNANCE","25-" + internalCode +"-10-130-01"}, {"HR","25-" + internalCode +"-10-120-01"}, {"HR ID","25-" + internalCode +"-12-120-06"}, {"INFRBO","25-" + externallCode +"-30-700-06"}, {"INPRBO","25-" + internalCode +"-30-290-06"}, {"INSRBO","25-" + externallCode +"-30-300-06"}, {"INSRSC","25-" + externallCode +"-30-300-01"}, {"MGDEXC","25-" + internalCode +"-30-280-01"}, {"MIS","25-" + internalCode +"-10-160-01"}, {"MKTSAL","25-" + internalCode +"-20-205-01"}, {"MRKT","25-" + internalCode +"-10-170-01"}, {"OCIOSC","25-" + externallCode +"-60-800-01"}, {"OPEXEC","25-" + internalCode +"-10-139-01"}, {"PRJMGT","25-" + internalCode +"-20-229-01"}, {"RCRTBO","25-" + internalCode +"-10-220-06"}, {"RCRTEN","25-" + internalCode +"-10-220-04"}, {"RCRTM","25-" + internalCode +"-20-220-01"}, {"RCRTME","25-" + internalCode +"-10-228-01"}, {"RCRTU","25-" + internalCode +"-10-222-01"}, {"RCRUIT","25-" + internalCode +"-10-220-01"}, {"RMBO","25-" + internalCode +"-10-222-06"}, {"SALEBO","25-" + internalCode +"-40-200-06"}, {"SALEMG","25-" + internalCode +"-20-209-01"}, {"SALES","25-" + internalCode +"-20-200-01"}, {"SMSC","25-" + internalCode +"-20-235-01"}, {"TRANBO","25-" + externallCode +"-30-310-06"}, {"TSG","25-" + internalCode +"-20-230-01"}, {"SALEEN","25-" + internalCode +"-20-234-01"}, {"TSGMGS","25-" + externallCode +"-60-600-01"}, {"CNTRNY","25-" + externallCode +"-20-500-02"}, {"","MISSING"} }; //cool stuff down here int queryCount = blob.Elements("eConnect").Elements("Employee").Count(); string[,] employeeList = new string[queryCount, 10]; int rowCount = 0; int columnCount = 0; //Console.WriteLine(blob.ToString()); foreach (XElement employee in blob.Elements("eConnect").Elements("Employee")) { if (employee.Element("INACTIVE").Value == "0") { string mName; if (employeeIDRequest == true) { employeeList[rowCount, columnCount] = employee.Element("FRSTNAME").Value + " " + employee.Element("LASTNAME").Value; columnCount += 1; } if (employeeIDRequest == true) { employeeList[rowCount, columnCount] = employee.Element("FRSTNAME").Value + " " + employee.Element("LASTNAME").Value; columnCount += 1; } if (lastFirstMIRequest == true) { if (employee.Element("MIDLNAME").Value == "") { mName = ""; } else { mName = employee.Element("MIDLNAME").Value.Substring(0, 1) + "."; } employeeList[rowCount, columnCount] = employee.Element("LASTNAME").Value + ", " + employee.Element("FRSTNAME").Value + " " + mName; columnCount += 1; } employeeList[rowCount, columnCount] = employee.Element("DEPRTMNT").Value; columnCount += 1; if (socialSecurityRequest == true) { Func<string, string> socialize = s => { if (employee.Element("SOCSCNUM").Value == null) { s = ""; return s; } else { s = employee.Element("SOCSCNUM").Value; s = s[0] + s[1] + s[2] + "-" + s[3] + s[4] + "-" + s[5] + s[6] + s[7] + s[8]; return s; } }; employeeList[rowCount, columnCount] = socialize(employee.Element("SOCSCNUM").Value); columnCount += 1; } try { employeeList[rowCount, columnCount] = deptDictionary[employee.Element("DEPRTMNT").Value]; columnCount += 1; } catch { employeeList[rowCount, columnCount] = "MISSING"; columnCount += 1; } rowCount += 1; columnCount = 0; } } //doing excel stuff here Microsoft.Office.Interop.Excel.Application oXL; Microsoft.Office.Interop.Excel._Workbook oWB; Microsoft.Office.Interop.Excel._Worksheet oSheet; Microsoft.Office.Interop.Excel.Range oRng; oXL = new Microsoft.Office.Interop.Excel.Application(); oXL.Visible = true; oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value)); oSheet = (Excel._Worksheet)oWB.ActiveSheet; oSheet.Cells[1, 1] = "EMPLOYEE ID"; oSheet.Cells[1, 2] = "THI FORMAT NAME"; oSheet.Cells[1, 3] = "Department"; oSheet.Cells[1, 4] = "Code"; string endCornerSheet = "G" + (queryCount + 1).ToString(); Excel.Range employeeRange = oSheet.Range["A2", endCornerSheet]; employeeRange.Value2 = employeeList; //oRng = oSheet.get_Range("A2", endCornerSheet).Value2 = employeeRange; oRng = oSheet.get_Range("A2", endCornerSheet).Value2 = employeeList; } catch (Exception ex) {// Dislay any errors that occur to the console // MessageBox.Show(ex.ToString()); } }
/////// //// GPQueOverLoad to Just get One Employee public XElement GPQue(string employeeID) { string pasheet; var xpasheet = new XElement("eConnect", new XElement("RQeConnectOutType", new XElement("eConnectProcessInfo", new XElement("Outgoing", "TRUE"), new XElement("MessageID", "Employee")), new XElement("eConnectOut", new XElement("DOCTYPE", "Employee"), new XElement("INDEX1FROM", employeeID), new XElement("INDEX1TO", employeeID), new XElement("OUTPUTTYPE", "2"), new XElement("FORLOAD", "0"), new XElement("FORLIST", "1"), new XElement("ACTION", "0"), new XElement("ROWCOUNT", "1"), new XElement("REMOVE", "0")))); pasheet = xpasheet.ToString(); // Create a connection string to specify the Microsoft Dynamics GP server and database // Change the data source and initial catalog to specify your server and database string sConnectionString = @"data source=sc-gpd;initial catalog=TMI;integrated security=SSPI;persist security info=False;packet size=4096"; // Create an eConnectMethods object eConnectMethods requester = new eConnectMethods(); // Call the eConnect_Requester method of the eConnectMethods object to retrieve specified XML data string reqDoc = requester.eConnect_Requester(sConnectionString, EnumTypes.ConnectionStringType.SqlClient, pasheet); // Display the result of the eConnect_Requester method call //Console.Write(reqDoc); XElement blob = XElement.Parse(reqDoc); return blob; }