View and Display data using ORM.NET

 

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.

DataManager.Get[Object] and  .Get[Object]Collection Methods

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

 

Using DataManger.Get[Object]

 

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.

 

Using DataManager.Get[Object]Collection

       

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..

 

 

 

DataManager.LastQueryText

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.

 

Collection methods generated for each column Property

 

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.

 

Using the FindBy[PropertyName]

 

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)

 

 

Using the FilterBy[PropertyName]

//Filter the CourseCollection to find courses created on 11/06/2002

CourseCollection = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));

 

Using the FilterBy[PropertyName] with optional CompareType enumerator

 

//Filter the CourseCollection to find courses created on 11/06/2002

 

CourseCollection oldcourses = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));

 

 

Using the SortBy[PropertyName](SortDirection)

 

StudentCollection students = dm.GetStudentCollection().SortByFirstName(SortDirection.Ascending);

 

CompareType Enumeration Definitions

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);

 

Collection.FindBy[Property](value,CompareType)  and

Collection.FilterBy[Property](value,CompareType)

 

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.

 

 

FetchPath Object - Retrieving a Dataset from related tables

 

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.

 

Examples using FetchPath

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);

 

Combining Multiple FetchPath statements

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.