This section describes how to update data using the ORM.NET Data Layer and use of the DataManager object's CommitAll method.
Once data is retrieved and assigned to either a single object or a collection of objects through the Get[Object] and Get[Object]Collection methods you are then able to view, modify, insert, or delete data against the set of object information and then commit that data back to the underlying data store.
This is accomplished through the DataManager.CommitAll() method which improves the functionality of the ADO.NET DataSet and DataAdapter objects by handling all required updates, deletes, and inserts as a one set of batch statements. In addition, the CommitAll() method wraps the batch of statements using the BEGIN TRANSACTION and END TRANSACTION statements so if there are any errors within the series of SQL operations CommitAll will safely roll back the entire set. CommitAll() also ensures that all object relationships and constraints are enforced to ensure data integrity is kept intact.
The following example shows how to perform a simple update on the Teacher object and then commit those changes to the underlying database table Teacher using the DataManager.CommitAll() method.
C#
DataManager dm = new DataManager(Config.Dsn);
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom",MatchType.Exact);
Teacher teacher = dm.GetTeacher(FetchPath.Teacher);
teacher.FirstName = "Thomas";
teacher.LastName = "Franklin";
dm.CommitAll(); // Will perform required update operation as a transaction
Visual Basic.NET
Dim dm As New DataManager(Config.Dsn)
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom",MatchType.Exact)
Dim teacher As Teacher = dm.GetTeacher(FetchPath.Teacher)
teacher.FirstName = "Thomas"
teacher.LastName = "Franklin"
dm.CommitAll() 'Will perform required update operation as a transaction
To perform this operation the CommitAll() method generated the following Transact SQL statement
BEGIN TRANSACTION
UPDATE[Teacher]
SET [LastName] = 'Franklin',
[FirstName] = 'Thomas'
WHERE ID = 10008
IF @@ERROR <> 0 BEGIN
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
Another example may require you to not only update the Teacher object but also to update information in the Parent object Contact.
C#
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom",MatchType.Exact);
Teacher teacher = dm.GetTeacher(FetchPath.Teacher.Contact);
if (teacher != null) // Check to ensure that the query returned data
{
// Update Teacher information
teacher.FirstName = "Thomas";
teacher.LastName = "Franklin";
// Update Contact info for Teacher
teacher.Contact.Address1 = "123 Main Street";
teacher.Contact.City = "Oakland";
teacher.Contact.State = "CA";
teacher.Contact.Country = "US";
dm.CommitAll(); // Will perform SQL update operations as a transaction
}
Visual Basic.NET
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom",MatchType.Exact)
Dim teacher As Teacher = dm.GetTeacher(FetchPath.Teacher.Contact) 'return Teacher and related Contact info
If (Not teacher Is Nothing) Then 'Check to ensure that the query returned data
'Update Teacher information
teacher.FirstName = "Thomas"
teacher.LastName = "Franklin"
'Update Contact info for Teacher
teacher.Contact.Address1 = "123 Main Street"
teacher.Contact.City = "Oakland"
teacher.Contact.State = "CA"
teacher.Contact.Country = "US"
dm.CommitAll() 'Will perform SQL update operations as a transaction
End If
In either case, ORM.NET will ensure that the Parent record for Contact is updated with the correct information and maintain the foreign key constraints defined within the schema.
If a valid Contact record already exists for a teacher you can also assign an object reference to the Teacher object. For example, the following example retrieves a Contact object where the Address1 Property is ‘123 Main Street’ and updates the Teacher object to reference this information.
C#
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName, "Tom" , MatchType.Exact);
Teacher teacher = dm.GetTeacher(FetchPath.Teacher.Contact);
// find the correct contact information
dm.QueryCriteria.And(JoinPath.Contact.Columns.Address1, "123 Main Street",MatchType.Exact);
Contact = dm.GetContact(FetchPath.Contact);
// Update Teacher information
teacher.FirstName = "Thomas";
teacher.LastName = "Franklin";
// Assign the existing Contact object to Teacher
teacher.Contact = contact;
dm.CommitAll(); // Will perform SQL update operations transactionally
Visual Basic.NET
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName, "Tom" , MatchType.Exact)
Dim teacher As Teacher = dm.GetTeacher(FetchPath.Teacher.Contact)
' Find the correct contact information
dm.QueryCriteria.And(JoinPath.Contact.Columns.Address1, "123 Main Street",MatchType.Exact)
Contact = dm.GetContact(FetchPath.Contact)
'Update Teacher information
teacher.FirstName = "Thomas"
teacher.LastName = "Franklin"
'Assign the existing Contact object to Teacher
teacher.Contact = contact
dm.CommitAll(); 'Will perform SQL update operations transactionally
The effect of this within the database is that Teacher’s foreign key FKContactId will be updated with the ID of the Contact record when CommitAll() is called.
If during the course of developing your application you want to view the generated SQL statement) without actually committing the changes to the database call DataManager.CommitAllDebug() instead.
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom", MatchType.Exact);
Teacher teacher = dm.GetTeacher(FetchPath.Teacher);
teacher.FirstName = "Thomas";
teacher.LastName = "Franklin";
dm.CommitAllDebug(); // Will NOT perform any changes but will generate the SQL statement(s) that would be
// used in the Output window
You can view the generated SQL Statements in the Output window within Visual Studio.NET .
You can also view the generated SQL statement by calling DataManager.LastCommitText immediately after calling DataManager.CommitAll(). LastCommitText will return the complete SQL output generated as a String. You can use this to log SQL calls for auditing purposes.
DataManager dm = new DataManager(Config.Dsn);
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Teacher.Columns.FirstName,"Tom",MatchType.Exact);
Teacher teacher = dm.GetTeacher(FetchPath.Teacher);
teacher.FirstName = "Thomas";
teacher.LastName = "Franklin";
dm.CommitAll();
Console.WriteLine("OUTPUT SQL " + dm.LastCommitText); // ouput generated SQL statement to console
Also see DataManager.LastQueryText to output the SQL generated during retrieval queries.