123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281 |
- using System;
- using System.Collections.Generic;
- using System.Web;
- using System.Data;
- using System.Data.SqlClient;
- using System.Collections;
- using System.Configuration;
- using System.Collections.Specialized;
- using System.Text;
- using NVC = System.Collections.Specialized.NameValueCollection;
- using HT = System.Collections.Hashtable;
- using Library;
- namespace Library {
- public class SimpleDB {
- public static string MakeWhere(HT condition) {
- return MakeWhere(ToNVC(condition));
- }
- public static string MakeWhere(NVC condition) {
- StringBuilder sb = new StringBuilder();
- string oper = "";
- string column = "";
- foreach (string col in condition.Keys) {
- column = col;
- if (condition[column].Trim().Length == 0) continue;
- oper = "";
- if (column.Length >= 5) {
- oper = column.Trim().Substring(column.Trim().Length - 5);
- }
- if (sb.Length > 0) {
- if (column.Length >= 3 && column.Trim().Substring(0, 3).ToUpper() == "OR ") {
- sb.Append(" OR ");
- column = column.Substring(3);
- } else {
- sb.Append(" AND ");
- }
- }
- if (oper == " LIKE") {
- sb.Append(column.Substring(0, column.Length - 5));
- sb.Append(" LIKE ");
- sb.Append("'%" + EscapeString((string)condition[column]) + "%'");
- } else if (oper == " EXPR") {
- sb.Append(column.Substring(0, column.Length - 5));
- sb.Append(" = ");
- sb.Append((string)condition[column]);
- } else if (oper == "eated") {
- sb.Append(column.Substring(0, column.Length - 7));
- sb.Append((string)condition[column]);
- } else {
- if (column.Length >= 3) {
- oper = column.Trim().Substring(column.Trim().Length - 3);
- }
- if (oper == " IN") {
- sb.Append(column);
- sb.Append("(" + EscapeString((string)condition[column]) + ")");
- } else {
- sb.Append(column);
- oper = column.Trim().Substring(column.Trim().Length - 1);
- if (oper != "=" && oper != ">" && oper != "<") {
- sb.Append(" = ");
- }
- sb.Append("'" + EscapeString((string)condition[column]) + "'");
- }
- }
- }
- return sb.ToString();
- }
- public string MakeSet(NVC columns) {
- StringBuilder sb = new StringBuilder();
- foreach (string column in columns.Keys) {
- if (sb.Length > 0) sb.Append(" , ");
- sb.Append(column);
- sb.Append(" = ");
- sb.Append("'" + EscapeString((string)columns[column]) + "'");
- }
- return sb.ToString();
- }
- public static string MakeFind(HT options) {
- StringBuilder sql = new StringBuilder();
- string fields = "*";
- string table = "";
- string conditions = "";
- string order = "";
- string group = "";
- string having = "";
- string leftjoin = "";
- int limit = 0;
- int page = 0;
- if (options["conditions"] is string) {
- conditions = (string)options["conditions"];
- } else if (options["conditions"] is NVC) {
- conditions = MakeWhere((NVC)options["conditions"]);
- } else if (options["conditions"] is HT) {
- conditions = MakeWhere((HT)options["conditions"]);
- }
- if (options["fields"] is string) {
- fields = (string)options["fields"];
- } else if (options["fields"] is string[]) {
- fields = string.Join(",", (string[])options["fields"]);
- }
- if (options["table"] is string) {
- table = (string)options["table"];
- }
- if (options["limit"] is string) {
- limit = int.Parse((string)options["limit"]);
- } else if (options["limit"] is int) {
- limit = (int)options["limit"];
- }
- if (options["page"] is string) {
- page = int.Parse((string)options["page"]);
- } else if (options["page"] is int) {
- page = (int)options["page"];
- }
- if (options["order"] is string) {
- order = (string)options["order"];
- }
- if (options["group"] is string) {
- group = (string)options["group"];
- }
- if (options["having"] is string) {
- having = (string)options["having"];
- }
- if (options["leftjoin"] is string) {
- leftjoin = (string)options["leftjoin"];
- } else if (options["leftjoin"] is HT) {
- HT leftjoins = (HT)options["leftjoin"];
- StringBuilder sb = new StringBuilder();
- foreach (string key in leftjoins.Keys) {
- sb.Append(" LEFT JOIN ");
- sb.Append(key);
- sb.Append(" ON ");
- sb.Append(leftjoins[key]);
- }
- leftjoin = sb.ToString();
- } else if (options["leftjoin"] is NVC) {
- NVC leftjoins = (NVC)options["leftjoin"];
- StringBuilder sb = new StringBuilder();
- foreach (string key in leftjoins.AllKeys) {
- sb.Append(" LEFT JOIN ");
- sb.Append(key);
- sb.Append(" ON ");
- sb.Append(leftjoins[key]);
- }
- leftjoin = sb.ToString();
- }
- if (limit == 0 || (limit > 0 && page <= 1)) {
- sql.Append("SELECT ");
- if (limit > 0) {
- sql.Append(" TOP ");
- sql.Append(limit);
- sql.Append(" ");
- }
- sql.Append(fields);
- sql.Append(" FROM ");
- sql.Append(table);
- sql.Append(leftjoin);
- if (conditions.Length > 0) {
- sql.Append(" WHERE ");
- sql.Append(conditions);
- }
- if (group.Length > 0) {
- sql.Append(" GROUP BY ");
- sql.Append(group);
- }
- if (having.Length > 0) {
- sql.Append(" HAVING ");
- sql.Append(having);
- }
- if (order.Length > 0) {
- sql.Append(" ORDER BY ");
- sql.Append(order);
- }
- } else {
- sql.Append("SELECT * FROM (");
- sql.Append("SELECT ");
- sql.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0}) as _ROWNUM_, ", order);
- sql.Append(fields);
- sql.Append(" FROM ");
- sql.Append(table);
- sql.Append(leftjoin);
- if (conditions.Length > 0) {
- sql.Append(" WHERE ");
- sql.Append(conditions);
- }
- if (group.Length > 0) {
- sql.Append(" GROUP BY ");
- sql.Append(group);
- }
- if (having.Length > 0) {
- sql.Append(" HAVING ");
- sql.Append(having);
- }
- sql.AppendFormat(") AS _ROWNUM_TABLE_ WHERE _ROWNUM_ BETWEEN {0} AND {1}", (page - 1) * limit + 1, (page - 1) * limit + limit);
- }
- return sql.ToString();
- }
- public string Delete(string table, string id) { return Delete(table, id, "Id"); }
- public string Delete(string table, string id, string primaryKey) {
- string sql = string.Format("DELETE FROM [{0}] WHERE {1} = {2}", table, id, primaryKey);
- return sql;
- }
- public string Delete(string table, NVC conditions) {
- string where = MakeWhere(conditions);
- string sql = string.Format("DELETE FROM [{0}] WHERE {1}", table, where);
- return sql;
- }
- public string Insert(string table, NVC fields) { return Insert(table, ToHT(fields)); }
- public string Insert(string table, HT fields) {
- string insertSql = InsertQuery(table, fields);
- return insertSql;
- }
- public string InsertQuery(string table, NVC fields) { return InsertQuery(table, ToHT(fields)); }
- public string InsertQuery(string table, HT fields) {
- StringBuilder sbVal = new StringBuilder();
- StringBuilder sbCol = new StringBuilder();
- foreach (string key in fields.Keys) {
- if (fields[key] == null) continue;
- if (sbCol.Length > 0) sbCol.Append(",");
- sbCol.Append(key);
- if (sbVal.Length > 0) sbVal.Append(",");
- string val = EscapeString((string)fields[key]);
- if (val.ToLower() == "getdate()") {
- sbVal.Append(val);
- } else {
- sbVal.Append("'");
- sbVal.Append(val);
- sbVal.Append("'");
- }
- }
- string columns = sbCol.ToString();
- string values = sbVal.ToString();
- string insertSql = $"INSERT INTO [{table}]({columns}) VALUES({values})";
- return insertSql;
- }
- public string Update(string table, NVC fields, string where) { return Update(table, ToHT(fields), where); }
- public string Update(string table, HT fields, string where) {
- string set = MakeSet(ToNVC(fields));
- string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
- return sql;
- }
- public string Update(string table, NVC fields, NVC conditions) {
- string where = MakeWhere(conditions);
- string set = MakeSet(fields);
- string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
- return sql;
- }
- public static string EscapeString(string str) {
- return str.Replace("'", "''");
- }
- public static NVC ToNVC(HT ht) {
- NVC nvc = new NVC();
- foreach (object col in ht.Keys) {
- if (col == null) continue;
- nvc.Add(col.ToString(), (ht[col] ?? "").ToString());
- }
- return nvc;
- }
- public static HT ToHT(NVC nvc) {
- HT ht = new HT();
- foreach (string key in nvc.Keys) {
- if (key == null) continue;
- ht.Add(key, nvc[key]);
- }
- return ht;
- }
- }
- }
|