后端
/// <summary>
/// SpecificQjValue(格式:1,2) 改为 1 as SpecificQjValue0,2 as SpecificQjValue1格式
/// </summary>
/// <param name="SpecificQjValue"></param>
/// <returns></returns>
public string GetQjValue(string SpecificQjValue)
{
int i = 0;
string strQjValue = string.Empty;
SpecificQjValue.Split(',').ToList().ForEach(x =>
{
strQjValue = (string.IsNullOrEmpty(strQjValue)) ? x + " as SpecificQjValue" + i :
strQjValue + "," + x + " as SpecificQjValue" + i;
i++;
});
return strQjValue;
}
///获取详情
public JsonModelInfoList<JsonSampleAcceptQjMxInfo> GetSampleAccepQjMxInfo(int Id)
{
string sql = string.Empty;
List<JsonSampleAcceptQjMxInfo> rowQj = DapperHelper.Select<JsonSampleAcceptQjMxInfo>(TargetDB.Target,
"select * from SampleAcceptQjMxInfo where SampleAcceptQjId = " + Id, null);
rowQj.ForEach(x =>
{
string strQjValue = GetQjValue(x.SpecificQjValue);
string sql1 = "select m.CreateTime,m.BatchNum,m.VolumeNum,m.ResultOk, " + strQjValue +
",(select MakeName from SampleAcceptQjInfo where Id = m.SampleAcceptQjId ) as MakeName," +
"(select AdmitName from SampleAcceptQjInfo where Id = m.SampleAcceptQjId ) as AdmitName," +
"stuff((select ','+ SpecQjColName from SpecificQjInfo t where SpecificMxId = m.SpecificMxId
order by Id for xml path('')), 1, 1, '') as SpecQjColName
from SampleAcceptQjMxInfo m where m.Id = " + x.Id;
sql = (string.IsNullOrEmpty(sql)) ? sql1 : sql + " union all " + sql1;
});
List<JsonSampleAcceptQjMxInfo> rows = DapperHelper.Select<JsonSampleAcceptQjMxInfo>(TargetDB.Target, sql, null);
// 总数
int total = rows.Count;
List<JsonSampleAcceptQjMxInfo> temp = rows;
return new JsonModelInfoList<JsonSampleAcceptQjMxInfo> { total = total, rows = rows};
}
原先查询结果
以上后端代码运行后最终查询结果:SpecificQjValue可以为多个 不固定 动态的
一般处理程序
public void GetSampleAccepQjMxInfo()
{
int Id = Convert.ToInt32(Context.Request["Id"].Trim());
//int pageSize = Convert.ToInt32(Context.Request["rows"].ToString());
//int pageIndex = Convert.ToInt32(Context.Request["page"].ToString());
JsonModelInfoList<JsonSampleAcceptQjMxInfo> datasource = bll.GetSampleAccepQjMxInfo(Id);
int i = 0;
int colCount = 6 + datasource.rows[0].SpecQjColName.Split(',').Length;//固定列+动态列
string jsonCol = ",\"totalCol\":"+ colCount + ",\"columns\":[{\"field\":\"CreateTime\",\"title\":\"检查日期\"}, {\"field\":\"BatchNum\",\"title\":\"批号\"},{\"field\":\"VolumeNum\",\"title\":\"卷号\"}";
datasource.rows[0].SpecQjColName.Split(',').ToList().ForEach(x =>
{
jsonCol += ",{\"field\":\"SpecificQjValue"+i+"\",\"title\":\"" + x + "\"}";
i++;
});
jsonCol += ",{\"field\":\"ResultOk\",\"title\":\"判定\"},{\"field\":\"MakeName\",\"title\":\"检查员\"},{\"field\":\"AdmitName\",\"title\":\"承认\"}]}";
string jsonRow = JsonConvert.SerializeObject(datasource);
string json = jsonRow.Substring(0, jsonRow.Length - 1) + jsonCol;
Context.Response.ContentType = "json";
Context.Response.Write(json);
}
前端获取的Json格式:
{
"total":1,
"rows":[{"Id":6,"QjNo":"1","TicketName":"666666PC","ManageProject":"1"},{"Id":7,"QjNo":"777","TicketName":"77777PC","ManageProject":"7"}],
"totalCol":1,
"columns":[{"field":"1111","title":"1111"},{"field":"2222","title":"2222"}]
}
$(function () {
$.ajax({
type: "post",
url: '',
data: { Id: $("#hidID").val() },
dataType: "json",
async: false,
cache: false,
error: function (x, e) {return true;},
success: function (data)
if (data.total > 0) {
var successData={
total:data.total,
rows:data.rows
};
var arrays = [];
var columnsArray = [];
for (var i = 0; i < data.totalCol; i++) { arrays.push({ field: '', title: '', width: '' }); }
columnsArray.push(arrays);//[[]]形式
$(data.columns).each(function(index,value){
columnsArray[0][index]['field'] = value.field;
columnsArray[0][index]['title'] = value.title;
columnsArray[0][index]['width'] = "100";
});
//赋值
$('#mygrid').datagrid({
columns:columnsArray,
data: successData
});
}
}
});
})