void GetCondition <T>(string col, T val, string andor = AndOr.And, string op = Op.Eq, bool isFunc = false)
{
if (IsKeyCondition)
{
return;
}
if (string.IsNullOrWhiteSpace(col))
{
return;
}
// 如果有左括号,忽略逻辑运算符
if (hasParenthesis)
{
hasParenthesis = false;
}
else if (OtherCondition.Length > 0)
{
OtherCondition.Append(andor);
}
if (isFunc)
{
OtherCondition.Append(DB.SetColumnFunc(col, val));
}
else
{
OtherCondition.Append(DB.GetCondition(col, op));
Params.Add(DB.GetParam(col, val));
}
}
/// <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>
/// 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>
/// 包含以……结束
/// </summary>
/// <param name="col">列名</param>
/// <param name="val">列值</param>
/// <returns></returns>
public Sql EndWith(string col, string val)
{
if (GetLikeCondition(col, val))
{
Params.Add(DB.GetParam(col, "%" + val));
}
return(this);
}
public Sql ID <T>(string col, T val)
{
if (IsKeyCondition)
{
IDCondition.Append(" and ");
}
IDCondition.Append(DB.GetCondition(col));
Params.Add(DB.GetParam(col, val));
IsKeyCondition = true;
return(this);
}
public Sql ID <T>(T val)
{
if (IsKeyCondition)
{
IDCondition.Append(" and ");
}
IDCondition.Append(DB.GetCondition("id"));
Params.Add(DB.GetParam("id", val));
IsKeyCondition = true;
return(this);
}
/// <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);
}
/// <summary>
/// 原生sql命令
/// 例如：例如：select * from users where age>=? and sex=?
/// </summary>
/// <param name="sqlString">例如：select * from users where age>=? and sex=?</param>
/// <param name="dbParams">返回的 List<DbParameter></param>
/// <param name="args">查询条件值,和sql字符串中的？号对应</param>
/// <returns>sql字符串</returns>
public static SqlCommand GetRawSql(string sqlString, params object[] args)
{
SqlCommand cmd = new SqlCommand();
var len = args.Length;
if (len == 0)
{
cmd.SqlString = sqlString;
return(cmd);
}
var i = 0;
if (SqlDict.TryGetValue(sqlString, out string sqlStr))
{
cmd.SqlString = sqlStr;
if (len > 0)
{
foreach (var arg in args)
{
cmd.SqlParams.Add(DB.GetParam("_cols_" + i, args[i]));
i++;
}
}
return(cmd);
}
var sql = new StringBuilder(200);
string col = string.Empty;
foreach (var c in sqlString)
{
if (c == '?' && i < len)
{
col = "_cols_" + i;
sql.Append(DB._ParameterPrefix + col);
cmd.SqlParams.Add(DB.GetParam(col, args[i]));
i++;
continue;
}
sql.Append(c);
}
cmd.SqlString = sql.ToString();;
SqlDict.AddOrUpdate(sqlString, cmd.SqlString, (x, y) => cmd.SqlString);
return(cmd);
}
/// <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);
}
}
}
static string GetCondition <V>(string col, V val, List <DbParameter> Params)
{
Params.Add(DB.GetParam(col, val));
return(DB.GetCondition(col, Op.Eq));
}
/// <summary>
/// 将列更新为指定值
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="col"></param>
/// <param name="val"></param>
/// <returns></returns>
public Sql Set <T>(string col, T val)
{
_SetColumns.Add(DB.GetCondition(col));
Params.Add(DB.GetParam(col, val));
return(this);
}
/// <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);
}