public static bool FoxProSeek(this AdsExtendedReader rdr, IFoxProEntity entity)
{
rdr.ActiveIndex = entity.Key;
var found = rdr.Seek(entity.KeyValue, AdsExtendedReader.SeekType.HardSeek);
return(found);
}
public bool Delete(IFoxProEntity entity)
{
var qTime = DateTime.Now;
var did = false;
var table = entity.GetType().Name;
Conn.Open();
var cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = table;
var rdr = cmd.ExecuteExtendedReader();
if (rdr.FoxProSeek(entity))
{
rdr.DeleteRecord();
did = rdr.IsRecordDeleted();
}
else
{
Debug.WriteLine($"ADS Delete did not find: table {table} keyvalue {entity.KeyValue}");
// write didn't find keyvalue record message
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"Delete - {table}");
return(did);
}
public static string GetTableName(this IFoxProEntity entity)
{
var table = entity.GetType().Name;
//if (table.StartsWith("h")) table = $"x{table.Remove(0, 1)}";
return(table);
}
public static string BuildSelectSql(this IFoxProEntity entity, object keyValue)
{
var where = "";
var props = keyValue.GetType().GetProperties();
foreach (var prop in props)
{
if (!string.IsNullOrEmpty(where))
{
where = $"{where} and";
}
var q = prop.PropertyType.FullName.Contains("String") ? "'" : "";
var propValue = prop.GetValue(keyValue);
where = $"{where} [{prop.Name}] = {q}{propValue}{q}";
}
var sql = $"select top 1 * from {entity.GetTableName()} where {where}";
return(sql);
}
public static string BuildInsertSql(this IFoxProEntity obj)
{
var sb = new StringBuilder();
var sb1 = new StringBuilder();
var sb2 = new StringBuilder();
foreach (var prop in obj.GetType().GetProperties())
{
var att = prop.GetAttribute <MyCustomAttribute>();
if (att != null && (att.AdsIgnore || att.AdsReadOnly))
{
continue;
}
var value = prop.GetValue(obj, null);
if (value == null)
{
continue;
}
var isDate = prop.PropertyType.FullName != null && prop.PropertyType.FullName.Contains("DateTime");
if (isDate)
{
value = ((DateTime)value).ToShortDateString();
}
var q = prop.PropertyType.Name.Equals("String") || isDate ? "'" : "";
sb1.Append($"[{prop.Name}], ");
sb2.Append($"{q}{value}{q}, ");
}
if (sb1.ToString().EndsWith(", "))
{
sb1.Length = sb1.Length - 2;
}
if (sb2.ToString().EndsWith(", "))
{
sb2.Length = sb2.Length - 2;
}
var table = obj.GetType().Name;
//if (table.StartsWith("h")) table = $"x{table.Remove(0, 1)}";
sb.Append($"insert into {table} ({sb1}) values ({sb2})");
return(sb.ToString());
}
public static string BuildMultiSelectSql(this IFoxProEntity entity, string whereValue)
{
return($"select * from {entity.GetTableName()} where {whereValue}");
}
public static string BuildSelectSql(this IFoxProEntity entity, string keyValue)
{
return($"select top 1 * from {entity.GetTableName()} where [{entity.Key}]='{keyValue}'");
}
public bool Update(IFoxProEntity entity)
{
var qTime = DateTime.Now;
entity.PrepareSave();
var did = false;
var table = entity.GetType().Name;
//if (table == "hfinv") table = "xfinv";
//if (table == "hfinvbm") table = "xfinvbm";
//if (table == "hbmnote") table = "xhbmnote";
//if (table == "hord") table = "xord";
//if (table == "hdet") table = "xdet";
Conn.Open();
var cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = table;
var rdr = cmd.ExecuteExtendedReader();
if (rdr.FoxProSeek(entity))
{
if (rdr.FoxRecordLock(table))
{
try {
foreach (var prop in entity.GetType().GetProperties())
{
var att = prop.GetAttribute <MyCustomAttribute>();
if (att != null && (att.AdsIgnore || att.AdsReadOnly))
{
continue;
}
if (!entity.DupDirtyList.Contains(prop.Name))
{
continue;
}
var value = prop.GetValue(entity, null);
//if (value == null) continue;
var ordinal = rdr.GetOrdinal(prop.Name);
switch (prop.GetFoxType())
{
case FoxType.DateTime:
rdr.SetDateTime(ordinal, Convert.ToDateTime(value));
break;
case FoxType.Boolean:
rdr.SetBoolean(ordinal, Convert.ToBoolean(value));
break;
case FoxType.Decimal:
rdr.SetDecimal(ordinal, Convert.ToDecimal(value));
break;
case FoxType.Int32:
rdr.SetInt32(ordinal, Convert.ToInt32(value));
break;
default:
var str = value.ToString();
if (att != null && att.Mask != null && att.Mask.Contains("###-###-####") && !str.Contains("-") && str.Length == 10)
{
str = $"{str.Substring(0, 3)}-{str.Substring(3, 3)}-{str.Substring(6, 4)}";
}
rdr.SetString(ordinal, str);
break;
}
}
rdr.WriteRecord();
rdr.UnlockRecord();
did = true;
}
catch (Exception e) {
// Undo changes, data was not written
// write update failure message
if (rdr.IsRecordLocked())
{
//rdr.WriteRecord();
rdr.UnlockRecord();
}
Debug.WriteLine($"ADS Update Error: {e.Message}");
}
}
else
{
// Record could not be locked, data was not written
// write failure message somewhere
if (rdr.IsRecordLocked())
{
rdr.UnlockRecord();
}
Debug.WriteLine($"ADS Update Error: lock failed!");
}
}
else
{
// write didn't find keyvalue record message
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"Update - {table}");
return(did);
}
public int Insert(IFoxProEntity entity, string table)
{
var qTime = DateTime.Now;
var recno = 0;
if (string.IsNullOrEmpty(table))
{
table = entity.GetType().Name;
}
Conn.Open();
var cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = table;
var rdr = cmd.ExecuteExtendedReader();
if (rdr.FoxTableLock(table))
{
try {
rdr.AppendRecord();
foreach (var prop in entity.GetType().GetProperties())
{
var att = prop.GetAttribute <MyCustomAttribute>();
if (att != null && att.AdsIgnore)
{
continue;
}
var value = prop.GetValue(entity, null);
if (value == null)
{
continue;
}
var ordinal = rdr.GetOrdinal(prop.Name);
switch (prop.GetFoxType())
{
case FoxType.DateTime:
rdr.SetDateTime(ordinal, Convert.ToDateTime(value));
break;
case FoxType.Boolean:
rdr.SetBoolean(ordinal, Convert.ToBoolean(value));
break;
case FoxType.Decimal:
rdr.SetDecimal(ordinal, Convert.ToDecimal(value));
break;
case FoxType.Int32:
rdr.SetInt32(ordinal, Convert.ToInt32(value));
break;
default:
var str = value.ToString();
if (att != null && att.Mask != null && att.Mask.Contains("###-###-####") && !str.Contains("-") && str.Length == 10)
{
str = $"{str.Substring(0, 3)}-{str.Substring(3, 3)}-{str.Substring(6, 4)}";
}
rdr.SetString(ordinal, str);
break;
}
}
rdr.WriteRecord();
rdr.UnlockTable();
recno = rdr.RecordNumber;
entity.MakeClean();
}
catch (Exception e) {
if (rdr.IsTableLocked())
{
//rdr.WriteRecord();
rdr.UnlockTable();
}
recno = 0;
Debug.WriteLine($"ADS Insert Error: {e.Message}");
}
}
else
{
if (rdr.IsTableLocked())
{
rdr.UnlockTable();
}
recno = 0;
Debug.WriteLine($"ADS Insert Error: lock failed!");
// write failure message somewhere
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"Insert - {table}");
return(recno);
}
public int Insert(IFoxProEntity entity)
{
return(Insert(entity, ""));
}
