封装的sql数据访问助手类

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;

namespace DAL
{
    public class sqlHelper
    {
        private SqlConnection conn = null;
        private SqlCommand cmd = null;
        private DataTable dt = null;

        public sqlHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }
        /// <summary>连接对象
        /// 
        /// </summary>
        /// <returns></returns>
        public SqlConnection getConn()
        {

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// <summary>执行返回一行的增删改sql语句或存储过程
        /// 
        /// </summary>
        /// <param name="cmdText">sql语句或存储过程</param>     
        /// <param name="paras">参数变量</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            int i;
            try
            {
                cmd = new SqlCommand(cmdText, getConn());
                cmd.Parameters.AddRange(paras);
                cmd.CommandType = ct;
                i = cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return i;
        }
        /// <summary> 执行返回结果集的sql语句或存储过程的查询
        ///
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="paras"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public DataTable ExecuteReader(string cmdText, SqlParameter[] paras, CommandType ct)
        {
            dt = new DataTable();
            cmd = new SqlCommand(cmdText, getConn());
            cmd.Parameters.AddRange(paras);
            cmd.CommandType = ct;
            using (SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }
        /// <summary>执行返回结果集中第一行第一列的查询
        /// 
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sql)
        {
            using (cmd = new SqlCommand(sql, getConn()))
            {
                string res = cmd.ExecuteScalar().ToString();
                if (res == "" && res == null)
                {
                    return "";
                }
                else
                {
                    return res;
                }
            }
            //以下这种方式也可以
            //try
            //{
            //    cmd = new SqlCommand(sql, getConn());
            //    object obj = cmd.ExecuteScalar();
            //    if (obj != null)
            //    {
            //        return obj.ToString();
            //    }
            //    return "";
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    if (conn.State == ConnectionState.Open)
            //    {
            //        conn.Close();
            //    }
            //}
        }
    }
}