/// <summary>
/// Create a dictionary for looking up a GhcnVariable object based on its variable code
/// </summary>
/// <returns>Variable code - GhcnVariable object lookup dictionary</returns>
private Dictionary <string, GhcnVariable> getVariableLookup()
{
Dictionary <string, GhcnVariable> lookup = new Dictionary <string, GhcnVariable>();
string connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString;
try
{
using (SqlConnection connection = new SqlConnection(connString))
{
string sql = "SELECT VariableID, VariableCode, VariableName, VariableUnitsID, SampleMedium, DataType FROM dbo.Variables";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.Connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string code = reader.GetString(1);
GhcnVariable variable = new GhcnVariable
{
VariableID = reader.GetInt32(0),
VariableCode = code,
VariableName = reader.GetString(2),
VariableUnitsID = reader.GetInt32(3),
SampleMedium = reader.GetString(4),
DataType = reader.GetString(5)
};
lookup.Add(code, variable);
}
reader.Close();
cmd.Connection.Close();
}
}
}
catch (Exception ex)
{
_log.LogWrite("SeriesCatalogManager ERROR in GetVariableLookup: " + ex.Message);
}
return(lookup);
}
/// <summary>
/// Update ODM SeriesCatalog using SQL bulk insert method
/// </summary>
public void UpdateSeriesCatalog_fast()
{
var siteLookup = GetSiteLookup();
var source = GetSource();
string methodDescription = GetMethodDescription();
List <GhcnSeries> seriesList = ReadSeriesFromInventory(siteLookup);
Console.WriteLine("updating series catalog for " + seriesList.Count.ToString() + " series ...");
string connString = ConfigurationManager.ConnectionStrings["OdmConnection"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connString))
{
// delete old entries from series catalog
string sql = "TRUNCATE TABLE dbo.SeriesCatalog";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
try
{
connection.Open();
cmd.ExecuteNonQuery();
Console.WriteLine("deleted old series from SeriesCatalog");
}
catch (Exception ex)
{
Console.WriteLine("error deleting old series from SeriesCatalog");
_log.LogWrite("UpdateSeriesCatalog: error deleting old series from SeriesCatalog");
return;
}
finally
{
connection.Close();
}
}
int batchSize = 500;
int numBatches = (seriesList.Count / batchSize) + 1;
long seriesID = 0L;
try
{
for (int b = 0; b < numBatches; b++)
{
// prepare for bulk insert
DataTable bulkTable = new DataTable();
bulkTable.Columns.Add("SeriesID", typeof(long));
bulkTable.Columns.Add("SiteID", typeof(long));
bulkTable.Columns.Add("SiteCode", typeof(string));
bulkTable.Columns.Add("SiteName", typeof(string));
bulkTable.Columns.Add("SiteType", typeof(string));
bulkTable.Columns.Add("VariableID", typeof(int));
bulkTable.Columns.Add("VariableCode", typeof(string));
bulkTable.Columns.Add("VariableName", typeof(string));
bulkTable.Columns.Add("Speciation", typeof(string));
bulkTable.Columns.Add("VariableUnitsID", typeof(int));
bulkTable.Columns.Add("VariableUnitsName", typeof(string));
bulkTable.Columns.Add("SampleMedium", typeof(string));
bulkTable.Columns.Add("ValueType", typeof(string));
bulkTable.Columns.Add("TimeSupport", typeof(float));
bulkTable.Columns.Add("TimeUnitsID", typeof(int));
bulkTable.Columns.Add("TimeUnitsName", typeof(string));
bulkTable.Columns.Add("DataType", typeof(string));
bulkTable.Columns.Add("GeneralCategory", typeof(string));
bulkTable.Columns.Add("MethodID", typeof(int));
bulkTable.Columns.Add("MethodDescription", typeof(string));
bulkTable.Columns.Add("SourceID", typeof(int));
bulkTable.Columns.Add("Organization", typeof(string));
bulkTable.Columns.Add("SourceDescription", typeof(string));
bulkTable.Columns.Add("Citation", typeof(string));
bulkTable.Columns.Add("QualityControlLevelID", typeof(int));
bulkTable.Columns.Add("QualityControlLevelCode", typeof(string));
bulkTable.Columns.Add("BeginDateTime", typeof(DateTime));
bulkTable.Columns.Add("EndDateTime", typeof(DateTime));
bulkTable.Columns.Add("BeginDateTimeUTC", typeof(DateTime));
bulkTable.Columns.Add("EndDateTimeUTC", typeof(DateTime));
bulkTable.Columns.Add("ValueCount", typeof(int));
int batchStart = b * batchSize;
int batchEnd = batchStart + batchSize;
if (batchEnd >= seriesList.Count)
{
batchEnd = seriesList.Count;
}
for (int i = batchStart; i < batchEnd; i++)
{
var row = bulkTable.NewRow();
seriesID = seriesID + 1;
GhcnVariable v = _variableLookup[seriesList[i].VariableCode];
row["SeriesID"] = seriesID;
row["SiteID"] = seriesList[i].SiteID;
row["SiteCode"] = seriesList[i].SiteCode;
row["SiteName"] = seriesList[i].SiteName;
row["SiteType"] = "Atmosphere";
row["VariableID"] = seriesList[i].VariableID;
row["VariableCode"] = v.VariableCode;
row["VariableName"] = v.VariableName;
row["Speciation"] = v.Speciation;
row["VariableUnitsID"] = v.VariableUnitsID;
row["VariableUnitsName"] = "Centimeter"; // todo get from DB!!
row["SampleMedium"] = v.SampleMedium;
row["ValueType"] = v.ValueType;
row["TimeSupport"] = v.TimeSupport;
row["TimeUnitsID"] = v.TimeUnitsID;
row["TimeUnitsName"] = "Day"; // todo get from DB!!
row["DataType"] = v.DataType;
row["GeneralCategory"] = v.GeneralCategory;
row["MethodID"] = 0;
row["MethodDescription"] = methodDescription;
row["SourceID"] = 1;
row["Organization"] = source.Organization;
row["SourceDescription"] = source.SourceDescription;
row["Citation"] = source.Citation;
row["QualityControlLevelID"] = 1;
row["QualityControlLevelCode"] = "1";
row["BeginDateTime"] = seriesList[i].BeginDateTime;
row["EndDateTime"] = seriesList[i].EndDateTime;
row["BeginDateTimeUTC"] = seriesList[i].BeginDateTime;
row["EndDateTimeUTC"] = seriesList[i].EndDateTime;
row["ValueCount"] = seriesList[i].ValueCount;
bulkTable.Rows.Add(row);
}
SqlBulkCopy bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = "dbo.SeriesCatalog";
connection.Open();
bulkCopy.WriteToServer(bulkTable);
connection.Close();
Console.WriteLine("SeriesCatalog inserted row " + batchEnd.ToString());
}
Console.WriteLine("UpdateSeriesCatalog: " + seriesList.Count.ToString() + " series updated.");
_log.LogWrite("UpdateSeriesCatalog: " + seriesList.Count.ToString() + " series updated.");
}
catch (Exception ex)
{
_log.LogWrite("UpdateSeriesCatalog ERROR: " + ex.Message);
}
}
}
private object SaveOrUpdateVariable(GhcnVariable variable, SqlConnection connection)
{
object variableIDResult = null;
using (SqlCommand cmd = new SqlCommand("SELECT VariableID FROM Variables WHERE VariableCode = @code", connection))
{
cmd.Parameters.Add(new SqlParameter("@code", variable.VariableCode));
connection.Open();
variableIDResult = cmd.ExecuteScalar();
connection.Close();
}
if (variableIDResult != null)
{
//update the variable
variable.VariableID = Convert.ToInt32(variableIDResult);
using (SqlCommand cmd = new SqlCommand("UPDATE Variables SET VariableName = @name, VariableUnitsID=@units, SampleMedium =@sampleMedium, DataType=@dataType, ValueType=@valueType WHERE VariableCode = @code", connection))
{
cmd.Parameters.Add(new SqlParameter("@code", variable.VariableCode));
cmd.Parameters.Add(new SqlParameter("@name", variable.VariableName));
cmd.Parameters.Add(new SqlParameter("@units", variable.VariableUnitsID));
cmd.Parameters.Add(new SqlParameter("@sampleMedium", variable.SampleMedium));
cmd.Parameters.Add(new SqlParameter("@dataType", variable.DataType));
cmd.Parameters.Add(new SqlParameter("@valueType", variable.ValueType));
connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
}
}
else
{
//save the variable
string sql = "INSERT INTO Variables(VariableCode, VariableName, Speciation, VariableUnitsID, SampleMedium, ValueType, IsRegular, TimeSupport, TimeUnitsID, DataType, GeneralCategory, NoDataValue) VALUES (@VariableCode, @VariableName, @Speciation, @VariableUnitsID, @SampleMedium, @ValueType, @IsRegular, @TimeSupport, @TimeUnitsID, @DataType, @GeneralCategory, @NoDataValue)";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
connection.Open();
cmd.Parameters.Add(new SqlParameter("@VariableCode", variable.VariableCode));
cmd.Parameters.Add(new SqlParameter("@VariableName", variable.VariableName));
cmd.Parameters.Add(new SqlParameter("@Speciation", variable.Speciation));
cmd.Parameters.Add(new SqlParameter("@VariableUnitsID", variable.VariableUnitsID));
cmd.Parameters.Add(new SqlParameter("@SampleMedium", variable.SampleMedium));
cmd.Parameters.Add(new SqlParameter("@ValueType", variable.ValueType));
cmd.Parameters.Add(new SqlParameter("@IsRegular", variable.IsRegular));
cmd.Parameters.Add(new SqlParameter("@TimeSupport", variable.TimeSupport));
cmd.Parameters.Add(new SqlParameter("@TimeUnitsID", variable.TimeUnitsID));
cmd.Parameters.Add(new SqlParameter("@DataType", variable.DataType));
cmd.Parameters.Add(new SqlParameter("@GeneralCategory", variable.GeneralCategory));
cmd.Parameters.Add(new SqlParameter("@NoDataValue", variable.NoDataValue));
// to get the inserted variable id
SqlParameter param = new SqlParameter("@VariableID", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
variableIDResult = cmd.Parameters["@VariableID"].Value;
connection.Close();
}
}
return(variableIDResult);
}