dataaccess.cs

监控系统

using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Web;

namespace MonitorSystem.BasicClass
{
	/// <summary>
	///     存储过程的返回值纪录类
	///     DataSet : 表示返回的表
	///     Output  : 存储过程的输出参数
	///     Value   : 存储过程的返回值
	/// </summary>
	public class SqlResult
	{
		public int Value;
		public Hashtable Output;
		public DataSet dataSet;

		public SqlResult()
		{
			Value = 0;
			Output = new Hashtable();
			dataSet = new DataSet();
		}
	}

	public class DataAccess
	{
		private string connectString;
		private SqlConnection sqlConnection;
		private SqlCommand sqlCommand;
		private SqlDataAdapter sqlDataAdapter;

		/// <summary>
		/// DataAccess 的摘要说明。
		/// </summary>
		public DataAccess(string connectString)
		{
			this.connectString = connectString;
			this.sqlConnection = new SqlConnection(connectString);
			this.sqlCommand = new SqlCommand();
			this.sqlCommand.Connection = this.sqlConnection;
			this.sqlDataAdapter = new SqlDataAdapter(this.sqlCommand);
		}
		/// <summary>
		/// 清除参数
		/// </summary>
		public void ClearParameters()
		{
			try
			{
				this.sqlCommand.Parameters.Clear();
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 添加输入参数
		/// </summary>
		/// <param name="paramName">参数名</param>
		/// <param name="theValue">参数值</param>
		/// <param name="dirction">输入类型：ParameterDirection.Input、ParameterDirection.InputOutput</param>
		public void AddParameter(string paramName, Object theValue, ParameterDirection dirction)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,theValue);
				param.Direction = dirction;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 添加输出参数
		/// </summary>
		/// <param name="paramName">参数名</param>
		/// <param name="sqlDbType">参数类型</param>
		/// <param name="dirction">输出类型：ParameterDirection.Output、ParameterDirection.ReturnValue</param>
		public void AddParameter(string paramName, SqlDbType sqlDbType, ParameterDirection dirction)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType);
				param.Direction = dirction;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		public void AddParameter(string paramName, SqlDbType sqlDbType, ParameterDirection dirction, Object theValue)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType);
				param.Direction = dirction;
				param.Value = theValue;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		public void AddParameter(string paramName, SqlDbType sqlDbType, int size, ParameterDirection dirction, Object theValue)
		{
			try
			{
				SqlParameter param = this.sqlCommand.Parameters.Add(paramName,sqlDbType,size);
				param.Direction = dirction;
				param.Value = theValue;
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
		}

		/// <summary>
		/// 执行一个存储过程或 SQL 语句，并将结果集输出到 dataReader 中。
		/// 注意：在正常执行后，请执行 dataReader.Close()
		/// e.g:
		/// SqlDataReader dataReader = new DataSet();
		/// DataAccess dataAccess = new DataAccess("....");
		/// dataAccess.ClearParameters();
		/// dataAccess.AddParameter("@SendType","1",ParameterDirection.Input));
		/// dataAccess.AddParameter("@oResult",SqlDbType.Int,ParameterDirection.Output);
		/// try {
		///		if(dataAccess.Excute(CommandType.StoredProcedure,"SDPPushGetTask",ref dataReader)) {
		///			//成功后的处理
		///			while(dataReader.Read()) {	
		///				......	
		///			}
		///		}
		///		else {
		///			//失败后的处理
		///		}
		/// } catch(exception e) { }
		///	finally {
		///		//关闭 DataReader
		///		if((dataReader!=null) && (dataReader.IsClosed==false)) {
		///			dataReader.Close();
		///		}
		/// }	
		/// </summary>
		/// <param name="cmdType">命令的类型，可以为 CommandType.StoredProcedure或者CommandType.Text</param>
		/// <param name="cmdText">命令的文本</param>
		/// <param name="dataReader">用来保存结果集</param>
		/// <returns>成功返回 true ； 否则返回 false</returns>
		public bool Excute( CommandType cmdType, string cmdText, ref SqlDataReader dataReader)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				dataReader = this.sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			return true;
		}

		public Object ExecuteScalar( CommandType cmdType, string cmdText)
		{
			Object result = null;
			if(this.connectString != null && this.connectString.Length > 0)
			{
				this.sqlCommand.CommandText = cmdText;
				this.sqlCommand.CommandType = cmdType;

				try
				{
					this.sqlCommand.Connection.Open();
					this.sqlCommand.Prepare();
					result = this.sqlCommand.ExecuteScalar();
				}
				catch(SqlException e )
				{
					throw new Exception( e.Message);
				}
				finally
				{
					this.sqlCommand.Connection.Close();
				}
			}

			return result;
		}

		public bool Excute( CommandType cmdType, string cmdText,ref int affectedRow)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				affectedRow = this.sqlCommand.ExecuteNonQuery();
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataTable dt)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				this.sqlDataAdapter.Fill( dt );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataSet dataSet , string tableName)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				if( dataSet.Tables.Contains( tableName ) )
					dataSet.Tables.Remove( tableName );
				dataSet.Tables.Add( tableName );
				this.sqlDataAdapter.Fill( dataSet ,tableName );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool Excute( CommandType cmdType, string cmdText, DataSet dataSet ,int startRecord,int maxRecords, string tableName)
		{
			if(this.connectString == null || this.connectString.Length ==0)
				return false;

			this.sqlCommand.CommandText = cmdText;
			this.sqlCommand.CommandType = cmdType;

			try
			{
				this.sqlCommand.Connection.Open();
				this.sqlCommand.Prepare();
				if( dataSet.Tables.Contains( tableName ) )
					dataSet.Tables.Remove( tableName );
				dataSet.Tables.Add( tableName );
				this.sqlDataAdapter.Fill( dataSet, startRecord, maxRecords ,tableName );
			}
			catch(SqlException e )
			{
				throw new Exception( e.Message);
			}
			finally
			{
				this.sqlCommand.Connection.Close();
			}
			return true;
		}

		public bool BatchExcute(string[] SqlArgs)