//public static string Stuff(this string input, int start, int length, string replaceWith)
//{
// return input.Remove(start, length).Insert(start, replaceWith);
//}
public List <string> GetInstanceDatabases(string SQLSrvrInstance)
{
List <string> Databases = new List <string>();
ServerConnection SQLSrvrConnection = new ServerConnection(SQLSrvrInstance);
System.Collections.Specialized.StringCollection script = new StringCollection();
// Recreate connection if necessary
if (SQLSrvrInstance == null)
{
SQLSrvrConnection = new ServerConnection();
}
// Fill in necessary information
SQLSrvrConnection.ServerInstance = SQLSrvrInstance;
// Setup capture and execute to be able to display script
SQLSrvrConnection.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Use Windows authentication
SQLSrvrConnection.LoginSecure = true;
// Go ahead and connect
SQLSrvrConnection.Connect();
Server srv = new Server(SQLSrvrConnection);
foreach (Database idb in srv.Databases)
{
if (!idb.IsSystemObject)
{
Databases.Add(idb.Name);
}
}
return(Databases);
}
public void ScriptForeignKeys(List <TableAndFKey> TblFkey, string SQLSrvrInstance)
{
ServerConnection SQLSrvrConnection = new ServerConnection(SQLSrvrInstance);
System.Collections.Specialized.StringCollection script = new StringCollection();
// Recreate connection if necessary
if (SQLSrvrInstance == null)
{
SQLSrvrConnection = new ServerConnection();
}
// Fill in necessary information
SQLSrvrConnection.ServerInstance = SQLSrvrInstance;
// Setup capture and execute to be able to display script
SQLSrvrConnection.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Use Windows authentication
SQLSrvrConnection.LoginSecure = true;
// Go ahead and connect
SQLSrvrConnection.Connect();
Server srv = new Server(SQLSrvrConnection);
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = true;
/*
* //oScriptingOptions.AppendToFile = true;
* //oScriptingOptions.DriForeignKeys = true;
* //oScriptingOptions.SchemaQualify = true;
* //oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
* //oScriptingOptions.ScriptSchema = true;
*/
Londeck.Net.Logging.LogFile _LogFile = new Londeck.Net.Logging.LogFile(@"C:\UnTrustedFKeys\June-UnTrustedFKeys.sql", true, 0);
foreach (TableAndFKey _FKeyTbl in TblFkey)
{
// ***** Make sure that the Database is correct in srv.Databases[""]
ForeignKey fk = srv.Databases[_FKeyTbl.DBName].Tables[_FKeyTbl.TableName, "dbo"].ForeignKeys[_FKeyTbl.FKeyName];
script = fk.Script(oScriptingOptions);
foreach (String str in script)
{
_LogFile.WriteLogMessage(str);
}
_LogFile.WriteBlankLines(1);
}
_LogFile.CloseLogFile(true);
}
public string ScriptMissingForeignKeys(List <TableAndFKey> TblFkey, string NodeMissingKeys, bool NRTs)
{
string SQLSrvrInstance;
if (NRTs)
{
SQLSrvrInstance = @"AH-N1-NRT1-VM\AH_N1_NRT1";
}
else
{
SQLSrvrInstance = @"AHN1PRIMARY";
}
ServerConnection SQLSrvrConnection = new ServerConnection(SQLSrvrInstance);
System.Collections.Specialized.StringCollection script = new StringCollection();
// Recreate connection if necessary
if (SQLSrvrInstance == string.Empty)
{
SQLSrvrConnection = new ServerConnection();
}
// Fill in necessary information
SQLSrvrConnection.ServerInstance = SQLSrvrInstance;
// Setup capture and execute to be able to display script
SQLSrvrConnection.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Use Windows authentication
SQLSrvrConnection.LoginSecure = true;
// Go ahead and connect
SQLSrvrConnection.Connect();
Server srv = new Server(SQLSrvrConnection);
ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = true;
//oScriptingOptions.DriForeignKeys = true;
//if (Replicated)
//{
// oScriptingOptions.ScriptDrops = true;
// oScriptingOptions.IncludeIfNotExists = false;
//}
//oScriptingOptions.SchemaQualify = true;
//oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
//oScriptingOptions.ScriptSchema = true;
string ScriptedFK = string.Empty;
string MissingFKeysFileName = ConstructMissingFKsLogFileName(NodeMissingKeys);
int i = 0;
if (System.IO.File.Exists(@"C:\MissingFKeys\" + MissingFKeysFileName))
{
System.IO.File.Delete(@"C:\MissingFKeys\" + MissingFKeysFileName);
}
Londeck.Net.Logging.LogFile _LogFile = new Londeck.Net.Logging.LogFile(@"C:\MissingFKeys\" + MissingFKeysFileName, true, 0);
try
{
foreach (TableAndFKey _TableFKey in TblFkey)
{
i++;
// ***** Make sure that the Database is correct in srv.Databases[""]
ForeignKey fk = srv.Databases[_TableFKey.DBName].Tables[_TableFKey.TableName, "dbo"].ForeignKeys[_TableFKey.FKeyName];
if (Replicated)
{
ScriptedFK = ScriptDropFK(fk); // +Environment.NewLine;
}
if (ScriptedFK.Length > 0)
{
ScriptedFK += Environment.NewLine;
ScriptedFK += ScriptCreateFK(fk) + Environment.NewLine;
}
//foreach (ForeignKey _fk in fk)
//{
//script = fk.Script(oScriptingOptions);
////}
//foreach (string str in script)
//{
// if (str.Contains("USE ["))
// {
// ScriptedFK += str + Environment.NewLine;
// }
// else
// {
// ScriptedFK += str;
// }
//}
// Fix the script to not include the IF EXISTS logic.
// Just assume that the key may just not exist.
//int idx = ScriptedFK.IndexOf("IF EXISTS (");
//ScriptedFK = ScriptedFK.Substring(0, idx);
//ScriptedFK += Environment.NewLine + "GO"; // + Environment.NewLine;
if (ScriptedFK.Length > 0)
{
_LogFile.WriteLogMessage(ScriptedFK);
_LogFile.WriteBlankLines(1);
}
ScriptedFK = string.Empty;
}
_LogFile.CloseLogFile(true);
}
catch (Exception ex)
{
_LogFile.CloseLogFile(true);
throw ex;
}
return(@"C:\MissingFKeys\" + MissingFKeysFileName);
}
public string ScriptUnTrustedKeys(List <TableAndFKey> TblFkey, string SQLSrvrInstance)
{
ServerConnection SQLSrvrConnection = new ServerConnection(SQLSrvrInstance);
System.Collections.Specialized.StringCollection script = new StringCollection();
// Recreate connection if necessary
if (SQLSrvrInstance == null)
{
SQLSrvrConnection = new ServerConnection();
}
if (string.IsNullOrEmpty(this.FileName))
{
System.Windows.Forms.MessageBox.Show("No file name give for the output script.", "Script Foreign Keys.", System.Windows.Forms.MessageBoxButtons.OK);
}
// Fill in necessary information
SQLSrvrConnection.ServerInstance = SQLSrvrInstance;
// Setup capture and execute to be able to display script
SQLSrvrConnection.SqlExecutionModes = SqlExecutionModes.ExecuteAndCaptureSql;
// Use Windows authentication
SQLSrvrConnection.LoginSecure = true;
// Go ahead and connect
SQLSrvrConnection.Connect();
Server srv = new Server(SQLSrvrConnection);
ScriptingOptions oScriptingOptions;
string ScriptedFK = string.Empty;
SMOWrapper smoWrapper = new SMOWrapper();
if (System.IO.File.Exists(FileName))
{
System.IO.File.Delete(FileName);
}
Londeck.Net.Logging.LogFile _LogFile = new Londeck.Net.Logging.LogFile(FileName, true, 0);
try
{
foreach (TableAndFKey _TableFKey in TblFkey)
{
// ***** Make sure that the Database is correct in srv.Databases[""]
// Set up the Foreign Key Drop script
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.IncludeIfNotExists = true;
oScriptingOptions.ScriptDrops = true;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.DriForeignKeys = true;
oScriptingOptions.SchemaQualify = true;
oScriptingOptions.SchemaQualifyForeignKeysReferences = true;
oScriptingOptions.ScriptSchema = true;
ForeignKey fk = srv.Databases[_TableFKey.DBName].Tables[_TableFKey.TableName, "dbo"].ForeignKeys[_TableFKey.FKeyName];
//script = fk.Script(oScriptingOptions);
ScriptedFK = smoWrapper.ScriptObject <ForeignKey, ScriptingOptions, int>(fk, oScriptingOptions, 0);
ScriptedFK += Environment.NewLine;
oScriptingOptions = null;
// Set up the Create Foreign Key script
oScriptingOptions = new ScriptingOptions();
oScriptingOptions.IncludeDatabaseContext = true;
ScriptedFK += smoWrapper.ScriptObject <ForeignKey, ScriptingOptions, int>(fk, oScriptingOptions, 2);
_LogFile.WriteLogMessage(ScriptedFK);
_LogFile.WriteBlankLines(1);
ScriptedFK = string.Empty;
}
_LogFile.CloseLogFile(true);
}
catch (NullReferenceException ex)
{
_LogFile.CloseLogFile(true);
throw ex;
}
catch (Exception ex)
{
_LogFile.CloseLogFile(true);
throw ex;
}
return(FileName);
}
