C#存储过程调用通用类

1. // ==============================
2. // Author:  ZhiQiao
3. // Date:    2008/09/16
4. // File:    StoreProcedure.cs
5. // ==============================
7. namespace Kenwood.Product.DataAccessHelper
8. {
9.     using System;
10.     using System.Data;
11.     using System.Data.SqlClient;
12.     
13.     /**//// <summary>
14.     /// 存储过程。
15.     /// </summary>
17.     public class StoreProcedure
18.     {
19.         // 连接字符串。
20.         private string connectionString;
21.         // 存储过程名称。
22.         private string storeProcedureName;
24.         /**//// <summary>
25.         /// 初始化 DataAccessHelper.StoreProceduer 对象。
26.         /// </summary>
27.         /// <param name="connectionString">数据库连接字符串。</param>
29.         public StoreProcedure(string connectionString) {
30.             this.connectionString = connectionString;
31.         }
33.         /**//// <summary>
34.         /// 初始化 DataAccessHelper.StoreProceduer 对象。
35.         /// </summary>
36.         /// <param name="connectionString">数据库连接字符串。</param>
37.         /// <param name="storeProcedureName">存储过程名称。</param>
38.         public StoreProcedure(string connectionString, string storeProcedureName) {
39.             this.connectionString = connectionString;
40.             this.storeProcedureName = storeProcedureName;
41.         }
43.         /**//// <summary>
44.         /// 获取或设置存储过程名称。
45.         /// </summary>
46.         public string StoreProcedureName {
47.             get { return this.storeProcedureName; }
48.             set { this.storeProcedureName = value; }
49.         }
51.         /**//// <summary>
52.         /// 执行操作类（Insert/Delete/Update）存储过程。
53.         /// </summary>
54.         /// <param name="paraValues">传递给存储过程的参数值列表。</param>
55.         /// <returns>受影响的行数。</returns>
56.         public int ExecuteNonQuery(params object[] paraValues) {
58.             using (SqlConnection connection = new SqlConnection(this.connectionString)) {
60.                 SqlCommand command = this.CreateSqlCommand(connection);
62.                 try {
63.                     this.DeriveParameters(command);
64.                     this.AssignParameterValues(command, paraValues);
65.                     connection.Open();
66.                     int affectedRowsCount = command.ExecuteNonQuery();
67.                     return affectedRowsCount;
68.                 } catch {
69.                     throw;
70.                 }
71.             }
72.         }
74.         /**//// <summary>
75.         /// 执行存储过程，返回 System.Data.DataTable。
76.         /// </summary>
77.         /// <param name="paraValues">传递给存储过程的参数值列表。</param>
78.         /// <returns>包含查询结果的 System.Data.DataTable。</returns>
79.         public DataTable ExecuteDataTable(params object[] paraValues) {
81.             using (SqlConnection connection = new SqlConnection(this.connectionString)) {
83.                 SqlCommand command = this.CreateSqlCommand(connection);
85.                 try {
86.                     this.DeriveParameters(command);
87.                     this.AssignParameterValues(command, paraValues);
88.                     SqlDataAdapter adapter = new SqlDataAdapter(command);
89.                     DataTable dataTable = new DataTable();
90.                     adapter.Fill(dataTable);
91.                     return dataTable;
92.                 } catch {
93.                     throw;
94.                 }
95.             }
96.         }
98.         /**//// <summary>
99.         /// 执行存储过程，填充指定的 System.Data.DataTable。
100.         /// </summary>
101.         /// <param name="dataTable">用于填充查询结果的 System.Data.DataTable。</param>
102.         /// <param name="paraValues">传递给存储过程的参数值列表。</param>
103.         public void ExecuteFillDataTable(DataTable dataTable, params object[] paraValues) {
105.             using (SqlConnection connection = new SqlConnection(this.connectionString)) {
107.                 SqlCommand command = this.CreateSqlCommand(connection);
109.                 try {
110.                     this.DeriveParameters(command);
111.                     this.AssignParameterValues(command, paraValues);
112.                     connection.Open();
113.                     SqlDataAdapter adapter = new SqlDataAdapter(command);
114.                     adapter.Fill(dataTable);
115.                 } catch {
116.                     throw;
117.                 }
118.             }
119.         }
120.         
121.         /**//// <summary>
122.         /// 执行存储过程返回 System.Data.SqlClient.SqlDataReader，
123.         /// 在 System.Data.SqlClient.SqlDataReader 对象关闭时，数据库连接自动关闭。
124.         /// </summary>
125.         /// <param name="paraValues">传递给存储过程的参数值列表。</param>
126.         /// <returns>包含查询结果的 System.Data.SqlClient.SqlDataReader 对象。</returns>
127.         public SqlDataReader ExecuteDataReader(params object[] paraValues) {
129.             using (SqlConnection connection = new SqlConnection(this.connectionString)) {
131.                 SqlCommand command = this.CreateSqlCommand(connection);
133.                 try {
134.                     this.DeriveParameters(command);
135.                     this.AssignParameterValues(command, paraValues);
136.                     connection.Open();
137.                     return command.ExecuteReader(CommandBehavior.CloseConnection);
138.                 } catch {
139.                     throw;
140.                 }
141.             }
142.         }
144.         /**//// <summary>
145.         /// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。
146.         /// </summary>
147.         /// <param name="paraValues">传递给存储过程的参数值列表。</param>
148.         /// <returns>结果集中第一行的第一列或空引用（如果结果集为空）。</returns>
149.         public object ExecuteScalar(params object[] paraValues) {
151.             using (SqlConnection connection = new SqlConnection(this.connectionString)) {
153.                 SqlCommand command = this.CreateSqlCommand(connection);
155.                 try {
156.                     this.DeriveParameters(command);
157.                     this.AssignParameterValues(command, paraValues);
158.                     connection.Open();
159.                     return command.ExecuteScalar();
160.                 } catch {
161.                     throw;
162.                 }
163.             }
164.         }
166.         /**//// <summary>
167.         /// 从在 System.Data.SqlClient.SqlCommand 中指定的存储过程中检索参数信息并填充指定的 
168.         /// System.Data.SqlClient.SqlCommand 对象的 System.Data.SqlClient.SqlCommand.Parameters 集合。
169.         /// </summary>
170.         /// <param name="sqlCommand">将从其中导出参数信息的存储过程的 System.Data.SqlClient.SqlCommand 对象。</param>
171.         internal void DeriveParameters(SqlCommand sqlCommand) {
172.             try {
173.                 sqlCommand.Connection.Open();
174.                 SqlCommandBuilder.DeriveParameters(sqlCommand);
175.                 sqlCommand.Connection.Close();
176.             } catch {
177.                 if (sqlCommand.Connection != null) {
178.                     sqlCommand.Connection.Close();
179.                 }
180.                 throw;
181.             }
182.         }
184.         // 用指定的参数值列表为存储过程参数赋值。
185.         private void AssignParameterValues(SqlCommand sqlCommand, params object[] paraValues) {
186.             if (paraValues != null) {
187.                 if ((sqlCommand.Parameters.Count - 1) != paraValues.Length) {
188.                     throw new ArgumentNullException("The number of parameters does not match number of values for stored procedure.");
189.                 }
190.                 for (int i = 0; i < paraValues.Length; i++) {
191.                     sqlCommand.Parameters[i + 1].Value = (paraValues[i] == null) ? DBNull.Value : paraValues[i];
192.                 }
193.             }
194.         }
196.         // 创建用于执行存储过程的 SqlCommand。
197.         private SqlCommand CreateSqlCommand(SqlConnection connection) {
199.             SqlCommand command = new SqlCommand(this.storeProcedureName, connection);
200.             command.CommandType = CommandType.StoredProcedure;
202.             return command;
203.         }
204.     }
205. }