sqlhelper.cs

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

			}
		}

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

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

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public static void RunSQL(string cmdText,ref DataSet dataSet)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateSQLDataAdapter(sqlCon,cmdText,null);

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

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public static void RunSQL(string cmdText,SqlParameter[] prams,ref DataSet dataSet)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateProcDataAdapter(sqlCon,cmdText,prams);

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

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="dataSet">返回DataSet对象</param>
		/// <param name="nStart">开始记录</param>
		/// <param name="nMax">返回记录数量</param>
		public static void RunSQL(string cmdText,ref DataSet dataSet,
			int nStart,int nMax)
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建并打开连接
			SqlConnection sqlCon = CreateAndOpen();
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateSQLDataAdapter(sqlCon,cmdText,null);

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

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

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

		#endregion

		#region 创建Command和DataAdapter
		/// <summary>
		/// 创建一个SqlCommand对象以此来执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <returns>返回SqlCommand对象</returns>
		private static SqlCommand CreateProcCommand(SqlConnection sqlCon,
			string procName,SqlParameter[] prams)
		{
			sqlCon.Open();
			///设置Command
			SqlCommand cmd = new SqlCommand(procName,sqlCon);
			cmd.CommandType = CommandType.StoredProcedure;

			///添加把存储过程的参数
			if(prams != null)
			{
				foreach(SqlParameter parameter in prams)
				{
					cmd.Parameters.Add(parameter);
				}
			}

			///添加返回参数ReturnValue
			cmd.Parameters.Add(
				new SqlParameter(RETURNVALUE,
				SqlDbType.Int,4,ParameterDirection.ReturnValue,
				false,0,0,string.Empty,DataRowVersion.Default,null));

			///返回创建的SqlCommand对象
			return cmd;
		}

		/// <summary>
		/// 创建一个SqlCommand对象以此来执行存储过程
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <returns>返回SqlCommand对象</returns>
		private static SqlCommand CreateSQLCommand(SqlConnection sqlCon,
			string cmdText,SqlParameter[] prams)
		{
			sqlCon.Open();
			///设置Command
			SqlCommand cmd = new SqlCommand(cmdText,sqlCon);

			///添加把存储过程的参数
			if(prams != null)
			{
				foreach(SqlParameter parameter in prams)
				{
					cmd.Parameters.Add(parameter);
				}
			}

			///添加返回参数ReturnValue
			cmd.Parameters.Add(
				new SqlParameter(RETURNVALUE,SqlDbType.Int,4,ParameterDirection.ReturnValue,
				false,0,0,string.Empty,DataRowVersion.Default,null));

			///返回创建的SqlCommand对象
			return cmd;
		}

		/// <summary>
		/// 创建一个SqlDataAdapter对象，用此来执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <returns>返回SqlDataAdapter对象</returns>
		private static SqlDataAdapter CreateProcDataAdapter(SqlConnection sqlCon,
			string procName,SqlParameter[] prams)
		{
			sqlCon.Open();
			///设置SqlDataAdapter对象
			SqlDataAdapter da = new SqlDataAdapter(procName,sqlCon);
			da.SelectCommand.CommandType = CommandType.StoredProcedure;

			///添加把存储过程的参数
			if(prams != null)
			{
				foreach(SqlParameter parameter in prams)
				{
					da.SelectCommand.Parameters.Add(parameter);
				}
			}

			///添加返回参数ReturnValue
			da.SelectCommand.Parameters.Add(
				new SqlParameter(RETURNVALUE,SqlDbType.Int,4,ParameterDirection.ReturnValue,
				false,0,0,string.Empty,DataRowVersion.Default,null));

			///返回创建的SqlDataAdapter对象
			return da;
		}

		/// <summary>
		/// 创建一个SqlDataAdapter对象，用此来执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <returns>返回SqlDataAdapter对象</returns>
		private static SqlDataAdapter CreateSQLDataAdapter(SqlConnection sqlCon,
			string cmdText,SqlParameter[] prams)
		{
			sqlCon.Open();
			///设置SqlDataAdapter对象
			SqlDataAdapter da = new SqlDataAdapter(cmdText,sqlCon);

			///添加把存储过程的参数
			if(prams != null)
			{
				foreach(SqlParameter parameter in prams)
				{
					da.SelectCommand.Parameters.Add(parameter);
				}
			}

			///添加返回参数ReturnValue
			da.SelectCommand.Parameters.Add(
				new SqlParameter(RETURNVALUE,SqlDbType.Int,4,ParameterDirection.ReturnValue,
				false,0,0,string.Empty,DataRowVersion.Default,null));

			///返回创建的SqlDataAdapter对象
			return da;
		}

		#endregion

		#region 创建参数

		/// <summary>
		/// 生成存储过程参数
		/// </summary>
		/// <param name="ParamName">存储过程名称</param>
		/// <param name="DbType">参数类型</param>
		/// <param name="Size">参数大小</param>
		/// <param name="Direction">参数方向</param>
		/// <param name="Value">参数值</param>
		/// <returns>新的 parameter 对象</returns>
		private static SqlParameter CreateParam(string ParamName,SqlDbType DbType,Int32 Size,ParameterDirection Direction,object Value)
		{
			SqlParameter param;

			///当参数大小为0时，不使用该参数大小值
			if(Size > 0)
			{
				param = new SqlParameter(ParamName,DbType,Size);
			}
			else
			{
				///当参数大小为0时，不使用该参数大小值
				param = new SqlParameter(ParamName,DbType);
			}

			///创建输出类型的参数
			param.Direction = Direction;
			if(!(Direction == ParameterDirection.Output && Value == null))
			{
				param.Value = Value;
			}

			///返回创建的参数
			return param;
		}

		/// <summary>
		/// 传入输入参数
		/// </summary>
		/// <param name="ParamName">存储过程名称</param>
		/// <param name="DbType">参数类型</param></param>
		/// <param name="Size">参数大小</param>
		/// <param name="Value">参数值</param>
		/// <returns>新的parameter 对象</returns>
		public static SqlParameter CreateInParam(string ParamName,SqlDbType DbType,int Size,object Value)
		{
			return CreateParam(ParamName,DbType,Size,ParameterDirection.Input,Value);
		}

		/// <summary>
		/// 传入返回值参数
		/// </summary>
		/// <param name="ParamName">存储过程名称</param>
		/// <param name="DbType">参数类型</param>
		/// <param name="Size">参数大小</param>
		/// <returns>新的 parameter 对象</returns>
		public static SqlParameter CreateOutParam(string ParamName,SqlDbType DbType,int Size)
		{
			return CreateParam(ParamName,DbType,Size,ParameterDirection.Output,null);
		}

		/// <summary>
		/// 传入返回值参数
		/// </summary>
		/// <param name="ParamName">存储过程名称</param>
		/// <param name="DbType">参数类型</param>
		/// <param name="Size">参数大小</param>
		/// <returns>新的 parameter 对象</returns>
		public static SqlParameter CreateReturnParam(string ParamName,SqlDbType DbType,int Size)
		{
			return CreateParam(ParamName,DbType,Size,ParameterDirection.ReturnValue,null);
		}

		#endregion		
	}
}