public List <DTO.DbObjectDefinition> GetObject(ObjectType objectType = ObjectType.All, string objectName = "", string schemaName = "")
{
List <DbParameter> parameters = new List <DbParameter>();
List <string> objectTypes;
List <DTO.DbObjectDefinition> dbObjectDefinitions = new List <DTO.DbObjectDefinition>();
try
{
objectTypes = objectType.GetValue();
foreach (string item in objectTypes)
{
#region Parameters
//Parameters need to be recreated every time otherwise,
//an exception ("The SqlParameter is already contained by another SqlParameterCollection.") will be thrown.
parameters.Clear();
parameters.Add(GetParameter("@ObjectName", string.IsNullOrEmpty(objectName) ? null : objectName));
parameters.Add(GetParameter("@SchemaName", string.IsNullOrEmpty(schemaName) ? null : schemaName));
if (objectType != ObjectType.All)
{
parameters.Add(GetParameter("@ObjectType", item));
}
else
{
parameters.Add(GetParameter("@ObjectType", null));
}
#endregion
using (DbDataReader dr = ExecuteDataReader(_generalInfo.GetObjectDefinition, parameters, CommandType.Text))
{
DTO.DbObjectDefinition dbObjectDefinition;
while (dr.Read())
{
dbObjectDefinition = new DTO.DbObjectDefinition(dr);
if (ObjectType.Views.GetValue().Contains(dbObjectDefinition.Type))
{
dbObjectDefinition.Definition += GetIndexFromView(dbObjectDefinition.Name);
}
dbObjectDefinitions.Add(dbObjectDefinition);
}
}
if (objectType == ObjectType.All)
{
break;
}
}
}
catch (Exception ex)
{
LogManager.ReconfigExistingLoggers();
_logger.Error(ex);
throw;
}
return(dbObjectDefinitions);
}
private List <string> Update(ObjectType objectType, string fileName, DTO.DbObjectDefinition dbObjectDefinition)
{
List <string> objectUpdated = new List <string>();
List <string> objectDefinitionLocal = System.IO.File.ReadLines(fileName, Util.GetEncoding(fileName)).ToList();
string objectTypeLocal = GetObjectTypeFromDefinition(string.Join(Environment.NewLine, objectDefinitionLocal));
List <string> objectDefinitionRemote = dbObjectDefinition != null?dbObjectDefinition.Definition.Split(new string[] { Environment.NewLine }, StringSplitOptions.None).ToList() : new List <string>();
string objectName = fileName.Split('\\').Last();
try
{
//Check if object type should be updated
var objectTypeList = objectType.GetValue();
if (objectType.Equals(ObjectType.All) || objectTypeList.Contains(objectTypeLocal))
{
//Clean up objects before compare
objectDefinitionLocal = CleanObjectDefinition(objectDefinitionLocal);
objectDefinitionRemote = CleanObjectDefinition(objectDefinitionRemote);
//Update when necessary
if (dbObjectDefinition == null || !AreObjectsEquals(objectName, objectDefinitionLocal, objectDefinitionRemote, objectTypeLocal))
{
CompareNameToDefinitionName(System.IO.Path.GetFileNameWithoutExtension(fileName), objectDefinitionLocal);
string[] queries = string.Join(Environment.NewLine, objectDefinitionLocal).Split(new string[] { CREATE_INDEX }, StringSplitOptions.None);
KeyValuePair <string, string> keyValuePair = GetReplaceString(objectTypeLocal);
for (int i = 0; i < queries.Length; i++)
{
//Remove Encryptation if exists
queries[i] = Regex.Replace(queries[i], "WITH ENCRYPTION*([^\\s]+)", string.Empty, RegexOptions.IgnoreCase);
Console.WriteLine(string.Format("Updating object {0} query {1} of {2}.", objectName, i + 1, queries.Length));
Console.WriteLine("");
if (ExecuteNonQuery(dbObjectDefinition != null ? Regex.Replace(queries[i], keyValuePair.Key, keyValuePair.Value, RegexOptions.IgnoreCase) : queries[i], commandType: CommandType.Text) != 0)
{
if (i == queries.Length - 1) //Wait until execute the last instruction
{
objectUpdated.Add(objectName);
}
}
}
}
}
}
catch (Exception ex)
{
_logger.Error(ex, $"Filename: {fileName }");
throw;
}
return(objectUpdated);
}
