database.cs

C#打造操作系统任务计划

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace MCDYService
{
	/// <summary>
	/// 数据库基类
	/// 
	/// </summary>
	public class DataBase
	{
		/// <summary>
		/// 获取数据库连接字符串
		/// </summary>
		/// 
		private SqlConnection sqlConn=null;
	    private static string strconn = "server=10.136.28.7; user id=hnwis;password=hnwis_4698382_zuoxing_7788;database=hnwisdbnew;timeout=0";
		//private static string strconn = "server=10.136.28.7; user id=importdata;password=importdata123;database=hnwisdbnew;timeout=0";
		private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
		private System.Data.SqlClient.SqlTransaction Trans;
		private System.Data.SqlClient.SqlCommand commd;
		public DataBase()
		{		

		}

		/// <summary>
		/// 打开连接
		/// </summary>
		public void OpenDataBase()
		{
			
			if(sqlConn==null)
			{
				sqlConn=new SqlConnection(strconn);
				sqlConn.Open();
				
			}
		}

		/// <summary>
		/// 关闭数据库加连接
		/// </summary>
		public void CloseDataBase()
		{
			if(sqlConn!=null)
				sqlConn.Close();
		}

		/// <summary>
		/// 释放资源
		/// </summary>
		public void DisponseDataBase()
		{
			if(sqlConn!=null)
			{
				sqlConn.Dispose();				
				sqlConn=null;
			}
		}


		/// <summary>
		/// 返回一个数据库对象
		/// </summary>
		/// <param name="sqlstr"></param>
		/// <returns>ds</returns>
		public DataSet DataSetObj(string sqlstr)
		{
			OpenDataBase();
			DataSet ds=new DataSet();
			SqlDataAdapter sqlComm=null;
			try
			{
				if(sqlstr!="")
				{					
					sqlComm=new SqlDataAdapter(sqlstr,sqlConn);
					sqlComm.Fill(ds);
				}
				
			}
			catch(Exception e)	
			{
				throw new Exception(e.Message);
			}

			finally
			{
				sqlComm.Dispose();
				CloseDataBase();
				DisponseDataBase();
			}
			return ds;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="sqlstr"></param>
		/// <param name="startRecord"></param>
		/// <param name="maxRecords"></param>
		/// <param name="strTable"></param>
		/// <returns></returns>
		public DataSet DataSetObj(string sqlstr,int startRecord,int maxRecords,string strTable) 
		{
			OpenDataBase();
			DataSet ds=new DataSet();
			SqlDataAdapter sqlComm=null;
			try 
			{
				if(sqlstr!="") 
				{
					
					sqlComm=new SqlDataAdapter(sqlstr,sqlConn);
					sqlComm.Fill(ds,startRecord,maxRecords,strTable);
				}
				
			}
			catch(Exception e) 
			{
				throw new Exception(e.Message);
			}

			finally 
			{
				sqlComm.Dispose();
				CloseDataBase();
				DisponseDataBase();
			}
			return ds;
		}
		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName"></param>
		/// <param name="coll"></param>
		/// <returns>ds</returns>
		public System.Data.DataSet ExecutePorcedure(string procName,SqlParameter[] coll)
		{
			SqlCommand comm = new SqlCommand();	
			comm.CommandTimeout=100;
			OpenDataBase();
			try
			{
				
				DataSet ds=new DataSet();
				SqlDataAdapter da=new SqlDataAdapter();
				for(int i=0;i<coll.Length;i++)
				{
					comm.Parameters.Add(coll[i]);
				}
				comm.CommandType=CommandType.StoredProcedure;
				comm.CommandText =procName;
				comm.Connection = this.sqlConn ;
				da.SelectCommand =comm;
				da.Fill(ds,procName);
				return ds;
			}
			catch(Exception e)
			{
				throw new Exception(e.Message);
			}
			finally
			{
				comm.Parameters.Clear();
				CloseDataBase();
				DisponseDataBase();
			}
		}

		///执行简单的SQL语句
		public void ExecuteSql(string sqlstr)
		{
			OpenDataBase();
			SqlCommand cmd=new SqlCommand(sqlstr,sqlConn);
			cmd.CommandTimeout=100;
			try
			{				
				cmd.ExecuteNonQuery();
			}
			catch (SqlException e)
			{
				throw new Exception(e.Message);
			}		
			finally
			{
				cmd.Dispose();				
				this.CloseDataBase();
				DisponseDataBase();
			}
		}

		public object GetPassword(string sqlstr)
		{
			object bb;
			OpenDataBase();
			SqlCommand cmd=new SqlCommand(sqlstr,sqlConn);
			cmd.CommandTimeout=100;
			try
			{		
				bb=cmd.ExecuteScalar();
				return bb;
			}
			catch (SqlException e)
			{
				throw new Exception(e.Message);
			}		
			finally
			{
				cmd.Dispose();				
				this.CloseDataBase();
				DisponseDataBase();
			}
		}

		public void ExecuteSqlPamater(string sqlstr,SqlParameter[] coll)
		{
			
			OpenDataBase();
			SqlCommand comm = new SqlCommand(sqlstr,sqlConn);	
			comm.CommandTimeout=100;
			
			try
			{
				
				for(int i=0;i<coll.Length;i++)
				{
					comm.Parameters.Add(coll[i]);
				}
				
				
				//				comm.Connection = this.sqlConn ;
				comm.ExecuteNonQuery();
			}
			catch (SqlException e)
			{
				throw new Exception(e.Message);
			}		
			finally
			{
				comm.Dispose();				
				this.CloseDataBase();
				DisponseDataBase();
			}
		}

		public void OpenTransaction()
		{
			this.OpenDataBase();
			Trans=sqlConn.BeginTransaction();
			commd=new SqlCommand();
			commd.Connection=sqlConn;
			commd.Transaction=Trans;
		}
		/// <summary>
		/// 执行事务语句
		/// </summary>
		/// <param name="sqlstr">执行语句</param>
		public void ExecuteTransaction(string sqlstr)
		{		
			commd.CommandText=sqlstr;
			commd.CommandTimeout=100;
			commd.ExecuteNonQuery();
		}
		/// <summary>
		/// 提交事务
		/// </summary>
		public void CommitTransaction()
		{
			try
			{
				Trans.Commit();
			} 
			catch(Exception e)
			{
				Trans.Rollback();
				throw(e);
			}
		}

		/// <summary>
		/// 缓存参数
		/// </summary>
		/// <param name="cacheKey"></param>
		/// <param name="cmdParms"></param>
		public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms) 
		{
			parmCache[cacheKey] = cmdParms;
		}
        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <returns></returns>
		public static SqlParameter[] GetCachedParameters(string cacheKey) 
		{
			SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];
			
			if (cachedParms == null)
				return null;
			
			SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

			for (int i = 0, j = cachedParms.Length; i < j; i++)
				clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

			return clonedParms;
		}		
	
	
	}


			

}