orahelper.cs

汽车销售公司ERP进销存系统 汽车销售公司ERP进销存系统

				DataSet ds = new DataSet();
				oda.Fill(ds,tableName);
				//Execute the query, stating that the connection should close when the resulting datareader has been read
//				OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				conn.Close();
				return ds;
			
			}
			catch (Exception e) 
			{

				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
				throw e;
			}
		}
		
		public static DataSet SqlserverExecuteDataSet(string connString, CommandType cmdType, string tableName, string cmdText, params SqlParameter[] cmdParms) 
		{
			
			//Create the command and connection
			SqlConnection conn = new SqlConnection(connString);

			SqlCommand cmd = new SqlCommand();
					
			try 
			{
				//Prepare the command to execute
				SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				SqlDataAdapter oda = new SqlDataAdapter(cmd);
				cmd.ExecuteNonQuery();
				DataSet ds = new DataSet();
				oda.Fill(ds,tableName);
				//Execute the query, stating that the connection should close when the resulting datareader has been read
				//				OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				conn.Close();
				return ds;
			
			}
			catch (Exception e) 
			{

				//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
				throw e;
			}
		}
		/// <summary>
		/// Execute an OracleCommand that returns the first column of the first record against the database specified in the connection string 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
		/// </remarks>
		/// <param name="connectionString">a valid connection string for a SqlConnection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
		public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{
			OracleCommand cmd = new OracleCommand();

			using (OracleConnection conn = new OracleConnection(connString)) 
			{
				SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return val;
			}
		}

		public static object SqlserverExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			SqlCommand cmd = new SqlCommand();

			using (SqlConnection conn = new SqlConnection(connString)) 
			{
				SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				object val = cmd.ExecuteScalar();
				cmd.Parameters.Clear();
				return val;
			}
		}

		/// <summary>
		/// Execute an OracleCommand that returns the first column of the first record against an existing database connection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
		/// </remarks>
		/// <param name="conn">an existing database connection</param>
		/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
		/// <param name="commandText">the stored procedure name or PL/SQL command</param>
		/// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
		/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
		public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms) 
		{
			
			OracleCommand cmd = new OracleCommand();

			SetCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}

		public static object SqlserverExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms) 
		{
			
			SqlCommand cmd = new SqlCommand();

			SetSqlCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			object val = cmd.ExecuteScalar();
			cmd.Parameters.Clear();
			return val;
		}

		/// <summary>
		/// Add a set of parameters to the cached
		/// </summary>
		/// <param name="cacheKey">Key value to look up the parameters</param>
		/// <param name="cmdParms">Actual parameters to cached</param>
		public static void CacheParameters(string cacheKey, params OracleParameter[] cmdParms) 
		{
			parmCache[cacheKey] = cmdParms;
		}

		public static void SqlserverCacheParameters(string cacheKey, params SqlParameter[] cmdParms) 
		{
			parmCache[cacheKey] = cmdParms;
		}
		/// <summary>
		/// Fetch parameters from the cache
		/// </summary>
		/// <param name="cacheKey">Key to look up the parameters</param>
		/// <returns></returns>
		public static OracleParameter[] GetCachedParameters(string cacheKey) 
		{
			OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
			
			if (cachedParms == null)
				return null;
			
			// If the parameters are in the cache
			OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];

			// return a copy of the parameters
			for (int i = 0, j = cachedParms.Length; i < j; i++)
				clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();

			return clonedParms;
		}

		public static SqlParameter[] GetSqlCachedParameters(string cacheKey) 
		{
			SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
			
			if (cachedParms == null)
				return null;
			
			// If the parameters are in the cache
			SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

			// return a copy of the parameters
			for (int i = 0, j = cachedParms.Length; i < j; i++)
				clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

			return clonedParms;
		}

		/// <summary>
		/// Internal function to prepare a command for execution by the database
		/// </summary>
		/// <param name="cmd">Existing command object</param>
		/// <param name="conn">Database connection object</param>
		/// <param name="trans">Optional transaction object</param>
		/// <param name="cmdType">Command type, e.g. stored procedure</param>
		/// <param name="cmdText">Command test</param>
		/// <param name="cmdParms">Parameters for the command</param>
		private static void SetCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms) 
		{
			
			//Open the connection if required
			if (conn.State != ConnectionState.Open)
				conn.Open();

			//Set up the command
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			cmd.CommandType = cmdType;

			//Bind it to the transaction if it exists
			if (trans != null)
				cmd.Transaction = trans;

			// Bind the parameters passed in
			if (cmdParms != null) 
			{
				foreach (OracleParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}

		private static void SetSqlCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 
		{
			
			//Open the connection if required
			if (conn.State != ConnectionState.Open)
				conn.Open();

			//Set up the command
			cmd.Connection = conn;
			cmd.CommandText = cmdText;
			cmd.CommandType = cmdType;

			//Bind it to the transaction if it exists
			if (trans != null)
				cmd.Transaction = trans;

			// Bind the parameters passed in
			if (cmdParms != null) 
			{
				foreach (SqlParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}
				
		//生成序列
		public static int createSequence(string seqname)
		{
			int seq = 0;
			string strseq = "SELECT "+seqname+".nextval FROM dual";
			OracleDataReader oraReader = ExecuteReader(GetOracleConnection(),CommandType.Text,strseq,null);
			if(oraReader.Read())
			{
				seq = oraReader.GetInt32(0);
				oraReader.Close();	
				if (seq == 0){
					throw new Exception("序列"+seqname+"读取错误！");
				}
			}
			oraReader.Close();
			return seq;
		}

	}
}