sqlhelper.cs

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

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ComponentModel;
using System.Web.Configuration;


//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)


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

		/// <summary>
		/// 打开数据库连接.
		/// </summary>
		public  void Open() 
		{
			// 打开数据库连接
			if (myConnection == null) 
			{
                myConnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["XMZLConnectionString"].ConnectionString);		
			}				
			if(myConnection.State == ConnectionState.Closed)
			{   
				try
				{
					///打开数据库连接
					myConnection.Open();
				}
				catch(Exception ex)
				{
					SystemError.CreateErrorLog(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.CreateErrorLog(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.CreateErrorLog(ex.Message);
			}
			finally
			{
				///关闭数据库的连接
				Close();
			}
//5_1_a_s_p_x.c_o_m
			
			///返回存储过程的参数值
			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.CreateErrorLog(ex.Message);
			}
		}

		/// <summary>
		/// 执行存储过程
		/// </summary>
		/// <param name="procName">存储过程的名称</param>
		/// <param name="prams">存储过程所需参数</param>
		/// <param name="dataSet">返回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.CreateErrorLog(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.CreateErrorLog(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.CreateErrorLog(ex.Message);
			}
			finally
			{
				///关闭数据库的连接
				Close();	
			}
		}

        public void RunProc(string procName, SqlParameter[] prams, ref DataSet dataSet,string DataTableName)
        {
            if (dataSet == null)
            {
                dataSet = new DataSet();
            }
            ///创建SqlDataAdapter
            SqlDataAdapter da = CreateProcDataAdapter(procName, prams);

            try
            {
                ///读取数据
                da.Fill(dataSet,DataTableName);
            }
            catch (Exception ex)
            {
                ///记录错误日志
                SystemError.CreateErrorLog(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.CreateErrorLog(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.CreateErrorLog(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) 
		{
			///创建Command
			SqlCommand cmd = CreateSQLCommand(cmdText, null);
			
			try
			{
				///读取数据
				dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);	
			}
			catch(Exception ex)
			{
				dataReader = null;
				///记录错误日志