//demo of sqlbuilder
static void Main(string[] args)
{
P p = new P();
DBBase db = new DBBase();
QueryClauses q = new QueryClauses();
OrderByClauses o = new OrderByClauses();
DBBase.GetQueryClauses("q0=age,eq,&q1=sex,eq,1&q2=age,lt,30&q3=sex,in,1,2,3,4&q4=id,eq,a001&orderby=sex,asc,age,desc", q, o);
SelectClauses s = new SelectClauses();
db.GetSelectClauses(p, s);
JoinClauses j = new JoinClauses();
db.GetJoinClauses(p, j);
WhereClauses w = new WhereClauses();
db.GetWhereClauses(p, q, w);
db.GetOrderByClauses(p, o);
SQLClause sql = new SQLClause(s,
db.GetFromClause(p),
j, w, o);
Trace.WriteLine(sql.ToString());
}
/*
* Where clause
* Get where clause
*/
public void GetWhereClauses <T>(T obj, QueryClauses query, WhereClauses where)
{
if (obj == null || query == null || where == null)
{
throw new ArgumentNullException("Null parameter.");
}
foreach (KeyValuePair <string, QueryClauses.QueryClause> e in query.Get())
{
where.Add(
GetColumnTypeByProp(obj, e.Value._key)._alias,
e.Value._key,
e.Value._operator,
GetColumnTypeByProp(obj, e.Value._key)._type,
Convert(GetColumnTypeByProp(obj, e.Value._key)._type, e.Value._value));
}
}
/* used to analyze query string with below format
* ex: q1=sex,eq,1&q2=age,lt,30&q3=sex,in,1,2,3,4&orderby=sex,asc,age,desc"
* q1: a query with no duplicated number, uses & to join multiple query conditions
* sex: a defined column name exists table
* eq: comparsion operator such as eq(=), nq(!=), lt(<), le(<=), gt(>), ge(>=), like, in
* 1: value
*/
public static void GetQueryClauses(string queries, QueryClauses query, OrderByClauses order)
{
if (String.IsNullOrEmpty(queries) || query == null || order == null)
{
throw new ArgumentNullException("Null parameter.");
}
foreach (string s in queries.Split('&'))
{
string[] part = s.Split('=');
if (part.Length != 2)
{
throw new ArgumentOutOfRangeException("Wrong query string.");
}
if (new Regex(@"q\d+", RegexOptions.IgnoreCase).Match(part[0]).Success)
{
string[] o = part[1].Split(new Char[] { ',', ' ' }, 3);
if (o.Length != 3)
{
throw new ArgumentOutOfRangeException("Wrong query string.");
}
query.Add(part[0], o[0], EnumExtensions.Convert <Operator>(o[1]), o[2]);
}
if (new Regex(@"orderby", RegexOptions.IgnoreCase).Match(part[0]).Success)
{
string[] o = part[1].Split(',');
if (o.Length % 2 != 0)
{
throw new ArgumentOutOfRangeException("Wrong orderby string.");
}
for (int i = 0; i < o.Length; i = i + 2)
{
order.Add(null, o[0 + i], EnumExtensions.Convert <SortType>(o[1 + i]));
}
}
}
}