sqlhelper.cs

明日Asp.net销售管理系统明日Asp.net销售管理系统

				SystemError.CreateErrorLog(ex.Message);
			}
		}

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <param name="dataReader">返回DataReader对象</param>
		public void RunSQL(string cmdText, SqlParameter[] prams, out SqlDataReader dataReader) 
		{
			///创建Command
			SqlCommand cmd = CreateSQLCommand(cmdText, prams);
			
			try
			{
				///读取数据
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				dataReader = null;
				///记录错误日志
				SystemError.CreateErrorLog(ex.Message);
			}
		}

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public void RunSQL(string cmdText, ref DataSet dataSet) 
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建SqlDataAdapter
			SqlDataAdapter da = CreateSQLDataAdapter(cmdText,null);
			
			try
			{
				///读取数据
				da.Fill(dataSet);
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.CreateErrorLog(ex.Message);
			}
			finally
			{
				///关闭数据库的连接
				Close();	
			}
		}

        public void RunSQL(string cmdText, ref DataSet dataSet,string TableName)
        {
            if (dataSet == null)
            {
                dataSet = new DataSet();
            }
            ///创建SqlDataAdapter
            SqlDataAdapter da = CreateSQLDataAdapter(cmdText, null);

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

		/// <summary>
		/// 执行SQL语句
		/// </summary>
		/// <param name="cmdText">SQL语句</param>
		/// <param name="prams">SQL语句所需参数</param>
		/// <param name="dataSet">返回DataSet对象</param>
		public void RunSQL(string cmdText, SqlParameter[] prams,ref DataSet dataSet) 
		{
			if(dataSet == null)
			{
				dataSet = new DataSet();
			}
			///创建SqlDataAdapter
            SqlDataAdapter da = CreateSQLDataAdapter(cmdText, prams);
			
			try
			{
				///读取数据
				da.Fill(dataSet);
			}
			catch(Exception ex)
			{
				///记录错误日志
				SystemError.CreateErrorLog(ex.Message);
			}
			finally
			{
				///关闭数据库的连接
				Close();	
			}
		}

        public void RunSQL(string cmdText, SqlParameter[] prams, ref DataSet dataSet,string TableName)
        {
            if (dataSet == null)
            {
                dataSet = new DataSet();
            }
            ///创建SqlDataAdapter
            SqlDataAdapter da = CreateSQLDataAdapter(cmdText, prams);

            try
            {
                ///读取数据
                da.Fill(dataSet,TableName);
            }
            catch (Exception ex)
            {
                ///记录错误日志
                SystemError.CreateErrorLog(ex.Message);
            }
            finally
            {
                ///关闭数据库的连接
                Close();
            }
        }
		
		/// <summary>
		/// 创建一个SqlCommand对象以此来执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <returns>返回SqlCommand对象</returns>
		private SqlCommand CreateProcCommand(string procName, SqlParameter[] prams) 
		{
			///打开数据库连接
			Open();
			
			///设置Command
			SqlCommand cmd = new SqlCommand(procName, myConnection);
			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 SqlCommand CreateSQLCommand(string cmdText, SqlParameter[] prams) 
		{
			///打开数据库连接
			Open();
			
			///设置Command
			SqlCommand cmd = new SqlCommand(cmdText,myConnection);			

			///添加把存储过程的参数
			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 SqlDataAdapter CreateProcDataAdapter(string procName,SqlParameter[] prams)
		{
			///打开数据库连接
			Open();
			
			///设置SqlDataAdapter对象
			SqlDataAdapter da = new SqlDataAdapter(procName,myConnection);
			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 SqlDataAdapter CreateSQLDataAdapter(string cmdText,SqlParameter[] prams)
		{
			///打开数据库连接
			Open();
			
			///设置SqlDataAdapter对象
			SqlDataAdapter da = new SqlDataAdapter(cmdText,myConnection);					

			///添加把存储过程的参数
			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>
		/// 生成存储过程参数
		/// </summary>
		/// <param name="ParamName">存储过程名称</param>
		/// <param name="DbType">参数类型</param>
		/// <param name="Size">参数大小</param>
		/// <param name="Direction">参数方向</param>
		/// <param name="Value">参数值</param>
		/// <returns>新的 parameter 对象</returns>
		public 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 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 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 SqlParameter CreateReturnParam(string ParamName, SqlDbType DbType, int Size) 
		{
			return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, null);
		}			
	}