Code First 存储过程
使用 Code First 访问存储过程。
引言
Code First 是由开发 MVC3 和 Razor 的同一个团队提供的一个新的、轻量级的数据库接口。虽然它在许多方面做得很好，但其接口的一个漏洞是存储过程。本项目提供了一些例程，可以调用存储过程并正确处理输入和输出参数、返回代码和多个结果集。
背景
从强类型语言调用存储过程的问题在于它们很混乱。它们可以通过标量参数或表 (T-SQL) 接受数据，并且可以通过它们的参数、返回代码和多个结果集返回数据，每个结果集可能完全不同。在 Microsoft 的聪明人向 Code First (Entity Framework 4.1) 添加存储过程支持之前，您可以使用此接口例程。本项目提供类和扩展方法，允许您调用存储过程并以相对清晰的方式处理所有这些混乱。
使用代码
让我们从一些示例存储过程开始。
//
-- Stored procedure with input and output parameters, and a single result set
create proc testone @in varchar(5), @out int out
as
begin
select table_name, column_name from INFORMATION_SCHEMA.COLUMNS
set @out = @@ROWCOUNT
end
go
-- Stored procedure with no parameters, a return code and a single result set
create proc testtwo
as
begin
select TABLE_CATALOG, TABLE_NAME
from INFORMATION_SCHEMA.TABLES
return @@ROWCOUNT
end
go
-- Stored procedure with no parameters and multiple result sets
create proc testthree
as
begin
select table_name, column_name from INFORMATION_SCHEMA.COLUMNS
select TABLE_CATALOG, TABLE_NAME
from INFORMATION_SCHEMA.TABLES
end
go
这三个示例过程执行了我们对存储过程期望的大多数复杂操作：输入和输出参数、返回代码和多个结果集。需要注意的是，任何一个存储过程都可以通过所有这些方式向调用者返回数据。
要使用此代码，我们需要遵循一个相当简单的调用模式：为参数和结果集创建类，填充参数类，调用扩展方法，然后处理输出。以下是上面第一个存储过程的示例类
/// <summary>
/// Parameters object for the 'testoneproc' stored procedure
/// </summary>
public class testone
{
// Override the parameter name. The parameter name is "in", but that's not a valid property
// name in C#, so we must name the property something else and provide an override to set
// the parameter name.
[StoredProcAttributes.Name("in")]
[StoredProcAttributes.ParameterType(System.Data.SqlDbType.VarChar)]
public String inparm { get; set; }
// This time we not only override the parameter name, we're also setting the parameter
// direction, indicating that this property will only receive data, not provide data
// to the stored procedure. Note that we must include the size in bytes.
[StoredProcAttributes.Name("out")]
[StoredProcAttributes.Direction(System.Data.ParameterDirection.Output)]
[StoredProcAttributes.Size(4)]
public Int32 outparm { get; set; }
}
/// <summary>
/// Results object for the 'testoneproc' stored procedure
/// </summary>
public class TestOneResultSet
{
// match by the attribute name rather than the property name
[StoredProcAttributes.Name("table_name")]
public string table { get; set; }
// match by the ordinal (column order, count from 0) rather than the property name
[StoredProcAttributes.Ordinal(1))]
public string column { get; set; }
}
为了处理输出参数，我们使用 `Direction` 属性装饰相应的属性，并为其指定 `ParameterDirection.Output` 值。当调用存储过程返回时，这将自动保存存储过程中设置的输出值。我们还将 `Size` 参数设置为 4 字节以匹配整数返回值的大小。如果 `Size` 参数太小，返回的值将被截断。默认情况下，返回的数据通过结果集中的列名与目标类对象中的属性名进行匹配。这可以使用 `Name` 或 `Ordinal` 属性进行覆盖。使用这些类，我们现在可以定义一个 StoredProc 对象来定义存储过程
// simple stored proc
public StoredProc<testone> testoneproc = new StoredProc<testone>(typeof(TestOneResultSet));
存储过程的定义包含参数类，构造函数包含每个预期的返回集类型，按它们在存储过程中创建的相同顺序排列。在这种情况下，我们期望一个返回集，因此我们提供一个类型对象。需要注意的是，作为结果集类型提供的任何类型**必须**具有默认构造函数，即不带参数的构造函数。现在我们有了数据源和目标类以及我们定义的存储过程，我们可以调用数据库了。
using (testentities te = new testentities())
{
//-------------------------------------------------------------
// Simple stored proc
//-------------------------------------------------------------
var parms1 = new testone() { inparm = "abcd" };
var results1 = te.CallStoredProc<testone>(te.testoneproc, parms1);
var r1 = results1.ToList<TestOneResultSet>();
}
请注意，参数类中的参数名称应与存储过程定义中声明的参数名称匹配。如果不可能（如我们上面的示例：“in”和“out”不是有效的属性名称），则可以使用 `Name` 属性来覆盖默认值并指定参数名称。
秉承 Code First 使用轻量级 POCO 对象作为数据载体的理念，结果集值通过匹配结果集中的列名与返回类型中的属性名来复制到输出对象中。这种“按属性复制”对用于标识不应映射到数据库 I/O 的对象属性的 `NotMappedAttribute` 敏感，并且可以使用 `Name` 属性进行覆盖。
结果集对象中的 `ToList
第二个示例存储过程同时包含一个结果集和一个返回代码。为了处理返回代码，我们可以创建一个参数类，并用 `Direction` 属性装饰该属性，并将其值设置为 `ParameterDirection.ReturnValue`。当存储过程调用返回时，这将保存存储过程中设置的返回代码。请注意，在 SQL Server 中，这必须是一个整数值。如果出于某种原因，您希望忽略返回代码参数，您可以调用 `CallStoredProc` 的非泛型版本
// stored proc with no parameters
public StoredProc testtwo = new StoredProc("testtwo", typeof(TestTwoResultSet));
//-------------------------------------------------------------
// Simple stored proc with no parameters
//-------------------------------------------------------------
var results2 = te.CallStoredProc(testtwo);
var r2 = results2.ToList<TestTwoResultSet>();
在这种情况下，我们故意丢弃存储过程将返回的返回代码参数。这不会导致错误。也可以忽略结果集。`CallStoredProc` 例程只会保存那些在方法调用中指定了类型的结果集。反之，如果存储过程返回的结果集少于您提供的类型，也不会导致错误。
第三个示例返回多个结果集。由于此存储过程不接受参数或返回代码，因此设置很简单 - 只需调用该过程。在这种情况下，我们使用 Fluent API 样式方法为 `StoredProc` 对象赋值。
StoredProc testthree = new StoredProc()
.HasName("testthree")
.ReturnsTypes(typeof(TestOneResultSet), typeof(TestTwoResultSet));
//-------------------------------------------------------------
// Stored proc with no parameters and multiple result sets
//-------------------------------------------------------------
var results3 = te.CallStoredProc(testthree);
var r3_one = results3.ToList<TestOneResultSet>();
var r3_two = results3.ToArray<TestTwoResultSet>();
`ResultsList` 的 `ToList
表值参数
SQL Server 可以接受表作为存储过程的参数。在数据库中，我们需要为表创建一个用户定义类型，然后使用此类型声明存储过程参数。参数上的“Readonly”修饰符是必需的。
-- Create Table variable
create type [dbo].[testTVP] AS TABLE(
[testowner] [nvarchar] (50) not null,
[testtable] [nvarchar] (50) NULL,
[testcolumn] [nvarchar](50) NULL
)
GO
-- Create procedure using table variable
create proc testfour @tt testTVP readonly
as
begin
select table_schema, table_name, column_name from INFORMATION_SCHEMA.COLUMNS
inner join @tt
on table_schema = testowner
where (testtable is null or testtable = table_name)
and (testcolumn is null or testcolumn = column_name)
end
go
在 .Net 方面，我们需要创建一个类来表示此表中的行，并且我们需要复制表定义，以便可以适当地处理数据行。我们将创建一个类来表示传递给表值参数的表的行，并使用与 SQL 表定义匹配的属性来装饰该类。
/// <summary>
/// Class representing a row of data for a table valued parameter.
/// Property names (or Name attribute) must match table type column names
/// </summary>
[StoredProcAttributes.Schema("dbo")]
[StoredProcAttributes.TableName("testTVP")]
public class sample
{
[StoredProcAttributes.Name("testowner")]
[StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
[StoredProcAttributes.Size(50)]
public string owner { get; set; }
[StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
[StoredProcAttributes.Size(50)]
public string testtable { get; set; }
[StoredProcAttributes.ParameterType(SqlDbType.VarChar)]
[StoredProcAttributes.Size(50)]
public string testcolumn { get; set; }
}
`Schema` 和 `TableName` 属性标识我们传递给表值参数的表定义，并且匹配每个列的方法必须用适当的 SQL 列定义属性进行装饰。
/// <summary>
/// Parameter object for 'testfour' stored procedure
/// </summary>
public class testfour
{
[StoredProcAttributes.ParameterType(SqlDbType.Structured)]
[StoredProcAttributes.Name("tt")]
public List<sample> tabledata { get; set; }
}
/// <summary>
/// output class for proc test four
/// </summary>
public class TestFourResultSet
{
public string table_schema { get; set; }
public string table_name { get; set; }
public string column_name { get; set; }
}
现在我们有了一个与表定义匹配的类，我们可以在参数对象中创建一个表值参数的实例。创建一个类型为 `IEnumerable<>` 的属性，并将其 SqlDbType 设置为 `Structured`。CodeFirstStoredProc 例程将动态构建表定义并将表传递给存储过程。要使用，只需为表值参数提供底层类的列表或数组即可。
//-------------------------------------------------------------
// Stored proc with a table valued parameter
//-------------------------------------------------------------
// new parameters object for testfour
testfour four = new testfour();
// load data to send in the table valued parameter
four.tabledata = new List<sample>()
{
new sample() { owner = "tester" },
new sample() { owner = "dbo" }
};
// call stored proc
var ret4 = te.CallStoredProc<testfour>(te.testfour, four);
var retdata = ret4.ToList<TestFourResultSet>();
致谢
感谢并鸣谢所有教我这些技术的人，所有花时间发布对我知识有帮助的技巧的博主，特别感谢 StackOverflow，没有它，这一切就不可能存在。
历史
版本 1.0 初始发布
版本 2.0
- 重大更改！
- 接口的完全替换，使其更易于使用，并且更符合 Code First 风格。
版本 2.1
- 重大更改！是的，这是一个坏习惯，但在这种情况下是好事。
- 删除了“TableType”类，并用更简单、更“代码优先”的风格替换，通过 POCO 对象和属性定义表值参数。
- 添加了 `precision` 和 `scale` 属性。
版本 2.6
- 添加对 Entity Framework 6 的支持
- 添加命令超时参数以控制长时间运行的存储过程调用
版本 3.0
- 急需的接口更新
- 添加对异步和流式传输的支持
版本 3.2
- 已更新以修复异步存储过程调用问题。
- 现在，从存储过程流式传输数据需要手动设置“CommandBehavior.SequentialAccess”
版本 3.3
- 更新以与 Glimpse.EF6 库一起使用。
版本 3.5
- 修复版本号不匹配问题。
版本 3.6
- 对于 null 或空（零行计数）的表值参数，不发送参数。修复了空或 null TVP 的错误。
版本 3.7
- 错误修复和一些代码重构。
版本 3.8
- 允许按序号值和按名称将返回列分配给属性。
版本 3.9
- 错误修复！传入的连接不再意外关闭。