databaseoperate.cs

手机串口编程 手机串口编程 手机串口编程

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace  JB.GraduateDesign.ESM.Common
{
	
	public class DataBaseOperate : IDisposable
	{

		/// <summary>
		/// 保存数据库连接字符串
		/// </summary>
		private string _connectionString;
		
		/// <summary>
		/// 数据库连接标志
		/// </summary>
		private bool _isConnected;
		
		/// <summary>
		/// 存储过程开启标志
		/// </summary>
		private bool _isTransaction;
		
	
		//数据库对象
		/// <summary>
		/// 
		/// </summary>
		private SqlConnection _connection;
		private SqlCommand _command;
		private SqlTransaction _transaction;
		private SqlDataAdapter _adapter;
	
		
		/// <summary>
		/// 构造函数，初始化内部变量
		/// </summary>
		/// <param name="">无</param>
		/// <returns>无</returns>
		public DataBaseOperate ():this( @"workstation id=YAOYANG;packet size=4096;user id=sa;data source=yaoyang\yaoyang;persist security info=True;initial catalog=EnterpriceShortMessage;password=510902" )
		{

		}
	
		/// <summary>
		/// 
		/// </summary>
		/// <param name="ConnectionString">连接串</param>
		public DataBaseOperate( string ConnectionString  )
		{
			if (  _connectionString != ""  )
			{
				_connectionString = ConnectionString;
				_isConnected = false;
			}
			_isTransaction = false;
		}

		/// <summary>
		/// 连接数据库，并打开数据库连接
		/// </summary>
		/// <param name="">无</param>
		/// <returns>成功返回true</returns>
		private bool ConnectDataBase()
		{
			if ( !_isConnected )
			{
				try
				{
					if ( _connection == null )
					{
						_connection = new SqlConnection( _connectionString);				
						_connection.Open();

					}

					if ( _command == null )
					{
						_command = new SqlCommand();
					}
					_isConnected = true;
					_command.Connection = _connection;
				}
				catch( SqlException e )
				{
					throw e;
				}
			}
			return true;
		}
		
		/// <summary>
		/// 关闭数据库，释放数据库资源
		/// </summary>
		/// <param name="">无</param>
		/// <returns>成功返回true</returns>
		public bool CloseDataBase()
		{
			Dispose();
			return true;
		}

		public bool Insert(DataTable SourceTable, string ProcedureName, SqlParameter[] Params)
		{
			if ( !ConnectDataBase() )
			{
				throw new ApplicationException("连接数据库不成功。");
			}
			_adapter = new SqlDataAdapter();
			
			_command.Parameters.Clear();
			_command.CommandText = ProcedureName;
			_command.CommandType = CommandType.StoredProcedure;

			for( int i = 0; i < Params.Length; i++ )
			{
				_command.Parameters.Add(Params[i]);
			}
			_adapter.InsertCommand = _command;
			
			try
			{
				_adapter.Update(SourceTable);
			}
			catch( Exception e )
			{
				throw e;
			}
			return true;
		}


		public bool Insert(DataSet Source, string TableName, string ProcedureName, SqlParameter[] Params)
		{
			if ( !ConnectDataBase() )
			{
				throw new ApplicationException("连接数据库不成功。");
			}
			_adapter = new SqlDataAdapter();
			
			_command.Parameters.Clear();
			_command.CommandText = ProcedureName;
			_command.CommandType = CommandType.StoredProcedure;

			for( int i = 0; i < Params.Length; i++ )
			{
				_command.Parameters.Add(Params[i]);
			}
			_adapter.InsertCommand = _command;
			
			try
			{
				_adapter.Update(Source, TableName);
			}
			catch( Exception e )
			{
				throw e;
			}
			return true;
		}


		public bool Search( string ProcedureName, DataTable Table, SqlParameter[] Params)
		{
			if ( !ConnectDataBase() )
			{
				throw new ApplicationException("还没有建立数据库连接。");
			}
						
			try
			{
				_command.CommandType = System.Data.CommandType.StoredProcedure;
				_command.CommandText = ProcedureName;
				_command.Parameters.Clear();
				foreach ( SqlParameter param in Params)
				{
					_command.Parameters.Add( param );
				}
				_adapter = new SqlDataAdapter();
				_adapter.SelectCommand = _command;
				_adapter.Fill(Table);

			}
			catch( SqlException e )
			{
				if ( _isTransaction )
					_transaction.Rollback();
				throw ( e );
			}
			return true;
		}

	
		public object Execute( string ProcedureName, SqlParameter[] Params)
		{
			if ( ! ConnectDataBase() )
			{
				throw new ApplicationException("还没有建立数据库连接。");
			}
			_command.CommandText = ProcedureName;
			_command.CommandType = System.Data.CommandType.StoredProcedure;
			_command.Parameters.Clear();
			foreach ( SqlParameter param in Params )
			{
				_command.Parameters.Add( param );
			}

			SqlParameter returnParam = new SqlParameter( "@Return_Value",SqlDbType.Int,4,ParameterDirection.ReturnValue,true,((System.Byte)(10)),((System.Byte)(0)),"", System.Data.DataRowVersion.Current, null);
			
			_command.Parameters.Add( returnParam );	
			
			try
			{
				_command.ExecuteNonQuery();
				return returnParam.Value;
			}
			catch( SqlException e )
			{
				if ( _isTransaction )
				{
					_transaction.Rollback();
				}
				throw e;
			}
		}

	
		public void StartTransation()
		{
			if ( !ConnectDataBase() )
			{
				throw new ApplicationException("数据库连接没有建立。");
			}
			_isTransaction = true;
			_transaction = _connection.BeginTransaction( IsolationLevel.ReadCommitted );
			_command.Transaction = _transaction;
			
		}

		/// <summary>
		/// 结束事务处理功能，执行方式改回逐条执行
		/// </summary>
		public void EndTransation()
		{
			_isTransaction = false;
			_transaction = null;
			_command.Transaction = null;
			
		}

		/// <summary>
		/// 主动回滚调用
		/// </summary>
		/// <returns></returns>
		public bool RollBack()
		{
			try
			{
				if ( _isTransaction )
				{
					_transaction.Rollback();
				}
			}
			catch( SqlException e )
			{
				throw new ApplicationException("提交事物回滚失败", e);
			}
			return true;
		}

		/// <summary>
		/// 当前待处理事务提交，失败全部回滚
		/// </summary>
		/// <param name="">无</param>
		/// <returns>成功提交返回true</returns>
		public bool Commit()
		{
			//如果没有开启事务处理功能，不做任何操作，直接返回成功
			if ( !_isTransaction )
			{	
				return true;
			}
		
			try
			{
				_transaction.Commit();
			}
			catch( SqlException e )
			{
				if ( _isTransaction )
				{
					RollBack();
				}
				
				throw e;
			}
			return true;
		}

		/// <summary>
		/// 除去对象资源.
		/// </summary>
		public void Dispose()
		{
			Dispose( true );
			GC.SuppressFinalize( true ); 
		}

		/// <summary>
		///	释放对象实例变量.
		/// </summary>
		protected virtual void Dispose( bool disposing )
		{
			if ( ! disposing )
			{
				return; 
			}

			if ( _isConnected )
			{
				if ( _connection.State != ConnectionState.Closed  )
				{
					_connection.Dispose();
					_connection.Close();
					
					_command = null;
					_connection = null;
					_transaction = null;

					_isConnected = false;
				}
			}
		}

	}
}