Executing Commands and StoredProcedures

With Telerik Data Access, you can directly execute data source commands.

In this topic:

The following methods belong to the OpenAccessContext type:

ExecuteNonQuery - executes a SQL statement using the context owned connection.
ExecuteQuery<T> - executes a query directly against the data source and returns a sequence of typed results.
ExecuteScalar<T> - executes the query and returns the first column of the first row in the result set returned by the query. All other columns are ignored.
ExecuteStoredProcedure<T> - executes the specified stored procedure and materializes the result of instances of T.
Translate<T> - translates a DbDataReader object that contains rows of data to objects of the requested persistent type.

Executing SQL Queries and Materializing the Result to a Collection of Persistent Types

To execute a query directly against the data source, you need to use the ExecuteQuery<T> method. This example shows you how to execute a query and materialize the result to a collection of persistent types.

Materialized persistent types are automatically attached to the context.

using (FluentModel dbContext = new FluentModel())
{
   string sqlQueryString = "select * from Categories";
   IEnumerable<Category> categories = dbContext.ExecuteQuery<Category>(sqlQueryString);               
}

Using dbContext As New FluentModel()
 Dim sqlQueryString As String = "select * from Categories"
 Dim categories As IEnumerable(Of Category) = dbContext.ExecuteQuery(Of Category)(sqlQueryString)
End Using

Executing SQL Queries and Materializing the Result to a Collection of NonPersistent Types

Sometimes the shape of the result returned by the query may not map to any of the available persistent types in your 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 Data Access 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:

string sqlQueryString = "select CategoryID, CategoryName, ImageFileName + CategoryName " + 
                        "as FormattedName from Categories";

Dim sqlQueryString As String = "select CategoryID, CategoryName, ImageFileName + CategoryName " &
                               "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.

public class CustomCategory
{
   public int CategoryID { get; set; }
   public string CategoryName { get; set; }
   public string FormattedName { get; set; }
}

Public Class CustomCategory
 Public Property CategoryID() As Integer
 Public Property CategoryName() As String
 Public Property FormattedName() As String
End Class

And after having the custom CLR type, you could materialize the result by using the ExecuteQuery<T> method.

using (FluentModel dbContext = new FluentModel())
{
   string sqlQueryString = "select CategoryID, CategoryName, ImageFileName + CategoryName " + 
                           "as FormattedName from Categories";
   IEnumerable<CustomCategory> categories = dbContext.ExecuteQuery<CustomCategory>(sqlQueryString);
}

Using dbContext As New FluentModel()
 Dim sqlQueryString As String = "select CategoryID, CategoryName, ImageFileName + CategoryName as " &
                                "FormattedName from Categories"
 Dim categories As IEnumerable(Of CustomCategory) = dbContext _
     .ExecuteQuery(Of CustomCategory)(sqlQueryString)
End Using

Executing Parameterized SQL Queries

The following code sample demonstrates how to construct a query string with one parameter. It creates an OACommand instance. Finally, the query is executed and the result is materialized to a collection of Car objects.

using (FluentModel dbContext = new FluentModel())
{
   string sqlQueryString = "select * from Cars where CategoryID = @CategoryId";

   OAParameter oaParameter = new OAParameter();
   oaParameter.ParameterName = "CategoryId";
   oaParameter.DbType = System.Data.DbType.Int32;
   oaParameter.Value = 1;

   IEnumerable<Car> cars = dbContext.ExecuteQuery<Car>(sqlQueryString, oaParameter);
}

Using dbContext As New FluentModel()
 Dim sqlQueryString As String = "select * from Cars where CategoryID = @CategoryId"

 Dim _oaParameter As New OAParameter()
 _oaParameter.ParameterName = "CategoryId"
 _oaParameter.DbType = System.Data.DbType.Int32
 _oaParameter.Value = 1

 Dim cars As IEnumerable(Of Car) = dbContext.ExecuteQuery(Of Car)(sqlQueryString, _oaParameter)
End Using

Executing StoredProcedures

In this section, you will see how to use the OpenAccessContext to execute a stored procedure. First, you need to create and initialize a new OAParameter object. The name of the parameter should match the name of the corresponding stored procedure parameter in the database. If your stored procedure doesn't accept any parameters, you don't need to create OAParameter objects. In this example one input parameter is used. By default, the Direction of the OAParameter is Input. In case, you have an output parameter, you need to change the Direction of the parameter to Output. When the parameter is set up, the second step is to call the ExecuteQuery<T> method of the OpenAccessContext instance, and get a materialized list of Car objects out of the context.

using (FluentModel dbContext = new FluentModel())
{
   OAParameter carMakeParameter = new OAParameter();
   carMakeParameter.ParameterName = "@CarMake";
   carMakeParameter.Value = "Honda";
   IEnumerable<Car> cars = dbContext.ExecuteQuery<Car>("GetCarsByMake", 
       System.Data.CommandType.StoredProcedure, carMakeParameter);
}

Using dbContext As New FluentModel()
 Dim carMakeParameter As New OAParameter()
 carMakeParameter.ParameterName = "@CarMake"
 carMakeParameter.Value = "Honda"
 Dim cars As IEnumerable(Of Car) = dbContext.ExecuteQuery(Of Car)("GetCarsByMake", 
     System.Data.CommandType.StoredProcedure, carMakeParameter)
End Using

The GetCarsByMake stored procedure is shown below:

CREATE PROCEDURE GetCarsByMake
@CarMake varchar(50)
AS
BEGIN
SELECT *
FROM Cars
WHERE Make = @CarMake
END
GO

Executing Non-Query SQL Statements

This section provides an example of how to execute non-query SQL statements (e.g. update, delete, insert operations). Note that you need to invoke the SaveChanges method of the context. Telerik Data Access cannot verify what are the exact changes performed by this query and roles back the changes when the context is disposed. So, in order to commit to the database the changes done by a query, you need to explicitly call SaveChanges.

using ( FluentModel dbContext = new FluentModel() )
{
   string sqlQueryString = "INSERT INTO Categories(CategoryName, ImageFileName) " + 
                           "VALUES(@CategoryName, @ImageFileName)";

   OAParameter categoryName = new OAParameter();
   categoryName.ParameterName = "@CategoryName";
   categoryName.Value = "NewCategory";

   OAParameter imageFileName = new OAParameter();
   imageFileName.ParameterName = "@ImageFileName";
   imageFileName.Value = "NewImageFileName";

   int rowsAffected = dbContext.ExecuteNonQuery( sqlQueryString, categoryName, imageFileName );

   dbContext.SaveChanges();

   Console.WriteLine( "Rows affected: {0}", rowsAffected );
}

Using dbContext As New FluentModel()
    Dim sqlQueryString As String = "INSERT INTO Categories(CategoryName, ImageFileName) " &
                                   "VALUES(@CategoryName, @ImageFileName)"

    Dim categoryName As New OAParameter()
    categoryName.ParameterName = "@CategoryName"
    categoryName.Value = "NewCategory"

    Dim imageFileName As New OAParameter()
    imageFileName.ParameterName = "@ImageFileName"
    imageFileName.Value = "NewImageFileName"

    Dim rowsAffected As Integer = dbContext.ExecuteNonQuery(sqlQueryString, categoryName, 
        imageFileName)

    dbContext.SaveChanges()

    Console.WriteLine("Rows affected: {0}", rowsAffected)
End Using

Executing SQL Queries that Return Scalar Values

The following example demonstrates how to retrieve the average value of the Daily column.

using (FluentModel dbContext = new FluentModel())
{
   string sqlQuery = "Select Avg(Daily) from RentalRates";
   decimal averageRate = dbContext.ExecuteScalar<decimal>(sqlQuery);               
}

Using dbContext As New FluentModel()
 Dim sqlQuery As String = "Select Avg(Daily) from RentalRates"
 Dim averageRate As Decimal = dbContext.ExecuteScalar(Of Decimal)(sqlQuery)
End Using

Translating a DbDataReader to a Collection of Persistent/NonPersistent Types

This section demonstrates how to translate DbDataReader object to persistent/non-persistent types.

using ( FluentModel dbContext = new FluentModel() )
{
   using ( IDbConnection dbConnection = dbContext.Connection )
   {
       using ( IDbCommand command = dbConnection.CreateCommand() )
       {
           command.CommandText = "Select * From Cars where Make = @CarMake";

           IDbDataParameter parameter = command.CreateParameter();
           parameter.ParameterName = "@CarMake";
           parameter.Value = "Honda";

           command.Parameters.Add( parameter );

           using ( IDataReader reader = command.ExecuteReader() )
           {
               IEnumerable<Car> cars = dbContext.Translate<Car>( reader as DbDataReader );
               foreach ( Car car in cars )
               {
                   Console.WriteLine( "Car Model: {0}", car.Model );
                   Console.WriteLine( "-----------------------------------------" );
               }
           }
       }
   }
}

Using dbContext As New FluentModel()
    Using dbConnection As IDbConnection = dbContext.Connection
        Using command As IDbCommand = dbConnection.CreateCommand()
            command.CommandText = "Select * From Cars where Make = @CarMake"

            Dim parameter As IDbDataParameter = command.CreateParameter()
            parameter.ParameterName = "@CarMake"
            parameter.Value = "Honda"

            command.Parameters.Add(parameter)

            Using reader As IDataReader = command.ExecuteReader()
                Dim cars As IEnumerable(Of Car) = dbContext.Translate(Of Car)(TryCast(reader, DbDataReader))
                For Each _car As Car In cars
                    Console.WriteLine("Car Model: {0}", _car.Model)
                    Console.WriteLine("-----------------------------------------")
                Next _car
            End Using
        End Using
    End Using
End Using