/*
* This routine is to add SQL WHERE clause to limit records to be loaded.
* Be sure to copy this routine to SeriesCatalogTableAdapter class when
* OD_1_1_DataSet.Designer.cs is regenerated to make GetRow() work.
* Copied from http://www.codeproject.com/Articles/17324/Extending-TableAdapters-for-Dynamic-SQL.
* public int FillWhere(OD_1_1_DataSet.SeriesCatalogDataTable dataTable, string whereExpression)
* {
* string text1 = this._commandCollection[0].CommandText;
* try
* {
* this._commandCollection[0].CommandText += " WHERE " + whereExpression;
* return this.Fill(dataTable);
* }
* finally { this._commandCollection[0].CommandText = text1; }
* }
*
* public OD_1_1DataSet.SeriesCatalogRow GetRow(string siteCode, string varCode)
* {
* string cond = "SiteCode = '" + siteCode + "' and ";
* cond += "VariableCode = '" + varCode + "'";
*
* Adapter.FillWhere(Table, cond);
* if (Table.Rows.Count == 0)
* return null;
* else {
* if (Table.Rows.Count > 1)
* Console.WriteLine(
* "SeriesCatalog table contains {0} records.",
* Table.Rows.Count);
* return (OD_1_1DataSet.SeriesCatalogRow)Table.Rows[0];
* }
* }
*/
public OD_1_1_1DataSet.SeriesCatalogRow GetOrCreateSeriesCatalog(
string siteCode, string varCode,
WaterML11.seriesCatalogTypeSeries scts)
{
string query;
int undefined = -99;
// Console.WriteLine(@"....No SeriesCatalog from hiscentral for site {0} and var {1},
// dataType '{2}' methodID {3} sourcdID {4} qualityID {5}.",
// siteCode, varCode, scts.dataType, scts.method.methodID,
// scts.source.sourceID,
// scts.qualityControlLevel.qualityControlLevelID);
if (scts.method.methodID < 0)
{
scts.method.methodID = undefined;
}
if (scts.qualityControlLevel.qualityControlLevelID < 0)
{
scts.qualityControlLevel.qualityControlLevelID = undefined;
}
if (scts.source.sourceID < 0)
{
scts.source.sourceID = undefined;
}
query = string.Format(@"SiteCode='{0}' and VariableCode='{1}' and
MethodID = {2} and QualityControlLevelID = {3} and
SourceID = {4}", siteCode, varCode,
scts.method.methodID,
scts.qualityControlLevel.qualityControlLevelID,
scts.source.sourceID);
Adapter.FillWhere(Table, query);
if (Table.Rows.Count > 0)
{
if (Table.Rows.Count > 1)
{
Console.WriteLine(
"SeriesCatalog table contains {0} records.", Table.Rows.Count);
}
return((OD_1_1_1DataSet.SeriesCatalogRow)Table.Rows[0]);
}
Console.WriteLine(">>>Parsing and inserting a new SeriesCatalog");
OD_1_1_1DataSet.SeriesCatalogRow row = CreateNewSeriesCatalog(siteCode, varCode, scts);
Table.AddSeriesCatalogRow(row);
Adapter.Update(Table);
return(row);
}
// The seriesCatalogID is not found in hiscental database, create one here before
// hiscental database adopts the new variable.
public int GetOrCreateSeriesID(string siteCode, string varCode,
WaterML10.seriesCatalogTypeSeries scts)
{
int scID;
string query;
int undefined = -99;
// Console.WriteLine(@"......hiscentral.SeriesCatalog has no record for site {0} and
// variable {1}, dataType '{2}' methodID {3} qualityID {4} sourceID {5}.",
// siteCode, varCode, scts.dataType, scts.method.methodID,
// scts.qualityControlLevel.qualityControlLevelID,
// scts.source.sourceID);
if (scts.Method.methodID < 0)
{
scts.Method.methodID = undefined;
}
if (scts.QualityControlLevel.QualityControlLevelID < 0)
{
scts.QualityControlLevel.QualityControlLevelID = undefined;
}
if (scts.Source.sourceID < 0)
{
scts.Source.sourceID = undefined;
}
query = string.Format(@"SiteCode='{0}' and VariableCode='{1}' and
MethodID = {2} and QualityControlLevelID = {3} and
SourceID = {4}", siteCode, varCode,
scts.Method.methodID,
scts.QualityControlLevel.QualityControlLevelID,
scts.Source.sourceID);
scID = OD_Utils.GetPrimaryKey("dbo.SeriesCatalog", "SeriesID", query, Adapter.Connection);
if (scID >= 0)
{
return(scID);
}
HealthQueryDataSet.SeriesCatalogRow row = Table.NewSeriesCatalogRow();
row.SiteCode = siteCode;
row.VariableCode = varCode;
row.MethodID = scts.Method.methodID;
row.SourceID = scts.Source.sourceID;
row.QualityControlLevelID = scts.QualityControlLevel.QualityControlLevelID;
if (OD_Utils.ConvertToString(scts.variable.dataType) != null)
{
row.DataType = OD_Utils.ConvertToString(scts.variable.dataType);
}
row.ValueCount = scts.valueCount.Value;
WaterML10.TimeIntervalType ti = (WaterML10.TimeIntervalType)scts.variableTimeInterval;
row.BeginDateTimeUTC = ti.beginDateTime.ToUniversalTime();
row.EndDateTimeUTC = ti.endDateTime.ToUniversalTime();
Table.AddSeriesCatalogRow(row);
Adapter.Update(Table);
Table.Clear();
scID = OD_Utils.GetPrimaryKey("dbo.SeriesCatalog", "SeriesID", query, Adapter.Connection);
Console.WriteLine(
"....Site {0} and variable {1} with ID {2} is saved in a private catalog table.",
siteCode, varCode, scID);
return(scID);
}
