public void Remove(T obj)
{
String sqlCommand;
SqlParameter[] parameters;
sqlCommand = @"DELETE FROM " + RelationshipTableName
+ " WHERE " + ChildKeyColumnName + " = @" + ChildKeyColumnName
+ " AND " + ParentKeyColumnName + " = @" + ParentKeyColumnName;
parameters = new SqlParameter[]
{
new SqlParameter(ParentKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(_parentObject.ActiveRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, _parentObject.Id),
new SqlParameter(ChildKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(ActiveRecord <T> .GetMap().PrimaryKeyColumnMap.Property.PropertyType)
, obj.Id)
};
//Execute Delete Command
ActiveRecordSession.GetSession().DatabaseServices.ExecuteNonQuery(sqlCommand, parameters);
ObjectList.Remove(obj);
_retrivedObjects.Remove(obj.Id);
}
protected override void Retrieve()
{
String sqlQuery;
SqlParameter[] parameters;
sqlQuery = @"SELECT " + SqlBuilderServices.BuildSqlColumnList(ActiveRecord <T> .GetMap().ColumnList(true), ActiveRecord <T> .GetMap().TableName, SqlBuilderServices.ColumnListType.List)
+ " FROM " + ActiveRecord <T> .GetMap().TableName
+ " INNER JOIN " + RelationshipTableName + " ON " + RelationshipTableName + "." + ChildKeyColumnName + " = " + ActiveRecord <T> .GetMap().TableName + "." + ChildKeyColumnName
+ " WHERE " + ParentKeyColumnName + " = @" + ParentKeyColumnName;
parameters = new SqlParameter[]
{
new SqlParameter(ParentKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(_parentObject.ActiveRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, _parentObject.ActiveRecordMap.PrimaryKeyColumnMap.Property.GetValue(_parentObject, null))
};
ObjectList = ActiveRecord <T> .FindManyWithSql(sqlQuery, parameters);
foreach (T obj in ObjectList)
{
_retrivedObjects.Add(obj.Id);
}
_loadStatus = LoadStatuses.Loaded;
}
public static T Find(long id)
{
if (id == null)
{
return(null);
}
ActiveRecordMap activeRecordMap = ActiveRecord <T> .GetMap();
string query = @"SELECT " + SqlBuilderServices.BuildSqlColumnList(activeRecordMap.ColumnList(true), activeRecordMap.TableName, SqlBuilderServices.ColumnListType.List)
+ " FROM " + activeRecordMap.TableName
+ " WHERE " + activeRecordMap.PrimaryKeyColumnName + " = @" + activeRecordMap.PrimaryKeyColumnName;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter(activeRecordMap.PrimaryKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(activeRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, id)
};
IDataReader dataReader = _activeRecordSession.DatabaseServices.ExecuteDataReader(query, parameters);
return(ObjectRetrievalServices.RetrieveObject <T>(dataReader, activeRecordMap));
}
public void Update()
{
string command = @"UPDATE " + _activeRecordMap.TableName
+ " SET " + SqlBuilderServices.BuildSqlColumnList(_activeRecordMap.ColumnList(false), _activeRecordMap.TableName, SqlBuilderServices.ColumnListType.UpdateParameterizedList)
+ " WHERE " + _activeRecordMap.PrimaryKeyColumnName + " = @" + _activeRecordMap.PrimaryKeyColumnName;
SqlParameter[] parameters = new SqlParameter[_activeRecordMap.ColumnMappings.Count + 1];
//Set Column Parameters
int parameterIndex = 0;
foreach (ColumnMap columnMap in _activeRecordMap.ColumnMappings.Values)
{
parameters[parameterIndex] = new SqlParameter(columnMap.ColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(columnMap.Property.PropertyType)
, columnMap.Property.GetValue(this, null));
if (parameters[parameterIndex].Type == DbType.DateTime && (DateTime)parameters[parameterIndex].Value == DateTime.MinValue)
{
parameters[parameterIndex].Value = DateTime.Parse("1/1/1900");
}
parameterIndex++;
}
//Set Primary Key Parameter
parameters[parameterIndex] = new SqlParameter(_activeRecordMap.PrimaryKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(_activeRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, _activeRecordMap.PrimaryKeyColumnMap.Property.GetValue(this, null));
_activeRecordSession.DatabaseServices.ExecuteNonQuery(command, parameters);
}
public void Insert()
{
string command = @"INSERT INTO " + _activeRecordMap.TableName
+ "(" + SqlBuilderServices.BuildSqlColumnList(_activeRecordMap.ColumnList(false), _activeRecordMap.TableName, SqlBuilderServices.ColumnListType.List) + ")"
+ " VALUES(" + SqlBuilderServices.BuildSqlColumnList(_activeRecordMap.ColumnList(false), _activeRecordMap.TableName, SqlBuilderServices.ColumnListType.ParameterizedList) + ")";
SqlParameter[] parameters = new SqlParameter[_activeRecordMap.ColumnMappings.Count];
//Set Column Parameters
int parameterIndex = 0;
foreach (ColumnMap columnMap in _activeRecordMap.ColumnMappings.Values)
{
SqlParameter parameter = new SqlParameter(columnMap.ColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(columnMap.Property.PropertyType)
, columnMap.Property.GetValue(this, null));
if (parameter.Type == DbType.DateTime && (DateTime)parameter.Value == DateTime.MinValue)
{
parameter.Value = DateTime.Parse("1/1/1900");
}
parameters[parameterIndex] = parameter;
parameterIndex++;
}
//Execute Insert Command
_activeRecordSession.DatabaseServices.ExecuteNonQuery(command, parameters);
//Retrive Primary Key
string query = @"SELECT MAX(" + _activeRecordMap.PrimaryKeyColumnName + ")"
+ " FROM " + _activeRecordMap.TableName
+ " WHERE " + SqlBuilderServices.BuildSqlColumnList(_activeRecordMap.ColumnList(false), _activeRecordMap.TableName, SqlBuilderServices.ColumnListType.WhereParameterizedList);
_activeRecordMap.PrimaryKeyColumnMap.Property.SetValue(this, _activeRecordSession.DatabaseServices.ExecuteScalar(query, parameters), null);
}
public static List <T> FindAll()
{
ActiveRecordMap activeRecordMap = ActiveRecord <T> .GetMap();
string query = @"SELECT " + SqlBuilderServices.BuildSqlColumnList(activeRecordMap.ColumnList(true), activeRecordMap.TableName, SqlBuilderServices.ColumnListType.List)
+ " FROM " + activeRecordMap.TableName;
IDataReader dataReader = _activeRecordSession.DatabaseServices.ExecuteDataReader(query);
return(ObjectRetrievalServices.RetrieveObjects <T>(dataReader, activeRecordMap));
}
public static T FindFirstWhere(String where, SqlParameter[] parameters)
{
ActiveRecordMap activeRecordMap = ActiveRecord <T> .GetMap();
string query = @"SELECT " + SqlBuilderServices.BuildSqlColumnList(activeRecordMap.ColumnList(true), activeRecordMap.TableName, SqlBuilderServices.ColumnListType.List)
+ " FROM " + activeRecordMap.TableName
+ " WHERE " + where;
IDataReader dataReader = _activeRecordSession.DatabaseServices.ExecuteDataReader(query, parameters);
return(ObjectRetrievalServices.RetrieveObject <T>(dataReader, activeRecordMap));
}
public void Delete()
{
string command = @"DELETE FROM " + _activeRecordMap.TableName
+ " WHERE " + _activeRecordMap.PrimaryKeyColumnName + " = @" + _activeRecordMap.PrimaryKeyColumnName;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter(_activeRecordMap.PrimaryKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(_activeRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, _activeRecordMap.PrimaryKeyColumnMap.Property.GetValue(this, null))
};
_activeRecordSession.DatabaseServices.ExecuteNonQuery(command, parameters);
this._objectStatus = ObjectStatus.Deleted;
}
protected override void Retrieve()
{
String where;
SqlParameter[] parameters;
where = ChildKeyColumnName + " = @" + ParentKeyColumnName;
parameters = new SqlParameter[]
{
new SqlParameter(ParentKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(ActiveRecord <T> .GetMap().PrimaryKeyColumnMap.Property.PropertyType)
, _parentObject.ActiveRecordMap.ColumnMappings[ParentKeyColumnName].Property.GetValue(_parentObject, null))
};
ObjectList = ActiveRecord <T> .FindManyWhere(where, parameters);
_loadStatus = LoadStatuses.Loaded;
}
public void Refresh()
{
ActiveRecordMap activeRecordMap = ActiveRecord <T> .GetMap();
string query = @"SELECT " + SqlBuilderServices.BuildSqlColumnList(activeRecordMap.ColumnList(true), activeRecordMap.TableName, SqlBuilderServices.ColumnListType.List)
+ " FROM " + activeRecordMap.TableName
+ " WHERE " + activeRecordMap.PrimaryKeyColumnName + " = @" + activeRecordMap.PrimaryKeyColumnName;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter(activeRecordMap.PrimaryKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(activeRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, this.Id)
};
IDataReader dataReader = _activeRecordSession.DatabaseServices.ExecuteDataReader(query, parameters);
ObjectRetrievalServices.RetrieveObject <T>(dataReader, activeRecordMap, true);
}
public override void Save()
{
String where;
SqlParameter[] parameters;
foreach (T obj in ObjectList)
{
//Set each child's foreign key to the parent's primary key
if (obj.Version < _parentObject.Version)
{
obj.Save();
}
//Insert relationship row for newly added/created child objects
if (!_retrivedObjects.Contains(obj.Id))
{
string command = @"INSERT INTO " + RelationshipTableName
+ "(" + ParentKeyColumnName + "," + ChildKeyColumnName + ")"
+ " VALUES(@" + ParentKeyColumnName + ", @" + ChildKeyColumnName + ")";
parameters = new SqlParameter[]
{
new SqlParameter(ParentKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(_parentObject.ActiveRecordMap.PrimaryKeyColumnMap.Property.PropertyType)
, _parentObject.Id),
new SqlParameter(ChildKeyColumnName
, SqlBuilderServices.GetDBTypeFromSystemType(ActiveRecord <T> .GetMap().PrimaryKeyColumnMap.Property.PropertyType)
, obj.Id)
};
//Execute Insert Command
ActiveRecordSession.GetSession().DatabaseServices.ExecuteNonQuery(command, parameters);
_retrivedObjects.Add(obj.Id);
}
}
}
