tchimev posted on Thursday, September 25, 2008
I would like to use CSLA and LinqToSql in my solutions.
I want to use LinqToSQl functionality instead of stored procedures or any sql statements to select/insert/update/delete data from the Database.
I want to simplify the code for the DataPortal methods so I can use it in snippets or abstract classes.
I would like to share my solution with you.
Can you tell me is there a better solution to achieve the goal?
Fetch data:
in my design I construct and execute Linq queries in master business objects only. All child business objects populate themselves with Linq object passed from the parent object.
example of BO.Persons master business list object:
DB.Person is Linq object
private IQueryable<DB.Person> ConstructQuery(Criteria criteria, DB.DefaultDataContext db)
{
IQueryable<DB.Person> result = null;
result = from p in db.Persons
where p.FirstName.StartsWith(criteria.Name)
select p;
return result;
}
private void DataPortal_Fetch(Criteria criteria)
{
RaiseListChangedEvents = false;
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
foreach (var item in ConstructQuery(criteria, db.DataContext))
{
this.Add(Person.GetPerson(item));
}
}
RaiseListChangedEvents = true;
}
example of BO.Person child business object:
//recieve Linq object as parameter
private void Child_Fetch(DB.Person person)
{
this.MapFromLinq(person);
}
private void MapFromLinq(DB.Person person)
{
LoadProperty<string>(_firstName, person.FirstName);
LoadProperty<string>(_middleName, person.MiddleName);
}
Insert/Update data:
In situations when I have master business base with child business base objects I have to create Linq object from master business base which has to know its foreign keys.
I prefer to use foreign key linq objects instead of ID's for foreign keys.
I implemented it like this:
1. In DataPortal_Insert() method I call MapToLinq() method which creates master object to Linq object.
2. Then create Linq objects from children.
3. After that I call DataPortal.UpdateChild(childBB, dbChild) and pass the created Linq object as parameter, so the business child object can save itself to Database.
4. Set foreign key objects to master linq object.
5. Attach master linq object ot DataContext
6. Reload master business object from the saved master Linq object.
public DB.Employment MapToLinq()
{
DB.Employment dbEmployment = new DB.Employment()
{
/* load Linq Employment properties */
HireDate = ReadProperty<SmartDate, DateTime>(_hireDate)
};
//read Person(child)
BO.Person boPerson = ReadProperty<Person>(_employee);
//map child business object Person to Linq object
DB.Person dbPerson = boPerson.MapToLinq();
//insert/delete/update business object Person
DataPortal.UpdateChild(boPerson, dbPerson);
//map saved Linq table Person to his parent table Contract
dbContract.Person = dbPerson;
return dbContract;
}
private void DataPortal_Insert()
{
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
//map master and update child
DB.Employment dbEmployment = this.MapToLinq();
//mark for insertion
db.DataContext.Employments.InsertOnSubmit(dbEmployment);
//save to DataBase
db.DataContext.SubmitChanges();
//reload business object properties from saved data
this.MapFromLinq(dbEmployment);
}
}
private void DataPortal_Update()
{
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
//map master and update child
DB.Employment dbEmployment = this.MapToLinq();
//attach Employment for update
db.DataContext.Employments.Attach(dbEmployment, this.IsSelfDirty);
//save to DataBase
db.DataContext.SubmitChanges();
//reload business object properties from saved data
this.MapFromLinq(dbEmployment);
}
}
example of BO.Person child business object:
private void MapFromLinq(DB.Person dbPerson)
{
LoadProperty<string>(_firstName, dbPerson.FirstName);
LoadProperty<string>(_middleName, dbPerson.MiddleName);
}
public DB.Person MapToLinq()
{
//check if business object Person isValid
if (this.IsValid)
{
return new DB.Person()
{
FirstName = ReadProperty<string>(_firstName),
MiddleName = ReadProperty<string>(_middleName)
};
}
return null;
}
private void Child_Insert(DB.Person dbPerson)
{
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
//mark parameter dbPerson(Linq object) for insert
db.DataContext.Persons.InsertOnSubmit(dbPerson);
//save to DataBase
db.DataContext.SubmitChanges();
//reload business object from saved data
this.MapFromLinq(dbPerson);
}
}
private void Child_Update(DB.Person dbPerson)
{
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
//map parameter dbPerson(Linq to sql table) for update
db.DataContext.Persons.Attach(dbPerson, this.IsSelfDirty);
//save to DataBase
db.DataContext.SubmitChanges();
//reload business object from saved data
this.MapFromLinq(dbPerson);
}
}
Delete Data:
in DataPortal_Delete(Criteria) method master business object fetches the object by criteria then calls delete() and save()
on the fetched object.
After that DataPortal_DeleteSelf() is called which maps the master business object to Linq object through MapToLinq() method.
And also maps and Updates any child business objects.
Then commit changes to Db.
private void DataPortal_DeleteSelf()
{
using (var db = ContextManager<DB.DefaultDataContext>.GetManager(Properties.Settings.Default.DefaultConnectionString, false))
{
//map master and update child
DB.Contract dbContract = this.MapToLinq();
//attach for deletion
db.DataContext.Contracts.Attach(dbContract);
db.DataContext.Contracts.DeleteOnSubmit(dbContract);
//save to Db
db.DataContext.SubmitChanges();
}
}
private void DataPortal_Delete(Criteria criteria)
{
BO.Contract obj = Contract.GetContractById(criteria.Id);
obj.Delete();
obj.Save();
}
tchimev:
I would like to use CSLA and LinqToSql in my solutions.