public Result Select(string sql)
{
List<string> fields = new List<string>();
string fieldset = sql.Split(new string[] { "FROM" }, StringSplitOptions.None)[0].Replace("SELECT", "");
string[] sf = fieldset.Split(',');
foreach (string item in sf)
{
string adder = "";
if (item.ToLower().Contains(" as "))
{
adder = item.ToLower().Split(new string[] { " as " }, StringSplitOptions.None)[1].Trim();
}
else
{
adder = item.Trim();
}
if (adder.Contains('.'))
{
adder = adder.Split('.')[1];
}
fields.Add(adder);
}
this.cmd.CommandText = sql;
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}
public Result getRow(Joinable j, string[] fields, Condition[] c = null, GroupBy g = null, OrderBy[] o = null, int limit = 0)
{
this.cmd.CommandText = "SELECT " + String.Join(",", fields) + " FROM " + j.GetTableName();
if (c != null)
{
this.cmd.CommandText += " WHERE (";
foreach (Condition item in c)
{
this.cmd.CommandText += item.ToString() + " AND ";
}
this.cmd.CommandText = this.cmd.CommandText.Remove(this.cmd.CommandText.Length - 5);
this.cmd.CommandText += ") ";
}
if (g != null)
{
this.cmd.CommandText += g.ToString();
}
if (o != null)
{
this.cmd.CommandText += " ORDER BY ";
foreach (OrderBy item in o)
{
this.cmd.CommandText += item.ToShortString() + ", ";
}
this.cmd.CommandText = this.cmd.CommandText.Remove(this.cmd.CommandText.Length - 2);
}
if (limit != 0)
{
this.cmd.CommandText += " LIMIT " + limit.ToString();
}
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}
/// <summary>
/// Get a Join on TWO tables.
/// </summary>
/// <param name="tables"></param>
/// <param name="c"></param>
/// <param name="g"></param>
/// <param name="o"></param>
/// <returns></returns>
public Result Join(Joinable[] tables, Condition[] c, GroupBy g, OrderBy[] o)
{
string sql = "SELECT ";
List<string> fields = new List<string>();
foreach (Joinable j in tables)
{
sql += String.Join(",", j.GetFields(true)) + ", ";
foreach (string item in j.GetFields(true))
{
fields.Add(item);
}
}
sql = sql.Remove(sql.Length - 2);
sql += " FROM ";
foreach (Joinable j in tables)
{
sql += j.GetTableName() + ", ";
}
sql = sql.Remove(sql.Length - 2);
sql += " WHERE (";
sql += tables[0].GetJoinOn(tables[1]) + "=" + tables[1].GetJoinOn(tables[0]); //TODO: make this work for more tables...
sql += ") ";
if (c != null)
{
sql += " AND (";
foreach (Condition item in c)
{
sql += item.ToString() + " AND ";
}
sql = sql.Remove(sql.Length - 5);
sql += ") ";
}
if (g != null)
{
sql += g.ToString();
}
if (o != null)
{
sql += " ORDER BY ";
foreach (OrderBy item in o)
{
sql += item.ToShortString() + ", ";
}
sql = sql.Remove(sql.Length - 2);
}
this.cmd.CommandText = sql;
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}
public Result getRow(string table, string[] fields, string filter = "", string orderby = "", int limit = 0)
{
this.cmd.CommandText = "SELECT " + String.Join(",", fields) + " FROM " + table;
if (!String.IsNullOrEmpty(filter)) this.cmd.CommandText += " WHERE " + filter;
if (!String.IsNullOrEmpty(orderby)) this.cmd.CommandText += " ORDER BY " + orderby;
if (limit > 0) this.cmd.CommandText += " LIMIT " + limit.ToString();
Result r = new Result(this.cmd.CommandText);
if (this.connection.State != System.Data.ConnectionState.Open) this.connection.Open();
SQLiteDataReader re = this.cmd.ExecuteReader();
while (re.Read())
{
Row row = new Row(re.StepCount);
foreach (string f in fields)
{
row.AddCell(f, re[f].ToString());
}
r.AddRow(row);
}
re.Close();
return r;
}