更新时间:2022-02-13 08:37:20
让我们说您想发送一个包含GUID单列的表。
Lets say you want to send a table with a single column of GUIDs.
首先,我们需要使用 SqlMetaData 代表表的架构(列)。
First we need to create a structure using SqlMetaData which represents the schema of the table (columns).
var tableSchema = new List<SqlMetaData>(1)
{
new SqlMetaData("Id", SqlDbType.UniqueIdentifier)
}.ToArray();
接下来,您将使用 SqlDataRecord 。
var tableRow = new SqlDataRecord(tableSchema);
tableRow.SetGuid(0, Guid.NewGuid());
var table = new List<SqlDataRecord>(1)
{
tableRow
};
然后创建 SqlParameter :
var parameter = new SqlParameter();
parameter.SqlDbType = SqlDbType.Structured;
parameter.ParameterName = "@UserIds";
parameter.Value = table;
var parameters = new SqlParameter[1]
{
parameter
};
然后只需使用 Database.SqlQuery 。
IEnumerable<ReturnType> result;
using (var myContext = new DbContext())
{
result = myContext.Database.SqlQuery<User>("GetUsers", parameters)
.ToList(); // calls the stored procedure
// ToListAsync(); // Async
{
在SQL Server中,创建用户定义的表类型(I在它们的后缀TTV,表类型值):
In SQL Server, create your User-Defined Table Type (I suffix them with TTV, Table Typed Value):
CREATE TYPE [dbo].[UniqueidentifiersTTV] AS TABLE(
[Id] [uniqueidentifier] NOT NULL
)
GO
然后指定类型作为参数(不要忘记,表类型值必须是只读的!):
Then specify the type as a parameter (don't forget, Table Type Values have to be readonly!):
CREATE PROCEDURE [dbo].[GetUsers] (
@UserIds [UniqueidentifiersTTV] READONLY
) AS
BEGIN
SET NOCOUNT ON
SELECT u.* -- Just an example :P
FROM [dbo].[Users] u
INNER JOIN @UserIds ids On u.Id = ids.Id
END