1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4
5 namespace Helper
6 {
7 public class ProcedureHelper
8 {
9 private string connectionString;
10
11 public ProcedureHelper(string strConn)
12 {
13 connectionString = strConn;
14 }
15
16 /// <summary>
17 /// 执行存储过程返回DataSet
18 /// </summary>
19 /// <param name="storedProcName">存储过程名</param>
20 /// <param name="parameters">存储过程参数</param>
21 /// <param name="tableName">DataSet结果中的表名</param>
22 /// <returns>DataSet</returns>
23 public DataSet ExecuteDataSet(string storedProcName, IDataParameter[] parameters, string tableName)
24 {
25 using (var connection = new SqlConnection(connectionString))
26 {
27 var dataSet = new DataSet();
28 connection.Open();
29 var sqlDA = new SqlDataAdapter();
30 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
31 sqlDA.Fill(dataSet, tableName);
32 connection.Close();
33 return dataSet;
34 }
35 }
36
37 /// <summary>
38 /// 执行存储过程返回DataTable
39 /// </summary>
40 /// <param name="storedProcName">存储过程名</param>
41 /// <param name="parameters">存储过程参数</param>
42 /// <returns>DataTable</returns>
43 public DataTable ExecuteDataTable(string storedProcName, IDataParameter[] parameters)
44 {
45 using (var connection = new SqlConnection(connectionString))
46 {
47 var dateTable = new DataTable();
48 connection.Open();
49 var sqlDA = new SqlDataAdapter();
50 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
51 sqlDA.Fill(dateTable);
52 connection.Close();
53 return dateTable;
54 }
55 }
56
57 /// <summary>
58 /// 执行存储过程返回受影响行数
59 /// </summary>
60 /// <param name="storedProcName">存储过程名称</param>
61 /// <param name="parameters">存储过程参数</param>
62 /// <returns>int</returns>
63 public int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters)
64 {
65 using (var connection = new SqlConnection(connectionString))
66 {
67 connection.Open();
68 var cmd = BuildQueryCommand(connection, storedProcName, parameters);
69 int affectedRowsCount = cmd.ExecuteNonQuery();
70 return affectedRowsCount;
71 }
72 }
73
74 /// <summary>
75 /// 执行存储过程，并返回查询所返回的结果集中第一行的第一列
76 /// </summary>
77 /// <param name="storedProcName">存储过程名</param>
78 /// <param name="parameters">存储过程参数</param>
79 /// <returns>object</returns>
80 public object ExecuteScalar(string storedProcName, IDataParameter[] parameters)
81 {
82 using (var connection = new SqlConnection(connectionString))
83 {
84 connection.Open();
85 var cmd = BuildQueryCommand(connection, storedProcName, parameters);
86 object returnValue = cmd.ExecuteScalar();
87 return returnValue;
88 }
89 }
90
91 /// <summary>
92 /// 执行查询语句,返回SqlDataReader
93 /// </summary>
94 /// <param name="storedProcName">存储过程名称</param>
95 /// <param name="parameters">存储过程参数</param>
96 /// <returns>SqlDataReader</returns>
97 public SqlDataReader ExecuteDataReader(string storedProcName, IDataParameter[] parameters)
98 {
99 using (var connection = new SqlConnection(connectionString))
100 {
101 connection.Open();
102 var cmd = BuildQueryCommand(connection, storedProcName, parameters);
103 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
104 return myReader;
105 }
106 }
107
108 /// <summary>
109 /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
110 /// </summary>
111 /// <param name="connection">数据库连接</param>
112 /// <param name="storedProcName">存储过程名</param>
113 /// <param name="parameters">存储过程参数</param>
114 /// <returns>SqlCommand</returns>
115 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName,
116 IDataParameter[] parameters)
117 {
118 var command = new SqlCommand(storedProcName, connection);
119 command.CommandType = CommandType.StoredProcedure;
120 foreach (IDataParameter dataParameter in parameters)
121 {
122 var parameter = (SqlParameter) dataParameter;
123 if (parameter != null)
124 {
125 // 检查未分配值的输出参数,将其分配以DBNull.Value.
126 if ((parameter.Direction == ParameterDirection.InputOutput ||
127 parameter.Direction == ParameterDirection.Input) &&
128 (parameter.Value == null))
129 {
130 parameter.Value = DBNull.Value;
131 }
132 command.Parameters.Add(parameter);
133 }
134 }
135 return command;
136 }
137 }
138 }
