public static bool SaveOnJSON(NpgsqlDataReader reader, string schema, string tableName, string path)
{
try {
JObject table = new JObject();
List <JObject> metrics = new List <JObject>();
while (reader.Read())
{
JObject metric = new JObject();
for (int i = 0; i < reader.FieldCount; i++)
{
metric[$"{reader.GetName(i)}"] = reader[i].ToString();
}
metrics.Add(metric);
}
table[tableName] = new JArray()
{
metrics
};
using (StreamWriter file = File.CreateText($"./metrics/{path}/{schema}_{tableName}.json")) {
using (JsonTextWriter writer = new JsonTextWriter(file)) {
table.WriteTo(writer);
}
}
Console.WriteLine($"Success to export {schema}.{tableName} to JSON");
return(true);
} catch (Exception) {
Console.WriteLine($"**Failing** to export {schema}.{tableName} to JSON");
return(false);
}
}
private static List <TEntity> MapQueryToEntities(NpgsqlDataReader reader)
{
var result = new List <TEntity>();
List <TableColumn> columns = MetadataResolver.TableColumns <TEntity>();
Dictionary <string, TableColumn> columnMap = columns.ToDictionary(i => i.Name, i => i);
while (reader.Read())
{
var entity = new TEntity();
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
if (columnMap.TryGetValue(columnName, out TableColumn column))
{
object columnValue = reader.GetValue(i);
column.SetValue(entity, columnValue);
}
else
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine($"Warning: Failed to map db column '{columnName}' a TableColumn on entity '{typeof(TEntity)}' (did the schema change recently? is a db upgrade required?)");
Console.ResetColor();
}
}
result.Add(entity);
}
return(result);
}
public static List <NameValueCollection> ExecuteSQL(string sql)
{
NpgsqlConnection npgsqlConnection = GetNpgSqlConnection();
List <NameValueCollection> result = new List <NameValueCollection>();
lock (npgsqlConnection) {
Console.WriteLine($"{DateTime.Now} : {sql}");
//base.ExecuteQuery(sql);
try {
// Execute a query
NpgsqlCommand command = new NpgsqlCommand(sql, npgsqlConnection);
NpgsqlDataReader sqlResult = command.ExecuteReader();
while (sqlResult.Read())
{
NameValueCollection c_result = new NameValueCollection();
for (int i = 0; i < sqlResult.FieldCount; i++)
{
c_result[sqlResult.GetName(i)] = sqlResult[i].ToString();
}
result.Add(c_result);
}
sqlResult.Close();
} catch (Exception e) {
Console.WriteLine(e.Message);
}
}
return(result);
}
public static DataColumn[] GetColumns(NpgsqlDataReader reader)
{
//reader.GetColumnSchema() на алиасы колонок в запросе забивает почему-то
//reader.GetSchemaTable() какую-то хрень в ColumnSize возвращает
var npgsqlColumns = reader.GetColumnSchema();
var result = new DataColumn[npgsqlColumns.Count];
for (var i = 0; i < result.Length; i++)
{
var c = npgsqlColumns[i];
var columnName = reader.GetName(i);
if (string.IsNullOrEmpty(columnName) || columnName == "?column?")
{
columnName = "col_" + i;
}
result[i] = new DataColumn
{
ColumnName = columnName,
AllowDBNull = true,
DataType = c.DataType,
MaxLength = c.ColumnSize.GetValueOrDefault(-1)
};
}
return(result);
}
private static Result OnQuery()
{
var columns = new List <string>();
var rows = new List <string[]>();
for (var index = 0; index < reader.FieldCount; index++)
{
columns.Add(reader.GetName(index));
}
while (reader.Read())
{
var row = new string[reader.FieldCount];
for (var index = 0; index < reader.FieldCount; index++)
{
object value = null;
if (!reader.IsDBNull(index))
{
value = reader.GetValue(index);
}
if (value == null)
{
value = "";
}
row[index] = value.ToString();
}
rows.Add(row);
}
return(End(new Result {
Success = true, Columns = columns.ToArray(), Rows = rows.ToArray()
}));
}
/// <summary>
/// Fill
/// </summary>
/// <param name="ds"></param>
public void Fill(DataSet ds)
{
if (ds == null)
{
ds = new DataSet();
}
using (NpgsqlDataReader dr = command.ExecuteReader())
{
do
{
var dt = new DataTable();
var columns = dt.Columns;
var rows = dt.Rows;
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i).Trim();
if (!columns.Contains(name))
{
columns.Add(new DataColumn(name, dr.GetFieldType(i)));
}
}
while (dr.Read())
{
DataRow daRow = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
daRow[columns[i].ColumnName] = dr.GetValue(i);
}
dt.Rows.Add(daRow);
}
ds.Tables.Add(dt);
} while (dr.NextResult());
}
}
private void Search()
{
try
{
NpgsqlDataReader dataReader = PgSql.GetContactReport();
dataGridView.RowCount = 0;
dataGridView.ColumnCount = dataReader.FieldCount;
int row = 0;
while (dataReader.Read())
{
int cell = 0;
dataGridView.RowCount++;
for (int i = 0; i < dataReader.FieldCount; i++)
{
dataGridView.Columns[i].HeaderText = dataReader.GetName(i);
dataGridView.Rows[row].Cells[cell].Value = dataReader[i].ToString();
cell++;
}
dataGridView.Rows[row].HeaderCell.Value = (row + 1).ToString();
row++;
}
dataReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public Dictionary <string, string> ExecuteQueryGetSingleRow(string commandText,
Dictionary <string, object> parameters)
{
Dictionary <string, string> row = null;
if (string.IsNullOrEmpty(commandText))
{
throw new ArgumentException("Command text cannot be null or empty.");
}
using (var conn = CreateConnection())
{
using (var command = CreateCommand(conn, commandText, parameters))
{
using (NpgsqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
row = new Dictionary <string, string>();
for (var i = 0; i < reader.FieldCount; i++)
{
var columnName = reader.GetName(i);
var columnValue = reader.IsDBNull(i) ? null : reader.GetValue(i).ToString();
row.Add(columnName, columnValue);
}
break;
}
}
}
}
return(row);
}
static public void ReaderSeatRow(NpgsqlConnection con)
{
Console.WriteLine("\n");
Console.WriteLine("Seat/Row");
Console.WriteLine("--------------------------------------");
var sql = $"select * from \"Seat/Row\"";
var cmd = new NpgsqlCommand(sql, con);
NpgsqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine($"{rdr.GetName(0),-4}\t {rdr.GetName(1),-7} \t {rdr.GetName(2),-15}");
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} \t {rdr.GetInt32(1),-7} \t{rdr.GetInt32(2),-15} ");
}
rdr.Close();
}
public void Write <R>(IDataOutput <R> o) where R : IDataOutput <R>
{
int count = reader.FieldCount;
for (int i = 0; i < count; i++)
{
string name = reader.GetName(i);
uint oid = reader.GetDataTypeOID(i);
if (reader.IsDBNull(i))
{
o.PutNull(name);
continue;
}
if (oid == 1043 || oid == 1042)
{
o.Put(name, reader.GetString(i));
}
else if (oid == 790) // money
{
o.Put(name, reader.GetDecimal(i));
}
}
}
/// <summary>
/// Executa Npgsql query retornando lista de linhas.
/// </summary>
/// <param name="strComando">Npgsql query a ser executada.</param>
/// <param name="dicParametros">Parametros opcionais da query.</param>
/// <returns>Lista tipo Dictionary of Key, par de valores representando
/// nome da coluna e o valor correspondente.</returns>
public List <Dictionary <string, string> > Query(string strComando, Dictionary <string, object> dicParametros)
{
List <Dictionary <string, string> > ldicLinhas = null;
if (string.IsNullOrEmpty(strComando))
{
throw new ArgumentException("Comando não pode ser Null ou Branco.");
}
try
{
AbrirConexao();
var vComando = CreateCommand(strComando, dicParametros);
using (NpgsqlDataReader npgsqlReader = vComando.ExecuteReader())
{
ldicLinhas = new List <Dictionary <string, string> >();
while (npgsqlReader.Read())
{
var vLinha = new Dictionary <string, string>();
for (var i = 0; i < npgsqlReader.FieldCount; i++)
{
var columnName = npgsqlReader.GetName(i);
var columnValue = npgsqlReader.IsDBNull(i) ? null : npgsqlReader.GetString(i);
vLinha.Add(columnName, columnValue);
}
ldicLinhas.Add(vLinha);
}
}
}
finally
{
FecharConexao();
}
return(ldicLinhas);
}
public List <Dictionary <string, object> > Select(string command_text)
{
List <Dictionary <string, object> > result = new List <Dictionary <string, object> >();
try
{
NpgsqlCommand command = new NpgsqlCommand(command_text, connection);
NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
row.Add(reader.GetName(i), reader[i]);
}
result.Add(row);
}
}
finally
{
connection.Close();
}
return(result);
}
private async Task IterateReader(NpgsqlDataReader reader, string schema, string tableName, CancellationToken cancellationToken = default(CancellationToken))
{
var selector = new List <string>();
var sb = new StringBuilder();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
sb.Append("(");
for (int i = 0; i < reader.FieldCount; i++)
{
var val = FormatValueFromDataType(reader.GetFieldType(i), reader[i]);
sb.Append($"{val},");
if (!selector.Count().Equals(reader.FieldCount))
{
selector.Add(reader.GetName(i));
}
}
sb.Length--;
sb.Append("),");
}
sb.Length--;
var str = $"INSERT INTO {schema}.{tableName} ({string.Join(",", selector)}) VALUES";
File.WriteAllText($"{FileDirectory}{schema}-{tableName}.sql", $"{str} {sb.ToString()};");
}
static public void ShowRead(NpgsqlDataReader reader)
{
if (reader.HasRows)
{
const int part_size = 15;
string title_part = "+" + new string('-', part_size);
string title = "";
for (int i = 0; i < reader.FieldCount; i++)
{
title += title_part;
}
title += "+";
Console.WriteLine(title);
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write($"|{reader.GetName(i),-part_size}");
}
Console.WriteLine("|");
Console.WriteLine(title);
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write($"|{reader[i],-part_size}");
}
Console.WriteLine("|");
Console.WriteLine(title);
}
}
reader.Close();
}
private static void MapObject <T>(this NpgsqlDataReader reader, T obj)
where T : class, new()
{
var mapInfo = GetMapInfo <T>();
for (var i = 0; i < reader.FieldCount; i++)
{
if (reader.GetName(i) == "fieldsgroup")
{
}
if (reader[i] != DBNull.Value && mapInfo.ContainsKey(reader.GetName(i).ToUpper()))
{
var prop = mapInfo[reader.GetName(i).ToUpper()];
prop.SetValue(obj, Convert.ChangeType(reader[i], prop.PropertyType), null);
}
}
}
/// <summary>
/// Get datasets from POSTEGRES - use this override when columns in query and in class T is diferent
/// </summary>
/// <param name="Sql_ora"></param>
/// <param name="Task_name"></param>
/// <param name="D_columns"></param>
/// <param name="P_columns"></param>
/// <returns></returns>
public async Task <List <T> > Get_PSTGR(string Sql_ora, string Task_name, Dictionary <string, int> D_columns, Dictionary <int, string> P_columns, Dictionary <int, Type> P_types)
{
List <T> Rows = new List <T>();
try
{
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
bool list_columns = false;
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
using NpgsqlCommand cust = new NpgsqlCommand(Sql_ora, conO);
using NpgsqlDataReader reader = cust.ExecuteReader();
while (await reader.ReadAsync())
{
if (!list_columns)
{
if (D_columns.Count == 0)
{
for (int col = 0; col < reader.FieldCount; col++)
{
D_columns.Add(reader.GetName(col).ToLower(), col);
}
}
list_columns = true;
}
Row = new T();
int counter = 0;
foreach (var Accessor in Accessors)
{
string metod = P_columns[counter];
if (D_columns.ContainsKey(metod))
{
int col = D_columns[metod];
object readData = reader.GetValue(D_columns[metod]);
if (readData != System.DBNull.Value)
{
Type pt = P_types[counter];
Accessor.SetValue(Row, Convert.ChangeType(readData, Nullable.GetUnderlyingType(pt) ?? pt, null));
}
}
counter++;
}
Rows.Add(Row);
}
}
Rows.Sort();
return(Rows);
}
catch (Exception e)
{
Loger.Log("Błąd modyfikacji tabeli:" + Task_name + e);
return(Rows);
}
}
private static string GetTableHeaderCSV(NpgsqlDataReader reader)
{
string header = "";
for (int i = 0; i < reader.FieldCount; i++)
{
header += reader.GetName(i) + ";";
}
return(header);
}
static void PrintBooks(NpgsqlConnection connection)
{
Console.WriteLine("\n\t\t\t\tBooks Table\n");
connection.Open();
var sqlb = "SELECT * FROM books";
using var cmdb = new NpgsqlCommand(sqlb, connection);
using NpgsqlDataReader rdrb = cmdb.ExecuteReader();
Console.WriteLine("{0,-5} | {1,-18} | {2,-12} | {3,-10}\n", rdrb.GetName(0), rdrb.GetName(1),
rdrb.GetName(2), rdrb.GetName(3));
while (rdrb.Read())
{
Console.WriteLine("{0,-5} | {1,-18} | {2,-12} | {3,-10}", rdrb.GetInt32(0), rdrb.GetString(1),
rdrb.GetString(2), rdrb.GetInt32(3));
}
connection.Close();
}
static void PrintStatistic(NpgsqlConnection connection)
{
Console.WriteLine("\n\t\t\t\t\tStatistic Table\n");
connection.Open();
var sqls = "SELECT * FROM statistic";
using var cmds = new NpgsqlCommand(sqls, connection);
using NpgsqlDataReader rdrs = cmds.ExecuteReader();
Console.WriteLine("{0,-8} | {1,-8} | {2,-8} | {3,-15} | {4,-6}\n", rdrs.GetName(0), rdrs.GetName(1),
rdrs.GetName(2), rdrs.GetName(3), rdrs.GetName(4));
while (rdrs.Read())
{
Console.WriteLine("{0,-8} | {1,-8} | {2,-8} | {3,-15} | {4,-6}...", rdrs.GetInt32(0), rdrs.GetInt32(1),
rdrs.GetInt32(2), rdrs.GetInt32(3), rdrs.GetString(4).Substring(0, rdrs.GetString(4).Length / 20));
}
connection.Close();
}
private static string[] GetColumnNames(NpgsqlDataReader reader)
{
var fieldCount = reader.FieldCount;
var result = new string[fieldCount];
for (var i = 0; i < fieldCount; i++)
{
result[i] = reader.GetName(i);
}
return(result);
}
/// <summary>
/// スキーマ取得
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private DataTable GetShcema(NpgsqlDataReader reader)
{
var result = new DataTable();
for (var i = 0; i < reader.FieldCount; i++)
{
result.Columns.Add(new DataColumn(reader.GetName(i)));
}
return(result);
}
public List <List <string> > QueryList(string sql)
{
List <List <string> > request_result = new List <List <string> >();
// Making a request
NpgsqlDataReader data_reader = default(NpgsqlDataReader);
try
{
_connection.Open();
data_reader = (new NpgsqlCommand(sql, _connection)).ExecuteReader();
bool is_column_headers_added = false;
while (data_reader.Read())
{
if (!data_reader.HasRows)
{
continue;
}
// If there is something in answer.
List <string> cortege = new List <string>();
if (!is_column_headers_added)
{
for (int column_index = 0; column_index < data_reader.FieldCount; column_index++)
{
cortege.Add(data_reader.GetName(column_index));
}
request_result.Add(cortege);
cortege = new List <string>();
is_column_headers_added = true;
}
for (int field_index = 0; field_index < data_reader.FieldCount; field_index++)
{
cortege.Add(data_reader[field_index].ToString());
}
// Adding next cortege to list
request_result.Add(cortege);
}
}
catch (Exception ex)
{
throw;
}
finally
{
data_reader.Close();
_connection.Close();
}
return(request_result);
}
private void GetDataFromSQLTable(NpgsqlConnection con)
{
var data = new List <KodName>();
con.Open();
NpgsqlCommand com = new NpgsqlCommand("select * from typebuilding order by kod", con);
NpgsqlDataReader reader = com.ExecuteReader();
DataTable Table = new DataTable();
// Заполнение шапки таблицы
Table.Columns.Add(reader.GetName(0));
Table.Columns.Add(reader.GetName(1));
while (reader.Read())
{
try
{
Table.Rows.Add(new object[] { reader.GetValue(0), reader.GetValue(1) });
}
catch { }
var mc = new KodName
{
Kod = reader[1].ToString().Trim(),
Name = reader[0].ToString().Trim()
};
data.Add(mc);
}
con.Close();
dataGridView1.DataSource = Table;
comboBox1.DataSource = data;
comboBox1.DisplayMember = "Name"; // То, что будет отображаться пользователю
comboBox1.ValueMember = "Kod"; // То, что будет в SelectedValue
}
private void FigureOutFunctionReturn(IEnumerable <Parameter> parameters, NpgsqlDataReader rdr, string actualName)
{
if (parameters.Where(param => param.Direction == ParameterDirection.InputOutput || param.Direction == ParameterDirection.Output).Count() == 0)
{
if (rdr.FieldCount == 1 && rdr.GetName(0) == actualName)
{
// Simple return
rdr.Read();
Console.WriteLine("Simple Return: {0}", rdr.GetValue(0));
}
}
}
static void PrintUsers(NpgsqlConnection connection)
{
Console.WriteLine("\n\t\t\tUsers Table\n");
connection.Open();
var sql = "SELECT * FROM users";
using var cmd = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine("{0,-4} | {1,-25} | {2,-25}\n", rdr.GetName(0), rdr.GetName(1),
rdr.GetName(2));
while (rdr.Read())
{
Console.WriteLine("{0,-4} | {1,-25} | {2,-25}", rdr.GetInt32(0), rdr.GetString(1),
rdr.GetString(2));
}
connection.Close();
}
static public void Dynamic_Search1(NpgsqlConnection con)
{
DateTime date;
Console.WriteLine("Input date");
date = Convert.ToDateTime(Console.ReadLine());
var sql = $"select team.id, team.name, game_date.date_id, schedule.date from team inner join game on team.id = game.team1_id " +
$"inner join game_date on game.id = game_date.game_id inner join schedule on game_date.game_id = schedule.id where schedule.date = '{date}'";
var cmd = new NpgsqlCommand(sql, con);
Execute(cmd);
NpgsqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine($"{rdr.GetName(0),-4}\t {rdr.GetName(1),-4}\t \t{rdr.GetName(2),10}\t {rdr.GetName(3),10}");
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} \t {rdr.GetString(1),-3}\t\t {rdr.GetInt32(2),10} \t\t{rdr.GetDate(3),10} \t\t ");
}
rdr.Close();
}
/// <summary>
/// スキーマ取得
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private DataTable GetShcema(NpgsqlDataReader reader)
{
var result = new DataTable();
for (var i = 0; i < reader.FieldCount; i++)
{
var dataType = reader.GetFieldType(i);
result.Columns.Add(new DataColumn(reader.GetName(i).ToUpper(), dataType));
}
return(result);
}
private void Init(NpgsqlDataReader _reader)
{
if (_reader == null)
{
return;
}
__rows = new List <DBRow>();
DBRow _row = null;
using (_reader)
{
while (_reader.HasRows)
{
while (_reader.Read())
{
if (_reader.FieldCount > 0)
{
string _firstColumn = _reader.GetName(0);
for (int _i = 0; _i < _reader.FieldCount; _i++)
{
DBField _field = new DBField();
_field.Name = _reader.GetName(_i);
_field.Value = _reader.GetValue(_i);
if (_field.Name == _firstColumn)
{
_row = new DBRow();
__rows.Add(_row);
}
_row.Add(_field);
}
}
}
_reader.NextResult();
}
}
}
public Task <PGDatabaseResult> ExecuteReaderAsyncIns(string sConnectionString, string sSQL, params NpgsqlParameter[] parameters)
{
return(Task.Run(() =>
{
try
{
List <object> sData = new List <object>();
using (var newConnection = new NpgsqlConnection(sConnectionString))
using (var newCommand = new NpgsqlCommand(sSQL, newConnection))
{
newCommand.CommandType = CommandType.Text;
if (parameters != null)
{
newCommand.Parameters.AddRange(parameters);
}
newConnection.Open();
NpgsqlDataReader reader = newCommand.ExecuteReader();
while (reader.Read())
{
procinsplink oData = new procinsplink();
foreach (var pro in oData.GetType().GetProperties())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (pro.Name == reader.GetName(i))
{
pro.SetValue(oData, reader[i]);
}
}
}
sData.Add(oData);
}
newConnection.Close();
}
PGDatabaseResult result = new PGDatabaseResult()
{
Data = sData,
Success = true
};
return result;
}
catch (Exception ex)
{
PGDatabaseResult result = new PGDatabaseResult()
{
Result = "Error: " + ex.Message,
Success = false
};
return result;
}
}));
}
private (string[] columns, string formatting) GetColumnNames(NpgsqlDataReader reader)
{
string[] columns = new string[reader.VisibleFieldCount];
string formatting = "";
for (int i = 0; i < reader.VisibleFieldCount; i++)
{
columns[i] = reader.GetName(i);
formatting += "{" + i + ", -15}";
}
return(columns, formatting);
}
