public List<JObject> Extract(Table table)
{
var result = new List<JObject> ();
using (var extractConnection = new SqlConnection(table.ExtractConnectionString)) {
extractConnection.Open ();
if (table.MaxExtractedVersion.All (b => b == byte.MaxValue)) {
return result;
}
string src = !string.IsNullOrEmpty (table.CustomSql) ? "(" + table.CustomSql + ") y" : table.Name + " with(nolock)";
string selectNewCommand = string.Format (
"select top ({2}) * from {0} where version > {1} order by version asc",
src,
Common.ByteArrayToString (table.MaxExtractedVersion),
table.BatchSize);
using (var cmd = new SqlCommand(selectNewCommand, extractConnection)) {
using (SqlDataReader reader = cmd.ExecuteReader()) {
if (table.FieldNames == null) {
table.FieldNames = new List<string> ();
for (int i = 0; i < reader.FieldCount; i++)
table.FieldNames.Add (reader.GetName (i));
}
while (reader.Read()) {
Dictionary<string, object> changedRow = table.FieldNames.ToDictionary (col => col,
col => reader [col]);
result.Add (JObject.FromObject (changedRow));
}
}
}
}
return result;
}
private static void Fill(Table t)
{
int count = 0;
LookupIdIndex[t.KeyColumns] = t;
using (var extractConnection = new SqlConnection(t.ExtractConnectionString))
{
extractConnection.Open();
string selectAllCommand = !string.IsNullOrEmpty(t.CustomSql)
? t.CustomSql
: String.Format("select * from {0} with(nolock)", t.Name);
using (var cmd =
new SqlCommand(selectAllCommand, extractConnection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
var fieldNames = new List<string>();
for (int i = 0; i < reader.FieldCount; i++)
fieldNames.Add(reader.GetName(i));
while (reader.Read())
{
count++;
Dictionary<string, object> changedRowDict = fieldNames.ToDictionary(col => col,
col => reader[col]);
var jobj = JObject.Parse (JsonConvert.SerializeObject (changedRowDict));
Set(t.Environment, t.Name, t.GetKey(jobj), jobj);
}
}
}
}
Console.WriteLine("Cached {0} rows from {1}.", count, t);
}
public void UpsertRow(Table table, JObject changedRow)
{
string parentId = null;
if (table.Parent != null) {
parentId = table.Parent.CreateKey (changedRow);
}
using (var _loadConnection = new SqlConnection(Common.TargetConnectionString)) {
_loadConnection.Open ();
string data = JsonConvert.SerializeObject (changedRow);
string sql = string.Format ("exec UpsertRow @id='{0}', @parent_id='{1}', @data='{2}'", table.CreateKey (changedRow), parentId, data.Replace ("'", "''"));
using (var upsertRowCmd = new SqlCommand(sql, _loadConnection)) {
upsertRowCmd.ExecuteNonQuery ();
}
}
}
public void PersistVersion(Table t)
{
string sql = string.Format ("update dbo.[table] set extract_version = {0} where name = '{1}';",
Common.ByteArrayToString (t.MaxExtractedVersion), t.Name);
using (var _loadConnection = new SqlConnection(Common.TargetConnectionString)) {
_loadConnection.Open ();
using (var cmd = new SqlCommand(sql, _loadConnection)) {
cmd.ExecuteNonQuery ();
}
}
}