Using Stored Procedures with the ADO.NET Entity Framework

Notes:

• The data provider does not support stored procedures that use a SQL descriptor area (SQLDA) data structure to specify the arguments.
• Make sure that the latest maintenance patches have been applied.

Refer to the DataDirect Connect Series for ADO.NET Reference for more information about using stored procedures.

Using Multiple Result Sets

The DB2 Entity Framework data provider supports multiple result sets and return codes from DB2 stored procedures when parameters are bound as return codes. The stored procedures must take an argument list.

Using Pseudo Stored Procedures

The Connection object includes properties and methods that provide reauthentication and enhanced statistics functionality. The methods and properties are standard in the ADO.NET data provider, but are not available at the ADO.NET Entity Framework layer. Instead, you expose the same functionality through "pseudo" stored procedures.

This approach uses the Entity Data Model (EDM) to achieve results that correspond to the ADO.NET results. This in effect provides entities and functions backed by pseudo stored procedures.

The following table lists the mapping of the DB2Connection properties to the corresponding pseudo stored procedure.

Mapping to Pseudo Stored Procedure

You can create a function mapping in the entity model to invoke the pseudo-stored procedure. Alternatively, applications can use the ObjectContext to create a stored procedure command as shown in the following C# code fragment:

using (MyContext context = new MyContext())
{
   EntityConnection entityConnection = (EntityConnection)context.Connection;
   // The EntityConnection exposes the underlying store connection
   DbConnection storeConnection = entityConnection.StoreConnection;
   DbCommand command = storeConnection.CreateCommand();
   command.CommandText = "DDTek_Connection_EnableStatistics";
   command.CommandType = CommandType.StoredProcedure;
   command.Parameters.Add(new DB2Parameter("cid", 1));
}
 
bool openingConnection = command.Connection.State == ConnectionState.Closed;
if (openingConnection) { command.Connection.Open(); }
int result;
try
{
   result = command.ExecuteNonQuery();
}
finally
{
   if (openingConnection && command.Connection.State == ConnectionState.Open)
   {
      command.Connection.Close();
   }
}