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