private void GenerateFKBinding(string tn, string[] allfields, DataDiffTable table)
{
foreach (var map in table.Mappings)
{
if (-1 != Array.IndexOf(allfields, map.Key.ToLowerInvariant()))
{
var t = map.Value;
var usealiascodes =
_context.Mappings.Any(
_ => (_.FromTable.ToLowerInvariant() == t.ToLowerInvariant() || _.FromTable == "*") && _.FromField.ToLowerInvariant() == "aliascodes");
if (usealiascodes)
{
_output.WriteLine(
"\t\tupdate @{0} set {1} = (select id from {2} where {2}.code = {1}_code ) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
_output.WriteLine(
"\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.aliascodes like '%/'+{1}_code+'/%' ),-1) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
}
else
{
_output.WriteLine(
"\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.code = {1}_code ),-1) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
}
}
}
if (-1 != Array.IndexOf(allfields, "set_parent"))
{
_output.WriteLine("\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.code = parent_code ),-1) where {1} is null and {1}_code is not null ", tn, "parent", table.TableName);
}
}
private void GenerateTempTableDeclaration(string tn, string[] allfields, DataDiffTable table)
{
_output.Write("\tdeclare @" + tn + " table ( id int, " + (table.NoCode?"":"code nvarchar(255),") + " _exists bit default 0 ");
foreach (var fld in allfields)
{
if (table.Mappings.ContainsKey(fld))
{
_output.Write(", " + EscapeFieldName(fld) + " int");
_output.Write(", " + EscapeFieldName(fld) + "_code nvarchar(255)");
}
else if (fld == "set_parent")
{
_output.Write(", parent int");
_output.Write(", parent_code nvarchar(255)");
}
else if (fld == "parent")
{
_output.Write(", parent int");
_output.Write(", parent_code nvarchar(255)");
}
else
{
_output.Write(", " + EscapeFieldName(fld) + " nvarchar(max)");
}
}
_output.WriteLine(")");
}
private static DataDiffItem ResolveItem(DataDiffTable table, int id, string code)
{
DataDiffItem existed;
lock (table.Definitions){
existed = table.Definitions.FirstOrDefault(
_ => (0 != id && _.Id == id) || (!string.IsNullOrWhiteSpace(code) && code == _.Code));
if (null == existed)
{
existed = new DataDiffItem {
Id = id, Code = code
};
table.Definitions.Add(existed);
}
else
{
if (0 == existed.Id)
{
existed.Id = id;
}
if (string.IsNullOrWhiteSpace(existed.Code))
{
existed.Code = code;
}
}
}
return(existed);
}
private void GenerateInsertTemp(string tn, string[] allfields, DataDiffTable table)
{
var chunknumber = 0;
while (GenerateChunk(tn, allfields, table, chunknumber++))
{
}
}
private void GenerateExistsBinding(string tn, string[] allfields, DataDiffTable table)
{
if (!table.NoCode)
{
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id)",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id,code) select id,isnull(code,id) from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (code) select code from @{0} where _exists = 0 and code is not null and id is null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id)",
tn, table.TableName);
}
else
if (table.UseAliasCodes)
{
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id or this.aliascodes like '%/'+temp.code+'/%')",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id,code) select id,isnull(code,id) from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (code) select code from @{0} where _exists = 0 and code is not null and id is null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id or this.aliascodes like '%/'+temp.code+'/%')",
tn, table.TableName);
}
else
{
_output.WriteLine(
"\t\t\tupdate @{0} set _exists =1 from {1} this join @{0} temp on ( temp.id=this.id)",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id) select id from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
}
}
private void GenerateMainUpdate(string tn, string[] allfields, DataDiffTable table)
{
_output.Write("\t\tupdate {0} set ", table.TableName);
for (var i = 0; i < allfields.Length; i++)
{
var name = allfields[i];
if (table.NoCode && name == "code")
{
continue;
}
if (table.NoCode && name == "set_code")
{
continue;
}
if (!table.UseAliasCodes && name == "aliascodes")
{
continue;
}
if (name == "set_id")
{
name = "id";
}
if (name == "set_code")
{
name = "code";
}
if (name == "set_parent")
{
name = "parent";
}
_output.Write("{0} = isnull(x.{2}{0},{1}.{0})", EscapeFieldName(name), table.TableName, (name == "id" || name == "code") ? "set_" : "");
if (i != allfields.Length - 1)
{
_output.Write(", ");
}
}
_output.Write(", ");
_output.Write("version=getdate() ");
_output.WriteLine("from @{0} x join {1} on x.id = {1}.id ", tn, table.TableName);
}
private void GenerateTempTableDeclaration(string tn, string[] allfields, DataDiffTable table){
_output.Write("\tdeclare @" + tn + " table ( id int, "+(table.NoCode?"":"code nvarchar(255),")+" _exists bit default 0 ");
foreach (var fld in allfields){
if (table.Mappings.ContainsKey(fld)){
_output.Write(", " + EscapeFieldName(fld) + " int");
_output.Write(", " + EscapeFieldName(fld) + "_code nvarchar(255)");
}
else if (fld == "set_parent"){
_output.Write(", parent int");
_output.Write(", parent_code nvarchar(255)");
}
else if (fld == "parent")
{
_output.Write(", parent int");
_output.Write(", parent_code nvarchar(255)");
}
else{
_output.Write(", " + EscapeFieldName(fld) + " nvarchar(max)");
}
}
_output.WriteLine(")");
}
private bool GenerateChunk(string tn, string[] allfields, DataDiffTable table, int st){
_output.Write("\t\tinsert @{0} (id"+(table.NoCode?"":", code"), tn);
foreach (var allfield in allfields){
if (table.Mappings.ContainsKey(allfield)){
_output.Write(", {0}, {0}_code", allfield);
}
else if (allfield == "set_parent" || allfield == "parent"){
_output.Write(",parent ,parent_code");
}
else{
_output.Write(",{0}", EscapeFieldName(allfield));
}
}
_output.WriteLine(") values");
var defs = table.Definitions.OrderBy(_ => _.Id).ThenBy(_ => _.Code).ToArray();
bool last = false;
for (var i = st*1000; i < defs.Length && i < st*1000 + 1000; i++){
if (i == defs.Length - 1) last = true;
var def = defs[i];
_output.Write("\t\t\t(");
if (0 == def.Id){
_output.Write("null");
}
else{
_output.Write("'{0}'", def.Id);
}
if (!table.NoCode){
if (string.IsNullOrWhiteSpace(def.Code)){
_output.Write(", null");
}
else{
_output.Write(",'{0}'", def.Code);
}
}
foreach (var allfield in allfields){
if (table.Mappings.ContainsKey(allfield)){
OutMappedField(def, allfield);
}
else if (allfield == "set_parent" || allfield == "parent"){
OutParentField(def);
}
else{
OutUsualField(def, allfield);
}
}
if (i != table.Definitions.Count - 1 && i != st * 1000 + 1000-1)
{
_output.WriteLine("),");
}
else{
_output.WriteLine(")");
}
}
return !last;
}
private void GenerateInsertTemp(string tn, string[] allfields, DataDiffTable table){
var chunknumber= 0;
while(GenerateChunk(tn, allfields, table, chunknumber++)){}
}
private void GenerateExistsBinding(string tn, string[] allfields, DataDiffTable table){
if (!table.NoCode)
{
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id)",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id,code) select id,isnull(code,id) from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (code) select code from @{0} where _exists = 0 and code is not null and id is null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id)",
tn, table.TableName);
}else
if (table.UseAliasCodes){
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id or this.aliascodes like '%/'+temp.code+'/%')",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id,code) select id,isnull(code,id) from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (code) select code from @{0} where _exists = 0 and code is not null and id is null", tn,
table.TableName);
_output.WriteLine(
"\t\t\tupdate @{0} set id = this.id, code=this.code, _exists =1 from {1} this join @{0} temp on (temp.code = this.code or temp.id=this.id or this.aliascodes like '%/'+temp.code+'/%')",
tn, table.TableName);
}
else{
_output.WriteLine(
"\t\t\tupdate @{0} set _exists =1 from {1} this join @{0} temp on ( temp.id=this.id)",
tn, table.TableName);
_output.WriteLine(
"\t\t\tinsert {1} (id) select id from @{0} where _exists = 0 and id is not null", tn,
table.TableName);
}
}
private void GenerateFKBinding(string tn, string[] allfields, DataDiffTable table){
foreach (var map in table.Mappings){
if (-1 != Array.IndexOf(allfields, map.Key.ToLowerInvariant())){
var t = map.Value;
var usealiascodes =
_context.Mappings.Any(
_ => (_.FromTable.ToLowerInvariant() == t.ToLowerInvariant()||_.FromTable=="*") && _.FromField.ToLowerInvariant() == "aliascodes");
if (usealiascodes){
_output.WriteLine(
"\t\tupdate @{0} set {1} = (select id from {2} where {2}.code = {1}_code ) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
_output.WriteLine(
"\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.aliascodes like '%/'+{1}_code+'/%' ),-1) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
}
else{
_output.WriteLine(
"\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.code = {1}_code ),-1) where {1} is null and {1}_code is not null ",
tn, map.Key, map.Value);
}
}
}
if (-1 != Array.IndexOf(allfields, "set_parent")){
_output.WriteLine("\t\tupdate @{0} set {1} = isnull((select id from {2} where {2}.code = parent_code ),-1) where {1} is null and {1}_code is not null ", tn, "parent",table.TableName);
}
}
private void GenerateMainUpdate(string tn, string[] allfields, DataDiffTable table){
_output.Write("\t\tupdate {0} set ",table.TableName);
for (var i = 0;i<allfields.Length;i++){
var name = allfields[i];
if (table.NoCode && name == "code") continue;
if (table.NoCode && name == "set_code") continue;
if (!table.UseAliasCodes && name == "aliascodes") continue;
if (name == "set_id"){
name = "id";
}
if (name == "set_code"){
name = "code";
}
if (name == "set_parent"){
name = "parent";
}
_output.Write("{0} = isnull(x.{2}{0},{1}.{0})", EscapeFieldName(name), table.TableName, (name == "id" || name == "code") ? "set_" : "");
if (i != allfields.Length - 1){
_output.Write(", ");
}
}
_output.Write(", ");
_output.Write("version=getdate() ");
_output.WriteLine("from @{0} x join {1} on x.id = {1}.id ",tn,table.TableName);
}
private bool GenerateChunk(string tn, string[] allfields, DataDiffTable table, int st)
{
_output.Write("\t\tinsert @{0} (id" + (table.NoCode?"":", code"), tn);
foreach (var allfield in allfields)
{
if (table.Mappings.ContainsKey(allfield))
{
_output.Write(", {0}, {0}_code", allfield);
}
else if (allfield == "set_parent" || allfield == "parent")
{
_output.Write(",parent ,parent_code");
}
else
{
_output.Write(",{0}", EscapeFieldName(allfield));
}
}
_output.WriteLine(") values");
var defs = table.Definitions.OrderBy(_ => _.Id).ThenBy(_ => _.Code).ToArray();
bool last = false;
for (var i = st * 1000; i < defs.Length && i < st * 1000 + 1000; i++)
{
if (i == defs.Length - 1)
{
last = true;
}
var def = defs[i];
_output.Write("\t\t\t(");
if (0 == def.Id)
{
_output.Write("null");
}
else
{
_output.Write("'{0}'", def.Id);
}
if (!table.NoCode)
{
if (string.IsNullOrWhiteSpace(def.Code))
{
_output.Write(", null");
}
else
{
_output.Write(",'{0}'", def.Code);
}
}
foreach (var allfield in allfields)
{
if (table.Mappings.ContainsKey(allfield))
{
OutMappedField(def, allfield);
}
else if (allfield == "set_parent" || allfield == "parent")
{
OutParentField(def);
}
else
{
OutUsualField(def, allfield);
}
}
if (i != table.Definitions.Count - 1 && i != st * 1000 + 1000 - 1)
{
_output.WriteLine("),");
}
else
{
_output.WriteLine(")");
}
}
return(!last);
}
