private void ProcessVersion(XmlElement xmlElement) { // Drop version property if present if (database.ExtendedProperties.Contains(Constants.KeyVersion)) { database.ExtendedProperties[Constants.KeyVersion].Drop(); } ExtendedProperty exp = new ExtendedProperty(database, Constants.KeyVersion); exp.Value = xmlElement.InnerText; database.ExtendedProperties.Add(exp); exp.Create(); }
public static void SetExtendedPropertyValue(this Database db, string epName, string value) { var ep = db.ExtendedProperties[epName]; if (ep == null) { ep = new ExtendedProperty(db, epName); ep.Value = value; ep.Create(); } else { ep.Value = value; ep.Alter(); } }
static int Main(string[] args) { try { if (args.Contains("/runondbupdated")) { var server = new Server(Common.Properties.SqlServer); var database = server.Databases[Common.Properties.SqlDatabase]; var dbVersion = database.ExtendedProperties[DatabaseVersionExtendedPropertyName]; if (dbVersion == null) throw new Exception("DatabaseVersion was null! Probably needs ScriptRunner to execute against it"); var extendedPropertyName = "DatabaseVersion(" + args[0] + "|" + Hash(args[1]) + ")"; var toolVersion = database.ExtendedProperties[extendedPropertyName]; var toolVersionParts = (toolVersion == null ? "0|" + DateTime.MinValue + "|" + DateTime.MinValue + "|" + "noscript" : (string) toolVersion.Value).Split('|'); var dbVersionParts = ((string) dbVersion.Value).Split('|'); if (int.Parse(toolVersionParts[0]) < int.Parse(dbVersionParts[0]) || DateTime.Parse(toolVersionParts[1]) < DateTime.Parse(dbVersionParts[1])) { Console.WriteLine("Db version changed. Running tool."); int returnValue = RunProcess(args); if (returnValue != 0) { return returnValue; } if (database.ExtendedProperties[extendedPropertyName] == null) { var ep = new ExtendedProperty() {Parent = database, Name = extendedPropertyName, Value = database.ExtendedProperties[DatabaseVersionExtendedPropertyName].Value}; ep.Create(); } else { database.ExtendedProperties[extendedPropertyName].Value = database.ExtendedProperties[DatabaseVersionExtendedPropertyName].Value; database.ExtendedProperties[extendedPropertyName].Alter(); } } return 0; } else { return RunProcess(args); } }catch(Exception ex) { Console.WriteLine(ex.ToString()); return 1; } }
public void Create() { ExtendedProperty prop; //Drop the properties this.Drop(); IDictionaryEnumerator enumerator = m_Properties.GetEnumerator(); while (enumerator.MoveNext()) { if (!this.ServiceBroker.Parent.ExtendedProperties.Contains(enumerator.Key.ToString())) { prop = new ExtendedProperty(this.ServiceBroker.Parent, enumerator.Key.ToString(), enumerator.Value.ToString()); prop.Create(); } } }
private void AddExtendedProperty(string name, string strValue, IExtendedProperties smoObject) { name = Constants.SchemaMeta + "." + name; object value = ParseString(strValue); ExtendedProperty exp; if (smoObject.ExtendedProperties.Contains(name)) { exp = smoObject.ExtendedProperties[name]; exp.Value = value; exp.Alter(); } else { exp = new ExtendedProperty((SqlSmoObject)smoObject, name, value); smoObject.ExtendedProperties.Add(exp); exp.Create(); } }
private static void SetupExtProperty(Column c, string propertyName, object value) { ExtendedProperty extProperty; if (c.ExtendedProperties[propertyName] == null) { extProperty = new ExtendedProperty { Parent = c, Name = propertyName, Value = value }; extProperty.Create(); } else { extProperty = c.ExtendedProperties[propertyName]; extProperty.Value = value; extProperty.Alter(); } }
/// <summary> /// Sets the schema version in the database. /// </summary> /// <param name="version">The version.</param> /// <returns></returns> public override bool SetVersion(string databaseName, Domain.Values.Version version) { try { if (_server.Databases[databaseName].ExtendedProperties.Contains("SCHEMA_VERSION")) { var extendedProperty = _server.Databases[databaseName].ExtendedProperties["SCHEMA_VERSION"]; extendedProperty.Value = version.ToString(); extendedProperty.Alter(); } else { var extendedProperty = new ExtendedProperty(_server.Databases[databaseName], "SCHEMA_VERSION", version.ToString()); extendedProperty.Create(); _server.Databases[databaseName].Alter(); } } catch (ExecutionFailureException efeEx) { log.Error("An Exception occurred. See the debug information that follows.", efeEx); return false; } catch (SmoException smoEx) { log.Error("An Exception occurred. See the debug information that follows.", smoEx); return false; } return true; }
public static void Main(string[] args) { var sb = new StringBuilder(); // Connect to the default instance // Be sure you have 'AdventureWorks2014' on default instance // or specify server on which exists 'AdventureWorks2014' database // ServerConnection cnn2 = new ServerConnection("<server name>"); var cnn = new ServerConnection(); cnn.Connect(); Console.WriteLine("Connected"); //Create the server object var server = new Server(cnn); Console.WriteLine("Create the server object - default instance"); //Create the database object var db = server.Databases[CDatabasename]; Console.WriteLine("Create the database object - AdventureWorks2014"); //Setup the extended property on database level ExtendedProperty extProperty; if (db.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = db, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = db.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property"); //---------------------------------------------------------------------- // Setup the extended property on schema level // db.Schemas is the way how we access schemas collection //---------------------------------------------------------------------- var sch = db.Schemas["HumanResources"]; if (sch.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = sch, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = sch.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } //---------------------------------------------------------------------- // Setup the extended property on table level // db.Tables is the way how we access tables collection //---------------------------------------------------------------------- var tbl = db.Tables["Employee", "HumanResources"]; if (tbl.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = tbl, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = tbl.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on table level"); //---------------------------------------------------------------------- // Setup the extended property on column level // tbl.Columns is the way how we access columns collection //---------------------------------------------------------------------- if (tbl.Columns["NationalIDNumber"].ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = tbl.Columns["NationalIDNumber"], Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = tbl.Columns["NationalIDNumber"].ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on column level"); //---------------------------------------------------------------------- // Setup the extended property on index level // tbl.Indexes is the way how we access indexes collection //---------------------------------------------------------------------- if (tbl.Indexes["PK_Employee_BusinessEntityID"].ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = tbl.Indexes["PK_Employee_BusinessEntityID"], Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = tbl.Indexes["PK_Employee_BusinessEntityID"].ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on index level"); //---------------------------------------------------------------------- // Setup the extended property on storedProcedure level // tbl.StoredProcedures is the way how we access StoredProcedures collection //---------------------------------------------------------------------- var sp = db.StoredProcedures["uspUpdateEmployeeHireInfo", "HumanResources"]; if (sp.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = sp, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = sp.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on stored procedure level"); //---------------------------------------------------------------------- // Setup the extended property on constraint level // tbl.Checks is the way how we access checks collection //---------------------------------------------------------------------- if (tbl.Checks["CK_Employee_BirthDate"].ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = tbl.Checks["CK_Employee_BirthDate"], Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = tbl.Checks["CK_Employee_BirthDate"].ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on constraint level"); //---------------------------------------------------------------------- // Setup the extended property on view level // db.Views is the way how we access views collection //---------------------------------------------------------------------- if (db.Views["vEmployee", "HumanResources"].ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = db.Views["vEmployee", "HumanResources"], Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = db.Views["vEmployee", "HumanResources"].ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on view level"); //---------------------------------------------------------------------- // Setup the extended property on XmlSchemaCollection level // db.XmlSchemaCollections is the way how we access XmlSchemaCollections collection //---------------------------------------------------------------------- var xmlsc = db.XmlSchemaCollections["IndividualSurveySchemaCollection", "Person"]; if (xmlsc.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = xmlsc, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = xmlsc.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on XML schema collection level"); //---------------------------------------------------------------------- // Setup the extended property on ForeignKey level // tbl.ForeignKeys is the way how we access ForeignKeys collection //---------------------------------------------------------------------- var fk = tbl.ForeignKeys["FK_Employee_Person_BusinessEntityID"]; if (fk.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = fk, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = fk.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on foreign key level"); //---------------------------------------------------------------------- // Setup the extended property on UserDefinedDataType level // db.UserDefinedDataType is the way how we access UserDefinedDataTypes collection //---------------------------------------------------------------------- var types2 = db.UserDefinedDataTypes["Flag"]; if (types2.ExtendedProperties[Createor] == null) { extProperty = new ExtendedProperty { Parent = types2, Name = Createor, Value = Value }; extProperty.Create(); } else { extProperty = types2.ExtendedProperties[Createor]; extProperty.Value = Value; extProperty.Alter(); } Console.WriteLine("Setup the extended property on user-defined type level"); if (cnn.IsOpen) { cnn.Disconnect(); } cnn = null; server = null; Console.WriteLine("Press any key to exit..."); Console.ReadLine(); }