//////////////////////////////////////////
public static DbDataColumns GetColumns(
this DbConnection Connection,
String TableName)
{
lock (_lck)
{
TableName = (TableName ?? "").Trim().ToUpper();
if (!_columnsCache.ContainsKey(TableName))
{
Connection.OpenIfClosed();
DbDataColumns columns = new DbDataColumns();
using (var lCommand = Connection.CreateCommand())
{
MyQuery query = new MyQuery();
query.Append("select column_name from information_schema.columns where table_name = {0}", TableName);
lCommand.CommandTimeout = 60 * 5;
lCommand.CommandText = query.ToString();
using (var lReader = lCommand.ExecuteReader())
{
while (lReader.Read())
{
string name = Convert.ToString(lReader.GetValue(0), CultureInfo.InvariantCulture);
columns[name] = new DbDataColumn()
{
Name = name,
};
}
}
}
_columnsCache[TableName] = columns;
}
return(_columnsCache[TableName]);
}
}
/// <summary>
/// wykonuje update
/// </summary>
public static String SqlGenerateSave(
this DbConnection Connection,
String TableName,
Object Item,
String PrimaryKey,
Boolean IsInsert,
String PostfixSql,
Boolean OverridePrimaryKey = false)
{
if (Connection != null)
{
if (String.IsNullOrEmpty(TableName))
{
throw new NotSupportedException("TableName sould not be empty!");
}
//TableName = getTableName(Item.GetType());
DbDataColumns databaseColumns = DatabaseCache.GetColumns(Connection, TableName);
MyQuery saveQuery = new MyQuery();
Object primaryKeyValue = GetPrimaryKeyValue(Item, PrimaryKey);
IList <DbDataColumnValue> valuesToSave = GetItemValuesForDbFields(Item, databaseColumns).ToArray();
if (valuesToSave.Count > 0)
{
if (IsInsert)
{
saveQuery.
Append(" insert into " + TableName + " ( ");
var lCount = 0;
foreach (DbDataColumnValue lValue in valuesToSave)
{
// dla sqlite
if (OverridePrimaryKey || !lValue.Name.EqualsNonsensitive(PrimaryKey))
{
if (lCount > 0)
{
saveQuery.Append(", ");
}
saveQuery.Append(lValue.Name);
lCount++;
}
}
saveQuery.
Append(" ) values ( ");
lCount = 0;
foreach (DbDataColumnValue lValue in valuesToSave)
{
if (OverridePrimaryKey || !lValue.Name.EqualsNonsensitive(PrimaryKey))
{
if (lCount > 0)
{
saveQuery.Append(", ");
}
saveQuery.AppendVal(lValue.Value);
lCount++;
}
// dla sqlite
/*else
* {
* if (lCount > 0) lQuery.Append(", ");
* lQuery.AppendVal(null);
* lCount++;
* }*/
}
saveQuery.
Append(" ) ");
}
else
{
saveQuery.Append(" update " + TableName + " set ");
var lCount = 0;
foreach (DbDataColumnValue lValue in valuesToSave)
{
if (!lValue.Name.EqualsNonsensitive(PrimaryKey))
{
if (lCount > 0)
{
saveQuery.Append(", ");
}
saveQuery.Append(lValue.Name).Append(" = ").AppendVal(lValue.Value);
lCount++;
}
}
saveQuery.Append(" where ").Append(PrimaryKey).Append(" = ").AppendVal(primaryKeyValue);
}
return(saveQuery.ToString() + (PostfixSql ?? ""));
}
else
{
throw new Exception("Nie można wykonać update!");
}
}
return(null);
}
private static IEnumerable <DbDataColumnValue> GetItemValuesForDbFields(Object Item, DbDataColumns DbFields)
{
if (Item is IDictionary <string, object> dictKeyValue)
{
foreach (var val in dictKeyValue)
{
DbDataColumn dataColumn = null;
DbFields.TryGetValue(val.Key, out dataColumn);
if (dataColumn == null)
{
continue;
}
yield return(new DbDataColumnValue()
{
Name = dataColumn.Name,
Value = val.Value
});
}
}
else if (Item is IDictionary dict)
{
foreach (var key in dict.Keys)
{
DbDataColumn dataColumn = null;
DbFields.TryGetValue(UniConvert.ToString(key), out dataColumn);
if (dataColumn == null)
{
continue;
}
yield return(new DbDataColumnValue()
{
Name = dataColumn.Name,
Value = dict[key]
});
}
}
else
{
foreach (DbDataColumn databaseColumn in DbFields.Values)
{
PropertyInfo itemProperty = ReflectionHelper.GetProperty(Item, databaseColumn.Name);
if (itemProperty != null)
{
yield return(new DbDataColumnValue()
{
Name = databaseColumn.Name,
Value = itemProperty.GetValue(Item, null)
});
}
}
}
}