/// <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); }