Run Updateable BAQ from customisation [C#]

(Richard Hodges) #1

Hi all

I’ve done some research online and I don’t think what I want to do is possible but I wanted to double check with you guys before I wrote it off.

I’ve currently got a very simple BAQ running in a Customization that returns a SELECT statement and apply’s it to an ultragrid.

This is the code I’ve used:

DynamicQueryAdapter dynamicQueryAdapter = new DynamicQueryAdapter(oTrans);
QueryExecutionDataSet queryExecutionDataSet = dynamicQueryAdapter.GetQueryExecutionParametersByID(“selectBAQ”);
queryExecutionDataSet.ExecutionParameter.AddExecutionParameterRow(“CompanyID”, companyTextBox.Text, “nvarchar”, false, Guid.Empty, “A”);
dynamicQueryAdapter.ExecuteByID(“selectBAQ”, queryExecutionDataSet);
baqUltraGrid.DataSource = dynamicQueryAdapter.QueryResults.Tables[“Results”];

What I’m wondering is, is there a way to run an UPDATE BAQ statement using the DynamicQueryAdapter object (or anything else in the customisations code)?

I’ve got an updateable BAQ set up and when I run the Update in the analyse tab it does work, however when I look at the query phrase in the General tab it’s still a SELECT statement so I’m not sure how I would go about using it in a customisation.

Is it possible to run an UPDATE statement from a BAQ in a similar manner to the one above (so what would happen is I would click a button and then the BAQ would run the UPDATE statement for now, later on I’d add parameters and a textbox for the updated value) or is that not possible?

The reason I’m using a BAQ and not a SqlCommand with the SQL passed in as a string (where I could just write the UPDATE query myself) is because I’m testing the runtime of BAQ’s, so the UPDATE has to be ran through a BAQ.

Many thanks

(Jose C Gomez) #2

Hi @Rik first, you should NEVER do the above. Never in Epicor should you write a SQL Command and or a SQL update command. Everything you do should be via the BOs or UBAQs. Running SQL violates your Epicor support agreement and it is a quick way to get into a lot of pain.

Yes you can run an updatable BAQ from code all you need to do is run a Trace on the BAQ Designer and see what it does / replicate on your code.

(Richard Hodges) #3

Thanks for letting me know, I haven’t done any inline SQL commands yet so I’ll avoid doing that.

I’ve actually solved the issue now but I had no idea you could run a trace on the BAQ Designer too, that’s a good trick to know thanks for your help!


(Jose C Gomez) #4

How did you solve it?


(Richard Hodges) #5

Used a DynamicQueryAdapter to run the query then updated the DynamicQueryAdapter’s QueryResults “Results” table with what I needed updating and then ran the DynamicQueryAdapter’s Update method.

(Linda Larsen) #6

@Rik, any chance you can provide the code you got working? I’m looking to do the same thing and haven’t been able to find any good examples of updatable BAQs within UI customization (C#).

(Chris Conn) #7

An example:

DynamicQueryAdapter dqa = new DynamicQueryAdapter(oTrans);
QueryExecutionDataSet qeds = dqa.GetQueryExecutionParametersByID("Printer_By_Company_CC");
if(dqa.QueryResults.Tables["Results"].Rows.Count > 0)
//SET TO GRID HERE........
///prntr = dqa.QueryResults.Tables["Results"].Rows[0]["SysPrinter_NetworkPath"].ToString();
PrinterGrid.DataSource = dqa.QueryResults;

(Linda Larsen) #8

@Chris_Conn, thank you for the example. Populating the grid with the query results is working fine.
How do I modify the code to save any changes made to the data in the grid? The BAQ is already updatable, but I am not sure how to initiate the update from the customization. Any suggestions?

(Daryl Hewison) #9

If you use the ExecuteByID method on DynamicQuery then the BAQ won’t be updatable in your code.

Here’s a version of what we use:

		if (baqName != string.Empty)
			if (!gotBAQ)
				if (adptr.GetByID(baqName)) { gotBAQ = true; }
			if (updateable)
				if (!(ds != null)) { ds = adptr.DynamicQueryData; }
				if (ds.DynamicQuery.Rows.Count == 0)
					Ice.BO.DynamicQueryDataSet dsQDesign = adptr.QueryDesignData;
					DataRow targetRow;
					foreach (DataTable table in ds.Tables)
						foreach (DataRow sourceRow in dsQDesign.Tables[table.ToString()].Rows)
							targetRow = table.NewRow();
							targetRow.ItemArray = sourceRow.ItemArray;
				if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParameters(ds); }
				if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParametersByID(baqName); }
			if (baqParams != null)
				int i = 0;
				foreach (KeyValuePair<string, string> p in baqParams)
					bool empty = false;
					string key = p.Key;
					string val = p.Value;
					if (key.Substring(0,1) == "-")
						if (val == string.Empty) { empty = true; }
						key = key.Substring(1, key.Length - 1);
					dsBAQ.ExecutionParameter[i].ParameterID = key;
					dsBAQ.ExecutionParameter[i].IsEmpty = empty;
					dsBAQ.ExecutionParameter[i].ParameterValue = val;
			if (updateable)
				adptr.Execute(ds, dsBAQ);
				adptr.ExecuteByID(baqName, dsBAQ);
			if (adptr.QueryResults != null && adptr.QueryResults.Tables.Count > 0)
				results = adptr.QueryResults.Tables["Results"];
				results = new DataTable();
			if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqName]; }
			if (!(edv != null))
				edv = new EpiDataView();
				oTrans.Add(baqName, edv);
			edv.dataView = results.DefaultView;
			if (grid != null) { grid.DataSource = results; }

You can leave out the last line and bind the grid to the EpiDataView directly if you want. Either way, as long as you make sure the parts of the above where “updateable” = true are the ones being used, the BAQ data will be updatable.