Edit D:\xinhaisoft\crisis_new\App

Edit D:\xinhaisoft\crisis_new\App_Code\SqlHelper.cs

??sing 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????????ataTable???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?????????????????????????ull
  /// </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????????son???????????op1?????????????????????????????op1??????????????????????????????????????]
  /// </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?????????????????????????????????????tring?????????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????????son?????????????????
  /// </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?????son?????????
  /// </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?????son?????????
  /// </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>
  /// ???????QL?????????????????????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?????frame??cript
  /// </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;
    }
  }
}

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de