自己封装的SQLSERVER 数据操作类

*
 * 作者 : Vernon start
 * 
 * 创建时间 ：2010-3-5
 * 
 * Email : 87690816@qq.com
 * 
 * 说明 ： 对数据操作的一个类 增,删,改,查
 * 
 * */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace DAL
{
    public class SqlHelper
    {
        private  SqlConnection conn = null;  //连接字符串
        private SqlCommand cmd = null;　　//执行SQL语句
        private SqlDataReader sdr = null; //读取数据
        //private readonly string  ConnString = "Data Source=.;Initial Catalog=newssystem;uid=sa;pwd=birdwangjianxing";
        private string ConnSr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
        /// <summary>
        /// 该方法是连接字符串
        /// </summary>
        /// <returns>返回一个SqlConnection</returns>
        public SqlConnection GetConnction()
        {

            conn = new SqlConnection(ConnSr);
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn;
        }
        /// <summary>
        /// 关闭连接释放资源
        /// </summary>
        /// <param name="conn">无返回方法</param>
        public void CloseConnedction(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 该方法执行增，删，改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回个bool类型</returns>
        public int GetExecuNonQuery(string sql)
        {

            try
            {

                conn = GetConnction();
                cmd = new SqlCommand(sql, conn);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnedction(conn);
            }

        }
        /// <summary>
        /// 带参数的增加新闻类别
        /// </summary>
        /// <param name="sql">传一个SQL语句</param>
        /// <param name="spras">传一个参数数组</param>
        /// <returns>返回int类型</returns>
        public int GetExecuteNonQuery(string sql, SqlParameter[] spras)
        {

            using(cmd = new SqlCommand(sql, GetConnction()))
            {
              cmd.Parameters.AddRange(spras);

            }
            return cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// 该方法执行第一行第一列　
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回int类型　</returns>
        public int GetExecuteScalarg(string sql)
        {
            try
            {

                cmd = new SqlCommand(sql, GetConnction());
                int result = Convert.ToInt32(cmd.ExecuteScalar());
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnedction(conn);
            }
        }
       /// <summary>
       /// 该方法执行类型查询
       /// </summary>
       /// <param name="cmdText">类型</param>
       /// <param name="paras">参数集合</param>
       /// <param name="ct"></param>
       /// <returns></returns>
        public DataTable GetDatatable(string cmdText,SqlParameter [] paras,CommandType ct)
        {

            try
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand(cmdText, GetConnction());
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 该方法执行普通类型查询
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public DataTable GetDatatables(string cmdText )
        {

            try
            {
                DataTable dt = new DataTable();
                cmd = new SqlCommand(cmdText, GetConnction());
                using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    dt.Load(sdr);
                }
                return dt;
            }
            catch (SqlException ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 该方法是一个数据集方法再很多地方用得到的　
        /// </summary>
        /// <param name="sql">第一个参数是传sql语句</param>
        /// <param name="table">第二个参数是传数据库当中的表名</param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, string table)
        {
            try
            {

                SqlDataAdapter sda = new SqlDataAdapter(sql,GetConnction());
                DataSet ds = new DataSet();
                sda.Fill(ds, table);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnedction(conn);
            }
        }

    }
}