public System.Data.DataSet QueryInterpolated(DateTime startTime, DateTime endTime, uint numSamples, params string[] tags)
{
//declare general purpose variables
int total = 0;
DateTime st = DateTime.Now;
DateTime en = st;
var ds = new System.Data.DataSet();
//change the cursor to busy
//Cursor = Cursors.WaitCursor;
if (sc == null || !sc.IsConnected()) { this.Connect(); }
//if currently connected
if (sc.IsConnected())
{
//create .net datatable to hold the rows selected on the tags grid
System.Data.DataRow dr;
//build the query string
DataQueryParams query = new InterpolatedQuery(startTime, endTime, numSamples, tags) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
ItemErrors errors;
var set = new Proficy.Historian.ClientAccess.API.DataSet();
//execute the query and populate the "set" Historian Dataset
sc.IData.Query(ref query, out set, out errors);
//sum up the total of samples
total += set.TotalSamples;
foreach (string tag in tags)
{
var dt = new System.Data.DataTable(tag);
dt.Columns.Add("Timestamp", typeof(DateTime));
dt.Columns.Add("Value", typeof(double));
dt.Columns.Add("Quality", typeof(double));
//loop thru all the samples and populate the .net datatable for data grid
for (int i = 0; i < set[tag].Count(); i++)
{
dr = dt.NewRow();
if (set[tag].GetValue(i) != null)
dr["Value"] = set[tag].GetValue(i);//.ToString();
//else
//dr["Value"] = "null";
dr["Timestamp"] = set[tag].GetTime(i);// .ToLongTimeString();
dr["Quality"] = set[tag].GetQuality(i).PercentGood();//.ToString();
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
//populate the data grid
//dataGridData.DataSource = dtTrend;
}
//calculate the elapsed time on the query operation and reset the cursor
en = DateTime.Now;
// Cursor = Cursors.Default;
//grpRead.Text = "Read Data - " + total.ToString() + " Samples - Elapsed Time: " + en.Subtract(st).Seconds.ToString() + " Secs";
return ds;
}
public System.Data.DataSet QueryInterpolated(DateTime startTime, DateTime endTime, uint numSamples, Type queryType, params TagSpecifics[] tags)
{
//declare general purpose variables
int total = 0;
DateTime st = DateTime.Now;
DateTime en = st;
var ds = new System.Data.DataSet();
//change the cursor to busy
//Cursor = Cursors.WaitCursor;
if (sc == null || !sc.IsConnected()) { this.Connect(); }
//if currently connected
if (sc.IsConnected())
{
var tagsForSeries = new List<string>();
var allTags = new List<string>();
var dependencies = new Dictionary<string, string[]>();
var delegates = new Dictionary<string, TagSpecifics.Formula>();
foreach (TagSpecifics tag in tags)
{
tagsForSeries.Add(tag.Tagname);
dependencies.Add(tag.Tagname, tag.DependencyTagnames);
delegates.Add(tag.Tagname, tag.CalcFormula);
if (tag.DependencyTagnames != null && tag.DependencyTagnames.Length > 0) allTags.AddRange(tag.DependencyTagnames);
if (tag.DependencyTagnames == null || tag.DependencyTagnames.Length == 0) allTags.Add(tag.Tagname);
}
//build the query string
DataQueryParams query;
ItemErrors errors;
Proficy.Historian.ClientAccess.API.DataSet set = new Proficy.Historian.ClientAccess.API.DataSet();
if (queryType == typeof(InterpolatedQuery))
{
query = new InterpolatedQuery(startTime, endTime, numSamples, allTags.ToArray<string>()) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "", ref ds, set);
}
else if (queryType == typeof(CalculatedQuery))
{
DataCriteria criteria = new DataCriteria()
{
Start = startTime,
End = endTime,
NumberOfSamples = numSamples,
SamplingMode = DataCriteria.SamplingModeType.Calculated,
CalculationMode = DataCriteria.CalculationModeType.Maximum,
Tagnames = new HashSet<string>(allTags)
};
query = new CalculatedQuery(DataCriteria.CalculationModeType.Maximum, allTags.ToArray<string>()) { Criteria = criteria, Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_MAX", ref ds, set);
query.Criteria.CalculationMode = DataCriteria.CalculationModeType.Average;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_AVG", ref ds, set);
query.Criteria.CalculationMode = DataCriteria.CalculationModeType.Minimum;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "_MIN", ref ds, set);
query.Criteria.SamplingMode = DataCriteria.SamplingModeType.Interpolated;
sc.IData.Query(ref query, out set, out errors);
ds = AddDatasetFromHistorianDataset(tagsForSeries, dependencies, delegates, "", ref ds, set);
//query = null;
}
else if (queryType == typeof(RawByTimeQuery))
{
DataCriteria criteria = new DataCriteria()
{
Start = startTime
};
query = new RawByTimeQuery(startTime, allTags.ToArray()) { Fields = DataFields.Time | DataFields.Value | DataFields.Quality };
sc.IData.Query(ref query, out set, out errors);
//ds
}
else
{
query = null;
return null;
}
//execute the query and populate the "set" Historian Dataset
//sum up the total of samples
total += set.TotalSamples;
}
//calculate the elapsed time on the query operation and reset the cursor
en = DateTime.Now;
// Cursor = Cursors.Default;
//grpRead.Text = "Read Data - " + total.ToString() + " Samples - Elapsed Time: " + en.Subtract(st).Seconds.ToString() + " Secs";
return ds;
}
internal Sql(InterpolatedQuery preview)
{
_isOnlyTracing = true;
_preview = preview;
}