自己封装的ASP.NET的MSSQL的数据库操作类(MSSQLHelper)

using System;   
using System.Data;   
using System.Data.SqlClient;   
using System.Web.Configuration;   
  
 
namespace Niunan.Tg029.Hr.Utility   
{   
    public class MSSQLHelper   
    {   
       private SqlConnection conn = null;   
       private SqlCommand cmd = null;   
        private SqlDataReader sdr = null;   
  
 
        public MSSQLHelper()   
        {   
           string connStr = WebConfigurationManager.ConnectionStrings["connStr"].ToString();   
            conn = new SqlConnection(connStr);   
       }   
  
  
       /// <summary>创建Command对象   
        /// 默认是SQL语句   
        /// </summary>   
        /// <param name="sql">SQL语句</param>   
       public void CreateCommand(string sql)   
        {   
           conn.Open();   
           cmd = new SqlCommand(sql, conn);   
       }   
  
 
       /// <summary>创建存储过程的Command对象   
       ///    
       /// </summary>   
        /// <param name="procName">存储过程名称</param>   
       public void CreateStoredCommand(string procName)   
       {   
            conn.Open();   
           cmd = new SqlCommand(procName, conn);   
            cmd.CommandType = CommandType.StoredProcedure;   
        }   
  
        /// <summary>添加参数   
       /// 默认是输入参数   
       /// </summary>   
        /// <param name="paramName">参数名称</param>   
       /// <param name="value">值</param>   
       public void AddParameter(string paramName, object value)   
       {   
           SqlParameter p = new SqlParameter(paramName, value);   
            cmd.Parameters.Add(p);   
        }   
  
 
        /// <summary>添加输出参数   
        /// 用于存储过程   
        /// </summary>   
        /// <param name="paramName">参数名称</param>   
        /// <param name="value">值</param>   
        public void AddOutputParameter(string paramName) {   
            SqlParameter p = new SqlParameter();   
            p.ParameterName = paramName;   
            p.Direction = ParameterDirection.Output;   
            p.Size = 20;   
            cmd.Parameters.Add(p);   
        }   
  
  
        /// <summary>获取输出参数的值   
        ///    
        /// </summary>   
        /// <param name="paramName">输出参数名称</param>   
        /// <returns></returns>   
        public string GetOutputParameter(string paramName) {   
            return cmd.Parameters[paramName].Value.ToString();   
        }   
  
  
        /// <summary>执行增删改SQL语句或存储过程   
        ///     
        /// </summary>   
        /// <returns></returns>   
        public bool ExecuteNonQuery()   
        {   
            int res;   
            try  
            {   
                res = cmd.ExecuteNonQuery();   
              if (res > 0)   
                {   
                    return true;   
                }   
            }   
            catch (Exception ex)   
            {   
                throw ex;   
            }   
            finally  
            {   
                if (conn.State == ConnectionState.Open)   
                {   
                    conn.Close();   
                }   
            }   
            return false;   
        }   
  
  
        /// <summary>执行查询SQL语句或存储过程   
        ///     
        /// </summary>   
        /// <returns></returns>   
        public DataTable ExecuteQuery()   
        {   
            DataTable dt = new DataTable();   
          using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))   
            {   
                dt.Load(sdr);   
            }   
            return dt;   
        }   
  
  
        /// <summary>返回查询SQL语句或存储过程查询出的结果的第一行第一列的值   
        ///    
        /// </summary>   
        /// <returns></returns>   
        public string ExecuteScalar()   
        {   
           string res = "";   
           try  
           {   
                object obj = cmd.ExecuteScalar();   
               if (obj != null)   
                {   
                   res = obj.ToString();   
                }   
           }   
           catch (Exception ex)   
           {   
                throw ex;   
            }   
           finally  
           {   
                if (conn.State == ConnectionState.Open)   
               {   
                   conn.Close();   
               }   
            }   
            return res;   
       }   
 
 
  
  
    }   
}