/// <summary>
/// Execute an UPDATE query.
/// The updated rows are returned.
/// </summary>
/// <param name="tableName">The table in which you wish to UPDATE.</param>
/// <param name="keyValuePairs">The key-value pairs for the data you wish to UPDATE.</param>
/// <param name="filter">The expression containing the UPDATE filter (i.e. WHERE clause data).</param>
/// <returns>DataTable containing the updated rows.</returns>
public DataTable Update(string tableName, Dictionary <string, object> keyValuePairs, Expression filter)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairs == null || keyValuePairs.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
#region Build-Key-Value-Clause
string keyValueClause = "";
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (added == 0)
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += PgsqlHelper.PreparedFieldname(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += PgsqlHelper.PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += PgsqlHelper.PreparedFieldname(curr.Key) + "=" + PgsqlHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += PgsqlHelper.PreparedFieldname(curr.Key) + "=" + PgsqlHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += PgsqlHelper.PreparedFieldname(curr.Key) + "= null";
}
}
else
{
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
keyValueClause += "," + PgsqlHelper.PreparedFieldname(curr.Key) + "='" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
keyValueClause += "," + PgsqlHelper.PreparedFieldname(curr.Key) + "=" + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
keyValueClause += "," + PgsqlHelper.PreparedFieldname(curr.Key) + "=" + PgsqlHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
keyValueClause += "," + PgsqlHelper.PreparedFieldname(curr.Key) + "=" + PgsqlHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
keyValueClause += "," + PgsqlHelper.PreparedFieldname(curr.Key) + "= null";
}
}
added++;
}
#endregion
#region Build-UPDATE-Query-and-Submit
return(Query(PgsqlHelper.UpdateQuery(tableName, keyValueClause, filter)));
#endregion
}
/// <summary>
/// Execute an INSERT query.
/// </summary>
/// <param name="tableName">The table in which you wish to INSERT.</param>
/// <param name="keyValuePairs">The key-value pairs for the row you wish to INSERT.</param>
/// <returns>A DataTable containing the results.</returns>
public DataTable Insert(string tableName, Dictionary <string, object> keyValuePairs)
{
if (String.IsNullOrEmpty(tableName))
{
throw new ArgumentNullException(nameof(tableName));
}
if (keyValuePairs == null || keyValuePairs.Count < 1)
{
throw new ArgumentNullException(nameof(keyValuePairs));
}
#region Build-Key-Value-Pairs
string keys = "";
string values = "";
int added = 0;
foreach (KeyValuePair <string, object> curr in keyValuePairs)
{
if (String.IsNullOrEmpty(curr.Key))
{
continue;
}
if (added == 0)
{
#region First
keys += PgsqlHelper.PreparedFieldname(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += "'" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
values += curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
values += PgsqlHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += PgsqlHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += "null";
}
#endregion
}
else
{
#region Subsequent
keys += "," + PgsqlHelper.PreparedFieldname(curr.Key);
if (curr.Value != null)
{
if (curr.Value is DateTime || curr.Value is DateTime?)
{
values += ",'" + DbTimestamp((DateTime)curr.Value) + "'";
}
else if (curr.Value is int || curr.Value is long || curr.Value is decimal)
{
values += "," + curr.Value.ToString();
}
else
{
if (Helper.IsExtendedCharacters(curr.Value.ToString()))
{
values += "," + PgsqlHelper.PreparedUnicodeValue(curr.Value.ToString());
}
else
{
values += "," + PgsqlHelper.PreparedStringValue(curr.Value.ToString());
}
}
}
else
{
values += ",null";
}
#endregion
}
added++;
}
#endregion
#region Build-INSERT-Query-and-Submit
return(Query(PgsqlHelper.InsertQuery(tableName, keys, values)));
#endregion
}