如何:直接对数据源执行命令

本主题介绍如何使用以下方法针对数据源直接执行命令:ExecuteStoreCommandExecuteStoreQueryTranslate。 有关更多信息,请参见直接执行存储命令

本主题中的示例使用 School 模型

示例

下面的示例演示如何将参数传递给 ExecuteStoreQuery 方法。

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);
    }
}

下面的示例创建一个名为 DepartmentInfo 的自定义类,该类的属性与 Department 表的列名具有相同的名称。 示例执行一个在 Department 表中插入一行的命令。 然后,使用 ExecuteStoreQuery 方法查询 Department,之后返回 DepartmentInfo 对象。 然后代码执行删除这个新行的命令。

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);
    }
}

下面的示例返回 DbDataReader。 然后将 DbDataReader 转换为 Department 类型的对象。

' 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();
}

另请参见

概念

直接执行存储命令