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; } }