} // Process private async Task <Dictionary <string, ChangeDescriptor> > GetAndProcessRoutineChangesAsync(SqlConnection con, string connectionString, int changesCount) { var cmdGetRoutineList = new SqlCommand(); cmdGetRoutineList.Connection = con; cmdGetRoutineList.CommandTimeout = 60; cmdGetRoutineList.CommandType = System.Data.CommandType.StoredProcedure; cmdGetRoutineList.CommandText = "ormv2.GetRoutineList"; cmdGetRoutineList.Parameters.Add("maxRowver", System.Data.SqlDbType.BigInt).Value = MaxRowDate ?? 0; var changesList = new Dictionary <string, ChangeDescriptor>(); using (var reader = await cmdGetRoutineList.ExecuteReaderAsync()) { if (!this.IsRunning) { return(null); } var columns = new string[] { "CatalogName", "SchemaName", "RoutineName", "RoutineType", "rowver", "IsDeleted", "ObjectId", "LastUpdateByHostName", "JsonMetadata", "ParametersXml", "ParametersHash", "ResultSetXml", "ResultSetHash" }; // maps column ordinals to proper names var ix = columns.Select(s => new { s, Value = reader.GetOrdinal(s) }).ToDictionary(p => p.s, p => p.Value); var curRow = 0; while (await reader.ReadAsync()) { if (!this.IsRunning) { break; } if (changesCount == 1) { //!this._log.info(`(single change) ${ dbSource.Name}\t[${ row.SchemaName}].[${row.RoutineName}]`); } var newCachedRoutine = new CachedRoutine() { Routine = reader.GetString(ix["RoutineName"]), Schema = reader.GetString(ix["SchemaName"]), Type = reader.GetString(ix["RoutineType"]), IsDeleted = reader.GetBoolean(ix["IsDeleted"]), Parameters = new List <RoutineParameterV2>(), RowVer = reader.GetInt64(ix["rowver"]) }; var parmHash = reader.GetSqlBinary(ix["ParametersHash"]); var resultSetHash = reader.GetSqlBinary(ix["ResultSetHash"]); if (!parmHash.IsNull) { newCachedRoutine.ParametersHash = string.Concat(parmHash.Value.Select(item => item.ToString("x2"))); } if (!resultSetHash.IsNull) { newCachedRoutine.ResultSetHash = string.Concat(resultSetHash.Value.Select(item => item.ToString("x2"))); } var lastUpdateByHostName = reader.GetString(ix["LastUpdateByHostName"]); string jsonMetadata = null; if (!reader.IsDBNull(ix["JsonMetadata"])) { jsonMetadata = reader.GetString(ix["JsonMetadata"]); } if (!string.IsNullOrWhiteSpace(jsonMetadata)) { try { newCachedRoutine.jsDALMetadata = JsonConvert.DeserializeObject <jsDALMetadata>(jsonMetadata); } catch (Exception ex) { newCachedRoutine.jsDALMetadata = new jsDALMetadata() { Error = ex.ToString() }; } } string parametersXml = null; if (!reader.IsDBNull(ix["ParametersXml"])) { parametersXml = reader.GetString(ix["ParametersXml"]); var parameterList = ExtractParameters(parametersXml); newCachedRoutine.Parameters = parameterList; } curRow++; var perc = ((double)curRow / (double)changesCount) * 100.0; this.Status = $"{ DateTime.Now.ToString("yyyy-MM-dd, HH:mm:ss")} - Overall progress: {curRow} of { changesCount } ({ perc.ToString("##0.00")}%) - [{ newCachedRoutine.Schema }].[{ newCachedRoutine.Routine }]"; if (curRow % 10 == 0) { Hubs.WorkerMonitor.Instance.NotifyObservers(); } if (!newCachedRoutine.IsDeleted) { /* * Resultset METADATA */ if (newCachedRoutine.ResultSetRowver >= newCachedRoutine.RowVer) { Log.Verbose("Result set metadata up to date"); } else { //logLine.Append("Generating result set metadata"); //console.log("Generating result set metadata"); // generate using legacy FMTONLY way if explicitly requested if ((newCachedRoutine.jsDALMetadata?.jsDAL?.fmtOnlyResultSet ?? false)) { // can't use the same connection await GenerateFmtOnlyResultsetsAsync(connectionString, newCachedRoutine); } else { string resultSetXml = null; if (!reader.IsDBNull(ix["ResultSetXml"])) { resultSetXml = reader.GetString(ix["ResultSetXml"]); (var resultSetMetdata, var error) = ExtractResultSetMetadata(resultSetXml); newCachedRoutine.ResultSetRowver = newCachedRoutine.RowVer; if (error == null) { newCachedRoutine.ResultSetMetadata = new Dictionary <string, List <ResultSetFieldMetadata> >() { [string.Intern("Table0")] = resultSetMetdata }; } else { newCachedRoutine.ResultSetError = error; } } } } } // !IsDeleted newCachedRoutine.PrecalculateJsGenerationValues(this.Endpoint); Endpoint.AddToCache(newCachedRoutine.RowVer, newCachedRoutine, lastUpdateByHostName, out var changesDesc); if (changesDesc != null) { if (!changesList.ContainsKey(newCachedRoutine.FullName.ToLower())) { changesList.Add(newCachedRoutine.FullName.ToLower(), changesDesc); } // TODO: Else update - so last change is what gets through? // ... Two things to consider here.. 1. We use changesList to determine if there was a significant change to the metadata so we can generate a new file and notify subscribers // 2. ..nothing else actually...? just think about the same sproc changing multiple times ....no sproc can only come back once per iteration!!!! } if (!this.MaxRowDate.HasValue || newCachedRoutine.RowVer > this.MaxRowDate.Value) { this.MaxRowDate = newCachedRoutine.RowVer; } } // while reader.Read } // using reader return(changesList); }