//获取表格数据Json
public string GetDataListJson(DataTable dt, int PageStart, int PageIndex, int PageSize, string order)
{
string strwhere = SetStrWhere(dt);
string sumsqlStr = GetTSQL(TableModel.SQL, "COUNT(" + OneFileName + ") as COUNTS", strwhere, "", false);
string sqlStr = PageBySQL(TableModel.SQL, TableModel.TableName, OneFileName, strwhere, order, PageIndex, PageSize);
DataSet ds = BaseClass.GetDataSet(sqlStr + sumsqlStr);
if (ds != null)
{
DataTable tableJson = ds.Tables[0];
DataTable tableSum = ds.Tables[1];
if (tableJson != null)
{
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableSum.Rows[0]["COUNTS"].ToString() + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
string datatablejson = JsonHelper.DataTableToJsonWithJsonNet(tableJson);
sb.Append(datatablejson);
sb.Append(SetSumHtml(strwhere));
sb.Append("\"}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
/// <summary>
/// 获取表格数据Json
/// </summary>
/// <param name="PageStart"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="order"></param>
/// <returns></returns>
public string GetDataListJson(int PageStart, int PageIndex, int PageSize, string where, string order)
{
var TableModel = this.GetTableModel();
if (TableModel.TableType == 1)
{
System.Data.DataSet ds = BaseClass.GetDataSet(SetFieldSQL(where, order, PageIndex, PageSize) + SetCountSQL(where));
if (ds != null)
{
System.Data.DataTable tableJson = ds.Tables[0];
System.Data.DataTable tableCount = ds.Tables[1];
if (tableJson != null)
{
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableCount.Rows[0]["COUNTS"].ToString() + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
string datatablejson = JsonHelper.DataTableToJsonWithJsonNet(tableJson);
sb.Append(datatablejson);
sb.Append(SetSumHtml(tableCount));
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else if (TableModel.TableType == 2)
{
var TableFielModelList = this.GetTableFieldModel();
System.Data.DataTable dataTable = BaseClass.GetDataTableColumns(TableModel.SQL);
var tableJson = BaseClass.GetDataTableForXML(dataTable, TableModel.TableName);
var WhereSQL = GetWhereSQL(where, 2);
var OrderBySQL = GetOrderBySQL(order, 2);
System.Data.DataRow[] queryRsesult = tableJson.Select(WhereSQL, OrderBySQL);
System.Data.DataTable dtCopy = tableJson.Clone();
int minNum = (Convert.ToInt32(PageIndex) - 1) * Convert.ToInt32(PageSize);
int maxNum = Convert.ToInt32(PageIndex) * Convert.ToInt32(PageSize);
for (int i = minNum; i < queryRsesult.Length && i < maxNum; i++)
{
dtCopy.Rows.Add(queryRsesult[i].ItemArray);
}
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableJson.Rows.Count + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
var datatablejson = JsonHelper.DataTableToJsonWithJsonNet(dtCopy);
sb.Append(datatablejson);
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
//获取表格单条数据
public string GetDataViewJson(string IdentityValue)
{
string sql = string.Format("select * from {0} where {1}='{2}'", TableModel.TableName, OneFileName, IdentityValue);
DataTable tableJson = DAL.SQLDBHelpercs.ExecuteReaderTable(sql, null);
if (tableJson != null && tableJson.Rows.Count > 0)
{
return(JsonHelper.DataTableToJsonWithJsonNet(tableJson));
}
else
{
return("");
}
}
/// <summary>
/// 获取单行数据
/// </summary>
/// <param name="ItemID"></param>
/// <returns></returns>
public string GetDataView(string ItemID)
{
var TableModel = this.GetTableModel();
if (TableModel.TableType == 1)//表格数据
{
string sql = string.Format("SELECT TOP(1)* FROM ({0}) AS NewCyFsTable WHERE NewCyFsTable.{1}='{2}'", TableModel.SQL, TableModel.PrimaryKey, ItemID);
System.Data.DataTable tableJson = DAL.SQLDBHelpercs.ExecuteReaderTable(sql, null);
if (tableJson != null && tableJson.Rows.Count > 0)
{
return(JsonHelper.DataTableToJsonWithJsonNet(tableJson));
}
else
{
return("");
}
}
else if (TableModel.TableType == 2)//XML数据
{
var TableFielModelList = this.GetTableFieldModel();
System.Data.DataTable dataTable = BaseClass.GetDataTableColumns(TableModel.SQL);
var tableJson = BaseClass.GetDataViewForXML(dataTable, TableModel.TableName, ItemID);
if (tableJson != null && tableJson.Rows.Count > 0)
{
return(JsonHelper.DataTableToJsonWithJsonNet(tableJson));
}
else
{
return("");
}
}
else
{
return("");
}
}
/// <summary>
/// 获取表格数据Json
/// </summary>
/// <param name="PageStart"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="order"></param>
/// <returns></returns>
public string GetDataListJson(int PageStart, int PageIndex, int PageSize, string where, string order)
{
var TableModel = this.GetTableModel();
var TableFielModelList = this.GetTableFieldModel();
if (TableModel.TableType == 1)
{
System.Data.DataSet ds = BaseClass.GetDataSet(SetFieldSQL(where, order, PageIndex, PageSize) + SetCountSQL(where));
if (ds != null)
{
System.Data.DataTable tableJson = ds.Tables[0];
var FileDataType3List = TableFielModelList.Where(x => x.FieldDataType == 3).ToList();
if (FileDataType3List.Count > 0)
{
for (int j = 0; j < FileDataType3List.Count; j++)
{
var FieldDataType = FileDataType3List[j].FieldDataType;
var FieldKey = FileDataType3List[j].FieldKey;
var FieldData = FileDataType3List[j].FieldData;
if (FieldData.ToLower().Contains(".xml"))//xml文件
{
var FielDataList = FieldData.Trim(' ').Split('_');
if (FielDataList.Length >= 2)
{
var XmlNodeKeyName = FieldKey;
if (FielDataList.Length == 3)
{
XmlNodeKeyName = FielDataList[2];
}
var NewFieldKey = "ToCc" + FieldKey;
tableJson.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < tableJson.Rows.Count; i++)
{
var FileValue = tableJson.Rows[i][FieldKey].ToString();
var NewFileValue = BaseClass.XmlSelectXmlKeyInnerText(FielDataList[0], XmlNodeKeyName, FileValue, FielDataList[1]);
tableJson.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
}
}
System.Data.DataTable tableCount = ds.Tables[1];
if (tableJson != null)
{
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableCount.Rows[0]["COUNTS"].ToString() + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
string datatablejson = JsonHelper.DataTableToJsonWithJsonNet(tableJson);
sb.Append(datatablejson);
sb.Append(SetSumHtml(tableCount));
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
else if (TableModel.TableType == 2)
{
System.Data.DataTable dataTable = BaseClass.GetDataTableColumns(TableModel.SQL);
var tableJson = BaseClass.GetDataTableForXML(dataTable, TableModel.TableName);
var WhereSQL = GetWhereSQL(where, 2);
var OrderBySQL = GetOrderBySQL(order, 2, TableModel.PrimaryKey);
System.Data.DataRow[] queryRsesult = tableJson.Select(WhereSQL, OrderBySQL);
System.Data.DataTable dtCopy = tableJson.Clone();
int minNum = (Convert.ToInt32(PageIndex) - 1) * Convert.ToInt32(PageSize);
int maxNum = Convert.ToInt32(PageIndex) * Convert.ToInt32(PageSize);
for (int i = minNum; i < queryRsesult.Length && i < maxNum; i++)
{
dtCopy.Rows.Add(queryRsesult[i].ItemArray);
}
StringBuilder sb = new StringBuilder();
sb.Append("{\"total\":" + tableJson.Rows.Count + ",\"page\":1,\"limit\":" + PageSize + ",\"data\":");
var FileDataType3List = TableFielModelList.Where(x => x.FieldDataType == 3).ToList();
if (FileDataType3List.Count > 0)
{
for (int j = 0; j < FileDataType3List.Count; j++)
{
var FieldDataType = FileDataType3List[j].FieldDataType;
var FieldKey = FileDataType3List[j].FieldKey;
var FieldData = FileDataType3List[j].FieldData;
if (FieldData.ToLower().Contains(".xml"))//xml文件
{
var FielDataList = FieldData.Trim(' ').Split('_');
if (FielDataList.Length >= 2)
{
var XmlNodeKeyName = FieldKey;
if (FielDataList.Length == 3)
{
XmlNodeKeyName = FielDataList[2];
}
var NewFieldKey = "ToCc" + FieldKey;
dtCopy.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < tableJson.Rows.Count; i++)
{
var FileValue = tableJson.Rows[i][FieldKey].ToString();
var NewFileValue = BaseClass.XmlSelectXmlKeyInnerText(FielDataList[0], XmlNodeKeyName, FileValue, FielDataList[1]);
dtCopy.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
else //数据表
{
var NewFieldKey = "ToCc" + FieldKey;
dtCopy.Columns.Add(NewFieldKey, Type.GetType("System.String"));
for (int i = 0; i < dtCopy.Rows.Count; i++)
{
var NewFileValue = GetFieldKeyValue(dtCopy.Rows[i], FieldData);
dtCopy.Rows[i][NewFieldKey] = NewFileValue;
}
}
}
}
var datatablejson = JsonHelper.DataTableToJsonWithJsonNet(dtCopy);
sb.Append(datatablejson);
sb.Append("}");
return(sb.ToString().Replace("\n", ""));
}
else
{
return("{\"total\":" + 0 + ",\"page\":0,\"limit\":" + PageSize + ",\"data\":[]}");
}
}
