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