public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID, W3CDateTime BeginDateTime, W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
if (!siteID.HasValue || !VariableID.HasValue)
{
return(ds);
}
ValuesDataSetTableAdapters.DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = Config.ODDB();
valuesTableAdapter.FillBySiteIdVariableIDBetweenDates(ds.DataValues, siteID.Value, VariableID.Value, BeginDateTime.DateTime, EndDateTime.DateTime);
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID)
{
ValuesDataSet ds = basicValuesDataSet();
if (!siteID.HasValue || !VariableID.HasValue)
{
return(ds);
}
ValuesDataSetTableAdapters.DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = Config.ODDB();
valuesTableAdapter.FillBySiteIDVariableID(ds.DataValues, siteID.Value, VariableID.Value);
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int siteID)
{
ValuesDataSet ds = basicValuesDataSet();
DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
try
{
valuesTableAdapter.FillBySiteID(ds.DataValues, siteID);
}
catch (Exception e)
{
log.Fatal("Cannot retrieve information from database: " + e.Message);
//+ valuesTableAdapter.Connection.DataSource
}
return ds;
}
public static ValuesDataSet GetValuesDataSet(int siteID)
{
ValuesDataSet ds = basicValuesDataSet();
ValuesDataSetTableAdapters.DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = Config.ODDB();
try
{
valuesTableAdapter.FillBySiteID(ds.DataValues, siteID);
}
catch (Exception e)
{
log.Fatal("Cannot retrieve information from database: " + e.Message); //+ valuesTableAdapter.Connection.DataSource
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int siteID, W3CDateTime BeginDateTime, W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
try
{
valuesTableAdapter.FillBySiteIDBetweenDates(ds.DataValues, siteID, BeginDateTime.DateTime,
EndDateTime.DateTime);
}
catch (Exception e)
{
log.Fatal("Cannot retrieve information from database: " + e.Message);
//+ valuesTableAdapter.Connection.DataSource
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int? siteID, int? VariableID)
{
ValuesDataSet ds = basicValuesDataSet();
if (!siteID.HasValue || !VariableID.HasValue) return ds;
DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
valuesTableAdapter.FillBySiteIDVariableID(ds.DataValues, siteID.Value, VariableID.Value);
return ds;
}
public static ValuesDataSet GetValueDataSet(int? siteID, int? VariableID, int? MethodID, int? SourceID,
int? QualityControlLevelID, W3CDateTime BeginDateTime,
W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
if (!siteID.HasValue || !VariableID.HasValue) return ds;
DataValuesTableAdapter valuesTableAdapter = new DataValuesTableAdapter();
valuesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
valuesTableAdapter.FillBySiteIdVariableIDBetweenDates(ds.DataValues, siteID.Value, VariableID.Value,
BeginDateTime.DateTime, EndDateTime.DateTime);
return ds;
}
// Return number of records inserted or found
public static int HandleDataValueInfo(SqlConnection sqlConn,
OD_SeriesCatalog odSC, OD_1_1_1DataSet.SeriesCatalogRow scRow,
SiteInfoType siteInfo, seriesCatalogTypeSeries scts, TimeSeriesResponseType tsRt)
{
DataValuesTableAdapter dvAdapter = new DataValuesTableAdapter();
dvAdapter.Connection = sqlConn;
dvAdapter.Adapter.UpdateBatchSize = Program.DbUpdateBatchSize;
dvAdapter.Adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None; // required to do batch insertion
string siteCode = siteInfo.siteCode[0].network + "|" + siteInfo.siteCode[0].Value;
string varCode = scts.variable.variableCode[0].Value;
string cond;
OD_1_1_1DataSet.DataValuesDataTable tblDataValues = new OD_1_1_1DataSet.DataValuesDataTable();
Console.WriteLine(">>>Parsing and inserting DATAVALUE");
// Get site ID
if (scRow.SiteID == -1)
{
cond = "SiteCode = '" + siteCode + "'";
scRow.SiteID = OD_Utils.GetPrimaryKey("Sites", "SiteID", cond, sqlConn);
}
// Get variable ID
if (scRow.VariableID == -1)
{
string[] vars = Regex.Split(varCode, "/");
string dataType = tsRt.timeSeries[0].variable.dataType;
cond = "VariableCode = '" + vars[0] + "_" + dataType + "'";
scRow.VariableID = OD_Utils.GetPrimaryKey("Variables", "VariableID", cond, sqlConn);
if (scRow.VariableID == -1)
{
Console.WriteLine("Failed to get variable ID from WS TimeSeries info (code: {0} type: {1}).",
varCode, dataType);
cond = "VariableCode = '" + vars[0] + "_" + scRow.DataType + "'";
scRow.VariableID = OD_Utils.GetPrimaryKey("Variables", "VariableID", cond, sqlConn);
if (scRow.VariableID == -1)
{
Console.WriteLine("Also failed to get variable ID with code: {0} and type: {1}. Give up.",
varCode, scRow.DataType);
return(0);
}
else
{
Console.WriteLine("Found variable ID {0} with code: {1} and type: {2} from database.",
scRow.VariableID, varCode, scRow.DataType);
}
}
}
// Update IDs if modified OD_SeriesCatalog odSC,
if (scRow.RowState == DataRowState.Modified)
{
odSC.Adapter.Update(scRow);
}
// Walk through each data value
if ((tsRt.timeSeries[0].values[0].value == null) || (tsRt.timeSeries[0].values[0].value.Count() == 0))
{
Console.WriteLine("No values in WS response.");
return(0);
}
int valueCount = tsRt.timeSeries[0].values[0].value.Count();
int currCount = 0, idx0 = 0, dupCount = 0;
ValueSingleVariable dvInfo0 = null;
bool dup;
// Begin database transaction to make sure the end data time and value count
// in SeriesCatalog and DataValues tables are consistent.
SqlTransaction sqlTrans = sqlConn.BeginTransaction();
for (int i = 0; i < valueCount; i++)
{
dup = false;
ValueSingleVariable dvInfo = tsRt.timeSeries[0].values[0].value[i];
if (dvInfo0 != null)
{
// We have seen many duplicate dvInfo which caused following DataValue insertion failure.
// "Violation of UNIQUE KEY constraint 'UNIQUE_DataValues'. Cannot insert duplicate key in object 'dbo.DataValues'"
// Have to skip the duplicate to avoid the whole batch insertion failure.
// Simplify the dup check by only comparing dateTime.
if (dvInfo0.dateTime == dvInfo.dateTime)
{
dupCount++;
Console.WriteLine("* Index {0} has duplicate time {1:s} with {2}, skip count {3}!",
i, dvInfo.dateTime, idx0, dupCount);
dup = true;
}
}
if (!dup)
{
InsertOneDataValue(tblDataValues, scRow.SiteID, scRow.VariableID, dvInfo);
idx0 = i;
dvInfo0 = dvInfo;
currCount++;
}
if ((currCount == Program.DbUpdateBatchSize) || (i + 1 == valueCount))
{
try
{
dvAdapter.Transaction = sqlTrans;
dvAdapter.Update(tblDataValues);
scRow.EndDateTime = dvInfo.dateTime;
scRow.EndDateTimeUTC = scRow.EndDateTime.ToUniversalTime();
scRow.ValueCount += currCount;
odSC.Adapter.Transaction = sqlTrans;
odSC.Adapter.Update(scRow);
sqlTrans.Commit();
}
catch (Exception e)
{
Console.WriteLine("!!!!!! Got exception: {0}.", e.Message);
Console.WriteLine("* Inserted {0} of {1} records for site {2} variable {3}",
i + 1 - currCount, valueCount, siteCode, varCode);
Console.WriteLine("* Rollback {0} records with {0} of {1} completed!",
currCount, i + 1 - currCount, valueCount);
sqlTrans.Rollback();
tblDataValues.Clear();
return(i + 1 - currCount);
}
currCount = 0;
tblDataValues.Clear();
if (i + 1 != valueCount)
{
sqlTrans = sqlConn.BeginTransaction();
}
}
}
dvAdapter.Transaction = null;
odSC.Adapter.Transaction = null;
return(valueCount - dupCount);
}
