service.cs

用于c#.net数据库操作的类库。能把结婚集以数组的形成操作。

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using System.Diagnostics;
using System.Runtime.Remoting.Messaging;
using System.Data.OleDb;

namespace EnterpriseObjects
{
	/// <summary>
	/// Summary description for Service.
	/// </summary>
	public class Service : ContextBoundObject, ICounterProvider,IDbService
	{
		// members...
		protected static PerformanceCounter _numServiceObjectsCounter;
		protected static PerformanceCounter _getEntitySetRateCounter;
		protected static PerformanceCounter _getAllRateCounter;
		protected static PerformanceCounter _getByIdRateCounter;

		// const...
		protected const string NumServiceObjectsCounterName = "NumServiceObjects";
		protected const string GetEntitySetRateCounterName = "Service.GetEntitySets/sec";
		protected const string GetAllRateCounterName = "Service.GetAll/sec";
		protected const string GetByIdRateCounterName = "Service.GetById/sec";
		private static System.Collections.Hashtable _spParameterCache=null;


		public Service()
		{
			if(_numServiceObjectsCounter != null)
				_numServiceObjectsCounter.Increment();
			if(null==_spParameterCache)
			{
				_spParameterCache=new System.Collections.Hashtable();
			}
		}

		~Service()
		{
			// try to decrement...
			try
			{
				if(_numServiceObjectsCounter != null)
					_numServiceObjectsCounter.Decrement();
			}
			catch
			{
			}
		}

		public virtual void CreateCounters(EnterpriseCounters counters)
		{
			// create the counters...
			counters.Counters.Add(new EnterpriseCounter(NumServiceObjectsCounterName, "Number of open service objects", PerformanceCounterType.NumberOfItems32));
			counters.Counters.Add(new EnterpriseCounter(GetEntitySetRateCounterName, "Number of GetEntitySet calls per second", PerformanceCounterType.RateOfCountsPerSecond32));
			counters.Counters.Add(new EnterpriseCounter(GetAllRateCounterName, "Number of GetAll calls per second", PerformanceCounterType.RateOfCountsPerSecond32));
			counters.Counters.Add(new EnterpriseCounter(GetByIdRateCounterName, "Number of GetById calls per second", PerformanceCounterType.RateOfCountsPerSecond32));
		}

		public virtual void CountersCreated(EnterpriseCounters counters)
		{
			// save them...
			_numServiceObjectsCounter = counters.Counters.Find(NumServiceObjectsCounterName).Counter;
			_getEntitySetRateCounter = counters.Counters.Find(GetEntitySetRateCounterName).Counter;
			_getAllRateCounter = counters.Counters.Find(GetAllRateCounterName).Counter;
			_getByIdRateCounter = counters.Counters.Find(GetByIdRateCounterName).Counter;
		}

		public EntitySet GetAll(string tableName, Type entitySetType)
		{
			// count...
			if(_getAllRateCounter != null)
				_getAllRateCounter.Increment();

			// run it...
			return GetEntitySet("select * from " + tableName, entitySetType);
		}
	
		/// <summary>
		/// xuc add
		/// </summary>
		/// <param name="p_CmdCommon"></param>
		/// <returns></returns>
		public SqlDataReader GetDataReader(string commandText)
		{

			SqlCommand command = new SqlCommand(commandText);

			SqlConnection connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
				
			if(command.Connection == null)
			{
				connection.Open();
				command.Connection = connection;
			}

			SqlDataReader DataReaderCommon = command.ExecuteReader();

			return DataReaderCommon;
	
		}

		public DataSet GetDataSet(string commandText) 
		{
			// create a command...
			SqlCommand command = new SqlCommand(commandText);
			DataSet dataset = GetDataSet(command);
			command.Dispose();

			// return...
			return dataset;
		}

		public DataSet GetDataSet(SqlCommand command)
		{
			// do we have a connection?
			using(SqlConnection connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString))
			{
				if(command.Connection == null)
				{
					connection.Open();
					command.Connection = connection;
				}
				// run...
				DataSet dataset = new DataSet();
				SqlDataAdapter adapter = new SqlDataAdapter(command);
				adapter.Fill(dataset);
				adapter.Dispose();

				// close the connection...
				if(connection != null)
					connection.Close();
				// return...
				return dataset;
			}
		}
		// GetEntitySet - run a statement...
		public EntitySet GetEntitySet(string commandText, Type entitySetType)
		{
			// create a command...
			SqlCommand command = new SqlCommand(commandText);
			EntitySet results = GetEntitySet(command, entitySetType);
			command.Dispose();

			// return...
			return results;
		}

		public EntitySet GetEntitySet(SqlCommand command, Type entitySetType)
		{
			// count...
			if(_getEntitySetRateCounter != null)
				_getEntitySetRateCounter.Increment();

			// do we have a connection?
			SqlConnection connection = null;
			if(command.Connection == null)
			{
				connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
				connection.Open();
				command.Connection = connection;
			}

			// run...
			EntitySet entityset = null;
			if(entitySetType != null)
				entityset = (EntitySet)System.Activator.CreateInstance(entitySetType);
			else
				entityset = new EntitySet();

			// adapter...
			SqlDataAdapter adapter = new SqlDataAdapter(command);
			adapter.Fill(entityset);
			adapter.Dispose();
	
			// close the connection...
			if(connection != null)
				connection.Close();

			// return...
			return entityset;
		}

		public Entity GetById(string tableName, string idColumnName, int id, Type entitySetType)
		{
			// count...
			if(_getByIdRateCounter != null)
				_getByIdRateCounter.Increment();

			// run...
			EntitySet entities = GetEntitySet("select top 1 * from " + tableName + " where " + idColumnName + "=" + id, entitySetType);
			if(entities.Count == 1)
				return entities.GetEntity(0);

			// none...
			return null;
		}	

		//law 2003-6-3 add
		public Entity GetById(string tableName, string idColumnName,string id, Type entitySetType)
		{
			// count...
			if(_getByIdRateCounter != null)
				_getByIdRateCounter.Increment();

			// run...
			EntitySet entities = GetEntitySet("select top 1 * from " + tableName + " where " + idColumnName + "='" + id+"'", entitySetType);
			if(entities.Count == 1)
				return entities.GetEntity(0);

			// none...
			return null;
		}
        //law 2003-5-24 add
		public Entity GetByMyId(string tableName, string idColumnName, string id, Type entitySetType)
		{
			// count...
			if(_getByIdRateCounter != null)
				_getByIdRateCounter.Increment();

			// run...
			EntitySet entities = GetEntitySet("select top 1 * from " + tableName + " where " + idColumnName + "='" + id + "'", entitySetType);
			if(entities.Count == 1)
				return entities.GetEntity(0);

			// none...
			return null;
		}	
        //law 2003-5-24 add
		public Entity GetByMySQL(string sql, Type entitySetType)
		{
			// count...
			if(_getByIdRateCounter != null)
				_getByIdRateCounter.Increment();

			// run...
			EntitySet entities = GetEntitySet(sql, entitySetType);
			if(entities.Count == 1)
				return entities.GetEntity(0);

			// none...
			return null;
		}


		public byte[] GetLatestTimestamp(string tableName, string idColumnName, int id)
		{
			// changed?
			byte[] latest = null;

			// get the latest version...
			SqlConnection connection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
			connection.Open();
			SqlCommand command = new SqlCommand("select timestamp from " + tableName + " where " + idColumnName + "=" + id.ToString(), connection);
			SqlDataReader reader = command.ExecuteReader();
			if(reader.Read() == true)
				latest = (byte[])reader[0];
	
			// close...
			reader.Close();
			connection.Close();

			// return...
			return latest;
		}	

		public bool HasChanged(SqlBinary timestamp1, SqlBinary timestamp2)
		{
			// compare...
			SqlBoolean result = SqlBinary.Equals(timestamp1, timestamp2);
			return !(result.Value);
		}

		public string GetSecurityToken()
		{
			// get the data from the call context...
			ContextToken token = (ContextToken)CallContext.GetData(EnterpriseApplication.SecurityTokenSlotName);
			if(token != null)
				return token.Token;
			
			return null;
		}
		/// <summary>
		/// 执行SQL语句(law 2003-5-16 add)
		/// </summary>
		/// <param name="sql"></param>
		/// <returns></returns>
        public int ExecuteSQL(string sql)
        {
			using (SqlConnection myCon = new SqlConnection(EnterpriseApplication.Application.ConnectionString))
			{
                myCon.Open();
                SqlCommand myCommand=new SqlCommand(sql,myCon);
                return myCommand.ExecuteNonQuery();
            }
        }
		/// <summary>
		/// 起用事务执行SQL语句(law 2003-10-19 add)
		/// </summary>
		/// <param name="sqls"></param>
		/// <returns></returns>
		public bool ExecuteSQLTrans(string []sqls)
		{
			SqlConnection myConnection = new SqlConnection(EnterpriseApplication.Application.ConnectionString);
			myConnection.Open();
			SqlCommand myCommand = new SqlCommand();
			SqlTransaction myTrans;
			// Start a local transaction
			myTrans = myConnection.BeginTransaction();
			// Must assign both transaction object and connection
			// to Command object for a pending local transaction
			myCommand.Connection = myConnection;
			myCommand.Transaction = myTrans;
			try
			{
				foreach(string sql in sqls)
				{
					myCommand.CommandText = sql;
					myCommand.ExecuteNonQuery();
				}
				myTrans.Commit();
			}
			catch
			{
        
				myConnection.Close();
				myTrans.Rollback();
				return false;
			}
			finally
			{
				myConnection.Close();
			}
			return true;
		}
		/// <summary>
		/// 填充数据集
		/// </summary>
		/// <param name="sql"> Sql </param>
		/// <param name="sTableName"> 表名 </param>
		/// <returns> 数据集 </returns>
		public   DataSet FillDataSet(string sql,string sTableName)
		{
			System.Data.DataSet ds=new DataSet();
			return this.FillDataSet(ref ds,sql,sTableName);
		}
		/// <summary>
		/// 填充数据集
		/// </summary>
		/// <param name="ds"></param>
		/// <param name="sql"></param>
		/// <param name="sTableName"></param>
		/// <returns></returns>
		public   DataSet FillDataSet(ref DataSet ds,string sql,string sTableName)
		{
			if(null!=ds)
			{
				using (SqlConnection myCon = new SqlConnection(EnterpriseApplication.Application.ConnectionString))
				{
					try
					{
						myCon.Open();
						SqlDataAdapter myAdpt=new SqlDataAdapter(sql,myCon);
						myAdpt.Fill(ds,sTableName);
						return ds;
					}
					catch
					{
						return null;
					}
				}
			}
			else
			{
				return null;
			}
		}
		/// <summary>
		/// 通过存储过程‘strBh’从表Biln1中获取编号
		/// </summary>
		/// <param name="BhId"> 编号前缀 </param>
		/// <param name="DT"> 时间 </param>
		/// <param name="isRed"> 读？写？ </param>
		/// <returns></returns>
		public static string GetBh(string BhId,System.DateTime DT,bool isRed)
		{
			// create a connection...
			using(System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(EnterpriseObjects.EnterpriseApplication.Application.ConnectionString))
			{
				connection.Open();
				// create a command...
				System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand("strBh", connection);
				command.CommandType = System.Data.CommandType.StoredProcedure;
				// parameters...
				command.Parameters.Add("@Id",BhId);
				command.Parameters.Add("@Date",DT);
				command.Parameters.Add("@isRed", isRed);
			
				System.Data.SqlClient.SqlParameter outputValueParam = command.Parameters.Add("@bh",System.Data.SqlDbType.VarChar,20);
				outputValueParam.Direction = System.Data.ParameterDirection.Output;
				// execute...
				command.ExecuteNonQuery();
				// cleanup...
				command.Dispose();
				connection.Close();