private string GetWhereStatement(Query query,DataQuery q)
{
var script = q.SelectSQL;
List<string> conditions = new List<string>();
Dictionary<string, object> parameters = new Dictionary<string, object>();
foreach (var p in query.Parameters)
{
var field = q.Fields[p.FeildName];
var val = p.Value;
//if (string.IsNullOrEmpty(val))
if( val == null)
continue;
string format = field.DataType == DbType.String ? "{0} = '{1}'" : "{0} = {1}";
conditions.Add(string.Format(format, p.FeildName, p.Value));
}
var where = string.Join(" AND ", conditions.ToArray());
if (!string.IsNullOrEmpty(where))
script += " WHERE " + where;
return script;
}
private string ParseScript(Query query, DataQuery q, out Dictionary<string, object> @params)
{
var script = q.SelectSQL;
var dict = new Dictionary<string, object>();
var val = regex.Replace(script,new MatchEvaluator( m => {
var name = m.Groups["name"].Value.TrimStart('@');
var field = query.Parameters.FirstOrDefault(f => string.Compare(f.FeildName, name, true) == 0);
dict[name] = field.Value;
return ":" + name;
}));
@params = dict;
return val;
}
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;
}
}
