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

namespace MCO.ADO.NET
{
    class SqlServerHelper
    {
        #region 该类的核心代码
        /// <summary>
        /// 私有化构造器(单例模式开发数据库查询工具类)
        /// </summary>
        private SqlServerHelper() { }

        /// <summary>
        /// 程序执行前实例化一个数据库帮助类
        /// </summary>
        private static SqlServerHelper sqlServer = new SqlServerHelper();

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string connection;

        /// <summary>
        /// 数据库命令执行方法(SQL语句)
        /// </summary>
        private int Command(string sql)
        {
            SqlConnection conn = new SqlConnection(connection);
            try
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                return command.ExecuteNonQuery();
            }
            catch
            {
                return 0;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 查询(SQL语句)
        /// </summary>
        private DataTable GetList(string sql)
        {
            SqlConnection conn = new SqlConnection(connection);
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                DataTable data = new DataTable();
                adapter.Fill(data);
                return data;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        /// <summary>
        /// 创建该单例类的方法(数据库连接字符串)
        /// </summary>
        public static SqlServerHelper GetSqlServer(string connection)
        {
            sqlServer.connection = connection;
            return sqlServer;
        }

        /// <summary>
        /// 完全查询(表名, 字段) //表名和字段最好加上中括号[Id]
        /// </summary>
        public DataTable GetList(string tableName, string field)
        {
            string sql = string.Format("select {0} from {1}", field, tableName);
            return GetList(sql);
        }

        /// <summary>
        /// 条件查询(表名, 字段, 条件)
        /// </summary>
        public DataTable GetList(string tableName, string field, string where)
        {
            string sql = string.Format("select {0} form {1} where {2}", field, tableName, where);
            return GetList(sql);
        }

        /// <summary>
        /// 分页查询(表名, 字段, 条件, 主键, 页码, 条数)
        /// </summary>
        public DataTable GetList(string tableName, string field, string where, string idField, int page, int size)
        {
            string sql = string.Format("select top {0} {1} from {2} where {3} not in (select top {4} {5} from {6} where {7}) and ({8})",
                                            size, field, tableName, idField, (page - 1) * size, idField, tableName, where, where
                                      );
            return GetList(sql);
        }

        /// <summary>
        /// 排序查询(表名, 字段, 条件, 排序)
        /// </summary>
        public DataTable GetList(string tableName, string field, string where, string order)
        {
            string sql = string.Format("select {0} form {1} where {2} order by {3}", field, tableName, where, order);
            return GetList(sql);
        }

        /// <summary>
        /// 分页排序查询(表名, 字段, 条件, 主键, 页码, 条数, 排序)
        /// </summary>
        public DataTable GetList(string tableName, string field, string where, string idField, int page, int size, string order)
        {
            string sql = string.Format("select top {0} {1} from {2} where {3} not in (select top {4} {5} from {6} where {7}) and ({8}) order by {9}",
                                            size, field, tableName, idField, (page - 1) * size, idField, tableName, where, where, order
                                      );
            return GetList(sql);
        }

        /// <summary>
        /// 条件删除(表名, 条件) //返回受影响的行数, 0 表示失败
        /// </summary>
        public int Delete(string tableName, string where)
        {
            string sql = string.Format("delete from {0} where {1}", tableName, where);
            return Command(sql);
        }

        /// <summary>
        /// 条件修改(表名, 更新的数据, 条件)
        /// </summary>
        public int Update(string tableName, string updateData, string where)
        {
            string sql = string.Format("update {0} set {1} where {2}", tableName, updateData, where);
            return Command(sql);
        }
    }
}