static void AddParameters(NpgsqlCommand cmd, PostgresRow row)
{
foreach (var column in row.Columns)
{
var type = row.ColumnType(column);
var obj = row.GetReference <object> (column);
if (obj != null && type == NpgsqlTypes.NpgsqlDbType.Unknown && obj.GetType() == typeof(string[]))
{
type = NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Varchar;
}
var param = cmd.Parameters.Add(column, type);
if (obj == null)
{
param.Value = DBNull.Value;
}
else if (type == NpgsqlTypes.NpgsqlDbType.Jsonb)
{
param.Value = JsonConvert.SerializeObject(obj);
}
else
{
param.Value = obj;
}
}
}
static IEnumerable <PostgresRow> Select(NpgsqlConnection conn, string queryString, IEnumerable <string> columnsEnumerable, PostgresRow whereValues)
{
var columns = columnsEnumerable.ToArray();
using (var cmd = CreateCommand(conn)) {
cmd.CommandText = queryString;
if (whereValues != null)
{
AddParameters(cmd, whereValues);
}
var rows = new List <PostgresRow> ();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read())
{
var row = new PostgresRow();
for (var i = 0; i < columns.Length; ++i)
{
var value = reader.GetValue(i);
if (value.GetType() == typeof(DateTime))
{
value = (object)TimeZoneInfo.ConvertTimeToUtc((DateTime)value);
}
row.Set(columns [i], NpgsqlTypes.NpgsqlDbType.Unknown, value);
}
rows.Add(row);
}
}
return(rows);
}
}
public void TakeValuesFrom(PostgresRow row, string prefix)
{
foreach (var key in row.values.Keys)
{
if (key.StartsWith(prefix))
{
var value = row.values [key];
values.Add(key.Substring(prefix.Length), value);
}
}
}
public static void Update(NpgsqlConnection conn, string table, PostgresRow row, string keyColumn)
{
var columns = row.Columns;
var commandString = string.Format("update {0} set {1} where {2} = :{2}",
table,
string.Join(",", columns.Select(c => c + "=:" + c)),
keyColumn);
using (var cmd = CreateCommand(conn)) {
cmd.CommandText = commandString;
AddParameters(cmd, row);
var numRows = cmd.ExecuteNonQuery();
if (numRows != 1)
{
throw new Exception("Error: Could not update row in " + table);
}
}
}
public static T Insert <T> (NpgsqlConnection conn, string table, PostgresRow row, string keyColumn)
{
var columns = row.Columns;
var commandString = string.Format("insert into {0} ({1}) values ({2}) returning {3}",
table,
string.Join(",", columns),
string.Join(",", columns.Select(c => ":" + c)),
keyColumn);
using (var cmd = CreateCommand(conn)) {
cmd.CommandText = commandString;
AddParameters(cmd, row);
using (var reader = cmd.ExecuteReader()) {
if (!reader.Read())
{
throw new Exception("Error: Failure inserting into " + table);
}
return(reader.GetFieldValue <T> (0));
}
}
}
public static IEnumerable <PostgresRow> Select(NpgsqlConnection conn, IDictionary <string, string> tableNames, IEnumerable <string> columnsEnumerable, string whereClause, PostgresRow whereValues)
{
var tables = string.Join(",", tableNames.Select(kvp => kvp.Value == null ? kvp.Key : kvp.Key + " " + kvp.Value));
var columns = columnsEnumerable.ToArray();
var whereString = whereClause == null ? "" : string.Format("where {0}", whereClause);
var queryString = string.Format("select {0} from {1} {2}", string.Join(",", columns), tables, whereString);
return(Select(conn, queryString, columns, whereValues));
}
public static int Delete(NpgsqlConnection conn, string table, string whereClause, PostgresRow row)
{
var columns = row.Columns;
var commandString = string.Format("delete from {0} where {1}", table, whereClause);
using (var cmd = CreateCommand(conn)) {
cmd.CommandText = commandString;
AddParameters(cmd, row);
return(cmd.ExecuteNonQuery());
}
}
public static IEnumerable <PostgresRow> Select(NpgsqlConnection conn, string table, IEnumerable <string> columnsEnumerable, string whereClause = null, PostgresRow whereValues = null)
{
return(Select(conn, new Dictionary <string, string> {
[table] = null
}, columnsEnumerable, whereClause, whereValues));
}