Data retrieval through ORM.NET consists of building a set of object based queries using the DataManager object which in turn generates the correct SQL statement(s) at run-time to return the appropriate set of data wrapped within a single object or collection of objects. This section explains some of the many different types of queries which can be composed in order to retrieve a desired set of data from the database.
All data access retrieval and modification is handled by the DataManager object. This object type extends and improves the functionality provided by the ADO.NET DataSet object to allow developers a more intuitive way to view, retrieve, sort, and filter data.
In order to perform any data operations within your application you must first create a new instance of the DataManager object as shown below:
static void Main(string[] args)
{
//
// TODO: Add code to start application here
//
DataManager dm = new DataManager(Config.Dsn);
The DataManager object constructor requires a string parameter which contains the DSN information for the database ORM.NET generated the data layer from. By default ORM.NET generates a valid DSN connection string and saves this information within the App.Config file during the generation process.
For more information see the section Customizing the Config Object.
Please note that you do not have to use the Config object to pass in the DSN connection string. Any valid string type can be used. For example:
DataManager dm = New DataManager("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=sspi")
In addition, The DataManager constructor will also accept a an ADO.NET Connection object or Transaction object.
Once the DataManager has been instantiated and assuming the connection string is valid you can begin viewing and modifying information within your database. Some of the many useful functions available through the DataManager object will be explored throughout the code examples provided below.
Most data access queries are composed using the QueryCriteria methods of the DataManager object. Based on the information from the QueryCriteria statements ORM.NET generates the correct SQL statement(s) at run-time and fills the appropriate table objects with data. This allows developers to easily generate complex data queries without depending on the arcane syntax of Transact SQL.
QueryCriteria.And and QueryCriteria.Or are used in a similar fashion to the SQL statements AND and OR to generate SQL queries within the WHERE clause.
Study the following example which retrieves and then displays a record from the Student object where the FirstName property contains the value Tom.
Example 1
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 - see Displaying Data for more info
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 - see Displaying Data for more info
Dim student As Student = dm.GetStudent()
' Display the retrieved information
Console.WriteLine(student.FirstName + student.LastName)
The equivalent Transact SQL code to generate this basic query might look like
SELECT * FROM STUDENT WHERE FirstName = Tom
QueryCriteria.Or is equivalent to the T-SQL operator OR. Although QueryCriteria.Or can be used as the first statement for a query as shown below for the sake of consistency and readability it is recommend beginning all new queries using the QueryCriteria.And method
Using QueryCriteria.Or method instead of QueryCriteria.And method
// Create the query to retrieve the desired information
dm.QueryCriteria.Or(JoinPath.Student.Columns.FirstName,"Tom",MatchType.Exact);
// continue coding..
The QueryCriteria.And and QueryCriteria.Or methods require 3 arguments. The most important parameter is the JoinPath enumeration object explained below.
Argument |
Definition |
|
JoinPath |
The JoinPath object is a specialized enumeration which exposes all of the database tables, columns and relationships within an object based hierarchy. By taking advantage of VS.NETs Intellisense functionality developers construct queries by navigating the object/table hierarchy to select the appropriate columns and tables which will be used by the ORM.NET Query Builder. The Query Builder uses the JoinPath path information to generate the appropriate data retrieval SQL statements at run-time. See below on how JoinPath must be used within both methods. |
|
Value |
The value to search the database column(s) for. This value can be of any valid type depending on the mapped .NET to SQL value. Examples using different SQL data types are explained in Working with SQL data types below. |
|
MatchType (optional) |
An enumeration which defines how the supplied Value argument should be searched. There are 15 different MatchTypes some of which map to the Transact SQL LIKE keyword. See the section MatchType Examples for more detailed information and usage. |
Combining QueryCriteria.And and QueryCriteria.Or methods to create queries
Statements which are constructed using the QueryCriteria.And or QueryCriteria.Or methods can be joined together to create more complex queries. One of the most compelling benefits of the ORM.NET object layer is the ease with which complex queries can be created compared with writing the equivalent SQL statements.
The following shows two separate ways you can construct the same queries which involve more than one QueryCriteria statement.
Example 2:
C#
// Combine .And and .Or QueryCriteria methods
dm.QueryCriteria.Clear(); // Removes any previous queries from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Bill",MatchType.Exact)
.Or(JoinPath.Student.Columns.LastName,"Clinton",MatchType.Exact);
// will generate the same query as writing them on separate lines.
dm.QueryCriteria.Clear(); // Removes the previous query from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Bill", MatchType.Exact);
dm.QueryCriteria.Or(JoinPath.Student.Columns.LastName,"Clinton",MatchType.Exact);
Visual Basic.NET
Dim dm As New DataManager(Config.Dsn)
'Combine .And and .Or QueryCriteria methods
dm.QueryCriteria.Clear() 'Removes any previous queries from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName, "Bill", MatchType.Exact).Or( _
JoinPath.Student.Columns.LastName, "Clinton", MatchType.Exact)
'Will generate the same query as writing them on separate lines.
dm.QueryCriteria.Clear() 'Removes the previous query from memory
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName, "Bill", MatchType.Exact)
dm.QueryCriteria.Or(JoinPath.Student.Columns.LastName, "Clinton", MatchType.Exact)
Both statements search the Student object for any rows where the FirstName property = Bill or the LastName property= Clinton.
The equivalent Transact SQL statement this would produce might be:
SELECT * FROM Student
WHERE FirstName = 'Bill' OR
LastName = 'Clinton'
MatchType.Exact Parameter
The MatchType enumeration is an optional parameter on both QueryCriteria.And and QueryCriteria.Or methods which will default to compare the values - "Bill" and "Clinton" as MatchType.Exact. Therefore the query statements will return the same results without explicitly passing in the MatchType enumeration. For example,
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,"Bill")
.Or(JoinPath.Student.Columns.LastName,"Clinton");
MatchType enumerations are explained in more detail below under the MatchType Examples section.
Internally the QueryCriteria methods store each connected and/or separate method statement into an array which is then parsed by the ORM.NET Query Builder to construct the necessary Transact SQL statement(s) and retrieve the correct DataSet.
Therefore, in order in order to begin a new query or set of queries it is important to clear out the elements of any previous QueryCriteria statements used within the scope of a DataManager object. This is accomplished by calling the DataManager.QueryCriteria.Clear() method.
dm.QueryCriteria.Clear(); //Removes any previous queries from memory
Be sure to call QueryCriteria.Clear() before starting new queries to ensure that the Query Builder can correctly form the SQL statements.
C#
DataManager dm = new DataManger(Config.Dsn);
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Smith");
StudentCollection students = dm.GetStudentCollection();
// work with the students data
dm.QueryCriteria.Clear(); // BE SURE TO CALL .Clear() or the Query Builder will try to add the
// previous QueryCriteria statement to next QueryCriteria statement!
dm.QueryCriteria.And(JoinPath.Contact.Columns.City,"Boulder");
Contact contact = dm.GetContact();
// work with contacts data
Visual Basic.NET
Dim dm as new DataManager(Config.Dsn)
dm.QueryCriteria.Clear()
cm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Smith")
Dim students as StudentCollection = dm.GetStudentCollection()
dm.QueryCriteria.Clear() // BE SURE TO CALL .Clear() or the Query Builder will try to add the
// previous QueryCriteria statement to next QueryCriteria statement!
dm.QueryCriteria.And(JoinPath.Contact.Columns.City,"Boulder")
Dim contact as Contact = dm.GetContact()
As mentioned above the QueryCriteria.And and QueryCriteria.Or methods require a JoinPath enumeration object as their first argument. The JoinPath enumeration object is a powerful feature of ORM.NET which when combined with the ability of joining multiple QueryCriteria statements makes creating complex SQL join queries very easy.
Before explaining how to use the JoinPath object an important point to understand with all the examples shown so far is that the Student object has been considered the root object of the query because it is the first object specified within the JoinPath statement. The root object provides the ORM.NET query constructor the context it needs in order to intelligently determine how to create efficient queries. In addition, the root object determines how the result set is constructed and returned. This becomes important when learning how to view and modify data in the sections that follow.
For example, if a QueryCriteria.And method's JoinPath enumeration begins with JoinPath.Contact. the returning dataset will be returned based on information from the Contact table. If the root object of the JoinPath is Student - QuerCriteria.And(JoinPath.Student.,value,MatchType) the dataset will be returned as an object based on information from the Student table.
The easiest way to understand how JoinPath is used and the importance of the Root object is to look at some examples.
The following uses the JoinPath enumeration object to easily navigate through the relational hierarchy of the database to create a query which asks to return a list of all students who are enrolled in the course Algorithms 100.
C#
//Student is the Root object
dm.QueryCriteria.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName,
""Algorithms 100"",MatchType.Exact);
Student student = dm.GetStudent();
Visual Basic.NET
'Student is the Root object
dm.QueryCriteria.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName,_
"Algorithms 100",MatchType.Exact)
Dim student As Student = dm.GetStudent()
In this case the JoinPath enumeration navigated from Student - the Root object - to the Enrollment object (Child of Student) to the Schedule object (Parent of Enrollment) to the Course object (Parent of Schedule) to search the ClassName property of Course. Using the auto-generated JoinPath object ORM.NET automatically joins the relationships between the objects without any of the guess work or complexity of Transact SQL join statements.
In addition, because the Student object is the root of the JoinPath the dataset that is returned will be based on the underlying Student table information.
Therefore, even though you are interested in information about Courses the following syntax is incorrect.
//INCORRECT - Student is the root object // WILL NOT COMPILE
dm.QueryCriteria.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName, "Calculus I", MatchType.Exact);
// !!!!!! - Student is the root object not Course !!!
Course course = dm.GetCourse();
The correct way to retrieve Course information is to write the query statement so that Course is the first object in JoinPath. For Example,
//CORRECT - Course is the root object
dm.QueryCriteria.And(JoinPath.Course.Columns.ClassName,"Calculus I",MatchType.Exact);
// Return a Course object
Course course = dm.GetCourse();
Another example might search for a list of students where the Contact object property City ="Boulder" and the Student object property LastName = "Jennings". In this case, Student must be Root object of the JoinPath enumeration on both .And statements.
Example 3:
C#
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings").And(JoinPath.Student.Contact.Columns.City,"Boulder");
Student student = dm.GetStudent();
Visual Basic.NET
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, "Jennings").And _
(JoinPath.Student.Contact.Columns.City, "Boulder")
Dim student As Student = dm.GetStudent()
The equivalent SQL statement might be:
Select *
FROM Student s, Contact c
WHERE s.FKContactId = c.ID and
s.LastName = Jennings and
c.City = Boulder
A more complex query might ask to list Students with a LastName of Jennings, who live in Boulder and are enrolled in History 101. The equivalent SQL statement would require numerous SQL Join statements to accomplish this. However, ORM.NET's object model makes this a very simple statement to build.
Example 4:
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings",MatchType.Exact)
.And(JoinPath.Student.Contact.Columns.City,"Boulder",MatchType.Exact)
.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName,
"History 101", MatchType.Exact);
Remember that the exact same query can be constructed the following way.
Using separate statement lines
Without passing in the optional MatchType enumeration - MatchType.Exact - which is the default.
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings");
dm.QueryCriteria.And(JoinPath.Student.Contact.Columns.City,"Boulder");
dm.QueryCriteria.And(JoinPath.Student.Enrollment.Schedule.Course.Columns.ClassName,"History 101");
Once the appropriate DataManager Get[Object] method is called the statements will be combined into one SQL query.
Student student = dm.GetStudent(); // will construct all QueryCriteria statements as one SQL Query
Note that in both examples the Root object must be Student in order to logically connect the query. The following example will not return valid results because the query builder will not be able to determine the correct context from which to join the separate statements when the Get[Object] or Get[Object]Collection.
// INCORRECT!!!! You must use the same Root object within related QueryCriteria statements
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings",MatchType.Exact)
.And(JoinPath.Contact.Columns.City,"Boulder",MatchType.Exact).And(JoinPath.Course.Columns.ClassName, "History 101",MatchType.Exact);
IMPORTANT: Tables that have two or more primary keys defined as JoinTables. JoinTables can be used to create queries, however, these tables cannot be used as the Root object of a query.
In some cases, you will need to perform more complex queries using grouped sub-expressions. ORM.NET allows you to create these queries using CriteriaGroups.
For example, if you were interested in searching for records in the Student table where the column FirstName = 'Bill' and the column LastName = 'Williams' OR the Contact table column City = 'Detroit' and the PostalCode = 87323.
The equivalent SQL syntax might be similar to:
SELECT *
FROM Student s, Contact c
WHERE s.FKCOntactId = c.ID AND
s.FirstName = 'Bill' AND
s.LastName = 'Williams' OR
(c.City = 'Detriot' AND c.PostalCode = '87323')
Using ORM.NET the following query would be constructed as follows:
C#
// Create a CriteriaGroup
DataManager.CriteriaGroup group1 = dm.QueryCriteria.NewGroup();
// Specify the criteria to add for this group
group1.And( JoinPath.Student.Columns.FirstName, "Bill");
group1.And( JoinPath.Student.Columns.LastName, "Williams");
// Create another group
DataManagerBase.CriteriaGroup group2 = dm.QueryCriteria.NewGroup();
// Create another group
group2.And( JoinPath.Student.Contact.Columns.City, "Detriot");
group2.And( JoinPath.Student.Contact.Columns.PostalCode,"87323");
//OR both groups together as the final criteria to create a single query
dm.QueryCriteria.Or(group1).Or(group2);
Visual Basic.NET
' Create a CriteriaGroup
Dim group1 As DataManager.CriteriaGroup = dm.QueryCriteria.NewGroup()
' Specify the criteria to add for this group
group1.And(JoinPath.Student.Columns.FirstName, "Bill")
group1.And(JoinPath.Student.Columns.LastName, "Williams")
' Create another group
Dim group2 As DataManager.CriteriaGroup = dm.QueryCriteria.NewGroup()
'Specify the criteria for this group as well
group2.And(JoinPath.Student.Contact.Columns.City, "Detroit")
group2.And(JoinPath.Student.Contact.Columns.PostalCode, "87323")
'OR both groups together as the final criteria to create a single query
dm.QueryCriteria.Or(group1).Or(group2)
In all of the queries constructed by the QueryCriteria methods so far the only MatchType enumeration used has been MatchType.Exact. However, the MatchType enumeration implements other types which can be used to find data within a query. The MatchType enumeration is similar to the LIKE keyword and determines how the Value argument will be searched against a specific column in the database.
If no MatchType enumeration is passed in to QueryCriteria.And or QueryCriteria.Or methods the default MatchType enumeration - MatchType.Exact will be assumed.
Therefore , the following two statements are identical:
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings",MatchType.Exact);
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,"Jennings"); //will use MatchType.Exact default
The different MatchType enumerations are listed below. The equivalent Transact SQL wildcard syntax is also provided:
MatchType |
SQL equivalent |
Definition |
|
MatchType.Exact
|
= |
Default - Return rows where the specified JoinPath column values exactly match the supplied QueryCriteria Value argument |
|
MatchType.Partial |
%value% |
Return rows where the specified JoinPath column values which contain the supplied QueryCriteria Value argument anywhere within the value. |
|
MatchType.StartsWith |
value% |
Return rows where the specified JoinPath column values which begin with the supplied QueryCriteria Value argument |
|
MatchType.EndsWith |
%value |
Return rows where the specified JoinPath column values which end with the supplied QueryCriteria Value argument |
|
MatchType.Lesser |
< |
Return rows where the specified JoinPath column values are less than the supplied QueryCriteria Value argument |
|
MatchType.Greater |
> |
Return rows where the specified JoinPath column values are greater than the supplied QueryCriteria Value argument |
|
MatchType.GreaterThanOrEqual |
>= |
Return rows where the specified JoinPath column values are greater than or equal to the supplied QueryCriteria Value argument |
|
MatchType.LessThanOrEqual |
<= |
Return rows where the specified JoinPath column values are less than or equal to the supplied QueryCriteria Value argument |
|
MatchType.Like |
LIKE [] [^] _ % * |
Returns rows which match the literal T-SQL LIKE statement to match the column values. Any valid SQL LIKE wildcard character can be used. i.e. 'abc[_]d%' |
|
MatchType.IsNull |
IS NULL |
Returns rows where the specified JoinPath column values are Null |
|
MatchType.IsNotNull |
IS NOT NULL
|
Returns rows where the specified JoinPath column values are not null |
|
MatchType.Not |
<> or 'NOT' |
Returns rows where the specified JoinPath column values are not equal to the Value argument |
|
MatchType.NotLike |
NOT LIKE [] [^] _ % *
|
Returns rows which do NOT match the literal T-SQL LIKE statement to match the column values. Any valid SQL LIKE wildcard character can be used. i.e. 'abc[_]d%' |
|
MatchType.NotIn |
NOT IN |
Return any rows where the specified JoinPath column values are contained in the Array of values passed in by the supplied QueryCriteria Value argument. |
|
MatchType.In |
WHERE Table.ColumnName IN (a,b,c) |
Return any rows where the specified JoinPath column values are contained in the Array of values passed in by the supplied QueryCriteria Value argument. |
The following query searches for any rows in the Student table where FirstName
contains oma and the LastName column ends with non.
C#
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,oma,MatchType.Partial);
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,non, MatchType.EndsWith);
Visual Basic.NET
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Student.Columns.FirstName,oma,MatchType.Partial)
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,non, MatchType.EndsWith)
The next example returns any rooms which are the 2nd floor or higher
C#
dm.QueryCriteria.Clear();
dm.QueryCriteria.And(JoinPath.Room.Columns.Floor,2, MatchType.GreaterOrEqual);
Visual Basic.NET
dm.QueryCriteria.Clear()
dm.QueryCriteria.And(JoinPath.Room.Columns.Floor,2, MatchType.GreaterOrEqual)
This example shows how to use the MatchType.In and MatchType.NotIn enumeration to return any values where the LastName is either Jennings and/or Williams.
C#
string[] arrLastNames = {"Jennings","Williams"};
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,arrLastNames,MatchType.In);
string[] arrLastNames = {"Jennings","Williams"};
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName,arrLastNames,MatchType.NotIn);
Visual Basic.NET
Dim arrLastNames() As String = {"Jennings", "Williams"}
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, arrLastNames, MatchType.In)
Dim arrLastNames() As String = {"Jennings", "Williams"}
dm.QueryCriteria.And(JoinPath.Student.Columns.LastName, arrLastNames, MatchType.NotIn)
If none of the provided MatchType enumerations will return the desired results use the MatchType.Like enumeration to pass in a valid T-SQL LIKE statement. This example will return any Teachers with a Last Name that ends with "arsen" and begins with the letters 'c' through 'p'. The same query is used to show an example using MatchType.NotLike.
C#
dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.Like);
dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.NotLike);
Visual Basic.NET
dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.Like)
dm.QueryCriteria.And(JoinPath.Teacher.Columns.LastName,"[c-p]arsen",MatchType.NotLike)
Refer to the SQL Server Books Online documentation for more information on the Transact SQL Like keyword and syntax.
C#
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null, MatchType.IsNull);
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null, MatchType.IsNotNull);
Visual Basic.NET
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, vbNull, MatchType.IsNull)
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, vbNull, MatchType.IsNotNull)
The same query will be executed using the following syntax:
// Enter "" instead of null
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, "", MatchType.IsNotNull);
// same query without explicitly passing MatchType.IsNull or .NotNull
dm.QueryCriteria.And(JoinPath.Teacher.Columns.Status, null);