public static void Update(this Recordset recordset, SboUserDefinedTableDefinition record)
{
var tableName = record.GetType().GetCustomAttributes(typeof(SboUserDefinedTableAttribute), true).Select(a => ((SboUserDefinedTableAttribute)a).Name).FirstOrDefault();
var fieldValues = GetUserDefinedTableDefinitionSqlValues(record);
var updateValues = fieldValues.Where(fv => !fv.Key.Equals("Code")).Select(fv => $"\"{fv.Key}\" = {fv.Value}");
var query = String.Format(@"UPDATE ""@{0}"" SET {1} WHERE ""Code"" = {2}", tableName, String.Join(",", updateValues), fieldValues["Code"]);
Logger.Trace("Updating UDT: {0}", query);
recordset.DoQuery(query);
}
public static void Replace(this Recordset recordset, SboUserDefinedTableDefinition record)
{
var tableName = record.GetType().GetCustomAttributes(typeof(SboUserDefinedTableAttribute), true).Select(a => ((SboUserDefinedTableAttribute)a).Name).FirstOrDefault();
if (recordset.DoQueryValue <int>(@"SELECT COUNT(*) FROM ""@{0}"" WHERE ""Code"" = '{1}'", tableName, record.Code) > 0)
{
Update(recordset, record);
}
else
{
Insert(recordset, record);
}
}
public static void Insert(this Recordset recordset, SboUserDefinedTableDefinition record)
{
var tableName = record.GetType().GetCustomAttributes(typeof(SboUserDefinedTableAttribute), true).Select(a => ((SboUserDefinedTableAttribute)a).Name).FirstOrDefault();
if (String.IsNullOrEmpty(record.Code))
{
record.Code = SboUserDefinedDataManager.GetNextTableCode(tableName);
}
if (String.IsNullOrEmpty(record.Name))
{
record.Name = record.Code;
}
var fieldValues = GetUserDefinedTableDefinitionSqlValues(record);
var query = String.Format(@"INSERT INTO ""@{0}"" ({1}) VALUES ({2})", tableName, String.Join(",", fieldValues.Keys.Select(k => $"\"{k}\"")), String.Join(",", fieldValues.Values));
Logger.Trace("Inserting into UDT: {0}", query);
recordset.DoQuery(query);
}
private static Dictionary <string, string> GetUserDefinedTableDefinitionSqlValues(SboUserDefinedTableDefinition record)
{
var sqlFieldValues = new Dictionary <string, string>();
foreach (var property in record.GetType().GetProperties())
{
var udf = (SboUserDefinedFieldAttribute)property.GetCustomAttributes(false).FirstOrDefault(a => a.GetType() == typeof(SboUserDefinedFieldAttribute));
if (udf == null && !(new [] { "Code", "Name" }).Contains(property.Name))
{
continue;
}
var sqlFieldName = (new[] { "Code", "Name" }).Contains(property.Name)
? property.Name
: "U_" + (udf != null ? udf.Name ?? property.Name : property.Name);
var val = property.GetMethod.Invoke(record, null);
if (val is string)
{
val = ((string)val).Replace("'", "''");
}
string strVal = String.Empty;
var propertyType = Nullable.GetUnderlyingType(property.GetMethod.ReturnType) ?? property.GetMethod.ReturnType;
if (val == null)
{
strVal = "NULL";
}
else if (propertyType == typeof(bool))
{
strVal = String.Format(CultureInfo.InvariantCulture, "'{0}'", ((bool)val) ? "Y" : "N");
}
else if (propertyType == typeof(string))
{
strVal = String.Format(CultureInfo.InvariantCulture, "'{0}'", val);
}
else if (propertyType == typeof(short))
{
strVal = String.Format(CultureInfo.InvariantCulture, "{0}", val);
}
else if (propertyType == typeof(int))
{
strVal = String.Format(CultureInfo.InvariantCulture, "{0}", val);
}
else if (propertyType == typeof(long))
{
strVal = String.Format(CultureInfo.InvariantCulture, "{0}", val);
}
else if (propertyType == typeof(float))
{
strVal = String.Format(CultureInfo.InvariantCulture, "{0:R}", val);
}
else if (propertyType == typeof(double))
{
strVal = String.Format(CultureInfo.InvariantCulture, "{0:R}", val);
}
else if (propertyType == typeof(DateTime) && BoFldSubTypes.st_Time.Equals(udf?.SubType))
{
strVal = String.Format(CultureInfo.InvariantCulture, "'{0:HHmm}'", val);
}
else if (propertyType == typeof(DateTime))
{
strVal = String.Format(CultureInfo.InvariantCulture, "'{0:yyyy-MM-dd'T'HH':'mm':'ss.fff}'", val);
}
else if (propertyType.IsEnum)
{
strVal = String.Format(CultureInfo.InvariantCulture, "'{0}'", Enum.GetName(val.GetType(), val));
}
else
{
throw new Exception(string.Format("Unknown data type for property {0}", property.Name));
}
sqlFieldValues.Add(sqlFieldName, strVal);
}
return(sqlFieldValues);
}
private static Dictionary <string, Type> GetUserDefinedTableDefinitionSqlColumns(SboUserDefinedTableDefinition record)
{
var sqlColumns = new Dictionary <string, Type>();
foreach (var property in record.GetType().GetProperties())
{
var udf =
(SboUserDefinedFieldAttribute)
property.GetCustomAttributes(false).FirstOrDefault(a => a.GetType() == typeof(SboUserDefinedFieldAttribute));
if (udf == null && !(new[] { "Code", "Name" }).Contains(property.Name))
{
continue;
}
var sqlFieldName = (new[] { "Code", "Name" }).Contains(property.Name)
? property.Name
: "U_" + (udf != null ? udf.Name ?? property.Name : property.Name);
var propertyType = Nullable.GetUnderlyingType(property.GetMethod.ReturnType) ?? property.GetMethod.ReturnType;
sqlColumns.Add(sqlFieldName, propertyType);
}
return(sqlColumns);
}
public static void ReplaceRecord(SboUserDefinedTableDefinition record)
{
using (var f = new SboDisposableBusinessObjectFactory())
f.Create <Recordset>(BoObjectTypes.BoRecordset).Replace(record);
}
