/// <summary> /// Insert 一条新数据 /// </summary> /// <param name="bean">表实体对象</param> /// <returns></returns> public DalResult Insert(T bean) { if (bean == null || bean.Count == 0) { throw new Exception("bean 不能为 NULL"); } List <string> _Fields = new List <string>(); List <string> _Values = new List <string>(); var _Params = new List <DbParameter>(); foreach (var item in bean) { if (_TColumns.Contains(item.Key)) { _Fields.Add(DB.GetName(item.Key)); _Values.Add(DB._ParameterPrefix + item.Key); _Params.Add(DB.GetParam(item.Key, item.Value)); } } var sql = "INSERT INTO " + TableString + " (" + string.Join(",", _Fields) + ") VALUES(" + string.Join(",", _Values) + ");"; var result = Db.Insert(_Session, sql, _Params); if (result.Success) { _OnInsert.Invoke(result.Id); } return(result); }
/// <summary> /// 原生UPDATE /// </summary> /// <param name="id">主键值</param> /// <param name="setString">SET字符串,"a=?,b=?"</param> /// <param name="args">?对应的参数值</param> /// <returns></returns> public DalResult Update(long id, string setString, params object[] args) { if (id == 0) { throw new Ex("id = 0 错误", Ex.BadParameter); } if (string.IsNullOrWhiteSpace(setString)) { throw new Ex("数据库操作命令不能为空", Ex.BadParameter); } if (!Exists(id)) { throw new Ex("目标数据不存在", Ex.NotFound); } var sql = new StringBuilder(200); sql.Append("UPDATE " + TableString); if (!setString.Trim().StartsWith("SET ", StringComparison.CurrentCultureIgnoreCase)) { sql.Append(" SET "); } var cmd = DB.GetRawSql(setString, args); sql.Append(cmd.SqlString); sql.Append($" WHERE {DB.GetName("ID")}={id};"); var result = Db.Write(_Session, sql.ToString(), cmd.SqlParams); if (result.Success) { _OnUpdate.Invoke(id); } return(result); }
/// <summary> /// 构造Select语句中的Fields /// </summary> /// <param name="args"></param> /// <returns></returns> static string GetSelectColumnStringByArgs(IEnumerable <string> args) { var _selectColumns = string.Empty; var _key = args.Join(); if (SelectColumns.TryGetValue(_key, out _selectColumns)) { return(_selectColumns); } var _strings = new List <string>(); foreach (var arg in args) { if (_TColumns.Contains(arg)) { _strings.Add(DB.GetName(arg)); // 聚合函数 continue; } _strings.Add(arg); } _selectColumns = _strings.Join(); SelectColumns.TryAdd(_key, _selectColumns); return(_selectColumns); }
/// <summary> /// 是否存在 /// </summary> /// <typeparam name="V"></typeparam> /// <param name="col">列名</param> /// <param name="val">列值</param> /// <returns></returns> public bool Exists <V>(string col, V val) { if (GetCache(col, val) != null) { return(true); } return(Db.ReadSingle(string.Concat("SELECT ", DB.GetName("id"), FromTableString, " where ", DB.GetCondition(col)), DB.GetParam(col, val)) != null); }
public Sql Desc(string col) { if (string.IsNullOrWhiteSpace(col)) { return(this); } _OrderBy.Add(DB.GetName(col) + " DESC"); return(this); }
/// <summary> /// 读取指定id的指定列数据 /// </summary> /// <param name="id"></param> /// <param name="col">列名</param> /// <returns></returns> public object GetSingle(long id, string col) { if (id < 0) { return(null); } var _sql = string.Concat("SELECT ", DB.GetName(col), FromTableString, " WHERE ", DB.SetColumnFunc("id", id), " limit 1;"); return(Db.ReadSingle(_sql)); }
/// <summary> /// 构造Select语句中的Fields /// </summary> /// <param name="cols"></param> /// <returns></returns> static string GetSelectColumnString(IEnumerable <string> cols) { var _strings = new List <string>(); foreach (var col in cols) { _strings.Add(DB.GetName(col)); // 列 } return(string.Join(",", _strings)); }
/// <summary> /// 是否存在 /// </summary> /// <param name="id"></param> /// <returns></returns> public bool Exists(long id) { if (GetCache(id) != null) { return(true); } var _sql = string.Concat("SELECT ", DB.GetName("id"), FromTableString, " WHERE ", DB.GetName("id"), "=", id, " limit 1;"); return(Db.ReadSingle(_sql) != null); }
/// <summary> /// 删除指定的若干行 /// </summary> /// <param name="ids"></param> /// <returns></returns> public DalResult Delete(IEnumerable <long> ids) { var sql = "DELETE FROM " + TableString + $" WHERE {DB.GetName("ID")} in ({string.Join(",", ids)});"; var result = Db.Write(_Session, sql); if (result.Success) { OnDelete(ids as long[]); } return(result); }
/// <summary> /// 读取ids(缺省读取前1000个) /// </summary> /// <param name="orderby"></param> /// <param name="count"></param> /// <returns></returns> public long[] GetIds(string orderby = null, uint count = 1000) { var _sql = new StringBuilder(string.Concat("SELECT ", DB.GetName("id"), FromTableString)); if (!string.IsNullOrWhiteSpace(orderby)) { _sql.Append(" ORDER BY " + orderby); } if (count > 0) { _sql.Append(" LIMIT " + count); } return(_GetIds(_sql, null)); }
/// <summary> /// Insert一条数据 /// </summary> /// <param name="bean"></param> /// <returns></returns> bool Insert(BaseEntity bean) { if (bean == null || bean.Count == 0) { throw new Exception("bean 不能为 NULL"); } List <string> _Values = new List <string>(); _Columns.Clear(); Params.Clear(); foreach (var item in bean) { _Columns.Add(DB.GetName(item.Key)); _Values.Add(DB._ParameterPrefix + item.Key); Params.Add(DB.GetParam(item.Key, item.Value.ToString())); } var sql = "INSERT INTO " + GetTableName(bean) + " (" + string.Join(",", _Columns) + ") VALUES (" + string.Join(",", _Values) + "); select ROW_COUNT(),LAST_INSERT_ID();"; var id = -1L; try { if (Db.Insert(sql, Params, ref id)) { if (id > 0) { bean["ID"] = id; } return(true); } return(false); } catch { throw; } finally { if (ShowSQL) { ShowSQLString(sql, Params); } } }
/// <summary> /// 删除指定行 /// </summary> /// <param name="id">ID值</param> /// <returns></returns> public DalResult Delete(long id) { if (id == 0) { throw new Ex("id = 0 错误", Ex.BadParameter); } if (!Exists(id)) { throw new Ex("目标数据不存在", Ex.NotFound); } var sql = "DELETE FROM " + TableString + $" WHERE {DB.GetName("ID")}={id};"; var result = Db.Write(_Session, sql); if (result.Success) { _OnDelete.Invoke(id); } return(result); }
/// <summary> /// 读取前size个ID /// </summary> /// <param name="bean"></param> /// <param name="orderby">排序</param> /// <param name="count"></param> /// <returns>数量</returns> public long[] GetIds(T bean, string orderby = null, uint count = 1000) { if (bean == null) { throw new Exception("bean 不能为 NULL"); } var _Params = new List <DbParameter>(); var _sql = new StringBuilder(string.Concat("SELECT ", DB.GetName("id"), FromTableString, GetWhereCondition(bean, _Params))); if (!string.IsNullOrWhiteSpace(orderby)) { _sql.Append(" ORDER BY " + orderby); } if (count > 0) { _sql.Append(" LIMIT " + count); } return(_GetIds(_sql, _Params)); }
/// <summary> /// 读取ids(缺省读取前1000个) /// </summary> /// <param name="cond"></param> /// <returns></returns> public long[] GetIds <V>(string col, V val, string orderby = null, uint count = 1000) { if (col == null || col.Length < 1 || val == null) { return(GetIds()); } var _Params = new List <DbParameter>(); var _sql = new StringBuilder(string.Concat("SELECT ", DB.GetName("id"), FromTableString, " WHERE ", GetCondition(col, val, _Params))); if (!string.IsNullOrWhiteSpace(orderby)) { _sql.Append(" ORDER BY " + orderby); } if (count > 0) { _sql.Append(" LIMIT " + count); } return(_GetIds(_sql, _Params)); }
/// <summary> /// 是否存在 /// </summary> /// <param name="cond"></param> /// <returns></returns> public bool Exists(Sql cond) { if (cond == null) { throw new Exception("cond 不能为 NULL"); } if (GetCache(cond) != null) { return(true); } try { var _sql = string.Concat("SELECT ", DB.GetName("id"), FromTableString, cond.GetWhereString()); return(Db.ReadSingle(_sql, cond.Params) != null); } catch { throw; } finally { if (cond != null && !cond.Disposed) { cond.Dispose(); } } }
/// <summary> /// 读取ids(缺省读取前1000个) /// </summary> /// <param name="cond"></param> /// <returns></returns> public long[] GetIds(Sql cond) { if (cond == null) { return(GetIds()); } try { var _sql = new StringBuilder(string.Concat("SELECT ", DB.GetName("id"), FromTableString, cond.GetWhereString())); _sql.Append(cond.GetGroupbyString()); _sql.Append(cond.GetOrderbyString()); _sql.Append(cond.GetPageString()); return(_GetIds(_sql, cond.Params)); } catch { throw; } finally { if (cond != null && !cond.Disposed) { cond.Dispose(); } } }
DalResult IncrDecr(long id, string col, int val, string op = "+") { if (!Exists(id)) { throw new Ex("目标数据不存在", Ex.NotFound); } string sql = null; if (_ColumnDictionary.ContainsKey(col)) { sql = "UPDATE " + TableString + " SET " + DB.GetName(col) + Op.Eq + DB.GetName(col) + $"{op}{val} WHERE {DB.GetName("ID")}={id};"; } else { throw new Ex(col + "列不存在", Ex.NotFound); } var result = Db.Write(_Session, sql); if (result.Success) { _OnUpdate.Invoke(id); } return(result); }
/// <summary> /// 列值递减 /// </summary> /// <param name="col">列名</param> /// <param name="val">递减值(缺省为1)</param> /// <returns></returns> public Sql Decr(string col, int val = 1) { _SetColumns.Add(string.Concat(DB.GetName(col), "=", DB.GetName(col), "-", val)); return(this); }
/// <summary> /// 表名 /// </summary> /// <param name="bean"></param> /// <returns></returns> string GetTableName(BaseEntity bean) { return(DB.GetName(string.IsNullOrWhiteSpace(TableName) ? bean.TableName : TableName)); }
/// <summary> /// 更新指定字段 /// </summary> /// <typeparam name="V"></typeparam> /// <param name="id">ID值</param> /// <param name="col">字段名</param> /// <param name="val">字段值</param> /// <returns></returns> public DalResult Update <V>(long id, string col, V val) { if (id == 0) { throw new Ex("id = 0 错误", Ex.BadParameter); } if (val == null || string.IsNullOrWhiteSpace(col)) { throw new Ex("参数不能为 NULL", Ex.BadParameter); } if (!Exists(id)) { throw new Ex("目标数据不存在", Ex.NotFound); } DbParameter _Param = null; string sql = null; if (_ColumnDictionary.ContainsKey(col)) { _Param = DB.GetParam(col, val); sql = "UPDATE " + TableString + " SET " + DB.GetCondition(col) + $" WHERE {DB.GetName("ID")}={id};"; } else { throw new Ex(col + "列不存在", Ex.NotFound); } var result = Db.Write(_Session, sql, _Param); if (result.Success) { _OnUpdate.Invoke(id); } return(result); }
/// <summary> /// Update 一条数据 /// </summary> /// <param name="bean">表实体对象(要更新的字段值),关键字段ID必须赋值,</param> /// <returns></returns> public DalResult Update(T bean) { if (bean == null) { throw new Ex("bean 不能为 NULL", Ex.Null); } if (bean.Count < 2) { throw new Ex("缺少更新字段", Ex.Null); } var _SetColumns = new List <string>(); var _Params = new List <DbParameter>(); var id = 0L; if (bean.ContainsKey("id")) { id = bean["id"].ToLong(); } if (id == 0) { throw new Ex("id = 0 错误", Ex.BadParameter); } //var _old = GetCache(id); //if (_old == null) { throw new Ex("目标数据不存在", Ex.NotFound); } foreach (var item in bean) { if (item.Key.Equals("ID", StringComparison.OrdinalIgnoreCase)) { continue; } if (_TColumns.Contains(item.Key)) { //// 数据没有变化 //if (_old[item.Key] == item.Value) continue; _SetColumns.Add(DB.GetCondition(item.Key)); _Params.Add(DB.GetParam(item.Key, item.Value)); } } if (_SetColumns.Count == 0) { throw new Ex("缺少更新字段", Ex.Null); } var sql = "UPDATE " + TableString + " SET " + string.Join(",", _SetColumns) + $" WHERE {DB.GetName("ID")}={id};"; var result = Db.Write(_Session, sql, _Params); if (result.Success) { _OnUpdate.Invoke(id); } return(result); }