public string ScriptDatabase()
{
var sb = new StringBuilder();
var server = new Server(@"DICKSON");
var databse = server.Databases["NYSCFileRecord"];
var scripter = new Scripter(server);
scripter.Options.ScriptDrops = false;
scripter.Options.WithDependencies = true;
scripter.Options.IncludeHeaders = true;
//And so on ....
var smoObjects = new Urn[1];
foreach (Microsoft.SqlServer.Management.Smo.Table t in databse.Tables)
{
smoObjects[0] = t.Urn;
if (t.IsSystemObject == false)
{
StringCollection sc = scripter.Script(smoObjects);
foreach (var st in sc)
{
sb.Append(st);
}
}
}
return(sb.ToString());
}
public async Task <Guid> Urn(Guid questionId, string name, byte[][] authorities, KeysPair keys)
{
var exist = urns.SingleOrDefault(o => o.QuestionId == questionId && o.Name == name);
if (exist != null)
{
return(exist.Id);
}
var urn = new Urn
{
Id = Guid.NewGuid(),
QuestionId = questionId,
Name = name,
Authorities = authorities
};
var signer = new Signer(service);
urn.Signature = signer.Sign(urn.GetData(), keys);
urn.PublicKey = new byte[keys.PublicKey.Length];
Buffer.BlockCopy(keys.PublicKey, 0, urn.PublicKey, 0, keys.PublicKey.Length);
await publisher.Add(urn);
urns.Add(urn);
return(urn.Id);
}
public IEnumerable <AggregateCommit> Apply(AggregateCommit current)
{
if (ShouldApply(current))
{
var urnRaw = Urn.Parse(Encoding.UTF8.GetString(current.AggregateRootId));
var urn = AggregateUrn.Parse(urnRaw.Value);
var fooId = new FooId(urn.Id, urn.Tenant);
LoadFromEventStore(fooId);
aggregateMaxRevision[fooId]++;
var newFooEvents = new List <IEvent>();
foreach (IEvent @event in current.Events)
{
if (@event.GetType() == typeof(TestCreateEventBar))
{
newFooEvents.Add(new TestCreateEventFoo(fooId));
}
else if (@event.GetType() == typeof(TestUpdateEventBar))
{
var theEvent = @event as TestUpdateEventBar;
newFooEvents.Add(new TestUpdateEventFoo(fooId, theEvent.UpdatedFieldValue));
}
}
var aggregateCommitFooBar = new AggregateCommit(fooId.RawId, aggregateMaxRevision[fooId], newFooEvents);
yield return(aggregateCommitFooBar);
}
else
{
yield return(current);
}
}
/// <summary>
/// setup internal members based upon a JobSchedule object.
/// </summary>
/// <param name="source"></param>
private void LoadData(JobSchedule source)
{
currentName = originalName = source.Name;
this.urn = source.Urn;
this.alreadyCreated = true;
this.enabled = source.IsEnabled;
this.frequencyType = source.FrequencyTypes;
this.startDate = source.ActiveStartDate;
this.startTime = source.ActiveStartTimeOfDay;
this.endDate = source.ActiveEndDate;
this.endTime = source.ActiveEndTimeOfDay;
this.frequencyInterval = source.FrequencyInterval;
this.frequencyRecurranceFactor = source.FrequencyRecurrenceFactor;
this.frequencyRelativeInterval = source.FrequencyRelativeIntervals;
this.frequencySubDayInterval = source.FrequencySubDayInterval;
this.frequencySubDayTypes = source.FrequencySubDayTypes;
// If this JobSchedule object hasn't been Created yet,
// then accessing the ID will fail.
try
{
this.id = source.ID;
}
catch (Microsoft.SqlServer.Management.Smo.PropertyNotSetException)
{
this.alreadyCreated = false;
}
}
private void objectBrowserTreeView_AfterExpand(object sender, TreeViewEventArgs e)
{
// Remove dummy node
e.Node.Nodes.RemoveAt(0);
SqlSmoObject smoObj = null;
Urn urnNode = (Urn)e.Node.Tag;
try
{
smoObj = server.GetSmoObject(urnNode);
AddCollections(e, smoObj);
}
catch (UnsupportedVersionException)
{
// Right now don't do anything... but you might change the
// Icon so it will emphasize the version issue
}
catch (SmoException)
{
try
{
AddItemInCollection(e, server);
}
catch (Exception except)
{
ExceptionMessageBox emb = new ExceptionMessageBox(except);
emb.Show(this);
}
}
catch (ApplicationException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
}
public Error(Urn id, HttpStatusCode statusCode = HttpStatusCode.InternalServerError, string message = null)
{
Id = id;
StatusCode = statusCode;
Message = message;
Properties = new Dictionary <string, string>();
}
public bool Should_indicate_that_urn_is_child(string parentValue, string childValue)
{
var parent = Urn.Parse(parentValue);
var child = Urn.Parse(childValue);
return(child.IsChildOf(parent));
}
public void Should_initialize_with_full_urn_string()
{
var urn = Urn.Parse("urn:ns:val");
urn.Nid.Should().Be("ns");
urn.Nss.Should().Be("val");
}
public void Should_serialize_URN_to_the_string_representation(string value)
{
var urn = Urn.Parse(value);
var json = serializer.SerializeToIndentedString(urn);
json.Should().Be(value.ToQuoted());
}
public void Should_initialize_urn_full_urn()
{
var urn = new Urn("ns", "val");
urn.Nid.Should().Be("ns");
urn.Nss.Should().Be("val");
}
public override int GetHashCode()
{
unchecked
{
int result = Id.GetHashCode();
result = (result * 397) ^ (Urn != null ? Urn.GetHashCode() : 0);
result = (result * 397) ^ UserId.GetHashCode();
result = (result * 397) ^ DateAdded.GetHashCode();
result = (result * 397) ^ DateModified.GetHashCode();
result = (result * 397) ^ (TargetUserId.HasValue ? TargetUserId.Value.GetHashCode() : 0);
result = (result * 397) ^ (ForwardedPostId.HasValue ? ForwardedPostId.Value.GetHashCode() : 0);
result = (result * 397) ^ OriginUserId.GetHashCode();
result = (result * 397) ^ (OriginUserName != null ? OriginUserName.GetHashCode() : 0);
result = (result * 397) ^ SourceUserId.GetHashCode();
result = (result * 397) ^ (SourceUserName != null ? SourceUserName.GetHashCode() : 0);
result = (result * 397) ^ (SubjectUrn != null ? SubjectUrn.GetHashCode() : 0);
result = (result * 397) ^ (ContentUrn != null ? ContentUrn.GetHashCode() : 0);
result = (result * 397) ^ (TrackUrns != null ? TrackUrns.GetHashCode() : 0);
result = (result * 397) ^ (Caption != null ? Caption.GetHashCode() : 0);
result = (result * 397) ^ CaptionUserId.GetHashCode();
result = (result * 397) ^ (CaptionSourceName != null ? CaptionSourceName.GetHashCode() : 0);
result = (result * 397) ^ (ForwardedPostUrn != null ? ForwardedPostUrn.GetHashCode() : 0);
result = (result * 397) ^ PostType.GetHashCode();
result = (result * 397) ^ (OnBehalfOfUserId.HasValue ? OnBehalfOfUserId.Value.GetHashCode() : 0);
return(result);
}
}
public SqlObjectBrowser(ServerConnection serverConn)
{
InitializeComponent();
// Connect to SQL Server
server = new Server(serverConn);
try
{
server.ConnectionContext.Connect();
// In case connection succeeded we add the sql server node as root in object explorer (treeview)
TreeNode tn = new TreeNode();
tn.Text = server.Name;
tn.Tag = server.Urn;
this.objUrn = server.Urn;
objectBrowserTreeView.Nodes.Add(tn);
AddDummyNode(tn);
connected = true;
}
catch (ConnectionException)
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.ConnectionFailed;
emb.Show(this);
}
catch (ApplicationException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
}
// copy constructor
public JobStepData(JobStepData source)
{
this.originalName = source.originalName;
this.currentName = source.currentName;
this.alreadyCreated = source.alreadyCreated;
this.deleted = source.deleted;
this.command = source.command;
this.commandExecutionSuccessCode = source.commandExecutionSuccessCode;
this.databaseName = source.databaseName;
this.databaseUserName = source.databaseUserName;
this.server = source.server;
this.id = source.id;
this.originalId = source.originalId;
this.failureAction = source.failureAction;
this.failStep = source.failStep;
this.failStepId = source.failStepId;
this.successAction = source.successAction;
this.successStep = source.successStep;
this.successStepId = source.successStepId;
this.priority = source.priority;
this.outputFileName = source.outputFileName;
this.appendToLogFile = source.appendToLogFile;
this.appendToStepHist = source.appendToStepHist;
this.writeLogToTable = source.writeLogToTable;
this.appendLogToTable = source.appendLogToTable;
this.retryAttempts = source.retryAttempts;
this.retryInterval = source.retryInterval;
this.subSystem = source.subSystem;
this.proxyName = source.proxyName;
this.urn = source.urn;
this.parent = source.parent;
}
/// <summary>
/// Set defaults for a new empty job
/// </summary>
private void SetDefaults()
{
this.alreadyCreated = false;
this.currentName = originalName = string.Empty;
this.command = string.Empty;
this.commandExecutionSuccessCode = 0;
this.databaseName = "master";
this.databaseUserName = string.Empty;
this.server = string.Empty;
this.originalId = this.id = -1;
this.failureAction = StepCompletionAction.QuitWithFailure;
this.failStep = null;
this.failStepId = -1;
this.successAction = StepCompletionAction.GoToNextStep;
this.successStep = null;
this.successStepId = -1;
this.priority = OSRunPriority.Normal;
this.outputFileName = string.Empty;
this.appendToLogFile = false;
this.appendToStepHist = false;
this.writeLogToTable = false;
this.appendLogToTable = false;
this.retryAttempts = 0;
this.retryInterval = 0;
this.subSystem = AgentSubSystem.TransactSql;
this.proxyName = string.Empty;
this.urn = null;
}
private string GenerateScriptSelect(Server server, UrnCollection urns)
{
string script = string.Empty;
ScriptingObject scriptingObject = this.Parameters.ScriptingObjects[0];
Urn objectUrn = urns[0];
string typeName = objectUrn.GetNameForType(scriptingObject.Type);
// select from service broker
if (string.Compare(typeName, "ServiceBroker", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValuesFromTransmissionQueue(objectUrn);
}
// select from queues
else if (string.Compare(typeName, "Queues", StringComparison.CurrentCultureIgnoreCase) == 0 ||
string.Compare(typeName, "SystemQueues", StringComparison.CurrentCultureIgnoreCase) == 0)
{
script = Scripter.SelectAllValues(objectUrn);
}
// select from table or view
else
{
Database db = server.Databases[databaseName];
bool isDw = db.IsSqlDw;
script = new Scripter().SelectFromTableOrView(server, objectUrn, isDw);
}
return(script);
}
public void Open(string serverName, string fullName, Urn urn, string text)
{
string key = urn.ToString();
IObjectExplorerService objExplorer = ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService)) as IObjectExplorerService;
var test = ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService)) as IObjectExplorerService;
var node = objExplorer.FindNode(key);
if (node != null)
{
objExplorer.SynchronizeTree(node);
}
Document existingDocument;
if (_openedDocuments.TryGetValue(key, out existingDocument))
{
existingDocument.Activate();
}
else if (text != null)
{
var script = ServiceCache.ScriptFactory.CreateNewBlankScript(ScriptType.Sql) as SqlScriptEditorControl;
script.EditorText = text;
_openedDocuments[key] = _applicationObject.ActiveDocument;
string fullPath = Properties.Settings.Default.ResolveProjectRoot();
if (!fullPath.EndsWith("\\"))
{
fullPath += '\\';
}
fullPath += serverName + '\\' + fullName.Replace('.', '\\').Replace(':', '_') + ".sql";
_applicationObject.ActiveDocument.Save(fullPath);
}
}
public static bool Execute(OpenedFileManager openedFileManager)
{
var accessor = new ObjectAccessor();
var objects = accessor.GetObjects();
var strToUrn = new Dictionary <string, Urn>();
var items = new List <Common.ChooseItem.Item>();
foreach (var obj in objects)
{
strToUrn[obj.fullName] = obj.urn;
var searchTerms = CreateSearchTerms(obj);
items.Add(new Common.ChooseItem.Item(obj.fullName, obj.type, searchTerms));
}
string title = "Choose object on " + accessor.ServerName();
var choose = new Common.ChooseItem(items.ToArray(), title);
if (choose.ShowDialog() != System.Windows.Forms.DialogResult.OK)
{
return(false);
}
string result = choose.Result();
Urn resultUrn = strToUrn[result];
string body = accessor.GetObjectText(resultUrn);
openedFileManager.Open(accessor.ServerName(), result, resultUrn, body);
return(true);
}
public override ApiTaskResult <TResult, TFailureResult> Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
{
using var jsonDocument = JsonDocument.ParseValue(ref reader);
var taskId = jsonDocument.RootElement.GetProperty(propNames.IdPropName.AsUtf8()).GetGuid();
var taskStateValue = jsonDocument.RootElement.GetProperty(propNames.TaskStatePropName.AsUtf8()).GetString();
var taskState = (ApiTaskState)Enum.Parse(typeof(ApiTaskState), taskStateValue !, true);
var taskTypeValue = jsonDocument.RootElement.GetProperty(propNames.TaskTypePropName.AsUtf8()).GetString();
var taskType = taskTypeValue == null ? null : Urn.Parse(taskTypeValue);
switch (taskState)
{
case ApiTaskState.Running:
return(ApiTaskResult <TResult, TFailureResult> .Running(taskId, taskType));
case ApiTaskState.Succeed:
return(DeserializeResult(jsonDocument, taskStateValue, taskId, taskType, options));
case ApiTaskState.Failed:
var errorJson = jsonDocument.RootElement.GetProperty(propNames.ApiErrorPropName.AsUtf8()).GetRawText();
var error = DeserializeAs <ApiError>(errorJson, propNames.ApiErrorPropName, taskStateValue, options);
return(ApiTaskResult <TResult, TFailureResult> .TaskFailure(error, taskId, taskType));
default:
throw Errors.UnexpectedEnumMember(nameof(taskState), taskState);
}
}
public void Update(Package pack)
{
Guard.IsNotNull(pack, nameof(pack));
Model = pack;
// Set URN
string urn = $"urn:{ChocolateyHandler.NAMESPACE_CHOCO}:{Model.Id}";
if (Model.Version != null)
{
urn += ":" + Model.Version.ToString();
}
Urn = Urn.Parse(urn);
// Set base properties
Title = pack.Title;
PackageId = pack.Id;
DeveloperName = pack.AuthorName;
ReleaseDate = pack.Created;
Description = pack.Description;
Version = pack.Version.ToString();
Website = Link.Create(pack.ProjectUrl, "Project website");
// Set Choco package properties
Links = new[]
{
Link.Create(pack.DocsUrl, ShortTitle + " docs"),
Link.Create(pack.BugTrackerUrl, ShortTitle + " bug tracker"),
Link.Create(pack.PackageSourceUrl, ShortTitle + " source"),
Link.Create(pack.MailingListUrl, ShortTitle + " mailing list"),
};
}
public bool Should_indicate_that_urn_is_parent(string parentValue, string childValue)
{
var parent = Urn.Parse(parentValue);
var child = Urn.Parse(childValue);
return(parent.IsParentOf(child));
}
public void TestInvalidNss([ValueSource(nameof(InvalidNss))] string text)
{
Assert.Throws <UriFormatException>(() =>
{
var urn = new Urn(text);
});
}
public void Should_return_error_when_given_an_invalid_string(string value)
{
var success = Urn.TryParse(value, out var urn);
success.Should().BeFalse();
urn.Should().BeNull();
}
public void TestFragment([ValueSource(nameof(Fragment))] string text)
{
Urn urn = null;
Assert.DoesNotThrow(() => { urn = new Urn(text); });
urn.Fragment.Should().NotBeNullOrEmpty();
}
public void Should_initialize_with_namespace_specific_string_only()
{
var urn = Urn.Parse("urn:ns");
urn.Nid.Should().BeEmpty();
urn.Nss.Should().Be("ns");
}
public void TestValid([ValueSource(nameof(Valid))] string text)
{
Assert.DoesNotThrow(() =>
{
var urn = new Urn(text);
});
}
public SqlObjectBrowser(ServerConnection serverConn)
{
InitializeComponent();
// Connect to SQL Server
server = new Server(serverConn);
try
{
server.ConnectionContext.Connect();
// In case connection succeeded we add the sql server node as root in object explorer (treeview)
TreeNode tn = new TreeNode();
tn.Text = server.Name;
tn.Tag = server.Urn;
this.objUrn = server.Urn;
objectBrowserTreeView.Nodes.Add(tn);
AddDummyNode(tn);
connected = true;
}
catch (ConnectionException)
{
ExceptionMessageBox emb = new ExceptionMessageBox();
emb.Text = Properties.Resources.ConnectionFailed;
emb.Show(this);
}
catch (ApplicationException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
}
public ArrayList IsPhysicalPathInLogicalDevice(string physicalPath)
{
Enumerator en = new Enumerator();
DataSet ds = new DataSet();
ds.Locale = System.Globalization.CultureInfo.InvariantCulture;
Request req = new Request();
ArrayList result = null;
int count = 0;
req.Urn = "Server/BackupDevice[@PhysicalLocation='" + Urn.EscapeString(physicalPath) + "']";
ds = en.Process(this.sqlConnection, req);
count = ds.Tables[0].Rows.Count;
if (count > 0)
{
result = new ArrayList(count);
for (int i = 0; i < count; i++)
{
result.Add(Convert.ToString(ds.Tables[0].Rows[0]["Name"], System.Globalization.CultureInfo.InvariantCulture));
}
}
return(result);
}
public CacheEntry?Get(Urn urn)
{
if (!DoesCacheExist())
{
return(null);
}
using FileStream cache = CacheDatabase.Open(FileMode.Open);
using BinaryReader reader = new(cache);
using BinaryWriter writer = new(cache);
// Skip file header
reader.BaseStream.Seek(FILE_MAGIC.Length, SeekOrigin.Begin);
// Loop through items in cache until target URN is found
byte[] targetUrnBytes = Encoding.Default.GetBytes(urn.ToString());
while (reader.BaseStream.Position < reader.BaseStream.Length)
{
// Read entry
if (reader.ReadByte() != byte.MaxValue)
{
continue;
}
CacheEntry entry = CacheEntry.ReadFromStream(reader);
// Check for equality
if (entry.urnBytes.SequenceEqual(targetUrnBytes))
{
return(entry);
}
}
return(null);
}
public ResourceRefs(IEnumerable <ResourceRef> refs)
{
var concepts = new List <ConceptRef>();
var queries = new List <QueryRef>();
foreach (var res in refs)
{
if (!res.UseUniversalId())
{
concepts.Add(new ConceptRef(res.Id.Value.ToString()));
continue;
}
if (!Urn.TryParse(res.UniversalId, out var urn))
{
throw new FormatException($"{res.UniversalId} is not mapped to an embeddable resource");
}
switch (urn)
{
case ConceptUrn concept:
concepts.Add(new ConceptRef {
UniversalId = concept, Id = res.Id
});
break;
case QueryUrn query:
queries.Add(new QueryRef {
UniversalId = query, Id = res.Id
});
break;
}
}
Concepts = concepts;
Queries = queries;
}
private void DependenciesMenuItem_Click(object sender, EventArgs e)
{
Urn[] urns = new Urn[1];
Scripter scripter;
DependencyForm frm;
DependencyTree deps;
// Get the urn from the node
urns[0] = (Urn)(this.DependenciesTreeView.SelectedNode.Tag);
// Instantiate scripter
scripter = new Scripter(server);
// Get a new form
frm = new DependencyForm();
// Discover dependencies
deps = scripter.DiscoverDependencies(urns, DependencyType.Parents);
// Set the tree
frm.ShowDependencies(server, deps);
// Show the form
frm.Show();
}
private void DeleteDependencies(IEnumerable <DependencyCollectionNode> dependencies)
{
Logger.WriteLine("Deleting dependencies:");
var script = new Scripter(_sourceServer)
{
Options = new ScriptingOptions
{
WithDependencies = true,
ScriptData = false,
ScriptDrops = true,
ScriptSchema = true,
IncludeIfNotExists = true
}
};
foreach (DependencyCollectionNode node in dependencies)
{
Urn urn = node.Urn;
Logger.WriteLine(" - Generating scripts from {0}", urn);
StringCollection scripts = script.Script(new[] { urn });
foreach (string scr in scripts)
{
Logger.WriteLine(" - Executing script on {0}: {1}", _targetDatabase.Urn, scr);
_targetDatabase.ExecuteNonQuery(scr);
}
}
}
/// <summary>
/// Event handler for where used menu item.
/// Adds a "USED BY" node and performs a dependency discovery after which
/// all dependents are added to the "USED BY" node.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void WhereUsedToolStripMenuItem_Click(object sender, EventArgs e)
{
TreeNode node, newNode;
node = DependenciesTreeView.SelectedNode;
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) ||
node.Name == Properties.Resources.UsedBy)
{
return;
}
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// Advanced Scripting
Scripter scripter = new Scripter(this.sqlServerSelection);
Urn[] urns = new Urn[1];
urns[0] = (Urn)node.Tag;
// Discover dependents
DependencyTree tree = scripter.DiscoverDependencies(urns, DependencyType.Children);
// Add to tree (recursive)
AddChildren(newNode, tree.FirstChild);
node.Expand();
newNode.Expand();
}
private void WhereUsedMenuItem_Click(object sender, EventArgs e)
{
TreeNode node;
TreeNode newNode = new TreeNode();
Urn[] urns = new Urn[1];
Scripter scripter;
// Get selected node
node = this.DependenciesTreeView.SelectedNode;
// Only do this once
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy) == true
| node.Name == Properties.Resources.UsedBy)
{
return;
}
// Get the urn from the node
urns[0] = (Urn)(node.Tag);
// Add a "where used" node
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// And add the tree to the current node
scripter = new Scripter(server);
AddChildren(newNode, scripter.DiscoverDependencies(urns, DependencyType.Children).FirstChild);
node.Expand();
newNode.Expand();
}
public GuidTenantId(IUrn urn, string aggregateRootName)
{
if (ReferenceEquals(null, urn)) throw new ArgumentNullException(nameof(urn));
var tenantUrn = new Urn(urn);
Tenant = tenantUrn.NID;
AggregateRootName = tenantUrn.Parts[2].ToLowerInvariant();
if (AggregateRootName.Equals(aggregateRootName.ToLowerInvariant()) == false)
{
throw new ArgumentException("Invalid Urn for " + aggregateRootName + " AggregateRootId");
}
Id = Guid.Parse(string.Join(string.Empty, tenantUrn.Parts.Skip(3).ToString()));
RawId = setRawId(Urn);
}
private string ScriptObject(Urn[] urns, Scripter scripter)
{
StringCollection sc = scripter.Script(urns);
var sb = new StringBuilder();
foreach (string str in sc)
{
sb.Append(str + Environment.NewLine + "GO" + Environment.NewLine + Environment.NewLine);
}
return sb.ToString();
}
static void Main(string[] args)
{
Server sqlServer = new Server(args[0]);
Database db = default(Database);
db = sqlServer.Databases[args[1]];
string filePath = args[2];
// set up text file
string filename = filePath + DateTime.Now.Year.ToString() + pad(DateTime.Now.Month.ToString(), 2) + pad(DateTime.Now.Day.ToString(), 2) + pad(DateTime.Now.Hour.ToString(), 2) + pad(DateTime.Now.Minute.ToString(), 2) + "_" + args[1] + ".sql";
Scripter scrp = default(Scripter);
scrp = new Scripter(sqlServer);
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = false;
Urn[] smoObjects = new Urn[2];
// write each table
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
// write each index
foreach (Index ix in tb.Indexes)
{
if (ix.IsSystemObject == false)
{
using (StreamWriter w = File.AppendText(filename))
{
StringCollection indexScript = ix.Script();
foreach (string s in indexScript)
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each trigger
foreach (Trigger trig in tb.Triggers)
{
if (trig.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = trig.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { trig.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
}
// write each view
foreach (View vw in db.Views)
{
if (vw.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = vw.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { vw.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each stored procedure
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = sp.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { sp.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
// write each user defined funtion
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = udf.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { udf.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
}
/*******************************************************************************
* Script XML Schema Collections
*******************************************************************************/
void scriptXmlSchemaCollections(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string xmlPath = _dbPath + @"\XML Schema Collections";
Directory.CreateDirectory(xmlPath);
foreach (XmlSchemaCollection xml in db.XmlSchemaCollections)
{
urn[0] = xml.Urn;
filename = xmlPath + @"\" + scrub(xml.Schema) + "." + scrub(xml.Name) + ".xmlschema.sql";
Console.WriteLine(" XML Schema Collection: " + xml.Schema + "." + xml.Name);
// script the xml schema collection
ScriptIt(urn, scrp, filename);
}
}
private void Setup(Urn urn1, Urn urn2)
{
if (serverName1 == null || serverName2 == null)
{
throw new ApplicationException(
Properties.Resources.ServerPropertiesCannotBeNull);
}
server1 = new Server(serverName1);
server2 = new Server(serverName2);
if (loginName1 == null || loginName1.Length == 0)
{
server1.ConnectionContext.LoginSecure = true;
}
else
{
server1.ConnectionContext.LoginSecure = false;
server1.ConnectionContext.Login = loginName1;
server1.ConnectionContext.Password = password1;
}
if (loginName2 == null || loginName2.Length == 0)
{
server2.ConnectionContext.LoginSecure = true;
}
else
{
server2.ConnectionContext.LoginSecure = false;
server2.ConnectionContext.Login = loginName2;
server2.ConnectionContext.Password = password2;
}
try
{
smoObject1 = server1.GetSmoObject(urn1);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorCreatingFirstObject, ex));
throw new ApplicationException(
Properties.Resources.ErrorCreatingFirstObjectException,
ex);
}
try
{
smoObject2 = server2.GetSmoObject(urn2);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorCreatingSecondObject, ex));
throw new ApplicationException(
Properties.Resources.ErrorCreatingSecondObjectException,
ex);
}
if (smoObject1 == null)
{
throw new ApplicationException(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ObjectNotCreated, urn1));
}
if (smoObject2 == null)
{
throw new ApplicationException(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ObjectNotCreated, urn2));
}
}
public bool Start(Urn urn1, Urn urn2, bool compareContents)
{
if (!compareContents)
return Start(urn1, urn2);
else
return Start(urn1, urn2) && CompareContent(urn1, urn2);
}
/// <summary>
/// Script the permissions on an object and save the script to add the permissions back later
/// </summary>
/// <param name="urn">The object to drop</param>
/// <param name="addObjects">The list of addObjects, or null to not re-add dependencies</param>
private void ScriptPermissions (Urn urn, List<SchemaObject> addObjects)
{
if (addObjects == null)
return;
// generate the script and add it if we've generated anything
_scripter.Options = new ScriptingOptions (ScriptOption.Permissions);
_scripter.Options.IncludeIfNotExists = true;
_scripter.Options.ScriptDrops = false;
string addScript = GenerateScript (urn);
// scripting permissions on a function returns the body
if (addScript.IndexOf("CREATE FUNCTION", StringComparison.OrdinalIgnoreCase) >= 0)
addScript = "";
if (addScript.Length > 0)
addObjects.Add (new SchemaObject (SchemaObjectType.Permission, "Scripted Permissions", addScript));
// smo switches to master, so switch back to our db
_connection.ChangeDatabase (_databaseName);
}
private void selectButton_Click(object sender, EventArgs e)
{
if (objectBrowserTreeView.SelectedNode.Tag != null)
{
this.objUrn = (Urn)objectBrowserTreeView.SelectedNode.Tag;
}
this.Visible = false;
}
void ScriptIt(Urn[] urn, Scripter scrp, string filename)
{
scrp.Options.FileName = filename;
//override previous settings.
scrp.Options.IncludeIfNotExists = true;
//var createPattern = new Regex(@"(N'CREATE)(.*)$", RegexOptions.Multiline | RegexOptions.IgnoreCase);
var createPattern = new Regex(@"CREATE PROCEDURE \[(.*)\]\.\[(.*)\](.*)", RegexOptions.Multiline | RegexOptions.IgnoreCase);
//createPattern = new Regex(@"CREATE PROCEDURE (dbo\..*)(\(.*)");
try
{
scrp.Script(urn);
var reader = new StreamReader(filename);
var createScript = reader.ReadToEnd();
reader.Close();
//Did the initial research on this. For our needs it's not that helpful. To many regular expressions required.
/*
var alterScript = createPattern.Replace(createScript, @"CREATE PROCEDURE [$1].[$2] AS RAISERROR(''Empty Stored Proc'', 16, 1) WITH SETERROR'
END
GO
ALTER PROCEDURE [$1].[$2]
$3");
int index = alterScript.LastIndexOf("'");
alterScript = alterScript.Substring(0, alterScript.LastIndexOf("'")) + "GO";
var writer = new StreamWriter(filename);
writer.Write(alterScript);
writer.Close();
*/
}
catch (Exception e)
{
Console.WriteLine("Scripting for this object FAILED for the following reason:");
Console.WriteLine(e.InnerException);
Console.WriteLine("");
return;
}
objectCount++;
}
/*******************************************************************************
* Script Stored Procedures
*******************************************************************************/
void scriptStoredProcedures(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string procPath = _dbPath + @"\Sprocs";
Directory.CreateDirectory(procPath);
scrp.Options.Permissions = true;
foreach (StoredProcedure proc in db.StoredProcedures)
{
// skip system procedures
if (proc.IsSystemObject)
{
continue;
}
urn[0] = proc.Urn;
filename = procPath + @"\" + scrub(proc.Schema) + "." + scrub(proc.Name) + ".proc.sql";
Console.WriteLine(" Stored Procedure: " + proc.Schema + "." + proc.Name);
// script the procedure with drop statement
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
}
}
/*******************************************************************************
* Script Database settings
*******************************************************************************/
void scriptDatabase(Database db, Scripter scrp, Urn[] urn)
{
string filename;
urn[0] = db.Urn;
filename = _dbPath + @"\" + scrub(db.Name) + ".database.sql";
Console.WriteLine("Database: " + db.Name);
// script the database
ScriptIt(urn, scrp, filename);
}
void ScriptIt(Urn[] urn, Scripter scrp, string filename, bool withDrop)
{
scrp.Options.ScriptDrops = withDrop;
scrp.Options.IncludeIfNotExists = withDrop;
ScriptIt(urn, scrp, filename);
}
void ScriptIt(Urn[] urn, Scripter scrp, string filename)
{
scrp.Options.FileName = filename;
try
{
scrp.Script(urn);
}
catch (Exception e)
{
Console.WriteLine("Scripting for this object FAILED for the following reason:");
Console.WriteLine(e.InnerException);
Console.WriteLine("");
return;
}
objectCount++;
}
static int Main(string[] args)
{
if (args.Length < 2) { ShowUsage(); return 1; }
Server sqlServer = new Server(args[0]);
Database db = default(Database);
db = sqlServer.Databases[args[1]];
string filePath = args[2];
DateTime now = DateTime.Now;
// set up text file
string filename = string.Format("{0:0000}{1:00}{2:00}{3:00}{4:00}_{5}.sql", now.Year, now.Month, now.Day, now.Hour, now.Minute, args[1]);
filename = Path.Combine(filePath, filename);
Scripter scrp = default(Scripter);
scrp = new Scripter(sqlServer);
scrp.Options.ScriptSchema = true;
scrp.Options.WithDependencies = true;
scrp.Options.ScriptData = false;
Urn[] smoObjects = new Urn[2];
int objectCount = 0;
// write each table
foreach (Table tb in db.Tables)
{
if (tb.IsSystemObject == false)
{
Console.WriteLine("Table: {0}", tb.Urn);
smoObjects = new Urn[1];
smoObjects[0] = tb.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { tb.Urn }))
{
w.WriteLine(s);
Console.Write(".");
objectCount++;
}
w.Close();
}
}
Console.WriteLine();
Console.Write("-Indexes: ");
// write each index
foreach (Index ix in tb.Indexes)
{
if (ix.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
using (StreamWriter w = File.AppendText(filename))
{
StringCollection indexScript = ix.Script();
foreach (string s in indexScript)
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("-Triggers: ");
// write each trigger
foreach (Trigger trig in tb.Triggers)
{
if (trig.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = trig.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { trig.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();//finished triggers
//next table
Console.WriteLine();
}
// write each view
Console.Write("Views: ");
foreach (View vw in db.Views)
{
if (vw.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = vw.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { vw.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
Console.Write("Stored Procedures: ");
// write each stored procedure
foreach (StoredProcedure sp in db.StoredProcedures)
{
if (sp.IsSystemObject == false)
{
Console.Write(".");
objectCount++;
smoObjects = new Urn[1];
smoObjects[0] = sp.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { sp.Urn }))
{
w.WriteLine(s);
}
w.Close();
}
}
}
Console.WriteLine();
// write each user defined funtion
Console.Write("UserDefinedFunctions: ");
foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
{
if (udf.IsSystemObject == false)
{
smoObjects = new Urn[1];
smoObjects[0] = udf.Urn;
using (StreamWriter w = File.AppendText(filename))
{
foreach (string s in scrp.EnumScript(new Urn[] { udf.Urn }))
{
w.WriteLine(s);
Console.Write(".");
}
w.Close();
}
objectCount++;
}
}
Console.WriteLine();
ReportProgress(objectCount);
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("File written: {0}", filename);
return 0;
}
/// <summary>
/// Event handler for where used menu item.
/// Adds a "USED BY" node and performs a dependency discovery after which
/// all dependents are added to the "USED BY" node.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void WhereUsedToolStripMenuItem_Click(object sender, EventArgs e)
{
TreeNode node, newNode;
node = DependenciesTreeView.SelectedNode;
if (node.Nodes.ContainsKey(Properties.Resources.UsedBy)
|| node.Name == Properties.Resources.UsedBy)
{
return;
}
newNode = new TreeNode(Properties.Resources.UsedBy);
newNode.Name = Properties.Resources.UsedBy;
node.Nodes.Add(newNode);
// Advanced Scripting
Scripter scripter = new Scripter(this.sqlServerSelection);
Urn[] urns = new Urn[1];
urns[0] = (Urn)node.Tag;
// Discover dependents
DependencyTree tree = scripter.DiscoverDependencies(urns, DependencyType.Children);
// Add to tree (recursive)
AddChildren(newNode, tree.FirstChild);
node.Expand();
newNode.Expand();
}
/// <summary>
/// Generate a script for a database object.
/// </summary>
/// <param name="urns">The object to script</param>
/// <returns>String containg the script</returns>
private string GenerateScript (Urn urn)
{
UrnCollection urns = new UrnCollection ();
urns.Add (urn);
return GenerateScript (urns);
}
/*******************************************************************************
* Script Tables
*******************************************************************************/
void scriptTables(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string tblPath = _dbPath + @"\Up";
Directory.CreateDirectory(tblPath);
foreach (Table tbl in db.Tables)
{
// skip system tables
if (tbl.IsSystemObject)
{
continue;
}
urn[0] = tbl.Urn;
scrp.Options.DriAll = false;
scrp.Options.Indexes = false;
scrp.Options.Triggers = false;
scrp.Options.NoFileGroup = false;
scrp.Options.DriForeignKeys = false;
scrp.Options.NoTablePartitioningSchemes = false;
//Script Tables
filename = tblPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name) + ".table.sql";
Console.WriteLine(" Table: " + tbl.Schema + "." + tbl.Name);
// script the table
ScriptIt(urn, scrp, filename);
// permissions
string command = "EXEC sp_helprotect"
+ " @name = '" + tbl.Name + "'"
+ ", @grantorname = '" + tbl.Schema + "'";
ScriptPermissions(_connection.connectionString(), command, filename);
// Script Table Indexes
string keyPath = tblPath + @"\Keys";
Directory.CreateDirectory(keyPath);
string ndxPath = tblPath + @"..\..\Indexes";
Directory.CreateDirectory(ndxPath);
foreach (Index ndx in tbl.Indexes)
{
Console.WriteLine(" Index: " + ndx.Name);
urn[0] = ndx.Urn;
if (ndx.IndexKeyType.ToString() == "DriUniqueKey")
{
filename = keyPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".ukey.sql";
}
else if (ndx.IndexKeyType.ToString() == "DriPrimaryKey")
{
filename = keyPath + @"\" + scrub(tbl.Schema) + ".1." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".pkey.sql";
}
else
{
filename = ndxPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(ndx.Name) + ".index.sql";
}
// script the index
ScriptIt(urn, scrp, filename);
}
// Script Table Triggers
string trgPath = tblPath + @"\Triggers";
Directory.CreateDirectory(trgPath);
foreach (Trigger trg in tbl.Triggers)
{
Console.WriteLine(" Trigger: " + trg.Name);
urn[0] = trg.Urn;
filename = trgPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(trg.Name) + ".trigger.sql";
// script the trigger
ScriptIt(urn, scrp, filename);
}
// Script Check Constraints
string chkPath = tblPath + @"\Constraints";
Directory.CreateDirectory(chkPath);
scrp.Options.DriChecks = true;
foreach (Check chk in tbl.Checks)
{
Console.WriteLine(" Constraint: " + chk.Name);
urn[0] = chk.Urn;
filename = chkPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(chk.Name) + ".chkconst.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
// Script Default Constraints
string defPath = chkPath;
scrp.Options.DriChecks = false;
foreach (Column col in tbl.Columns)
{
if (col.DefaultConstraint != null)
{
Console.WriteLine(" Constraint: " + col.DefaultConstraint.Name);
urn[0] = col.DefaultConstraint.Urn;
filename = defPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(col.DefaultConstraint.Name) + ".defconst.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
}
// Script Foreign Keys
scrp.Options.DriForeignKeys = true;
scrp.Options.SchemaQualifyForeignKeysReferences = true;
foreach (ForeignKey fk in tbl.ForeignKeys)
{
Console.WriteLine(" Foreign Key: " + fk.Name);
urn[0] = fk.Urn;
filename = keyPath + @"\" + scrub(tbl.Schema) + "." + scrub(tbl.Name)
+ "." + scrub(fk.Name) + ".fkey.sql";
// script the constraint
ScriptIt(urn, scrp, filename);
}
}
}
/// <summary>
/// Drop an object and generate the script to re-add it
/// </summary>
/// <param name="urn">The object to drop</param>
/// <param name="addObjects">The list of addObjects, or null to not re-add dependencies</param>
/// <param name="options">Options for scripting</param>
private void DropAndReAdd (Urn urn, List<SchemaObject> addObjects, TableScriptOptions options)
{
// generate the script to readd
if (addObjects != null)
{
_scripter.Options.ScriptDrops = false;
_scripter.Options.IncludeIfNotExists = true;
string addScript = GenerateScript (urn);
// unnamed primary keys and constraints need to not be auto-added, since they must be built into the table
if ((options & TableScriptOptions.ScriptAnonymousConstraints) != 0)
addScript = _anonymousReferenceRegex.Replace (addScript, "IF 0=1 $0");
else
{
addScript = _anonymousRegex.Replace (addScript, "IF 0=1 $0");
addScript = _anonymousDefaultRegex.Replace (addScript, "IF 0=1 $0");
}
// if the database has autostatistics, then skip all statistics
addScript = _statisticsRegex.Replace (addScript, "");
// create triggers must be the first statement in the batch
int pos = addScript.IndexOf("CREATE TRIGGER", StringComparison.OrdinalIgnoreCase);
if (pos >= 0)
addScript = addScript.Substring (pos);
// remove primary xml indexes if we don't need them
if ((options & TableScriptOptions.PrimaryXmlIndexes) == 0)
addScript = _primaryXmlIndex.Replace (addScript, "IF 0=1 $0");
if (addScript.Length > 0)
{
SchemaObject newObject = new SchemaObject (SchemaObjectType.Script, "Scripted Dependencies", addScript);
if ((options & TableScriptOptions.AddAtEnd) != 0)
addObjects.Add (newObject);
else
addObjects.Insert (0, newObject);
}
}
// script the drop of everything
_scripter.Options.ScriptDrops = true;
_scripter.Options.IncludeIfNotExists = true;
string dropScript = GenerateScript (urn);
// the scripter should not be scripting the table drop, so we have to comment it out
// note that the !PrimaryObject option above works for the create script
SqlSmoObject smo = _scripter.Server.GetSmoObject (urn);
Table table = smo as Table;
if (table != null)
dropScript = _dropTableRegex.Replace (dropScript, "SELECT 1");
// smo switches to master, so switch back to our db
_connection.ChangeDatabase (_databaseName);
if (!String.IsNullOrWhiteSpace(dropScript))
ExecuteNonQuery (dropScript);
ResetScripter ();
}
public void scriptDB(string directory)
{
DateTime began = DateTime.Now;
_dbPath = generateScriptPath(directory);
Console.WriteLine("\n**********************************************");
Console.WriteLine("Scripting [" + _database + "] to " + _dbPath);
Console.WriteLine("**********************************************\n");
Server srvr = _connection.server(_database);
try
{
srvr.Initialize();
srvr.SetDefaultInitFields(true);
}
catch (Exception e)
{
Console.WriteLine("\nERROR: Connection to Server " + _connection.serverName + ", Database " + _database + " failed\n");
Console.WriteLine(e);
return;
}
Database db = srvr.Databases[_database];
if (db == null)
{
Console.WriteLine("\nERROR: Database " + _database + " does not exist\n");
return;
}
// set up SMO scripting objects
Urn[] urn = new Urn[1];
Scripter scrp = new Scripter(srvr);
// set common scipter options
scrp.Options.IncludeHeaders = false; // don't include header info in version-controlled scripts
scrp.Options.AppendToFile = false; // fresh file every time
scrp.Options.AnsiFile = true;
scrp.Options.ContinueScriptingOnError = true;
scrp.Options.PrimaryObject = true;
scrp.Options.SchemaQualify = true;
scrp.Options.ToFileOnly = true;
scrp.Options.ConvertUserDefinedDataTypesToBaseType = true;
// options that make the results more likely to be executable.
scrp.Options.ExtendedProperties = false; // don't want extra guff - just defailts please
scrp.Options.NoCollation = true; // don't include collation info - defaults are ok
scrp.Options.Permissions = false;
// scripting the objects
//scriptDatabase(db, scrp, urn);
scriptUsers(db, scrp, urn);
scriptTables(db, scrp, urn);
scriptViews(db, scrp, urn);
scriptStoredProcedures(db, scrp, urn);
scriptUserDefinedFunctions(db, scrp, urn);
// done!
DateTime ended = DateTime.Now;
Console.WriteLine("\n[" + _database + "] began: " + began.ToLongTimeString() + ", ended: " + ended.ToLongTimeString());
Console.WriteLine("Number of objects scripted: " + objectCount.ToString());
Console.WriteLine("\n[" + _database + "] done....");
}
private void DropTypeDependencies (Urn urn, List<SchemaObject> addObjects)
{
try
{
DependencyTree tree = _scripter.DiscoverDependencies(new Urn[] { urn }, DependencyType.Children);
for (DependencyTreeNode dependent = tree.FirstChild.FirstChild; dependent != null; dependent = dependent.NextSibling)
{
// for each child object, script it and its permissions
_scripter.Options = new ScriptingOptions();
_scripter.Options.DriAll = true;
_scripter.Options.Permissions = true;
DropAndReAdd(dependent.Urn, addObjects, TableScriptOptions.AddAtEnd);
}
}
finally
{
_connection.ChangeDatabase(_databaseName);
}
}
/*******************************************************************************
* Script User Defined Functions
*******************************************************************************/
void scriptUserDefinedFunctions(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string funcPath = _dbPath + @"\Functions";
Directory.CreateDirectory(funcPath);
scrp.Options.ScriptSchema = true;
foreach (UserDefinedFunction func in db.UserDefinedFunctions)
{
// skip system functions
if (func.IsSystemObject)
{
continue;
}
urn[0] = func.Urn;
filename = funcPath + @"\" + scrub(func.Schema) + "." + scrub(func.Name) + ".function.sql";
Console.WriteLine(" User Defined Function: " + func.Schema + "." + func.Name);
// script the function with drop statement
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
}
}
private void cancelCommandButton_Click(object sender, EventArgs e)
{
this.objUrn = null;
this.Visible = false;
}
/*******************************************************************************
* Script Users
*******************************************************************************/
void scriptUsers(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string userPath = _dbPath + @"\runAfterCreateDatabase";
Directory.CreateDirectory(userPath);
scrp.Options.Permissions = true;
foreach (User user in db.Users)
{
// skip system procedures
if (user.IsSystemObject)
{
continue;
}
urn[0] = user.Urn;
filename = userPath + @"\" + scrub(user.Name) + ".user.sql";
Console.WriteLine(" User: " + user.Name);
ScriptIt(urn, scrp, filename, false);
}
}
public bool Start(Urn urn1, Urn urn2)
{
Setup(urn1, urn2);
if (smoObject1 == null)
{
throw new ApplicationException(string.Format(
System.Threading.Thread.CurrentThread.CurrentCulture,
Properties.Resources.ObjectNotCreated, urn1));
}
if (smoObject2 == null)
{
throw new ApplicationException(string.Format(
System.Threading.Thread.CurrentThread.CurrentCulture,
Properties.Resources.ObjectNotCreated, urn2));
}
// See if the object have the same Type;
if (smoObject1.GetType().Name != smoObject2.GetType().Name)
{
throw new ApplicationException(string.Format(
System.Threading.Thread.CurrentThread.CurrentCulture,
Properties.Resources.ObjectsHaveDifferentTypes,
smoObject1.GetType().Name, smoObject2.GetType().Name));
}
// For small databases because of the huge number of SPs it takes a lot longer than it should.
Urn TempUrn1 = urn1;
Urn TempUrn2 = urn2;
while (TempUrn1.Type != "Database")
{
TempUrn1 = TempUrn1.Parent;
TempUrn2 = TempUrn2.Parent;
if (TempUrn1 == null)
{
break;
}
}
if (urn1.Type == "Database")
{
Database database1 = (Database)server1.GetSmoObject(TempUrn1);
database1.PrefetchObjects();
Database database2 = (Database)server2.GetSmoObject(TempUrn2);
database2.PrefetchObjects();
}
return Compare(smoObject1, smoObject2);
}
/*******************************************************************************
* Script Views
*******************************************************************************/
void scriptViews(Database db, Scripter scrp, Urn[] urn)
{
string filename;
string vwPath = _dbPath + @"\Views";
Directory.CreateDirectory(vwPath);
foreach (View vw in db.Views)
{
// skip system views
if (vw.IsSystemObject)
{
continue;
}
urn[0] = vw.Urn;
scrp.Options.Indexes = false;
scrp.Options.Triggers = false;
scrp.Options.ScriptDrops = true; //include the drop statement prior to create
scrp.Options.ScriptSchema = true;
filename = vwPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name) + ".view.sql";
Console.WriteLine(" View: " + vw.Schema + "." + vw.Name);
// script the view
//ScriptIt(urn, scrp, filename, true);
ScriptIt(urn, scrp, filename, false);
// Script View Indexes
string ndxPath = vwPath + @"..\..\Indexes";
Directory.CreateDirectory(ndxPath);
foreach (Index ndx in vw.Indexes)
{
Console.WriteLine(" Index: " + ndx.Name);
urn[0] = ndx.Urn;
filename = ndxPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name)
+ "." + scrub(ndx.Name) + ".index.sql";
// script the index
ScriptIt(urn, scrp, filename);
}
// Script View Triggers
string trgPath = vwPath + @"\Triggers";
Directory.CreateDirectory(trgPath);
foreach (Trigger trg in vw.Triggers)
{
Console.WriteLine(" Trigger: " + trg.Name);
urn[0] = trg.Urn;
filename = trgPath + @"\" + scrub(vw.Schema) + "." + scrub(vw.Name)
+ "." + scrub(trg.Name) + ".trigger.sql";
// script the trigger with drop statement
ScriptIt(urn, scrp, filename);
}
}
}
public bool CompareContent(Urn urn1, Urn urn2)
{
bool result = true;
// See if they are tables
Table table1 = server1.GetSmoObject(urn1) as Table;
Table table2 = server2.GetSmoObject(urn2) as Table;
if (table1 != null && table2 != null)
return CompareObjectContents(urn1, urn2, table1.Schema, table2.Schema);
// See if they are views
View view1 = server1.GetSmoObject(urn1) as View;
View view2 = server2.GetSmoObject(urn2) as View;
if (view1 != null && view2 != null)
return CompareObjectContents(urn1, urn2, view1.Schema, view2.Schema);
Database database1 = server1.GetSmoObject(urn1) as Database;
Database database2 = server2.GetSmoObject(urn2) as Database;
if (database1 == null || database2 == null)
{
WriteLine(Properties.Resources.ObjectsNotDatabaseEtc,
MessageType.Error);
return false;
}
// Get all tables
if (database1.Tables.Count != database2.Tables.Count)
{
WriteLine(Properties.Resources.DifferentNumberTables, MessageType.Error);
return false;
}
for (int k = 0; k < database1.Tables.Count; k++)
result &= CompareObjectContents(database1.Tables[k].Urn,
database2.Tables[k].Urn, database1.Tables[k].Schema,
database2.Tables[k].Schema);
// Get all views
if (database1.Views.Count != database2.Views.Count)
{
WriteLine(Properties.Resources.DifferentNumberViews,
MessageType.Error);
return false;
}
for (int k = 0; k < database1.Views.Count; k++)
result &= CompareObjectContents(database1.Views[k].Urn,
database2.Views[k].Urn, database1.Views[k].Schema,
database2.Views[k].Schema);
return result;
}
private bool CompareObjectContents(Urn urn1, Urn urn2, string schema1, string schema2)
{
ArrayList sysSchemas
= new ArrayList(new string[] { "sys", "INFORMATION_SCHEMA" });
bool result = true;
string objectName1 = null;
string objectName2 = null;
string databaseName1 = null;
string databaseName2 = null;
string command1 = null;
string command2 = null;
SqlDataReader myReader1 = null;
SqlDataReader myReader2 = null;
Type type1 = null;
Type type2 = null;
object value1 = null;
object value2 = null;
bool notEnd1 = false;
bool notEnd2 = false;
bool bAux = true;
int nRows = 0;
if (sysSchemas.Contains(schema1) || sysSchemas.Contains(schema2))
return true;
WriteLine(string.Format(System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Comparing,
urn1, urn2));
try
{
try
{
// This has to be a database
databaseName1 = urn1.Parent.GetAttribute("Name");
objectName1 = urn1.GetAttribute("Name");
command1 = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
"SELECT * FROM [{0}].[{1}].[{2}]",
databaseName1, schema1, objectName1);
myReader1
= server1.ConnectionContext.ExecuteReader(command1);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorReadingFirst,
objectName1, ex), MessageType.Error);
return false;
}
try
{
// This has to be a database
databaseName2 = urn2.Parent.GetAttribute("Name");
objectName2 = urn2.GetAttribute("Name");
command2 = string.Format(
System.Globalization.CultureInfo.InvariantCulture,
"SELECT * FROM [{0}].[{1}].[{2}]",
databaseName2, schema2, objectName2);
myReader2
= server2.ConnectionContext.ExecuteReader(command2);
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorReadingSecond,
objectName2, ex), MessageType.Error);
return false;
}
if (myReader1.FieldCount != myReader2.FieldCount)
{
WriteLine(Properties.Resources.FieldCountDiffers,
MessageType.Error);
result = false;
}
else
{
notEnd1 = myReader1.Read();
notEnd2 = myReader2.Read();
while (notEnd1 && notEnd2)
{
bAux = true;
nRows++;
for (int k = 0; k < myReader1.FieldCount; k++)
{
type1 = myReader1.GetFieldType(k);
type2 = myReader2.GetFieldType(k);
value1 = myReader1.GetValue(k);
value2 = myReader2.GetValue(k);
bAux = value1.Equals(value2);
result &= bAux;
if (!bAux)
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Values,
value1.ToString(), value2.ToString()));
bAux = type1.Equals(type2);
result &= bAux;
if (!bAux)
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.Types, type1.FullName,
type2.FullName));
}
notEnd1 = myReader1.Read();
notEnd2 = myReader2.Read();
}
if (notEnd1 != notEnd2)
{
result = false;
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.MoreRows,
nRows), MessageType.Error);
}
}
}
catch (ApplicationException ex)
{
WriteLine(string.Format(
System.Globalization.CultureInfo.InvariantCulture,
Properties.Resources.ErrorCreatingFirstObject, ex));
throw new ApplicationException(
Properties.Resources.ErrorCreatingFirstObjectException,
ex);
}
finally
{
myReader1.Close();
myReader2.Close();
}
return result;
}
