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; } } }