protected CSFilter BuildRelationFilter(string tableAlias) { if (tableAlias == null) { tableAlias = ""; } else { tableAlias += "."; } if (Relation != null) { CSParameterCollection parameters = new CSParameterCollection(); switch (Relation.RelationType) { case CSSchemaRelationType.OneToMany: { CSParameter csParameter = parameters.Add(); csParameter.Value = RelationObject.Data["#" + Relation.LocalKey].Value; return(new CSFilter("{" + tableAlias + Relation.ForeignKey + "}=" + csParameter.Name, parameters)); } case CSSchemaRelationType.ManyToMany: { if (Relation.ForeignKey == null) { Relation.ForeignKey = Schema.KeyColumns[0].Name; } if (Relation.ForeignLinkKey == null) { Relation.ForeignLinkKey = Relation.ForeignKey; } CSParameter csParameter = parameters.Add(); csParameter.Value = RelationObject.Data["#" + Relation.LocalKey].Value; return(new CSFilter("{" + tableAlias + Relation.ForeignKey + "} $in ($select {" + Relation.ForeignLinkKey + "} $from [" + Relation.LinkTable + "] where {" + Relation.LocalLinkKey + "}=" + csParameter.Name + ")", parameters)); } } } return(CSFilter.None); }
/// <summary> /// Physically deletes the object from the database /// </summary> /// <returns><c>true</c> if the object was deleted successfully. Otherwise <c>false</c></returns> public bool Delete() { if (_dataState == CSObjectDataState.Deleted || _dataState == CSObjectDataState.New) { return(false); } bool cancel = false; Fire_ObjectDeleting(ref cancel); if (cancel) { return(false); } StringBuilder whereClause = new StringBuilder(); CSParameterCollection parameters = new CSParameterCollection(); foreach (CSSchemaColumn schemaColumn in _schema.Columns) { if (!schemaColumn.IsKey) { continue; } CSParameter parameter = parameters.Add(); parameter.Value = _fieldData["#" + schemaColumn.Name].ValueDirect; if (whereClause.Length > 0) { whereClause.Append(" and "); } whereClause.Append(_schema.DB.QuoteField(schemaColumn.Name) + "=@" + parameter.Name.Substring(1)); } if (whereClause.Length == 0) { throw new CSException("No key fields"); } using (CSTransaction csTransaction = new CSTransaction(_schema)) { string deleteSql = _schema.DB.BuildDeleteSQL(_schema.TableName, null, whereClause.ToString()); int numDeleted = _schema.DB.ExecuteNonQuery(deleteSql, parameters); if (numDeleted == 1) { csTransaction.Commit(); } else { return(false); } } _dataState = CSObjectDataState.Deleted; Fire_ObjectDeleted(); return(true); }
private bool Create() { CSParameterCollection parameters = new CSParameterCollection(); List <string> fieldNames = new List <string>(); List <string> fieldValues = new List <string>(); List <string> sequenceNames = new List <string>(); foreach (CSSchemaColumn schemaColumn in _schema.Columns) { CSFieldValue fieldValue = _fieldData["#" + schemaColumn.Name]; if (fieldValue == null) { continue; } CSSchemaField schemaField = fieldValue.SchemaField; if (schemaField.ClientGenerated && schemaColumn.IsKey) { if (schemaField.FieldType == typeof(Guid)) { fieldValue.Value = Guid.NewGuid(); } } else { if (string.IsNullOrEmpty(schemaField.SequenceName) && (!fieldValue.IsDirty || schemaField.ReadOnly || schemaField.NoCreate || schemaColumn.Identity)) { continue; } } if (schemaField.ServerGenerated && schemaColumn.IsKey) { continue; } if (!string.IsNullOrEmpty(schemaField.SequenceName)) { sequenceNames.Add(schemaField.SequenceName); fieldValues.Add(null); } else { CSParameter parameter = parameters.Add(); parameter.Value = fieldValue.ValueDirect; fieldValues.Add("@" + parameter.Name.Substring(1)); sequenceNames.Add(null); } fieldNames.Add(schemaColumn.Name); } string[] primaryKeys = new string[_schema.KeyColumns.Count]; for (int i = 0; i < _schema.KeyColumns.Count; i++) { primaryKeys[i] = _schema.KeyColumns[i].Name; } using (ICSDbReader reader = _schema.DB.ExecuteInsert(_schema.TableName, fieldNames.ToArray(), fieldValues.ToArray(), primaryKeys, sequenceNames.ToArray(), (_schema.IdentityColumn != null && _schema.IdentityColumn.MappedField != null) ? _schema.IdentityColumn.Name : null, parameters)) { if (reader != null && !reader.IsClosed && reader.Read()) { FromDataReader(reader, null); } } _dataState = CSObjectDataState.Loaded; return(true); }
private bool Write() { if (!_fieldData.IsDirty) { return(true); } if (_dataState == CSObjectDataState.Deleted) { return(false); } List <string> fieldNames = new List <string>(); List <string> fieldValues = new List <string>(); CSFilter whereClause = new CSFilter(); CSParameterCollection parameters = new CSParameterCollection(); foreach (CSSchemaColumn schemaColumn in _schema.Columns) { CSFieldValue fieldValue = _fieldData["#" + schemaColumn.Name]; if (!schemaColumn.IsKey && (fieldValue == null || !fieldValue.IsDirty || fieldValue.SchemaField.ReadOnly)) { continue; } CSParameter parameter = parameters.Add(); parameter.Value = fieldValue.ValueDirect; if (schemaColumn.IsKey) { whereClause = whereClause.And(_schema.DB.QuoteField(schemaColumn.Name) + "=@" + parameter.Name.Substring(1)); } if (fieldValue.IsDirty && !fieldValue.SchemaField.ReadOnly && !schemaColumn.Identity && !schemaColumn.ReadOnly) { fieldNames.Add(schemaColumn.Name); fieldValues.Add("@" + parameter.Name.Substring(1)); } fieldValue.ValueState = CSFieldValueState.Read; } if (whereClause.Expression.Length == 0) { throw new CSException("No key fields"); } if (fieldValues.Count > 0) { string sqlQuery = _schema.DB.BuildUpdateSQL(_schema.TableName, fieldNames.ToArray(), fieldValues.ToArray(), whereClause.Expression); if (_schema.DB.ExecuteNonQuery(sqlQuery, parameters) != 1) { return(false); } } return(true); }
internal bool ReadFields(CSStringCollection columnList, CSStringCollection keyList, List <object> valueList) { List <string> fieldList = new List <string>(); List <string> aliasList = new List <string>(); Dictionary <string, string> fieldAliasMap = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase); CSFilter whereClause = new CSFilter(); CSTable table = new CSTable(_schema); CSParameterCollection parameters = new CSParameterCollection(); foreach (CSSchemaColumn schemaColumn in _schema.Columns) { if (keyList.Contains(schemaColumn.Name)) { CSParameter parameter = parameters.Add(); parameter.Value = valueList[keyList.IndexOf(schemaColumn.Name)]; whereClause = whereClause.And(table.TableAlias + "." + _schema.DB.QuoteField(schemaColumn.Name) + "=" + parameter.Name); _fieldData["#" + schemaColumn.Name].ValueDirect = parameter.Value; _fieldData["#" + schemaColumn.Name].ValueState = CSFieldValueState.Read; } else if (columnList.Contains(schemaColumn.Name)) { string alias = CSNameGenerator.NextFieldAlias; fieldList.Add(table.TableAlias + "." + schemaColumn.Name); aliasList.Add(alias); fieldAliasMap[alias] = schemaColumn.Name; } } /** Build query for prefetch of relations **/ CSJoinList joinList = new CSJoinList(); List <PrefetchField> prefetchFields = GetPrefetchFieldsOne(table, fieldList, aliasList, joinList, null); if (whereClause.Expression.Length == 0) { return(false); } if (fieldList.Count == 0) { return(true); } string sqlQuery = _schema.DB.BuildSelectSQL(table.TableName, table.TableAlias, fieldList.ToArray(), aliasList.ToArray(), joinList.BuildJoinExpressions(), whereClause.Expression, null, 1, 1, true, false); using (CSTransaction csTransaction = new CSTransaction(_schema)) { using (ICSDbReader dataReader = _schema.DB.CreateReader(sqlQuery, parameters)) { if (!dataReader.Read()) { return(false); } FromDataReader(dataReader, fieldAliasMap); foreach (PrefetchField prefetchField in prefetchFields) { ReadRelationToOne(prefetchField.SchemaField, dataReader, prefetchField.AliasMap); } } csTransaction.Commit(); } return(true); }