// This routine is to fill in the data what stinfo and scts have. The IDs will // be updated when handling DataValues. OD_1_1_1DataSet.SeriesCatalogRow CreateNewSeriesCatalog( string siteCode, string varCode, seriesCatalogTypeSeries scts) { OD_1_1_1DataSet.SeriesCatalogRow row = Table.NewSeriesCatalogRow(); VariableInfoType varInfo = scts.variable; row.SiteID = -1; row.SiteCode = siteCode; row.SiteName = null; row.VariableID = -1; row.VariableCode = varInfo.variableCode[0].Value; row.VariableName = varInfo.variableName; row.Speciation = varInfo.speciation; row.VariableUnitsID = Convert.ToInt32(varInfo.unit.unitCode); row.VariableUnitsName = varInfo.unit.unitName; row.SampleMedium = varInfo.sampleMedium; row.ValueType = varInfo.valueType; row.TimeSupport = varInfo.timeScale.timeSupport; row.TimeUnitsID = Convert.ToInt32(varInfo.timeScale.unit.unitCode); row.TimeUnitsName = varInfo.timeScale.unit.unitName; row.DataType = varInfo.dataType; row.GeneralCategory = varInfo.generalCategory; if (scts.method != null) { row.MethodID = scts.method.methodID; row.MethodDescription = scts.method.methodDescription; } row.SourceID = scts.source.sourceID; row.Organization = scts.source.organization; row.SourceDescription = scts.source.sourceDescription; row.SetCitationNull(); row.QualityControlLevelID = scts.qualityControlLevel.qualityControlLevelID; row.QualityControlLevelCode = scts.qualityControlLevel.qualityControlLevelCode; // This table is to track what we have in DataValues table. // We don't have anything in DataValues table at this moment. TimeIntervalType ti = (TimeIntervalType)scts.variableTimeInterval; row.BeginDateTime = ti.beginDateTime; row.EndDateTime = OD_Utils.GetDateTime(row.BeginDateTime, row.TimeUnitsID, -1); row.BeginDateTimeUTC = ti.beginDateTime.ToUniversalTime(); row.EndDateTimeUTC = row.EndDateTime.ToUniversalTime(); row.ValueCount = 0; return(row); }
/* * 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); }
static void HandleSites_1_1(string url, SqlConnection sqlConn) { string network, siteCode, varCode; string queryParam = string.Empty; string errMsg = string.Empty; int count; WaterML11.WaterOneFlowClient WofClient = new WaterOneFlowClient("WaterOneFlow", url); string[] blank = new string[0]; WaterML11.SiteInfoResponseType siteRt = null; try { siteRt = WofClient.GetSitesObject(blank, ""); } catch (Exception e) { errMsg = "Exception@HandleSites: " + e.Message; Console.WriteLine(errMsg); } if (siteRt == null) { if (errMsg.Length == 0) { errMsg = "Info: no site was returned from the URL."; } Console.WriteLine(errMsg); return; } // Write into "Sites" //siteRt = WofClient.GetSiteInfoObject(siteCode, ""); if (OD_SiteInfo.HandleSiteInfo(sqlConn, siteRt) == 0) { OD_Utils.Exit(1); } int nsites = siteRt.site.Count(); Console.WriteLine("Reading {0} sites from {1}..", nsites, url); WaterML11.seriesCatalogType[] sctAll; OD_SeriesCatalog odSC = new OD_SeriesCatalog(sqlConn); #region Loop for each site for (int i = 0; i < nsites; i++) { network = siteRt.site[i].siteInfo.siteCode[0].network; siteCode = network + ":" + siteRt.site[i].siteInfo.siteCode[0].Value; if (i % 10 == 0) { // Do this check and refresh for after every 100 sites CheckAndRefreshResources(); sqlConn = SqlConnOD; odSC.Adapter.Connection = sqlConn; } sctAll = LoadOnesiteInfo(WofClient, url, siteCode, ref errMsg); if (sctAll == null) { if (errMsg.Length == 0) { errMsg = "Error: LoadOneSiteInfo() returned NULL."; } Console.WriteLine(errMsg); continue; } if ((i + 1) % 50 == 0) { Console.WriteLine("..Site[{0}] {1} with {2} catalogs from {3} Time {4:HH:mm:ss}.", i, siteCode, sctAll.Count(), url, DateTime.Now); } else { Console.WriteLine("..Site[{0}] {1} with {2} catalogs Time {3:HH:mm:ss}.", i, siteCode, sctAll.Count(), DateTime.Now); } string sourceOrg = null; int methodID = 0; #region Loop for each seriesCatalogType for (int j = 0; j < sctAll.Count(); j++) { WaterML11.seriesCatalogType sct = sctAll[j]; if (sct.series == null) { errMsg = string.Format("Error: WaterML11.seriesCatalogType[{0}].series is NULL.", j); continue; } #region Loop for each seriesCatalogTypeSeries for (int k = 0; k < sct.series.Count(); k++) { WaterML11.seriesCatalogTypeSeries scts = sct.series[k]; WaterML11.TimeIntervalType ti = (WaterML11.TimeIntervalType)scts.variableTimeInterval; DateTime beginDateTime = ti.beginDateTime; DateTime endDateTime = ti.endDateTime; string code = scts.variable.variableCode[0].Value; varCode = network + ":" + code; //valueCount = scts.valueCount.Value; Console.WriteLine(""); Console.WriteLine("....Series[{0}] var: {1} of site[{2}]", k, code, i); // Use variable info from siteInfoResponseType // instead of from variablesResponseType // since the former includes the latter and contains more infomration if (OD_VariableInfo.HandleVariableInfo(sqlConn, scts.variable) == 0) { Console.WriteLine("Failed to insert variable. Give up!"); continue; } // Add to SeriesCatalog table if not there // Check SeriesCatalog table to get the newest data time OD_1_1_1DataSet.SeriesCatalogRow scRow = odSC.GetOrCreateSeriesCatalog( siteRt.site[i].siteInfo.siteCode[0].Value, scts.variable.variableCode[0].Value, scts); if (scRow.EndDateTime >= ti.endDateTime) { Console.WriteLine("No further action since database has most recent ending date time."); Console.WriteLine("Web service reported {0} values from <{1}> to <{2}>.", scts.valueCount.Value, ti.beginDateTime.ToString(), ti.endDateTime.ToString()); Console.WriteLine("Database has {0} values from <{1}> to <{2}>.", scRow.ValueCount, scRow.BeginDateTime.ToString(), scRow.EndDateTime.ToString()); if (ti.beginDateTime != scRow.BeginDateTime) { Console.WriteLine("WARNING: Web server has older data not in database! Please double check!"); } if (scts.valueCount.Value != scRow.ValueCount) { Console.WriteLine("WARNING: data value counts mismatch, maybe due to duplicate values from web service!"); } continue; } // Should we use UTC time? if (scRow.ValueCount > 0) { beginDateTime = OD_Utils.GetDateTime( scRow.EndDateTime, scRow.TimeUnitsID, 1); } // Update DataValue table //try //{ WaterML11.TimeSeriesResponseType tsrt = null; Console.WriteLine("......Getting {0} values from <{1}> to <{2}>", scts.valueCount.Value, beginDateTime, endDateTime); tsrt = LoadDataValueInfo(siteCode, varCode, WofClient, beginDateTime, endDateTime); if (tsrt == null) { continue; } if ((sourceOrg == null) || (!string.Equals(sourceOrg, scts.source.organization))) { // Insert a new source sourceOrg = scts.source.organization; OD_SourceInfo.HandleSourceInfo(sqlConn, siteRt.site[i].siteInfo, scts, tsrt.timeSeries[0]); } //if ((methodCode == null) || (!string.Equals(methodCode, scts.method.methodCode))) if ((methodID == 0) || (methodID != scts.method.methodID)) { // Insert a new method methodID = scts.method.methodID; OD_Methods.HandleMethodsInfo(sqlConn, scts.method); } count = OD_DataValues.HandleDataValueInfo(sqlConn, odSC, scRow, siteRt.site[i].siteInfo, scts, tsrt); Console.WriteLine(" -------->>>>>>> Inserted {0} records. Database has {1} values from <{2}> to <{3}>.", count, scRow.ValueCount, scRow.BeginDateTime.ToString(), scRow.EndDateTime.ToString()); //} //catch (Exception ex) //{ // errMsg = "Exception@HandleDataValueInfo: " + ex.Message; // Console.WriteLine(errMsg); //} } #endregion Loop for each seriesCatalogTypeSeries } #endregion Loop for each seriesCatalogType } #endregion Loop for each site } // end of HandleSites_1_1()
// 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); }