private Response Refresh(XDocument configDoc, string tableName) { Response response = new Response(); try { _dataDictionary = new library.DataDictionary(); XElement config = configDoc.Element("configuration"); SQLBuilder sqlBuilder; // // Parse project assignments // Dictionary<string, string> projectAssignments = new Dictionary<string, string>(); IEnumerable<XElement> projectAssignmentElts = config.Element("assignments").Elements("assignment"); if (projectAssignmentElts != null && projectAssignmentElts.Count() > 0) { foreach (XElement assignment in projectAssignmentElts) { string name = assignment.Attribute("name").Value; string value = assignment.Attribute("value").Value; if (name.StartsWith("@") && value.Length > 0) { projectAssignments[name] = value; } } } // // Parse project text replacements // Dictionary<string, string> projectReplacements = new Dictionary<string, string>(); IEnumerable<XElement> projectReplacementElts = config.Element("replacements").Elements("replacement"); if (projectReplacementElts != null && projectReplacementElts.Count() > 0) { foreach (XElement replacement in projectReplacementElts) { string placeHolder = replacement.Attribute("placeHolder").Value; string name = replacement.Attribute("name").Value; string value = replacement.Attribute("value").Value; if (placeHolder == string.Empty || name == string.Empty || value == string.Empty) { continue; } projectReplacements[placeHolder[0] + name + placeHolder[1]] = value; } } // // Get query elements // IEnumerable<XElement> queryElts = config.Elements("query"); DBType siteDbType = Utility.GetDBType(_siteConnStr); DataTable siteSchemaResult = DBManager.Instance.ExecuteQuery(_siteConnStr, Constants.ORACLE_GET_CURRENT_SCHEMA); string siteSchema = siteSchemaResult.Rows[0][0].ToString(); // // Process !SiteData query // XElement siteQueryElt = (from query in queryElts where query.Attribute("name").Value == Constants.SITE_DATA_QUERY select query).First(); Dictionary<string, string> siteSchemaMap = new Dictionary<string, string>(); siteSchemaMap["SITE"] = siteSchema; sqlBuilder = new SQLBuilder(siteDbType, siteQueryElt, siteSchemaMap, projectAssignments, projectReplacements); string siteSelectQuery = sqlBuilder.Build(SQLCommand.SELECT); DataTable siteInfo = DBManager.Instance.ExecuteQuery(_siteConnStr, siteSelectQuery); // // Get actual schemas from !SiteData query // Dictionary<string, string> schemaMap = new Dictionary<string, string>(); if (siteInfo != null && siteInfo.Rows.Count > 0) { foreach (DataRow row in siteInfo.Rows) { schemaMap[row["SP_SCHEMA_TYPE"].ToString()] = row["USERNAME"].ToString(); } } // // Process other queries // if (string.IsNullOrEmpty(tableName)) { queryElts = from query in queryElts where query.Attribute("name").Value != Constants.TEMPLATE_QUERY && query.Attribute("name").Value != Constants.SITE_DATA_QUERY select query; } else { queryElts = from query in queryElts where query.Attribute("name").Value != Constants.TEMPLATE_QUERY && query.Attribute("name").Value != Constants.SITE_DATA_QUERY && query.Attribute("destination").Value.ToUpper() == tableName.ToUpper() select query; } DBType stagingDbType = Utility.GetDBType(_stagingConnStr); // NOTE - although it is possible to make use of an INTO clause to create a selection query that will // also automatically create the destination table, this has limitations, the most serious of which is // it is not safe to assume that the Source DB and Staging DB have the same security requirements. Instead, // we will always assume that security is separate for these two databases and that the connection strings for the // Source and Staging connections provide this information for each individual location. We also cannot assume that // the specified credentials have the power to create a Linked Server connection or that both SQL Server instances // allow ad hoc (OpenDataSource) queries. Instead, the provided credentials are used to copy the data to the // local machine and then bulk copied out to the staging server, bypassing the need for a more sophisticated security // check/edit) DBType plantDbType = Utility.GetDBType(_plantSchemaConnStr); if (plantDbType == DBType.ORACLE) { string plantDictConnStr = _settings[Constants.SPPID_PLANT_DICTIONARY]; if (Utility.IsBase64Encoded(plantDictConnStr)) { plantDictConnStr = EncryptionUtility.Decrypt(plantDictConnStr); } string pidSchemaConnStr = _settings[Constants.SPPID_PID_SCHEMA]; if (Utility.IsBase64Encoded(pidSchemaConnStr)) { pidSchemaConnStr = EncryptionUtility.Decrypt(pidSchemaConnStr); } string pidDictConnStr = _settings[Constants.SPPID_PID_DICTIONARY]; if (Utility.IsBase64Encoded(pidDictConnStr)) { pidDictConnStr = EncryptionUtility.Decrypt(pidDictConnStr); } _workingSet = new WorkingSet(_plantSchemaConnStr, plantDictConnStr, pidSchemaConnStr, pidDictConnStr); } else if (plantDbType == DBType.SQLServer) { _workingSet = new WorkingSet(_plantSchemaConnStr); } else { throw new Exception("SPPID DB type not supported."); } _workingSet.GrantPrivilege("SELECT"); foreach (XElement queryElt in queryElts) { sqlBuilder = new SQLBuilder(stagingDbType, queryElt, schemaMap, projectAssignments, projectReplacements, true); response.StatusList.Add(new Status() { Messages = new Messages() { "Query [" + queryElt.Attribute("name").Value + "] processed." } }); // // Delete existing staging table // string stagingTableName = queryElt.Attribute("destination").Value; string deleteQuery = string.Format(Constants.SQLSERVER_DELETE_TEMPLATE, stagingTableName); DBManager.Instance.ExecuteNonQuery(_stagingConnStr, deleteQuery); // // Create new staging table // string createQuery = sqlBuilder.Build(SQLCommand.CREATE); DBManager.Instance.ExecuteNonQuery(_stagingConnStr, createQuery); response.StatusList.Add(new Status() { Messages = new Messages() { "Staging table [" + stagingTableName + "] created." } }); // // Fetch data // string selectQuery = sqlBuilder.Build(SQLCommand.SELECT); DataTable result = DBManager.Instance.ExecuteQuery(_plantSchemaConnStr, selectQuery); response.StatusList.Add(new Status() { Messages = new Messages() { "New data fetched." } }); // // Bulk copy data to staging table // SqlBulkCopy bulkCopy = new SqlBulkCopy(_stagingConnStr); bulkCopy.DestinationTableName = stagingTableName; bulkCopy.WriteToServer(result); response.StatusList.Add(new Status() { Messages = new Messages() { "Data copied to staging table." } }); // // Add to data dictionary // DataObject objDef = new DataObject() { tableName = stagingTableName, objectNamespace = "SPPID", objectName = stagingTableName }; foreach (var pair in sqlBuilder.Keys) { objDef.keyProperties.Add(new KeyProperty() { keyPropertyName = pair.Key }); } foreach (DBField field in sqlBuilder.Fields) { DataProperty dataProperty = new DataProperty() { propertyName = field.Name, columnName = field.Name, dataType = Utility.ResolveDataType(field.DataType), isNullable = field.Nullable, }; if (sqlBuilder.Keys.ContainsKey(field.Name)) { dataProperty.keyType = (sqlBuilder.Keys[field.Name] == KeyType.AUTO) ? library.KeyType.unassigned : library.KeyType.assigned; } objDef.dataProperties.Add(dataProperty); } _dataDictionary.dataObjects.Add(objDef); } _workingSet.RevokePrivilege("SELECT"); } catch (Exception ex) { string error = "Error refreshing [" + tableName + "]: " + ex.Message; response.Level = StatusLevel.Error; response.Messages = new Messages() { error }; _logger.Error(error); } return response; }