public void InsertOrUpdateEtlPackage(EtlPackage package) { var sql = @" if exists(select 1 from {0}[EtlPackages] p with (nolock) where p.[Id] = @id) update {0}[EtlPackages] set [Name] = @name, [Enabled] = @enabled, [RunIntervalSeconds] = @ris, [Text] = @text where [Id] = @id else insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text]) values (@id, @name, @enabled, @ris, @text) "; var serializer = new EtlPackageXmlSerializer(); var packageText = serializer.Serialize(package); using (var conn = CreateConnection()) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format(sql, _schemaToken); cmd.Parameters.AddWithValue("id", package.Id); cmd.Parameters.AddWithValue("name", package.Name); cmd.Parameters.AddWithValue("enabled", package.Enabled); cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds); cmd.Parameters.AddWithValue("text", packageText); cmd.ExecuteNonQuery(); } }
private void UpdateEtlPackage(EtlPackage package) { var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension); var serializer = new EtlPackageXmlSerializer(); var xml = serializer.Serialize(package); File.WriteAllText(filePath, xml); }
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 void UpdateCurrentPackageXml() { if (_currentPackage != null) { var serializer = new EtlPackageXmlSerializer(); var xml = serializer.Serialize(_currentPackage); txtPackageXml.Text = xml; } else { txtPackageXml.Text = ""; } }
private void AddEtlPackage(EtlPackage package) { var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension); if (File.Exists(filePath)) { throw new EtlPackageAlreadyExistsException(package.Id); } var serializer = new EtlPackageXmlSerializer(); var xml = serializer.Serialize(package); File.WriteAllText(filePath, xml); }
public void InsertPackage(EtlPackage package) { var sql = @" insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text]) values (@id, @name, @enabled, @ris, @text) "; var serializer = new EtlPackageXmlSerializer(); var packageText = serializer.Serialize(package); using (var conn = CreateConnection()) { conn.Open(); var cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = string.Format(sql, _schemaToken); cmd.Parameters.AddWithValue("id", package.Id); cmd.Parameters.AddWithValue("name", package.Name); cmd.Parameters.AddWithValue("enabled", package.Enabled); cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds); cmd.Parameters.AddWithValue("text", packageText); try { cmd.ExecuteNonQuery(); } catch (SqlException exc) { if (exc.Number == PRIMARY_KEY_VIOLATION_NUMBER) { throw new EtlPackageAlreadyExistsException(package.Id, null, exc); } else { throw; } } } }
private List <EtlPackage> SelectEtlPackages() { var packages = new List <EtlPackage>(); if (!Directory.Exists(_directoryPath)) { return(packages); } var filePathes = Directory.GetFiles(_directoryPath, "*" + this.PackageFileExtension); var serializer = new EtlPackageXmlSerializer(); foreach (var filePath in filePathes) { var xml = File.ReadAllText(filePath); var package = serializer.Deserialize(xml); if (package != null) { packages.Add(package); } } return(packages); }
//todo: localize log message public void ExecuteCommand(EtlConsoleArguments options) { System.Console.WriteLine("Verifying input parameters..."); var errorMsg = VerifyArguments(options); if (!String.IsNullOrEmpty(errorMsg)) { throw new Exception(String.Format("Input parameters incorrect: {0}", errorMsg)); } var providerFactory = DbProviderFactories.GetFactory(options.CommandOptions[PARAM_NAME_PROVIDER_NAME]); var packageText = String.Empty; System.Console.WriteLine("Receiving package text"); using (var connection = providerFactory.CreateConnection()) { connection.ConnectionString = options.CommandOptions[PARAM_NAME_CONNECTION_STRING]; var command = connection.CreateCommand(); command.CommandText = options.CommandOptions[PARAM_NAME_PACKAGE_QUERY]; command.CommandTimeout = 60; // 1 minute command.CommandType = System.Data.CommandType.Text; connection.Open(); using (var reader = command.ExecuteReader()) { if (reader.Read()) { if (reader.FieldCount == 1) { packageText = reader[0].ToString(); } else { throw new Exception("Package text query result contains more than one element"); } } else { throw new Exception("Package text query result contains no rows"); } if (reader.Read()) { throw new Exception("Package text query result contains multiple rows"); } } } if (String.IsNullOrEmpty(packageText)) { throw new Exception("Package text cannot be null or empty"); } System.Console.WriteLine("Deserializing package"); var etlPackageSerializer = new EtlPackageXmlSerializer(); var package = etlPackageSerializer.Deserialize(packageText); var etlLoggerInfo = new EtlLoggerInfo() { ConnectionString = options.CommandOptions[PARAM_NAME_CONNECTION_STRING], LoggerType = options.CommandOptions[PARAM_NAME_LOGGER_TYPE], SchemaName = options.CommandOptions.ContainsKey(PARAM_NAME_SCHEMA) ? options.CommandOptions[PARAM_NAME_SCHEMA] : String.Empty, }; var packageLogger = EtlLoggers.GetLogger(etlLoggerInfo); System.Console.WriteLine("Executing package in a sub-session"); var result = package.Invoke(packageLogger); System.Console.WriteLine("Package has been executed"); }
private List<EtlPackage> SelectEtlPackages() { var packages = new List<EtlPackage>(); if (!Directory.Exists(_directoryPath)) { return packages; } var filePathes = Directory.GetFiles(_directoryPath, "*" + this.PackageFileExtension); var serializer = new EtlPackageXmlSerializer(); foreach (var filePath in filePathes) { var xml = File.ReadAllText(filePath); var package = serializer.Deserialize(xml); if (package != null) { packages.Add(package); } } return packages; }
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; }