SimpleDB.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Web;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. using System.Collections;
  7. using System.Configuration;
  8. using System.Collections.Specialized;
  9. using System.Text;
  10. using NVC = System.Collections.Specialized.NameValueCollection;
  11. using HT = System.Collections.Hashtable;
  12. using Library;
  13. namespace Library {
  14. public class SimpleDB {
  15. public static string MakeWhere(HT condition) {
  16. return MakeWhere(ToNVC(condition));
  17. }
  18. public static string MakeWhere(NVC condition) {
  19. StringBuilder sb = new StringBuilder();
  20. string oper = "";
  21. string column = "";
  22. foreach (string col in condition.Keys) {
  23. column = col;
  24. if (condition[column].Trim().Length == 0) continue;
  25. oper = "";
  26. if (column.Length >= 5) {
  27. oper = column.Trim().Substring(column.Trim().Length - 5);
  28. }
  29. if (sb.Length > 0) {
  30. if (column.Length >= 3 && column.Trim().Substring(0, 3).ToUpper() == "OR ") {
  31. sb.Append(" OR ");
  32. column = column.Substring(3);
  33. } else {
  34. sb.Append(" AND ");
  35. }
  36. }
  37. if (oper == " LIKE") {
  38. sb.Append(column.Substring(0, column.Length - 5));
  39. sb.Append(" LIKE ");
  40. sb.Append("'%" + EscapeString((string)condition[column]) + "%'");
  41. } else if (oper == " EXPR") {
  42. sb.Append(column.Substring(0, column.Length - 5));
  43. sb.Append(" = ");
  44. sb.Append((string)condition[column]);
  45. } else if (oper == "eated") {
  46. sb.Append(column.Substring(0, column.Length - 7));
  47. sb.Append((string)condition[column]);
  48. } else {
  49. if (column.Length >= 3) {
  50. oper = column.Trim().Substring(column.Trim().Length - 3);
  51. }
  52. if (oper == " IN") {
  53. sb.Append(column);
  54. sb.Append("(" + EscapeString((string)condition[column]) + ")");
  55. } else {
  56. sb.Append(column);
  57. oper = column.Trim().Substring(column.Trim().Length - 1);
  58. if (oper != "=" && oper != ">" && oper != "<") {
  59. sb.Append(" = ");
  60. }
  61. sb.Append("'" + EscapeString((string)condition[column]) + "'");
  62. }
  63. }
  64. }
  65. return sb.ToString();
  66. }
  67. public string MakeSet(NVC columns) {
  68. StringBuilder sb = new StringBuilder();
  69. foreach (string column in columns.Keys) {
  70. if (sb.Length > 0) sb.Append(" , ");
  71. sb.Append(column);
  72. sb.Append(" = ");
  73. sb.Append("'" + EscapeString((string)columns[column]) + "'");
  74. }
  75. return sb.ToString();
  76. }
  77. public static string MakeFind(HT options) {
  78. StringBuilder sql = new StringBuilder();
  79. string fields = "*";
  80. string table = "";
  81. string conditions = "";
  82. string order = "";
  83. string group = "";
  84. string having = "";
  85. string leftjoin = "";
  86. int limit = 0;
  87. int page = 0;
  88. if (options["conditions"] is string) {
  89. conditions = (string)options["conditions"];
  90. } else if (options["conditions"] is NVC) {
  91. conditions = MakeWhere((NVC)options["conditions"]);
  92. } else if (options["conditions"] is HT) {
  93. conditions = MakeWhere((HT)options["conditions"]);
  94. }
  95. if (options["fields"] is string) {
  96. fields = (string)options["fields"];
  97. } else if (options["fields"] is string[]) {
  98. fields = string.Join(",", (string[])options["fields"]);
  99. }
  100. if (options["table"] is string) {
  101. table = (string)options["table"];
  102. }
  103. if (options["limit"] is string) {
  104. limit = int.Parse((string)options["limit"]);
  105. } else if (options["limit"] is int) {
  106. limit = (int)options["limit"];
  107. }
  108. if (options["page"] is string) {
  109. page = int.Parse((string)options["page"]);
  110. } else if (options["page"] is int) {
  111. page = (int)options["page"];
  112. }
  113. if (options["order"] is string) {
  114. order = (string)options["order"];
  115. }
  116. if (options["group"] is string) {
  117. group = (string)options["group"];
  118. }
  119. if (options["having"] is string) {
  120. having = (string)options["having"];
  121. }
  122. if (options["leftjoin"] is string) {
  123. leftjoin = (string)options["leftjoin"];
  124. } else if (options["leftjoin"] is HT) {
  125. HT leftjoins = (HT)options["leftjoin"];
  126. StringBuilder sb = new StringBuilder();
  127. foreach (string key in leftjoins.Keys) {
  128. sb.Append(" LEFT JOIN ");
  129. sb.Append(key);
  130. sb.Append(" ON ");
  131. sb.Append(leftjoins[key]);
  132. }
  133. leftjoin = sb.ToString();
  134. } else if (options["leftjoin"] is NVC) {
  135. NVC leftjoins = (NVC)options["leftjoin"];
  136. StringBuilder sb = new StringBuilder();
  137. foreach (string key in leftjoins.AllKeys) {
  138. sb.Append(" LEFT JOIN ");
  139. sb.Append(key);
  140. sb.Append(" ON ");
  141. sb.Append(leftjoins[key]);
  142. }
  143. leftjoin = sb.ToString();
  144. }
  145. if (limit == 0 || (limit > 0 && page <= 1)) {
  146. sql.Append("SELECT ");
  147. if (limit > 0) {
  148. sql.Append(" TOP ");
  149. sql.Append(limit);
  150. sql.Append(" ");
  151. }
  152. sql.Append(fields);
  153. sql.Append(" FROM ");
  154. sql.Append(table);
  155. sql.Append(leftjoin);
  156. if (conditions.Length > 0) {
  157. sql.Append(" WHERE ");
  158. sql.Append(conditions);
  159. }
  160. if (group.Length > 0) {
  161. sql.Append(" GROUP BY ");
  162. sql.Append(group);
  163. }
  164. if (having.Length > 0) {
  165. sql.Append(" HAVING ");
  166. sql.Append(having);
  167. }
  168. if (order.Length > 0) {
  169. sql.Append(" ORDER BY ");
  170. sql.Append(order);
  171. }
  172. } else {
  173. sql.Append("SELECT * FROM (");
  174. sql.Append("SELECT ");
  175. sql.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0}) as _ROWNUM_, ", order);
  176. sql.Append(fields);
  177. sql.Append(" FROM ");
  178. sql.Append(table);
  179. sql.Append(leftjoin);
  180. if (conditions.Length > 0) {
  181. sql.Append(" WHERE ");
  182. sql.Append(conditions);
  183. }
  184. if (group.Length > 0) {
  185. sql.Append(" GROUP BY ");
  186. sql.Append(group);
  187. }
  188. if (having.Length > 0) {
  189. sql.Append(" HAVING ");
  190. sql.Append(having);
  191. }
  192. sql.AppendFormat(") AS _ROWNUM_TABLE_ WHERE _ROWNUM_ BETWEEN {0} AND {1}", (page - 1) * limit + 1, (page - 1) * limit + limit);
  193. }
  194. return sql.ToString();
  195. }
  196. public string Delete(string table, string id) { return Delete(table, id, "Id"); }
  197. public string Delete(string table, string id, string primaryKey) {
  198. string sql = string.Format("DELETE FROM [{0}] WHERE {1} = {2}", table, id, primaryKey);
  199. return sql;
  200. }
  201. public string Delete(string table, NVC conditions) {
  202. string where = MakeWhere(conditions);
  203. string sql = string.Format("DELETE FROM [{0}] WHERE {1}", table, where);
  204. return sql;
  205. }
  206. public string Insert(string table, NVC fields) { return Insert(table, ToHT(fields)); }
  207. public string Insert(string table, HT fields) {
  208. string insertSql = InsertQuery(table, fields);
  209. return insertSql;
  210. }
  211. public string InsertQuery(string table, NVC fields) { return InsertQuery(table, ToHT(fields)); }
  212. public string InsertQuery(string table, HT fields) {
  213. StringBuilder sbVal = new StringBuilder();
  214. StringBuilder sbCol = new StringBuilder();
  215. foreach (string key in fields.Keys) {
  216. if (fields[key] == null) continue;
  217. if (sbCol.Length > 0) sbCol.Append(",");
  218. sbCol.Append(key);
  219. if (sbVal.Length > 0) sbVal.Append(",");
  220. string val = EscapeString((string)fields[key]);
  221. if (val.ToLower() == "getdate()") {
  222. sbVal.Append(val);
  223. } else {
  224. sbVal.Append("'");
  225. sbVal.Append(val);
  226. sbVal.Append("'");
  227. }
  228. }
  229. string columns = sbCol.ToString();
  230. string values = sbVal.ToString();
  231. string insertSql = $"INSERT INTO [{table}]({columns}) VALUES({values})";
  232. return insertSql;
  233. }
  234. public string Update(string table, NVC fields, string where) { return Update(table, ToHT(fields), where); }
  235. public string Update(string table, HT fields, string where) {
  236. string set = MakeSet(ToNVC(fields));
  237. string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
  238. return sql;
  239. }
  240. public string Update(string table, NVC fields, NVC conditions) {
  241. string where = MakeWhere(conditions);
  242. string set = MakeSet(fields);
  243. string sql = $"UPDATE [{table}] SET {set} WHERE {where}";
  244. return sql;
  245. }
  246. public static string EscapeString(string str) {
  247. return str.Replace("'", "''");
  248. }
  249. public static NVC ToNVC(HT ht) {
  250. NVC nvc = new NVC();
  251. foreach (object col in ht.Keys) {
  252. if (col == null) continue;
  253. nvc.Add(col.ToString(), (ht[col] ?? "").ToString());
  254. }
  255. return nvc;
  256. }
  257. public static HT ToHT(NVC nvc) {
  258. HT ht = new HT();
  259. foreach (string key in nvc.Keys) {
  260. if (key == null) continue;
  261. ht.Add(key, nvc[key]);
  262. }
  263. return ht;
  264. }
  265. }
  266. }