进一步封装: DbHelperSQL2

 

  1    /**//// <summary>
  2
  3    /// Enterprise Library 2.0 数据访问进一步封装类
  4
  5     /// Copyright (C) 2006-2008 LiTianPing
  6
  7     /// All rights reserved 
  8
  9     /// </summary>
 10
 11     public abstract class DbHelperSQL2
 12
 13     {        
 14
 15         public DbHelperSQL2()
 16
 17         {
 18
 19        }
 20
 21 
 22
 23        公用方法#region 公用方法
 24
 25        /**//// <summary>
 26
 27        /// 获取表某个字段的最大值
 28
 29        /// </summary>
 30
 31        /// <param name="FieldName"></param>
 32
 33        /// <param name="TableName"></param>
 34
 35        /// <returns></returns>
 36
 37        public static int GetMaxID(string FieldName,string TableName)
 38
 39        {
 40
 41            string strSql = "select max(" + FieldName + ")+1 from " + TableName;
 42
 43            Database db = DatabaseFactory.CreateDatabase();
 44
 45            DbCommand dbCommand = db.GetSqlStringCommand(strSql);           
 46
 47            object obj = db.ExecuteScalar(dbCommand);
 48
 49            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 50
 51            {
 52
 53                return 1;
 54
 55            }
 56
 57            else
 58
 59            {
 60
 61                return int.Parse(obj.ToString());
 62
 63            }           
 64
 65        }
 66
 67        /**//// <summary>
 68
 69        /// 检测一个记录是否存在(SQL语句方式)
 70
 71        /// </summary>
 72
 73        /// <param name="strSql"></param>
 74
 75        /// <returns></returns>
 76
 77         public static bool Exists(string strSql)
 78
 79         {
 80
 81            Database db = DatabaseFactory.CreateDatabase();
 82
 83            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 84
 85            object obj = db.ExecuteScalar(dbCommand);
 86
 87              int cmdresult;
 88
 89              if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 90
 91              {
 92
 93                   cmdresult = 0;
 94
 95              }
 96
 97              else
 98
 99              {
100
101                   cmdresult = int.Parse(obj.ToString());
102
103              }
104
105              if (cmdresult == 0)
106
107              {
108
109                   return false;
110
111              }
112
113              else
114
115              {
116
117                   return true;
118
119              }
120
121         }
122
123        /**//// <summary>
124
125        ///  检测一个记录是否存在(SqlParameter语句方式)
126
127        /// </summary>
128
129        /// <param name="strSql"></param>
130
131        /// <param name="cmdParms"></param>
132
133        /// <returns></returns>
134
135        public static bool Exists(string strSql, params SqlParameter[] cmdParms)
136
137        {
138
139            Database db = DatabaseFactory.CreateDatabase();
140
141            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
142
143            BuildDBParameter(db, dbCommand, cmdParms);            
144
145            object obj = db.ExecuteScalar(dbCommand);           
146
147            int cmdresult;
148
149            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
150
151            {
152
153                cmdresult = 0;
154
155            }
156
157            else
158
159            {
160
161                cmdresult = int.Parse(obj.ToString());
162
163            }
164
165            if (cmdresult == 0)
166
167            {
168
169                return false;
170
171            }
172
173            else
174
175            {
176
177                return true;
178
179            }
180
181        }
182
183        
184
185        /**//// <summary>
186
187        /// 加载参数
188
189        /// </summary>
190
191        public static void BuildDBParameter(Database db, DbCommand dbCommand, params SqlParameter[] cmdParms)
192
193        {
194
195            foreach (SqlParameter sp in cmdParms)
196
197            {
198
199                db.AddInParameter(dbCommand, sp.ParameterName, sp.DbType,sp.Value);
200
201            }
202
203        }
204
205        #endregion
206
207

  1执行简单SQL语句#region  执行简单SQL语句
  2
  3 
  4
  5        /**//// <summary>
  6
  7         /// 执行SQL语句，返回影响的记录数
  8
  9         /// </summary>
 10
 11         /// <param name="strSql">SQL语句</param>
 12
 13         /// <returns>影响的记录数</returns>
 14
 15        public static int ExecuteSql(string strSql)
 16
 17         {    
 18
 19            Database db = DatabaseFactory.CreateDatabase();
 20
 21            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 22
 23            return db.ExecuteNonQuery(dbCommand);
 24
 25         }
 26
 27        /**//// <summary>
 28
 29        /// 执行SQL语句，返回影响的记录数(对于长时间查询的语句，设置等待时间避免查询超时)
 30
 31        /// </summary>
 32
 33        /// <param name="strSql"></param>
 34
 35        /// <param name="Times"></param>
 36
 37        /// <returns></returns>
 38
 39         public static int ExecuteSqlByTime(string strSql,int Times)
 40
 41         {           
 42
 43            Database db = DatabaseFactory.CreateDatabase();
 44
 45            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 46
 47            dbCommand.CommandTimeout = Times;
 48
 49            return db.ExecuteNonQuery(dbCommand);
 50
 51         }
 52
 53         
 54
 55         /**//// <summary>
 56
 57         /// 执行多条SQL语句，实现数据库事务。
 58
 59         /// </summary>
 60
 61         /// <param name="SQLStringList">多条SQL语句</param>     
 62
 63         public static void ExecuteSqlTran(ArrayList SQLStringList)
 64
 65         {
 66
 67 
 68
 69            Database db = DatabaseFactory.CreateDatabase();
 70
 71            using (DbConnection dbconn = db.CreateConnection())
 72
 73            { 
 74
 75                dbconn.Open();
 76
 77                DbTransaction dbtran = dbconn.BeginTransaction();
 78
 79                try
 80
 81                {
 82
 83                    //执行语句
 84
 85                    for (int n = 0; n < SQLStringList.Count; n++)
 86
 87                    {
 88
 89                        string strsql = SQLStringList[n].ToString();
 90
 91                        if (strsql.Trim().Length > 1)
 92
 93                        {
 94
 95                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
 96
 97                            db.ExecuteNonQuery(dbCommand);
 98
 99                        }
100
101                    }
102
103                    //执行存储过程
104
105                    //db.ExecuteNonQuery(CommandType.StoredProcedure, "InserOrders");
106
107                    //db.ExecuteDataSet(CommandType.StoredProcedure, "UpdateProducts");
108
109                    dbtran.Commit();
110
111                }
112
113                catch
114
115                {
116
117                    dbtran.Rollback();
118
119                }
120
121                finally
122
123                {
124
125                    dbconn.Close();
126
127                }
128
129            }
130
131        }
132
133 
134
135        执行一个特殊字段带参数的语句#region 执行一个特殊字段带参数的语句
136
137        /**//// <summary>
138
139         /// 执行带一个存储过程参数的的SQL语句。
140
141         /// </summary>
142
143         /// <param name="strSql">SQL语句</param>
144
145         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
146
147         /// <returns>影响的记录数</returns>
148
149         public static int ExecuteSql(string strSql,string content)
150
151         {    
152
153            Database db = DatabaseFactory.CreateDatabase();
154
155            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
156
157            db.AddInParameter(dbCommand, "@content", DbType.String, content);
158
159            return db.ExecuteNonQuery(dbCommand);
160
161         }        
162
163         
164
165        /**//// <summary>
166
167         /// 执行带一个存储过程参数的的SQL语句。
168
169         /// </summary>
170
171         /// <param name="strSql">SQL语句</param>
172
173         /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
174
175         /// <returns>返回语句里的查询结果</returns>
176
177         public static object ExecuteSqlGet(string strSql,string content)
178
179         {
180
181            Database db = DatabaseFactory.CreateDatabase();
182
183            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
184
185            db.AddInParameter(dbCommand, "@content", DbType.String, content);
186
187            object obj = db.ExecuteNonQuery(dbCommand);
188
189            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
190
191            {
192
193                return null;
194
195            }
196
197            else
198
199            {
200
201                return obj;
202
203            }      
204
205         }        
206
207         
208
209        /**//// <summary>
210
211         /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
212
213         /// </summary>
214
215         /// <param name="strSql">SQL语句</param>
216
217         /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
218
219         /// <returns>影响的记录数</returns>
220
221         public static int ExecuteSqlInsertImg(string strSql,byte[] fs)
222
223         {
224
225            Database db = DatabaseFactory.CreateDatabase();
226
227            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
228
229            db.AddInParameter(dbCommand, "@fs", DbType.Byte, fs);
230
231            return db.ExecuteNonQuery(dbCommand);            
232
233        }
234
235        #endregion
236
237

  1 /**//// <summary>
  2
  3         /// 执行一条计算查询结果语句，返回查询结果（object）。
  4
  5         /// </summary>
  6
  7         /// <param name="strSql">计算查询结果语句</param>
  8
  9         /// <returns>查询结果（object）</returns>
 10
 11         public static object GetSingle(string strSql)
 12
 13         {            
 14
 15            Database db = DatabaseFactory.CreateDatabase();
 16
 17            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 18
 19            object obj = db.ExecuteScalar(dbCommand);
 20
 21            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 22
 23            {
 24
 25                return null;
 26
 27            }
 28
 29            else
 30
 31            {
 32
 33                return obj;
 34
 35            }      
 36
 37         }
 38
 39         
 40
 41        /**//// <summary>
 42
 43        /// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
 44
 45         /// </summary>
 46
 47         /// <param name="strSql">查询语句</param>
 48
 49         /// <returns>SqlDataReader</returns>
 50
 51        public static SqlDataReader ExecuteReader(string strSql)
 52
 53         {
 54
 55            Database db = DatabaseFactory.CreateDatabase();
 56
 57            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 58
 59            SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);
 60
 61            return dr;      
 62
 63              
 64
 65         }        
 66
 67         
 68
 69        /**//// <summary>
 70
 71         /// 执行查询语句，返回DataSet
 72
 73         /// </summary>
 74
 75         /// <param name="strSql">查询语句</param>
 76
 77         /// <returns>DataSet</returns>
 78
 79         public static DataSet Query(string strSql)
 80
 81         {            
 82
 83            Database db = DatabaseFactory.CreateDatabase();
 84
 85            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
 86
 87            return db.ExecuteDataSet(dbCommand);
 88
 89            
 90
 91         }
 92
 93        //(对于长时间查询的语句，设置等待时间避免查询超时)
 94
 95         public static DataSet Query(string strSql,int Times)
 96
 97         {
 98
 99            Database db = DatabaseFactory.CreateDatabase();
100
101            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
102
103            dbCommand.CommandTimeout = Times;
104
105            return db.ExecuteDataSet(dbCommand);
106
107         }
108
109 
110
111         #endregion
112
113 
114
115         执行带参数的SQL语句#region 执行带参数的SQL语句
116
117 
118
119         /**//// <summary>
120
121         /// 执行SQL语句，返回影响的记录数
122
123         /// </summary>
124
125         /// <param name="strSql">SQL语句</param>
126
127         /// <returns>影响的记录数</returns>
128
129         public static int ExecuteSql(string strSql,params SqlParameter[] cmdParms)
130
131         {
132
133            Database db = DatabaseFactory.CreateDatabase();
134
135            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
136
137            BuildDBParameter(db, dbCommand, cmdParms);    
138
139            return db.ExecuteNonQuery(dbCommand);
140
141         }
142
143         
144
145              
146
147         /**//// <summary>
148
149         /// 执行多条SQL语句，实现数据库事务。
150
151         /// </summary>
152
153         /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
154
155         public static void ExecuteSqlTran(Hashtable SQLStringList)
156
157         {
158
159            Database db = DatabaseFactory.CreateDatabase();
160
161            using (DbConnection dbconn = db.CreateConnection())
162
163            {
164
165                dbconn.Open();
166
167                DbTransaction dbtran = dbconn.BeginTransaction();
168
169                try
170
171                {
172
173                    //执行语句
174
175                    foreach (DictionaryEntry myDE in SQLStringList)
176
177                    {
178
179                        string strsql = myDE.Key.ToString();
180
181                        SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;                        
182
183                        if (strsql.Trim().Length > 1)
184
185                        {
186
187                            DbCommand dbCommand = db.GetSqlStringCommand(strsql);
188
189                            BuildDBParameter(db, dbCommand, cmdParms);    
190
191                            db.ExecuteNonQuery(dbCommand);
192
193                        }
194
195                    }
196
197                    dbtran.Commit();
198
199                }
200
201                catch
202
203                {
204
205                    dbtran.Rollback();
206
207                }
208
209                finally
210
211                {
212
213                    dbconn.Close();
214
215                }
216
217            }
218
219         }
220
221     
222
223                   
224
225         /**//// <summary>
226
227         /// 执行一条计算查询结果语句，返回查询结果（object）。
228
229         /// </summary>
230
231         /// <param name="strSql">计算查询结果语句</param>
232
233         /// <returns>查询结果（object）</returns>
234
235         public static object GetSingle(string strSql,params SqlParameter[] cmdParms)
236
237         {
238
239            Database db = DatabaseFactory.CreateDatabase();
240
241            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
242
243            BuildDBParameter(db, dbCommand, cmdParms);    
244
245            object obj = db.ExecuteScalar(dbCommand);
246
247            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
248
249            {
250
251                return null;
252
253            }
254
255            else
256
257            {
258
259                return obj;
260
261            }      
262
263         }
264
265         
266
267         /**//// <summary>
268
269        /// 执行查询语句，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
270
271         /// </summary>
272
273         /// <param name="strSql">查询语句</param>
274
275         /// <returns>SqlDataReader</returns>
276
277         public static SqlDataReader ExecuteReader(string strSql,params SqlParameter[] cmdParms)
278
279         {        
280
281            Database db = DatabaseFactory.CreateDatabase();
282
283            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
284
285            BuildDBParameter(db, dbCommand, cmdParms);
286
287            SqlDataReader dr = (SqlDataReader)db.ExecuteReader(dbCommand);
288
289            return dr;
290
291              
292
293         }        
294
295         
296
297         /**//// <summary>
298
299         /// 执行查询语句，返回DataSet
300
301         /// </summary>
302
303         /// <param name="strSql">查询语句</param>
304
305         /// <returns>DataSet</returns>
306
307         public static DataSet Query(string strSql,params SqlParameter[] cmdParms)
308
309         {
310
311            Database db = DatabaseFactory.CreateDatabase();
312
313            DbCommand dbCommand = db.GetSqlStringCommand(strSql);
314
315            BuildDBParameter(db, dbCommand, cmdParms);   
316
317            return db.ExecuteDataSet(dbCommand);
318
319         }
320
321 
322
323 
324
325         private static void PrepareCommand(SqlCommand cmd,SqlConnection conn,SqlTransaction trans, string cmdText, SqlParameter[] cmdParms) 
326
327         {
328
329              if (conn.State != ConnectionState.Open)
330
331                   conn.Open();
332
333              cmd.Connection = conn;
334
335              cmd.CommandText = cmdText;
336
337              if (trans != null)
338
339                   cmd.Transaction = trans;
340
341              cmd.CommandType = CommandType.Text;//cmdType;
342
343              if (cmdParms != null) 
344
345              {
346
347                foreach (SqlParameter parameter in cmdParms)
348
349                   {
350
351                       if ( ( parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input ) && 
352
353                            (parameter.Value == null))
354
355                       {
356
357                            parameter.Value = DBNull.Value;
358
359                       }
360
361                       cmd.Parameters.Add(parameter);
362
363                   }
364
365              }
366
367         }
368
369 
370
371         #endregion
372
373 
374
375         存储过程操作#region 存储过程操作
376
377 
378
379        /**//// <summary>
380
381        /// 执行存储过程，返回影响的行数       
382
383        /// </summary>       
384
385        public static int RunProcedure(string storedProcName)
386
387        {
388
389            Database db = DatabaseFactory.CreateDatabase();
390
391            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
392
393            return db.ExecuteNonQuery(dbCommand);
394
395        }
396
397 
398
399        /**//// <summary>
400
401        /// 执行存储过程，返回输出参数的值和影响的行数       
402
403        /// </summary>
404
405        /// <param name="storedProcName">存储过程名</param>
406
407        /// <param name="parameters">存储过程参数</param>
408
409        /// <param name="OutParameter">输出参数名称</param>
410
411        /// <param name="rowsAffected">影响的行数</param>
412
413        /// <returns></returns>
414
415        public static object RunProcedure(string storedProcName, IDataParameter[] InParameters, SqlParameter OutParameter, int rowsAffected)
416
417        {
418
419            Database db = DatabaseFactory.CreateDatabase();
420
421            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName);
422
423            BuildDBParameter(db, dbCommand, (SqlParameter[])InParameters);
424
425            db.AddOutParameter(dbCommand, OutParameter.ParameterName, OutParameter.DbType, OutParameter.Size);
426
427            rowsAffected = db.ExecuteNonQuery(dbCommand);
428
429            return db.GetParameterValue(dbCommand,"@" + OutParameter.ParameterName);  //得到输出参数的值
430
431        }
432
433 
434
435         /**//// <summary>
436
437        /// 执行存储过程，返回SqlDataReader ( 注意：使用后一定要对SqlDataReader进行Close )
438
439         /// </summary>
440
441         /// <param name="storedProcName">存储过程名</param>
442
443         /// <param name="parameters">存储过程参数</param>
444
445         /// <returns>SqlDataReader</returns>
446
447         public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
448
449         {
450
451            Database db = DatabaseFactory.CreateDatabase();
452
453            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName, parameters);            
454
455            //BuildDBParameter(db, dbCommand, parameters);
456
457            return (SqlDataReader)db.ExecuteReader(dbCommand);           
458
459         }
460
461                   
462
463         /**//// <summary>
464
465        /// 执行存储过程，返回DataSet
466
467         /// </summary>
468
469         /// <param name="storedProcName">存储过程名</param>
470
471         /// <param name="parameters">存储过程参数</param>
472
473         /// <param name="tableName">DataSet结果中的表名</param>
474
475         /// <returns>DataSet</returns>
476
477         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
478
479         {           
480
481            Database db = DatabaseFactory.CreateDatabase();
482
483            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName, parameters);
484
485            //BuildDBParameter(db, dbCommand, parameters);
486
487            return db.ExecuteDataSet(dbCommand); 
488
489         }
490
491        /**//// <summary>
492
493        /// 执行存储过程，返回DataSet(设定等待时间)
494
495        /// </summary>
496
497         public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ,int Times)
498
499         {           
500
501            Database db = DatabaseFactory.CreateDatabase();
502
503            DbCommand dbCommand = db.GetStoredProcCommand(storedProcName, parameters);
504
505            dbCommand.CommandTimeout = Times;
506
507            //BuildDBParameter(db, dbCommand, parameters);
508
509            return db.ExecuteDataSet(dbCommand); 
510
511         }
512
513 
514
515         
516
517         /**//// <summary>
518
519         /// 构建SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
520
521         /// </summary>
522
523         /// <param name="connection">数据库连接</param>
524
525         /// <param name="storedProcName">存储过程名</param>
526
527         /// <param name="parameters">存储过程参数</param>
528
529         /// <returns>SqlCommand</returns>
530
531         private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
532
533         {             
534
535              SqlCommand command = new SqlCommand( storedProcName, connection );
536
537              command.CommandType = CommandType.StoredProcedure;
538
539              foreach (SqlParameter parameter in parameters)
540
541              {
542
543                   if( parameter != null )
544
545                   {
546
547                       // 检查未分配值的输出参数,将其分配以DBNull.Value.
548
549                       if ( ( parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input ) && 
550
551                            (parameter.Value == null))
552
553                       {
554
555                            parameter.Value = DBNull.Value;
556
557                       }
558
559                       command.Parameters.Add(parameter);
560
561                   }
562
563              }             
564
565              return command;             
566
567         }        
568
569         /**//// <summary>
570
571         /// 创建SqlCommand 对象实例(用来返回一个整数值)    
572
573         /// </summary>
574
575         /// <param name="storedProcName">存储过程名</param>
576
577         /// <param name="parameters">存储过程参数</param>
578
579         /// <returns>SqlCommand 对象实例</returns>
580
581         private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
582
583         {
584
585              SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters );
586
587              command.Parameters.Add( new SqlParameter ( "ReturnValue",
588
589                   SqlDbType.Int,4,ParameterDirection.ReturnValue,
590
591                   false,0,0,string.Empty,DataRowVersion.Default,null ));
592
593              return command;
594
595         }
596
597         #endregion    
598
599 
600
601     }
602
603