自己写的SQLHelper

/* 
 *  作者：唐山师范学院 张**

 *  创建时间：2011-10-14 21:12:11
 */ 
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    class SQLHelper
    {
        SqlConnection conn = null;
        SqlDataAdapter adapter = null;
        SqlDataReader reader = null;
        /// <summary>
        /// 创建SQLHelper构造函数
        /// </summary>
        public SQLHelper()
        {
            string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            conn = new SqlConnection(connStr);
        }
        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConn()
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        #region ExecuteNonQuery操作，对数据库进行 增、删、改 操作
        /// <summary> 
        /// ExecuteNonQuery操作，对数据库进行 增、删、改 操作(（1） 
        /// </summary> 
        /// <param name="sql">要执行的SQL语句 </param> 
        /// <returns> </returns> 
        /// 
        public int ExecuteNonQuery(string sql)
        {
            int i;

            using (SqlCommand cmd = new SqlCommand(sql, GetConn()))
            {
                i = cmd.ExecuteNonQuery();

            }
            return i;
        }
        /// <summary>
        /// 执行不带参数的增,删,改SQL语句或存储过程
        /// </summary>
        /// <param name="sql">sql语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程、SQL文本）</param>
        /// <returns></returns>
        public int ExcuteNonQuery(string sql, CommandType ct)
        {
            int i;
            using (SqlCommand cmd = GetConn().CreateCommand())
            {
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                i = cmd.ExecuteNonQuery();
            }
            return i;

        }
        /// <summary>
        /// 执行带有参数的增删改SQL语句或存储过程
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本）</param>
        /// <param name="param">参数数组</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, CommandType ct, SqlParameter[] param) 
        {
            int i;
            using (SqlCommand cmd=GetConn().CreateCommand())
            {
                foreach (SqlParameter parameter in param)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                i = cmd.ExecuteNonQuery();
            }

            return i;
        }
        #endregion
        #region 对数据库进行查询 返回DataSet
        /// <summary>
        /// 执行不带参数的SQL语句或存储过程，返回一个DataSet数据集
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本）</param>
        /// <returns>ds</returns>
        public DataSet ExecuteDataSet(string sql,CommandType ct) 
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd=GetConn().CreateCommand())
            {
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        }
        /// <summary>
        /// 执行带参数的SQL语句或存储过程，返回一个DataSet数据集
        /// </summary>
        /// <param name="sql">SQL语句或存储过程<</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本）</param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql,CommandType ct,SqlParameter[] param)
        {
            DataSet ds = new DataSet();
            using (SqlCommand cmd = GetConn().CreateCommand())
            {
                foreach (SqlParameter parameter in param)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
            }
            return ds;
        
        }
        #endregion
        #region 对数据库进行查询 返回DataTable
        /// <summary>
        /// 执行不带参数的Sql查询语句，返回一个DataTable
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本）</param>
        /// <returns>ds</returns>
        public DataTable ExecuteDataTable(string sql,CommandType ct) 
        {
            return ExecuteDataSet(sql, ct).Tables[0];
        }
        /// <summary>
        /// 执行带参数的SQL语句或存储过程，返回一个DataTable
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本）</param>
        /// <returns>ds</returns>
        public DataTable ExecuteDataTable(string sql,CommandType ct,SqlParameter[] param)
        {

           return ExecuteDataSet(sql, ct, param).Tables[0];
        }
        #endregion
        #region  #region 对数据库进行查询 返回返回第一行第一列
        /// <summary>
        /// 执行不带参数的SQL语句或存储过程返回第一行第一列
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本</param>
        /// <returns></returns>

        public object ExecuteScalar(string sql,CommandType ct)
        {
            object obj=null;
            using (SqlCommand cmd=GetConn().CreateCommand())
            {
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                obj= cmd.ExecuteScalar();
            }
            return obj;
        }
        /// <summary>
        /// 执行带参数的SQL语句或存储过程返回第一行第一列
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本</param>
        /// <returns></returns>
        public object ExecuteScalar(string sql,CommandType ct,SqlParameter[] param)
        {
            object obj = null;
            using (SqlCommand cmd = GetConn().CreateCommand())
            {
                foreach (SqlParameter parameter in param)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.CommandType = ct;
                cmd.CommandText = sql;
                obj = cmd.ExecuteScalar();
            }
            return obj;
        
        }
        #endregion
        #region 对数据库进行查询 返回SqlDataReader
        /// <summary>
        /// 执行不带参数的SQL语句或存储过程，返回一个SqlDataReader
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql,CommandType ct)
        {
            using (SqlCommand cmd=GetConn().CreateCommand())
            {
                cmd.CommandText = sql;
                cmd.CommandType = ct;
                reader = cmd.ExecuteReader();
            }
            return reader;
        }
        /// <summary>
        /// 执行带参数的SQL语句或存储过程，返回一个SqlDataReader
        /// </summary>
        /// <param name="sql">SQL语句或存储过程</param>
        /// <param name="ct">要执行的查询类型（存储过程，SQL文本</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string sql, CommandType ct,SqlParameter[] param)
        {
            using (SqlCommand cmd = GetConn().CreateCommand())
            {
                foreach (SqlParameter parameter in param)
                {
                    cmd.Parameters.Add(parameter);
                }
                cmd.CommandText = sql;
                cmd.CommandType = ct;
                reader = cmd.ExecuteReader();
            }
            return reader;
        }
        #endregion
    }
}