sqlhelper.cs

办公室系统包括上下班打卡

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

namespace GUCAS.SC.SqlHelper
{
	public class SqlHelper
	{
		#region  定义静态常量

		private static readonly string RETURNVALUE = "RETURNVALUE";
		private static readonly string SQLCONNECTIONSTRING = "SQLCONNECTIONSTRING";

		#endregion

		#region 管理连接

		/// <summary>
		/// 打开数据库连接.
		/// </summary>
		private static SqlConnection CreateAndOpen()
		{   ///获取缓存中的连接字符串
			string conString = (string)CacheData.GetCacheValue(SQLCONNECTIONSTRING);
			
			if(null == conString)
			{   ///如果字符串为空，则从配置文件中获取连接字符串，并添加到缓存中
				conString = ConfigurationManager.ConnectionStrings[SQLCONNECTIONSTRING].ConnectionString;
				CacheData.CacheObjectValue(SQLCONNECTIONSTRING,conString);
			}
			// 创建数据库连接
			return(new SqlConnection(conString));			
		}
	
		#endregion

		#region 执行存储过程

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <returns>返回存储过程返回值</returns>
		public static int RunProc(string procName)
		{  
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			SqlCommand cmd = CreateProcCommand(sqlCon,procName,null);
			try
			{
				///执行存储过程
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}

			///返回存储过程的参数值
			return (int)cmd.Parameters[RETURNVALUE].Value;
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <returns>返回存储过程返回值</returns>
		public static int RunProc(string procName,SqlParameter[] prams)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			SqlCommand cmd = CreateProcCommand(sqlCon,procName,prams);
			try
			{
				///执行存储过程
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}

			///返回存储过程的参数值
			return (int)cmd.Parameters[RETURNVALUE].Value;
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="dataReader">返回存储过程返回值</param>
		public static void RunProc(string procName,out SqlDataReader dataReader)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建Command
			SqlCommand cmd = CreateProcCommand(sqlCon,procName,null);

			try
			{
				///读取数据
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				dataReader = null;
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <param name="dataReader">返回DataReader对象</param>
		public static void RunProc(string procName,SqlParameter[] prams,out SqlDataReader dataReader)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建Command
			SqlCommand cmd = CreateProcCommand(sqlCon,procName,prams);

			try
			{
				///读取数据
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				dataReader = null;
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public static void RunProc(string procName,ref DataSet dataSet)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(sqlCon,procName,null);

			try
			{
				///读取数据
				da.Fill(dataSet);
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public static void RunProc(string procName,SqlParameter[] prams,ref DataSet dataSet)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(sqlCon,procName,prams);

			try
			{
				///读取数据
				da.Fill(dataSet);
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="dataSet">返回DataSet对象</param>
		/// <param name="nStart">开始记录</param>
		/// <param name="nMax">返回记录数量</param>
		public static void RunProc(string procName,ref DataSet dataSet,
			int nStart,int nMax)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(sqlCon,procName,null);

			try
			{
				///读取数据
				da.Fill(dataSet,nStart,nMax,"DataTable");
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <param name="dataSet">返回DataSet对象</param>
		/// <param name="nStart">开始记录</param>
		/// <param name="nMax">返回记录数量</param>
		public static void RunProc(string procName,SqlParameter[] prams,ref DataSet dataSet,
			int nStart,int nMax)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(sqlCon,procName,prams);

			try
			{
				///读取数据
				da.Fill(dataSet,nStart,nMax,"DataTable");
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}
		}
		#endregion 

		#region 执行SQL语句

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <returns>返回值</returns>
		public static int RunSQL(string cmdText)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			SqlCommand cmd = CreateSQLCommand(sqlCon,cmdText,null);
			try
			{
				///执行存储过程
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}

			///返回存储过程的参数值
			return (int)cmd.Parameters[RETURNVALUE].Value;
		}


		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <returns>返回值</returns>
		public static int RunSQL(string cmdText,SqlParameter[] prams)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			SqlCommand cmd = CreateSQLCommand(sqlCon,cmdText,prams);
			try
			{
				///执行存储过程
				cmd.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1002",ex.Message,ex);
			}
			finally
			{   ///关闭数据库连接
				sqlCon.Close();
			}

			///返回存储过程的参数值
			return (int)cmd.Parameters[RETURNVALUE].Value;
		}

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>		
		/// <param name="dataReader">返回DataReader对象</param>
		public static void RunSQL(string cmdText,out SqlDataReader dataReader)
		{
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建Command
			SqlCommand cmd = CreateSQLCommand(sqlCon,cmdText,null);

			try
			{
				///读取数据
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				dataReader = null;
				///记录错误日志
				SystemError.SystemLog(ex.Message);
				throw new SystemException("1003",ex.Message,ex);