Procédure : exécuter une requête qui retourne des résultats RefType (EntityClient)
Cette rubrique fournit un exemple d'exécution d'une commande sur un modèle EDM (Entity Data Model) en utilisant EntityCommand, et de récupération des résultats du RefType en utilisant EntityDataReader.
Pour exécuter le code de cet exemple
Ajoutez le modèle de vente AdventureWorks Sales Model à votre projet et configurez votre projet de façon à utiliser Entity Framework. Pour ce faire, effectuez l'une des opérations suivantes :
Si les outils Entity Data Model sont installés, exécutez la procédure décrite dans Procédure : utiliser l'Assistant Entity Data Model (Entity Framework).
Sinon, exécutez les procédures décrites dans Procédure : configurer manuellement un projet Entity Framework et Procédure : définir manuellement un modèle EDM (Entity Data Model) (Entity Framework).
Dans la page de codes de votre application, ajoutez les instructions using (Imports en Visual Basic) suivantes :
Imports System Imports System.Collections.Generic Imports System.Collections Imports System.Data.Common Imports System.Data Imports System.Data.SqlClient Imports System.Data.EntityClient Imports System.Data.Metadata.Edm Imports System.IO ' Add AdventureWorksModel prepended with the root namespace for the project. 'Imports ProjectName.AdventureWorksModel
using System; using System.Collections.Generic; using System.Collections; using System.Data.Common; using System.Data; using System.IO; using System.Data.SqlClient; using System.Data.EntityClient; using AdventureWorksModel; using System.Data.Metadata.Edm;
Exemple
Pour tester le code, passez la requête suivante comme argument à la fonction ExectueRefTypeQuery
:
"SELECT REF(p) FROM AdventureWorksEntities.Product as p"
La requête ci-dessus retourne les résultats RefType.
Sub ExectueRefTypeQuery(ByVal esqlQuery As String)
If (esqlQuery.Length = 0) Then
Console.WriteLine("The query string is empty.")
Return
End If
Using conn As EntityConnection = New EntityConnection("name=AdventureWorksEntities")
conn.Open()
Try
' Create an EntityCommand.
Using cmd As EntityCommand = conn.CreateCommand()
cmd.CommandText = esqlQuery
' Execute the command.
Using reader As EntityDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
Do While (reader.Read())
RefTypeVisitRecord(CType(reader, IExtendedDataRecord))
Loop
End Using
End Using
Catch ex As Exception
Console.WriteLine(ex.ToString())
End Try
conn.Close()
End Using
End Sub
Sub RefTypeVisitRecord(ByVal record As IExtendedDataRecord)
' For RefType the record contains exactly one field.
Dim fieldIndex As Integer
fieldIndex = 0
' If the field is flagged as DbNull, the shape of the value is undetermined.
' An attempt to get such a value may trigger an exception.
If (record.IsDBNull(fieldIndex) = False) Then
Dim fieldTypeKind As BuiltInTypeKind = record.DataRecordInfo.FieldMetadata(fieldIndex).FieldType.TypeUsage.EdmType.BuiltInTypeKind()
'read only fields that contain PrimitiveType
If (fieldTypeKind = BuiltInTypeKind.RefType) Then
' Ref types are surfaced as EntityKey instances.
' The containing record sees them as atomic.
Dim key As EntityKey = CType(record.GetValue(fieldIndex), EntityKey)
' Get the EntitySet name.
Console.WriteLine("EntitySetName " + key.EntitySetName)
' Get the Name and the Value information of the EntityKey.
Dim keyMember As EntityKeyMember
For Each keyMember In key.EntityKeyValues
Console.WriteLine(" Key Name: " + keyMember.Key)
Console.WriteLine(" Key Value: " + keyMember.Value)
Next
End If
End If
End Sub
static void ExectueRefTypeQuery(string esqlQuery)
{
if (esqlQuery.Length == 0)
{
Console.WriteLine("The query string is empty.");
return;
}
using (EntityConnection conn =
new EntityConnection("name=AdventureWorksEntities"))
{
conn.Open();
try
{
// Create an EntityCommand.
using (EntityCommand cmd = conn.CreateCommand())
{
cmd.CommandText = esqlQuery;
// Execute the command.
using (EntityDataReader rdr =
cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
// Start reading results.
while (rdr.Read())
{
RefTypeVisitRecord(rdr as IExtendedDataRecord);
}
}
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
}
}
static void RefTypeVisitRecord(IExtendedDataRecord record)
{
// For RefType the record contains exactly one field.
int fieldIndex = 0;
// If the field is flagged as DbNull, the shape of the value is undetermined.
// An attempt to get such a value may trigger an exception.
if (record.IsDBNull(fieldIndex) == false)
{
BuiltInTypeKind fieldTypeKind = record.DataRecordInfo.FieldMetadata[fieldIndex].
FieldType.TypeUsage.EdmType.BuiltInTypeKind;
//read only fields that contain PrimitiveType
if (fieldTypeKind == BuiltInTypeKind.RefType)
{
// Ref types are surfaced as EntityKey instances.
// The containing record sees them as atomic.
EntityKey key = record.GetValue(fieldIndex) as EntityKey;
// Get the EntitySet name.
Console.WriteLine("EntitySetName " + key.EntitySetName);
// Get the Name and the Value information of the EntityKey.
foreach (EntityKeyMember keyMember in key.EntityKeyValues)
{
Console.WriteLine(" Key Name: " + keyMember.Key);
Console.WriteLine(" Key Value: " + keyMember.Value);
}
}
}
}