/// <summary>
/// Returns a list of all of the workspaces in the C_DB_WORKSHEET_NAME worksheet.
/// Workspaces that could not be opened will not be included.
/// </summary>
/// <returns></returns>
public List <Common.WorkspaceInfo> GetWorkspacesFromSpreadsheet()
{
// Error checking: make sure that the required worksheet can be found
if (!this.Open())
{
WmauError error = new WmauError(WmauErrorCodes.C_EXCEL_WORKBOOK_ERROR);
m_gpMessages.AddError(error.ErrorCodeAsInt, error.Message);
throw new Exception(error.Message);
}
Worksheet worksheet = this.GetWorksheetByName(C_DB_WORKSHEET_NAME);
if (worksheet == null)
{
WmauError error = new WmauError(WmauErrorCodes.C_EXCEL_WORKSHEET_ERROR);
m_gpMessages.AddError(error.ErrorCodeAsInt, error.Message);
throw new Exception(error.Message);
}
// Create the output element
List <Common.WorkspaceInfo> workspaceList = new List <Common.WorkspaceInfo>();
// Do some math and set up the "column header" range (used to determine which field is which)
int numColumns = worksheet.UsedRange.Columns.Count;
int numRows = worksheet.UsedRange.Rows.Count;
int dataStartRowNum = 2;
int dataEndRowNum = dataStartRowNum + numRows;
string headerStartCell = "A1";
string headerEndCell = GetExcelColumnFromIndex(numColumns) + "1";
Range headerRange = worksheet.get_Range(headerStartCell, headerEndCell);
// Iterate through all of the data rows (i.e., workspaces) in the worksheet
for (int rowNum = dataStartRowNum; rowNum < dataEndRowNum; rowNum++)
{
string name = string.Empty;
try
{
// Create a range for this row of cells
string dataStartCell = "A" + rowNum.ToString();
string dataEndCell = GetExcelColumnFromIndex(numColumns) + rowNum.ToString();
Range dataRange = worksheet.get_Range(dataStartCell, dataEndCell);
// Initialize the various values of the workspace configuration object
name = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_DB_ALIAS).Column);
if (string.IsNullOrEmpty(name))
{
// Stop reading through the worksheet once we've found a row without any workspace listed
break;
}
// Create the workspace configuration object that will be set up from this row
Common.WorkspaceInfo workspaceInfo = new Common.WorkspaceInfo();
workspaceInfo.Name = name;
workspaceInfo.Server = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_SERVER).Column);
workspaceInfo.Instance = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_INSTANCE).Column);
workspaceInfo.Database = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_DATABASE).Column);
workspaceInfo.Version = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_VERSION).Column);
workspaceInfo.UseOsAuthentication = GetValueAsBool(dataRange, FindCellMatchingText(headerRange, C_OS_AUTH).Column);
workspaceInfo.UseIndividualLogins = GetValueAsBool(dataRange, FindCellMatchingText(headerRange, C_INDIVIDUAL_LOGINS).Column);
if (workspaceInfo.UseOsAuthentication)
{
if (workspaceInfo.UseIndividualLogins)
{
throw new Exception("Workspace '" + name + "': OS authentication and individual logins are mutually exclusive");
}
// Nothing else to do if OS authentication is being used
}
else
{
if (workspaceInfo.UseIndividualLogins)
{
List <Common.WorkspaceInfo.LoginInfo> loginList =
GetLoginInfoFromSpreadsheet(workspaceInfo.Name);
foreach (Common.WorkspaceInfo.LoginInfo login in loginList)
{
workspaceInfo.AddLogin(
login.WmxUsername,
login.DatabaseUsername,
login.DatabasePassword,
login.IsPasswordEncrypted);
}
}
else
{
string username = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_USERNAME).Column);
string password = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_PASSWORD).Column);
bool isEncrypted = GetValueAsBool(dataRange, FindCellMatchingText(headerRange, C_IS_ENCRYPTED).Column);
// Only base64-encoded encrypted passwords are supported by this
// utility, so unencode the password before sending it along to
// the WMX object
if (isEncrypted)
{
byte[] pwBytes = System.Convert.FromBase64String(password);
password = System.Text.UTF8Encoding.UTF8.GetString(pwBytes);
}
// Store the shared username and password
workspaceInfo.AddLogin(string.Empty, username, password, isEncrypted);
}
}
workspaceList.Add(workspaceInfo);
}
catch (Exception ex)
{
WmauError error = new WmauError(WmauErrorCodes.C_WORKSPACE_FETCH_ERROR);
string errMsg = error.Message;
errMsg += System.Environment.NewLine + ex.Message;
errMsg += System.Environment.NewLine + "Skipping workspace '" + name + "'";
m_gpMessages.AddError(error.ErrorCodeAsInt, errMsg);
continue;
}
}
this.Close();
return(workspaceList);
}
/// <summary>
/// Required by IGPFunction2 interface; this function is called when the GP tool is ready to be executed.
/// </summary>
/// <param name="paramValues"></param>
/// <param name="trackCancel"></param>
/// <param name="envMgr"></param>
/// <param name="msgs"></param>
public override void Execute(IArray paramValues, ITrackCancel trackCancel, IGPEnvironmentManager envMgr, IGPMessages msgs)
{
// Do some common error-checking
base.Execute(paramValues, trackCancel, envMgr, msgs);
WorkspaceWorksheetWriter writer = null;
try
{
// Ensure that the current user has admin access to the current Workflow Manager DB
if (!CurrentUserIsWmxAdministrator())
{
throw new WmauException(WmauErrorCodes.C_USER_NOT_ADMIN_ERROR);
}
// Load the workspace information from the database
IJTXDatabaseConnectionManager dbConnectionManager = new JTXDatabaseConnectionManagerClass();
IJTXDatabaseConnection dbConnection = dbConnectionManager.GetConnection(WmxDatabase.Alias);
int numWorkspaces = dbConnection.DataWorkspaceCount;
// Copy the info into an intermediate list
List <Common.WorkspaceInfo> workspaceInfo = new List <Common.WorkspaceInfo>();
Dictionary <string, string> namedWorkspaces = new Dictionary <string, string>();
for (int i = 0; i < numWorkspaces; i++)
{
IJTXWorkspaceConfiguration workspace = dbConnection.get_DataWorkspace(i);
Common.WorkspaceInfo tempInfo = new Common.WorkspaceInfo();
tempInfo.Name = workspace.Name;
tempInfo.Server = workspace.Server;
tempInfo.Instance = workspace.Instance;
tempInfo.Database = workspace.Database;
tempInfo.Version = workspace.Version;
tempInfo.UseOsAuthentication = workspace.OSAuthentication;
tempInfo.UseIndividualLogins = workspace.IndividualLogins;
int numLogins = workspace.LoginCount;
for (int j = 0; j < numLogins; j++)
{
IJTXWorkspaceLogin tempLogin = workspace.get_Login(j);
tempInfo.AddLogin(tempLogin.JTXUserName, tempLogin.UserName, tempLogin.Password, true);
}
if (namedWorkspaces.Keys.Contains(tempInfo.Name))
{
msgs.AddWarning("Multiple workspaces detected with name '" + tempInfo.Name +
"'; only the first workspace found will be exported.");
}
else
{
workspaceInfo.Add(tempInfo);
namedWorkspaces.Add(tempInfo.Name, null);
}
}
// Save the list to an Excel worksheet
writer = new WorkspaceWorksheetWriter(this.m_excelFilePath, msgs);
writer.SaveWorkspacesToSpreadsheet(workspaceInfo);
msgs.AddMessage(Properties.Resources.MSG_DONE);
}
catch (WmauException wmEx)
{
try
{
msgs.AddError(wmEx.ErrorCodeAsInt, wmEx.Message);
}
catch
{
// Catch anything else that possibly happens }
}
}
catch (Exception ex)
{
try
{
WmauError error = new WmauError(WmauErrorCodes.C_WORKSPACE_LOAD_ERROR);
msgs.AddError(error.ErrorCodeAsInt, error.Message + "; " + ex.Message);
}
catch
{
// Catch anything else that possibly happens }
}
}
finally
{
if (writer != null)
{
writer.Close();
}
}
}
