public async Task <IActionResult> DynamicSave([FromBody] ObjectInfoModel model)
{
var res = extractDbService.DynamicInsertUpdate(model);
return(Json(new ResultBase <DataTable>()
{
success = true, data = res.Data
}));
}
public ActionResultType <DataTable> DynamicInsertUpdate(ObjectInfoModel model)
{
DataTable tbResult = null;
var res = new ActionResultType <DataTable>()
{
Data = null, Success = false
};
var tableInfo = GetTableInfo(model.TableName);
var columnNames = tableInfo.ColumnInfos.Select(c => c.ColumnName.ToLower());
var idField = model.Fields.FirstOrDefault(c => c.FieldName.ToLower() == "id");
var mappingFields = model.Fields.Where(c => columnNames.Contains(c.FieldName.ToLower()) &&
string.Compare(c.FieldName, "Id", true) != 0).ToList();
if (!(idField != null && idField.FieldValue.ToString() != "0"))
{
var command = $"insert into [{model.TableName}] ";
var insertColumns = new List <string>();
var valueParams = new List <string>();
var paramaters = new List <SqlParameter>();
foreach (var field in mappingFields)
{
insertColumns.Add($"[{field.FieldName}]");
valueParams.Add($"@{field.FieldName}");
paramaters.Add(new SqlParameter($"@{field.FieldName}", field.FieldValue));
}
command += "(" + (string.Join(" , ", insertColumns)) + " ) ";
command += $"values({string.Join(", ", valueParams)})";
command += " SELECT CAST(scope_identity() AS int);";
var newId = context.ExecScalar(command, paramaters);
tbResult = context.ExecDataTable($"select * from [{model.TableName}] where id=@id", CommandType.Text, new SqlParameter("@id", newId));
}
else
{
var command = $"update [{model.TableName}] set ";
var listSetValues = new List <string>();
var paramaters = new List <SqlParameter>();
foreach (var field in mappingFields)
{
listSetValues.Add($"[{field.FieldName}]=@{field.FieldName}");
paramaters.Add(new SqlParameter($"@{field.FieldName}", field.FieldValue));
}
paramaters.Add(new SqlParameter("@Id", idField.FieldValue));
command += (string.Join(" , ", listSetValues));
command += $" where id=@id";
context.ExecScalar(command, paramaters);
tbResult = context.ExecDataTable($"select * from [{model.TableName}] where id=@id", CommandType.Text, new SqlParameter("@id", idField.FieldValue));
}
return(new ActionResultType <DataTable>()
{
Success = true, Data = tbResult
});
}
