最近一段时间一直在总结过去一年中的经验教训，突然发现还没毕业那会做的项目：一平台，语言是c#，数据库用MYSQL，所以需要写一个类似于SQLHelper的类，虽然不再使用了，拿出来晒晒，说不定哪天会有人需要，也可以参考下。
View Code
1
/*
----------------------------------------------------------------
2
// File Name：MYSQLHelper.cs
3
// File Description：
4
* DataBase Deal Layer
5
*
6
// Create Mark：
7
* Create Date: 2011-04-14
8
* Create By: Mike.Jiang
9
// Modify Mark：
10
* Modify Date
11
* Modify By
12
//----------------------------------------------------------------
*/
13
using
System;
14
using
System.Collections.Generic;
15
using
System.Text;
16
using
System.Data;
17
using
System.Collections;
18
using
MySql.Data.MySqlClient;
19
using
System.Configuration;
20
using
System.IO;
21
22
23
namespace
PLA.DBUtility
24
{
25
///
<summary>
26
///
when use mysql database application this class
27
///
Generic database access code
28
///
this class that is a abstract,which does not allow instantiation, the application can directly call it
29
///
</summary>
30
public
abstract
class
MYSQLHelper
31
{
32
//
Get the database connectionstring, which are static variables and readonly, all project documents can be used directly, but can not modify it
33
//
the database connectionString
34
//
public static readonly string connectionStringManager = ConfigurationManager.ConnectionStrings["MYSQLConnectionString"].ConnectionString;
35
public
static
string
ConnectionStringManager {
36
get
{
return
connectionStringManager; }
37
}
38
39
40
41
//
This connectionString for the local test
42
public
static
readonly
string
connectionStringManager = ConfigurationManager.ConnectionStrings[
"
LocalMYSQLConnectionString
"
].ConnectionString;
43
44
//
hashtable to store the parameter information, the hash table can store any type of argument
45
//
Here the hashtable is static types of static variables, since it is static, that is a definition of global use.
46
//
All parameters are using this hash table, how to ensure that others in the change does not affect their time to read it
47
//
Before ,the method can use the lock method to lock the table, does not allow others to modify.when it has readed then unlocked table.
48
//
Now .NET provides a HashTable's Synchronized methods to achieve the same function, no need to manually lock, completed directly by the system framework
49
private
static
Hashtable parmCache = Hashtable.Synchronized(
new
Hashtable());
50
51
///
<summary>
52
///
Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
53
///
The parameter list using parameters that in array forms
54
///
</summary>
55
///
<remarks>
56
///
Usage example:
57
///
int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,
58
///
"PublishOrders", new MySqlParameter("@prodid", 24));
59
///
</remarks>
60
///
<param name="connectionString">
a valid database connectionstring
</param>
61
///
<param name="cmdType">
MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
</param>
62
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
63
///
<param name="commandParameters">
MySqlCommand to provide an array of parameters used in the list
</param>
64
///
<returns>
Returns a value that means number of rows affected/returns>
65
public
static
int
ExecuteNonQuery(
string
connectionString, CommandType cmdType,
string
cmdText,
params
MySqlParameter[] commandParameters)
66
{
67
MySqlCommand cmd =
new
MySqlCommand();
68
69
using
(MySqlConnection conn =
new
MySqlConnection(connectionString))
70
{
71
PrepareCommand(cmd, conn,
null
, cmdType, cmdText, commandParameters);
72
int
val =
cmd.ExecuteNonQuery();
73
cmd.Parameters.Clear();
74
return
val;
75
}
76
}
77
78
///
<summary>
79
///
Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
80
///
The parameter list using parameters that in array forms
81
///
</summary>
82
///
<remarks>
83
///
Usage example:
84
///
int result = ExecuteNonQuery(connString, CommandType.StoredProcedure,
85
///
"PublishOrders", new MySqlParameter("@prodid", 24));
86
///
</remarks>
87
///
<param name="cmdType">
MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
</param>
88
///
<param name="connectionString">
a valid database connectionstring
</param>
89
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
90
///
<param name="commandParameters">
MySqlCommand to provide an array of parameters used in the list
</param>
91
///
<returns>
Returns true or false
</returns>
92
public
static
bool
ExecuteNonQuery(CommandType cmdType,
string
connectionString,
string
cmdText,
params
MySqlParameter[] commandParameters)
93
{
94
MySqlCommand cmd =
new
MySqlCommand();
95
96
using
(MySqlConnection conn =
new
MySqlConnection(connectionString))
97
{
98
PrepareCommand(cmd, conn,
null
, cmdType, cmdText, commandParameters);
99
try
100
{
101
int
val =
cmd.ExecuteNonQuery();
102
return
true
;
103
}
104
catch
105
{
106
return
false
;
107
}
108
finally
109
{
110
cmd.Parameters.Clear();
111
}
112
}
113
}
114
///
<summary>
115
///
Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
116
///
Array of form parameters using the parameter list
117
///
</summary>
118
///
<param name="conn">
connection
</param>
119
///
<param name="cmdType">
MySqlCommand command type (stored procedures, T-SQL statement, and so on.)
</param>
120
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
121
///
<param name="commandParameters">
MySqlCommand to provide an array of parameters used in the list
</param>
122
///
<returns>
Returns a value that means number of rows affected
</returns>
123
public
static
int
ExecuteNonQuery(MySqlConnection conn, CommandType cmdType,
string
cmdText,
params
MySqlParameter[] commandParameters)
124
{
125
MySqlCommand cmd =
new
MySqlCommand();
126
PrepareCommand(cmd, conn,
null
, cmdType, cmdText, commandParameters);
127
int
val =
cmd.ExecuteNonQuery();
128
cmd.Parameters.Clear();
129
return
val;
130
}
131
132
///
<summary>
133
///
Execute a SqlCommand command that does not return value, by appointed and specified connectionstring
134
///
Array of form parameters using the parameter list
135
///
</summary>
136
///
<param name="conn">
sql Connection that has transaction
</param>
137
///
<param name="cmdType">
SqlCommand command type (stored procedures, T-SQL statement, and so on.)
</param>
138
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
139
///
<param name="commandParameters">
MySqlCommand to provide an array of parameters used in the list
</param>
140
///
<returns>
Returns a value that means number of rows affected
</returns>
141
public
static
int
ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType,
string
cmdText,
params
MySqlParameter[] commandParameters)
142
{
143
MySqlCommand cmd =
new
MySqlCommand();
144
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
145
int
val =
cmd.ExecuteNonQuery();
146
cmd.Parameters.Clear();
147
return
val;
148
}
149
150
///
<summary>
151
///
Call method of sqldatareader to read data
152
///
</summary>
153
///
<param name="connectionString">
connectionstring
</param>
154
///
<param name="cmdType">
command type, such as using stored procedures: CommandType.StoredProcedure
</param>
155
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
156
///
<param name="commandParameters">
parameters
</param>
157
///
<returns>
SqlDataReader type of data collection
</returns>
158
public
static
MySqlDataReader ExecuteReader(
string
connectionString, CommandType cmdType,
string
cmdText,
params
MySqlParameter[] commandParameters)
159
{
160
MySqlCommand cmd =
new
MySqlCommand();
161
MySqlConnection conn =
new
MySqlConnection(connectionString);
162
163
//
we use a try/catch here because if the method throws an exception we want to
164
//
close the connection throw code, because no datareader will exist, hence the
165
//
commandBehaviour.CloseConnection will not work
166
try
167
{
168
PrepareCommand(cmd, conn,
null
, cmdType, cmdText, commandParameters);
169
MySqlDataReader rdr =
cmd.ExecuteReader(CommandBehavior.CloseConnection);
170
cmd.Parameters.Clear();
171
return
rdr;
172
}
173
catch
174
{
175
conn.Close();
176
throw
;
177
}
178
}
179
180
///
<summary>
181
///
use the ExectueScalar to read a single result
182
///
</summary>
183
///
<param name="connectionString">
connectionstring
</param>
184
///
<param name="cmdType">
command type, such as using stored procedures: CommandType.StoredProcedure
</param>
185
///
<param name="cmdText">
stored procedure name or T-SQL statement
</param>
186
///
<param name="commandParameters">
parameters
</param>
187
///
<returns>
a value in object type
</returns>
188
public
static
object
ExecuteScalar(
string
connectionString, CommandType cmdType,
string
cmdText,
params
MySqlParameter[] commandParameters)
189
{
190
MySqlCommand cmd =
new
MySqlCommand();
191
192
using
(MySqlConnection connection =
new
MySqlConnection(connectionString))
193
{
194
PrepareCommand(cmd, connection,
null
, cmdType, cmdText, commandParameters);
195
object
val =
cmd.ExecuteScalar();
196
cmd.Parameters.Clear();
197
return
val;
198
}
199
}
200
201
public
static
DataSet GetDataSet(
string
connectionString,
string
cmdText,
params
MySqlParameter[] commandParameters)
202
{
203
DataSet retSet =
new
DataSet();
204
using
(MySqlDataAdapter msda =
new
MySqlDataAdapter(cmdText, connectionString))
205
{
206
msda.Fill(retSet);
207
}
208
return
retSet;
209
}
210
211
///
<summary>
212
///
cache the parameters in the HashTable
213
///
</summary>
214
///
<param name="cacheKey">
hashtable key name
</param>
215
///
<param name="commandParameters">
the parameters that need to cached
</param>
216
public
static
void
CacheParameters(
string
cacheKey,
params
MySqlParameter[] commandParameters)
217
{
218
parmCache[cacheKey] =
commandParameters;
219
}
220
221
///
<summary>
222
///
get parameters in hashtable by cacheKey
223
///
</summary>
224
///
<param name="cacheKey">
hashtable key name
</param>
225
///
<returns>
the parameters
</returns>
226
public
static
MySqlParameter[] GetCachedParameters(
string
cacheKey)
227
{
228
MySqlParameter[] cachedParms =
(MySqlParameter[])parmCache[cacheKey];
229
230
if
(cachedParms ==
null
)
231
return
null
;
232
233
MySqlParameter[] clonedParms =
new
MySqlParameter[cachedParms.Length];
234
235
for
(
int
i =
0
, j = cachedParms.Length; i < j; i++
)
236
clonedParms[i] =
(MySqlParameter)((ICloneable)cachedParms[i]).Clone();
237
238
return
clonedParms;
239
}
240
241
///
<summary>
242
///
Prepare parameters for the implementation of the command
243
///
</summary>
244
///
<param name="cmd">
mySqlCommand command
</param>
245
///
<param name="conn">
database connection that is existing
</param>
246
///
<param name="trans">
database transaction processing
</param>
247
///
<param name="cmdType">
SqlCommand command type (stored procedures, T-SQL statement, and so on.)
</param>
248
///
<param name="cmdText">
Command text, T-SQL statements such as Select * from Products
</param>
249
///
<param name="cmdParms">
return the command that has parameters
</param>
250
private
static
void
PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType,
string
cmdText, MySqlParameter[] cmdParms)
251
{
252
if
(conn.State !=
ConnectionState.Open)
253
conn.Open();
254
255
cmd.Connection =
conn;
256
cmd.CommandText =
cmdText;
257
258
if
(trans !=
null
)
259
cmd.Transaction =
trans;
260
261
cmd.CommandType =
cmdType;
262
263
if
(cmdParms !=
null
)
264
foreach
(MySqlParameter parm
in
cmdParms)
265
cmd.Parameters.Add(parm);
266
}
267
#region
parameters
268
///
<summary>
269
///
Set parameters
270
///
</summary>
271
///
<param name="ParamName">
parameter name
</param>
272
///
<param name="DbType">
data type
</param>
273
///
<param name="Size">
type size
</param>
274
///
<param name="Direction">
input or output
</param>
275
///
<param name="Value">
set the value
</param>
276
///
<returns>
Return parameters that has been assigned
</returns>
277
public
static
MySqlParameter CreateParam(
string
ParamName, MySqlDbType DbType, Int32 Size, ParameterDirection Direction,
object
Value)
278
{
279
MySqlParameter param;
280
281
282
if
(Size >
0
)
283
{
284
param =
new
MySqlParameter(ParamName, DbType, Size);
285
}
286
else
287
{
288
289
param =
new
MySqlParameter(ParamName, DbType);
290
}
291
292
293
param.Direction =
Direction;
294
if
(!(Direction == ParameterDirection.Output && Value ==
null
))
295
{
296
param.Value =
Value;
297
}
298
299
300
return
param;
301
}
302
303
///
<summary>
304
///
set Input parameters
305
///
</summary>
306
///
<param name="ParamName">
parameter names, such as:@ id
</param>
307
///
<param name="DbType">
parameter types, such as: MySqlDbType.Int
</param>
308
///
<param name="Size">
size parameters, such as: the length of character type for the 100
</param>
309
///
<param name="Value">
parameter value to be assigned
</param>
310
///
<returns>
Parameters
</returns>
311
public
static
MySqlParameter CreateInParam(
string
ParamName, MySqlDbType DbType,
int
Size,
object
Value)
312
{
313
return
CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
314
}
315
316
///
<summary>
317
///
Output parameters
318
///
</summary>
319
///
<param name="ParamName">
parameter names, such as:@ id
</param>
320
///
<param name="DbType">
parameter types, such as: MySqlDbType.Int
</param>
321
///
<param name="Size">
size parameters, such as: the length of character type for the 100
</param>
322
///
<param name="Value">
parameter value to be assigned
</param>
323
///
<returns>
Parameters
</returns>
324
public
static
MySqlParameter CreateOutParam(
string
ParamName, MySqlDbType DbType,
int
Size)
325
{
326
return
CreateParam(ParamName, DbType, Size, ParameterDirection.Output,
null
);
327
}
328
329
///
<summary>
330
///
Set return parameter value
331
///
</summary>
332
///
<param name="ParamName">
parameter names, such as:@ id
</param>
333
///
<param name="DbType">
parameter types, such as: MySqlDbType.Int
</param>
334
///
<param name="Size">
size parameters, such as: the length of character type for the 100
</param>
335
///
<param name="Value">
parameter value to be assigned
<</param>
336
///
<returns>
Parameters
</returns>
337
public
static
MySqlParameter CreateReturnParam(
string
ParamName, MySqlDbType DbType,
int
Size)
338
{
339
return
CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue,
null
);
340
}
341
342
///
<summary>
343
///
Generate paging storedProcedure parameters
344
///
</summary>
345
///
<param name="CurrentIndex">
CurrentPageIndex
</param>
346
///
<param name="PageSize">
pageSize
</param>
347
///
<param name="WhereSql">
query Condition
</param>
348
///
<param name="TableName">
tableName
</param>
349
///
<param name="Columns">
columns to query
</param>
350
///
<param name="Sort">
sort
</param>
351
///
<returns>
MySqlParameter collection
</returns>
352
public
static
MySqlParameter[] GetPageParm(
int
CurrentIndex,
int
PageSize,
string
WhereSql,
string
TableName,
string
Columns, Hashtable Sort)
353
{
354
MySqlParameter[] parm =
{
355
MYSQLHelper.CreateInParam(
"
@CurrentIndex
"
, MySqlDbType.Int32,
4
, CurrentIndex ),
356
MYSQLHelper.CreateInParam(
"
@PageSize
"
, MySqlDbType.Int32,
4
, PageSize ),
357
MYSQLHelper.CreateInParam(
"
@WhereSql
"
, MySqlDbType.VarChar,
2500
, WhereSql ),
358
MYSQLHelper.CreateInParam(
"
@TableName
"
, MySqlDbType.VarChar,
20
, TableName ),
359
MYSQLHelper.CreateInParam(
"
@Column
"
, MySqlDbType.VarChar,
2500
, Columns ),
360
MYSQLHelper.CreateInParam(
"
@Sort
"
, MySqlDbType.VarChar,
50
, GetSort(Sort) ),
361
MYSQLHelper.CreateOutParam(
"
@RecordCount
"
, MySqlDbType.Int32,
4
)
362
};
363
return
parm;
364
}
365
///
<summary>
366
///
Statistics data that in table
367
///
</summary>
368
///
<param name="TableName">
table name
</param>
369
///
<param name="Columns">
Statistics column
</param>
370
///
<param name="WhereSql">
conditions
</param>
371
///
<returns>
Set of parameters
</returns>
372
public
static
MySqlParameter[] GetCountParm(
string
TableName,
string
Columns,
string
WhereSql)
373
{
374
MySqlParameter[] parm =
{
375
MYSQLHelper.CreateInParam(
"
@TableName
"
, MySqlDbType.VarChar,
20
, TableName ),
376
MYSQLHelper.CreateInParam(
"
@CountColumn
"
, MySqlDbType.VarChar,
20
, Columns ),
377
MYSQLHelper.CreateInParam(
"
@WhereSql
"
, MySqlDbType.VarChar,
250
, WhereSql ),
378
MYSQLHelper.CreateOutParam(
"
@RecordCount
"
, MySqlDbType.Int32,
4
)
379
};
380
return
parm;
381
}
382
///
<summary>
383
///
Get the sql that is Sorted
384
///
</summary>
385
///
<param name="sort">
sort column and values
</param>
386
///
<returns>
SQL sort string
</returns>
387
private
static
string
GetSort(Hashtable sort)
388
{
389
string
str =
""
;
390
int
i =
0
;
391
if
(sort !=
null
&& sort.Count >
0
)
392
{
393
foreach
(DictionaryEntry de
in
sort)
394
{
395
i++
;
396
str += de.Key +
"
"
+
de.Value;
397
if
(i !=
sort.Count)
398
{
399
str +=
"
,
"
;
400
}
401
}
402
}
403
return
str;
404
}
405
406
///
<summary>
407
///
execute a trascation include one or more sql sentence(author:donne yin)
408
///
</summary>
409
///
<param name="connectionString"></param>
410
///
<param name="cmdType"></param>
411
///
<param name="cmdTexts"></param>
412
///
<param name="commandParameters"></param>
413
///
<returns>
execute trascation result(success: true | fail: false)
</returns>
414
public
static
bool
ExecuteTransaction(
string
connectionString, CommandType cmdType,
string
[] cmdTexts,
params
MySqlParameter[][] commandParameters)
415
{
416
MySqlConnection myConnection =
new
MySqlConnection(connectionString);
//
get the connection object
417
myConnection.Open();
//
open the connection
418
MySqlTransaction myTrans = myConnection.BeginTransaction();
//
begin a trascation
419
MySqlCommand cmd =
new
MySqlCommand();
420
cmd.Connection =
myConnection;
421
cmd.Transaction =
myTrans;
422
423
try
424
{
425
for
(
int
i =
0
;i<cmdTexts.Length; i++
)
426
{
427
PrepareCommand(cmd, myConnection,
null
, cmdType, cmdTexts[i], commandParameters[i]);
428
cmd.ExecuteNonQuery();
429
cmd.Parameters.Clear();
430
}
431
myTrans.Commit();
432
}
433
catch
434
{
435
myTrans.Rollback();
436
return
false
;
437
}
438
finally
439
{
440
myConnection.Close();
441
}
442
return
true
;
443
}
444
#endregion
445
}
446
}
