Besides querying an OpenAccess Domain Model by using standard LINQ expressions, there is one more way you can query the domain model, and that is through the OpenAccess ADO API. Using LINQ queries is simple and easy. However, if you are concerned about the performance and comfortable writing ADO.NET like code, then the OpenAccess ADO API is perfect for you.
In this topic, you will learn:
 |
The OpenAccess ADO API resides in the Telerik.OpenAccess.Data.Common namespace. |
Basic Command Usage
This section provides an example of how to execute a simple "select" command against a Telerik OpenAccess Domain Model by using the OACommand class, and examine the result by using the DbDataReader class.
Suppose, you have a Categories table like the one shown in the figure below:
And you have created a domain model that looks like:
You want to execute one or more SQL select statements directly against the Categories table. Although you have a Category entity in your domain model, it is not required. You simply need some model (an OpenAccessContext instance) that is connected to the database against you want to execute the OpenAccess commands. To execute one or more SQL statements, you need to follow this pattern:
- Create a new instance of the OpenAccessContext.
- Get an existing instance of the OAConnection class, by using the OpenAccessContext.Connection property.
- Create a string with the SQL select statement.
- Create a new instance of the OACommand class, by using the OAConnection.CreateCommand method.
- Set the OACommand.CommandText property.
- Execute the command by calling the OACommand.ExecuteReader method.
- The final step is to consume the result.
The listing below transforms the pattern into code.
| C# |
 Copy Code |
|
using System;
using System.Data.Common;
using Telerik.OpenAccess.Data.Common;
using System.Collections;
using System.Collections.Generic;
namespace GettingStartedAdoApi
{
class Program
{
// Basic Command Usage.
private static void Sample1()
{
// 1. Create a new instance of the OpenAccessContext.
using (EntitiesModel dbContext = new EntitiesModel())
{
// 2. Retrieve the OAConnection instance.
OAConnection oaConnection = dbContext.Connection;
// 3. Create a string containing the sql query.
string sqlQueryString = "select * from Categories";
// 4. Create a new instance of the OACommand class.
using (OACommand oaCommand = oaConnection.CreateCommand())
{
// 5. Set the CommandText property.
oaCommand.CommandText = sqlQueryString;
// 6. Execute the command.
using (DbDataReader reader = oaCommand.ExecuteReader())
{
// 7. Examine the result.
while (reader.Read())
{
Console.WriteLine(
string.Format("{0}: {1}",
reader["Id"], reader["Name"]));
}
}
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Imports Telerik.OpenAccess.Data.Common
Imports System.Data.Common
Module Module1
Private Sub Sample1()
Using dbContext As New EntitiesModel()
Dim _oaConnection As OAConnection = dbContext.Connection
Dim sqlQueryString As String = "select * from Categories"
Using _oaCommand As OACommand = _oaConnection.CreateCommand()
_oaCommand.CommandText = sqlQueryString
Using reader As DbDataReader = _oaCommand.ExecuteReader()
Do While reader.Read()
Console.WriteLine(String.Format("{0}: {1}", reader("CategoryID"), reader("CategoryName")))
Loop
End Using
End Using
End Using
End Sub
End Module |
|
It is a good practice to enclose the DbDataReader and OACommand instances in using statements. |
How to Materialize a DbDataReader to a Collection of Persistent Types
In the previous example, after you obtained a DbDataReader instance, you just iterated through the records in the result set. In some scenarios, it will be useful to translate (materialize) the DbDataReader object to a collection of persistent types. You could achieve this by using the generic OpenAccessContext.Translate method. The following example demonstrates how to materialize the DbDataReader instance to a collection of Category objects.
| C# |
Copy Code |
|
using System;
using System.Data.Common;
using Telerik.OpenAccess.Data.Common;
using System.Collections;
using System.Collections.Generic;
namespace GettingStartedAdoApi
{
class Program
{
// Materialize DbDataReader to a collection of persistent types
private static void Sample2()
{
// 1. Create a new instance of the OpenAccessContext.
using (EntitiesModel dbContext = new EntitiesModel())
{
// 2. Retrieve the OAConnection instance.
OAConnection oaConnection = dbContext.Connection;
// 3. Create a string containing the sql query.
string sqlQueryString = "select * from Categories";
// 4. Create a new instance of the OACommand class.
using (OACommand oaCommand = oaConnection.CreateCommand())
{
// 5. Set the CommandText property.
oaCommand.CommandText = sqlQueryString;
// 6. Execute the command.
using (DbDataReader reader = oaCommand.ExecuteReader())
{
// 7. Materialize the reader.
IEnumerable<Category> categories = dbContext.Translate<Category>(reader);
}
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Imports Telerik.OpenAccess.Data.Common
Imports System.Data.Common
Module Module1
Private Sub Sample2()
Using dbContext As New EntitiesModel()
Dim _oaConnection As OAConnection = dbContext.Connection
Dim sqlQueryString As String = "select * from Categories"
Using _oaCommand As OACommand = _oaConnection.CreateCommand()
_oaCommand.CommandText = sqlQueryString
Using reader As DbDataReader = _oaCommand.ExecuteReader()
Dim categories As IEnumerable(Of Category) = dbContext.Translate(Of Category)(reader)
End Using
End Using
End Using
End Sub
End Module |
 |
Materialized persistent types are automatically attached to the context. |
Materialized persistent types are automatically attached to the context. Namely, if you modify the materialized Category objects and call the SaveChanges method of the context, then all changes will be committed to the database.
| C# |
Copy Code |
|
// 7. Materialize the reader.
IEnumerable<Category> categories = dbContext.Translate<Category>(reader);
Category category = categories.FirstOrDefault();
category.Name = "new";
dbContext.SaveChanges(); |
| VB.NET |
Copy Code |
|
Dim categories As IEnumerable(Of Category) = dbContext.Translate(Of Category)(reader)
Dim _category As Category = categories.FirstOrDefault()
_category.Name = "new"
dbContext.SaveChanges()
|
How to Materialize a DbDataReader to a Collection of Non-Persistent Types
Sometimes the shape of the result returned by the query may not map to any of the available persistent types in your domain model. In this case, you would need to define a custom CLR type that can hold the result. The requirements for this CLR type are:
- It should provide a parameterless constructor, so that Telerik OpenAccess ORM can create instances of that type.
- The property names should match the column names that are returned by the query. The case is ignored.
- The property types should be compatible with the column types.
Consider the following SQL query:
| C# |
Copy Code |
|
string sqlQueryString = "select Id, Name, Name + Description as FormattedName from Categories"; |
| VB.NET |
Copy Code |
|
Dim sqlQueryString As String = "select Id, Name, Name + Description as FormattedName from Categories" |
In this scenario, you will still be able to materialize the DbDataReader to a collection of Category objects. However, you will lose the information from the FormattedName column. You may ask yourself what will happen if a corresponding property for a column is not found. The value for this column is simply ignored.
The solution is to create a custom CLR type that match the result returned by the query.
| C# |
Copy Code |
|
public class CustomCategory
{
public int Id { get; set; }
public string Name { get; set; }
public string FormattedName { get; set; }
} |
| VB.NET |
Copy Code |
|
Public Class CustomCategory
Public Property Id() As Integer
Public Property Name() As String
Public Property FormattedName() As String
End Class |
And after having the custom CLR type, you could materialize the DbDataReader in the well-known manner.
| C# |
Copy Code |
|
using System;
using System.Data.Common;
using Telerik.OpenAccess.Data.Common;
using System.Collections;
using System.Collections.Generic;
namespace GettingStartedAdoApi
{
class Program
{
// Materialize DbDataReader to a collection of non-persistent types
private static void Sample3()
{
// 1. Create a new instance of the OpenAccessContext.
using (EntitiesModel dbContext = new EntitiesModel())
{
// 2. Retrieve the OAConnection instance.
OAConnection oaConnection = dbContext.Connection;
// 3. Create a string containing the sql query.
string sqlQueryString = "select Id, Name, Name + Description as FormattedName from Categories";
// 4. Create a new instance of the OACommand class.
using (OACommand oaCommand = oaConnection.CreateCommand())
{
// 5. Set the CommandText property.
oaCommand.CommandText = sqlQueryString;
// 6. Execute the command.
using (DbDataReader reader = oaCommand.ExecuteReader())
{
// 7. Materialize the reader.
IEnumerable<CustomCategory> categories = dbContext.Translate<CustomCategory>(reader);
}
}
}
}
}
} |
| VB.NET |
Copy Code |
|
Private Sub Sample3()
Using dbContext As New EntitiesModel()
Dim _oaConnection As OAConnection = dbContext.Connection
Dim sqlQueryString As String = "select Id, Name, Name + Description as FormattedName from Categories"
Using _oaCommand As OACommand = _oaConnection.CreateCommand()
_oaCommand.CommandText = sqlQueryString
Using reader As DbDataReader = _oaCommand.ExecuteReader()
Dim categories As IEnumerable(Of CustomCategory) = dbContext.Translate(Of CustomCategory)(reader)
End Using
End Using
End Using
End Sub
|
How to Execute SQL Statements by Using the Context API Approach
In the last example in this topic, you will learn how to execute SQL statements by using the Context API Approach. Instead of creating an OACommand and obtaining a DbDataReader after that, you could use directly the generic OpenAccessContext.ExecuteQuery method.
| C# |
Copy Code |
|
using System;
using System.Data.Common;
using Telerik.OpenAccess.Data.Common;
using System.Collections;
using System.Collections.Generic;
namespace GettingStartedAdoApi
{
class Program
{
// Using the ExecuteQuery<T> method.
private static void Sample4()
{
// 1. Create a new instance of the OpenAccessContext.
using (EntitiesModel dbContext = new EntitiesModel())
{
// 2. Create a string containing the sql query.
string sqlQueryString = "select * from Categories";
// 3. Execute the query and materialize the result.
IEnumerable<Category> categories = dbContext.ExecuteQuery<Category>(sqlQueryString);
}
}
}
} |
| VB.NET |
Copy Code |
|
Private Shared Sub Sample4()
Using dbContext As New EntitiesModel()
Dim sqlQueryString As String = "select * from Categories"
Dim categories As IEnumerable(Of Category) = dbContext.ExecuteQuery(Of Category)(sqlQueryString)
End Using
End Sub
|
The topics in this section show how to perform programming task with the OpenAccess ADO API:
- Executing Stored Procedures
How to: Execute SQL Queries that Return Scalar Values
How to: Execute a Parameterized SQL Query
How to: Execute Non-Query SQL Statements
How to: Materialize Objects
How to: Translate DbDataReader