Share via


How to: Directly Execute Commands Against the Data Source

This topic shows how to directly execute commands against the data source using the following methods: ExecuteStoreCommand, ExecuteStoreQuery, and Translate. For more information, see Directly Executing Store Commands.

The examples in this topic use the School Model.

Example

The following example shows how to pass parameters to the ExecuteStoreQuery method.

Using context As New SchoolEntities()
    ' The following three queries demonstrate 
    ' three different ways of passing a parameter. 
    ' The queries return a string result type. 

    ' Use the parameter substitution pattern. 
    For Each name As String In context.ExecuteStoreQuery(Of String)("Select Name from Department where DepartmentID < {0}", 5)
        Console.WriteLine(name)
    Next

    ' Use parameter syntax with object values. 
    For Each name As String In context.ExecuteStoreQuery(Of String)("Select Name from Department where DepartmentID < @p0", 5)
        Console.WriteLine(name)
    Next
    ' Use an explicit SqlParameter. 
    For Each name As String In context.ExecuteStoreQuery(Of String)("Select Name from Department where DepartmentID < @p0", _
                                                                    New SqlParameter())
        Console.WriteLine(name)
    Next
End Using
using (SchoolEntities context =
    new SchoolEntities())
{
    // The following three queries demonstrate 
    // three different ways of passing a parameter.
    // The queries return a string result type.

    // Use the parameter substitution pattern.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < {0}", 5))
    {
        Console.WriteLine(name);
    }

    // Use parameter syntax with object values.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < @p0", 5))
    {
        Console.WriteLine(name);
    }
    // Use an explicit SqlParameter.
    foreach (string name in context.ExecuteStoreQuery<string>
        ("Select Name from Department where DepartmentID < @p0",
            new SqlParameter { ParameterName = "p0", Value = 5 }))
    {
        Console.WriteLine(name);
    }
}

The following example creates a custom class named DepartmentInfo that has properties with the same names as the column names of the Department table. The example executes a command that inserts a row into the Department table. It then uses the ExecuteStoreQuery method to query for a Department and then returns the DepartmentInfo object. The code then executes a command that deletes the new row.

Public Class DepartmentInfo
    Private _startDate As DateTime
    Private _name As String
    Private _departmentID As Int32

    Public Property DepartmentID() As Int32
        Get
            Return _departmentID
        End Get
        Set(ByVal value As Int32)
            _departmentID = value
        End Set
    End Property
    Public Property Name() As String
        Get
            Return _name
        End Get
        Set(ByVal value As String)
            _name = value
        End Set
    End Property
    Public Property StartDate() As DateTime
        Get
            Return _startDate
        End Get
        Set(ByVal value As DateTime)
            _startDate = value
        End Set
    End Property
End Class

Public Shared Sub ExecuteStoreCommands()
    Using context As New SchoolEntities()

        Dim DepartmentID As Integer = 21
        ' Insert the row in the Department table. Use the parameter substitution pattern. 
        Dim rowsAffected As Integer = context.ExecuteStoreCommand("insert Department values ({0}, {1}, {2}, {3}, {4})", _
                                                                  DepartmentID, "Engineering", 350000.0R, "2009-09-01", 2)
        Console.WriteLine("Number of affected rows: {0}", rowsAffected)

        ' Get the DepartmentTest object. 
        Dim department As DepartmentInfo = context.ExecuteStoreQuery(Of DepartmentInfo) _
                                           ("select * from Department where DepartmentID= {0}", _
                                           DepartmentID).FirstOrDefault()

        Console.WriteLine("ID: {0}, Name: {1} ", _
                          department.DepartmentID, department.Name)

        rowsAffected = context.ExecuteStoreCommand("delete from Department where DepartmentID = {0}", _
                                                   DepartmentID)
        Console.WriteLine("Number of affected rows: {0}", _
                          rowsAffected)
    End Using
End Sub
public class DepartmentInfo
{
    private DateTime _startDate;
    private String _name;
    private Int32 _departmentID;

    public Int32 DepartmentID
    {
        get
        {
            return _departmentID;
        }
        set
        {
            _departmentID = value;
        }
    }
    public String Name
    {
        get
        {
            return _name;
        }
        set
        {
            _name = value;
        }
    }
    public DateTime StartDate
    {
        get
        {
            return _startDate;
        }
        set
        {
            _startDate = value;
        }
    }
}

public static void ExecuteStoreCommands()
{
    using (SchoolEntities context =
        new SchoolEntities())
    {

        int DepartmentID = 21;
        // Insert the row in the Department table. Use the parameter substitution pattern.
        int rowsAffected = context.ExecuteStoreCommand("insert Department values ({0}, {1}, {2}, {3}, {4})",
                        DepartmentID, "Engineering", 350000.00, "2009-09-01", 2);
        Console.WriteLine("Number of affected rows: {0}", rowsAffected);

        // Get the DepartmentTest object. 
        DepartmentInfo department = context.ExecuteStoreQuery<DepartmentInfo>
            ("select * from Department where DepartmentID= {0}", DepartmentID).FirstOrDefault();

        Console.WriteLine("ID: {0}, Name: {1} ", department.DepartmentID, department.Name);

        rowsAffected = context.ExecuteStoreCommand("delete from Department where DepartmentID = {0}", DepartmentID);
        Console.WriteLine("Number of affected rows: {0}", rowsAffected);
    }
}

The following example returns a DbDataReader. It then translates the DbDataReader into objects of the Department type.

' Initialize the connection string builder for the 
' underlying provider. 
Dim sqlBuilder As New SqlConnectionStringBuilder()

sqlBuilder.DataSource = "."
sqlBuilder.InitialCatalog = "School"
sqlBuilder.IntegratedSecurity = True

Dim con As New SqlConnection(sqlBuilder.ToString())
If True Then
    con.Open()
    Dim cmd As DbCommand = con.CreateCommand()
    cmd.CommandText = "SELECT * FROM Department"

    ' Create a reader that contains rows of entity data. 
    Using rdr As DbDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
        Using context As New SchoolEntities()
            ' Translate the reader to the objects of the Department type. 
            For Each d As Department In context.Translate(Of Department)(rdr)
                Console.WriteLine("DepartmentID: {0} ", d.DepartmentID)
            Next
        End Using
    End Using
    con.Close()
End If
// Initialize the connection string builder for the
// underlying provider.
SqlConnectionStringBuilder sqlBuilder =
    new SqlConnectionStringBuilder();

sqlBuilder.DataSource = ".";
sqlBuilder.InitialCatalog = "School";
sqlBuilder.IntegratedSecurity = true;

SqlConnection con = new SqlConnection(sqlBuilder.ToString());
{
    con.Open();
    DbCommand cmd = con.CreateCommand();
    cmd.CommandText = @"SELECT * FROM Department";

    // Create a reader that contains rows of entity data. 
    using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        using (SchoolEntities context =
            new SchoolEntities())
        {
            // Translate the reader to the objects of the Department type.
            foreach (Department d in context.Translate<Department>(rdr))
            {
                Console.WriteLine("DepartmentID: {0} ", d.DepartmentID);
            }
        }
    }
    con.Close();
}

See Also

Concepts

Directly Executing Store Commands