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