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);   
       }   
  
  
       /// 
´´½¨Command¶ÔÏó   
        /// ĬÈÏÊÇSQLÓï¾ä   
        /// 
   
        /// SQLÓï¾ä   
       public void CreateCommand(string sql)   
        {   
           conn.Open();   
           cmd = new SqlCommand(sql, conn);   
       }   
  
 
       /// 
´´½¨´æ´¢¹ý³ÌµÄCommand¶ÔÏó   
       ///    
       /// 
   
        /// ´æ´¢¹ý³ÌÃû³Æ   
       public void CreateStoredCommand(string procName)   
       {   
            conn.Open();   
           cmd = new SqlCommand(procName, conn);   
            cmd.CommandType = CommandType.StoredProcedure;   
        }   
  
        /// 
Ìí¼Ó²ÎÊý   
       /// ĬÈÏÊÇÊäÈë²ÎÊý   
       /// 
   
        /// ²ÎÊýÃû³Æ   
       /// Öµ   
       public void AddParameter(string paramName, object value)   
       {   
           SqlParameter p = new SqlParameter(paramName, value);   
            cmd.Parameters.Add(p);   
        }   
  
 
        /// 
Ìí¼ÓÊä³ö²ÎÊý   
        /// ÓÃÓÚ´æ´¢¹ý³Ì   
        /// 
   
        /// ²ÎÊýÃû³Æ   
        /// Öµ   
        public void AddOutputParameter(string paramName) {   
            SqlParameter p = new SqlParameter();   
            p.ParameterName = paramName;   
            p.Direction = ParameterDirection.Output;   
            p.Size = 20;   
            cmd.Parameters.Add(p);   
        }   
  
  
        /// 
»ñÈ¡Êä³ö²ÎÊýµÄÖµ   
        ///    
        /// 
   
        /// Êä³ö²ÎÊýÃû³Æ   
        ///    
        public string GetOutputParameter(string paramName) {   
            return cmd.Parameters[paramName].Value.ToString();   
        }   
  
  
        /// 
Ö´ÐÐÔöɾ¸ÄSQLÓï¾ä»ò´æ´¢¹ý³Ì   
        ///     
        /// 
   
        ///    
        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;   
        }   
  
  
        /// 
Ö´ÐвéѯSQLÓï¾ä»ò´æ´¢¹ý³Ì   
        ///     
        /// 
   
        ///    
        public DataTable ExecuteQuery()   
        {   
            DataTable dt = new DataTable();   
          using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))   
            {   
                dt.Load(sdr);   
            }   
            return dt;   
        }   
  
  
        /// 
·µ»Ø²éѯSQLÓï¾ä»ò´æ´¢¹ý³Ì²éѯ³öµÄ½á¹ûµÄµÚÒ»ÐеÚÒ»ÁеÄÖµ   
        ///    
        /// 
   
        ///    
        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;   
       }   
 
 
  
  
    }   
}