public QueryResult GetQueryData(int moduleId, string queryName, string condition)
{
DataQueryCollection lst = service.GetModuleQuery(moduleId);
var query = lst.FirstOrDefault(c => c.Name == queryName);
if (query == null)
return null;
using (var con = Unity.GetConnection())
{
var cmd = con.CreateCommand();
cmd.CommandText = query.SelectSQL;
if(!string.IsNullOrEmpty(condition) )
cmd.CommandText += " WHERE " + condition;
cmd.Connection = con;
con.Open();
var rd = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(rd);
var result = new QueryResult() { Total = dt.Rows.Count, Data = dt };
return result;
}
}
public QueryResult PostQuery(Query query)
{
DataQueryCollection lst = service.GetModuleQuery(query.ModuleId);
var q = lst.FirstOrDefault(c => c.Name == query.DataQueryName);
if (q == null)
return null;
var script = q.SelectSQL;
Dictionary<string, object> parameters = null;// new Dictionary<string, object>()
if (query.IsDynamic)
{
script = GetWhereStatement(query, q);
}
else
{
script = ParseScript(query, q, out parameters);
}
using (var con = Unity.GetConnection())
{
var cmd = con.CreateCommand();
cmd.CommandText = script;
cmd.Connection = con;
if (parameters.Count > 0)
{
foreach (var key in parameters.Keys)
{
var p = cmd.CreateParameter();
p.ParameterName = ":" + key;
p.Value = parameters[key];
cmd.Parameters.Add(p);
}
}
con.Open();
var rd = cmd.ExecuteReader();
var dt = new DataTable();
dt.Load(rd);
var result = new QueryResult() { Total = dt.Rows.Count, Data = dt };
return result;
}
}