// Return number of records inserted or found.
public static int HandleVariableInfo(SqlConnection sqlConn, VariableInfoType varInfo)
{
VariablesTableAdapter varAdapter = new VariablesTableAdapter();
varAdapter.Connection = sqlConn;
OD_1_1_1DataSet.VariablesDataTable tblVariables = new OD_1_1_1DataSet.VariablesDataTable();
Console.WriteLine(">>>Parsing and inserting VARIABLES");
InsertOneVariable(tblVariables, varInfo, sqlConn);
try
{
varAdapter.Update(tblVariables);
}
catch (Exception e)
{
Console.WriteLine("Failed to insert VARIABLE {0}: {1}",
varInfo.variableCode[0].Value, e.Message);
return(0);
}
return(1);
//PrintTable(varAdapter, tblVariables);
}
public static ValuesDataSet GetValuesDataSet(int siteID, W3CDateTime BeginDateTime, W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//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;
siteAdapter.FillBySiteID(ds.Sites, siteID);
vAdapter.FillBySiteID(ds.Variables, ds.Sites[0].SiteID);
foreach (VariableInfoType variable in ds.Variables.Rows)
{
ds.DataValues.Rows.Add(getDataValues(ds, BeginDateTime.DateTime, BeginDateTime.DateTime));
}
return(ds);
}
/// <summary>
/// Returns a VariablesDataSet, as defined by VariablesDataSet.xsd
///
/// In SDSC code, this variable is loaded once, and is stored in an
/// Application, or AppServer variable, and queries are run against the stored dataset.
/// Other methods in this class are used to query the dataset.
/// </summary>
/// <param name="networkID"></param>
/// <returns></returns>
public static VariablesDataset GetVariableDataSet(int networkID)
{
VariablesDataset ds = new VariablesDataset();
UnitsTableAdapter unitTableAdapter = new UnitsTableAdapter();
VariablesTableAdapter varsTableAdapter = new VariablesTableAdapter();
CategoriesTableAdapter categoriesTableAdapter = new CategoriesTableAdapter();
unitTableAdapter.Connection.ConnectionString = Config.ODDB();
varsTableAdapter.Connection.ConnectionString = Config.ODDB();
categoriesTableAdapter.Connection.ConnectionString = Config.ODDB();
try
{
unitTableAdapter.Fill(ds.Units);
varsTableAdapter.Fill(ds.Variables);
categoriesTableAdapter.Fill(ds.Categories);
}
catch (Exception e)
{
log.Fatal("Cannot retrieve units or variables from database" + e.Message);
//+ unitTableAdapter.Connection.DataSource
throw new WaterOneFlowServerException("Cannot retrieve units or variables from database", e);
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int siteID, W3CDateTime BeginDateTime, W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
VariablesTableAdapter variablesTableAdapter = new VariablesTableAdapter();
variablesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
DataTable Variables = variablesTableAdapter.GetDataBySiteID(siteID);
foreach (DataRow row in Variables.Rows)
{
return(getDataValues(siteID, 1, BeginDateTime.DateTime, EndDateTime.DateTime));
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int siteID)
{
ValuesDataSet ds = basicValuesDataSet();
VariablesTableAdapter variablesTableAdapter = new VariablesTableAdapter();
variablesTableAdapter.Connection.ConnectionString = odws.Config.ODDB();
DataTable Variables = variablesTableAdapter.GetDataBySiteID(siteID);
foreach (DataRow row in Variables.Rows)
{
return(getDataValues(siteID, Convert.ToInt32(row["VariableID"]), new DateTime(1900, 01, 01), DateTime.Now));
}
return(ds);
}
public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID, W3CDateTime BeginDateTime,
W3CDateTime EndDateTime)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//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;
siteAdapter.FillBySiteID(ds.Sites, siteID.Value);
vAdapter.FillByVariableID(ds.Variables, VariableID.Value);
return(getDataValues(ds, BeginDateTime.DateTime, EndDateTime.DateTime));
}
public static ValuesDataSet GetValuesDataSet(int?siteID, int?VariableID)
{
ValuesDataSet ds = basicValuesDataSet();
SitesTableAdapter siteAdapter = new SitesTableAdapter();
VariablesTableAdapter vAdapter = new VariablesTableAdapter();
siteAdapter.Connection.ConnectionString = odws.Config.ODDB();
vAdapter.Connection.ConnectionString = odws.Config.ODDB();
//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;
siteAdapter.FillBySiteID(ds.Sites, siteID.Value);
vAdapter.FillBySiteID(ds.Variables, VariableID.Value);
return(getDataValues(ds, new DateTime(1900, 01, 01), DateTime.Now));
}
static void PrintTable(VariablesTableAdapter dtAdapter, OD_1_1_1DataSet.VariablesDataTable dt)
{
dtAdapter.Fill(dt);
// Print out the column names.
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Columns[curCol].ColumnName + "\t");
}
Console.WriteLine("\n----------------------------------");
// Print the DataTable.
for (int curRow = 0; curRow < dt.Rows.Count; curRow++)
{
for (int curCol = 0; curCol < dt.Columns.Count; curCol++)
{
Console.Write(dt.Rows[curRow][curCol].ToString() + "\t");
}
Console.WriteLine();
}
}
public VariablesResponseType GetVariables(VariableParam[] variables)
{
UsgsDbDailyValues.VariablesDataTable vDs = new UsgsDbDailyValues.VariablesDataTable();
UsgsDbDailyValuesTableAdapters.VariablesTableAdapter TableAdapter = new VariablesTableAdapter();
List <VariableInfoType> vList = new List <VariableInfoType>();
// if nothing. get it all
if (variables == null || variables.Length == 0)
{
try
{
TableAdapter.Fill(vDs);
} catch
{
log.Error("Cannot connect to USGS Database");
throw new WaterOneFlowServerException("Server Error: Cannot Connect to Database");
}
if (vDs.Rows.Count > 0)
{
foreach (UsgsDbDailyValues.VariablesRow vRow
in vDs)
{
VariableInfoType vit = RowToVariable(vRow);
if (vit != null)
{
vList.Add(vit);
}
}
}
}
else
{
foreach (VariableParam v in variables)
{
UsgsDbDailyValues.VariablesRow[] vRows;
try
{
TableAdapter.FillByVariableCode(vDs, v.Code);
string where = VariableOptions2WhereClause(v); /* Use Caution, where clause not SQL inject safe. */
vRows = (UsgsDbDailyValues.VariablesRow[])
vDs.Select(where);
}
catch
{
log.Error("Cannot connect to USGS Database");
throw new WaterOneFlowServerException("Server Error: Cannot Connect to Database");
}
if (vRows.Length > 0)
{
foreach (UsgsDbDailyValues.VariablesRow vRow
in vRows)
{
VariableInfoType vit = RowToVariable(vRow);
if (vit != null)
{
vList.Add(vit);
}
}
}
}
}
//if (vDs.Rows.Count > 0)
//{
// foreach (UsgsDbDailyValues.VariablesRow vRow
// in vDs)
// {
// VariableInfoType v = RowToVariable(vRow);
// if (v != null) vList.Add(v);
// }
//}
VariablesResponseType vRes = new VariablesResponseType();
if (vList != null && vList.Count > 0)
{
// build response
vRes.variables = vList.ToArray();
}
else
{
log.Info("User Error: No Variables Returned");
throw new WaterOneFlowException("No Variables Returned. Submit with no values to get full list.");
}
return(vRes);
}
/// <summary>
/// Returns a VariablesDataSet, as defined by VariablesDataSet.xsd
///
/// In SDSC code, this variable is loaded once, and is stored in an
/// Application, or AppServer variable, and queries are run against the stored dataset.
/// Other methods in this class are used to query the dataset.
/// </summary>
/// <param name="networkID"></param>
/// <returns></returns>
public static VariablesDataset GetVariableDataSet(int networkID)
{
VariablesDataset ds = new VariablesDataset();
UnitsTableAdapter unitTableAdapter = new UnitsTableAdapter();
VariablesTableAdapter varsTableAdapter = new VariablesTableAdapter();
CategoriesTableAdapter categoriesTableAdapter = new CategoriesTableAdapter();
unitTableAdapter.Connection.ConnectionString = Config.ODDB();
varsTableAdapter.Connection.ConnectionString = Config.ODDB();
categoriesTableAdapter.Connection.ConnectionString = Config.ODDB();
try
{
unitTableAdapter.Fill(ds.Units);
varsTableAdapter.Fill(ds.Variables);
categoriesTableAdapter.Fill(ds.Categories);
}
catch (Exception e)
{
log.Fatal("Cannot retrieve units or variables from database" + e.Message);
//+ unitTableAdapter.Connection.DataSource
throw new WaterOneFlowServerException("Cannot retrieve units or variables from database", e);
}
return ds;
}
