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(")");
 }
Exemple #3
0
        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);
        }