public void TestNoWhere()
{
var msg = new UpdateValuesMessage();
var ex = Assert.Throws <Exception>(msg.Validate);
Assert.AreEqual("There must be at least one search field for WHERE section. Otherwise this would update entire tables", ex.Message);
}
public void TestNoWhereValue()
{
var msg = new UpdateValuesMessage();
msg.WhereFields = new string[] { "ff" };
var ex = Assert.Throws <Exception>(msg.Validate);
Assert.AreEqual("WhereFields length must match HaveValues length", ex.Message);
}
public void Test_GoodMessage()
{
var msg = new UpdateValuesMessage();
msg.WhereFields = new string[] { "ff" };
msg.HaveValues = new string[] { null }; //where column ff has a null value
msg.WriteIntoFields = new string[] { "ff" };
msg.Values = new string[] { "ddd" }; //write the value ddd
msg.Validate();
}
public void TestNoSet()
{
var msg = new UpdateValuesMessage();
msg.WhereFields = new string[] { "ff" };
msg.HaveValues = new string[] { null }; //where column ff has a null value
var ex = Assert.Throws <Exception>(msg.Validate);
Assert.AreEqual("There must be at least one value to write", ex.Message);
}
public void TestEquality()
{
var m1 = new UpdateValuesMessage();
var m2 = new UpdateValuesMessage();
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
m1.WhereFields = new [] { "fff" };
Assert.AreNotEqual(m1, m2);
m2.WhereFields = new [] { "fff" };
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
m1.WhereFields = null;
m2.WhereFields = null;
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
foreach (var prop in typeof(UpdateValuesMessage).GetProperties())
{
if (prop.Name.Equals(nameof(UpdateValuesMessage.ExplicitTableInfo)))
{
prop.SetValue(m1, new int[] { 6 });
Assert.AreNotEqual(m1, m2);
prop.SetValue(m2, new int[] { 6 });
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
prop.SetValue(m2, new int[] { 7 });
Assert.AreNotEqual(m1, m2);
prop.SetValue(m2, new int[] { 6 });
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
}
else
{
prop.SetValue(m1, new string[] { "ss" });
Assert.AreNotEqual(m1, m2);
prop.SetValue(m2, new string[] { "ss" });
Assert.AreEqual(m1, m2);
Assert.AreEqual(m1.GetHashCode(), m2.GetHashCode());
}
}
}
public void TestNoSetValue()
{
var msg = new UpdateValuesMessage();
msg.WhereFields = new string[] { "ff" };
msg.HaveValues = new string[] { null }; //where column ff has a null value
msg.WriteIntoFields = new string[] { "ff" };
var ex = Assert.Throws <Exception>(msg.Validate);
Assert.AreEqual("WriteIntoFields length must match Values length", ex.Message);
}
public void TestTwoValuesOneOperator()
{
var msg = new UpdateValuesMessage();
msg.WhereFields = new string[] { "ff", "mm" };
msg.HaveValues = new string[] { "111", "123" };
msg.Operators = new string[] { "=" };
msg.WriteIntoFields = new string[] { "ff" };
msg.Values = new string[] { "ff" };
var ex = Assert.Throws <Exception>(msg.Validate);
Assert.AreEqual("WhereFields length must match Operators length", ex.Message);
}
public int HandleUpdate(UpdateValuesMessage message)
{
message.Validate();
ITableInfo[] tables;
int affectedRows = 0;
if (message.ExplicitTableInfo != null && message.ExplicitTableInfo.Length != 0)
{
tables = _repository.GetAllObjectsInIDList(typeof(TableInfo), message.ExplicitTableInfo).Cast <ITableInfo>().ToArray();
if (tables.Length != message.ExplicitTableInfo.Length)
{
throw new Exception($"Could not find all TableInfos IDs={string.Join(",",message.ExplicitTableInfo)}. Found {tables.Length}:{string.Join(",",tables.Select(t=>t.ID))}");
}
}
else
{
tables = GetAllTables(message.WhereFields.Union(message.WriteIntoFields).ToArray()).ToArray();
if (tables.Length == 0)
{
throw new Exception($"Could not find any tables to update that matched the field set {message}");
}
}
//TODO: Expose IsView in ITableInfo in RDMP so we don't need this cast
//don't try to update views
tables = tables.Where(t => !((TableInfo)t).IsView).ToArray();
foreach (var t in tables)
{
var tbl = t.Discover(ReusableLibraryCode.DataAccess.DataAccessContext.DataLoad);
if (!tbl.Exists())
{
throw new Exception($"Table {tbl} did not exist");
}
affectedRows += UpdateTable(tbl, message);
}
return(affectedRows);
}
/// <summary>
/// Generates and runs an SQL command on <paramref name="t"/>
/// </summary>
/// <param name="t"></param>
/// <param name="message"></param>
protected virtual int UpdateTable(DiscoveredTable t, UpdateValuesMessage message)
{
var audit = _audits.GetOrAdd(t, (k) => new UpdateTableAudit(k));
StringBuilder builder = new StringBuilder();
builder.AppendLine("UPDATE ");
builder.AppendLine(t.GetFullyQualifiedName());
builder.AppendLine(" SET ");
for (int i = 0; i < message.WriteIntoFields.Length; i++)
{
var col = t.DiscoverColumn(message.WriteIntoFields[i]);
builder.Append(GetFieldEqualsValueExpression(col, message.Values[i], "="));
//if there are more SET fields to come
if (i < message.WriteIntoFields.Length - 1)
{
builder.AppendLine(",");
}
}
builder.AppendLine(" WHERE ");
for (int i = 0; i < message.WhereFields.Length; i++)
{
var col = t.DiscoverColumn(message.WhereFields[i]);
builder.Append(GetFieldEqualsValueExpression(col, message.HaveValues[i], message?.Operators?[i]));
//if there are more WHERE fields to come
if (i < message.WhereFields.Length - 1)
{
builder.AppendLine(" AND ");
}
}
var sql = builder.ToString();
int affectedRows = 0;
audit.StartOne();
try
{
using (var con = t.Database.Server.GetConnection())
{
con.Open();
var cmd = t.Database.Server.GetCommand(sql, con);
cmd.CommandTimeout = UpdateTimeout;
try
{
return(affectedRows = cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw new Exception($"Failed to excute query {sql} ", ex);
}
}
}
finally
{
audit.EndOne(affectedRows < 0 ? 0 : affectedRows);
}
}
