private EtlPackage SelectEtlPackage(string etlPackageId)
{
var sql = @"
select top 1
p.[Id],
p.[Name],
p.[RunIntervalSeconds],
p.[Text],
p.[Enabled]
from
{0}[EtlPackages] p with (nolock)
where
p.[Id] = @id
";
EtlPackage package = null;
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", etlPackageId);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var id = EtlValueConverter.ToString(reader["Id"]);
var name = EtlValueConverter.ToString(reader["Name"]);
var enabled = EtlValueConverter.ParseBoolean(reader["Enabled"]);
var runIntervalSeconds = Convert.ToInt32(reader["RunIntervalSeconds"]);
var text = EtlValueConverter.ToString(reader["Text"]);
if (!string.IsNullOrEmpty(text))
{
var serializer = new EtlPackageXmlSerializer();
package = serializer.Deserialize(text);
}
else
{
package = new EtlPackage();
}
package.Id = id;
package.Name = name;
package.Enabled = enabled;
package.RunIntervalSeconds = runIntervalSeconds;
}
}
}
return(package);
}
private List <EtlVariable> SelectEtlVariables(string etlPackageId, string etlSessionId)
{
const string sql =
@"select
sv.[Name],
sv.[Modifier],
sv.[Value],
sv.[LogDateTime],
sv.[LogUtcDateTime],
sv.[IsSecure]
from
{0}[EtlVariables] sv with (nolock)
where
sv.[EtlPackageId] = @EtlPackageId and
sv.[EtlSessionId] = @EtlSessionId
";
var variables = new List <EtlVariable>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("@EtlPackageId", etlPackageId);
cmd.Parameters.AddWithValue("@EtlSessionId", etlSessionId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var es = new EtlVariable
{
EtlPackageId = etlPackageId,
EtlSessionId = etlSessionId,
Name = EtlValueConverter.ToString(reader["Name"]),
Modifier = (EtlVariableModifier)EtlValueConverter.ParseInt32(reader["Modifier"]),
Value = EtlValueConverter.ToString(reader["Value"]),
IsSecure = EtlValueConverter.ParseBoolean(reader["IsSecure"]),
DateTime = EtlValueConverter.ParseDateTime(reader["LogDateTime"]),
UtcDateTime = EtlValueConverter.ParseDateTime(reader["LogUtcDateTime"]),
};
variables.Add(es);
}
}
}
return(variables);
}
private List <EtlPackage> SelectEtlPackages()
{
var sql = @"
select
p.[Id],
p.[Name],
p.[RunIntervalSeconds],
p.[Enabled]
from
{0}[EtlPackages] p with (nolock)
order by
p.[Name]
";
var packages = new List <EtlPackage>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
packages.Add
(
new EtlPackage
{
Id = EtlValueConverter.ToString(reader["Id"]),
Name = EtlValueConverter.ToString(reader["Name"]),
Enabled = EtlValueConverter.ParseBoolean(reader["Enabled"]),
RunIntervalSeconds = Convert.ToInt32(reader["RunIntervalSeconds"]),
}
);
}
}
}
return(packages);
}