odbchelper.cs

运用ASP.NET开发的一个同学录系统

using System;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Collections;

namespace DBHelper
{
	/// <summary>
	/// Summary description for OdbcHelper.
	/// </summary>
	public class OdbcHelper
	{
		/// <summary>
		/// Execute a database query which does not include a select
		/// </summary>
		/// <param name="connString">Connection string to database</param>
		/// <param name="cmdType">Command type either stored procedure or SQL</param>
		/// <param name="cmdText">Acutall SQL Command</param>
		/// <param name="cmdParms">Parameters to bind to the command</param>
		/// <returns></returns>
		public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OdbcParameter[] cmdParms) 
		{
			
			// Create a new Odbc command
			OdbcCommand cmd = new OdbcCommand();

			//Create a connection
			using (OdbcConnection conn = new OdbcConnection(connString)) 
			{
				
				//Prepare the command
				PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the command
				int val = cmd.ExecuteNonQuery();
				cmd.Parameters.Clear();
				return val;
			}
		}

		/// <summary>
		/// Execute an OdbcCommand (that returns no resultset) against an existing database transaction 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OdbcParameter(":prodid", 24));
		/// </remarks>
		/// <param name="trans">an existing database transaction</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 OdbcParamters used to execute the command</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(OdbcTransaction trans, CommandType cmdType, string cmdText, params OdbcParameter[] cmdParms) 
		{
			OdbcCommand cmd = new OdbcCommand();
			PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		/// <summary>
		/// Execute an OdbcCommand (that returns no resultset) against an existing database connection 
		/// using the provided parameters.
		/// </summary>
		/// <remarks>
		/// e.g.:  
		///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OdbcParameter(":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 OdbcParamters used to execute the command</param>
		/// <returns>an int representing the number of rows affected by the command</returns>
		public static int ExecuteNonQuery(OdbcConnection conn, CommandType cmdType, string cmdText, params OdbcParameter[] cmdParms) 
		{

			OdbcCommand cmd = new OdbcCommand();

			PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
			int val = cmd.ExecuteNonQuery();
			cmd.Parameters.Clear();
			return val;
		}

		/// <summary>
		/// Execute a select query that will return a result set
		/// </summary>
		/// <param name="connString">Connection string</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 OdbcParamters used to execute the command</param>
		/// <returns></returns>
		public static OdbcDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OdbcParameter[] cmdParms) 
		{
			
			//Create the command and connection
			OdbcCommand cmd = new OdbcCommand();
			OdbcConnection conn = new OdbcConnection(connString);

			try 
			{
				//Prepare the command to execute
				PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
				
				//Execute the query, stating that the connection should close when the resulting datareader has been read
				OdbcDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
				cmd.Parameters.Clear();
				return rdr;
			
			}
			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
				conn.Close();
				throw e;
			}
		}
		
		/// <summary>
		/// Execute an OdbcCommand 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 OdbcParameter(":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 OdbcParamters 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 OdbcParameter[] cmdParms) 
		{
			OdbcCommand cmd = new OdbcCommand();

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

		/// <summary>
		/// Execute an OdbcCommand 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 OdbcParameter(":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 OdbcParamters 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(OdbcConnection conn, CommandType cmdType, string cmdText, params OdbcParameter[] cmdParms) 
		{
			
			OdbcCommand cmd = new OdbcCommand();

			PrepareCommand(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 OdbcParameter[] cmdParms) 
		{
			DBHelper.paramCache[cacheKey] = cmdParms;
		}

		/// <summary>
		/// Fetch parameters from the cache
		/// </summary>
		/// <param name="cacheKey">Key to look up the parameters</param>
		/// <returns></returns>
		public static OdbcParameter[] GetCachedParameters(string cacheKey) 
		{
			OdbcParameter[] cachedParms = (OdbcParameter[])DBHelper.paramCache[cacheKey];
			
			if (cachedParms == null)
				return null;
			
			// If the parameters are in the cache
			OdbcParameter[] clonedParms = new OdbcParameter[cachedParms.Length];

			// return a copy of the parameters
			for (int i = 0, j = cachedParms.Length; i < j; i++)
				clonedParms[i] = (OdbcParameter)((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 PrepareCommand(OdbcCommand cmd, OdbcConnection conn, OdbcTransaction trans, CommandType cmdType, string cmdText, OdbcParameter[] 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 (OdbcParameter parm in cmdParms)
					cmd.Parameters.Add(parm);
			}
		}
	}
}