data.cs

非常实用的NET的SQL 程序

namespace StoredProcs
{
    using System;
	using System.Data;
	using System.Data.SQL;
	using System.Data.ADO;
	using System.Diagnostics;

    /// <summary>
    ///			This contains all of the methods for the procedure UI
    /// </summary>
    public class Data
    {
		
        public Data()
        {
			//
        }
		
		/// <summary>
		///			This executes a command with no return value
		/// </summary>
		/// <param name="commandName"> </param>
		/// <param name="args"> </param>
		public void ExecuteProc(ref ADOConnection cnn, string commandName, params object[] args )
		{
			ADOCommand cmd = new ADOCommand();

			try
			{
				cmd.ActiveConnection = cnn;

				cmd.CommandText = SpacedCommand(commandName);

				cmd.CommandType = CommandType.StoredProcedure;

				cmd.ResetParameters();
				
				for(int i = 0 ; i < cmd.Parameters.Count ; i++)
					cmd.Parameters[i].Value = (object)args[i];

				cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(e.Message, "Method: ExecuteProc, Stored Proc: " + commandName);	
			}
			
		}

		/// <summary>
		///			This Selects the Stored Procs for a given Database
		/// </summary>
		public SQLDataReader SelectProcs(ref SQLConnection cnn)
		{
			SQLDataReader dr = null;
			string query = "SELECT Name FROM sysobjects WHERE xtype = 'P' And Category = 0";
			SQLCommand cmd = new SQLCommand(query,cnn);

			try
			{
				cmd.CommandType = CommandType.Text;

				cmd.Execute(out dr);
			}
			catch(Exception e)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(e.Message, "Method: SelectProcs");	
			}
			
			return(dr);
		}

		/// <summary>
		///			This Executes a Select Statement which has no Parameters
		/// </summary>
		/// <param name="commandName"> </param>
		public DataSet Select(ADOConnection cnn, string commandName)
		{
			DataSet ds = new DataSet();
			ADODataSetCommand cmd = new ADODataSetCommand(SpacedCommand(commandName),cnn);

			try
			{
				cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
				
				cmd.FillDataSet(ds,"Return");
			}
			catch(Exception e)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(e.Message, "Method: Select, Stored Proc: " + commandName);
			}
			
			return(ds);
			
		}
		
		public DataSet Select(ADOConnection cnn, string commandName, params object[] args)
		{
			DataSet ds = new DataSet();
			ADODataSetCommand cmd = new ADODataSetCommand(SpacedCommand(commandName),cnn);

			cmd.SelectCommand.CommandType = CommandType.StoredProcedure;

			cmd.SelectCommand.ResetParameters();
			try
			{	
				for(int i = 0 ; i < cmd.SelectCommand.Parameters.Count ; i++)
					cmd.SelectCommand.Parameters[i].Value = args[i];

				cmd.FillDataSet(ds,"Return");

			}
			catch(Exception e)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(e.Message, "Method: Select, Stored Proc: " + commandName);
			}
			
			return(ds);

		}
		


		/// <summary>
		///			This selects the parameters from a strored proc 
		///			and returns them in a datatable for display
		/// </summary>
		/// <param name="commandName"> </param>
		public  DataTable SelectParms(ADOConnection cnn, string commandName)
		{
			DataTable dt = new DataTable();
			object[] arr = new object[5];
			ADOCommand cmd = new ADOCommand(SpacedCommand(commandName), cnn);

			cmd.CommandType = CommandType.StoredProcedure;

			try
			{
				cmd.ResetParameters();
				
				dt.Columns.Add("Index");
				dt.Columns.Add("Name");
				dt.Columns.Add("Type");
				dt.Columns.Add("Length");
				dt.Columns.Add("Value");
	
				for(int i = 0 ; i < cmd.Parameters.Count ; i++)
				{
					arr[0] = i;
					arr[1] = cmd.Parameters[i].ParameterName.ToString();
					arr[2] = cmd.Parameters[i].DataType.ToString();
					arr[3] = cmd.Parameters[i].Size.ToString();
					arr[4] = null;

					dt.Rows.AddUpdate(arr);
				}
			}
			catch(Exception e)
			{
				ErrorLog errLog = new ErrorLog(); 
				
				errLog.LogError(e.Message, "Method: SelectParams, Stored Proc: " + commandName);
			}
			finally
			{
				//cmd.Parameters.Clear();
				cmd.ActiveConnection.Close();
			}
			return(dt);
		}

		private string SpacedCommand(string commandName)
		{
			string[] arr = commandName.Split(new char[] {' '});

			if(arr.Length > 0)
			{
				commandName = "[" + commandName + "]";
			}
			
			return(commandName);
		}

		/// <summary>
		///			This is the destructor
		/// </summary>
		protected override void Finalize()
		{
			base.Finalize();
		}
    }

	internal class ErrorLog
	{
		/// <summary>
		///		This method writes to the application log
		/// </summary>
		/// <param name="errMessage"> </param>
		/// <param name="errSource"> </param>
	    public void LogError(string errMessage, string errSource)
	    {
			EventLog errLog = new System.Diagnostics.EventLog("Application",".",errSource);

			errLog.WriteEntry(errMessage, EventLogEntryType.Error);
	    }
		
	}
}