The goal of constructing queries using the QueryCriteria methods is based on the assumption that we want to return data to view and/or manipulate within our application. The ORM.NET data layer provides a great deal of flexibility in how a developer wishes to work with data returned from the database. The following examples show some of the most common techniques used to display and view this data.
During the ORM.NET data layer generation process a pair of separate methods are created for each generated table object. The generated methods Get[Object] and Get[Object]Collection wrap the ADO.NET DataRow object and provide the means by which developers assign and retrieve sets of data retrieved through the DataManger object from the database.
NOTE: ORM.NET does not actually generate any SQL statements or connect to the database until a Get[Object] or Get[Object]Collection method is called.
DataManager.Get[Object] returns a single row of data from a query. If you know that the result of your query will only return one row this is the most efficient method to use. In cases where the query produces more than one row only the first row of data will be returned.
Object object = DataManager.Get[Object]
DataManger.Get[Object]Collection returns a collection of rows from a generated query and is appropriate for queries where multiple rows of information will be returned. The returned collection object wraps the resulting data rows and provides developers a great deal of functionality in how they can view the data within the application.
ObjectCollection object = DataManager.Get[Object]Collection
Below is the code example from the Retrieving Data Section. After constructing a query using the QueryCriteria.And method the next statement creates a Student object and then assigns this object the data values returned by the query using the Get[Object] method - in this case DataManager.GetStudent().
Remember that only one RowSet of information will be assigned to the local student object. If the query does return more than one row of data only the first row will be available.
C#
// Create a new DataManager object with database connection string
DataManager dm = new DataManager(Config.Dsn);
// Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tom",MatchType.Exact);
// Copy the resulting dataset from DataManger to a new Student object
Student student = dm.GetStudent();
// Display the retrieved information
Console.WriteLine(student.FirstName + " " + student.LastName);
Visual Basic.NET
' Create a new DataManager object with database connection string
Dim dm As New DataManager(Config.Dsn)
'Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tom",MatchType.Exact)
// Copy the resulting dataset from DataManger to a new Student object
Dim student As Student = dm.GetStudent()
// Display the retrieved information
Console.WriteLine(student.FirstName + " " + student.LastName)
In this example the Student object is considered the "root" object because it is the first object that JoinPath will use to navigate the object hierarchy. Therefore, the DataSet which is returned will be wrapped as a Student object and assumes that the GetStudent() method will copy an object reference to an instance of the Student object shown above.
For example, the following will not work:
//INCORRECT - Will generate an error
Contact contact = dm.GetStudent();
Nor are the following statements allowed
// Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Contact.Columns.Address1,"123 Main Street",MatchType.Exact);
// INCORRECT - Cannot create and copy the root object Contact from above into a Student object!!
Student student = dm.GetStudent();
Refer to the Retrieve Data section which describes Using the JoinPath Object To Create Complex Queries for more information about root objects and the JoinPath object.
The following example is similar to the one above except that more than one row will be returned by the query. In this case, the DataManager.Get[Object]Collection should be used to return a collection of wrapped DataRow objects.
C#
'Create a new DataManager object with DSN information
DataManager dm = new DataManager(Config.Dsn);
// Clear any previous queries
dm.QueryCriteria.Clear();
// Create the query
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, "Jones", MatchType.Exact);
//Copy the result dataset from DataManager as a Collection of Student objects instead of a single object
StudentCollection students = dm.GetStudentCollection();
// Loop through each student in the collection.
foreach (Student s in students)
Console.WriteLine("FullName: {0} {1}", s.FirstName, s.LastName);
Visual Basic.NET
// Create a new DataManager object with DSN information
Dim dm As New DataManager(Config.Dsn)
' Clear any previous queries
dm.QueryCriteria.Clear()
' Create the query
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, "Jones", MatchType.Exact)
//Copy the result dataset from DataManager as a Collection of Student objects instead of a single object
Dim students As StudentCollection = dm.GetStudentCollection()
'Loop through each student in the collection.
For Each (Student s In students)
Console.WriteLine("FullName: {0} {1}", s.FirstName, s.LastName)
Next
You are not required to specify a QueryCriteria before retrieving an ORM.NET collection. For example, if you wanted to retrieve all of the Student records from the table you would write the example above as:
// Create a new DataManager object with DSN information
DataManager dm = new DataManager(Config.Dsn);
// Return ALL Students from the Student Table. Copy the result dataset from DataManager as a Collection of Student objects instead of // a single object
StudentCollection students = dm.GetStudentCollection();
// Loop through each student in the collection.
foreach (Student s in students)
Console.WriteLine("FullName: {0} {1}", s.FirstName, s.LastName);
Optionally you can write the GetStudentCollection method as:
// .. Sort by the FirstName column in descending order
StudentCollection students = dm.GetStudentCollection().SortByFirstName(SortDirection.Descending);
// now loop through each student in the sorted collection as shown above..
You can output the generated SQL statement that ORM creates by calling DataManager.LastQueryText after calling a Get[Object] or Get[Object]Collection method. The generated SQL statements will be output as a String.
StudentCollection students = dm.GetStudentCollection();
Console.WriteLine("OUTPUT SQL: " + dm.LastQueryText);
The example above will output the generated SQL statement to retrieve all students to the console window.
This statement will return the collection of data objects sorted by the FirstName column in Descending order - SortDirection.Descending. One of the benefits in working with ORM.NET’s Collections objects is the flexibility developers have in how they sort, search, or filter the cached set of records within the collection. The following methods are also generated by ORM.NET for every exposed Property (table column) within each table object.
FindBy[Property](value) |
Will search for and return a single rowset object based on the value passed in for the Property ( i.e. FirstName). Requires a single argument which will be the same data type of the column. Optionally, the value can also be passed in as a string type. |
FilterBy[Property](value) |
Will filter the current collection of records matching the value passed in for that column. Requires a single argument which must be the same type as the Property type. Optionally, the value can also be passed in as a string type. |
SortBy[Property](SortDirection) |
Will return a collection of rows sorted by the Property name. Requires a SortDirection enumeration be passed in. The enumeration values are SortDirection.Ascending, SortDirection.Descending |
In addition, for each of the generated Collection methods a set of overridden methods are also created which allow you to use an optional CompareType enumeration explained below, or to pass in a generic string type on each of the methods above.
C#
// Retrieve a single object from the cached collection where the first //name is ‘Karen'
Student student = dm.GetStudentCollection().FindByFirstName("Karen");
Console.WriteLine("Student Name :" + student.FirstName + " " + student.LastName);
// or you can access the same information directly
Console.WriteLine("Test: " + students.FindByFirstName("Karen").LastName);
Visual Basic.NET
'Retrieve a single object from the cached collection where the first //name is ‘Karen'
Dim student As Student = dm.GetStudentCollection().FindByFirstName("Karen")
Console.WriteLine("Student Name :" + student.FirstName + " " + student.LastName);
' or you can access the same information directly
Console.WriteLine("Test: " + students.FindByFirstName("Karen").LastName)
//Filter the CourseCollection to find courses created on 11/06/2002
CourseCollection = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));
//Filter the CourseCollection to find courses created on 11/06/2002
CourseCollection oldcourses = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));
StudentCollection students = dm.GetStudentCollection().SortByFirstName(SortDirection.Ascending);
The CompareType enumeration is an optional parameter which can be passed in the FindBy[PropertyName] and FilterBy[PropertyName] collection methods to allow for pattern matching within the cached collection of data.
Collection.FindBy[Property](value,CompareType)
For example, to return only the records from the students collection of all Students who entered the system after 11/06/2002
you can write the following:
StudentCollection students = dm.GetStudentCollection(); // return ALL students from the table
// Use FilterBy to return only students which have a DateCreated value greater than 11/06/2002
StudentCollection newStudents =
students.FilterByDateCreated(DateTime.Parse("11/06/2002"),CompareType.Greater);
The possible CompareType enumerations are:
CompareType.Contains
|
Will search the database column that Property maps to and return all rows where value is contained anywhere within the column's value. |
CompareType.EndsWith
|
Will search the database column that Property maps to and return all rows where value is at the end of the column's value. |
CompareType.Exact
|
Default - Will search the database column that Property maps to and return all rows where the column's value exactly matches value. |
CompareType.Greater
|
Will search the database column that Property maps to and return all rows where value is greater than the column's value. |
CompareType.GreaterOrEqual
|
Will search the database column that Property maps to and return all rows where value is greater than or equal to the column's value. |
CompareType.Lesser
|
Will search the database column that Property maps to and return all rows where value is less than the column's value. |
CompareType.LesserOrEqual
|
Will search the database column that Property maps to and return all rows where value is less than or equal to the column's value. |
CompareType.Not
|
Will search the database column that Property maps to and return all rows where value does NOT exactly match the column's value. |
CompareType.NotContain
|
Will search the database column that Property maps to and return all rows where value does NOT exactly match the column's value. |
CompareType.StartsWith
|
Will search the database column that Property maps to and return all rows where value is at the beginning of the column's value. |
So far the row set of data returned by the DataManager.Get[Object] and Get[Object]Collection have only returned information from the Root object (table) - in this case the Student table object. However, in many cases an application developer is interested in retrieving not only the information from the immediate table but also the related information from multiple Child and Parent tables. For example, querying for a particular Student may also involve returning the related Contact information for the Student as well as the Enrollments that the Student has signed up for.
In addition, the developer may also want to return Schedule information (Parent of Enrollment) as well as Course information (Parent of Schedule) for a given Student object.
ORM.NET allows developers to control how deep the related set of data from multiple joined tables should be returned from a query without requiring the developer to construct complex join statements or to make multiple calls to the database.
In addition, any updates or inserts made to the root table and/or any related tables will also be performed as a transaction to assure that all data modifications occur synchronously and that data integrity is maintained.
In order to facilitate these powerful features ORM.NET generates a FetchPath enumeration object based on the exposed database schema as an optional parameter which can be passed in to either Get[Object] or Get[Object]Collection methods.
The FetchPath object is similar in implementation to the JoinPath object in that both implement a hierarchical view of the underlying database schema which can be navigated as a set of related objects. However, their usage is different. Whereas JoinPath allows a developer to navigate the object hierarchy in order to create queries, FetchPath is used to control the level of related row set data that is returned within a given data object or collection of data objects.
By default if the FetchPatch object is not passed into Get[Object] or Get[Object]Collection only the row information from the immediate Root table is returned. In this case the root object is Student, because it is the first object specified in the JoinPath statement at the beginning of the code block.
// Create the query to retrieve the desired information
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Tom",MatchType.Exact);
// Copy the resulting dataset from DataManger to a new Student object
Student student = dm.GetStudent();
The same result is achieved by explicitly passing in the FetchPath object
Student student = dm.GetStudent(FetchPath.Student); // same as GetStudent();
If you are interested in returning not only the Student information but also wanted to view/modify the related Contact information (Parent of Student) for Student change the FetchPath statement to the following:
Student student = dm.GetStudent(FetchPath.Student.Contact);
If you are interested in both Contact information (Parent of Student) and Enrollment information (Child of Student) for Student use the All keyword to return all the Children and Parent table information for the object.
Student student = dm.GetStudent(FetchPath.Student.All);
Using the All statement on the Contact object will return both Student and Teacher (both children of Contact) object information.
Contact contact = dm.GetContact(FetchPath.Contact.All);
Use .AllChildren to return only the Children of a Root object.
// will return Student's Child - Enrollment (the only Child of Student)
Student student = dm.GetStudent(FetchPath.Student.AllChildren);
Use .AllParents to return all Parents of a Root Object
// will return Schedules Parents - Course, Teacher, Room
Schedule schedule = dm.GetSchedule(FetchPath.Student.AllParents);
Use .All to return immediate Parent and Children of the Root objects
If you want to return all the classes that a Student has enrolled in based on the Student LastName the FetchPath object will need to map to that relation as shown below.
C#
//Show all the classes that the student has enrolled in
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, “Jennings”, MatchType.Exact);
student = dm.GetStudent(FetchPath.Student.Enrollment.Schedule.All);
Console.WriteLine("{0} {1}'s schedule:", student.FirstName, student.LastName);
foreach(Enrollment e in student.Enrollments)
{
if (e.Schedule != null && e.Schedule.Course != null) //Check to ensure objects have data
{
Console.WriteLine(e.Schedule.Course.ClassName + " is in building " +
e.Schedule.Room.Building + ", Room " + e.Schedule.Room.RoomNumber);
}
}
Visual Basic.NET
'Show all the classes that the student has enrolled in
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, "Jennings", MatchType.Exact)
Dim student As Student = dm.GetStudent(FetchPath.Student.Enrollment.Schedule.All)
Console.WriteLine("{0} {1}'s schedule:", student.FirstName, student.LastName)
Dim e As Enrollment
For Each e In student.Enrollments
If Not e.Schedule Is Nothing And Not e.Schedule.Course Is Nothing Then 'Check to ensure objects have data
Console.WriteLine(e.Schedule.Course.ClassName + " is in building " + _
e.Schedule.Room.Building + ", Room " + e.Schedule.Room.RoomNumber)
End If
Next
By specifying the All keyword on FetchPath every Parent (and Child) record related to the Schedule object and related to the Student object by the QueryCriteria.And statement will be returned. For this example Course and Room object data will also be returned within the DataSet and DataRow because they are Parents of the Schedule object.
If you are also interested in the Student’s Contact information in addition to the Course, Room and Schedule information you can add an additional FetchPath statement to the method.
student = dm.GetStudent(FetchPath.Student.Contact,FetchPath.Student.Enrollment.Schedule.All);
The same example will work if the result of the QueryCriteria.And returned many Students who may be in many Classes. The FetchPath syntax remains the same but instead of a Get[Object] method invoke the Get[Object]Collection object instead.
student = dm.GetStudentCollection(FetchPath.Student.Contact,FetchPath.Student.Enrollment.Schedule.All);
You can combine more than one FetchPath statement to retrieve the specific information you require. Separate FetchPath statements using a comma.
This example will retrieve Student's Contact information - FetchPath.Student.Contact - as well as the related Room, Course and Teacher information - FetchPath.Student.Enrollment.Schedule.AllParents - that the Student is enrolled in.
C#
student = dm.GetStudent(FetchPath.Student.Contact, FetchPath.Student.Enrollment.Schedule.AllParents);
Console.WriteLine(student.Contact.City);
foreach(Enrollment e in student.Enrollments)
{
Console.WriteLine(e.Schedule.Teacher.LastName);
Console.WriteLine(e.Schedule.Course.ClassName);
Console.WriteLine(e.Schedule.Room.Building);
}
Visual Basic.NET
Dim student As Student = dm.GetStudent(FetchPath.Student.Contact, _ FetchPath.Student.Enrollment.Schedule.AllParents)
Console.WriteLine(student.Contact.City)
Dim e As Enrollment
For Each e in student.Enrollments
Console.WriteLine(e.Schedule.Teacher.LastName)
Console.WriteLine(e.Schedule.Course.ClassName)
Console.WriteLine(e.Schedule.Room.Building)
Next
NOTE: Because of the potential performance problems with returning a large amount of related data within a
Dataset, specify the appropriate FetchPath statement to return only the data that you know you will
need for a given object.