Vorgehensweise: Direktes Ausführen von Befehlen für die Datenquelle
In diesem Thema wird beschrieben, wie Befehle mithilfe der folgenden Methoden direkt für die Datenquelle ausgeführt werden: ExecuteStoreCommand, ExecuteStoreQuery und Translate. Weitere Informationen finden Sie unter Direktes Ausführen von Speicherbefehlen.
In den Beispielen in diesem Thema wird das Modell "School" verwendet.
Beispiel
Im folgenden Beispiel werden andere Möglichkeiten gezeigt, Parameter an die ExecuteStoreQuery-Methode zu übergeben.
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);
}
}
Im folgenden Beispiel wird die benutzerdefinierte Klasse DepartmentInfo erstellt, die über Eigenschaften mit den gleichen Namen wie den Namen der Spalten der Tabelle Department verfügt. Im Beispiel wird ein Befehl ausgeführt, der eine Zeile in die Tabelle Department einfügt. Dann wird mithilfe der ExecuteStoreQuery-Methode Department abgefragt und ein DepartmentInfo-Objekt zurückgegeben. Im Code wird anschließend ein Befehl ausgeführt, der die neue Zeile löscht.
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);
}
}
Im folgenden Beispiel wird ein DbDataReader zurückgegeben. Anschließend wird der DbDataReader in Objekte des Department-Typs übersetzt.
' 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();
}