方法: データ ソースに対してコマンドを直接実行する
このトピックでは、ExecuteStoreCommand、ExecuteStoreQuery、および Translate の各メソッドを使用して、データ ソースに対してコマンドを直接実行する方法について説明します。 詳細については、「ストア コマンドの直接実行」を参照してください。
このトピックの例では、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);
}
}
次の例は、Department テーブルの列名と同じ名前のプロパティを持つ DepartmentInfo という名前のカスタム クラスを作成します。 この例では、Department テーブルに 1 行を挿入するコマンドを実行します。 次に 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();
}