SQLsever的DBHelper类

SQLsever的DBHelper类

 /// <summary>
    /// 数据辅助类（维护数据库）
    /// </summary>
    class DBHelper
    {
        private string connstr = "Data Source=PKBR49YZZLA3X8L\\SQLEXPRESS;Initial Catalog=CanYingDB;User id=sa;PassWord=sa";
        //private string connstr = "Data Source=.;Initial Catalog=CanYingDB;User id=sa;PassWord=sa";

        /// <summary>
        /// 连接对象
        /// </summary>
        private SqlConnection connection;
        
        public SqlConnection Connection
        {
            get
            {
                if(connection==null)
                {
                    connection = new SqlConnection(connstr);
                }
                return connection;
            }
        }

        /// <summary>
        /// 数据集
        /// </summary>
        public DataSet ds = new DataSet();

        /// <summary>
        /// 适配器
        /// </summary>
        public SqlDataAdapter adapter;

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void OpenConnection()
        {
            if(connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            else if(connection.State == ConnectionState.Broken)
            {
                connection.Close();
                connection.Open();
            }
            SqlDataAdapter adapter = new SqlDataAdapter();
            
        }
    
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseConnection()
        {
            if (Connection.State == ConnectionState.Open || Connection.State == ConnectionState.Broken)
            {
                Connection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句的对象
        /// </summary>
        /// <param name="connection"> SQL语句 </param>
        /// <returns> Command对象 </returns>
        public SqlCommand Command(string SQL)
        {
            return new SqlCommand(SQL,Connection);
        }

        /// <summary>
        /// 执行增删改操作，返回0和大于0的值
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SQL)
        {
            try
            {
                SqlCommand comm = Command(SQL);
                OpenConnection();
                int i = comm.ExecuteNonQuery();
                if ( i > 0 )
                {
                    return i;
                }
                return 0;
            }
            catch (Exception)
            {
                return 0;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行特殊情况(数据备份T-SQL语句),错误返回-1，i为随意值
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SQL,int i)
        {
            try
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = Connection;
                comm.CommandType = CommandType.Text;
                comm.CommandText = SQL;
                OpenConnection();
                comm.ExecuteNonQuery();
                return 0;
            }
            catch (Exception)
            {
                return -1;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行查询单个值的方法,返回Object
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns>true/false</returns>
        public object ExecuteScalar(string SQL)
        {
            try
            {
                SqlCommand comm = Command(SQL);
                OpenConnection();
                return comm.ExecuteScalar();
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 执行查询结果集的方法,返回SqlDateReader
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns>异常（null）</returns>
        public SqlDataReader ExecuteReader(string SQL)
        {
            try
            {
                SqlCommand comm = Command(SQL);
                OpenConnection();
                return comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 接收SQL语句和表名称，返回数据源
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="Name"></param>
        /// <returns></returns>
        public DataTable GetTable(string SQL, string Name)
        {
            adapter = new SqlDataAdapter(SQL, Connection); //实例化适配器

            if (ds.Tables[Name] != null)
            {
                ds.Tables[Name].Clear(); //清除原有数据
            }

            adapter.Fill(ds, Name); //添加数据集

            return ds.Tables[Name];
        }