下面是一个封装了的ADO.NET连接数据库的模块，可以执行几乎所有的存储过程，稍做修改便可执行所有的SQL Command的单语句命令：
1
using System;
2using System.Configuration;
3using System.Data;
4using System.Data.SqlClient;
5
6namespace DBModules
7
{
8
/// <summary>
9
/// 数据库访问辅助类，该类中都是静态的方法，以更方便的调用存储过程
10
/// </summary>
11 public sealed class SqlHelper
12 {
13 /// <summary>
14 /// 这里用私有函数，防止实例化该类
15 /// </summary>
16 private SqlHelper()
17 {
18
19 }
20 /// <summary>
21 /// 获取数据库连接字符串
22 /// </summary>
23 public static string connectionString
24 {
25 get{ return ConfigurationSettings.AppSettings["connectString"];}
26 }
27
28 /// <summary>
29 /// Private routine allowed only by this base class, it automates the task
30 /// of building a SqlCommand object designed to obtain a return value from
31 /// the stored procedure.
32 /// </summary>
33 /// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>
34 /// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>
35 /// <returns>Newly instantiated SqlCommand instance</returns>
36 private static SqlCommand BuildIntCommand(
37 SqlConnection connection,
38 string storedProcName,
39 IDataParameter[] parameters)
40 {
41 SqlCommand command =
42 BuildQueryCommand( connection,storedProcName, parameters );
43
44 command.Parameters.Add( new SqlParameter ( "ReturnValue",
45 SqlDbType.Int,
46 4, /* Size */
47 ParameterDirection.ReturnValue,
48 false, /* is nullable */
49 0, /* byte precision */
50 0, /* byte scale */
51 string.Empty,
52 DataRowVersion.Default,
53 null ));
54
55 return command;
56 }
57
58 /// <summary>
59 /// Builds a SqlCommand designed to return a SqlDataReader, and not
60 /// an actual integer value.
61 /// </summary>
62 /// <param name="storedProcName">Name of the stored procedure</param>
63 /// <param name="parameters">Array of IDataParameter objects</param>
64 /// <returns></returns>
65 private static SqlCommand BuildQueryCommand(
66 SqlConnection connection,
67 string storedProcName,
68 IDataParameter[] parameters)
69 {
70 if(connectionString==null)
71 throw new ApplicationException("Sql连接字符串connectionString没有初始化");
72
73 SqlCommand command = new SqlCommand( storedProcName,connection );
74 command.CommandType = CommandType.StoredProcedure;
75
76 foreach (SqlParameter parameter in parameters)
77 {
78 command.Parameters.Add( parameter );
79 }
80
81 return command;
82
83 }
84
85 /// <summary>
86 /// Runs a stored procedure, can only be called by those classes deriving
87 /// from this base. It returns an integer indicating the return value of the
88 /// stored procedure, and also returns the value of the RowsAffected aspect
89 /// of the stored procedure that is returned by the ExecuteNonQuery method.
90 /// </summary>
91 /// <param name="storedProcName">Name of the stored procedure</param>
92 /// <param name="parameters">Array of IDataParameter objects</param>
93 /// <param name="rowsAffected">Number of rows affected by the stored procedure.</param>
94 /// <returns>An integer indicating return value of the stored procedure</returns>
95 public static int RunIntProcedure(
96 string storedProcName,
97 IDataParameter[] parameters,
98 out int rowsAffected )
99 {
100 int result = 0;
101 rowsAffected = 0;
102 SqlConnection connection = new SqlConnection(SqlHelper.connectionString);
103 try
104 {
105 SqlCommand command = BuildIntCommand(connection,storedProcName, parameters );
106 rowsAffected = command.ExecuteNonQuery();
107 result = (int)command.Parameters["ReturnValue"].Value;
108 }
109 finally
110 {
111 connection.Close();
112 }
113 return result;
114 }
115
116 /// <summary>
117 /// 运行存储过程，并且返回存储过程的结果
118 /// </summary>
119 /// <param name="storedProcName">Name of the stored procedure</param>
120 /// <param name="parameters">Array of IDataParameter objects</param>
121 /// <returns>An integer indicating return value of the stored procedure</returns>
122 public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
123 {
124 int result = 0;
125
126 SqlConnection connection=new SqlConnection(SqlHelper.connectionString);
127 try
128 {
129 connection.Open();
130 SqlCommand command = BuildIntCommand(connection, storedProcName, parameters );
131 command.CommandType = CommandType.StoredProcedure;
132 command.ExecuteNonQuery();
133
134 result = (int)command.Parameters["ReturnValue"].Value;
135 }
136 finally
137 {
138 connection.Close();
139 }
140
141 return result;
142 }
143
144 /// <summary>
145 /// Will run a stored procedure, can only be called by those classes deriving
146 /// from this base. It returns a SqlDataReader containing the result of the stored
147 /// procedure.
148 /// </summary>
149 /// <param name="storedProcName">Name of the stored procedure</param>
150 /// <param name="parameters">Array of parameters to be passed to the procedure</param>
151 /// <returns>A newly instantiated SqlDataReader object</returns>
152 /// <remarks>
153 /// 返回的SqlDataReader保持了一个打开的连接，一定要记住用完SqlDataReader后调用close方法。
154 /// </remarks>
155 public static SqlDataReader RunDataReaderProcedure(string storedProcName, IDataParameter[] parameters )
156 {
157 SqlDataReader returnReader;
158 SqlConnection connection = new SqlConnection(SqlHelper.connectionString);
159
160 connection.Open();
161 SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );
162 command.CommandType = CommandType.StoredProcedure;
163
164 returnReader = command.ExecuteReader();
165 //connection.Close();
166 return returnReader;
167 }
168
169 /// <summary>
170 /// Creates a DataSet by running the stored procedure and placing the results
171 /// of the query/proc into the given tablename.
172 /// </summary>
173 /// <param name="storedProcName">存储过程名称</param>
174 /// <param name="parameters">存储过程参数</param>
175 /// <param name="tableName">返回的DataSet中的Table的名称</param>
176 /// <returns>存储过程的结果集</returns>
177 public static DataSet RunDataSetProcedure(
178 string storedProcName,
179 IDataParameter[] parameters,
180 string tableName )
181 {
182 DataSet dataSet = new DataSet();
183 SqlConnection connection = new SqlConnection(SqlHelper.connectionString);
184 try
185 {
186 connection.Open();
187 SqlDataAdapter sqlDA = new SqlDataAdapter();
188 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
189 sqlDA.Fill( dataSet, tableName );
190 }
191 finally
192 {
193 connection.Close();
194 }
195
196 return dataSet;
197 }
198
199 /// <summary>
200 /// 运行一个存储过程，并且结果集用DataSet形式返回
201 /// </summary>
202 /// <param name="storedProcName">存储过程名称</param>
203 /// <param name="parameters">存储过程参数</param>
204 /// <returns>存储过程的结果集，DataSet中的表名为Sql操作的数据表名</returns>
205 public static DataSet RunDataSetProcedure(string storedProcName, IDataParameter[] parameters)
206 {
207 DataSet dataSet = new DataSet();
208 SqlConnection connection = new SqlConnection(SqlHelper.connectionString);
209
210 try
211 {
212 connection.Open();
213 SqlDataAdapter sqlDA = new SqlDataAdapter();
214 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
215 sqlDA.Fill( dataSet);
216 }
217 finally
218 {
219 connection.Close();
220 }
221
222 return dataSet;
223 }
224
225 /// <summary>
226 /// Takes an -existing- dataset and fills the given table name with the results
227 /// of the stored procedure.
228 /// </summary>
229 /// <param name="storedProcName">存储过程名称</param>
230 /// <param name="parameters">存储过程参数</param>
231 /// <param name="dataSet">已有的DataSet,将向其中添加表数据</param>
232 /// <param name="tableName">将向DataSet中添加数据的表名称</param>
233 /// <returns>无</returns>
234 public static void RunDataSetProcedure(
235 string storedProcName,
236 IDataParameter[] parameters,
237 DataSet dataSet,
238 string tableName )
239 {
240 SqlConnection connection = new SqlConnection(SqlHelper.connectionString);
241 try
242 {
243 connection.Open();
244 SqlDataAdapter sqlDA = new SqlDataAdapter();
245 sqlDA.SelectCommand = BuildIntCommand( connection,storedProcName, parameters );
246 sqlDA.Fill( dataSet, tableName );
247 }
248 finally
249 {
250 connection.Close();
251 }
252 }
253
254 /// <summary>
255 /// 运行一个存储过程，并且结果集用DataTable形式返回,这是DataSet中的第一个表
256 /// </summary>
257 /// <param name="storedProcName">存储过程名字</param>
258 /// <param name="parameters">Sql参数</param>
259 /// <returns>结果集的第一个表</returns>
260 /// <remarks>不管结果集有多少个表，该方法仅仅返回结果集的第一个表.如果结果集不存在,返回null
261 /// </remarks>
262 public static DataTable RunDataTableProcedure(string storedProcName, IDataParameter[] parameters)
263 {
264 DataSet dataSet = RunDataSetProcedure(storedProcName,parameters);
265 if( dataSet!=null && dataSet.Tables.Count>0 )
266 {
267 return dataSet.Tables[0];
268 }
269 else
270 {
271 return null;
272 }
273 }
274
275 /// <summary>
276 /// 运行一个存储过程，并且结果集用DataRow形式返回,这是DataSet中的第一个表的第一行
277 /// </summary>
278 /// <param name="storedProcName">存储过程名字</param>
279 /// <param name="parameters">Sql参数</param>
280 /// <returns>结果集的第一个表的第一行</returns>
281 /// <remarks>不管结果集有多少行，该方法仅仅返回第一行,如果结果集不存在,返回null
282 /// </remarks>
283 public static DataRow RunDataRowProcedure(string storedProcName, IDataParameter[] parameters)
284 {
285 DataTable dataTable = RunDataTableProcedure(storedProcName,parameters);
286 if( dataTable!=null && dataTable.Rows.Count>0 )
287 {
288 return dataTable.Rows[0];
289 }
290 else
291 {
292 return null;
293 }
294 }
295
296 /// <summary>
297 /// 运行一个存储过程，并且结果集用DataTable形式返回,这是DataSet中的第一个表的第一行
298 /// </summary>
299 /// <param name="storedProcName">存储过程名字</param>
300 /// <param name="parameters">Sql参数</param>
301 /// <returns>结果集的第一个表的第一行的第一列</returns>
302 /// <remarks>
303 /// 不管结果集有多少行，该方法仅仅返回第一行的第一个值,如果结果集不存在,返回null
304 /// </remarks>
305 public static object RunScalarProcedure(string storedProcName, IDataParameter[] parameters)
306 {
307 DataRow row = RunDataRowProcedure(storedProcName,parameters);
308 if( row!=null && row.ItemArray.Length>0 )
309 {
310 return row.ItemArray[0];
311 }
312 else
313 {
314 return null;
315 }
316 }
317 }
318
}
using System;2
using System.Configuration;3
using System.Data;4
using System.Data.SqlClient;5
6
namespace DBModules7
{
8
/// <summary>
9
/// 数据库访问辅助类，该类中都是静态的方法，以更方便的调用存储过程10
/// </summary>11
public sealed class SqlHelper12
{13
/// <summary>14
/// 这里用私有函数，防止实例化该类15
/// </summary>16
private SqlHelper()17
{18
19
}20
/// <summary>21
/// 获取数据库连接字符串22
/// </summary>23
public static string connectionString 24
{25
get{ return ConfigurationSettings.AppSettings["connectString"];}26
}27
28
/// <summary>29
/// Private routine allowed only by this base class, it automates the task30
/// of building a SqlCommand object designed to obtain a return value from31
/// the stored procedure.32
/// </summary>33
/// <param name="storedProcName">Name of the stored procedure in the DB, eg. sp_DoTask</param>34
/// <param name="parameters">Array of IDataParameter objects containing parameters to the stored proc</param>35
/// <returns>Newly instantiated SqlCommand instance</returns>36
private static SqlCommand BuildIntCommand(37
SqlConnection connection,38
string storedProcName, 39
IDataParameter[] parameters)40
{41
SqlCommand command = 42
BuildQueryCommand( connection,storedProcName, parameters ); 43
44
command.Parameters.Add( new SqlParameter ( "ReturnValue",45
SqlDbType.Int,46
4, /* Size */47
ParameterDirection.ReturnValue,48
false, /* is nullable */49
0, /* byte precision */50
0, /* byte scale */51
string.Empty,52
DataRowVersion.Default,53
null ));54
55
return command;56
}57
58
/// <summary>59
/// Builds a SqlCommand designed to return a SqlDataReader, and not60
/// an actual integer value.61
/// </summary>62
/// <param name="storedProcName">Name of the stored procedure</param>63
/// <param name="parameters">Array of IDataParameter objects</param>64
/// <returns></returns>65
private static SqlCommand BuildQueryCommand(66
SqlConnection connection,67
string storedProcName, 68
IDataParameter[] parameters)69
{70
if(connectionString==null)71
throw new ApplicationException("Sql连接字符串connectionString没有初始化");72
73
SqlCommand command = new SqlCommand( storedProcName,connection );74
command.CommandType = CommandType.StoredProcedure;75
76
foreach (SqlParameter parameter in parameters)77
{78
command.Parameters.Add( parameter );79
}80
81
return command;82
83
}84
85
/// <summary>86
/// Runs a stored procedure, can only be called by those classes deriving87
/// from this base. It returns an integer indicating the return value of the88
/// stored procedure, and also returns the value of the RowsAffected aspect89
/// of the stored procedure that is returned by the ExecuteNonQuery method.90
/// </summary>91
/// <param name="storedProcName">Name of the stored procedure</param>92
/// <param name="parameters">Array of IDataParameter objects</param>93
/// <param name="rowsAffected">Number of rows affected by the stored procedure.</param>94
/// <returns>An integer indicating return value of the stored procedure</returns>95
public static int RunIntProcedure(96
string storedProcName, 97
IDataParameter[] parameters, 98
out int rowsAffected )99
{100
int result = 0; 101
rowsAffected = 0;102
SqlConnection connection = new SqlConnection(SqlHelper.connectionString);103
try104
{ 105
SqlCommand command = BuildIntCommand(connection,storedProcName, parameters );106
rowsAffected = command.ExecuteNonQuery();107
result = (int)command.Parameters["ReturnValue"].Value;108
}109
finally110
{111
connection.Close();112
}113
return result;114
}115
116
/// <summary>117
/// 运行存储过程，并且返回存储过程的结果118
/// </summary>119
/// <param name="storedProcName">Name of the stored procedure</param>120
/// <param name="parameters">Array of IDataParameter objects</param>121
/// <returns>An integer indicating return value of the stored procedure</returns>122
public static int RunProcedure(string storedProcName, IDataParameter[] parameters)123
{124
int result = 0;125
126
SqlConnection connection=new SqlConnection(SqlHelper.connectionString);127
try128
{129
connection.Open();130
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters );131
command.CommandType = CommandType.StoredProcedure; 132
command.ExecuteNonQuery();133
134
result = (int)command.Parameters["ReturnValue"].Value;135
}136
finally137
{138
connection.Close();139
}140
141
return result;142
}143
144
/// <summary>145
/// Will run a stored procedure, can only be called by those classes deriving146
/// from this base. It returns a SqlDataReader containing the result of the stored147
/// procedure.148
/// </summary>149
/// <param name="storedProcName">Name of the stored procedure</param>150
/// <param name="parameters">Array of parameters to be passed to the procedure</param>151
/// <returns>A newly instantiated SqlDataReader object</returns>152
/// <remarks>153
/// 返回的SqlDataReader保持了一个打开的连接，一定要记住用完SqlDataReader后调用close方法。154
/// </remarks>155
public static SqlDataReader RunDataReaderProcedure(string storedProcName, IDataParameter[] parameters )156
{157
SqlDataReader returnReader;158
SqlConnection connection = new SqlConnection(SqlHelper.connectionString);159
160
connection.Open();161
SqlCommand command = BuildQueryCommand( connection,storedProcName, parameters );162
command.CommandType = CommandType.StoredProcedure;163
164
returnReader = command.ExecuteReader();165
//connection.Close();166
return returnReader;167
}168
169
/// <summary>170
/// Creates a DataSet by running the stored procedure and placing the results171
/// of the query/proc into the given tablename.172
/// </summary>173
/// <param name="storedProcName">存储过程名称</param>174
/// <param name="parameters">存储过程参数</param>175
/// <param name="tableName">返回的DataSet中的Table的名称</param>176
/// <returns>存储过程的结果集</returns>177
public static DataSet RunDataSetProcedure(178
string storedProcName, 179
IDataParameter[] parameters, 180
string tableName )181
{182
DataSet dataSet = new DataSet();183
SqlConnection connection = new SqlConnection(SqlHelper.connectionString);184
try185
{186
connection.Open();187
SqlDataAdapter sqlDA = new SqlDataAdapter();188
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); 189
sqlDA.Fill( dataSet, tableName );190
}191
finally192
{193
connection.Close();194
}195
196
return dataSet;197
}198
199
/// <summary>200
/// 运行一个存储过程，并且结果集用DataSet形式返回201
/// </summary>202
/// <param name="storedProcName">存储过程名称</param>203
/// <param name="parameters">存储过程参数</param>204
/// <returns>存储过程的结果集，DataSet中的表名为Sql操作的数据表名</returns>205
public static DataSet RunDataSetProcedure(string storedProcName, IDataParameter[] parameters)206
{207
DataSet dataSet = new DataSet();208
SqlConnection connection = new SqlConnection(SqlHelper.connectionString);209
210
try211
{212
connection.Open();213
SqlDataAdapter sqlDA = new SqlDataAdapter();214
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );215
sqlDA.Fill( dataSet);216
}217
finally218
{219
connection.Close();220
}221
222
return dataSet;223
}224
225
/// <summary>226
/// Takes an -existing- dataset and fills the given table name with the results227
/// of the stored procedure.228
/// </summary>229
/// <param name="storedProcName">存储过程名称</param>230
/// <param name="parameters">存储过程参数</param>231
/// <param name="dataSet">已有的DataSet,将向其中添加表数据</param>232
/// <param name="tableName">将向DataSet中添加数据的表名称</param>233
/// <returns>无</returns>234
public static void RunDataSetProcedure(235
string storedProcName, 236
IDataParameter[] parameters, 237
DataSet dataSet, 238
string tableName )239
{240
SqlConnection connection = new SqlConnection(SqlHelper.connectionString);241
try242
{243
connection.Open();244
SqlDataAdapter sqlDA = new SqlDataAdapter();245
sqlDA.SelectCommand = BuildIntCommand( connection,storedProcName, parameters );246
sqlDA.Fill( dataSet, tableName );247
}248
finally249
{250
connection.Close();251
}252
}253
254
/// <summary>255
/// 运行一个存储过程，并且结果集用DataTable形式返回,这是DataSet中的第一个表256
/// </summary>257
/// <param name="storedProcName">存储过程名字</param>258
/// <param name="parameters">Sql参数</param>259
/// <returns>结果集的第一个表</returns>260
/// <remarks>不管结果集有多少个表，该方法仅仅返回结果集的第一个表.如果结果集不存在,返回null261
/// </remarks>262
public static DataTable RunDataTableProcedure(string storedProcName, IDataParameter[] parameters)263
{264
DataSet dataSet = RunDataSetProcedure(storedProcName,parameters);265
if( dataSet!=null && dataSet.Tables.Count>0 )266
{267
return dataSet.Tables[0];268
}269
else270
{271
return null;272
}273
}274
275
/// <summary>276
/// 运行一个存储过程，并且结果集用DataRow形式返回,这是DataSet中的第一个表的第一行277
/// </summary>278
/// <param name="storedProcName">存储过程名字</param>279
/// <param name="parameters">Sql参数</param>280
/// <returns>结果集的第一个表的第一行</returns>281
/// <remarks>不管结果集有多少行，该方法仅仅返回第一行,如果结果集不存在,返回null282
/// </remarks>283
public static DataRow RunDataRowProcedure(string storedProcName, IDataParameter[] parameters)284
{285
DataTable dataTable = RunDataTableProcedure(storedProcName,parameters);286
if( dataTable!=null && dataTable.Rows.Count>0 )287
{288
return dataTable.Rows[0];289
}290
else291
{292
return null;293
}294
}295
296
/// <summary>297
/// 运行一个存储过程，并且结果集用DataTable形式返回,这是DataSet中的第一个表的第一行298
/// </summary>299
/// <param name="storedProcName">存储过程名字</param>300
/// <param name="parameters">Sql参数</param>301
/// <returns>结果集的第一个表的第一行的第一列</returns>302
/// <remarks>303
/// 不管结果集有多少行，该方法仅仅返回第一行的第一个值,如果结果集不存在,返回null304
/// </remarks>305
public static object RunScalarProcedure(string storedProcName, IDataParameter[] parameters)306
{307
DataRow row = RunDataRowProcedure(storedProcName,parameters);308
if( row!=null && row.ItemArray.Length>0 )309
{310
return row.ItemArray[0];311
}312
else313
{314
return null;315
}316
}317
}318
}
浙公网安备 33010602011771号