1 using System; 2 using System.Data; 3 using System.Collections.Generic; 4 using System.Configuration; 5 using System.Data.OracleClient; 6 using System.Text; 7 using System.IO; 8 9 /// <summary> 10 /// Oracle数据库操作类 11 /// </summary> 12 public static class OracleHelper 13 { 14 /// <summary> 15 /// 执行数据库非查询操作,返回受影响的行数 16 /// </summary> 17 /// <param name="connectionString">数据库连接字符串</param> 18 /// <param name="cmdType">命令的类型</param> 19 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 20 /// <param name="cmdParms">命令参数集合</param> 21 /// <returns>当前查询操作影响的数据行数</returns> 22 public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 23 { 24 OracleCommand cmd = new OracleCommand(); 25 using (OracleConnection conn = new OracleConnection(connectionString)) 26 { 27 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 28 int val = cmd.ExecuteNonQuery(); 29 cmd.Parameters.Clear(); 30 return val; 31 } 32 } 33 34 /// <summary> 35 /// 执行数据库事务非查询操作,返回受影响的行数 36 /// </summary> 37 /// <param name="transaction">数据库事务对象</param> 38 /// <param name="cmdType">Command类型</param> 39 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 40 /// <param name="cmdParms">命令参数集合</param> 41 /// <returns>当前事务查询操作影响的数据行数</returns> 42 public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 43 { 44 OracleCommand cmd = new OracleCommand(); 45 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms); 46 int val = cmd.ExecuteNonQuery(); 47 cmd.Parameters.Clear(); 48 return val; 49 } 50 51 /// <summary> 52 /// 执行数据库非查询操作,返回受影响的行数 53 /// </summary> 54 /// <param name="connection">Oracle数据库连接对象</param> 55 /// <param name="cmdType">Command类型</param> 56 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 57 /// <param name="cmdParms">命令参数集合</param> 58 /// <returns>当前查询操作影响的数据行数</returns> 59 public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 60 { 61 if (connection == null) 62 throw new ArgumentNullException("当前数据库连接不存在"); 63 OracleCommand cmd = new OracleCommand(); 64 PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms); 65 int val = cmd.ExecuteNonQuery(); 66 cmd.Parameters.Clear(); 67 return val; 68 } 69 70 /// <summary> 71 /// 执行数据库查询操作,返回OracleDataReader类型的内存结果集 72 /// </summary> 73 /// <param name="connectionString">数据库连接字符串</param> 74 /// <param name="cmdType">命令的类型</param> 75 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 76 /// <param name="cmdParms">命令参数集合</param> 77 /// <returns>当前查询操作返回的OracleDataReader类型的内存结果集</returns> 78 public static OracleDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 79 { 80 OracleCommand cmd = new OracleCommand(); 81 OracleConnection conn = new OracleConnection(connectionString); 82 try 83 { 84 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 85 OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 86 cmd.Parameters.Clear(); 87 return reader; 88 } 89 catch 90 { 91 cmd.Dispose(); 92 conn.Close(); 93 throw; 94 } 95 } 96 97 /// <summary> 98 /// 执行数据库查询操作,返回DataSet类型的结果集 99 /// </summary> 100 /// <param name="connectionString">数据库连接字符串</param> 101 /// <param name="cmdType">命令的类型</param> 102 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 103 /// <param name="cmdParms">命令参数集合</param> 104 /// <returns>当前查询操作返回的DataSet类型的结果集</returns> 105 public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 106 { 107 OracleCommand cmd = new OracleCommand(); 108 OracleConnection conn = new OracleConnection(connectionString); 109 DataSet ds = null; 110 try 111 { 112 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 113 OracleDataAdapter adapter = new OracleDataAdapter(); 114 adapter.SelectCommand = cmd; 115 ds = new DataSet(); 116 adapter.Fill(ds); 117 cmd.Parameters.Clear(); 118 } 119 catch 120 { 121 throw; 122 } 123 finally 124 { 125 cmd.Dispose(); 126 conn.Close(); 127 conn.Dispose(); 128 } 129 130 return ds; 131 } 132 133 /// <summary> 134 /// 执行数据库查询操作,返回DataTable类型的结果集 135 /// </summary> 136 /// <param name="connectionString">数据库连接字符串</param> 137 /// <param name="cmdType">命令的类型</param> 138 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 139 /// <param name="cmdParms">命令参数集合</param> 140 /// <returns>当前查询操作返回的DataTable类型的结果集</returns> 141 public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 142 { 143 OracleCommand cmd = new OracleCommand(); 144 OracleConnection conn = new OracleConnection(connectionString); 145 DataTable dt = null; 146 147 try 148 { 149 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 150 OracleDataAdapter adapter = new OracleDataAdapter(); 151 adapter.SelectCommand = cmd; 152 dt = new DataTable(); 153 adapter.Fill(dt); 154 cmd.Parameters.Clear(); 155 } 156 catch 157 { 158 throw; 159 } 160 finally 161 { 162 cmd.Dispose(); 163 conn.Close(); 164 conn.Dispose(); 165 } 166 167 return dt; 168 } 169 170 /// <summary> 171 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 172 /// </summary> 173 /// <param name="connectionString">数据库连接字符串</param> 174 /// <param name="cmdType">命令的类型</param> 175 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 176 /// <param name="cmdParms">命令参数集合</param> 177 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 178 public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 179 { 180 OracleCommand cmd = new OracleCommand(); 181 OracleConnection conn = new OracleConnection(connectionString); 182 object result = null; 183 try 184 { 185 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 186 result = cmd.ExecuteScalar(); 187 cmd.Parameters.Clear(); 188 } 189 catch 190 { 191 throw; 192 } 193 finally 194 { 195 cmd.Dispose(); 196 conn.Close(); 197 conn.Dispose(); 198 } 199 200 return result; 201 } 202 203 /// <summary> 204 /// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值 205 /// </summary> 206 /// <param name="trans">一个已存在的数据库事务对象</param> 207 /// <param name="commandType">命令类型</param> 208 /// <param name="commandText">Oracle存储过程名称或PL/SQL命令</param> 209 /// <param name="cmdParms">命令参数集合</param> 210 /// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 211 public static object ExecuteScalar(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 212 { 213 if (trans == null) 214 throw new ArgumentNullException("当前数据库事务不存在"); 215 OracleConnection conn = trans.Connection; 216 if (conn == null) 217 throw new ArgumentException("当前事务所在的数据库连接不存在"); 218 219 OracleCommand cmd = new OracleCommand(); 220 object result = null; 221 222 try 223 { 224 PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms); 225 result = cmd.ExecuteScalar(); 226 cmd.Parameters.Clear(); 227 } 228 catch 229 { 230 throw; 231 } 232 finally 233 { 234 trans.Dispose(); 235 cmd.Dispose(); 236 conn.Close(); 237 conn.Dispose(); 238 } 239 240 return result; 241 } 242 243 /// <summary> 244 /// 执行数据库查询操作,返回结果集中位于第一行第一列的Object类型的值 245 /// </summary> 246 /// <param name="conn">数据库连接对象</param> 247 /// <param name="cmdType">Command类型</param> 248 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 249 /// <param name="cmdParms">命令参数集合</param> 250 /// <returns>当前查询操作返回的结果集中位于第一行第一列的Object类型的值</returns> 251 public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 252 { 253 if (conn == null) throw new ArgumentException("当前数据库连接不存在"); 254 OracleCommand cmd = new OracleCommand(); 255 object result = null; 256 257 try 258 { 259 PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms); 260 result = cmd.ExecuteScalar(); 261 cmd.Parameters.Clear(); 262 } 263 catch 264 { 265 throw; 266 } 267 finally 268 { 269 cmd.Dispose(); 270 conn.Close(); 271 conn.Dispose(); 272 } 273 274 return result; 275 } 276 277 /// <summary> 278 /// 执行数据库命令前的准备工作 279 /// </summary> 280 /// <param name="cmd">Command对象</param> 281 /// <param name="conn">数据库连接对象</param> 282 /// <param name="trans">事务对象</param> 283 /// <param name="cmdType">Command类型</param> 284 /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> 285 /// <param name="cmdParms">命令参数集合</param> 286 private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms) 287 { 288 if (conn.State != ConnectionState.Open) 289 conn.Open(); 290 291 cmd.Connection = conn; 292 cmd.CommandText = cmdText; 293 294 if (trans != null) 295 cmd.Transaction = trans; 296 297 cmd.CommandType = cmdType; 298 299 if (cmdParms != null) 300 { 301 foreach (OracleParameter parm in cmdParms) 302 cmd.Parameters.Add(parm); 303 } 304 } 305 306 /// <summary> 307 /// 将.NET日期时间类型转化为Oracle兼容的日期时间格式字符串 308 /// </summary> 309 /// <param name="date">.NET日期时间类型对象</param> 310 /// <returns>Oracle兼容的日期时间格式字符串(如该字符串:TO_DATE('2007-12-1','YYYY-MM-DD'))</returns> 311 public static string GetOracleDateFormat(DateTime date) 312 { 313 return "TO_DATE('" + date.ToString("yyyy-M-dd") + "','YYYY-MM-DD')"; 314 } 315 316 /// <summary> 317 /// 将.NET日期时间类型转化为Oracle兼容的日期格式字符串 318 /// </summary> 319 /// <param name="date">.NET日期时间类型对象</param> 320 /// <param name="format">Oracle日期时间类型格式化限定符</param> 321 /// <returns>Oracle兼容的日期时间格式字符串(如该字符串:TO_DATE('2007-12-1','YYYY-MM-DD'))</returns> 322 public static string GetOracleDateFormat(DateTime date, string format) 323 { 324 if (format == null || format.Trim() == "") format = "YYYY-MM-DD"; 325 return "TO_DATE('" + date.ToString("yyyy-M-dd") + "','" + format + "')"; 326 } 327 328 /// <summary> 329 /// 将指定的关键字处理为模糊查询时的合法参数值 330 /// </summary> 331 /// <param name="source">待处理的查询关键字</param> 332 /// <returns>过滤后的查询关键字</returns> 333 public static string HandleLikeKey(string source) 334 { 335 if (source == null || source.Trim() == "") return null; 336 337 source = source.Replace("[", "[]]"); 338 source = source.Replace("_", "[_]"); 339 source = source.Replace("%", "[%]"); 340 341 return ("%" + source + "%"); 342 } 343 344 }