How to: Execute a Parameterized Entity SQL Query Using EntityCommand (EntityClient)

This topic provides an example of how to execute Entity SQL query with parameters by using EntityCommand.

To run the code in this example

1. Add the AdventureWorks Sales Model to your project and configure your project to use the Entity Framework. To do this, do one of the following:

   • If you have the Entity Dada Model tools installed, complete the procedure in How to: Use the Entity Data Model Wizard (Entity Framework).

   • Otherwise, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

2. In the code page for your application, add the following using statements (Imports in Visual Basic):

   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;
   

Example

The following example shows how to construct a query string with two parameters. It then creates an EntityCommand, adds two parameters to the EntityParameter collection of that EntityCommand, and iterates through the collection of Contact items.

Using conn As EntityConnection = New EntityConnection("name=AdventureWorksEntities")
    conn.Open()

    ' Create a query that takes two parameters.
    Dim esqlQuery As String = "SELECT VALUE Contact FROM AdventureWorksEntities.Contact " & _
        "AS Contact WHERE Contact.LastName = @ln AND " & _
        "Contact.FirstName = @fn"

    Try
        Using cmd As EntityCommand = New EntityCommand(esqlQuery, conn)
            ' Create two parameters and add them to 
            ' the EntityCommand's Parameters collection 
            Dim param1 As New EntityParameter
            param1.ParameterName = "ln"
            param1.Value = "Adams"
            Dim param2 As New EntityParameter
            param2.ParameterName = "fn"
            param2.Value = "Frances"
            cmd.Parameters.Add(param1)
            cmd.Parameters.Add(param2)

            Using rdr As DbDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
                ' Iterate through the collection of Contact items.
                Do While rdr.Read
                    Console.WriteLine(rdr.Item("FirstName"))
                    Console.WriteLine(rdr.Item("LastName"))
                Loop
            End Using
        End Using
    Catch ex As EntityException
        Console.WriteLine(ex.ToString())
    End Try
    conn.Close()
End Using

using (EntityConnection conn =
    new EntityConnection("name=AdventureWorksEntities"))
{
    conn.Open();
    // Create a query that takes two parameters.
    string esqlQuery =
        @"SELECT VALUE Contact FROM AdventureWorksEntities.Contact 
                    AS Contact WHERE Contact.LastName = @ln AND
                    Contact.FirstName = @fn";

    try
    {
        using (EntityCommand cmd = new EntityCommand(esqlQuery, conn))
        {
            // Create two parameters and add them to 
            // the EntityCommand's Parameters collection 
            EntityParameter param1 = new EntityParameter();
            param1.ParameterName = "ln";
            param1.Value = "Adams";
            EntityParameter param2 = new EntityParameter();
            param2.ParameterName = "fn";
            param2.Value = "Frances";

            cmd.Parameters.Add(param1);
            cmd.Parameters.Add(param2);

            using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
            {
                // Iterate through the collection of Contact items.
                while (rdr.Read())
                {
                    Console.WriteLine(rdr["FirstName"]);
                    Console.WriteLine(rdr["LastName"]);
                }
            }
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    conn.Close();
}

See Also

Tasks

How to: Execute a Parameterized Query (Entity Framework)

Concepts

Entity SQL Language

Other Resources

Working with EntityClient (Entity Framework Tasks)