using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class CommonConn
    {
        private SqlConnection con;
        public CommonConn()
        {
            // 
            // TODO: 在此处添加构造函数逻辑 
            // 
        }

        #region 建立数据库连接
        public void OpenConn(string conn)
        {
            if (con == null)
            {
                con = new SqlConnection(conn);
                con.Open();
            }
        }
        #endregion

        #region 关闭连接
        public void CloseConn(string conn)
        {
            if (con != null)
            {
                con.Close();
            }
        }
        #endregion

        #region 释放连接
        public void DisposeConn()
        {
            if (con != null)
            {
                con.Dispose();
                con = null;
            }
        }
        #endregion

        #region 返回一个数据集
        public DataSet SqlDataSet(string conn, string Sql, string tableName)
        {
            OpenConn(conn);
            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(Sql, con);
            da.Fill(ds, tableName);
            CloseConn(conn);
            return ds;
        }
        #endregion

        #region 执行一个SQL操作:添加、删除、更新操作
        public void SqlExcute(string conn, string sql)
        {
            OpenConn(conn);
            SqlCommand cmd;
            cmd = new SqlCommand(sql, con);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            CloseConn(conn);
        }
        #endregion

        #region 执行一个SQL操作:添加、删除、更新操作，返回受影响的行
        public int SqlExecuteNonQuery(string conn, string sql)
        {
            OpenConn(conn);
            SqlCommand cmd;
            cmd = new SqlCommand(sql, con);
            int flag = cmd.ExecuteNonQuery();
            CloseConn(conn);
            return flag;
        }
        #endregion

        #region 返回一个对象
        public object SqlExecuteScalar(string conn, string sql)
        {
            OpenConn(conn);
            SqlCommand cmd;
            cmd = new SqlCommand(sql, con);
            object obj = cmd.ExecuteScalar();
            cmd.Dispose();
            CloseConn(conn);
            return obj;
        }
        #endregion

        #region 返回DataTable
        public DataTable SqlDataTable(string conn, string sql)
        {
            OpenConn(conn);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds, "table");
            CloseConn(conn);
            return ds.Tables["table"];
        }
        #endregion

        #region 返回一个数据集的记录数
        public int SqlRecordCount(string conn, string sql)
        {
            //注：Sql 语句必须是一个统计查询 
            OpenConn(conn);
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Connection = con;
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            int RecordCount = -1;
            while (dr.Read())
            {
                RecordCount = int.Parse(dr[0].ToString());
            }
            CloseConn(conn);
            return RecordCount;
        }
        #endregion
    }
}