public static SqlQuery Translate(Expression expression)
{
var query = new SqlQuery();
var translator = new QueryTranslator(query);
translator.Visit(expression);
return query;
}
public bool Equals(SqlQuery obj)
{
var @equals = true;
@equals &= Take.Equals(obj.Take);
@equals &= Skip.Equals(obj.Skip);
@equals &= CompareList(Tables, obj.Tables);
@equals &= CompareList(ColumnsOutput, obj.ColumnsOutput);
@equals &= CompareList(OrderBy, obj.OrderBy);
@equals &= CompareList(Joins, obj.Joins);
@equals &= ((WhereClause != null && WhereClause.Equals(obj.WhereClause)) || (WhereClause == null && obj.WhereClause == null));
return @equals;
}
protected override string GetQueryText(SqlQuery query)
{
var sql = query.ToString();
var limit = " LIMIT {0}";
if (query.Skip > 0) {
limit = string.Format(limit, query.Skip, "{0}");
if (query.Take > 0) {
limit = string.Format("{0}, {1}", limit, query.Take);
}
sql += limit;
} else if (query.Take > 0) {
sql += string.Format(limit, string.Format("0, {0}", query.Take));
}
return sql;
}
protected override object ExecuteScalar(SqlQuery query)
{
using (var conn = (MySqlConnection)GetConnection()) {
var sql = GetQueryText(query);
var cmd = new MySqlCommand(sql, conn);
foreach (var param in query.Parameters) {
cmd.Parameters.AddWithValue(param.Name, param.Value);
}
return cmd.ExecuteScalar();
}
}
protected override IDataReader ExecuteReader(SqlQuery query)
{
var conn = (MySqlConnection)GetConnection();
var sql = GetQueryText(query);
var cmd = new MySqlCommand(sql, conn);
foreach (var param in query.Parameters) {
//object value;
//var arrayObj = param.Value as Array;
//if (arrayObj != null) {
// var arrayStrings = new string[arrayObj.Length];
// for (int i = 0; i < arrayObj.Length; i++) {
// arrayStrings[i] = arrayObj.GetValue(i).ToString();
// }
// value = string.Join(",", arrayStrings);
//} else {
// value = param.Value;
//}
//cmd.Parameters.AddWithValue(param.Name, value);
cmd.Parameters.AddWithValue(param.Name, param.Value);
}
return cmd.ExecuteReader();
}
protected override string GetQueryText(SqlQuery query)
{
var sql = query.ToString();
// custom settings
if (query.Skip > 0) {
// Paging
var sql2 = "SELECT * FROM ({0}) AS Limit WHERE {1}";
var rownumber = "SELECT ROW_NUMBER() OVER(ORDER BY {0}) AS Row,";
string where;
if (query.Take > 0) {
where = string.Format("Row BETWEEN {0} AND {1}", query.Skip + 1, query.Take + query.Skip);
} else {
where = string.Format("Row > {0}", query.Skip);
}
if (query.OrderBy.Count > 0) {
var list = new List<string>();
foreach (var column in query.OrderBy) {
list.Add(column.ToString());
}
rownumber = string.Format(rownumber, string.Join(",", list.ToArray()));
} else {
rownumber = string.Format(rownumber, "(SELECT 1)");
}
sql = string.Format(sql2, rownumber + sql.Remove(0, 6), where);
} else if (query.Take > 0) {
// top
sql = string.Format("SELECT TOP {0}", query.Take) + sql.Remove(0, 6);
}
return sql;
}
protected override IDataReader ExecuteReader(SqlQuery query)
{
var conn = (SqlConnection)GetConnection();
var sql = GetQueryText(query);
var cmd = new SqlCommand(sql, conn);
foreach (var param in query.Parameters) {
cmd.Parameters.AddWithValue(param.Name, param.Value);
}
return cmd.ExecuteReader();
}
private QueryTranslator(SqlQuery query)
{
Query = query;
}