public void SqlUpdate(IEnumerable <UserRole> items)
{
if (!items.Any())
{
return;
}
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
stringBuilder.AppendFormat("UPDATE sqltable{0}", Environment.NewLine);
stringBuilder.AppendLine(string.Format("SET CreationDate={0}, CreatedBy={1}, ModificationDate={2}, ModifiedBy={3}, Comment={4}, CommitmentDate={5}, FarmGroup={6}, Role_Id={7}, User_Id={8}", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(DateTime.Now), SqlEncode(UserName), SqlEncode(item.Comment), SqlEncode(item.CommitmentDate), (int)item.FarmGroup, SqlEncodeId(item.Role), SqlEncodeId(item.User)));
stringBuilder.AppendFormat("FROM [{0}].[{1}].[{2}] AS sqltable {3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <UserRole>(), Environment.NewLine);
stringBuilder.AppendFormat("WHERE sqltable.Id='{0}'{1}", item.Id, Environment.NewLine);
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlDeleteTable(string tableName)
{
var fullTableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", tableName);
var command = string.Format("DELETE FROM {0}", fullTableName);
repository_.ExecuteSqlCommand(command);
}
public void SqlUpdate(IEnumerable <User> items)
{
if (!items.Any())
{
return;
}
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
stringBuilder.AppendFormat("UPDATE sqltable{0}", Environment.NewLine);
stringBuilder.AppendLine(string.Format("SET CreationDate={0}, CreatedBy={1}, ModificationDate={2}, ModifiedBy={3}, Blocked={4}, Email={5}, LastActivityDate={6}, LastLoginDate={7}, LastPasswordChangeDate={8}, Password={9}, SuperUser={10}, UserName={11}", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(DateTime.Now), SqlEncode(UserName), SqlEncode(item.Blocked), SqlEncode(item.Email), SqlEncode(item.LastActivityDate), SqlEncode(item.LastLoginDate), SqlEncode(item.LastPasswordChangeDate), SqlEncode(item.Password), SqlEncode(item.SuperUser), SqlEncode(item.UserName)));
stringBuilder.AppendFormat("FROM [{0}].[{1}].[{2}] AS sqltable {3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <User>(), Environment.NewLine);
stringBuilder.AppendFormat("WHERE sqltable.Id='{0}'{1}", item.Id, Environment.NewLine);
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
/// <summary>
/// Creates a missing row on a given table.
/// </summary>
/// <param name="table">Table on which to create a missing row.</param>
/// <param name="keyValues">Key values for the missing row.</param>
/// <returns>Created rows.</returns>
private IEnumerable <IDataObjectBase> CreateMissingRow(ITable table, ICollection <string> keyValues)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (keyValues == null)
{
throw new ArgumentNullException("keyValues");
}
var missingRow = new List <IDataObjectBase>(table.CreateRow());
foreach (var setFieldValue in SetFieldValues)
{
DataRepositoryHelper.UpdateSourceValue(DataRepositoryHelper.GetDataObject(missingRow, setFieldValue.Key), setFieldValue.Value);
}
for (var i = 0; i < Math.Min(table.PrimaryKey.Fields.Count, keyValues.Count); i++)
{
IDataObjectBase dataObject;
try
{
dataObject = DataRepositoryHelper.GetDataObject(missingRow, table.PrimaryKey.Fields.ElementAt(i).Key);
}
catch (DeliveryEngineSystemException)
{
dataObject = DataRepositoryHelper.GetDataObject(missingRow, table.PrimaryKey.Fields.ElementAt(i).Key.NameTarget);
}
DataRepositoryHelper.UpdateSourceValue(dataObject, keyValues.ElementAt(i));
}
return(missingRow);
}
/// <summary>
/// Gets the extra criterias to the data queryer.
/// </summary>
/// <param name="key">Key on which to build extra criterias.</param>
/// <param name="dataObjects">Data objects used to build the criteria.</param>
/// <returns>Extra criterias.</returns>
private static IList <KeyValuePair <string, object> > GetExtraCriterias(IKey key, IList <IDataObjectBase> dataObjects)
{
if (key == null)
{
throw new ArgumentNullException("key");
}
try
{
var tableFields = key.Table.Fields;
var extraCriterias = key.Fields
.Select(keyField => keyField.Key)
.Select(keyField =>
{
var dataObject = DataRepositoryHelper.GetDataObject(dataObjects, keyField);
var keyFieldName = tableFields.Count(m => String.Compare(m.NameSource, keyField.NameSource, StringComparison.OrdinalIgnoreCase) == 0) > 1 ? keyField.NameTarget : keyField.NameSource;
return(new Tuple <string, object>(keyFieldName, DataRepositoryHelper.GetSourceValue(dataObject)));
})
.Where(extraCriteria => Equals(extraCriteria.Item2, null) == false)
.Select(extraCriteria => new KeyValuePair <string, object>(extraCriteria.Item1, extraCriteria.Item2));
return(new List <KeyValuePair <string, object> >(extraCriterias));
}
finally
{
while (dataObjects.Count > 0)
{
dataObjects.Clear();
}
}
}
public ISuperService RemoveAndSave <TItem>(TItem item) where TItem : PocoBase
{
IList <IService> successfullyRemovedServices = new List <IService>();
IService serviceRefusingRemove = null;
try
{
foreach (var service in Services)
{
serviceRefusingRemove = service;
service
.Remove(item)
.Save();
successfullyRemovedServices.Add(service);
}
}
catch (Exception exception)
{
foreach (var successfullyRemovedService in successfullyRemovedServices)
{
item.Id = 0;
successfullyRemovedService
.Add(item)
.Save();
}
throw new InvalidOperationException(
string.Format("Could not remove {0} from service {1} among {2} following services modified (undo = remove;add) {3}",
item,
DataRepositoryHelper.DatabaseNameFor(serviceRefusingRemove.ConnectionString),
ServiceListToString(Services),
ServiceListToString(successfullyRemovedServices)),
exception);
}
return(this);
}
/// <summary>
/// Manipulates data for the table used by the data manipulator.
/// </summary>
/// <param name="table">Table on which to manipulate data.</param>
/// <param name="dataToManipulate">Data which sould be manipulated.</param>
/// <returns>Manipulated data for the table.</returns>
protected override IEnumerable <IEnumerable <IDataObjectBase> > Manipulate(ITable table, IList <IEnumerable <IDataObjectBase> > dataToManipulate)
{
for (var dataRowNo = 0; dataRowNo < dataToManipulate.Count; dataRowNo++)
{
var dataObject = DataRepositoryHelper.GetDataObject(dataToManipulate.ElementAt(dataRowNo).ToList(), FieldName);
var sourceValue = DataRepositoryHelper.GetSourceValue(dataObject);
if (Equals(sourceValue, null))
{
continue;
}
var sourceValueAsString = sourceValue.ToString();
switch (ApplyOn)
{
case RegularExpressionApplyOn.ApplyOnMatch:
if (RegularExpression.IsMatch(sourceValueAsString))
{
DataRepositoryHelper.UpdateSourceValue(dataObject, FieldValue);
}
continue;
case RegularExpressionApplyOn.ApplyOnUnmatch:
if (RegularExpression.IsMatch(sourceValueAsString) == false)
{
DataRepositoryHelper.UpdateSourceValue(dataObject, FieldValue);
}
continue;
default:
throw new DeliveryEngineSystemException(Resource.GetExceptionMessage(ExceptionMessage.IllegalValue, ApplyOn, "ApplyOn"));
}
}
return(dataToManipulate);
}
public void SqlUpdate(IEnumerable <MetadataNavigationProperty> items)
{
if (!items.Any())
{
return;
}
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
stringBuilder.AppendFormat("UPDATE sqltable{0}", Environment.NewLine);
stringBuilder.AppendLine(string.Format("SET CreationDate={0}, CreatedBy={1}, ModificationDate={2}, ModifiedBy={3}, Documentation={4}, FromMultiplicity_={5}, Guid={6}, Name={7}, ToMultiplicity_={8}, Type={9}, MetadataEntity_Id={10}", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(DateTime.Now), SqlEncode(UserName), SqlEncode(item.Documentation), SqlEncode(item.FromMultiplicity_), SqlEncode(item.Guid), SqlEncode(item.Name), SqlEncode(item.ToMultiplicity_), SqlEncode(item.Type), SqlEncodeId(item.MetadataEntity)));
stringBuilder.AppendFormat("FROM [{0}].[{1}].[{2}] AS sqltable {3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <MetadataNavigationProperty>(), Environment.NewLine);
stringBuilder.AppendFormat("WHERE sqltable.Id='{0}'{1}", item.Id, Environment.NewLine);
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlUpdate(IEnumerable <NewsItem> items)
{
if (!items.Any())
{
return;
}
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
stringBuilder.AppendFormat("UPDATE sqltable{0}", Environment.NewLine);
stringBuilder.AppendLine(string.Format("SET CreationDate={0}, CreatedBy={1}, ModificationDate={2}, ModifiedBy={3}, Comment={4}, Description={5}, FileData={6}, FileName={7}, FileType={8}, LinkUrl={9}, Title={10}, ValidityDateEnd={11}, ValidityDateStart={12}", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(DateTime.Now), SqlEncode(UserName), SqlEncode(item.Comment), SqlEncode(item.Description), SqlEncode(item.FileData), SqlEncode(item.FileName), SqlEncode(item.FileType), SqlEncode(item.LinkUrl), SqlEncode(item.Title), SqlEncode(item.ValidityDateEnd), SqlEncode(item.ValidityDateStart)));
stringBuilder.AppendFormat("FROM [{0}].[{1}].[{2}] AS sqltable {3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <NewsItem>(), Environment.NewLine);
stringBuilder.AppendFormat("WHERE sqltable.Id='{0}'{1}", item.Id, Environment.NewLine);
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlDeleteTable <TPoco>() where TPoco : class, IPocoBase
{
var tableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <TPoco>());
var command = string.Format("DELETE FROM {0}", tableName);
repository_.ExecuteSqlCommand(command);
}
/// <summary>
/// Clear data rows where the foreign key is missing.
/// </summary>
/// <param name="table">Table for which to manipulate data.</param>
/// <param name="dataToManipulate">Data rows which should be manipulated.</param>
/// <param name="keyValuesToClear">Key values for the rows, where the value should be cleared.</param>
/// <param name="foreignKeyFields">Field in the foreign key.</param>
/// <param name="keyValueComparer">Functionality to compare key values.</param>
/// <returns>Data rows where value for the foreign key missing is cleaned.</returns>
private IEnumerable <IEnumerable <IDataObjectBase> > ClearDataRows(INamedObject table, ICollection <IEnumerable <IDataObjectBase> > dataToManipulate, IList <string> keyValuesToClear, IList <IField> foreignKeyFields, IEqualityComparer <string> keyValueComparer)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (dataToManipulate == null)
{
throw new ArgumentNullException("dataToManipulate");
}
if (keyValuesToClear == null)
{
throw new ArgumentNullException("keyValuesToClear");
}
if (foreignKeyFields == null)
{
throw new ArgumentNullException("foreignKeyFields");
}
if (keyValueComparer == null)
{
throw new ArgumentNullException("keyValueComparer");
}
var dataRowsToClear = new List <IEnumerable <IDataObjectBase> >();
try
{
while (keyValuesToClear.Count > 0)
{
dataRowsToClear.AddRange(DataRepositoryHelper.GetDataRowsForKeyValue(foreignKeyFields, dataToManipulate, keyValuesToClear.ElementAt(0)));
keyValuesToClear.RemoveAt(0);
}
dataRowsToClear.ForEach(dataRow =>
{
var dataObject = dataRow.Single(m => m.Field != null && m.Field.Equals(foreignKeyFields.Last()));
dataObject.UpdateSourceValue <object>(null);
});
var foreignKeyInformationBuilder = new StringBuilder();
foreignKeyInformationBuilder.AppendFormat("{0}(", ForeignKeyTable.NameTarget);
for (var i = 0; i < _foreignKeyFields.Count; i++)
{
if (i > 0)
{
foreignKeyInformationBuilder.AppendFormat(", {0}", _foreignKeyFields.ElementAt(i));
continue;
}
foreignKeyInformationBuilder.Append(_foreignKeyFields.ElementAt(i));
}
foreignKeyInformationBuilder.Append(')');
InformationLogger.LogInformation(Resource.GetText(Text.ForeignKeyCleanerInformation, GetType().Name, dataRowsToClear.Count, table.NameTarget, foreignKeyInformationBuilder.ToString()));
return(dataToManipulate);
}
finally
{
while (dataRowsToClear.Count > 0)
{
dataRowsToClear.Clear();
}
}
}
public void SqlDropTable <TPoco>() where TPoco : class, IPocoBase
{
var tableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <TPoco>());
var command = string.Format("IF object_id('{0}') is not null drop table {0}", tableName);
Log.Verbose("Dropping table '{0}'", tableName);
repository_.ExecuteSqlCommand(command);
}
public void SqlDropTable(string tableName)
{
var fullTableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", tableName);
var command = string.Format("IF object_id('{0}') is not null drop table {0}", fullTableName);
Log.Verbose("Dropping table '{0}'", tableName);
repository_.ExecuteSqlCommand(command);
}
public void SqlDeleteAndReseedTable <TPoco>(int reseedValue = 0) where TPoco : class, IPocoBase
{
var tableName = string.Format("'{0}.{1}.{2}'", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <TPoco>());
SqlDeleteTable <TPoco>();
var command = string.Format("DBCC CHECKIDENT ({0}, reseed, {1})", tableName, reseedValue);
repository_.ExecuteSqlCommand(command);
}
public static IAgrideaService GetService(string databaseName, DbInitializationModes contextInitialization = DbInitializationModes.CreateIfNotExists)
{
Requires <ArgumentException> .IsNotEmpty(databaseName);
return(new AgrideaService(
GetRepository(
DataRepositoryHelper.SqlServerConnectionString(databaseName),
contextInitialization)));
}
public void SqlInsert(IEnumerable <MetadataEntity> items)
{
if (!items.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <MetadataEntity>(), Environment.NewLine);
commandStringBuilder.AppendFormat("{0}{1}", "(CreationDate, CreatedBy, ModificationDate, ModifiedBy, Dimension, Documentation, FileContent, FileName, Guid, Name, Url)", Environment.NewLine);
//Nav. property 'MetadataFieldList' is OneToMany => IGNORE
//Nav. property 'MetadataNavigationPropertyList' is OneToMany => IGNORE
//Nav. property 'UsedEntityList' is ManyToMany => IGNORE
//Nav. property 'UsingEntityList' is ManyToMany => IGNORE
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10})", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(item.ModificationDate), SqlEncode(item.ModifiedBy), SqlEncode(item.Dimension), SqlEncode(item.Documentation), SqlEncode(item.FileContent), SqlEncode(item.FileName), SqlEncode(item.Guid), SqlEncode(item.Name), SqlEncode(item.Url)));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}' duration {2}", rowCount, "MetadataEntity", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
private string ServiceListToString(IEnumerable <IService> services)
{
string toString = "[";
string separator = "";
foreach (var service in services)
{
toString += string.Format("{0}{1}", separator, DataRepositoryHelper.DatabaseNameFor(service.ConnectionString));
separator = ",";
}
toString += "]";
return(toString);
}
public void SqlInsert(IEnumerable <Role> items)
{
if (!items.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <Role>(), Environment.NewLine);
commandStringBuilder.AppendFormat("{0}{1}", "(CreationDate, CreatedBy, ModificationDate, ModifiedBy, CanAccessAllFarms, Description, IsWebApi, Name)", Environment.NewLine);
//Nav. property 'PermissionList' is OneToMany => IGNORE
//Nav. property 'UserRoleList' is OneToMany => IGNORE
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7})", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(item.ModificationDate), SqlEncode(item.ModifiedBy), SqlEncode(item.CanAccessAllFarms), SqlEncode(item.Description), SqlEncode(item.IsWebApi), SqlEncode(item.Name)));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}' duration {2}", rowCount, "Role", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlInsert(IEnumerable <User> items)
{
if (!items.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <User>(), Environment.NewLine);
commandStringBuilder.AppendFormat("{0}{1}", "(CreationDate, CreatedBy, ModificationDate, ModifiedBy, Blocked, Email, LastActivityDate, LastLoginDate, LastPasswordChangeDate, Password, SuperUser, UserName)", Environment.NewLine);
//Nav. property 'UserRoleList' is OneToMany => IGNORE
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11})", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(item.ModificationDate), SqlEncode(item.ModifiedBy), SqlEncode(item.Blocked), SqlEncode(item.Email), SqlEncode(item.LastActivityDate), SqlEncode(item.LastLoginDate), SqlEncode(item.LastPasswordChangeDate), SqlEncode(item.Password), SqlEncode(item.SuperUser), SqlEncode(item.UserName)));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}' duration {2}", rowCount, "User", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
/// <summary>
/// Manipulates data for the table used by the data manipulator.
/// </summary>
/// <param name="table">Table on which to manipulate data.</param>
/// <param name="dataToManipulate">Data which sould be manipulated.</param>
/// <returns>Manipulated data for the table.</returns>
protected override IEnumerable <IEnumerable <IDataObjectBase> > Manipulate(ITable table, IList <IEnumerable <IDataObjectBase> > dataToManipulate)
{
var filter = GenerateFilter(table, CriteriaConfigurations);
for (var i = 0; i < dataToManipulate.Count; i++)
{
if (filter.Exclude(dataToManipulate.ElementAt(i)))
{
continue;
}
var dataObject = DataRepositoryHelper.GetDataObject(dataToManipulate.ElementAt(i).ToList(), FieldName);
DataRepositoryHelper.UpdateSourceValue(dataObject, FieldValue);
}
return(dataToManipulate);
}
public void SqlInsertMetadataUsage(IList <Tuple <int, int> > idPairs)
{
if (!idPairs.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", "MetadataUsage", Environment.NewLine);
commandStringBuilder.AppendFormat("({0}_Id,{1}_Id{2})", "UsedEntity", "UsingEntity", Environment.NewLine);
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var idPair in idPairs)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0},{1})", idPair.Item1, idPair.Item2));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}-{2}' duration {3}", rowCount, "MetadataEntity", "MetadataEntity", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlInsert(IEnumerable <NewsItem> items)
{
if (!items.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <NewsItem>(), Environment.NewLine);
commandStringBuilder.AppendFormat("{0}{1}", "(CreationDate, CreatedBy, ModificationDate, ModifiedBy, Comment, Description, FileData, FileName, FileType, LinkUrl, Title, ValidityDateEnd, ValidityDateStart)", Environment.NewLine);
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12})", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(item.ModificationDate), SqlEncode(item.ModifiedBy), SqlEncode(item.Comment), SqlEncode(item.Description), SqlEncode(item.FileData), SqlEncode(item.FileName), SqlEncode(item.FileType), SqlEncode(item.LinkUrl), SqlEncode(item.Title), SqlEncode(item.ValidityDateEnd), SqlEncode(item.ValidityDateStart)));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}' duration {2}", rowCount, "NewsItem", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlInsert(IEnumerable <UserRole> items)
{
if (!items.Any())
{
return;
}
StringBuilder commandStringBuilder = new StringBuilder();
commandStringBuilder.AppendFormat("INSERT INTO [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <UserRole>(), Environment.NewLine);
commandStringBuilder.AppendFormat("{0}{1}", "(CreationDate, CreatedBy, ModificationDate, ModifiedBy, Comment, CommitmentDate, FarmGroup, Role_Id, User_Id)", Environment.NewLine);
commandStringBuilder.AppendFormat("VALUES{0}", Environment.NewLine);
int rowCount = 0;
var startTime = DateTime.Now;
string separator = null;
StringBuilder stringBuilder = new StringBuilder();
foreach (var item in items)
{
if (rowCount % 1000 == 0)
{
stringBuilder.Append(commandStringBuilder);
separator = null;
}
if (separator != null)
{
stringBuilder.AppendLine(separator);
}
stringBuilder.Append(string.Format("({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8})", SqlEncode(item.CreationDate), SqlEncode(item.CreatedBy), SqlEncode(item.ModificationDate), SqlEncode(item.ModifiedBy), SqlEncode(item.Comment), SqlEncode(item.CommitmentDate), (int)item.FarmGroup, SqlEncodeId(item.Role), SqlEncodeId(item.User)));
rowCount++;
separator = ",";
}
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
Log.Verbose("Inserted {0} '{1}' duration {2}", rowCount, "UserRole", DateTime.Now - startTime);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlDelete <TPoco>(Expression <Func <TPoco, int> > foreignKeyExpression, int value) where TPoco : class, IPocoBase
{
var member = foreignKeyExpression.Body as MemberExpression;
Asserts <ArgumentException> .IsNotNull(member);
var memberName = member.ToString();
Asserts <ArgumentException> .AreEqual(3, memberName.Split('.'), "foreignKeyExpression should be something like m.NavigationProperty.Id");
Asserts <ArgumentException> .IsTrue(memberName.EndsWith(".Id"), "foreignKeyExpression should end with .Id");
var foreignKeyName = string.Join("_", memberName.Split('.').Skip(1));
var tableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <TPoco>());
repository_.ExecuteSqlCommand(string.Format("DELETE FROM {0} WHERE {1} = {2}", tableName, foreignKeyName, value));
}
public void SqlDeleteMetadataUsage()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("DELETE [{0}].[{1}].[{2}]{3}", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", "MetadataUsage", Environment.NewLine);
string command = stringBuilder.ToString();
try
{
Repository.ExecuteSqlCommand(command);
}
catch (Exception)
{
Log.Error(command);
throw;
}
}
public void SqlDelete <TPoco>(IEnumerable <TPoco> items) where TPoco : class, IPocoBase
{
if (!items.Any())
{
return;
}
var stringBuilder = new StringBuilder();
var tableName = string.Format("[{0}].[{1}].[{2}]", DataRepositoryHelper.DatabaseNameFor(ConnectionString), "dbo", CodeGenerationHelper.GetTableName <TPoco>());
foreach (var item in items.Select(m => m.Id))
{
stringBuilder.AppendFormat("DELETE FROM {0} WHERE Id = {1}{2}", tableName, item, Environment.NewLine);
}
var command = stringBuilder.ToString();
repository_.ExecuteSqlCommand(command);
}
/// <summary>
/// Manipulates missing foreign key values for a given table.
/// </summary>
/// <param name="table">Tabel to manipulate data for missing foreign key values.</param>
/// <param name="dataRepository">Data repository.</param>
/// <param name="dataToManipulate">Data for the table where to manipulate data for missing foreign key values.</param>
/// <returns>Manipulates data for the table.</returns>
protected override IEnumerable <IEnumerable <IDataObjectBase> > Manipulate(ITable table, IDataRepository dataRepository, IList <IEnumerable <IDataObjectBase> > dataToManipulate)
{
var dictionaryName = GetDictionaryName(table.PrimaryKey);
var workerDictionaryName = string.Format("{0} - {1}", dictionaryName, WorkerId);
// Store primary key values in the cache.
lock (SyncRoot)
{
try
{
var keyValues = new List <string>(DataRepositoryHelper.GetKeyValues(table.PrimaryKey, dataToManipulate, false));
try
{
if (DataCache.ContainsKey(workerDictionaryName) == false)
{
DataCache.Add(workerDictionaryName, new Tuple <List <string>, List <string> >(new List <string>(keyValues), null));
return(dataToManipulate);
}
DataCache[workerDictionaryName].Item1.AddRange(keyValues);
return(dataToManipulate);
}
finally
{
while (keyValues.Count > 0)
{
keyValues.Clear();
}
}
}
catch
{
while (DataCache.ContainsKey(workerDictionaryName))
{
while (DataCache[workerDictionaryName].Item1.Count > 0)
{
DataCache[workerDictionaryName].Item1.Clear();
}
DataCache.Remove(workerDictionaryName);
}
throw;
}
}
}
/// <summary>
/// Gets the values for data objects for a given foreign key.
/// </summary>
/// <param name="primaryKey">Primary key for the table.</param>
/// <param name="foreignKey">Foreign key.</param>
/// <param name="dataObjects">Data objects.</param>
/// <returns>String representation of data objects in the foreign key.</returns>
private static string GetKeyValues(IKey primaryKey, IKey foreignKey, IEnumerable <IDataObjectBase> dataObjects)
{
if (primaryKey == null)
{
throw new ArgumentNullException("primaryKey");
}
if (foreignKey == null)
{
throw new ArgumentNullException("foreignKey");
}
if (dataObjects == null)
{
throw new ArgumentNullException("dataObjects");
}
var stringBuilder = new StringBuilder();
var keyFields = new List <KeyValuePair <IField, IMap> >(primaryKey.Fields);
foreach (var foreignKeyField in foreignKey.Fields)
{
if (keyFields.Contains(foreignKeyField))
{
continue;
}
keyFields.Add(foreignKeyField);
}
keyFields.Aggregate(stringBuilder, (current, keyField) =>
{
var dataObject = DataRepositoryHelper.GetDataObject(dataObjects.ToList(), keyField.Key);
var targetValueType = dataObject.Field.DatatypeOfTarget;
var targetValueMethod = dataObject.GetType().GetMethod("GetTargetValue", new[] { typeof(IMap) }).MakeGenericMethod(new[] { targetValueType });
var targetValue = targetValueMethod.Invoke(dataObject, new object[] { keyField.Value });
if (keyField.Equals(keyFields.Last()))
{
return(current.Append(string.Format("{0}={1}", dataObject.Field.NameSource, Equals(targetValue, null) ? "{null}" : targetValue)));
}
return(current.Append(string.Format("{0}={1}, ", dataObject.Field.NameSource, Equals(targetValue, null) ? "{null}" : targetValue)));
});
return(stringBuilder.ToString());
}
/// <summary>
/// Gets the foreign table with a not null criteria in the filters.
/// </summary>
/// <param name="foreignTable">Foreign table to clone and insert a not null criteria in the filters.</param>
/// <param name="fieldName">Name of the field on which to create the not null criteria.</param>
/// <returns>Cloned foreign table with a not null criteria in the filters.</returns>
private static ITable GetForeignTableWithNotNullCriteria(ICloneable foreignTable, string fieldName)
{
if (foreignTable == null)
{
throw new ArgumentNullException("foreignTable");
}
if (string.IsNullOrEmpty(fieldName))
{
throw new ArgumentNullException("fieldName");
}
var clonedForeignTable = (ITable)foreignTable.Clone();
var notNullField = DataRepositoryHelper.GetTableField(clonedForeignTable, fieldName);
if (clonedForeignTable.RecordFilters.Count == 0)
{
clonedForeignTable.AddRecordFilter(new Filter());
}
foreach (var recordFilter in clonedForeignTable.RecordFilters)
{
recordFilter.AddCriteria(new NotNullCriteria(notNullField));
}
return(clonedForeignTable);
}
/// <summary>
/// Manipulates data for the table used by the data manipulator.
/// </summary>
/// <param name="table">Table on which to manipulate data.</param>
/// <param name="dataToManipulate">Data which sould be manipulated.</param>
/// <returns>Manipulated data for the table.</returns>
protected override IEnumerable <IEnumerable <IDataObjectBase> > Manipulate(ITable table, IList <IEnumerable <IDataObjectBase> > dataToManipulate)
{
var filter = GenerateFilter(table, CriteriaConfigurations);
var duplicatedRows = new List <IEnumerable <IDataObjectBase> >(dataToManipulate.Count);
try
{
for (var dataRowNo = 0; dataRowNo < dataToManipulate.Count; dataRowNo++)
{
if (filter.Exclude(dataToManipulate.ElementAt(dataRowNo)))
{
continue;
}
var duplicatedRow = new List <IDataObjectBase>(dataToManipulate.ElementAt(dataRowNo).Count());
duplicatedRow.AddRange(new List <IDataObjectBase>(dataToManipulate.ElementAt(dataRowNo).Select(m => (IDataObjectBase)m.Clone())));
duplicatedRows.Add(duplicatedRow);
}
for (var duplicatedRowNo = 0; duplicatedRowNo < duplicatedRows.Count; duplicatedRowNo++)
{
foreach (var fieldUpdate in FieldUpdates)
{
var dataObject = DataRepositoryHelper.GetDataObject(duplicatedRows.ElementAt(duplicatedRowNo).ToList(), fieldUpdate.Item1);
DataRepositoryHelper.UpdateSourceValue(dataObject, fieldUpdate.Item2);
}
}
duplicatedRows.ForEach(dataToManipulate.Add);
return(dataToManipulate);
}
finally
{
while (duplicatedRows.Count > 0)
{
duplicatedRows.Clear();
}
}
}