ASP.NET DBHelper

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

namespace JHDAL
{
    public static class DBHelper
    {
        public static SqlConnection connection;
        public static SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    //string connectionString = ConfigurationManager.ConnectionStrings["MyBookShop"].ConnectionString;
                    string strConn = @"Data Source=.;Initial Catalog=MYBOOKSHOP;Integrated Security=True; pwd=AbcXyz123";
                    // string strConn = @"Data Source=MICROSOF-E36F55;integrated security=sspi;database=MyBookShop"; 
                    connection = new SqlConnection(strConn); connection.Open();
                }
                else if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                else if (connection.State == ConnectionState.Broken)
                {
                    connection.Close(); connection.Open();
                }
                return connection;
            }
        }
      /// <summary>
        /// 执行增,删,改,的方法 
      /// </summary>
      /// <param name="commandText"></param>
      /// <param name="commandType"></param>
      /// <param name="para"></param>
      /// <returns></returns>
        public static int ExecuteCommand(string commandText, CommandType commandType, SqlParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            try
            {
                if (para != null)
                {
                    cmd.Parameters.AddRange(para);
                }
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                connection.Close();
            }
        }
       /// <summary>
        /// 执行查询的方法
       /// </summary>
       /// <param name="commandText"></param>
       /// <param name="commandType"></param>
       /// <param name="para"></param>
       /// <returns></returns>
        public static DataTable GetDataTable(string commandText, CommandType commandType, SqlParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            try
            {
                if (para != null)
                {
                    cmd.Parameters.AddRange(para);
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable temp = new DataTable();
                da.Fill(temp); return temp;
            }
            finally
            {
                connection.Close();
            }
        }
       /// <summary>
        /// 执行少量查询的方法 
       /// </summary>
       /// <param name="commandText"></param>
       /// <param name="commandType"></param>
       /// <param name="para"></param>
       /// <returns></returns>
        public static SqlDataReader GetReader(string commandText, CommandType commandType, SqlParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            if (para != null)
            {
                cmd.Parameters.AddRange(para);
            } return cmd.ExecuteReader();
        }
        /// <summary>
        ///  执行聚合函数的方法 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public static object GetScalar(string commandText, CommandType commandType, SqlParameter[] para)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = Connection;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            try
            {
                if (para != null)
                {
                    cmd.Parameters.AddRange(para);
                } return cmd.ExecuteScalar();
            }
            finally
            {
                connection.Close();
            }
        }
    }
}