public void MergeTable(Chunk chunk, SqlConnection cn, SqlTransaction tn) { DateTime start = DateTime.Now; Console.WriteLine("{0} > Merging table: {1}...", chunk.ID, TargetTableName); // Figure out script name to use PropertyInfo p = typeof(LoadScripts).GetProperty(String.Format("merge_{0}", TargetTableName), BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.IgnoreCase); StringBuilder sql = new StringBuilder((string)p.GetValue(null, null)); sql.Replace("$loaddb", chunk.LoaderDB.InitialCatalog); sql.Replace("$targetdb", chunk.TargetDB.InitialCatalog); sql.Replace("$tablename", String.Format("{0}_{1}", chunk.ChunkId, SourceTableName)); var optPlaceholder = GetOptionalPlaceholder(chunk, cn, tn); if (optPlaceholder != null) { sql.Replace("$opt", optPlaceholder); } //sql.Replace("$run_id", chunk.RunId.ToString()); using (SqlCommand cmd = new SqlCommand(sql.ToString(), cn, tn)) { //the command timeout limit is one day: cmd.CommandTimeout = 86400; //cmd.Parameters.Add("@run_id", SqlDbType.SmallInt).Value = RunID; cmd.ExecuteNonQuery(); } Console.WriteLine("{0} > ... merged {1} in {2} sec.", chunk.ID, TargetTableName, (DateTime.Now - start).TotalSeconds); }
public override IEnumerable<string> selectObjects(Chunk chunk) { // The name of source file (without path, extensions and file name suffix) is passed to Selector // which will concatenate this string for the beginning of the read lines. var fileName = Path.GetFileNameWithoutExtension(chunk.Filename); var fileSuffix = chunk.FileSuffix; if ((fileSuffix != null) && (fileName.EndsWith(fileSuffix))) { fileName = fileName.Substring(0, fileName.Length - fileSuffix.Length); } Selector.Prefix = fileName; return this.Select(Selector.selectObjects); }
protected virtual string GetOptionalPlaceholder(Chunk chunk, SqlConnection cn, SqlTransaction tn) { return null; }
private void SaveChunk(Chunk chunk) { using (var context = new DatabaseContext()) { batch.ModifyChunk((Chunk)chunk, context); context.Commit(); } }
private void Merge(Chunk chunk) { chunk.MergeStart = DateTime.Now; SaveChunk(chunk); var chunkService = ObjectFactory.GetNamedInstance<ChunkService<string>>("WithAutoWiring"); chunkService.MergeTables(chunk); SaveChunk(chunk); }
public void CreateChunk(Chunk chunk, DatabaseContext context) { var sql = @" INSERT chunk (batch_id, chunk_id, prepare_start, prepare_end, load_start, load_end, merge_start, merge_end, cleanup_start, cleanup_end) VALUES (@batch_id, @chunk_id, @prepare_start, @prepare_end, @load_start, @load_end, @merge_start, @merge_end, @cleanup_start, @cleanup_end); "; using (var cmd = new SqlCommand(sql, context.Connection, context.Transaction)) { AppendChunkCreateModifyParameters(chunk, cmd); cmd.ExecuteNonQuery(); } }
private Chunk LoadChunkFromDataReader(SqlDataReader dr) { var c = new Chunk(); int o = -1; c.BatchID = dr.GetInt32(++o); c.ChunkId = dr.GetString(++o); c.PrepareStart = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.PrepareEnd = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.LoadStart = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.LoadEnd = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.MergeStart = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.MergeEnd = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.CleanupStart = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.CleanupEnd = !dr.IsDBNull(++o) ? dr.GetDateTime(o) : DateTime.MinValue; c.Binary = binary; c.BulkPath = bulkPath; c.Filename = Path.Combine(sourcePath, c.ChunkId); c.FileSuffix = fileSuffix; c.ColumnOrders = columnOrders; c.LoaderDB.ConnectionString = loaderDB.ConnectionString; c.TargetDB.ConnectionString = targetDB.ConnectionString; return c; }
private void AppendChunkCreateModifyParameters(Chunk chunk, SqlCommand cmd) { cmd.Parameters.Add("@batch_id", SqlDbType.Int).Value = batchID; cmd.Parameters.Add("@chunk_id", SqlDbType.NVarChar).Value = chunk.ChunkId; cmd.Parameters.Add("@prepare_start", SqlDbType.DateTime).Value = chunk.PrepareStart == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.PrepareStart; cmd.Parameters.Add("@prepare_end", SqlDbType.DateTime).Value = chunk.PrepareEnd == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.PrepareEnd; cmd.Parameters.Add("@load_start", SqlDbType.DateTime).Value = chunk.LoadStart == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.LoadStart; cmd.Parameters.Add("@load_end", SqlDbType.DateTime).Value = chunk.LoadEnd == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.LoadEnd; cmd.Parameters.Add("@merge_start", SqlDbType.DateTime).Value = chunk.MergeStart == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.MergeStart; cmd.Parameters.Add("@merge_end", SqlDbType.DateTime).Value = chunk.MergeEnd == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.MergeEnd; cmd.Parameters.Add("@cleanup_start", SqlDbType.DateTime).Value = chunk.CleanupStart == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.CleanupStart; cmd.Parameters.Add("@cleanup_end", SqlDbType.DateTime).Value = chunk.CleanupEnd == DateTime.MinValue ? (object)DBNull.Value : (object)chunk.CleanupEnd; }
public void ModifyChunk(Chunk chunk, DatabaseContext context) { var sql = @" UPDATE chunk SET prepare_start = @prepare_start, prepare_end = @prepare_end, load_start = @load_start, load_end = @load_end, merge_start = @merge_start, merge_end = @merge_end, cleanup_start = @cleanup_end WHERE batch_id = @batch_id AND chunk_id = @chunk_id"; using (var cmd = new SqlCommand(sql, context.Connection, context.Transaction)) { AppendChunkCreateModifyParameters(chunk, cmd); cmd.ExecuteNonQuery(); } }
public override void Run() { // Find files matching pattern var dir = Path.GetDirectoryName(Source); var pat = Path.GetFileName(Source); var ext = Path.GetExtension(Source); var files = Directory.GetFiles(dir, pat); var currentMapper = ObjectFactory.GetInstance<Mapper<string>>(); if (!currentMapper.PreferredSourceFileExt.Equals(ext)) { Console.WriteLine("WARNING: the extension of the given Source does not equal the " + "preferred file extension of the given Mapper type! Are you sure you want to use it?"); } if (files.Length == 0) { throw new ArgumentException("No files matching the source pattern were found."); } else { Console.WriteLine("Found {0} file(s), now creating batch...", files.Length); } var b = new Batch(); using (var context = new DatabaseContext()) { // Create batch object b.SourcePath = Path.GetDirectoryName(Source); b.FileSuffix = FileSuffix; b.ColumnOrders = ColumnOrders; b.BulkPath = BulkPath; b.TargetDB.DataSource = Server; b.TargetDB.InitialCatalog = TargetDB; b.TargetDB.IntegratedSecurity = IntegratedSecurity; if (!IntegratedSecurity) { b.TargetDB.UserID = UserID; b.TargetDB.Password = Password; } b.LoaderDB.DataSource = Server; b.LoaderDB.InitialCatalog = LoaderDB; b.LoaderDB.IntegratedSecurity = IntegratedSecurity; if (!IntegratedSecurity) { b.LoaderDB.UserID = UserID; b.LoaderDB.Password = Password; } b.Binary = binary; // Create chunks foreach (var f in files) { var c = new Chunk(); c.ChunkId = Path.GetFileName(f); b.Chunks.Add(c); } b.Save(context); b.CreateChunks(context); context.Commit(); } Console.WriteLine("New batch created with ID {0}", b.BatchID); }