ADO.NET 访问存储过程

ADO.NET 访问存储过程其实也比较简单，但是有些小细节部分是需要注意的。

做了个小案例，演示在ADO.NET中如何调用存储过程。

数据库表结构代码如下：

CREATE TABLE TestTB(
    [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 )
GO

存储过程代码如下：

CREATE PROCEDURE [dbo].[SelectAllProc]
(
    @inputData nvarchar,
    @outPutData int output
)
AS
    declare @myCount int
    print @inputData
    select * from dbo.TestTB
    select @myCount=count(*) from dbo.TestTB

    set @outPutData=@myCount

    /* SET NOCOUNT ON */
    RETURN @myCount

GO

    该存储过程包含了输入参数、输出参数、select语句返回的结果集以及return返回的单个结果值。在ADO.NET调用的时候需要同时设置和获取这些值。

     界面部分使用的是ASP.NET MVC3来做。为了呈现数据的方便所以定制了两个Model类。分别是TestTB类和TestTBViewModel类。第一个类用于承载单个数据实体，第二个用于呈现界面的时候一次性的将数据传到界面呈现。

     Models部分的代码如下：

 1 /// <summary>
 2     /// 承载测试数据用的模型类
 3     /// </summary>
 4      public class TestTB
 5     {
 6         public int ID { get; set; }
 7         public string Name { get; set; }
 8     }
 9 
10     /// <summary>
11     /// 承载界面呈现数据的时候的模型类
12     /// 用于一次性将所有数据传递到界面
13     /// </summary>
14      public class TestTBViewModel
15     {
16         public TestTBViewModel()
17         {
18             this.TestTBList = new List<TestTB>();
19         }
20         /// <summary>
21         /// TestTB实体集合
22         /// </summary>
23         public List<TestTB> TestTBList { get; set; }
24         /// <summary>
25         /// 存储过程return回来的值
26         /// </summary>
27         public int TotalCount { get; set; }
28         /// <summary>
29         /// 存储过程output回来的值
30         /// </summary>
31         public int TotalCount2 { get; set; }
32     }

    调用存储过程以及MVC控制器中的代码如下：

 1   /// <summary>
 2         /// 展示数据的Action
 3         /// </summary>
 4         /// <returns></returns>
 5         public ActionResult ShowData()
 6         {
 7             return View(this.GetAllData());
 8         }
 9 
10         /// <summary>
11         /// 获取数据的方法
12         /// </summary>
13         /// <returns>返回用于界面呈现的模型</returns>
14         private TestTBViewModel GetAllData()
15         {
16             TestTBViewModel model = new TestTBViewModel();
17             //创建数据库连接和命令对象并限定其使用范围（超过该范围则自动释放资源）
18             using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString))
19             using (SqlCommand command = new SqlCommand())
20             {
21                 command.Connection = connection;//设置命令所使用的连接对象
22                 command.CommandText = "dbo.SelectAllProc";//设置命令对象调用的存储过程的名称
23                 command.CommandType = System.Data.CommandType.StoredProcedure;//设置该命令的类型为存储过程
24 
25                 //为命令对象添加参数，该参数名可以随便定义，用于接收存储过程return回来的值
26                 SqlParameter pReturn = command.Parameters.Add("@aaa", System.Data.SqlDbType.Int);
27                 //设置该参数的类型为返回值
28                 pReturn.Direction = System.Data.ParameterDirection.ReturnValue;
29 
30                 //添加存储过程第一个参数，用于向存储过程输入值
31                 SqlParameter pInput = command.Parameters.Add("@inputData", System.Data.SqlDbType.NVarChar);
32                 //设置该参数的类型为输入参数
33                 pInput.Direction = System.Data.ParameterDirection.Input;
34                 //设置参数的值（因为该值在存储过程中只是print一下所以随便输入）
35                 pInput.Value = "aaa";
36 
37                 //添加存储过程第二个参数，用于接收存储过程输出参数的值
38                 SqlParameter pOut = command.Parameters.Add("@outPutData", System.Data.SqlDbType.Int);
39                 //设置该参数的类型为输出参数
40                 pOut.Direction = System.Data.ParameterDirection.Output;
41 
42                 //打开数据库连接
43                 connection.Open();
44                 //执行命令并获取反馈回来的读取流
45                 //ExecuteReader的参数System.Data.CommandBehavior.CloseConnection的意思是关闭这个流的时候同时会关闭对应的连接对象
46                 using (SqlDataReader dataReader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
47                 {
48                     //循环读取流对象中的每行数据并将其添加到模型中
49                     while (dataReader.Read())
50                     {
51                         model.TestTBList.Add(new TestTB()
52                         {
53                             ID = dataReader.GetInt32(0),
54                             Name = dataReader.GetString(1)
55                         });
56                     }
57                 }
58 
59                 //读取return、output回来的数据之前一定要关闭dataReader，否则读取不到值。
60 
61                 //读取存储过程return回来的值并保存到模型中
62                 model.TotalCount = (int)pReturn.Value;
63                 //读取存储过程output回来的值并保存到模型中
64                 model.TotalCount2 = (int)pOut.Value;
65             }
66 
67             return model;
68         }

    <SPAN style="COLOR: #ff0000; BACKGROUND-COLOR: #ffffff" color="#ff0000"><STRONG>一定要注意：读取return、output的数据必须先要关闭掉DataReader对象，否则不能获取到值。</STRONG></SPAN>

    界面部分使用了Razor模板来呈现。具体代码如下：

@model MvcApplication1.Models.TestTBViewModel
@{
    ViewBag.Title = "ShowData";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<h2>
    ShowData</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table>
    <tr>
        <th>
            ID
        </th>
        <th>
            Name
        </th>
    </tr>
    @foreach (var item in Model.TestTBList)
    {
        <tr>
            <td>
                @item.ID
            </td>
            <td>
                @item.Name
            </td>
        </tr>
    }
</table>
<p>
    Count: @Model.TotalCount
</p>
<p>
    Count2: @Model.TotalCount2
</p>