RhinoETL与webservice结果合并表
本文关键字:合并 结果 webservice RhinoETL | 更新日期: 2023-09-27 17:51:25
我在SQL Server中有一个从Web服务填充的表。我希望它能定期更新。我想有一些类似于SQL合并操作。
这是我定义我的源(Web服务)和我的目标(SQL表),我定义如何处理从源缺少从目标和匹配。
让我们考虑一个场景,我在表Description和Deleted中只有两个字段,并且Web Service只提供Description。
-
如果一个描述在表和web服务中都存在,那么它只是更新(或不更新)。
-
如果一个描述存在于web服务中,但不在表中,我希望它被插入
-
如果一个描述不再存在于web服务器中,我希望它被标记为删除= true
我现在拥有的是:
public class WebServiceResults: AbstractOperation
{
public WebServiceResults()
{
var WebService = new WebService();
WSResults = WebService.GetResults();
}
IEnumerable<WSResult> WSResults { get; set; }
public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
{
foreach(var obj in WSResults)
yield return Row.FromObject(obj);
}
}
class SQLTableResults : AbstractOperation
{
public SQLTableResults()
{
SQLResults = data.MyTable.Select(x=>new {x.Description,x.Deletet});
}
Data data = new Data();
IEnumerable<SQLResult> SQLResults { get; set; }
public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
{
foreach (var obj in SQLResults)
yield return Row.FromObject(obj);
}
}
public override void Dispose()
{
data.Dispose();
base.Dispose();
}
}
class JoinTables : JoinOperation
{
protected override Row MergeRows(Row leftRow, Row rightRow)
{
Row row = leftRow.Clone();
row["Description2"] = rightRow["Description"];
return row;
}
protected override void SetupJoinConditions()
{
FullOuterJoin
.Left("Description")
.Right("Description");
}
}
class MergeTables : AbstractOperation
{
Data data = new Data();
public MergeTables()
{ }
public override IEnumerable<Row> Execute(IEnumerable<Row> rows)
{
foreach (var obj in rows)
{
if (String.IsNullOrEmpty((string)obj["Description2"]))
{
//Code for not matched at target
yield return Row.FromObject(obj);
}
if (String.IsNullOrEmpty((string)obj["Description"]))
{
//Code for not matched at source
yield return Row.FromObject(obj);
}
{
//Code for matched
yield return Row.FromObject(obj);
}
}
}
public override void Dispose()
{
data.Dispose();
base.Dispose();
}
}
protected override void Initialize()
{
Register(
new JoinTables()
.Left(new SQLTableResults())
.Right(new WebServiceResults())
);
Register(new MergeTables());
foreach (var error in GetAllErrors())
Console.Write(error.Message);
}
是这条路吗?我会想象一些更循序渐进的过程,比如
Register(new NotMatchedAtSourceOperation());
Register(new NotMatchedAtTargetOperation());
Register(new MatchedOperation());
,但据我所知,每个寄存器将其行返回到下一个,所以如果我过滤不匹配的,那么其他两个将不做任何事情。
我应该为每个案例创建一个新流程吗?
顺便说一下,我正在寻找关于RhinoEtl的文档。你知道有什么联系吗?教程吗?
确定使用完整外部连接的单个操作中的合并操作。你可以在这里看到一个例子。这并不是你所需要的,所以我会尽量根据你的情况修改如下:
protected override Row MergeRows(Row wsRow, Row dbRow) {
Row row;
// if the db row doesn't exist, then the ws row is new, and it should be inserted
if (dbRow["id"] == null) {
row = wsRow.Clone();
row["action"] = "Insert";
row["deleted"] = false;
return row;
}
// if the ws row doesn't exist, it should be marked as deleted in the database (if not already)
if (wsRow["id"] == null) {
row = dbRow.Clone();
row["deleted"] = true;
row["action"] = dbRow["deleted"].Equals(true) ? "None" : "Update";
return row;
}
// ws and db descriptions match, but check and make sure it's not marked as deleted in database
row = wsRow.Clone();
row["deleted"] = false;
row["action"] = dbRow["deleted"].Equals(true) ? "Update" : "None";
return row;
}
protected override void SetupJoinConditions() {
FullOuterJoin.Left("description").Right("description");
}
运行此操作后,每一行将有一个"插入"、"更新"或"无"的动作。基于此操作,您可以为SqlBatchOperation编写插入和更新语句以执行。