public string AddParameter(string column, object value)
{
var par = new PgSqlParameter(column, value);
_Parameters.Add(par);
return(par.UniqueId);
}
internal static string GetValuePlaceholder(PgSqlParameter parameter)
{
if (parameter.Value == null)
{
return("DEFAULT");
}
var npgsqlDbType = parameter.Column.GetNpgSqlDbType();
if (npgsqlDbType == NpgsqlDbType.Text)
{
var str = parameter.Value as string;
if (str != null)
{
if (str.Equals("DEFAULT", StringComparison.OrdinalIgnoreCase))
{
return("'DEFAULT'");
}
}
}
if (parameter.Value is string _str && _str == "DEFAULT")
{
return("DEFAULT");
}
if (npgsqlDbType == NpgsqlDbType.Uuid)
{
if (!String.IsNullOrWhiteSpace(parameter.Column.DefaultValue))
{
if (parameter.Value.ToString() == Guid.Empty.ToString())
{
return("DEFAULT");
}
}
}
if (npgsqlDbType == NpgsqlDbType.Timestamp)
{
if (!String.IsNullOrWhiteSpace(parameter.Column.DefaultValue))
{
if ((DateTime)parameter.Value == default(DateTime))
{
return("DEFAULT");
}
}
}
if (!String.IsNullOrWhiteSpace(parameter.Column?.DefaultValue) && parameter.Value == null)
{
return("DEFAULT");
}
return($"@{parameter.UniqueId}");
}
internal static bool IsNotDefault(PgSqlParameter parameter)
{
var str = parameter.Value as string;
if (str != null)
{
return(!str.Equals("DEFAULT", StringComparison.OrdinalIgnoreCase));
}
return(true);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
if (ConflictAction == "THROW")
{
return(sqlCommand);
}
sqlCommand.AppendCommand($"ON CONFLICT");
if (ConflictAction == "DO NOTHING")
{
sqlCommand.AppendCommandLine(" DO NOTHING");
return(sqlCommand);
}
sqlCommand.AppendCommandLine($" ({String.Join(", ", tableDefinition.PrimaryKeys().Select(c => c.DbName))})");
sqlCommand.AppendCommandLine(" DO UPDATE SET");
if (_valueClauses.Any())
{
foreach (var valuesClause in _valueClauses)
{
if (valuesClause is NamedValues namedValues)
{
foreach (var namedValuesValue in namedValues._values)
{
var expr = new PgSqlParameter(namedValuesValue._key, namedValuesValue._value).SetColum(tableDefinition);
sqlCommand.AppendCommandLine($"{namedValuesValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>()
{
expr
});
}
}
else if (valuesClause is PositionedValues positionedValues)
{
foreach (var positionedValue in positionedValues._values)
{
var expr = new PgSqlParameter(positionedValue._key, positionedValue._value);
sqlCommand.AppendCommandLine($"{positionedValue._key} = {ValuesClauseHelper.GetValuePlaceholder(expr)}", new List <PgSqlParameter>()
{
expr
});
}
}
}
sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray()) + Environment.NewLine;
}
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
var values = _values.Select(val =>
{
var col = tableDefinition.GetColumnByClrName(val._key) ??
tableDefinition.GetColumnByDbName(val._key);
var expr = new PgSqlParameter(col.GetNameForDb(), val._value);
return(expr);
}).ToList();
var valuesIds = values.Select(v => v.UniqueId).ToList();
var sortedValues = new List <PgSqlParameter>();
if (tableDefinition != null)
{
foreach (var column in tableDefinition.Columns())
{
var exists = values.FirstOrDefault(v => v.ColumnName.Equals(column.GetNameForDb(), StringComparison.OrdinalIgnoreCase));
if (exists != null)
{
exists.Column = column;
sortedValues.Add(exists);
}
else
{
var defaultExpr = new PgSqlParameter(column.GetNameForDb(), "DEFAULT");
defaultExpr.Column = column;
sortedValues.Add(defaultExpr);
}
}
}
else
{
sortedValues = values;
}
var sortedValuesIds = sortedValues.Select(v => v.UniqueId).ToList();
if (_values.Any())
{
var parms = sortedValues.Where(ValuesClauseHelper.IsNotDefault);
var parmsIds = parms.Select(p => p.UniqueId);
var placeHolders = sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder);
sqlCommand.AppendCommand($"{String.Join(", ", placeHolders)}", parms);
}
return(sqlCommand);
}
/// Adds an object that is a query parameter and returns the name that got assigned to it.
public string AddParameter(object parameterValue, Column column = null)
{
var par = new PgSqlParameter(column?.DbName, parameterValue);
par.SetColum(column);
par.OverrideType = column?.PgType;
_Parameters.Add(par);
//var newParameterName = $"p{Parameters.Count + 1}";
//Parameters[newParameterName] = parameterValue;
return(par.UniqueId);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
_values.ForEach(v =>
{
if (tableDefinition == null || tableDefinition.GetColumnBuilderByDbName(v._key) != null)
{
var param = new PgSqlParameter(v._key, v._value).SetColum(tableDefinition);
sqlCommand.AppendCommandLine($"\"{param.ColumnName}\" = @{param.UniqueId},", new List <PgSqlParameter>()
{
param
});
}
});
sqlCommand.Command = sqlCommand.Command.TrimEnd($",{Environment.NewLine}".ToCharArray());
return(sqlCommand);
}
public PgSqlCommand GetSqlCommand(TableDefinition tableDefinition)
{
var sqlCommand = new PgSqlCommand();
var sortedValues = new List <PgSqlParameter>();
var columns = tableDefinition.Columns().ToList();
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
IValueItem valueItem = null;
if (_values.Count > i)
{
valueItem = _values[i];
}
if (valueItem == null)
{
var defaultExpr = new PgSqlParameter(i.ToString(), "DEFAULT");
sortedValues.Add(defaultExpr);
}
else
{
var expr = new PgSqlParameter(i.ToString(), valueItem._value);
sortedValues.Add(expr);
}
}
if (_values.Any())
{
sqlCommand.AppendCommand($"{String.Join(", ", sortedValues.Select(ValuesClauseHelper.GetValuePlaceholder))}", sortedValues.Where(ValuesClauseHelper.IsNotDefault));
}
return(sqlCommand);
}
public static void Main()
{
Console.WriteLine("** Start Test...");
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
Console.WriteLine("** Creating connection...");
con = new PgSqlConnection(connectionString);
Console.WriteLine("** opening connection...");
con.Open();
string tableName = "pg_type";
string sql;
sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";
Console.WriteLine("** Creating command...");
PgSqlCommand cmd = new PgSqlCommand(sql, con);
// add parameter for inTableName
Console.WriteLine("** Create parameter...");
PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);
Console.WriteLine("** set dbtype of parameter to string");
parm.DbType = DbType.String;
Console.WriteLine("** set direction of parameter to input");
parm.Direction = ParameterDirection.Input;
Console.WriteLine("** set value to the tableName string...");
parm.Value = tableName;
Console.WriteLine("** add parameter to parameters collection in the command...");
cmd.Parameters.Add(parm);
PgSqlDataReader rdr;
Console.WriteLine("** ExecuteReader()...");
rdr = cmd.ExecuteReader();
Console.WriteLine("[][] And now we are going to our results [][]...");
int c;
int results = 0;
do
{
results++;
Console.WriteLine("Result Set " + results + "...");
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows)
{
foreach (DataColumn schemaCol in dt.Columns)
{
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
}
Console.WriteLine();
}
string output, metadataValue, dataValue;
int nRows = 0;
// Read and display the rows
while (rdr.Read())
{
Console.WriteLine(" Row " + nRows + ": ");
for (c = 0; c < rdr.FieldCount; c++)
{
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if (rdr.IsDBNull(c) == true)
{
dataValue = " is NULL";
}
else
{
dataValue =
": " +
rdr.GetValue(c);
}
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
con.Close();
}
public static void Main() {
Console.WriteLine("** Start Test...");
String connectionString = null;
connectionString =
"host=localhost;" +
"dbname=test;" +
"user=postgres";
PgSqlConnection con;
Console.WriteLine("** Creating connection...");
con = new PgSqlConnection(connectionString);
Console.WriteLine("** opening connection...");
con.Open();
string tableName = "pg_type";
string sql;
sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";
Console.WriteLine("** Creating command...");
PgSqlCommand cmd = new PgSqlCommand(sql, con);
// add parameter for inTableName
Console.WriteLine("** Create parameter...");
PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);
Console.WriteLine("** set dbtype of parameter to string");
parm.DbType = DbType.String;
Console.WriteLine("** set direction of parameter to input");
parm.Direction = ParameterDirection.Input;
Console.WriteLine("** set value to the tableName string...");
parm.Value = tableName;
Console.WriteLine("** add parameter to parameters collection in the command...");
cmd.Parameters.Add(parm);
PgSqlDataReader rdr;
Console.WriteLine("** ExecuteReader()...");
rdr = cmd.ExecuteReader();
Console.WriteLine("[][] And now we are going to our results [][]...");
int c;
int results = 0;
do {
results++;
Console.WriteLine("Result Set " + results + "...");
// get the DataTable that holds
// the schema
DataTable dt = rdr.GetSchemaTable();
// number of columns in the table
Console.WriteLine(" Total Columns: " +
dt.Columns.Count);
// display the schema
foreach (DataRow schemaRow in dt.Rows) {
foreach (DataColumn schemaCol in dt.Columns)
Console.WriteLine(schemaCol.ColumnName +
" = " +
schemaRow[schemaCol]);
Console.WriteLine();
}
string output, metadataValue, dataValue;
int nRows = 0;
// Read and display the rows
while(rdr.Read()) {
Console.WriteLine(" Row " + nRows + ": ");
for(c = 0; c < rdr.FieldCount; c++) {
// column meta data
DataRow dr = dt.Rows[c];
metadataValue =
" Col " +
c + ": " +
dr["ColumnName"];
// column data
if(rdr.IsDBNull(c) == true)
dataValue = " is NULL";
else
dataValue =
": " +
rdr.GetValue(c);
// display column meta data and data
output = metadataValue + dataValue;
Console.WriteLine(output);
}
nRows++;
}
Console.WriteLine(" Total Rows: " +
nRows);
} while(rdr.NextResult());
Console.WriteLine("Total Result sets: " + results);
con.Close();
}
public static NpgsqlParameter ToNpgsqlParameter(this PgSqlParameter param)
{
try
{
if (param.Value == null)
{
return(new NpgsqlParameter(param.UniqueId, DBNull.Value));
}
//if (Value.GetType().GetTypeInfo().IsEnum)
// Value = Json.ToJson(Value);
NpgsqlDbType type = NpgsqlDbType.Text;
bool findType = true;
if (param.Column?.DotNetType?.IsEnum == true)
{
switch (param.Value)
{
case int i:
{
var val = (Enum)Enum.ToObject(param.Column.DotNetType, i);
return(new NpgsqlParameter(param.UniqueId, type)
{
Value = val.GetName()
});
}
}
}
//var typeInfo = param.Value.GetType().GetTypeInfo();
//if (typeInfo.IsEnum)
//{
// type = NpgsqlDbType.Text;
// findType = false;
//}
if (findType)
{
if (!Enum.TryParse(param.OverrideType, true, out type))
{
type = param.Column.GetNpgSqlDbType();// PgSqlTypeManager.GetNpgsqlDbType(param.Column.DotNetType);
}
}
if (type == NpgsqlDbType.Json || type == NpgsqlDbType.Jsonb)
{
var valueType = PgSqlTypeManager.GetNpgsqlDbType(param.Value.GetType());
if (valueType != NpgsqlDbType.Json && valueType != NpgsqlDbType.Jsonb)
{
type = NpgsqlDbType.Text; //PgSqlTypeManager.GetNpgsqlDbType(param.Value.GetType());
}
}
object _value = null;
switch (type)
{
case NpgsqlDbType.Enum:
{
//TODO: Error: When specifying NpgsqlDbType.Enum, EnumType must be specified as well
type = NpgsqlDbType.Text;
_value = JSON.ToJson(param.Value);
break;
}
case NpgsqlDbType.Jsonb:
{
_value = JSON.ToJson(param.Value);
break;
}
case NpgsqlDbType.Array | NpgsqlDbType.Jsonb:
{
var objAr = new List <object>();
if (param.Value is string)
{
objAr.Add(param.Value.ToString());
}
else
{
var arr = param.Value as IEnumerable;
if (arr != null)
{
foreach (var o in arr)
{
objAr.Add(JSON.ToJson(o));
}
}
}
_value = objAr;
break;
}
case NpgsqlDbType.Array | NpgsqlDbType.Uuid:
{
var json = JSON.ToJson(param.Value);
_value = JSON.ToObject <List <Guid> >(json);
break;
}
case NpgsqlDbType.Uuid:
{
_value = new Guid(param.Value.ToString());
break;
}
default:
{
_value = param.Value;
break;
}
}
return(new NpgsqlParameter(param.UniqueId, type)
{
Value = _value
});
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
