1
using
System;
2 using System.Configuration;
3 using System.Data;
4 using System.Data.SqlClient;
5
6 namespace 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}
2 using System.Configuration;
3 using System.Data;
4 using System.Data.SqlClient;
5
6 namespace 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}