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();
}
}
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();
}
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();
}
}
/// <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 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();
}
}
}
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();
}
