using System;   
using System.Data;   
using System.Data.SqlClient;   
using System.Web.Configuration;   
  
namespace Xiaobin.Shop.DAL   
{   
    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>創(chuàng)建Command對象   
        /// 默認是SQL語句   
        /// </summary>   
        /// <param name="sql">SQL語句</param>   
        public void CreateCommand(string sql)   
        {   
            conn.Open();   
           cmd = new SqlCommand(sql, conn);   
        }   
  
  
        /// <summary>創(chuàng)建存儲過程的Command對象   
        ///    
        /// </summary>   
        /// <param name="procName">存儲過程名稱</param>   
        public void CreateStoredCommand(string procName)   
        {   
            conn.Open();   
            cmd = new SqlCommand(procName, conn);   
            cmd.CommandType = CommandType.StoredProcedure;   
        }   
  
  
        /// <summary>添加參數(shù)   
        /// 默認是輸入?yún)?shù)   
        /// </summary>   
        /// <param name="paramName">參數(shù)名稱</param>   
        /// <param name="value">值</param>   
        public void AddParameter(string paramName, object value)   
        {   
            SqlParameter p = new SqlParameter(paramName, value);   
            cmd.Parameters.Add(p);   
        }   
  
  
        /// <summary>添加輸出參數(shù)   
        /// 用于存儲過程   
        /// </summary>   
        /// <param name="paramName">參數(shù)名稱</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>獲取輸出參數(shù)的值   
        /// 
      /// </summary>   
       /// <param name="paramName">輸出參數(shù)名稱</param>   
       /// <returns></returns>   
        public string GetOutputParameter(string paramName) {   
           return cmd.Parameters[paramName].Value.ToString();   
        }   
  
  
      /// <summary>執(zhí)行增刪改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>執(zhí)行查詢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;   
      }   
  
   }   
}