Get Results of Epicor BAQ as DataSet in C#

One of the easiest, and fastest ways to get data out of Epicor is to use C# and the Epicor client library assemblies. This article provides sample code to export the results of a business activity query created using the BAQ Designer into a C# .Net framework dataset.

Step 1 - Add References
In you project, at a minimum you will need to reference the following assemblies:

-Epicor.Mfg.BO.DynamicQuery
-Epicor.Mfg.IF.IDynamicQuery
-Epicor.Mfg.Core.BLConnectionPool
-Epicor.Mfg.Lib.ISessionMod
-Epicor.Mfg.Lib.SessionMod


DynamicQuery is the business object to manage BAQs (it replaces QueryDesignDataSet in Vantage). BLConnectionPool & SessionMod are necessary to explicitly set the company for the session. This is important, because otherwise the company will be whatever company was last selected for the user account used to create the session, which can yield unpredictable results.

Step 2 - Add class to create business object
The following is my class to create Epicor business objects for me:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using Epicor.Mfg.Core;
using Epicor.Mfg.Proxy;
using Epicor.Mfg.BO;
using Epicor.Mfg.Lib;

namespace ExecuteDynamicQuery
{
public class BOFactory
{
private BLConnectionPool m_cnVantage = null;
private static string m_strKeyVantageServer = "VantageServer";
private static string m_strKeyVantagePort = "VantagePort";
private string company;
private string companyName;
private string plantID;
private string plantName;
private string workstationID;
private string workstationDescription;
private string employeeID;
private string countryGroupCode;
private string countryCode;

public BOFactory(string company)
{
this.company = company;
string user = ConfigurationSettings.AppSettings["UserName"];
string password = ConfigurationSettings.AppSettings["Password"];

m_cnVantage = new BLConnectionPool(user, password, "AppServerDC://"
+ ConfigurationSettings.AppSettings[m_strKeyVantageServer] + ":"
+ ConfigurationSettings.AppSettings[m_strKeyVantagePort]);

SetCompany(company);
}

///

/// Returns the DyanmicQuery object which manages BAQs
///

///
public Epicor.Mfg.BO.DynamicQuery GetDynamicQueryBO()
{
return new Epicor.Mfg.BO.DynamicQuery(m_cnVantage);
}

///

/// returns the session mod
///

///
public Epicor.Mfg.Lib.SessionMod GetSessionMod()
{
SessionMod sessionMod = new SessionMod(m_cnVantage);
return sessionMod;
}

///

/// changes the company associated with the current session
///

///
public void SetCompany(string company)
{
this.company = company;
Epicor.Mfg.Lib.SessionMod sessionMod = GetSessionMod();

sessionMod.SetCompany(company,
out companyName, out plantID, out plantName, out workstationID, out workstationDescription,
out employeeID, out countryGroupCode, out countryCode);
}
}
}

Step 3 - Add the call to your dynamic query

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ExecuteDynamicQuery
{
class Program
{
static void Main(string[] args)
{
string company = "12";
string queryID = "12-MyQuery";
string orderNum = "726250";

BOFactory factory = new BOFactory(company);
var dynamicQuery = factory.GetDynamicQueryBO();

// get query
var queryDesignDataSet = dynamicQuery.GetByID(queryID);

// get only specific order
// QueryWhereItem is a criteria on OrderHed table
queryDesignDataSet.QueryWhereItem[0]["RValue"] = orderNum;

// execute query
DataSet ds = dynamicQuery.Execute(queryDesignDataSet);

// print results
for (int i = 0; i < ds.Tables[0].Columns.Count; i++ )
{
Console.Write(ds.Tables[0].Rows[0][i].ToString());
}
}
}
}


Above, I'm passing in an order number as the constant value to a criteria that already exists on the OrderHed table in the design of the query.