当前文件： SinaVote/DBUtility/DbHelperOra.cs[17K，2009-6-12 11:54:10]，打开代码结构图

		            
  1using System;
  2using System.Collections;
  3using System.Collections.Specialized;
  4using System.Data;
  5using System.Data.OracleClient;
  6using System.Configuration;
  7
  8namespace Maticsoft.DBUtility
  9...{
 10    /**//// <summary>
 11    /// Copyright (C) 2004-2008 LiTianPing 
 12    /// 数据访问基础类(基于Oracle)
 13    /// 可以用户可以修改满足自己项目的需要。
 14    /// </summary>
 15    public abstract class DbHelperOra
 16    ...{
 17        //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        
 18        public static string connectionString = PubConstant.ConnectionString;     
 19        public DbHelperOra()
 20        ...{            
 21        }
 22        公用方法#region 公用方法
 23        
 24        public static int GetMaxID(string FieldName, string TableName)
 25        ...{
 26            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
 27            object obj = DbHelperOra.GetSingle(strsql);
 28            if (obj == null)
 29            ...{
 30                return 1;
 31            }
 32            else
 33            ...{
 34                return int.Parse(obj.ToString());
 35            }
 36        }
 37        public static bool Exists(string strSql)
 38        ...{
 39            object obj = DbHelperOra.GetSingle(strSql);
 40            int cmdresult;
 41            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 42            ...{
 43                cmdresult = 0;
 44            }
 45            else
 46            ...{
 47                cmdresult = int.Parse(obj.ToString());
 48            }
 49            if (cmdresult == 0)
 50            ...{
 51                return false;
 52            }
 53            else
 54            ...{
 55                return true;
 56            }
 57        }
 58
 59        public static bool Exists(string strSql, params OracleParameter[] cmdParms)
 60        ...{
 61            object obj = DbHelperOra.GetSingle(strSql, cmdParms);
 62            int cmdresult;
 63            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 64            ...{
 65                cmdresult = 0;
 66            }
 67            else
 68            ...{
 69                cmdresult = int.Parse(obj.ToString());
 70            }
 71            if (cmdresult == 0)
 72            ...{
 73                return false;
 74            }
 75            else
 76            ...{
 77                return true;
 78            }
 79        }
 80        #endregion
 81
 82        执行简单SQL语句#region  执行简单SQL语句
 83
 84        /**//// <summary>
 85        /// 执行SQL语句，返回影响的记录数
 86        /// </summary>
 87        /// <param name="SQLString">SQL语句</param>
 88        /// <returns>影响的记录数</returns>
 89        public static int ExecuteSql(string SQLString)
 90        ...{
 91            using (OracleConnection connection = new OracleConnection(connectionString))
 92            ...{                
 93                using (OracleCommand cmd = new OracleCommand(SQLString,connection))
 94                ...{
 95                    try
 96                    ...{        
 97                        connection.Open();
 98                        int rows=cmd.ExecuteNonQuery();
 99                        return rows;
100                    }
101                    catch(System.Data.OracleClient.OracleException E)
102                    ...{                    
103                        connection.Close();
104                        throw new Exception(E.Message);
105                    }
106                    finally
107                    ...{
108                        cmd.Dispose();
109                        connection.Close();
110                    }
111                }                
112            }
113        }
114        
115        /**//// <summary>
116        /// 执行多条SQL语句，实现数据库事务。
117        /// </summary>
118        /// <param name="SQLStringList">多条SQL语句</param>        
119        public static void ExecuteSqlTran(ArrayList SQLStringList)
120        ...{
121            using (OracleConnection connection = new OracleConnection(connectionString))
122            ...{
123                connection.Open();
124                OracleCommand cmd = new OracleCommand();
125                cmd.Connection = connection;
126                OracleTransaction tx = connection.BeginTransaction();            
127                cmd.Transaction=tx;                
128                try
129                ...{           
130                    for(int n=0;n<SQLStringList.Count;n++)
131                    ...{
132                        string strsql=SQLStringList[n].ToString();
133                        if (strsql.Trim().Length>1)
134                        ...{
135                            cmd.CommandText=strsql;
136                            cmd.ExecuteNonQuery();
137                        }
138                    }                                        
139                    tx.Commit();                    
140                }
141                catch(System.Data.OracleClient.OracleException E)
142                ...{        
143                    tx.Rollback();
144                    throw new Exception(E.Message);
145                }
146                finally
147                ...{
148                    cmd.Dispose();
149                    connection.Close();
150                }
151            }
152        }
153        /**//// <summary>
154        /// 执行带一个存储过程参数的的SQL语句。
155        /// </summary>
156        /// <param name="SQLString">SQL语句</param>
157        /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
158        /// <returns>影响的记录数</returns>
159        public static int ExecuteSql(string SQLString,string content)
160        ...{                
161            using (OracleConnection connection = new OracleConnection(connectionString))
162            ...{
163                OracleCommand cmd = new OracleCommand(SQLString,connection);
164                System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@content", OracleType.NVarChar);
165                myParameter.Value = content ;
166                cmd.Parameters.Add(myParameter);
167                try
168                ...{
169                    connection.Open();
170                    int rows=cmd.ExecuteNonQuery();
171                    return rows;
172                }
173                catch(System.Data.OracleClient.OracleException E)
174                ...{                
175                    throw new Exception(E.Message);
176                }
177                finally
178                ...{
179                    cmd.Dispose();
180                    connection.Close();
181                }    
182            }
183        }        
184        /**//// <summary>
185        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
186        /// </summary>
187        /// <param name="strSQL">SQL语句</param>
188        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
189        /// <returns>影响的记录数</returns>
190        public static int ExecuteSqlInsertImg(string strSQL,byte[] fs)
191        ...{        
192            using (OracleConnection connection = new OracleConnection(connectionString))
193            ...{
194                OracleCommand cmd = new OracleCommand(strSQL,connection);
195                System.Data.OracleClient.OracleParameter myParameter = new System.Data.OracleClient.OracleParameter("@fs", OracleType.LongRaw);
196                myParameter.Value = fs ;
197                cmd.Parameters.Add(myParameter);
198                try
199                ...{
200                    connection.Open();
201                    int rows=cmd.ExecuteNonQuery();
202                    return rows;
203                }
204                catch(System.Data.OracleClient.OracleException E)
205                ...{                
206                    throw new Exception(E.Message);
207                }
208                finally
209                ...{
210                    cmd.Dispose();
211                    connection.Close();
212                }                
213            }
214        }
215        
216        /**//// <summary>
217        /// 执行一条计算查询结果语句，返回查询结果（object）。
218        /// </summary>
219        /// <param name="SQLString">计算查询结果语句</param>
220        /// <returns>查询结果（object）</returns>
221        public static object GetSingle(string SQLString)
222        ...{
223            using (OracleConnection connection = new OracleConnection(connectionString))
224            ...{
225                using(OracleCommand cmd = new OracleCommand(SQLString,connection))
226                ...{
227                    try
228                    ...{
229                        connection.Open();
230                        object obj = cmd.ExecuteScalar();
231                        if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
232                        ...{                    
233                            return null;
234                        }
235                        else
236                        ...{
237                            return obj;
238                        }                
239                    }
240                    catch(System.Data.OracleClient.OracleException e)
241                    ...{                        
242                        connection.Close();
243                        throw new Exception(e.Message);
244                    }
245                    finally
246                    ...{
247                        cmd.Dispose();
248                        connection.Close();
249                    }
250                }
251            }
252        }
253        /**//// <summary>
254        /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
255        /// </summary>
256        /// <param name="strSQL">查询语句</param>
257        /// <returns>OracleDataReader</returns>
258        public static OracleDataReader ExecuteReader(string strSQL)
259        ...{
260            OracleConnection connection = new OracleConnection(connectionString);            
261            OracleCommand cmd = new OracleCommand(strSQL,connection);                
262            try
263            ...{
264                connection.Open();
265                OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
266                return myReader;
267            }
268            catch(System.Data.OracleClient.OracleException e)
269            ...{                                
270                throw new Exception(e.Message);
271            }    
272        
273            
274        }        
275        /**//// <summary>
276        /// 执行查询语句，返回DataSet
277        /// </summary>
278        /// <param name="SQLString">查询语句</param>
279        /// <returns>DataSet</returns>
280        public static DataSet Query(string SQLString)
281        ...{
282            using (OracleConnection connection = new OracleConnection(connectionString))
283            ...{
284                DataSet ds = new DataSet();
285                try
286                ...{
287                    connection.Open();
288                    OracleDataAdapter command = new OracleDataAdapter(SQLString,connection);                
289                    command.Fill(ds,"ds");
290                }
291                catch(System.Data.OracleClient.OracleException ex)
292                ...{                
293                    throw new Exception(ex.Message);
294                }
295                finally
296                ...{                   
297                    connection.Close();
298                }
299                return ds;
300            }            
301        }
302
303
304        #endregion
305
306        执行带参数的SQL语句#region 执行带参数的SQL语句
307
308        /**//// <summary>
309        /// 执行SQL语句，返回影响的记录数
310        /// </summary>
311        /// <param name="SQLString">SQL语句</param>
312        /// <returns>影响的记录数</returns>
313        public static int ExecuteSql(string SQLString,params OracleParameter[] cmdParms)
314        ...{
315            using (OracleConnection connection = new OracleConnection(connectionString))
316            ...{                
317                using (OracleCommand cmd = new OracleCommand())
318                ...{
319                    try
320                    ...{        
321                        PrepareCommand(cmd, connection, null,SQLString, cmdParms);
322                        int rows=cmd.ExecuteNonQuery();
323                        cmd.Parameters.Clear();
324                        return rows;
325                    }
326                    catch(System.Data.OracleClient.OracleException E)
327                    ...{                
328                        throw new Exception(E.Message);
329                    }
330                    finally
331                    ...{
332                        cmd.Dispose();
333                        connection.Close();
334                    }
335                }                
336            }
337        }
338        
339            
340        /**//// <summary>
341        /// 执行多条SQL语句，实现数据库事务。
342        /// </summary>
343        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
344        public static void ExecuteSqlTran(Hashtable SQLStringList)
345        ...{            
346            using (OracleConnection conn = new OracleConnection(connectionString))
347            ...{
348                conn.Open();
349                using (OracleTransaction trans = conn.BeginTransaction()) 
350                ...{
351                    OracleCommand cmd = new OracleCommand();
352                    try 
353                    ...{
354                        //循环
355                        foreach (DictionaryEntry myDE in SQLStringList)
356                        ...{    
357                            string     cmdText=myDE.Key.ToString();
358                            OracleParameter[] cmdParms=(OracleParameter[])myDE.Value;
359                            PrepareCommand(cmd,conn,trans,cmdText, cmdParms);
360                            int val = cmd.ExecuteNonQuery();
361                            cmd.Parameters.Clear();
362
363                            trans.Commit();
364                        }                    
365                    }
366                    catch 
367                    ...{
368                        trans.Rollback();
369                        throw;
370                    }
371                }                
372            }
373        }
374    
375                
376        /**//// <summary>
377        /// 执行一条计算查询结果语句，返回查询结果（object）。
378        /// </summary>
379        /// <param name="SQLString">计算查询结果语句</param>
380        /// <returns>查询结果（object）</returns>
381        public static object GetSingle(string SQLString,params OracleParameter[] cmdParms)
382        ...{
383            using (OracleConnection connection = new OracleConnection(connectionString))
384            ...{
385                using (OracleCommand cmd = new OracleCommand())
386                ...{
387                    try
388                    ...{
389                        PrepareCommand(cmd, connection, null,SQLString, cmdParms);
390                        object obj = cmd.ExecuteScalar();
391                        cmd.Parameters.Clear();
392                        if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
393                        ...{                    
394                            return null;
395                        }
396                        else
397                        ...{
398                            return obj;
399                        }                
400                    }
401                    catch(System.Data.OracleClient.OracleException e)
402                    ...{                
403                        throw new Exception(e.Message);
404                    }
405                    finally
406                    ...{
407                        cmd.Dispose();
408                        connection.Close();
409                    }
410                }
411            }
412        }
413        
414        /**//// <summary>
415        /// 执行查询语句，返回OracleDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
416        /// </summary>
417        /// <param name="strSQL">查询语句</param>
418        /// <returns>OracleDataReader</returns>
419        public static OracleDataReader ExecuteReader(string SQLString,params OracleParameter[] cmdParms)
420        ...{        
421            OracleConnection connection = new OracleConnection(connectionString);
422            OracleCommand cmd = new OracleCommand();                
423            try
424            ...{
425                PrepareCommand(cmd, connection, null,SQLString, cmdParms);
426                OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
427                cmd.Parameters.Clear();
428                return myReader;
429            }
430            catch(System.Data.OracleClient.OracleException e)
431            ...{                                
432                throw new Exception(e.Message);
433            }                    
434            
435        }        
436        
437        /**//// <summary>
438        /// 执行查询语句，返回DataSet
439        /// </summary>
440        /// <param name="SQLString">查询语句</param>
441        /// <returns>DataSet</returns>
442        public static DataSet Query(string SQLString,params OracleParameter[] cmdParms)
443        ...{
444            using (OracleConnection connection = new OracleConnection(connectionString))
445            ...{
446                OracleCommand cmd = new OracleCommand();
447                PrepareCommand(cmd, connection, null,SQLString, cmdParms);
448                using( OracleDataAdapter da = new OracleDataAdapter(cmd) )
449                ...{
450                    DataSet ds = new DataSet();    
451                    try
452                    ...{                                                
453                        da.Fill(ds,"ds");
454                        cmd.Parameters.Clear();
455                    }
456                    catch(System.Data.OracleClient.OracleException ex)
457                    ...{                
458                        throw new Exception(ex.Message);
459                    }
460                    finally
461                    ...{
462                        cmd.Dispose();
463                        connection.Close();
464                    }
465                    return ds;
466                }                
467            }            
468        }
469
470
471        private static void PrepareCommand(OracleCommand cmd,OracleConnection conn,OracleTransaction trans, string cmdText, OracleParameter[] cmdParms) 
472        ...{
473            if (conn.State != ConnectionState.Open)
474                conn.Open();
475            cmd.Connection = conn;
476            cmd.CommandText = cmdText;
477            if (trans != null)
478                cmd.Transaction = trans;
479            cmd.CommandType = CommandType.Text;//cmdType;
480            if (cmdParms != null) 
481            ...{
482                foreach (OracleParameter parm in cmdParms)
483                    cmd.Parameters.Add(parm);
484            }
485        }
486
487        #endregion
488
489        存储过程操作#region 存储过程操作
490
491        /**//// <summary>
492        /// 执行存储过程 返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
493        /// </summary>
494        /// <param name="storedProcName">存储过程名</param>
495        /// <param name="parameters">存储过程参数</param>
496        /// <returns>OracleDataReader</returns>
497        public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
498        ...{
499            OracleConnection connection = new OracleConnection(connectionString);
500            OracleDataReader returnReader;
501            connection.Open();
502            OracleCommand command = BuildQueryCommand( connection,storedProcName, parameters );
503            command.CommandType = CommandType.StoredProcedure;
504            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);                
505            return returnReader;            
506        }
507        
508        
509        /**//// <summary>
510        /// 执行存储过程
511        /// </summary>
512        /// <param name="storedProcName">存储过程名</param>
513        /// <param name="parameters">存储过程参数</param>
514        /// <param name="tableName">DataSet结果中的表名</param>
515        /// <returns>DataSet</returns>
516        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
517        ...{
518            using (OracleConnection connection = new OracleConnection(connectionString))
519            ...{
520                DataSet dataSet = new DataSet();
521                connection.Open();
522                OracleDataAdapter sqlDA = new OracleDataAdapter();
523                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
524                sqlDA.Fill( dataSet, tableName );
525                connection.Close();
526                return dataSet;
527            }
528        }
529
530        
531        /**//// <summary>
532        /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
533        /// </summary>
534        /// <param name="connection">数据库连接</param>
535        /// <param name="storedProcName">存储过程名</param>
536        /// <param name="parameters">存储过程参数</param>
537        /// <returns>OracleCommand</returns>
538        private static OracleCommand BuildQueryCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
539        ...{            
540            OracleCommand command = new OracleCommand( storedProcName, connection );
541            command.CommandType = CommandType.StoredProcedure;
542            foreach (OracleParameter parameter in parameters)
543            ...{
544                command.Parameters.Add( parameter );
545            }
546            return command;            
547        }
548        
549        /**//// <summary>
550        /// 执行存储过程，返回影响的行数        
551        /// </summary>
552        /// <param name="storedProcName">存储过程名</param>
553        /// <param name="parameters">存储过程参数</param>
554        /// <param name="rowsAffected">影响的行数</param>
555        /// <returns></returns>
556        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
557        ...{
558            using (OracleConnection connection = new OracleConnection(connectionString))
559            ...{
560                int result;
561                connection.Open();
562                OracleCommand command = BuildIntCommand(connection,storedProcName, parameters );
563                rowsAffected = command.ExecuteNonQuery();
564                result = (int)command.Parameters["ReturnValue"].Value;
565                //Connection.Close();
566                return result;
567            }
568        }
569        
570        /**//// <summary>
571        /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
572        /// </summary>
573        /// <param name="storedProcName">存储过程名</param>
574        /// <param name="parameters">存储过程参数</param>
575        /// <returns>OracleCommand 对象实例</returns>
576        private static OracleCommand BuildIntCommand(OracleConnection connection,string storedProcName, IDataParameter[] parameters)
577        ...{
578            OracleCommand command = BuildQueryCommand(connection,storedProcName, parameters );
579            command.Parameters.Add( new OracleParameter ( "ReturnValue",
580                OracleType.Int32, 4, ParameterDirection.ReturnValue,
581                false,0,0,string.Empty,DataRowVersion.Default,null ));
582            return command;
583        }
584        #endregion    
585
586    }
587}
588