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);
}
