/// <summary>
/// Saves a lookup table row for a lookup table. If the table doesn't exist, nothing occurs. If the row exists,
/// it is updated; otherwise, the row is inserted.
/// </summary>
/// <param name="model">The lookup table row model to be updated.</param>
public virtual void SaveLookupTableRow(LookupTableRowModel model)
{
if (model == null)
throw new ArgumentNullException("model");
var lookupTable = GetLookupTable(model.TableId);
if (lookupTable == null)
return;
//sync the column names
var columns = LookupTableAdapter.MetadataColumnNames.Union(lookupTable.KeysAndColumns.Select(c => c.Name)).ToArray();
var row = lookupTable.Rows.FirstOrDefault(r => Convert.ToInt64(r.RowId) == model.RowId && r.EffectiveDate == EffectiveDate);
//get the columns/values
var values = columns.Zip(model.Values, (col, val) => new { col, val })
.ToDictionary(x => x.col, x =>
TypeDescriptor.GetConverter(
lookupTable.AllColumns.First(c => c.Name == x.col).DataType).ConvertFromString(x.val));
//remove columns that don't need to be updated
if (values.ContainsKey("Id"))
values.Remove("Id");
var parameters = new Dictionary<string, object>
{
{ "Schema", lookupTable.Properties.Schema },
{ "Table", lookupTable.Name }
};
LookupTableCommand commandType;
if (row == null) //add the row
{
commandType = LookupTableCommand.InsertRow;
if (values.Count() != columns.Count() - 1)
throw new Exception(
string.Format("LookupTableRowModel value count ({0}) does not equal the number of columns in target table {1}.",
values.Count() + 1, lookupTable.Name));
//set the changeId if it hasn't been set
Guid changeId;
if (Guid.TryParse(values["ChangeId"].ToString(), out changeId))
{
if (changeId == Guid.Empty)
values["ChangeId"] = Guid.NewGuid();
}
else
{
values["ChangeId"] = Guid.NewGuid();
}
//increment the sequence number if it's a low value
if (Convert.ToInt64(values["Sequence"]) <= 0)
values["Sequence"] = lookupTable.Rows.Any() ? lookupTable.Rows.Max(r => r.Sequence) + 1 : 1;
}
else //update the row
{
commandType = LookupTableCommand.UpdateRow;
parameters.Add("Where", new SqlWhereClauseItem
{
ColumnName = "Id",
ColumnValue = model.RowId,
Operator = SqlWhereOperator.Equal
});
}
parameters.Add("Values", values);
using (var command = _contexts[lookupTable.Properties.Context].GetCommand(commandType))
{
command.Execute(parameters);
}
}
public ActionResult SaveRateTableRow(LookupTableRowModel model)
{
TableStrategy.SaveLookupTableRow(model);
return Json(true, JsonRequestBehavior.AllowGet);
}
public ActionResult SaveLookupTableRow(LookupTableRowModel model)
{
TableStrategy.SaveLookupTableRow(model);
return View("LookupTable", TableStrategy.GetLookupTable(model.TableId));
}
public void SaveLookupTableRow(LookupTableRowModel model)
{
var columns = GetTableColumns(model.TableName);
var columnNames = columns.Select(p => p.ColumnName).ToArray();
//if (!SetupMode)
//{
//(string.IsNullOrEmpty(model.RowId) || model.RowId == "0")
if (model.RowId == 0 || model.EffectiveDate != EffectiveDate)
{
// Create new row
RatingTableQuery(FlatTableQueries.InsertRowScript(
model.TableName, columnNames, model.Values, model.ChangeId.ToString(), EffectiveDate, model.Active));
}
else if (model.EffectiveDate == EffectiveDate)
{
// Update the row
RatingTableQuery(FlatTableQueries.UpdateRowScript(model.RowId, model.TableName, columnNames, model.Values, model.ChangeId.ToString(), model.EffectiveDate, model.Active));
}
//}
//else
//{
// }
}
public LookupTableRowModel GetLookupTableRow(string tableName, string tableId, string rowId)
{
var LookupTableRow = new LookupTableRowModel() { Active = true, ChangeId = Guid.NewGuid(), TableId = tableId, EffectiveDate = EffectiveDate };
var tableColumns = GetTableColumns(tableName);
var values = new string[tableColumns.Length];
RatingTableQuery(FlatTableQueries.SelectSingle(tableName,
GetTableColumns(tableName).Select(p => p.ColumnName).ToArray(),
rowId),
reader =>
{
if (reader.HasRows)
{
reader.Read(); // Read this first row
var index = 0;
LookupTableRow.RowId =
reader.GetInt64(index++); //.ToString();
LookupTableRow.ChangeId = reader.GetGuid(index++);
LookupTableRow.EffectiveDate =
reader.GetDateTime(index++);
//LookupTableRow.ExceptionId = reader.GetInt32(index++);
LookupTableRow.Active = reader.GetBoolean(index++);
for (var i = 0;
i < (reader.FieldCount - 4);
i++)
values[i] = reader.GetString(index++);
LookupTableRow.Values = values.ToArray();
}
});
LookupTableRow.TableName = tableName;
LookupTableRow.Values = values;
return LookupTableRow;
}
public void InsertLookupTableRowMultipleDatabases()
{
const string tableId = "6C84C211-032B-4531-A33B-A083B8A4406B";
var row = new LookupTableRowModel
{
TableId = tableId,
EffectiveDate = new DateTime(2013, 10, 1),
IsOverride = true,
Active = true
};
row.Values = new[]
{
"0", //rowId
"0", //sequence
Guid.Empty.ToString(), //changeId
row.EffectiveDate.ToString(CultureInfo.InvariantCulture), //effectiveDate
DateTime.MaxValue.ToString(CultureInfo.InvariantCulture), //expirationDate
"true", //active
"true", //override
"4,999", //keycol1
"BFS", //writingcompany
"0.4999" //valcol1
};
_multipleRepository.SaveLookupTableRow(row);
var lookupTable = _multipleRepository.GetLookupTable(tableId);
var insertedRow = lookupTable.Rows.Last();
Assert.IsTrue(insertedRow.KeyValues.First().LowValue == row.Values[7]);
Assert.IsTrue(insertedRow.ColumnValues["Factor"].ToString() == row.Values[9]);
const string tableId2 = "8abbb5c8-2a0b-49cc-9369-1ab20f745f4a";
row = new LookupTableRowModel
{
TableId = tableId2,
EffectiveDate = new DateTime(2014, 1, 1),
IsOverride = true,
Active = true
};
row.Values = new[]
{
"0",
"0",
Guid.Empty.ToString(),
row.EffectiveDate.ToString(CultureInfo.InvariantCulture),
DateTime.MaxValue.ToString(CultureInfo.InvariantCulture),
"true",
"true",
"999",
"5"
};
_multipleRepository.SaveLookupTableRow(row);
lookupTable = _multipleRepository.GetLookupTable(tableId2);
insertedRow = lookupTable.Rows.Last();
Assert.IsTrue(insertedRow.KeyValues.First().LowValue == row.Values[7]);
Assert.IsTrue(insertedRow.ColumnValues["Tier"].ToString() == row.Values[8]);
}
