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; }