Dynamic Query write updates back to database

@LBARKER, @tanner - here you go. It might be a bit rough around the edges because I’ve rewritten this from scratch, leaving out a lot of the bits that I’ve included over time which are convenient for me but confuse the issue for anyone else. But I’ve tested the basics, and at a minimum it’ll give you the general idea.

There’s probably a lot of code in here that could be done better and more efficient ways. It would be nice to have feedback if so!

The main feature is that you can declare classes of your own before the Script part of the customisation code.

You need some extra “usings” and the DynamicQuery assemblies:

using Ice.Core;
using Infragistics.Win.UltraWinToolbars; // for manual button addition
using Infragistics.Win.UltraWinGrid;
using System.Collections;
using System.Collections.Generic;
using System.Linq;

Then the class is:

class M8DynQuery
{
	private string baqName;
	private Ice.Adapters.DynamicQueryAdapter adptr;
	private Ice.BO.DynamicQueryDataSet ds;
	private Ice.BO.QueryExecutionDataSet dsBAQ;
	private EpiDataView edv;
	private DataTable results;
	private EpiUltraGrid grid;
	private Dictionary<string, string> baqParams;
	private Dictionary<string, string> defParams;
	private Dictionary<string, string> lastParams;
	private bool changedparams;
	private Dictionary<string, Control> paramControls;
	private string[] keynames;
	private string[] keys;
	private EpiTransaction oTrans;
	private Control maincontrol;
	private bool gotBAQ;

	public M8DynQuery (string baqname, EpiTransaction trans, Control sheet, EpiUltraGrid ultragrid, string[] paramnames, string[] paramdefaults)
	{
		baqName = baqname;
		oTrans = trans;
		adptr = new Ice.Adapters.DynamicQueryAdapter(oTrans);
		adptr.BOConnect();
		edv = new EpiDataView();
		results = new DataTable();
		edv.dataView = results.DefaultView;
		oTrans.Add(baqName, edv);
		changedparams = true;
		maincontrol = sheet;
		gotBAQ = false;

		if (paramnames != null && paramdefaults != null && paramnames.Length == paramdefaults.Length)
		{
			baqParams = new Dictionary<string, string>();
			defParams = new Dictionary<string, string>();
			for (int i = 0; i < paramnames.Length; i++)
			{
				baqParams[paramnames[i]] = paramdefaults[i];
				defParams[paramnames[i]] = paramdefaults[i];
			}
			GetParamControls();
		}

		if (ultragrid != null)
		{
			grid = ultragrid;
			grid.EpiBinding = edv.ViewName;
			grid.UpdateMode = UpdateMode.OnCellChange;
		}
		GetData(false);
		MatchDropdowns();
	}

	public Ice.Adapters.DynamicQueryAdapter Adapter()
	{
		return adptr;
	}

	public EpiDataView EpiDataView()
	{
		return edv;
	}

	public DataTable DataTable()
	{
		return results;
	}

	public void Clear()
	{
		adptr.ClearDynamicQueryData();
	}

	public DataRowView CurrentDataRow()
	{
		if (edv == null || edv.Row < 0)
		{
			return null;
		}
		else
		{
			return edv.dataView[edv.Row];
		}
	}

	public void GetData(bool getparams = true)
	{
		//MessageBox.Show("get " + baqName);
		string ep = "Start";
		try
		{
			oTrans.PushStatusText("Getting data for " + baqName + "...",true);
			if (getparams) { ParamsFromControls(); }
			ep = "1";
			if (baqName != string.Empty)
			{
				if (!gotBAQ)
				{
					if (adptr.GetByID(baqName)) { gotBAQ = true; }
				}
				if (!gotBAQ)
				{
					return;
				}
				ep = "2";
				if (!(ds != null)) { ds = adptr.DynamicQueryData; }
				if (ds.DynamicQuery.Rows.Count == 0)
				{
					ep = "3";
					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;
							table.Rows.Add(targetRow);
						}
					}
				}
				ep = "4";
				if (!(dsBAQ != null)) { dsBAQ = adptr.GetQueryExecutionParameters(ds); }
				if (baqParams != null)
				{
					ep = "5";
					int i = 0;
					foreach (KeyValuePair<string, string> p in baqParams)
					{
						bool empty = false;
						string key = p.Key;
						string val = p.Value;
						dsBAQ.ExecutionParameter[i].ParameterID = key;
						dsBAQ.ExecutionParameter[i].IsEmpty = empty;
						dsBAQ.ExecutionParameter[i].ParameterValue = val;
						i++;
					}
					dsBAQ.AcceptChanges();
					ep = "6";
					lastParams = new Dictionary<string,string>(baqParams);
				}
				ep = "execute";
				adptr.ExecuteByID(baqName, dsBAQ);
				ep = "7";
				if (adptr.QueryResults != null && adptr.QueryResults.Tables.Count > 0)
				{
					results = adptr.QueryResults.Tables["Results"];
				}
				else
				{
					results = new DataTable();
				}
				ep = "8";
				if (!(edv != null)) { edv = (EpiDataView)oTrans.EpiDataViews[baqName]; }
				if (!(edv != null))
				{
					edv = new EpiDataView();
					oTrans.Add(baqName, edv);
				}
				ep = "9";
				if (edv.dataView != results.DefaultView) { edv.dataView = results.DefaultView; }
				ep = "10";
				if (grid != null && grid.DataSource != results) { grid.DataSource = results; }
				//edv.EpiDataChanged();
				//edv.Notify(new EpiNotifyArgs(oTrans,0,EpiTransaction.NotifyType.Initialize));
				//if (grid != null) { grid.EpiBinding = edv.ViewName; grid.Refresh(); }
				changedparams = false;
			}
		}
		catch (Exception e)
		{
			MessageBox.Show("Data Error " + baqName + " (" + ep + "):" + System.Environment.NewLine + e.Message);
		}
		finally
		{
			oTrans.PopStatus();
		}
	}

	public void RefreshData()
	{
		if (baqParams != null && lastParams != null)
		{
			bool matched = true;
			foreach (KeyValuePair<string,string> kp in baqParams)
			{
				if (!kp.Value.Equals(lastParams[kp.Key]))
				{
					matched = false;
					break;
				}
			}
			if (!matched) { GetData(); }
		}
		else
		{
			GetData();
		}
	}

	public bool Save()
	{
		bool ret = true;
		try
		{
			oTrans.PushStatusText("Saving " + baqName + "...",true);
			DataSet retds = adptr.Update(adptr.DynamicQueryData, results.DataSet, false);
			if (retds != null && retds.Tables.Count > 0 && retds.Tables["Errors"] != null && retds.Tables["Errors"].Rows.Count > 0)
			{
				MessageBox.Show("Save Error " + baqName + System.Environment.NewLine + retds.Tables["Errors"].Rows[0]["ErrorText"].ToString());
				ret = false;
			}
			else
			{
				GetData();
			}
		}
		catch (Exception e)
		{
			MessageBox.Show(e.Message, "Save Error " + baqName);
			ret = false;
		}
		finally
		{
			oTrans.PopStatus();
		}
		return ret;
	}

	#region Parameters

	public string[] ParamNames()
	{
		return baqParams.Keys.ToArray();
	}

	public Dictionary<string, string> Params()
	{
		return baqParams;
	}

	private void GetParamControls()
	{
		Control c = maincontrol;
		while (c.Parent != null) { c = c.Parent; }
		AddParamControl(c);
	}

	private void AddParamControl(Control parentcontrol)
	{
		foreach (Control c in parentcontrol.Controls)
		{
			if (c.HasChildren)
			{
				AddParamControl(c);
			}
			else
			{
				if (c.Name.StartsWith("param" + baqName))
				{
					string pname = c.Name.Substring(baqName.Length + 5,c.Name.Length - (baqName.Length + 5));
					if (!(paramControls != null)) { paramControls = new Dictionary<string, Control>(); }
					paramControls[pname] = c;
				}
			}
		}		
	}	

	public bool ParamsChanged()
	{
		bool ret = true;
		if (baqParams != null && lastParams != null)
		{
			bool matched = true;
			foreach (KeyValuePair<string,string> kp in baqParams)
			{
				if (!kp.Value.Equals(lastParams[kp.Key]))
				{
					matched = false;
					break;
				}
			}
			ret = !matched;
		}
		return ret;
	}

	public void ParamsFromControls()
	{
		if (paramControls != null && maincontrol != null)
		{
			Control top = maincontrol;
			while (top.Parent != null) { top = top.Parent; }
			foreach (KeyValuePair<string, Control> p in paramControls)
			{
				Control c = p.Value;
				string val = ControlValue(c);
				if (baqParams[p.Key] != val)
				{
					UpdateParam(p.Key, val);
					//MessageBox.Show(p.Key + " - " + val);
				}
			}
		}
	}

	public void UpdateParam(string key, string newval)
	{
		if (baqParams.ContainsKey(key))
		{
			baqParams[key] = newval;
			changedparams = true;
		}
	}

	public void ResetParams()
	{
		baqParams = new Dictionary<string, string>(defParams);
		changedparams = true;
	}

	private string ControlValue(Control c)
	{
		string val = string.Empty;
		if (c is EpiTextBox)
		{
			val = ((EpiTextBox)c).Text ?? string.Empty;
		}
		else if (c is EpiCombo && ((EpiCombo)c).Value != null)
		{
			val = ((EpiCombo)c).Value.ToString();
		}
		else if (c is EpiCheckBox && ((EpiCheckBox)c).CheckState != CheckState.Indeterminate)
		{
			val = ((EpiCheckBox)c).Checked.ToString();
		}
		else if (c is BAQCombo && ((BAQCombo)c).Value != null)
		{
			val = ((BAQCombo)c).Value.ToString();
		}
		else if (c is EpiDateTimeEditor && ((EpiDateTimeEditor)c).Value != null)
		{
			val = ((DateTime)((EpiDateTimeEditor)c).Value).ToString("s");
		}
		else if (c is EpiTimeEditor)
		{
			
		}
		else if (c is EpiNumericEditor && ((EpiNumericEditor)c).Value != null)
		{
			val = ((EpiNumericEditor)c).Value.ToString();
		}
		else if (c is EpiCurrencyEditor && (decimal?)((EpiCurrencyEditor)c).Value != null)
		{
			val = ((EpiCurrencyEditor)c).Value.ToString();
		}
		else if (c is EpiRetrieverCombo && ((EpiRetrieverCombo)c).Value != null)
		{
			val = ((EpiRetrieverCombo)c).Value.ToString();
		}
		return val;
	}

	#endregion

	#region DropDowns

	public void MatchDropdowns()
	{
		if (grid != null)
		{
			UltraGridBand listBand = grid.DisplayLayout.Bands[0];
			for (int i = 0; i < listBand.Columns.Count; i++)
			{
				string caption = listBand.Columns[i].Header.Caption;
				string key = listBand.Columns[i].Key;
				Control top = grid;
				while (top.Parent != null) { top = top.Parent; }
				MatchDropdownControls(top, listBand, caption, key);
			}
		}
	}

	private bool MatchDropdownControls(Control parentcontrol, UltraGridBand listBand, string caption, string key)
	{
		bool donebind = false;
		foreach (Control c in parentcontrol.Controls)
		{
			string ctype = c.GetType().ToString().Replace("Ice.Lib.Framework.", "");
			if (ctype == "BAQCombo" || ctype == "EpiCombo")
			{
				if (ctype == "BAQCombo")
				{
					if (((BAQCombo)c).EpiBinding == baqName + "." + key)
					{
						listBand.Columns[key].ValueList = (BAQCombo)c;
						listBand.Columns[key].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
						((BAQCombo)c).ForceRefreshList();
						donebind = true;
						break;
					}
				}
				else if (ctype == "EpiCombo")
				{
					if (((EpiCombo)c).EpiBinding == baqName + "." + key)
					{
						listBand.Columns[key].ValueList = (EpiCombo)c;
						listBand.Columns[key].Style = Infragistics.Win.UltraWinGrid.ColumnStyle.DropDownList;
						((EpiCombo)c).ForceRefreshList();
						donebind = true;
						break;
					}
				}
			}
			else if (c.HasChildren)
			{
				if (MatchDropdownControls(c, listBand, caption, key))
				{
					return true;
				}
			}
		}
		return donebind;
	}

	#endregion

	protected virtual void Dispose(bool disposing)
	{
	}

	public void Dispose()
	{
		paramControls = null;
		defParams = null;
		baqParams = null;
		results = null;
		edv = null;
		dsBAQ = null;
		ds = null;
		adptr.Dispose();
		adptr = null;
		Dispose(true);
		GC.SuppressFinalize(this);
	}	
}

To use it, you declare one variable per BAQ needed, at the Script level (after “// Add Custom Module Level Variables Here **”):

private M8DynQuery dqXXXX;

Declare and initialise those variables within InitializeCustomCode:

dqXXXX = new M8DynQuery(
		"BAQNAME_HERE", // string, name of BAQ
		oTrans, // always oTrans
		csm.GetNativeControlReference("guid"), // use GUID of the panel where the controls are
		grdXXXX, // grid for the data, or null if none
		new string[] {"PARAM1","PARAM2"}, // string array of BAQ parameter names, or null if none
		new string[] {"VALUE1","VALUE2"} // string array of initial parameter values, or null if no parameters
	);

Dispose of the object in DestroyCustomCode, after “// Begin Custom Code Disposal”:

dqXXXX.Dispose();

Methods:

GetData() - redownloads the BAQ data.
RefreshData() - redownloads the BAQ data only if the parameters have been changed.
Save() - saves the updated BAQ data, assuming the BAQ is updateable.
UpdateParam(string paramName, string paramValue) - updates the named parameter.
ResetParams() - sets all parameters back to the initial defaults.
Clear() - resets all data and the Dynamic Query.

Properties:
Adapter() - returns the DynamicQueryAdapter for the M8DynQuery object.
EpiDataView() - returns the associated EpiDataView (which is also in the oTrans.EpiDataViews collection).
DataTable - returns the underlying DataTable (DynamicQuery results).
CurrentDataRow() - the DataRowView of the active EpiDataView row, or null if none is active.
ParamNames() - a List of the parameter names.
Params() - a Dictionary<string,string> of parameter names and current values.

There are two conveniences built in.

Automatic drop-downs within the grid - if drop-down controls are used within the screen and bound to fields in the EpiDataView used by the M8DynQuery object, those drop-downs will be pushed to the same fields within the grid so both behave in the same way.

Parameter input boxes - if a control is placed within the screen, unbound, and named according to the M8DynQuery convention, it will automatically be treated as the input field for the associated BAQ parameter. The naming convention for this is “param” + the BAQ name + the parameter name.

2 Likes

@dhewi Thanks for taking the time to document and share this!

1 Like

Here is mine, example.

public System.Data.DataSet xyz()
{
       // Declare and create an instance of the Adapter.
       DynamicQueryAdapter adapterDynamicQuery = new DynamicQueryAdapter(this.oTrans);
       adapterDynamicQuery.BOConnect();

       // Declare and Initialize Variables
       string BAQName = "HASO-FileWrite";

       bool more;
       var dqds = adapterDynamicQuery.GetQueryExecutionParametersByID(BAQName);
       var ds = adapterDynamicQuery.GetList(BAQName, dqds, 0, 0, out more);

       // This shows me adding 2 Rows manually, here you would loop through your dataset and add many many rows
       // then you would call .Update once! And then in the BPM you can Combine them all and use
       // WriteAllText to write it out in a single file open. 
       var newRow  = ds.Tables["Results"].NewRow();
       newRow["Calculated_FileName"] = "MyFileName.txt";
       newRow["Calculated_Content"] = "rrrrrra";
       newRow["RowMod"] = "A";
       newRow["SysRowID"] = Guid.NewGuid();
       newRow["RowIdent"] = newRow["SysRowID"].ToString();
       ds.Tables["Results"].Rows.Add(newRow);

       var newRow2  = ds.Tables["Results"].NewRow();
       newRow2["Calculated_FileName"] = " MyFileName.txt";
       newRow2["Calculated_Content"] = "eeeeee";
       newRow2["RowMod"] = "A";
       newRow2["SysRowID"] = Guid.NewGuid();
       newRow2["RowIdent"] = newRow2["SysRowID"].ToString();
       ds.Tables["Results"].Rows.Add(newRow2);

       adapterDynamicQuery.Update(BAQName, ds);
       
       // Get Results if we need to read for example a Success or Failure Column
       // results.Tables["Results"].Rows[0]["Calculated_SomeSuccessColumn"]
       DataSet results = adapterDynamicQuery.QueryResults;

       // Cleanup Adapter Reference
       adapterDynamicQuery.Dispose();

       return results;
}
5 Likes

@dhewi, You’ve gone above and beyond on this one!!

As I mentioned earlier, I’m still cutting my teeth on customizations so please excuse my ignorance…

After adding the usings and assemblies, does the “M8DynQuery” class get added in straight after the usings and before the "public class Script?

image

Also, the only parts I’ll have to edit are:

image

And… I can add as many of the following as I need to??

private M8DynQuery dqXXXX;

Regards Lawson

I think you’re correct on all parts, @LBARKER.

Yes, the extra class goes before “public class Script”, and is then available for object creation within Script.

You shouldn’t ever need to edit the class itself unless you want to change what it does (which of course is always possible, and I do as and when I need something different or think of an improvement). Yes, where you instantiate the new object you need to edit the details, but just the ones you’ve highlighted.

And yes, you can add as many objects based on that class as you like, and they’ll all work without needing to duplicate any of the class code.

1 Like

You’re a Legend fella, thank you for sharing,

1 Like

You’re very welcome. I’d be interested to know how you get on. Feel free to message if you prefer.