Sql Server 数据库操作类

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;  
        }  
    }  
}