public int Execute() { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(); ITransaction transaction = database.CreateTransaction(); try { using (IDbCommand command = transaction.CreateCommand()) { string countAlias; string statementStr; IList <BindVar> bindVars; MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement, new QueryAttribute[] { }, null, _necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null); IDictionary <QueryAttribute, string> qa2a; builder.GenerateSqlGroupByStatement(out countAlias, out qa2a, out statementStr, out bindVars); command.CommandText = statementStr; foreach (BindVar bindVar in bindVars) { database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType); } return(Convert.ToInt32(command.ExecuteScalar())); } } finally { transaction.Dispose(); } }
protected ICollection <UserProfile> GetProfiles(Guid?profileId, string name) { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(); ITransaction transaction = database.CreateTransaction(); try { int profileIdIndex; int nameIndex; using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserProfilesCommand(transaction, profileId, name, out profileIdIndex, out nameIndex)) { ICollection <UserProfile> result = new List <UserProfile>(); using (IDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Guid profileId_ = database.ReadDBValue <Guid>(reader, profileIdIndex); string name_ = database.ReadDBValue <string>(reader, nameIndex); result.Add(new UserProfile(profileId_, name_)); } } return(result); } } finally { transaction.Dispose(); } }
public bool GetUserAdditionalData(Guid profileId, string key, out string data) { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(); ITransaction transaction = database.CreateTransaction(); try { int dataIndex; using (IDbCommand command = UserProfileDataManagement_SubSchema.SelectUserAdditionalDataCommand(transaction, profileId, key, out dataIndex)) { using (IDataReader reader = command.ExecuteReader()) { if (reader.Read()) { data = database.ReadDBValue <string>(reader, dataIndex); return(true); } } } data = null; return(false); } finally { transaction.Dispose(); } }
public IList <MediaItem> Query(bool singleMode) { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(); ITransaction transaction = database.CreateTransaction(); try { return(Query(database, transaction, singleMode)); } finally { transaction.Dispose(); } }
public Tuple <HomogenousMap, HomogenousMap> Execute() { ISQLDatabase database = ServiceRegistration.Get <ISQLDatabase>(); ITransaction transaction = database.CreateTransaction(); try { using (IDbCommand command = transaction.CreateCommand()) { string keyAlias = null; string valueAlias = null; string groupSizeAlias = null; string statementStr = null; IList <BindVar> bindVars = null; if (_selectValueAttribute.Cardinality == Cardinality.Inline || _selectValueAttribute.Cardinality == Cardinality.ManyToOne) { List <QueryAttribute> qAttributes = new List <QueryAttribute>(); QueryAttribute selectValueAttributeQA = new QueryAttribute(_selectValueAttribute); qAttributes.Add(selectValueAttributeQA); QueryAttribute selectKeyAttributeQA = null; if (_selectKeyAttribute != null) { selectKeyAttributeQA = new QueryAttribute(_selectKeyAttribute); qAttributes.Add(selectKeyAttributeQA); } MIAQueryBuilder builder = new MIAQueryBuilder(_miaManagement, qAttributes, _selectProjectionFunction, _necessaryRequestedMIATypes, new MediaItemAspectMetadata[] {}, _filter, null); IDictionary <QueryAttribute, string> qa2a; builder.GenerateSqlGroupByStatement(out groupSizeAlias, out qa2a, out statementStr, out bindVars); valueAlias = qa2a[selectValueAttributeQA]; if (_selectKeyAttribute != null) { keyAlias = qa2a[selectKeyAttributeQA]; } } else { if (_selectKeyAttribute != null) { throw new InvalidDataException("Value attribute '{0}' does not support key value grouping", _selectValueAttribute.AttributeName); } ComplexAttributeQueryBuilder builder = new ComplexAttributeQueryBuilder(_miaManagement, _selectValueAttribute, _selectProjectionFunction, _necessaryRequestedMIATypes, _filter); builder.GenerateSqlGroupByStatement(_selectAttributeFilter, out valueAlias, out groupSizeAlias, out statementStr, out bindVars); } command.CommandText = statementStr; foreach (BindVar bindVar in bindVars) { database.AddParameter(command, bindVar.Name, bindVar.Value, bindVar.VariableType); } Tuple <HomogenousMap, HomogenousMap> result = null; if (_selectKeyAttribute != null) { Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType; Type keyType = _selectKeyAttribute.AttributeType; HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int)); HomogenousMap keyMap = new HomogenousMap(valueType, keyType); using (IDataReader reader = command.ExecuteReader()) { int keyCol = reader.GetOrdinal(keyAlias); int valueCol = reader.GetOrdinal(valueAlias); int groupSizeCol = reader.GetOrdinal(groupSizeAlias); while (reader.Read()) { if (!keyMap.ContainsKey(database.ReadDBValue(valueType, reader, valueCol))) { keyMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue(keyType, reader, keyCol)); valueMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue <int>(reader, groupSizeCol)); } } } result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, keyMap); } else { Type valueType = _projectionValueType ?? _selectValueAttribute.AttributeType; HomogenousMap valueMap = new HomogenousMap(valueType, typeof(int)); using (IDataReader reader = command.ExecuteReader()) { int valueCol = reader.GetOrdinal(valueAlias); int groupSizeCol = reader.GetOrdinal(groupSizeAlias); while (reader.Read()) { valueMap.Add(database.ReadDBValue(valueType, reader, valueCol), database.ReadDBValue <int>(reader, groupSizeCol)); } } result = new Tuple <HomogenousMap, HomogenousMap>(valueMap, null); } return(result); } } finally { transaction.Dispose(); } }