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