protected string query(ref tablesViewModel viewModel)
{
string ret = "";
tableDisp tmpModel = viewModel.editModel;
tableLib tlib = new tableLib(connEPLabDB);
var paras = new Dapper.DynamicParameters();
paras.Add("TableName", tmpModel.tableName + "", DbType.String);
paras.Add("TableDesc", tmpModel.tableDesc + "", DbType.String);
string sql = @"
select top 1000 *
from tables
where (@TableName='' or tablename=@TableName)
and (@TableDesc='' or tableDesc=@TableDesc)
";
var qry = tlib.Query <tableDisp>(sql, paras);
if (qry.Any())
{
viewModel.queryResult = qry.ToList();
}
else
{
viewModel.queryResult = null;
}
return(ret);
}
public SelectList tableList()
{
SelectList ret;
List <SelectListItem> _table = new List <SelectListItem>();
tableLib tlib = new tableLib(connS);
List <tables> tableList = tlib.GetAll();//.getAll().ToList();
if (tableList != null)
{
foreach (tables rec in tableList)
{
_table.Add(new SelectListItem()
{
Text = rec.tableName,
Value = rec.tableId.ToString()
});
}
}
ret = new SelectList(_table, "Value", "Text", null);
return(ret);
}
public ActionResult Index(tablesViewModel viewModel)
{
ActionResult ar;
string multiSelect = Request.Form[MultiSelect] + "";
tablesViewModel tmpVM;
viewModel.clearMsg();
ViewBag.pageStatus = TempData[PageStatus];
if (ViewBag.pageStatus == null)
{
ViewBag.pageStatus = (int)PAGE_STATUS.QUERY;
}
tableLib dapperLib = new tableLib(connEPLabDB);
EPLabDBbigger bigLib = new EPLabDBbigger(connEPLabDB);
string tableId;
string tableName;
tables model;
switch (viewModel.cmd)
{
case "query":
if (ViewBag.pageStatus <= (int)PAGE_STATUS.QUERY)
{
viewModel.errorMsg = query(ref viewModel);
ar = View(viewModel);
}
else
{
ViewBag.pageStatus = (int)PAGE_STATUS.QUERY;
TempData[modelName] = null;
TempData[PageStatus] = ViewBag.pageStatus;
ar = RedirectToAction("Index");
return(ar);
}
break;
case "add":
case "addNew":
viewModel.editModel = new tableDisp();
ViewBag.pageStatus = (int)PAGE_STATUS.ADD;
TempData[modelName] = null;
TempData[PageStatus] = ViewBag.pageStatus;
ar = RedirectToAction("Index");
return(ar);
case "update":
model = dapperLib.GetOne(new Guid(viewModel.singleSelect));
if (model != null)
{
tmpVM = new tablesViewModel();
tmpVM.editModel = JsonConvert.DeserializeObject <tableDisp>(
JsonConvert.SerializeObject(model));
TempData[PageStatus] = (int)PAGE_STATUS.EDIT;
TempData[modelName] = tmpVM;
ar = RedirectToAction("Index");
return(ar);
}
viewModel.errorMsg = $"error reading this {modelMessage}";
ar = View(viewModel);
break;
case "fields":
tableId = viewModel.singleSelect;
tableName = dapperLib.TableNameById(new Guid(tableId));
if (!string.IsNullOrWhiteSpace(tableName))
{
Session["tableId"] = tableId;
Session["tableName"] = tableName;
ar = RedirectToAction("Index", "Field");
return(ar);
}
viewModel.errorMsg = $"error reading this {modelMessage}";
ar = View(viewModel);
break;
case "rows":
tableId = viewModel.singleSelect;
tableName = dapperLib.TableNameById(new Guid(tableId));
if (!string.IsNullOrWhiteSpace(tableName))
{
Session["tableId"] = tableId;
Session["tableName"] = tableName;
ar = RedirectToAction("Index", "Row");
return(ar);
}
viewModel.errorMsg = $"error reading this {modelMessage}";
ar = View(viewModel);
break;
case "delete":
if (string.IsNullOrWhiteSpace(multiSelect))
{
viewModel.errorMsg = $"please select {modelMessage} to delete";
}
else
{
string[] selected = multiSelect.Split(',');
foreach (string recId in selected.ToList())
{
model = dapperLib.GetOne(new Guid(recId));
if (model == null)
{
continue;
}
viewModel.errorMsg += dapperLib.Delete(model);
}
if (string.IsNullOrWhiteSpace(viewModel.errorMsg))
{
viewModel.successMsg = "successfully deleted";
viewModel.errorMsg = query(ref viewModel);
}
}
ar = View(viewModel);
break;
case "save":
string err = checkForm(viewModel);
if (err.Length > 0)
{
viewModel.errorMsg = err;
ar = View(viewModel);
break;
}
if (ViewBag.pageStatus == (int)PAGE_STATUS.ADD)
{
viewModel.editModel.tableId =
bigLib.getNewId("tables");
model = JsonConvert.DeserializeObject <tables>(
JsonConvert.SerializeObject(viewModel.editModel));
viewModel.errorMsg = dapperLib.Insert(model);
if (string.IsNullOrWhiteSpace(viewModel.errorMsg))
{
viewModel.successMsg = $"new {modelMessage} added";
ViewBag.pageStatus = (int)PAGE_STATUS.ADDSAVED;
}
}
else if (ViewBag.pageStatus == (int)PAGE_STATUS.EDIT)
{
var qry = dapperLib.GetOne(viewModel.editModel.tableId);
if (qry != null)
{
model = JsonConvert.DeserializeObject <tables>(
JsonConvert.SerializeObject(viewModel.editModel));
viewModel.errorMsg = dapperLib.Update(model);
if (string.IsNullOrWhiteSpace(viewModel.errorMsg))
{
viewModel.successMsg = $"{modelMessage} updated";
ViewBag.pageStatus = (int)PAGE_STATUS.SAVED;
}
}
else
{
viewModel.errorMsg = $"{modelMessage} not found";
}
}
else
{
viewModel.errorMsg = $"wrong page status {ViewBag.pageStatus}";
}
ar = View(viewModel);
break;
default:
ar = View(viewModel);
break;
}
TempData[modelName] = viewModel;
TempData[PageStatus] = ViewBag.pageStatus;
return(ar);
}
