//private void helper_DatabaseScriptEvent(object sender, DatabaseScriptEventArgs e)
//{
// if(e.IsNew)
// {
// lstStatus.Items.Insert(0,new ListViewItem(new string[]{e.SourceFile,e.Status,e.FullPath}));
// }
// else
// {
// for(int i=0;i<15;i++)
// {
// if(lstStatus.Items[i].SubItems[0].Text == e.SourceFile)
// {
// lstStatus.Items[i].SubItems[1].Text = e.Status;
// if(e.Status == "Object not in Db")
// {
// lstStatus.Items[i].BackColor = Color.Orange;
// }
// break;
// }
// }
// }
//}
private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
if (e.Argument is ObjectScriptingConfigData)
{
ObjectScriptHelper helper = new ObjectScriptHelper(((ObjectScriptingConfigData)e.Argument).ConnData);
helper.ProcessFullScripting(e.Argument as ObjectScriptingConfigData, sender as BackgroundWorker, e);
}
}
public ObjectComparison(ObjectSyncData[] arrData, ref ObjectScriptHelper helper)
{
this.arrData = arrData;
this.helper = helper;
//
// Required for Windows Form Designer support
//
InitializeComponent();
}
private void mnuViewObjectScript_Click(object sender, System.EventArgs e)
{
if (lstResults.SelectedItems.Count == 0)
{
return;
}
ListViewItem item = lstResults.SelectedItems[0];
SqlSync.ObjectScript.ObjectScriptHelper helper = new ObjectScriptHelper(this.connData);
string script = string.Empty;
string desc = string.Empty;
string message;
string fullObjName = item.SubItems[0].Text;
string name = fullObjName;
string schema;
InfoHelper.ExtractNameAndSchema(name, out name, out schema);
switch (item.SubItems[1].Text.Trim())
{
case "V":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.View, name, schema, ref script, ref desc, out message);
break;
case "P":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.StoredProcedure, name, schema, ref script, ref desc, out message);
break;
case "FN":
helper.ScriptDatabaseObject(SqlSync.Constants.DbObjectType.UserDefinedFunction, name, schema, ref script, ref desc, out message);
break;
default:
message = string.Empty;
break;
}
if (script.Length > 0 || message.Length > 0)
{
ScriptDisplayForm frmDisplay;
if (script.Length > 0)
{
frmDisplay = new ScriptDisplayForm(script, this.connData.SQLServerName, item.SubItems[0].Text);
}
else
{
frmDisplay = new ScriptDisplayForm(message, this.connData.SQLServerName, item.SubItems[0].Text);
}
frmDisplay.ShowDialog();
}
}
public void GetDatabaseObjectHashesTest()
{
Initialization init = new Initialization();
ConnectionData data = init.connData;
ObjectScriptHelper target = new ObjectScriptHelper(data);
ObjectScriptHashData actual;
actual = target.GetDatabaseObjectHashes();
Assert.IsNotNull(actual);
Assert.AreEqual("C9D84C93D15E8D9ADF4F78BF8B97C051", actual.Tables["dbo.TransactionTest"].HashValue);
Assert.AreEqual("Added", actual.Tables["dbo.TransactionTest"].ComparisonValue);
}
public void GetDatabaseObjectHashesTest_BadDatabase()
{
Initialization init = new Initialization();
ConnectionData data = init.connData;
data.DatabaseName = "BADNAME";
ObjectScriptHelper target = new ObjectScriptHelper(data);
ObjectScriptHashData actual;
actual = target.GetDatabaseObjectHashes();
Assert.IsNull(actual);
}
private void bgScripting_DoWork(object sender, DoWorkEventArgs e)
{
ObjectScriptHelper scrHelper;
if (e.Argument == null)
{
scrHelper = new ObjectScriptHelper(this.connData);
scrHelper.ProcessScripts(sender as BackgroundWorker, e);
}
else
{
scrHelper = new ObjectScriptHelper(((ObjectScriptingConfigData)e.Argument).ConnData);
scrHelper.ProcessFullScripting(e.Argument as ObjectScriptingConfigData, sender as BackgroundWorker, e);
}
}
public void CollateScriptWithSchemaCheckTest_NeedsSchema()
{
ObjectScriptHelper target = new ObjectScriptHelper(new ConnectionData());
StringCollection coll = new StringCollection();
coll.Add(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[MyObject]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MyObject](
[MyObjectID] [int] IDENTITY(1,1) NOT NULL
" );
string schema = "dbo";
StringBuilder sb = new StringBuilder();
target.CollateScriptWithSchemaCheck(coll, schema, ref sb);
Assert.IsTrue(sb.ToString().IndexOf("OBJECT_ID(N'[dbo].[MyObject]')") > -1);
}
public void CollateScriptWithSchemaCheckTest_MultipleGoodWithSchema()
{
ObjectScriptHelper target = new ObjectScriptHelper(new ConnectionData());
StringCollection coll = new StringCollection();
coll.Add(@"IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MyObject_Fk1]') AND parent_object_id = OBJECT_ID(N'[dbo].[MyObject]'))
ALTER TABLE [dbo].[MyObject] WITH CHECK ADD CONSTRAINT [FK_MyObject_Fk1] FOREIGN KEY([MyObjectType])
REFERENCES [MyObjectType] ([MyObjectTypeID])");
string expected = String.Format(@"{0}
GO
", coll[0]);
string schema = "dbo";
StringBuilder sb = new StringBuilder();
target.CollateScriptWithSchemaCheck(coll, schema, ref sb);
Assert.AreEqual(expected, sb.ToString());
}
public void CollateScriptWithSchemaCheckTest_HasObjectIdButNothingToDo()
{
ObjectScriptHelper target = new ObjectScriptHelper(new ConnectionData());
StringCollection coll = new StringCollection();
coll.Add(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyObject]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MyObject](
[MyObjectID] [int] IDENTITY(1,1) NOT NULL
" );
string expected = String.Format(@"{0}
GO
", coll[0]);
string schema = "dbo";
StringBuilder sb = new StringBuilder();
target.CollateScriptWithSchemaCheck(coll, schema, ref sb);
Assert.AreEqual(expected, sb.ToString());
}
public void CollateScriptWithSchemaCheckTest_NothingToDo()
{
ObjectScriptHelper target = new ObjectScriptHelper(new ConnectionData());
StringCollection coll = new StringCollection();
coll.Add("This does not start with IF NOT EXISTS");
coll.Add("Neither does this");
string expected = String.Format(@"{0}
GO
{1}
GO
", coll[0], coll[1]);
string schema = "dbo";
StringBuilder sb = new StringBuilder();
target.CollateScriptWithSchemaCheck(coll, schema, ref sb);
Assert.AreEqual(expected, sb.ToString());
}
public void CollectHashes()
{
if (this.HashCollectionRunnerUpdate != null)
{
HashCollectionRunnerUpdate(this, new HashCollectionRunnerUpdateEventArgs(this.serverName, this.databaseName, "Starting"));
}
ConnectionData connData = new ConnectionData(serverName, databaseName);
ObjectScriptHelper helper = new ObjectScriptHelper(connData);
helper.HashScriptingEvent += new ObjectScriptHelper.HashScriptingEventHandler(helper_HashScriptingEvent);
this.hashData = helper.GetDatabaseObjectHashes();
if (hashData != null)
{
this.hashData.IsBaseLine = isBaseLine;
}
else
{
hashData = new ObjectScriptHashData();
}
this.hashData.Database = this.databaseName;
this.hashData.Server = this.serverName;
}
public static bool CreateBackoutPackage(ConnectionData connData,
List <SqlBuild.Objects.ObjectUpdates> objectUpdates,
List <SqlBuild.Objects.ObjectUpdates> dontUpdate,
List <string> manualScriptsCanNotUpdate, string sourceBuildZipFileName,
string destinationBuildZipFileName, string sourceServer, string sourceDb,
bool removeNewObjectsFromPackage, bool markManualScriptsAsRunOnce,
bool dropNewRoutines, ref BackgroundWorker bg)
{
//Copy the source straight over...
bool reportProgress = false;
if (bg != null && bg.WorkerReportsProgress)
{
reportProgress = true;
bg.ReportProgress(-1, "Copying package to destination...");
}
if (!CopyOriginalToBackout(sourceBuildZipFileName, destinationBuildZipFileName))
{
return(false);
}
//init working location for destination backout package
string workingDir = string.Empty;
string projectPath = string.Empty;
string projectFileName = string.Empty;
SqlBuildFileHelper.InitilizeWorkingDirectory(ref workingDir, ref projectPath, ref projectFileName);
string message = $"Initialized working directory {workingDir}";
log.LogDebug(message);
if (reportProgress)
{
bg.ReportProgress(-1, "Initialized working directory");
}
//Extract destination package into working folder
string result;
bool success = SqlBuildFileHelper.ExtractSqlBuildZipFile(destinationBuildZipFileName, ref workingDir,
ref projectPath, ref projectFileName, out result);
if (success)
{
log.LogDebug($"Successfully extracted build file {destinationBuildZipFileName} to {workingDir}");
}
else
{
log.LogError("Unable to proceed with Backout package. See previous errors");
return(false);
}
//Load the build data
SqlSyncBuildData buildData;
if (reportProgress)
{
bg.ReportProgress(-1, "Loading project file for modification.");
}
bool successfulLoad = SqlBuildFileHelper.LoadSqlBuildProjectFile(out buildData, projectFileName, false);
if (!successfulLoad)
{
log.LogError("Unable to load SBM project data");
return(false);
}
ConnectionData tmpData = new ConnectionData();
tmpData.DatabaseName = sourceDb;
tmpData.SQLServerName = sourceServer;
tmpData.Password = connData.Password;
tmpData.UserId = connData.UserId;
tmpData.AuthenticationType = connData.AuthenticationType;
ObjectScriptHelper helper = new ObjectScriptHelper(tmpData);
//Get the updated scripts...
if (reportProgress)
{
bg.ReportProgress(-1, "Generating updated scripts.");
}
List <UpdatedObject> lstScripts = ObjectScriptHelper.ScriptDatabaseObjects(objectUpdates, tmpData, ref bg);
//Log if some scripts were not updated properly...
var notUpdated = from s in objectUpdates
where !(from u in lstScripts select s.ShortFileName).Contains(s.ShortFileName)
select s.ShortFileName;
if (notUpdated.Any())
{
foreach (string file in notUpdated)
{
log.LogError($"Unable to create new script for {file}");
}
return(false);
}
if (lstScripts.Count() != objectUpdates.Count())
{
log.LogError($"Not all scripts were updated. Expected {lstScripts.Count().ToString()}, only {objectUpdates.Count().ToString()} were updated");
return(false);
}
//Save the updated scripts...
DateTime updateTime = DateTime.Now;
bool errorWriting = false;
if (lstScripts != null)
{
foreach (UpdatedObject obj in lstScripts)
{
try
{
File.WriteAllText(projectPath + obj.ScriptName, obj.ScriptContents);
//Update the buildData object with the update date/time and user;
var sr = from r in buildData.Script
where r.FileName == obj.ScriptName
select r;
if (sr.Any())
{
SqlSyncBuildData.ScriptRow row = sr.First();
row.DateModified = updateTime;
row.ModifiedBy = System.Environment.UserName;
}
}
catch (Exception exe)
{
errorWriting = true;
log.LogError(exe, $"Unable to save updated script file to {obj.ScriptName}");
}
}
}
//Update new object scripts: either remove or mark as run once
if (dontUpdate != null)
{
foreach (SqlBuild.Objects.ObjectUpdates obj in dontUpdate)
{
try
{
//Update the buildData object with the update date/time and user;
var sr = from r in buildData.Script
where r.FileName == obj.ShortFileName
select r;
if (sr.Any())
{
SqlSyncBuildData.ScriptRow row = sr.First();
if (obj.ObjectType == DbScriptDescription.StoredProcedure ||
obj.ObjectType == DbScriptDescription.UserDefinedFunction ||
obj.ObjectType == DbScriptDescription.Trigger ||
obj.ObjectType == DbScriptDescription.View)
{
if (dropNewRoutines)
{
string schema, objName;
string[] arr = obj.SourceObject.Split(new char[] { '.' });
schema = arr[0];
objName = arr[1];
string str = CreateRoutineDropScript(schema, objName, obj.ObjectType);
File.WriteAllText(projectPath + "DROP " + row.FileName, str);
row.FileName = "DROP " + row.FileName;
row.DateModified = updateTime;
row.ModifiedBy = System.Environment.UserName;
}
else
{
row.AllowMultipleRuns = false;
row.DateModified = updateTime;
row.ModifiedBy = System.Environment.UserName;
}
}
else if (removeNewObjectsFromPackage)
{
buildData.Script.RemoveScriptRow(row);
}
else
{
row.AllowMultipleRuns = false;
row.DateModified = updateTime;
row.ModifiedBy = System.Environment.UserName;
}
}
}
catch (Exception exe)
{
errorWriting = true;
if (removeNewObjectsFromPackage)
{
log.LogError(exe, $"Unable to remove new object script '{obj.ShortFileName}' from package");
}
else
{
log.LogError(exe, $"Unable to mark new object script {obj.ShortFileName} as run once");
}
}
}
}
//Mark non-updated scripts as run-once
if (manualScriptsCanNotUpdate != null)
{
foreach (string scr in manualScriptsCanNotUpdate)
{
try
{
//Update the buildData object with the update date/time and user;
var sr = from r in buildData.Script
where r.FileName == scr
select r;
if (sr.Any())
{
if (markManualScriptsAsRunOnce)
{
SqlSyncBuildData.ScriptRow row = sr.First();
if (row.BuildOrder < 1000)
{
row.AllowMultipleRuns = false;
row.DateModified = updateTime;
row.ModifiedBy = System.Environment.UserName;
}
}
}
}
catch (Exception exe)
{
errorWriting = true;
log.LogError(exe, $"Unable to mark script {scr} as run once");
}
}
}
if (errorWriting)
{
return(false);
}
if (reportProgress)
{
bg.ReportProgress(-1, "Saving backout package.");
}
buildData.AcceptChanges();
SqlBuildFileHelper.SaveSqlBuildProjectFile(ref buildData, projectFileName, destinationBuildZipFileName);
return(true);
}
private void InitilizeHelperClass()
{
helper = new ObjectScriptHelper(this.connData);
//helper.DatabaseScriptEvent += new DatabaseScriptEventHandler(UpdateScriptStatus);
//helper.StatusEvent += new StatusEventHandler(helper_StatusEvent);
}
