/// <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>
/// 生成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>
/// 取INSERT语句的定义串
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public static string GetInsertSql <T>(this DbContext connInfo, SqlFunction sqlFunc = null, IEnumerable <string> onlyFields = null, string tableName = "")
where T : new()
{
Type t = typeof(T);
string key = $"{t.FullName}:insert:{connInfo.DbType.ToString()}:{tableName}:{GetFieldsKey(onlyFields)}:{sqlFunc?.ToString()}";
string result = "";
if (insertStr.TryGetValue(key, out result))
{
return(result);
}
TableInfo tableInfo = EntityReader.GetEntityMapping(t, true);
bool specFields = (0 < (onlyFields?.Count() ?? 0)); //指定栏位
Dictionary <string, PropertyInfo> fieldDefine = tableInfo.PropertyMappings;
StringBuilder strbld = new StringBuilder(256);
strbld.Concat("INSERT INTO ", GetTableName <T>(connInfo.DbType, tableName, true), " (");
if (!specFields)
{
strbld.Append(string.Join(",", fieldDefine.Select(c => connInfo.DbDelimiter(c.Key)).ToArray()));
}
else
{
strbld.Append(string.Join(",", onlyFields));
}
strbld.Append(") VALUES (");
if (!specFields)
{
strbld.Append(string.Join(",", fieldDefine.Select(c =>
tableInfo.IsCurrentTimeField(c.Key) ? connInfo.DbType.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c.Key) ?? false) ? sqlFunc?.GetSqlFunction(c.Key) :
connInfo.DbType.TreatParaName(c.Key)
).ToArray()));
}
else
{
strbld.Append(string.Join(",", onlyFields.Select(c =>
tableInfo.IsCurrentTimeField(c) ? connInfo.DbType.GetCurrentTimeFuncName() :
(sqlFunc?.ExistsField(c) ?? false) ? sqlFunc?.GetSqlFunction(c) :
connInfo.DbType.TreatParaName(c)
).ToArray()));
}
strbld.Append(" )");
result = strbld.ToString();
insertStr.TryAdd(key, result);
return(result);
}
public static string GetSelectSql <T>(this DbContext db, bool isAll, IEnumerable <string> onlyFields = null, string tableName = "") where T : new()
{
string result = "";
Type t = typeof(T);
string key = $"{t.FullName}:select:{db.DbType.ToString()}:{isAll}:{tableName}:{GetFieldsKey(onlyFields)}";
if (dicSelectStr.TryGetValue(key, out result))
{
return(result);
}
StringBuilder sqlbld = new StringBuilder(512);
string fieldStr = "";
if (0 == (onlyFields?.Count() ?? 0))
{
fieldStr = SqlBuilder.GetFieldStr <T>(db.DbType);
}
else
{
fieldStr = string.Join(",", onlyFields);
}
TableInfo ti = GetTableInfo(t, tableName: tableName);
sqlbld.Concat("SELECT ", fieldStr, " FROM ", db.DbDelimiter(ti.TableName));
if (!isAll)
{
sqlbld.Concat(" WHERE ");
int i = 0;
foreach (string pk in ti.PKeys)
{
if (i > 0)
{
sqlbld.Concat(" and ");
}
sqlbld.Concat(GetParaPair(db, pk));
++i;
}
}
result = sqlbld.ToString();
dicSelectStr.TryAdd(key, result);
return(result);
}
public static bool ExistsInTable(this DbContext connInfo, string tableName, RequestBase param)
{
StringBuilder sqlbld = new StringBuilder(128);
sqlbld.Concat(" select 'Y' from ", connInfo.DbDelimiter(tableName, true));
int i = 0;
foreach (var ele in param)
{
if (i == 0)
{
sqlbld.Append(" where ");
}
else
{
sqlbld.Append(" and ");
}
sqlbld.Append(connInfo.GetParaPair(ele.Key));
++i;
}
return(connInfo.Db.ExecuteScalar <string>(sqlbld.ToString(), param, connInfo.Transaction, commandType: CommandType.Text).IsTrue());
}
public static string GetExistsSql(this DbContext connInfo, Type t, string tableName = "")
{
string key = $"{t.FullName}:exists:{connInfo.DbType.ToString()}:{tableName}";
string result = "";
if (existsStr.TryGetValue(key, out result))
{
return(result);
}
StringBuilder sqlbld = new StringBuilder(128);
sqlbld.Append("SELECT 'Y' FROM ");
TableInfo tableInfo = GetTableInfo(t, 0, tableName);
if (0 == (tableInfo?.PKeys?.Count() ?? 0))
{
throw new ArgumentNullException("PKeys");
}
sqlbld.Concat(connInfo.DbDelimiter(tableInfo.TableName, true));
int j = 0;
foreach (string pkName in tableInfo.PKeys)
{
if (0 != j)
{
sqlbld.Append(" AND ");
}
else
{
sqlbld.Append(" where ");
}
sqlbld.Append(connInfo.GetParaPair(pkName));
++j;
}
result = sqlbld.ToString();
existsStr.TryAdd(key, result);
return(result);
}
/// <summary>
/// 返回 Id=@Id
/// </summary>
/// <param name="connInfo"></param>
/// <param name="paraName"></param>
/// <returns></returns>
public static string GetParaPair(this DbContext connInfo, string paraName)
{
return(string.Format(" {0}={1} ", connInfo.DbDelimiter(paraName), connInfo.TreatParaName(paraName)));
}
public static string GetDeleteSql(this DbContext db, Type t, string tableName = "", IEnumerable <string> matchFields = null)
{
bool noType = false;
if (null == t)
{
if (string.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException("t/tableName");
}
if (0 == (matchFields?.Count() ?? 0))
{
throw new ArgumentNullException("matchFields");
}
noType = true;
}
TableInfo ti = null != t?GetTableInfo(t, tableName) : null;
string key = $"{t?.FullName?? ti?.TableName??tableName }:delete:{db.DbType.ToString()}:{tableName}:{GetFieldsKey(matchFields)}";
string result = "";
if (dicDeleteStr.TryGetValue(key, out result))
{
return(result);
}
StringBuilder sqlbld = new StringBuilder(128);
if (!noType)
{
if (0 == (ti?.PKeys?.Count() ?? 0))
{
throw new ArgumentNullException("PKeys");
}
sqlbld.Concat("DELETE FROM ", ti.TableName.DbDelimiter(db, true), " WHERE ");
int j = 0;
if (0 < (matchFields?.Count() ?? 0))
{
foreach (string field in matchFields)
{
if (j > 0)
{
sqlbld.Append(" AND ");
}
sqlbld.Concat(db.GetParaPair(field));
++j;
}
}
else
{
foreach (string pkField in ti.PKeys)
{
if (j > 0)
{
sqlbld.Append(" AND ");
}
sqlbld.Append(db.GetParaPair(pkField));
++j;
}
}
}
else
{
sqlbld.Concat("delete from ", db.DbDelimiter(ti?.TableName ?? tableName, true), " where ");
int j = 0;
foreach (string field in matchFields)
{
if (j > 0)
{
sqlbld.Append(" AND ");
}
sqlbld.Append(db.GetParaPair(field));
++j;
}
}
result = sqlbld.ToString();
dicDeleteStr.TryAdd(key, result);
return(result);
}
public static string DbDelimiter(this string TableOrFieldName, DbContext conn, bool checkSymExists = false) => conn.DbDelimiter(TableOrFieldName, checkSymExists);