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 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); }