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 int Delete(this DbContext connInfo, string tableName, RequestBase param) { StringBuilder sqlbld = new StringBuilder(128); sqlbld.Append($"Delete from {tableName.DbDelimiter(connInfo)} "); int i = 0; foreach (var ele in param) { if (i > 0) { sqlbld.Append(" and "); } else { sqlbld.Append(" where "); } sqlbld.Append(connInfo.GetParaPair(ele.Key)); ++i; } return(connInfo.Db.Execute(sqlbld.ToString(), param, connInfo.Transaction, commandType: CommandType.Text)); }
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); }
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); }