dbojbect.cs

用vs2005写的一个生产任务管理系统。数据的统计和综合查询是主要功能

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

namespace RWSB.Data
{
	/// <summary>
	/// 基础数据类
	/// </summary>
	public abstract class DbObject
	{
		private string connectionString;

		protected System.Data.SqlClient.SqlConnection Connection;
		private int pageNum=1;
		private int pageSize;
		private int recordCount;
		private int pageCount;
		public int PageNum
		{
			get
			{
				return pageNum;
			}
			set
			{
				if(value<=1)
					this.pageNum=1;
				else
					pageNum=value;
			}
		}
		public int PageSize
		{
			get
			{
				return pageSize;
			}
			set
			{
				if(value<=0)
					this.pageSize=0;
				else
					pageSize=value;
			}
		}
		public  int RecordCount
		{
			get
			{
				return recordCount;
			}
			set
			{
				recordCount=value;
			}
		}
		public int PageCount
		{
			get
			{
				return pageCount;
			}
			set
			{
				pageCount=value;
			}
		}
		
		public DbObject( string newConnectionString )
		{
			connectionString = newConnectionString;
			Connection = new SqlConnection( connectionString );
		}
		public DbObject(  )
		{
			connectionString =RWSB.Modules.Config.GetSetting("conn");
			Connection = new SqlConnection( connectionString );
		}
		protected string ConnectionString
		{
			get 
			{
				return connectionString;
			}
		}
		private SqlCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters)
		{
			SqlCommand command = BuildQueryCommand( storedProcName, parameters );			

			command.Parameters.Add( new SqlParameter ( "ReturnValue",
				SqlDbType.Int,
				4, /* Size */
				ParameterDirection.ReturnValue,
				false, /* is nullable */
				0, /* byte precision */
				0, /* byte scale */
				string.Empty,
				DataRowVersion.Default,
				null ));

			return command;
		}
		
		private SqlCommand BuildCommand(string commandString)
		{
			SqlCommand command = new SqlCommand( commandString, Connection );
			command.CommandType = CommandType.Text;			
			return command;
		}
		private SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
		{
			SqlCommand command = new SqlCommand( storedProcName, Connection );
			command.CommandType = CommandType.StoredProcedure;

			foreach (SqlParameter parameter in parameters)
			{
				command.Parameters.Add( parameter );
			}

			return command;

		}


		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <param name="rowsAffected"></param>
		/// <returns></returns>
		protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
		{
			int result;
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)

			Connection.Open();
			SqlCommand command = BuildIntCommand( storedProcName, parameters );
			rowsAffected = command.ExecuteNonQuery();
			result = (int)command.Parameters["ReturnValue"].Value;
			Connection.Close();
			this.Connection.Dispose();
			return result;
		}
		/// <summary>
		/// 执行存储过程返回一个DataReader
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters )
		{
			SqlDataReader returnReader;

			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
	if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlCommand command = BuildQueryCommand( storedProcName, parameters );
			command.CommandType = CommandType.StoredProcedure;

			returnReader = command.ExecuteReader();
			
			return returnReader;
		}
		/// <summary>
		/// 执行存储过程返回一DataSet
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <param name="tableName"></param>
		/// <returns></returns>
		protected DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			DataSet dataSet = new DataSet();
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlDataAdapter sqlDA = new SqlDataAdapter();
			
			
			System.Data.SqlClient.SqlCommand command=BuildQueryCommand( storedProcName, parameters );
			sqlDA.SelectCommand = command;			
			sqlDA.Fill( dataSet, tableName );
			
			Connection.Close();
			this.Connection.Dispose();
			return dataSet;
		}
		/// <summary>
		/// 得到一个DataSet
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <param name="dataSet"></param>
		/// <param name="tableName"></param>
		protected void RunProcedure(string storedProcName, IDataParameter[] parameters, DataSet dataSet, string tableName )
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlDataAdapter sqlDA = new SqlDataAdapter();
			sqlDA.SelectCommand = BuildIntCommand( storedProcName, parameters );
			sqlDA.Fill( dataSet, tableName );
			Connection.Close();	
			this.Connection.Dispose();
		}
		/// <summary>
		/// 返回当前页的DataSet
		/// </summary>
		/// <param name="calculateCmdString">计算总记录数</param>
		/// <param name="commandString">返回记录集</param>
		/// <param name="tableName"></param>
		/// <returns></returns>
		protected DataSet RunCommand(string calculateCmdString,string commandString,string tableName)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			DataSet dataSet = new DataSet();
			if(this.Connection.State!=System.Data.ConnectionState.Open)
				Connection.Open();

			SqlDataAdapter sqlDA = new SqlDataAdapter();
			System.Data.SqlClient.SqlCommand command=this.BuildCommand(calculateCmdString);
			System.Data.SqlClient.SqlDataReader sqlDataReader=command.ExecuteReader();
			if(sqlDataReader.Read())
			{
				this.recordCount=sqlDataReader.GetInt32(0);
			}			
			sqlDataReader.Close();
			command.CommandText=commandString;
			sqlDA.SelectCommand = command;
			if(pageSize==0)
			{
				pageSize=this.recordCount;
				sqlDA.Fill( dataSet, tableName );
				if(this.recordCount<=0)
					this.pageCount=0;
				else
					this.pageCount=1;
			}
			else
			{
				sqlDA.Fill( dataSet,(pageNum-1)*pageSize,pageSize,tableName );
				
				double ln=this.recordCount;
				ln=ln/this.pageSize;
				this.pageCount=(int)System.Math.Ceiling(ln);
			}
			Connection.Close();
			this.Connection.Dispose();
			return dataSet;
		}
		/// <summary>
		/// 
		/// </summary>
		/// <param name="commandString"></param>
		/// <param name="tableName"></param>
		/// <returns></returns>
		protected DataSet RunCommand(string commandString,string tableName)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			DataSet dataSet = new DataSet();
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();

			SqlDataAdapter sqlDA = new SqlDataAdapter();
			System.Data.SqlClient.SqlCommand command=this.BuildCommand(commandString);
			this.recordCount=command.ExecuteNonQuery();
			sqlDA.SelectCommand = command;
			sqlDA.Fill( dataSet, tableName );			
			Connection.Close();
			this.Connection.Dispose();
			return dataSet;
		}
		protected void RunCommand(string commandString,DataSet dataSet,string tableName)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlDataAdapter sqlDA = new SqlDataAdapter();
			sqlDA.SelectCommand = this.BuildCommand( commandString );
			sqlDA.Fill( dataSet, tableName );
			Connection.Close();
			this.Connection.Dispose();
		}
		/// <summary>
		/// 执行Sql命令返回一个DataReader
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected SqlDataReader  RunCommand(string commandString)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlCommand command = BuildCommand( commandString );
			SqlDataReader returnReader = command.ExecuteReader();
			return returnReader;
		}
		/// <summary>
		/// 执行Sql命令
		/// </summary>
		/// <param name="storedProcName"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected void RunCommand(string commandString,out int rowsAffected)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			SqlCommand command = BuildCommand( commandString );
			rowsAffected = command.ExecuteNonQuery();
			Connection.Close();		
			this.Connection.Dispose();
		}
		/// <summary>
		/// 执行Sql命令数组
		/// </summary>
		/// <param name="commandString">字符串数组</param>
		/// <returns>成功或失败</returns>
		protected bool RunCommand(string[] commandString)
		{
			if(this.Connection==null)
				this.Connection=new SqlConnection(this.connectionString);
			if(this.Connection.State!=System.Data.ConnectionState.Open)
			Connection.Open();
			System.Data.SqlClient.SqlTransaction trans=this.Connection.BeginTransaction("trans1");
			SqlCommand command=new SqlCommand();	
			command.Connection=this.Connection;
			command.Transaction=trans;
			try
			{
				for(int i=0;i<commandString.Length  ;i++)
				{
					command.CommandText=commandString[i];
					command.ExecuteNonQuery();
				}
				trans.Commit(); //没有错误就提交事务
				Connection.Close();
				this.Connection.Dispose();
				return  true;
			}
			catch 
			{
				trans.Rollback("trans1"); //回滚事务
				Connection.Close();
				this.Connection.Dispose();
				return false ;
			}
			
		}
	}
}