public String getDataTable(
bool isIncludeData = false,
bool isIncludeFooter = true,
String qDataTableID = "qViewTable") {
StringBuilder Table = new StringBuilder();
StringBuilder THead = new StringBuilder();
THead.AppendLine("<tr>");
foreach (var Column in ColumDef) {
THead.Append("<th>");
THead.Append(Util.toCaption(Column));
THead.AppendLine("</th>");
}//foreach
if (IsPrimaryKey) THead.Append("<th class=\"menu\"> </th>");
THead.AppendLine("</tr>");
Table.AppendLine($"<table id=\"{qDataTableID}\" class=\"{ClassName}\">");
Table.AppendLine("<thead>");
Table.Append(THead);
Table.AppendLine("</thead>");
if (isIncludeData) {
Table.AppendLine("<tbody>");
Table.Append(getTableRows());
Table.AppendLine("</tbody>");
}
//add total row to the sql
if (!String.IsNullOrEmpty(TotalSQL)) {
Dictionary<String, Object> Row = Util.getDBRow(TotalSQL);
StringBuilder TotalRow = new StringBuilder();
TotalRow.AppendLine("<tfoot>");
TotalRow.AppendLine("<tr>");
foreach (var Column in Row) {
switch (Column.Key.ToLower()) {
case "hasrows":
break;
default:
TotalRow.AppendLine("<th>" + Column.Value.ToString() + "</th>");
break;
}
}
if (IsPrimaryKey) TotalRow.AppendLine("<th></th>");
TotalRow.AppendLine("</tr>");
TotalRow.AppendLine("</tfoot>");
Table.Append(TotalRow);
}
if (isIncludeFooter) {
Table.AppendLine("<tfoot>");
Table.Append(THead);
Table.AppendLine("</tfoot>");
}
Table.AppendLine("</table>");
return Table.ToString();
}//getDataTable()
public String getTable()
{
int Col = 0;
StringBuilder Table = new StringBuilder();
using (var ctx = new ExponentPortalEntities())
using (var cmd = ctx.Database.Connection.CreateCommand()) {
ctx.Database.Connection.Open();
cmd.CommandText = SQL;
using (var reader = cmd.ExecuteReader()) {
//For each row
while (reader.Read())
{
Table.AppendLine("<table class=\"qDetailView\">");
for (int i = 0; i < reader.FieldCount; i++)
{
Col = Col + 1;
if (Col == 1)
{
Table.AppendLine("<tr>");
}
Table.Append("<td>");
Table.Append("<span class=\"caption\">");
Table.Append(Util.toCaption(reader.GetName(i)));
Table.Append(":</span>");
Table.Append("<span class=\"value\">");
//checking whether the column required link
if (FormatCols.ContainsKey(reader.GetName(i)))
{
Table.Append(FormatCols[reader.GetName(i)]);
}
else
{
Table.Append("$" + reader.GetName(i).ToString() + "$");
}
Table.Append("</span>");
Table.Append("</td>");
if (Col >= Columns)
{
Col = 0;
Table.AppendLine("</tr>");
}
} //for
for (int i = 0; i < reader.FieldCount; i++)
{
Table.Replace("$" + reader.GetName(i) + "$", reader.GetValue(i).ToString());
}
//add additional columns if required
if (Col > 0)
{
for (int i = Col + 1; i <= Columns; i++)
{
Table.AppendLine("<td></td>");
}
Table.AppendLine("</tr>");
}
Table.AppendLine("</table>");
} //while
} //using reader
}//using database
return(Table.ToString());
} //getTable()
