Beispiel #1
0
        /// <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);
        }
Beispiel #2
0
        /// <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);
        }
Beispiel #3
0
        /// <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);
 }
Beispiel #5
0
 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));
        }
Beispiel #7
0
        /// <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);
        }
Beispiel #9
0
        /// <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));
        }
Beispiel #11
0
        /// <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);
                }
            }
        }
Beispiel #12
0
        /// <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();
                      }
            }
        }
Beispiel #17
0
        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);
 }
Beispiel #19
0
 /// <summary>
 /// 表名
 /// </summary>
 /// <param name="bean"></param>
 /// <returns></returns>
 string GetTableName(BaseEntity bean)
 {
     return(DB.GetName(string.IsNullOrWhiteSpace(TableName) ? bean.TableName : TableName));
 }
Beispiel #20
0
        /// <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);
        }
Beispiel #21
0
        /// <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);
        }