/// <summary>
/// 生成UPDATE的语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connType"></param>
/// <param name="pkCount"></param>
/// <returns></returns>
public static string GetUpdateSql <T>(this DbContext connInfo, SqlFunction sqlFunc = null, IEnumerable <string> onlyFields = null, string tableName = "")
where T : new()
{
Type t = typeof(T);
string key = $"{t.FullName}:update:{connInfo.DbType.ToString()}:{tableName}:{GetFieldsKey(onlyFields)}:{sqlFunc?.ToString()}";
string result = "";
if (updateStr.TryGetValue(key, out result))
{
return(result);
}
TableInfo ti = GetTableInfo(t, tableName);
if (0 == (ti?.PKeys?.Count ?? 0))
{
throw new Exception("Primary keys are missing!");
}
bool specFields = (0 < (onlyFields?.Count() ?? 0)); //指定栏位
Dictionary <string, PropertyInfo> fieldDefine = EntityReader.GetTypePropertyMapping(t);
StringBuilder strbld = new StringBuilder(256);
strbld.Concat("UPDATE ", connInfo.DbDelimiter(ti.TableName), " SET ");
//非主键
if (!specFields)
{
strbld.Append(string.Join(",", fieldDefine.Where(c =>
!ti.PKeys.Contains(c.Key, StringComparer.OrdinalIgnoreCase)).Select(c =>
string.Format("{0}={1}", connInfo.DbDelimiter(c.Key),
ti.IsCurrentTimeField(c.Key) ? connInfo.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c.Key) ?? false) ? sqlFunc?.GetSqlFunction(c.Key) :
connInfo.TreatParaName(c.Key)
)).ToArray()));
}
else
{
strbld.Append(string.Join(",", onlyFields.Where(c =>
!ti.PKeys.Contains(c, StringComparer.OrdinalIgnoreCase)).Select(c =>
string.Format("{0}={1}", connInfo.DbDelimiter(c),
ti.IsCurrentTimeField(c) ? connInfo.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c) ?? false) ? sqlFunc?.GetSqlFunction(c) :
connInfo.TreatParaName(c)
)).ToArray()));
}
strbld.Append(" WHERE ");
//主键
strbld.Append(string.Join(" AND ", fieldDefine.Where(c =>
ti.PKeys.Contains(c.Key, StringComparer.OrdinalIgnoreCase)).Select(c =>
string.Format("{0}={1}", connInfo.DbDelimiter(c.Key),
connInfo.TreatParaName(c.Key))).ToArray()));
result = strbld.ToString();
updateStr.TryAdd(key, result);
return(result);
}
/// <summary>
/// 锁定一条记录,UpdateField在ORACLE下无效，用于执行 Update T set UpdateField=UpdateField where ...
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connInfo"></param>
/// <param name="obj"></param>
public static bool LockRecord <T>(this DbContext connInfo, T record, string updateField, string tableName = "")
{
Type t = typeof(T);
TableInfo tableInfo = GetTableInfo(t, 0, tableName);
if (null == tableInfo)
{
throw new ArgumentNullException("GetTableInfo");
}
StringBuilder whereState = new StringBuilder(256);
whereState.Append(" WHERE"); // where子句
int pkcount = tableInfo.PKeys.Count;
RequestBase pars = new RequestBase();
Dictionary <string, PropertyInfo> fields = EntityReader.GetTypePropertyMapping(t);
int i = 0;
foreach (KeyValuePair <string, PropertyInfo> item in
fields.Where(c => tableInfo.PKeys.Contains(c.Key, StringComparer.OrdinalIgnoreCase)))
{
if (i > 0)
{
whereState.Append(" AND ");
}
whereState.AppendFormat(" {0}={1} ", connInfo.DbDelimiter(item.Key), connInfo.TreatParaName(item.Key));
pars.Add(item.Key, GetPropertyValue <T>(record, item, connInfo.DbType));
++i;
}
StringBuilder sql = new StringBuilder(512);
if (connInfo.SupportForUpdate)
{
sql.Append("SELECT 'Y' FROM ");
sql.Append(connInfo.DbDelimiter(tableName));
sql.Append(whereState.ToString());
sql.Append(" FOR UPDATE ");
return(connInfo.Db.ExecuteScalar <string>(sql.ToString(), pars, connInfo.Transaction, commandType: CommandType.Text).IsTrue());
}
else
{
if (string.IsNullOrEmpty(updateField))
{
throw new Exception("update field error!");
}
sql.Append("UPDATE ");
sql.Append(tableInfo.TableName.DbDelimiter(connInfo));
sql.AppendFormat(" SET {0}={1}", updateField.DbDelimiter(connInfo), updateField);
sql.Append(whereState.ToString());
return(1 == connInfo.Db.Execute(sql.ToString(), pars, connInfo.Transaction, commandType: CommandType.Text));
}
}
/// <summary>
/// 获取实体类的属性
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public static List <string> GetFields(Type t, bool isExceptField = false, params string[] onlyFields)
{
bool specFields = 0 < (onlyFields?.Length ?? 0);
if (isExceptField && !specFields)
{
throw new ArgumentException("isExceptField/onlyFields");
}
Dictionary <string, PropertyInfo> pi = EntityReader.GetTypePropertyMapping(t);
if (null == pi || 0 == pi.Count)
{
return(null);
}
List <string> result = new List <string>();
foreach (var ele in pi)
{
if (!specFields)
{
result.Add(ele.Key);
}
else
{
if (isExceptField) //排除字段
{
if (onlyFields.Contains(ele.Key, StringComparer.OrdinalIgnoreCase))
{
continue;
}
}
else
{
if (!onlyFields.Contains(ele.Key, StringComparer.OrdinalIgnoreCase))
{
continue;
}
}
result.Add(ele.Key);
}
}
return(result);
}
