public List <string> fieldDropdownList(string queryName
, string fieldName, string desc = "desc")
{
List <string> ret = null;
string sql = string.Format(@"
select fv.fieldValue
from queries q
join queryFields qf on q.queryId=qf.queryId
join fields f on qf.fieldId=f.fieldId and f.fieldName=@fieldName
join fieldValues fv on fv.fieldId=qf.fieldId
join [rows] r on fv.rowId=r.rowId and r.tableId=q.tableId
where q.queryName=@queryName
group by fv.fieldValue
order by fv.fieldValue {0}
", desc);
DapperSelect <string> dp = new
DapperSelect <string>(connS);
DynamicParameters dpara = new DynamicParameters();
dpara.Add("@queryName", queryName, DbType.String);
dpara.Add("@fieldName", fieldName, DbType.String);
var qry = dp.Select <string>(sql, dpara);
if (qry != null && qry.Any())
{
ret = qry.ToList();
}
return(ret);
}
public List <string> formParameters(string queryName)
{
List <string> ret = null;
string sql = string.Format(@"
with expressList
as
(
select a.*
from expressions a
join queries q on a.expressionId=q.whereExpressionId
where q.queryName=@queryName
union all
select b.*
from expressions b
join expressList c on c.subExpression1Id=b.expressionId
union all
select d.*
from expressions d
join expressList e on e.subExpression2Id=d.expressionId
)
select e.para1externalName externalPara
from expressList e
where e.para1externalName is not null
union
select e.para2externalName externalPara
from expressList e
where e.para2externalName is not null
");
// formParameters
DapperSelect <string> dp = new
DapperSelect <string>(connS);
DynamicParameters dpara = new DynamicParameters();
dpara.Add("@queryName", queryName, DbType.String);
var qry = dp.Select <string>(sql, dpara);
if (qry.Any())
{
ret = qry.ToList();
}
return(ret);
}
protected List <queryWhereRec> whereConditions(string queryName)
{
List <queryWhereRec> ret = null;
string sql;
// sql = @"
//select o.operatorName, o.stringInSourceCode, o.isPrefix, o.paraNum
// , e.paraField1id, f1.fieldName field1Name
// , e.paraField2id, f2.fieldName field2Name
// , e.para2externalName, e.subExpression1Id, e.subExpression2Id
//from queries q
//join expressions e on q.whereExpressionId=e.expressionId
//join operators o on e.operatorId=o.operatorId
//join fields f1 on e.paraField1id=f1.fieldId
//left join fields f2 on e.paraField2id=f2.fieldId
//where q.queryName=@queryName
//";
sql = @"
with expressList
as
(
select a.*, cast (1 as bit) isWhereExpressId
from expressions a
join queries q on a.expressionId=q.whereExpressionId
where q.queryName=@queryName
union all
select b.*, cast (0 as bit) isWhereExpressId
from expressions b
join expressList c on c.subExpression1Id=b.expressionId
where c.subExpression1Id is not null
union all
select d.*, cast (0 as bit) isWhereExpressId
from expressions d
join expressList e on e.subExpression2Id=d.expressionId
where e.subExpression2Id is not null
)
select e.isWhereExpressId,
e.expressionId, o.operatorName, o.stringInSourceCode, o.isPrefix, o.paraNum
, e.paraField1id, f1.fieldName field1Name
, e.paraField2id, f2.fieldName field2Name
, e.para2externalName, e.subExpression1Id, e.subExpression2Id
from expressList e
join operators o on e.operatorId=o.operatorId
left join fields f1 on e.paraField1id=f1.fieldId
left join fields f2 on e.paraField2id=f2.fieldId
";
//EF hit as well
//var qry = efs.select<queryWhereRec>(sql
// , new SqlParameter("queryName", queryName));
DapperSelect <queryWhereRec> dp = new
DapperSelect <queryWhereRec>(connS);
DynamicParameters dpara = new DynamicParameters();
dpara.Add("@queryName", queryName, DbType.String);
var qry = dp.Select <queryWhereRec>(sql, dpara);
if (qry.Any())
{
ret = qry.ToList();
}
return(ret);
}