public static Dictionary <int, int[]> GetContentIds(IEnumerable <int> ids, Customer customer)
{
var dbConnector = new DBConnector(customer.ConnectionString, customer.DatabaseType);
var idList = SqlQuerySyntaxHelper.IdList(customer.DatabaseType, "@Ids", "Ids");
var dbCommand = dbConnector.CreateDbCommand($@"SELECT CONTENT_ID, CONTENT_ITEM_ID
FROM CONTENT_ITEM
{SqlQuerySyntaxHelper.WithNoLock(customer.DatabaseType)}
WHERE CONTENT_ITEM_ID IN (SELECT ID FROM {idList})" );
dbCommand.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, customer.DatabaseType));
var dt = dbConnector.GetRealData(dbCommand);
return(dt
.AsEnumerable()
.GroupBy(x => (int)(decimal)x["CONTENT_ID"])
.ToDictionary(x => x.Key, x => x.Select(y => (int)(decimal)y["CONTENT_ITEM_ID"]).ToArray()));
}
public IDictionary <int, AbstractItemExtensionCollection> GetAbstractItemExtensionlessData(IEnumerable <int> ids,
ContentPersistentData baseContent,
bool isStage, IDbTransaction transaction = null)
{
var idListTable = SqlQuerySyntaxHelper.IdList(UnitOfWork.DatabaseType, "@ids", "ids");
var withNoLock = SqlQuerySyntaxHelper.WithNoLock(UnitOfWork.DatabaseType);
string extFieldsQuery =
$@"SELECT * FROM {baseContent.GetTableName(isStage)} ai {withNoLock}
INNER JOIN {idListTable} on Id = ai.Content_item_id";
using (var command = UnitOfWork.Connection.CreateCommand())
{
command.CommandText = extFieldsQuery;
command.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, UnitOfWork.DatabaseType));
command.Transaction = transaction;
return(LoadAbstractItemExtension(command));
}
}
protected override void GetData(Dictionary <int, ContentModification> newValues)
{
Enum.TryParse <DatabaseType>(DbType, out var dbType);
using (var cs = new QPConnectionScope(ConnectionString, dbType))
{
var con = cs.DbConnection;
string query = string.Format(_cmdText, SqlQuerySyntaxHelper.WithNoLock(dbType));
DbCommand cmd = dbType == DatabaseType.SqlServer
? (DbCommand) new SqlCommand(query)
: new NpgsqlCommand(query);
using (cmd)
{
cmd.CommandType = CommandType.Text;
if (con.State != ConnectionState.Open)
{
con.Open();
}
// производим запрос - без этого не будет работать dependency
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var item = new ContentModification
{
ContentId = Convert.ToInt32(reader["CONTENT_ID"]),
LiveModified = Convert.ToDateTime(reader["LIVE_MODIFIED"]),
StageModified = Convert.ToDateTime(reader["STAGE_MODIFIED"])
};
newValues[item.ContentId] = item;
}
}
}
}
//tsSuppressed.Complete();
}
public IDictionary <int, M2mRelations> GetManyToManyData(IEnumerable <int> ids, bool isStage, IDbTransaction transaction = null)
{
var m2MTableName = QpTableNameHelper.GetM2MTableName(isStage);
var idListTable = SqlQuerySyntaxHelper.IdList(UnitOfWork.DatabaseType, "@ids", "ids");
var withNoLock = SqlQuerySyntaxHelper.WithNoLock(UnitOfWork.DatabaseType);
var query = $@"
SELECT link_id, item_id, linked_item_id
FROM {m2MTableName} link {withNoLock}
INNER JOIN {idListTable} on Id = link.item_id";
using (var command = UnitOfWork.Connection.CreateCommand())
{
command.CommandText = query;
command.Parameters.Add(SqlQuerySyntaxHelper.GetIdsDatatableParam("@Ids", ids, UnitOfWork.DatabaseType));
command.Transaction = transaction;
var result = new Dictionary <int, M2mRelations>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var itemId = Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("item_id")));
if (!result.ContainsKey(itemId))
{
result[itemId] = new M2mRelations();
}
result[itemId]
.AddRelation(Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("link_id"))),
Convert.ToInt32(reader.GetDecimal(reader.GetOrdinal("linked_item_id"))));
}
return(result);
}
}
}
private string GetCmdText(DatabaseType dbType)
{
return($@"SELECT CONTENT_ID, LIVE_MODIFIED, STAGE_MODIFIED FROM CONTENT_MODIFICATION {SqlQuerySyntaxHelper.WithNoLock(dbType)}");
}