LYZHGD_3.0/还原代码/DBUtility/DbHelperOra_...

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;

namespace DBUtility
{
	public abstract class DbHelperOra
	{
		public static string connectionString = ConfigurationSettings.AppSettings["strCoonOracle"].ToString();

		public DbHelperOra()
		{
		}

		public static int GetMaxID(string FieldName, string TableName)
		{
			string strsql = "select max(" + FieldName + ")+1 from " + TableName;
			object obj = DbHelperOra.GetSingle(strsql);
			int result;
			if (obj == null)
			{
				result = 1;
			}
			else
			{
				result = int.Parse(obj.ToString());
			}
			return result;
		}

		public static bool Exists(string strSql)
		{
			object obj = DbHelperOra.GetSingle(strSql);
			int cmdresult;
			if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
			{
				cmdresult = 0;
			}
			else
			{
				cmdresult = int.Parse(obj.ToString());
			}
			return cmdresult != 0;
		}

		public static bool Exists(string strSql, params OracleParameter[] cmdParms)
		{
			object obj = DbHelperOra.GetSingle(strSql, cmdParms);
			int cmdresult;
			if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
			{
				cmdresult = 0;
			}
			else
			{
				cmdresult = int.Parse(obj.ToString());
			}
			return cmdresult != 0;
		}

		public static int ExecuteSql(string SQLString)
		{
			int result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				using (OracleCommand cmd = new OracleCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						int rows = cmd.ExecuteNonQuery();
						result = rows;
					}
					catch (OracleException E)
					{
						connection.Close();
						throw new Exception(E.Message);
					}
				}
			}
			return result;
		}

		public static int ExecuteSqlTran(List<string> SQLStringList)
		{
			int result;
			using (OracleConnection conn = new OracleConnection(DbHelperOra.connectionString))
			{
				conn.Open();
				OracleCommand cmd = new OracleCommand();
				cmd.Connection = conn;
				OracleTransaction tx = conn.BeginTransaction();
				cmd.Transaction = tx;
				try
				{
					int count = 0;
					for (int i = 0; i < SQLStringList.Count; i++)
					{
						string strsql = SQLStringList[i].ToString();
						if (strsql.Trim().Length > 1)
						{
							cmd.CommandText = strsql;
							count += cmd.ExecuteNonQuery();
						}
					}
					tx.Commit();
					result = count;
				}
				catch (OracleException E_98)
				{
					tx.Rollback();
					result = -1;
				}
			}
			return result;
		}

		public static int ExecuteSql(string SQLString, string content)
		{
			int result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleCommand cmd = new OracleCommand(SQLString, connection);
				OracleParameter myParameter = new OracleParameter("@content", OracleType.NVarChar);
				myParameter.Value = content;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					result = rows;
				}
				catch (OracleException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
			return result;
		}

		public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
		{
			int result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleCommand cmd = new OracleCommand(strSQL, connection);
				OracleParameter myParameter = new OracleParameter("@fs", OracleType.LongRaw);
				myParameter.Value = fs;
				cmd.Parameters.Add(myParameter);
				try
				{
					connection.Open();
					int rows = cmd.ExecuteNonQuery();
					result = rows;
				}
				catch (OracleException E)
				{
					throw new Exception(E.Message);
				}
				finally
				{
					cmd.Dispose();
					connection.Close();
				}
			}
			return result;
		}

		public static object GetSingle(string SQLString)
		{
			object result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				using (OracleCommand cmd = new OracleCommand(SQLString, connection))
				{
					try
					{
						connection.Open();
						object obj = cmd.ExecuteScalar();
						if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
						{
							result = null;
						}
						else
						{
							result = obj;
						}
					}
					catch (OracleException e)
					{
						connection.Close();
						throw new Exception(e.Message);
					}
				}
			}
			return result;
		}

		public static OracleDataReader ExecuteReader(string strSQL)
		{
			OracleConnection connection = new OracleConnection(DbHelperOra.connectionString);
			OracleCommand cmd = new OracleCommand(strSQL, connection);
			OracleDataReader result;
			try
			{
				connection.Open();
				OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				result = myReader;
			}
			catch (OracleException e)
			{
				throw new Exception(e.Message);
			}
			return result;
		}

		public static DataSet Query(string SQLString)
		{
			DataSet result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				DataSet ds = new DataSet();
				try
				{
					connection.Open();
					OracleCommand cmd = new OracleCommand(SQLString, connection);
					new OracleDataAdapter
					{
						SelectCommand = cmd
					}.Fill(ds);
				}
				catch (OracleException ex)
				{
					throw new Exception(ex.Message);
				}
				result = ds;
			}
			return result;
		}

		public static int ExecuteSql(string SQLString, params OracleParameter[] cmdParms)
		{
			int result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				using (OracleCommand cmd = new OracleCommand())
				{
					try
					{
						DbHelperOra.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
						int rows = cmd.ExecuteNonQuery();
						cmd.Parameters.Clear();
						result = rows;
					}
					catch (OracleException E)
					{
						throw new Exception(E.Message);
					}
				}
			}
			return result;
		}

		public static void ExecuteSqlTran(Hashtable SQLStringList)
		{
			using (OracleConnection conn = new OracleConnection(DbHelperOra.connectionString))
			{
				conn.Open();
				using (OracleTransaction trans = conn.BeginTransaction())
				{
					OracleCommand cmd = new OracleCommand();
					try
					{
						foreach (DictionaryEntry myDE in SQLStringList)
						{
							string cmdText = myDE.Key.ToString();
							OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
							DbHelperOra.PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
							int val = cmd.ExecuteNonQuery();
							cmd.Parameters.Clear();
							trans.Commit();
						}
					}
					catch
					{
						trans.Rollback();
						throw;
					}
				}
			}
		}

		public static object GetSingle(string SQLString, params OracleParameter[] cmdParms)
		{
			object result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				using (OracleCommand cmd = new OracleCommand())
				{
					try
					{
						DbHelperOra.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
						object obj = cmd.ExecuteScalar();
						cmd.Parameters.Clear();
						if (object.Equals(obj, null) || object.Equals(obj, DBNull.Value))
						{
							result = null;
						}
						else
						{
							result = obj;
						}
					}
					catch (OracleException e)
					{
						throw new Exception(e.Message);
					}
				}
			}
			return result;
		}

		public static OracleDataReader ExecuteReader(string SQLString, params OracleParameter[] cmdParms)
		{
			OracleConnection connection = new OracleConnection(DbHelperOra.connectionString);
			OracleCommand cmd = new OracleCommand();
			OracleDataReader result;
			try
			{
				DbHelperOra.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
				OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				result = myReader;
			}
			catch (OracleException e)
			{
				throw new Exception(e.Message);
			}
			return result;
		}

		public static DataSet Query(string SQLString, params OracleParameter[] cmdParms)
		{
			DataSet result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleCommand cmd = new OracleCommand();
				DbHelperOra.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
				using (OracleDataAdapter da = new OracleDataAdapter(cmd))
				{
					DataSet ds = new DataSet();
					try
					{
						da.Fill(ds, "ds");
						cmd.Parameters.Clear();
					}
					catch (OracleException ex)
					{
						throw new Exception(ex.Message);
					}
					result = ds;
				}
			}
			return result;
		}

		private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.CommandType = CommandType.Text;
			if (cmdParms != null)
			{
				for (int i = 0; i < cmdParms.Length; i++)
				{
					OracleParameter parm = cmdParms[i];
					if ((parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Input) && parm.Value == null)
					{
						parm.Value = DBNull.Value;
					}
					cmd.Parameters.Add(parm);
				}
			}
		}

		private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdtype, string cmdText, OracleParameter[] cmdParms)
		{
			if (conn.State != ConnectionState.Open)
			{
				conn.Open();
			}
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			if (trans != null)
			{
				cmd.Transaction = trans;
			}
			cmd.CommandType = cmdtype;
			if (cmdParms != null)
			{
				for (int i = 0; i < cmdParms.Length; i++)
				{
					OracleParameter parm = cmdParms[i];
					cmd.Parameters.Add(parm);
				}
			}
		}

		public static OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
		{
			OracleConnection connection = new OracleConnection(DbHelperOra.connectionString);
			connection.Open();
			OracleCommand command = DbHelperOra.BuildQueryCommand(connection, storedProcName, parameters);
			command.CommandType = CommandType.StoredProcedure;
			return command.ExecuteReader(CommandBehavior.CloseConnection);
		}

		public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
		{
			DataSet result;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				DataSet dataSet = new DataSet();
				connection.Open();
				new OracleDataAdapter
				{
					SelectCommand = DbHelperOra.BuildQueryCommand(connection, storedProcName, parameters)
				}.Fill(dataSet, tableName);
				connection.Close();
				result = dataSet;
			}
			return result;
		}

		private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
		{
			OracleCommand command = new OracleCommand(storedProcName, connection);
			command.CommandType = CommandType.StoredProcedure;
			for (int i = 0; i < parameters.Length; i++)
			{
				OracleParameter parameter = (OracleParameter)parameters[i];
				command.Parameters.Add(parameter);
			}
			return command;
		}

		public static DataTable ExecuteDataTable(OracleConnection connection, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
		{
			OracleCommand cmd = new OracleCommand();
			DbHelperOra.PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
			OracleDataAdapter da = new OracleDataAdapter(cmd);
			DataTable dt = new DataTable();
			da.Fill(dt);
			cmd.Parameters.Clear();
			connection.Close();
			connection.Dispose();
			return dt;
		}

		public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
		{
			int result2;
			using (OracleConnection connection = new OracleConnection(DbHelperOra.connectionString))
			{
				connection.Open();
				OracleCommand command = DbHelperOra.BuildIntCommand(connection, storedProcName, parameters);
				rowsAffected = command.ExecuteNonQuery();
				int result = (int)command.Parameters["ReturnValue"].Value;
				result2 = result;
			}
			return result2;
		}

		private static OracleCommand BuildIntCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
		{
			OracleCommand command = DbHelperOra.BuildQueryCommand(connection, storedProcName, parameters);
			command.Parameters.Add(new OracleParameter("ReturnValue", OracleType.Int32, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
			return command;
		}

		public static string Proc_GetNewGDBH(string strGDZLid, string ZXBH)
		{
			string result = "";
			using (OracleConnection con = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleCommand cmd = new OracleCommand("UP_GETNEW_GDBH", con);
				cmd.CommandType = CommandType.StoredProcedure;
				OracleParameter para = new OracleParameter("S_GDZL", OracleType.VarChar, 10);
				para.Direction = ParameterDirection.Input;
				para.Value = strGDZLid;
				cmd.Parameters.Add(para);
				OracleParameter para2 = new OracleParameter("S_ZXBH", OracleType.VarChar, 20);
				para2.Direction = ParameterDirection.Input;
				para2.Value = ZXBH;
				cmd.Parameters.Add(para2);
				OracleParameter para3 = new OracleParameter("S_GDBH", OracleType.VarChar, 50);
				para3.Direction = ParameterDirection.Output;
				cmd.Parameters.Add(para3);
				try
				{
					con.Open();
					cmd.ExecuteNonQuery();
					result = cmd.Parameters["S_GDBH"].Value.ToString();
				}
				catch
				{
				}
				finally
				{
					con.Close();
				}
			}
			return result;
		}

		public static string Proc_GetAddress(string strTel)
		{
			string result = "";
			using (OracleConnection con = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleParameter[] paras = new OracleParameter[]
				{
					new OracleParameter("s_telNmb", OracleType.VarChar, 20),
					new OracleParameter("s_agentId", OracleType.VarChar, 10),
					new OracleParameter("s_Address", OracleType.VarChar, 20)
				};
				paras[0].Value = strTel;
				paras[1].Direction = ParameterDirection.Output;
				paras[2].Direction = ParameterDirection.Output;
				OracleCommand cmd = DbHelperOra.BuildQueryCommand(con, "Proc_GetAreaZXFJ_Page", paras);
				try
				{
					con.Open();
					cmd.ExecuteNonQuery();
					result = cmd.Parameters["s_Address"].Value.ToString();
				}
				catch
				{
				}
				finally
				{
					con.Close();
				}
			}
			return result;
		}

		public static string Proc_GetPwd(string pwd)
		{
			string result = "";
			using (OracleConnection con = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleParameter[] paras = new OracleParameter[]
				{
					new OracleParameter("pwd", OracleType.VarChar, 30),
					new OracleParameter("v_encryted_pwd", OracleType.VarChar, 3000)
				};
				paras[0].Value = pwd;
				paras[1].Direction = ParameterDirection.Output;
				OracleCommand cmd = DbHelperOra.BuildQueryCommand(con, "proc_pwd", paras);
				try
				{
					con.Open();
					cmd.ExecuteNonQuery();
					result = cmd.Parameters["v_encryted_pwd"].Value.ToString();
				}
				catch
				{
				}
				finally
				{
					con.Close();
				}
			}
			return result;
		}

		public static void Proc_UpCallmode(string hjbh, string callmode, string callarea, string zxbh)
		{
			using (OracleConnection con = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleParameter[] paras = new OracleParameter[]
				{
					new OracleParameter("s_hjbh", OracleType.Number, 7),
					new OracleParameter("s_callmode", OracleType.Number, 7),
					new OracleParameter("s_callarea", OracleType.VarChar, 20),
					new OracleParameter("s_zxbh", OracleType.Number, 7)
				};
				paras[0].Value = int.Parse(hjbh);
				paras[1].Value = int.Parse(callmode);
				paras[2].Value = callarea;
				paras[3].Value = int.Parse(zxbh);
				OracleCommand cmd = DbHelperOra.BuildQueryCommand(con, "proc_upcallmode", paras);
				try
				{
					con.Open();
					cmd.ExecuteNonQuery();
				}
				catch
				{
				}
				finally
				{
					con.Close();
				}
			}
		}

		public static string Proc_GetFaxCode()
		{
			string result = "";
			using (OracleConnection con = new OracleConnection(DbHelperOra.connectionString))
			{
				OracleParameter[] paras = new OracleParameter[]
				{
					new OracleParameter("s_faxcode", OracleType.VarChar, 100)
				};
				paras[0].Direction = ParameterDirection.Output;
				OracleCommand cmd = DbHelperOra.BuildQueryCommand(con, "UP_HY_Fax_GetFaxCode", paras);
				try
				{
					con.Open();
					cmd.ExecuteNonQuery();
					result = cmd.Parameters["s_faxcode"].Value.ToString();
				}
				catch
				{
				}
				finally
				{
					con.Close();
				}
			}
			return result;
		}
	}
}