public SsisProjectNodeFactory(BipsDrive drive, SsisDbFolderDescriptor folder, SsisDbProjectDescriptor project)
{
_drive = drive;
_folder = folder;
_project = project;
}
public IEnumerable <SsisDbProjectDescriptor> GetProjectsForFolder(SsisDbFolderDescriptor folder)
{
var connectionString = String.Format(
"Data Source={0};Initial Catalog=SSISDB;Integrated Security=True",
_serverName
);
var sql = String.Format(@"
select P.*, F.Name as folder_name
from [ssisdb].[catalog].[projects] as P
inner join [ssisdb].[catalog].[folders] as F on
P.folder_id=F.folder_id
where P.folder_id={0}
order by p.[Name]
",
folder.FolderId);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var cmd = new SqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var oname = reader.GetOrdinal("name");
var oid = reader.GetOrdinal("project_id");
var foid = reader.GetOrdinal("folder_id");
var odesc = reader.GetOrdinal("description");
var ofolder = reader.GetOrdinal("folder_name");
var odeployedby = reader.GetOrdinal("deployed_by_name");
var oinstant = reader.GetOrdinal("created_time");
var odeployinstant = reader.GetOrdinal("last_deployed_time");
var ovalidationinstant = reader.GetOrdinal("last_validation_time");
var ovalidationstatus = reader.GetOrdinal("validation_status");
while (reader.Read())
{
var projectId = reader.GetInt64(oid);
var folderId = reader.GetInt64(foid);
var name = reader.GetString(oname);
var folderName = reader.GetString(ofolder);
var description = reader.IsDBNull(odesc) ? null : reader.GetString(odesc);
var deployedBy = reader.GetString(odeployedby);
var createdTime = reader.IsDBNull(oinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(oinstant);
var lastValidationTime = reader.IsDBNull(ovalidationinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(ovalidationinstant);
var lastDeployedTime = reader.IsDBNull(odeployinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(odeployinstant);
var validationStatus = reader.GetString(ovalidationstatus)[0];
var descriptor = new SsisDbProjectDescriptor
{
Server = _serverName,
ProjectId = projectId,
FolderId = folderId,
Name = name,
FolderName = folderName,
Description = description,
DeployedBy = deployedBy,
CreatedTime = createdTime,
LastValidationTime = lastValidationTime,
LastDeployedTime = lastDeployedTime,
ValidationStatus = validationStatus
};
yield return(descriptor);
}
}
}
}
}
public IEnumerable<SsisDbProjectDescriptor> GetProjectsForFolder(SsisDbFolderDescriptor folder)
{
var connectionString = String.Format(
"Data Source={0};Initial Catalog=SSISDB;Integrated Security=True",
_serverName
);
var sql = String.Format(@"
select P.*, F.Name as folder_name
from [ssisdb].[catalog].[projects] as P
inner join [ssisdb].[catalog].[folders] as F on
P.folder_id=F.folder_id
where P.folder_id={0}
order by p.[Name]
",
folder.FolderId);
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var cmd = new SqlCommand(sql, connection))
{
cmd.CommandType = CommandType.Text;
using (var reader = cmd.ExecuteReader())
{
var oname = reader.GetOrdinal("name");
var oid = reader.GetOrdinal("project_id");
var foid = reader.GetOrdinal("folder_id");
var odesc = reader.GetOrdinal("description");
var ofolder = reader.GetOrdinal("folder_name");
var odeployedby = reader.GetOrdinal("deployed_by_name");
var oinstant = reader.GetOrdinal("created_time");
var odeployinstant = reader.GetOrdinal("last_deployed_time");
var ovalidationinstant = reader.GetOrdinal("last_validation_time");
var ovalidationstatus = reader.GetOrdinal("validation_status");
while (reader.Read())
{
var projectId = reader.GetInt64(oid);
var folderId = reader.GetInt64(foid);
var name = reader.GetString(oname);
var folderName = reader.GetString(ofolder);
var description = reader.IsDBNull(odesc) ? null : reader.GetString(odesc);
var deployedBy = reader.GetString(odeployedby);
var createdTime = reader.IsDBNull(oinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(oinstant);
var lastValidationTime = reader.IsDBNull(ovalidationinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(ovalidationinstant);
var lastDeployedTime = reader.IsDBNull(odeployinstant) ? DateTimeOffset.MinValue : reader.GetDateTimeOffset(odeployinstant);
var validationStatus = reader.GetString(ovalidationstatus)[0];
var descriptor = new SsisDbProjectDescriptor
{
Server = _serverName,
ProjectId = projectId,
FolderId = folderId,
Name = name,
FolderName = folderName,
Description = description,
DeployedBy= deployedBy,
CreatedTime = createdTime,
LastValidationTime = lastValidationTime,
LastDeployedTime = lastDeployedTime,
ValidationStatus = validationStatus
};
yield return descriptor;
}
}
}
}
}
public SsisFolderNodeFactory( BipsDrive drive, SsisDbFolderDescriptor folder )
{
_drive = drive;
_folder = folder;
}