using System;
using System.Collections.Generic;
using System.Web;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Caching;
using System.Text;

using Newtonsoft.Json;
using Newtonsoft.Json.Converters;

/// <summary>
///SqlHelper 的摘要说明
/// </summary>
public abstract class SqlHelper {
  //连接字符串
  public static readonly string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

  public SqlHelper() { }

  /// <summary>
  /// 执行SQL命令，返回DataTable，用Table.Rows.Count来判断是否有结果
  /// </summary>
  public static DataTable ExecuteDataTable(string sql) {
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
      con.Open();
      using (SqlCommand cmd = new SqlCommand(sql, con)) {
        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
          DataTable dt = new DataTable();
          dt.Load(dr);
          return dt;
        }
      }
    }
  }

  /// <summary>
  /// 执行带参数的存储过程
  /// </summary>
  public static DataTable ExecuteProcedure(string ProcName, SqlParameter[] param) {
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
      con.Open();
      using (SqlCommand cmd = new SqlCommand(ProcName, con)) {
        cmd.CommandType = CommandType.StoredProcedure;
        if (param != null) { foreach (SqlParameter parameter in param) { cmd.Parameters.Add(parameter); } }
        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
          DataTable dt = new DataTable();
          dt.Load(dr);
          return dt;
        }
      }
    }
  }

  /// <summary>
  /// 执行返回一行记录的SQL语句，如果没有结果则返回null
  /// </summary>
  public static DataRow ExecuteDataRow(string sql) {
    using (DataTable dt = ExecuteDataTable(sql)) {
      if (dt.Rows.Count == 0) { return null; }
      else { return dt.Rows[0]; }
    }
  }

  /// <summary>
  /// 执行不返回数据的SQL语句，返回受影响的行数
  /// </summary>
  public static int ExecuteNonQuery(string sql) {
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
      con.Open();
      using (SqlCommand cmd = new SqlCommand(sql, con)) {
        int val = cmd.ExecuteNonQuery();
        return val;
      }
    }
  }

  /// <summary>
  /// 执行不返回数据的SQL语句
  /// </summary>
  public static void ExecuteNonQueryVoid(string sql) {
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
      con.Open();
      using (SqlCommand cmd = new SqlCommand(sql, con)) {
        cmd.ExecuteNonQuery();
      }
    }
  }

  /// <summary>
  /// 执行SQL命令，返回单一值，数据集为空返回null
  /// </summary>
  public static object ExecuteScalar(string sql) {
    using (SqlConnection con = new SqlConnection(ConnectionString)) {
      con.Open();
      using (SqlCommand cmd = new SqlCommand(sql, con)) {
        object val = cmd.ExecuteScalar();
        return val;
      }
    }
  }

  /// <summary>
  /// 执行SQL命令，返回Json字符串；如果是top1则：含两端花括号，不含方括号；如果不是top1则：含两端方括号，无数据（无论取多条单条）都返回：[]
  /// </summary>
  public static string ExecuteJson(string sql) {
    string json = string.Empty;
    using (DataTable dt = ExecuteDataTable(sql)) {
      json = JsonConvert.SerializeObject(dt, new DataTableConverter(), new IsoDateTimeConverter { DateTimeFormat = "yyyy-MM-dd HH:mm:ss" });
      if (sql.IndexOf(" top 1 ") == 6 && json != "[]") { json = json.Substring(1, json.Length - 2); }
    }
    return json;
  }

  /// <summary>
  /// 传入Sql语句、每页行数和页码，返回当前页Json字符串，sql语句中必须有order by，没有记录返回[]
  /// </summary>
  public static string ExecuteJson(string sql, int rows, int page) {
    string orderby = sql.Substring(sql.LastIndexOf(" order by ") + 10);
    page = page == 0 ? 1 : page;
    int start = rows * (page - 1) + 1;
    int end = rows * page;
    sql = sql.Substring(7);
    sql = sql.Substring(0, sql.LastIndexOf(" order by "));
    sql = "with temptb as (select row_number() over (order by " + orderby + ") as row," + sql + ") select * from temptb where row between " + start + " and " + end;
    return ExecuteJson(sql);
  }

  /// <summary>
  /// 传入Sql语句、要取的行数和开始行号（由于重载需要，此项为string类型），返回Json字符串
  /// </summary>
  public static string ExecuteJson(string sql, int rows, string start) {
    string orderby = sql.Substring(sql.LastIndexOf(" order by ") + 10);
    int end = int.Parse(start) + rows;
    sql = sql.Substring(7);
    sql = sql.Substring(0, sql.LastIndexOf(" order by "));
    sql = "with temptb as (select row_number() over (order by " + orderby + ") as row," + sql + ") select * from temptb where row between " + start + " and " + end;
    return ExecuteJson(sql);
  }

  /// <summary>
  /// 传入DataRow数据，返回Json字串，不包含两端花括号
  /// </summary>
  public static string ExecuteJson(DataRow dr) {
    //Newtonsoft.Json貌似不能实现DataRow的转换，只能人工转
    StringBuilder json = new StringBuilder();
    for (int i = 0; i < dr.Table.Columns.Count; i++) {
      if (i > 0) { json.Append(","); }
      json.Append("\"" + dr.Table.Columns[i].Caption + "\":");
      Type type = dr[i].GetType(); //当前列数据类型
      if (Convert.IsDBNull(dr[i])) { json.Append("null"); }
      else if (type == typeof(bool)) { json.Append((bool)dr[i] ? 1 : 0); }
      else if (type == typeof(byte) || type == typeof(short) || type == typeof(int)) { json.Append(dr[i]); }
      else if (type == typeof(DateTime)) { json.Append("\"" + dr[i].ToString().Replace("/", "-") + "\""); }
      else {
        string str = dr[i].ToString();
        str = str.Replace("\\", "\\\\").Replace("\"", "\\\"").Replace("\r", "\\r").Replace("\n", "\\n").Replace("\t", "\\t");
        json.Append("\"" + str + "\"");
      }
    }
    return json.ToString();
  }

  /// <summary>
  /// 返回easyui-datagrid可用的json数据，无分页
  /// </summary>
  public static string ExecuteEasyuiDatagrid(string sql) {
    string json = string.Empty;
    int count = GetRecordCount(sql);
    if (count == 0) { json = "{\"total\":0,\"rows\":[]}"; }
    else { json = "{\"total\":" + count + ",\"rows\":" + ExecuteJson(sql) + "}"; }
    return json;
  }

  /// <summary>
  /// 返回easyui-datagrid可用的json数据，有分页
  /// </summary>
  public static string ExecuteEasyuiDatagrid(string sql, int rows, int page) {
    string json = string.Empty;
    int count = GetRecordCount(sql);
    if (count == 0) { json = "{\"total\":0,\"rows\":[]}"; }
    else { json = "{\"total\":" + count + ",\"rows\":" + ExecuteJson(sql, rows, page) + "}"; }
    return json;
  }

  /// <summary>
  /// 从缓存中读取某一表，返回DataTable，如果缓存没有则自动创建
  /// </summary>
  public static DataTable GetDataTableFromCache(string TableName) {
    string CacheName = "Table_" + TableName.ToUpper();
    Cache Cache = HttpContext.Current.Cache;

    DataTable dt = (DataTable)Cache[CacheName];
    if (dt == null) {
      dt = ExecuteDataTable("select * from " + TableName + " order by id");
      Cache.Insert(CacheName, dt);
    }

    return dt;
  }

  /// <summary>
  /// 根据SQL命令返回记录总数
  /// </summary>
  public static int GetRecordCount(string sql) {
    if (sql.IndexOf(" order by ") >= 0) { sql = sql.Substring(0, sql.LastIndexOf(" order by ")); }
    sql = sql.Substring(sql.IndexOf(" from ") + 6);
    sql = "select count(id) from " + sql;
    return (int)ExecuteScalar(sql);
  }

  /// <summary>
  /// 获取不是自动增长ID列的编号，传入表名称和起始数字
  /// </summary>
  public static int GetNewID(string TableName, int n) {
    string sql = string.Empty;
    sql = "with temptb as (select id,id-(row_number()over(order by id)+" + (n - 1) + ") as diff from [" + TableName + "] where id>" + (n - 1) + ") select max(id)+1 from temptb where diff=0";
    object newid = ExecuteScalar(sql);
    if (newid == null || Convert.IsDBNull(newid)) { return n; }
    else { return (int)newid; }
  }

  /// <summary>
  /// 获取不是自动增长ID列的编号，传入表名，默认从1开始
  /// </summary>
  public static int GetNewID(string TableName) {
    return GetNewID(TableName, 1);
  }

  /// <summary>
  /// 判断一条SQL语句查询是否有记录，有则返回true，没有则返回false
  /// </summary>
  public static bool CheckRecordExists(string sql) {
    object obj = ExecuteScalar(sql);
    if (Object.Equals(obj, null) || Object.Equals(obj, System.DBNull.Value)) { return false; }
    else { return true; }
  }


  /// <summary>
  /// 编码SQL命令中的单引号，进行HtmlDecode，过滤iframe和script
  /// </summary>
  public static string SqlEncode(string str) {
    if (str == null) { return string.Empty; }
    {
      str = str.Trim();
      if (str != string.Empty) {
        str = str.Replace("'", "''");
        str = HttpUtility.HtmlDecode(str);
        str = str.Replace("<iframe", "&lt;iframe");
        str = str.Replace("<script", "&lt;script");
      }
      return str;
    }
  }
}