internal static SqlExpr Join(SqlExpr[] expl)
{
SqlExpr exp = new SqlExpr()
.Next(string.Join <SqlExpr>(",", expl));
return(exp);
}
internal static SqlExpr ParameterName(string name)
{
SqlExpr exp = new SqlExpr().Next(name.SqlParameterName());
exp.AddParam(name, null);
return(exp);
}
public SqlExpr this[SqlExpr exp]
{
get
{
this.Next("[").Next(exp).Next("]");
return(this);
}
}
internal static SqlExpr OPR(string opr, SqlExpr exp1)
{
SqlExpr exp = new SqlExpr()
.Next(string.Format("{0} {1}", opr, ExpToString(exp1)));
exp.Merge(exp1);
return(exp);
}
public SqlExpr BETWEEN(SqlExpr exp1, SqlExpr exp2)
{
this.Next(" BETWEEN ")
.Next(exp1)
.Next(" AND ")
.Next(exp2);
return(this);
}
internal static SqlExpr OPR(SqlExpr exp1, string opr, SqlExpr exp2)
{
SqlExpr exp = new SqlExpr()
.Next(string.Format("{0} {1} {2}", ExpToString(exp1), opr, ExpToString(exp2)));
exp.Merge(exp1).Merge(exp2);
exp.compound = true;
return(exp);
}
public static SqlExpr operator !=(SqlExpr exp1, SqlExpr exp2)
{
if ((object)exp2 == null || exp2.ToString() == "NULL")
{
SqlExpr exp = new SqlExpr().Next(exp1).Next(" IS NOT NULL");
exp.Merge(exp1);
return(exp);
}
return(OPR(exp1, "<>", exp2));
}
private static string ExpToString(SqlExpr exp)
{
if (exp.compound)
{
return(string.Format("({0})", exp));
}
else
{
return(exp.ToString());
}
}
internal static SqlExpr Func(string func, params SqlExpr[] expl)
{
SqlExpr exp = new SqlExpr()
.Next(func)
.Next("(")
.Next(string.Join <SqlExpr>(",", expl))
.Next(")");
//exp.Merge(exp1);
return(exp);
}
/// <summary>
/// Instantiate an instant from select a record from database
/// </summary>
/// <param name="where"></param>
public void UpdateObject(SqlExpr where)
{
DataRow row = new SqlCmd(this.TableName.Provider, new SqlBuilder().SELECT().COLUMNS().FROM(TableName).WHERE(where).Clause).FillDataRow();
this.exists = row != null;
if (exists)
{
this.UpdateObject(row);
}
}
/// <summary>
/// delete record based on WHERE condition
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual bool Delete(SqlExpr where)
{
Locator temp = this.locator;
this.locator = new Locator(where);
bool result = Delete();
//restore Locator
this.locator = temp;
return(result);
}
/// <summary>
/// Update record(s) based on WHERE condition
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public virtual DataRow Update(SqlExpr where)
{
Locator temp = this.locator;
this.locator = new Locator(where);
DataRow row = save(new Selector(), SaveMode.Update);
//restore Locator
this.locator = temp;
return(row);
}
internal static SqlExpr AddParameter(string columnName, string parameterName)
{
SqlExpr exp = new SqlExpr()
.Next("[" + columnName + "]")
.Next("=")
.Next(parameterName.SqlParameterName());
exp.AddParam(parameterName, columnName);
return(exp);
}
public static SqlExpr OR(this IEnumerable <SqlExpr> expl)
{
if (expl.Count() > 1)
{
return(SqlExpr.OPR(expl.First(), "OR", expl.Skip(1).ToArray()));
}
else
{
return(expl.First());
}
}
internal static SqlExpr AllColumnNames(string alias)
{
SqlExpr exp = new SqlExpr();
if (alias != null)
{
exp.Next(alias)
.Next(".");
}
exp.Next("*");
return(exp);
}
internal static SqlExpr ColumnName(string name, string alias)
{
SqlExpr exp = new SqlExpr();
if (alias != null)
{
exp.Next(alias)
.Next(".");
}
exp.Next("[" + name + "]");
return(exp);
}
// AND(A==1, B!=3, C>4) => "(A=1 AND B<>3 AND C>4)"
internal static SqlExpr OPR(SqlExpr exp1, string opr, SqlExpr[] exps)
{
SqlExpr exp = new SqlExpr();
exp.Next("(")
.Next(string.Format("{0}", ExpToString(exp1)));
foreach (SqlExpr exp2 in exps)
{
exp.Next(string.Format(" {0} {1}", opr, ExpToString(exp2)));
}
exp.compound = true;
return(exp.Next(")"));
}
/// <summary>
/// read records by filter
/// </summary>
/// <param name="where"></param>
public TableReader(SqlExpr where)
{
this.reader = new TableReader(TableName, new SqlBuilder().SELECT().COLUMNS().FROM(TableName).WHERE(where).Clause);
}
/// <summary>
/// Delete records
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="where"></param>
public static void Delete <T>(this SqlExpr where) where T : class, IDPObject, new()
{
TableName tableName = typeof(T).TableName();
tableName.Provider.ExecuteScalar($"DELETE FROM {tableName} WHERE {where}");
}
public SqlBuilder HAVING(SqlExpr expr)
{
return(Append($"HAVING {expr} "));
}
public static SqlExpr AllColumnNames(string alias = null)
{
return(SqlExpr.AllColumnNames(alias));
}
public SqlBuilder WHERE(SqlExpr exp)
{
Append($"WHERE {exp} ");
this.Merge(exp);
return(AppendLine());
}
public SqlBuilder ON(SqlExpr exp)
{
Append($"ON {exp} ");
this.Merge(exp);
return(this);
}
/// <summary>
/// write directly into SQL clause
/// </summary>
/// <param name="any"></param>
/// <returns></returns>
public static SqlExpr Inject(this string any)
{
return(SqlExpr.Write(any));
}
public SqlBuilder SET(string key, SqlExpr value)
{
return(AppendLine($"SET {key} {value}"));
}
/// <summary>
/// "name" -> "@name"
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static SqlExpr ParameterName(this string name)
{
return(SqlExpr.ParameterName(name));
}
public static SqlExpr AddParameter(this string columnName, SqlExpr value)
{
return(SqlExpr.AddParameter(columnName, columnName));
}
public Locator(SqlExpr expression)
{
this.where = expression.ToString();
}
public SqlExpr AS(SqlExpr alias)
{
this.Next(" AS ").Next(alias);
return(this);
}
public static SqlExpr Func(this string name, params SqlExpr[] args)
{
return(SqlExpr.Func(name, args));
}