viewing/comparing SQL code in difference

fordc03

okay, since I've been tasked with re-inventing the wheel...

I've got a collection of differences...now, in my windows app, since SQLcompare only does 1 database at a time and I have a need to do a server at a time...I have it grouped by databases, then by differences...
basically, I have a baseline database and need to compare that baseline with about 500 other databases on a server to verify they're the same.

Well, like in SQLCompare, I kind of need the ability to select a row or difference and have it display the SQL code from both...I'll have to figure out a highlighting option later...right now...I just wanna know the best way to "select" the T-SQL code from Obect1 and Object2...

Am I making sense?

fordc03

Let me add...I can do the compare just fine...I just need a way to now select that difference and display the code from each side Object1 and Object2 in a split panel.

what I am not sure how to do is select the code of the difference or if it's available even.

Chris Auckland

Hi,

Sorry about the delay in this response.

You can use the reporting functionality of the SQL Compare GUI for exporting results as HTML. This report would contain the SQL from both objects in a split panel.

If this sounds like the sort of thing you are trying to achieve, you could send me a private email and I can send you an example C# Project to create HTML reports with SQL Toolkit.

fordc03

Which is exactly what I already did, and you sent me what I needed! Thanks!

I posted this before I read that I could email support for an example.

Chris Auckland

I thought it sounded familiar.

Thanks for letting me know.

DarkoJ.

Hello,
I didn't want to start a new topic on the forum since I think this could be the thing I am looking for.
I am using SQL Toolkit API and writing an application which compares and sync's two databases, everything works fine but how can I create some kind of pane with SQL Differences so when I select the difference which is found between two db's I see how the SQL differs in the two instances in a text box or something like that?

Thank you

fordc03

Use use the Regions object...
You should be able to modify the below example to fit your needs, but this will write out the differences to an XML file.

static void GenerateXml(XmlTextWriter writer, RedGate.SQLCompare.Engine.Database dbSourceDatabase, RedGate.SQLCompare.Engine.Database dbTargetDatabase, RedGate.SQLCompare.Engine.Differences obDatabaseDifferences, RedGate.SQLCompare.Engine.Options enOptions)
		{
			writer.WriteStartElement("root");
			writer.WriteStartElement("databases");
			string line1 = string.Format("Databases compared on {0}", DateTime.Now.ToString());
			writer.WriteElementString("timestamp", line1);

			writer.WriteStartElement("database");
			writer.WriteAttributeString("id", "1");

			writer.WriteElementString("servername", dbSourceDatabase.ConnectionProperties.ServerName);
			writer.WriteElementString("databasename", dbSourceDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteElementString("description", "Database 1 - " +dbSourceDatabase.ConnectionProperties.ServerName+ "/" +dbSourceDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteEndElement();

			writer.WriteStartElement("database");
			writer.WriteAttributeString("id", "2");
			writer.WriteElementString("servername", dbTargetDatabase.ConnectionProperties.ServerName);
			writer.WriteElementString("databasename", dbTargetDatabase.ConnectionProperties.DatabaseName);
			
			writer.WriteElementString("description", "Database 2 - " +dbTargetDatabase.ConnectionProperties.ServerName + "/" + dbTargetDatabase.ConnectionProperties.DatabaseName);
			writer.WriteEndElement();

			// databases
			writer.WriteEndElement();
			// Start writing the database differences.
			writer.WriteStartElement("differences");
			
			for(int i = 0; i <= obDatabaseDifferences.Count-1; i++)
			{
				Difference diff = (Difference)obDatabaseDifferences[i];
				if (diff.Selected == true) // only report selected objects!
				{					
					writer.WriteStartElement("difference");
					writer.WriteAttributeString("id", i.ToString());

					writer.WriteStartElement("name");
					writer.WriteString(diff.Name);
					writer.WriteEndElement();

					writer.WriteStartElement("objecttype");
					writer.WriteString(diff.DatabaseObjectType.ToString());
					writer.WriteEndElement();

					int existsin1 = -1;
					if(diff.Type == DifferenceType.OnlyIn2)
						existsin1 = 0;

					writer.WriteStartElement("existsin1");
					writer.WriteString(existsin1.ToString());
					writer.WriteEndElement();
					
					int existsin2 = -1;
					if(diff.Type == DifferenceType.OnlyIn1)
						existsin2 = 0;

					writer.WriteStartElement("existsin2");
					writer.WriteString(existsin2.ToString());
					writer.WriteEndElement();


					writer.WriteStartElement("action");
					writer.WriteString(diff.Type.ToString());
					writer.WriteEndElement();
					//Begin bit to show the SQL
					writer.WriteStartElement("comparestrings");
					Work w = new Work();
								
					Regions regions1 = w.ScriptObject(diff.ObjectIn1, enOptions);
					Regions regions2 = w.ScriptObject(diff.ObjectIn2, enOptions);
				
					string[] string1;
					string[] string2;
					int iCounter = 0;
					int iCommonLength=0;

					string1 = regions1.ToString().Split('\n');
					string2 = regions2.ToString().Split('\n');
					// Get the length of the shortest SQL block
					// Compare the shortest number of lines
					// to prevent an index out-of-range error
					if (string1.Length < string2.Length) iCommonLength = string1.Length;
					else iCommonLength = string2.Length;
				
					while (iCounter < iCommonLength) 
					{
						writer.WriteStartElement("comparestring");
						writer.WriteStartElement("action");
						if (string1[iCounter].Equals(string2[iCounter])) writer.WriteString("equal");
						else writer.WriteString("different");
						CapElement(writer, string1[iCounter], string2[iCounter]);
						iCounter++;
					}
					//fill out the longest SQL block
					if (string1.Length > string2.Length) 
					{
						while (iCounter < string1.Length) 
						{
							writer.WriteStartElement("comparestring");
							writer.WriteStartElement("action");
							writer.WriteString("leftonly");
							CapElement(writer, string1[iCounter], "");
							iCounter++;
						}
					}
					if (string1.Length < string2.Length) 
					{
						while (iCounter < string2.Length) 
						{
							writer.WriteStartElement("comparestring");
							writer.WriteStartElement("action");
							writer.WriteString("rightonly");
							CapElement(writer, "", string2[iCounter]);
							iCounter++;
						}
					}
								
					writer.WriteEndElement(); // end comparestrings tag
					writer.WriteEndElement(); // end difference tag
				}
			} //end if diff.Selected == true
				writer.WriteEndElement(); // end differences tag
				writer.WriteEndElement(); // end root tag
			
			writer.Close();
			
		}
		// This function will write the comparestring and close
		// the XML tag -- needed by GenerateXml()
		static void CapElement(XmlTextWriter writer, string left, string right) 
		{
			writer.WriteEndElement(); // end action
			writer.WriteStartElement("leftstring");
			writer.WriteString(left);
			writer.WriteEndElement();
			writer.WriteStartElement("rightstring");
			writer.WriteString(right);
			writer.WriteEndElement(); // end rightstring
			writer.WriteEndElement(); // end comparestring
		}

DarkoJ.

this was fast reply...
I will drop a view on the code you provided but as you said using of regions is what solves the problem...I found this among the posts

for(int i = 0; i <= obDatabaseDifferences.Count-1; i++)
{
Difference diff = (Difference)obDatabaseDifferences;
Work w = new Work();
Regions regions1 = w.ScriptObject(diff.ObjectIn1, enOptions);
Regions regions2 = w.ScriptObject(diff.ObjectIn2, enOptions);
}

and this is doing the job I want, I think :?
thanks..

fordc03

NP That code was part of what i use to generate an HTML report for users when they compare the baseline to a product DB.

fordc03

Which, credit goes to Red-Gate because that's part of the code they sent me when I asked for how I should generate a report of differences.