sqlhelper.cs

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

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

namespace SQLHelper
{
	/// <summary>
	/// SQLHelper类封装对SQL Server数据库的添加、删除、修改和选择等操作
	/// </summary>
	public class SQLHelper
	{
		/// 连接数据源
		private SqlConnection myConnection = null;
		private readonly string RETURNVALUE = "RETURNVALUE";

		/// <summary>
		/// 打开数据库连接.
		/// </summary>
		private void Open()
		{
			// 打开数据库连接
			if(myConnection == null)
			{
				myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
			}
			if(myConnection.State == ConnectionState.Closed)
			{
				try
				{
					///打开数据库连接
					myConnection.Open();
				}
				catch(Exception ex)
				{
					SystemError.SystemLog(ex.Message);
				}
				finally
				{
					///关闭已经打开的数据库连接				
				}
			}
		}

		/// <summary>
		/// 关闭数据库连接
		/// </summary>
		public void Close()
		{
			///判断连接是否已经创建
			if(myConnection != null)
			{
				///判断连接的状态是否打开
				if(myConnection.State == ConnectionState.Open)
				{
					myConnection.Close();
				}
			}
		}

		/// <summary>
		/// 释放资源
		/// </summary>
		public void Dispose()
		{
			// 确认连接是否已经关闭
			if(myConnection != null)
			{
				myConnection.Dispose();
				myConnection = null;
			}
		}

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

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

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

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

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

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

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

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

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

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

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

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

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

			try
			{
				///读取数据
				da.Fill(dataSet,nStart,nMax,"DataTable");
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.SystemLog(ex.Message);
			}
			finally
			{
				///关闭数据库的连接
				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 void RunProc(string procName,SqlParameter[] prams,ref DataSet dataSet,
			int nStart,int nMax)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(procName,prams);

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

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

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


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

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

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>		
		/// <param name="dataReader">返回DataReader对象</param>
		public void RunSQL(string cmdText,out SqlDataReader dataReader)
		{