SQLHelper类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class SQLhepler
    {
        private SqlConnection connection = null;
        private SqlCommand comm = null;
        private SqlDataReader der = null;
        public SQLhepler()
        {
            string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
            connection = new SqlConnection(connstr);
        }
        private SqlConnection getconn()
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            return connection;
        }
        /// <summary>
        /// 执行不带参数的sql增删改语句或存储过程
        /// </summary>
        /// <param name="cmdtext">增删改sql或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdtext, CommandType ct)
        {
            int ints;
            try
            {
                comm = new SqlCommand(cmdtext, getconn());
                comm.CommandType = ct;
                ints = comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return ints;
        }
        /// <summary>
        /// 执行不带参数的sql查询语言或存储过程
        /// </summary>
        /// <param name="cmdtext">sql查询语言或存储过程</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdtext, CommandType ct)
        {
            DataTable dt = new DataTable();
            comm = new SqlCommand(cmdtext, getconn());
            comm.CommandType = ct;
            using (der = comm.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(der);
            }
            return dt;
        }
        /// <summary>
        /// 执行带参数的sql增删改语句或存储过程
        /// </summary>
        /// <param name="cmdtext">sql增删改语句或存储过程</param>
        /// <param name="para">参数集合</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdtext, SqlParameter[] para, CommandType ct)
        {
            int ints;
            using (comm = new SqlCommand(cmdtext, getconn()))
            {
                comm.CommandType = ct;
                comm.Parameters.AddRange(para);
                ints = comm.ExecuteNonQuery();
            }

            return ints;
        }
        /// <summary>
        /// 执行带参数的sql查询语句或存储过程;
        /// </summary>
        /// <param name="cmdtext"></param>
        /// <param name="para"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdtext, SqlParameter[] para, CommandType ct)
        {
            DataTable dt = new DataTable();
            comm = new SqlCommand(cmdtext, getconn());
            comm.CommandType = ct;
            comm.Parameters.AddRange(para);
            using (der = comm.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(der);
            }
            return dt;
        }
    }
}