如何：直接对数据源执行命令
本主题介绍如何使用以下方法针对数据源直接执行命令：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);
}
}
下面的示例创建一个名为 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();
}