/// <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);
}
}
/// <summary>
/// Get datasets from ORACLE - use this override when columns in query and in class T is same and create prepared parameters
/// </summary>
public async Task <List <T> > Get_Ora(string Sql_ora, string Task_name, ORA_parameters parameters)
{
Dictionary <string, int> D_columns = new Dictionary <string, int>();
Dictionary <int, string> P_columns = new Dictionary <int, string>();
Dictionary <int, Type> P_types = new Dictionary <int, Type>();
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
int counter = 0;
foreach (var p in Accessors)
{
P_types.Add(counter, p.PropertyInfo.PropertyType);
P_columns.Add(counter, p.PropertyInfo.Name.ToLower());
counter++;
}
return(await Get_Ora(Sql_ora, Task_name, D_columns, P_columns, P_types, parameters));
}
/// <summary>
/// Set changes in database table
/// </summary>
/// <param name="_list"></param>
/// <param name="name_table"></param>
/// <param name="guid_col"></param>
/// <returns></returns>
public async Task <int> PSTRG_Changes_to_dataTable(IChanges_list <T> _list, string name_table, string guid_col, string[] query_before, string[] query_after)
{
try
{
Dictionary <string, int> P_columns = new Dictionary <string, int>();
Dictionary <int, Type> P_types = new Dictionary <int, Type>();
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
int counter = 0;
foreach (var p in Accessors)
{
P_types.Add(counter, p.PropertyInfo.PropertyType);
P_columns.Add(p.PropertyInfo.Name.ToLower(), counter);
counter++;
}
List <Npgsql_Schema_fields> Schema = await Get_shema(name_table, P_columns);
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
using NpgsqlTransaction npgsqlTransaction = conO.BeginTransaction();
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET start_update=current_timestamp, in_progress=true,updt_errors=false " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
if (query_before != null)
{
foreach (string comm in query_before)
{
using NpgsqlCommand cmd = new NpgsqlCommand(comm, conO);
cmd.ExecuteNonQuery();
}
}
if (_list.Delete.Count > 0)
{
string comand = "DELETE FROM " + name_table;
string tbl_values = " WHERE ";
string param_values = "=";
using NpgsqlCommand cmd = new NpgsqlCommand
{
Connection = conO
};
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (guid_col == nam && _Fields.Dtst_col != 10000)
{
tbl_values += nam;
param_values = "=@" + nam.ToLower();
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values + param_values;
cmd.Prepare();
foreach (T row in _list.Delete)
{
cmd.Parameters[0].Value = Accessors[P_columns[guid_col]].GetValue(row);
cmd.ExecuteNonQuery();
}
}
if (_list.Update.Count > 0)
{
string comand = "UPDATE " + name_table + " SET";
string tbl_values = " ";
string param_values = " WHERE ";
using NpgsqlCommand cmd = new NpgsqlCommand
{
Connection = conO
};
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (_Fields.Dtst_col != 10000)
{
if (nam.ToLower() == guid_col.ToLower())
{
param_values = param_values + nam + "=@" + nam.ToLower();
}
else
{
tbl_values = tbl_values + nam + "=@" + nam.ToLower() + ",";
}
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values[0..^ 1] + " " + param_values;
/// <summary>
/// Find changes
/// </summary>
/// <param name="Old_list"></param>
/// <param name="New_list"></param>
/// <param name="ID_column"></param>
/// <param name="IntSorted_by"></param>
/// <param name="guid_col"></param>
/// <returns></returns>
public IChanges_list <T> Changes(List <T> Old_list, List <T> New_list, string[] ID_column, string IntSorted_by, string guid_col)
{
Changes_List <T> modyfications = new Changes_List <T>();
try
{
List <T> _operDEl = new List <T>();
List <T> _operINS = new List <T>();
List <T> _operMOD = new List <T>();
int[] ID = new[] { 100000 };
int srt = 1000;
int counter = 0;
int guid_id = 10000;
Dictionary <int, Type> P_types = new Dictionary <int, Type>();
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
foreach (var p in Accessors)
{
string pt_name = p.PropertyInfo.Name.ToLower();
if (ID_column.Contains(pt_name))
{
ID = (ID ?? Enumerable.Empty <int>()).Concat(Enumerable.Repeat(counter, 1)).ToArray();
}
if (pt_name == IntSorted_by.ToLower())
{
srt = counter;
}
if (pt_name == guid_col.ToLower())
{
guid_id = counter;
}
P_types.Add(counter, p.PropertyInfo.PropertyType);
counter++;
}
counter = 0;
int max_old_rows = Old_list.Count;
bool add_Record = false;
foreach (T rows in New_list)
{
if (max_old_rows > counter)
{
while (Convert.ToInt64(Accessors[srt].GetValue(rows)) > Convert.ToInt64(Accessors[srt].GetValue(Old_list[counter])))
{
_operDEl.Add(Old_list[counter]);
counter++;
if (max_old_rows <= counter)
{
break;
}
}
if (max_old_rows > counter)
{
if (Convert.ToInt64(Accessors[srt].GetValue(rows)) == Convert.ToInt64(Accessors[srt].GetValue(Old_list[counter])))
{
bool changed = false;
int col = 0;
foreach (var rw in Accessors)
{
if (!ID.Contains(col))
{
Type pt = P_types[col];
var val1 = rw.GetValue(rows) == null ? null : Convert.ChangeType(rw.GetValue(rows), Nullable.GetUnderlyingType(pt) ?? pt, null);
var val2 = rw.GetValue(Old_list[counter]) == null ? null : Convert.ChangeType(rw.GetValue(Old_list[counter]), Nullable.GetUnderlyingType(pt) ?? pt, null);
if (val1 == null)
{
if (val2 != null)
{
changed = true;
}
}
else
{
if (val2 == null)
{
if (val1 != null)
{
changed = true;
}
}
else
{
if (!val1.Equals(val2))
{
changed = true;
break;
}
}
}
}
col++;
}
if (changed)
{
Row = new T();
col = 0;
foreach (var p in Accessors)
{
if (guid_id == col)
{
p.SetValue(Row, Accessors[guid_id].GetValue(Old_list[counter]));
}
else
{
p.SetValue(Row, p.GetValue(rows));
}
col++;
}
_operMOD.Add(Row);
}
counter++;
}
else
{
add_Record = true;
}
}
else
{
add_Record = true;
}
}
else
{
add_Record = true;
}
if (add_Record)
{
_operINS.Add(rows);
counter++;
add_Record = false;
}
}
var dataset = new Changes_List <T>
{
Insert = _operINS,
Delete = _operDEl,
Update = _operMOD
};
modyfications = dataset;
return(modyfications);
}
catch (Exception e)
{
Loger.Log("Błąd w procedurze porównania :" + e);
return(modyfications);
}
}
/// <summary>
/// Get datasets from ORACLE - use this override when columns in query and in class T is diferent and use prepared parameters
/// </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_Ora(string Sql_ora, string Task_name, Dictionary <string, int> D_columns, Dictionary <int, string> P_columns, Dictionary <int, Type> P_types, ORA_parameters parameters)
{
List <T> Rows = new List <T>();
try
{
using (OracleConnection conO = new OracleConnection(Str_oracle_conn))
{
await conO.OpenAsync();
OracleGlobalization info = conO.GetSessionInfo();
info.DateFormat = "YYYY-MM-DD";
conO.SetSessionInfo(info);
bool list_columns = false;
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
using (OracleCommand cust = new OracleCommand(Sql_ora, conO))
{
using (OracleDataReader reader = cust.ExecuteReader())
{
reader.FetchSize = cust.RowSize * 200;
while (await reader.ReadAsync())
{
if (!list_columns)
{
if (D_columns.Count == 0)
{
for (int col = 0; col < reader.FieldCount; col++)
{
string nam = reader.GetName(col).ToLower();
D_columns.Add(nam, 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);
}
}
/// <summary>
/// Set changes in database table
/// </summary>
/// <param name="_list"></param>
/// <param name="name_table"></param>
/// <param name="guid_col"></param>
/// <returns></returns>
public async Task <int> PSTRG_Changes_to_dataTable(Changes_List <T> _list, string name_table, string guid_col, string[] query_before, string [] query_after)
{
try
{
Dictionary <string, int> P_columns = new Dictionary <string, int>();
Dictionary <int, Type> P_types = new Dictionary <int, Type>();
T Row = new T();
IPropertyAccessor[] Accessors = Row.GetType().GetProperties()
.Select(pi => PropertyInfoHelper.CreateAccessor(pi)).ToArray();
int counter = 0;
foreach (var p in Accessors)
{
P_types.Add(counter, p.PropertyInfo.PropertyType);
P_columns.Add(p.PropertyInfo.Name.ToLower(), counter);
counter++;
}
List <Npgsql_Schema_fields> Schema = await Get_shema(name_table, P_columns);
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
using (NpgsqlTransaction npgsqlTransaction = conO.BeginTransaction())
{
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET start_update=current_timestamp, in_progress=true,updt_errors=false " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
if (query_before != null)
{
foreach (string comm in query_before)
{
using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO))
{
cmd.ExecuteNonQuery();
}
}
}
if (_list.Delete.Count > 0)
{
string comand = "DELETE FROM " + name_table;
string tbl_values = " WHERE ";
string param_values = "=";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (guid_col == nam && _Fields.Dtst_col != 10000)
{
tbl_values += nam;
param_values = "=@" + nam.ToLower();
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values + param_values;
cmd.Prepare();
foreach (T row in _list.Delete)
{
cmd.Parameters[0].Value = Accessors[P_columns[guid_col]].GetValue(row);
cmd.ExecuteNonQuery();
}
}
}
if (_list.Update.Count > 0)
{
string comand = "UPDATE " + name_table + " SET";
string tbl_values = " ";
string param_values = " WHERE ";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (_Fields.Dtst_col != 10000)
{
if (nam.ToLower() == guid_col.ToLower())
{
param_values = param_values + nam + "=@" + nam.ToLower();
}
else
{
tbl_values = tbl_values + nam + "=@" + nam.ToLower() + ",";
}
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + " " + param_values;
cmd.Prepare();
foreach (T row in _list.Update)
{
foreach (Npgsql_Schema_fields _field in Schema)
{
if (_field.Dtst_col != 10000)
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
if (_list.Insert.Count > 0)
{
string comand = "INSERT INTO " + name_table;
string tbl_values = "(";
string param_values = " VALUES (";
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conO;
foreach (Npgsql_Schema_fields _Fields in Schema)
{
string nam = _Fields.Field_name;
if (_Fields.Dtst_col != 10000)
{
tbl_values = tbl_values + nam + ",";
param_values = param_values + "@" + nam.ToLower() + ",";
cmd.Parameters.Add("@" + nam.ToLower(), _Fields.Field_type);
}
}
cmd.CommandText = comand + tbl_values.Substring(0, tbl_values.Length - 1) + ")" + param_values.Substring(0, param_values.Length - 1) + ")";
cmd.Prepare();
foreach (T row in _list.Insert)
{
foreach (Npgsql_Schema_fields _field in Schema)
{
if (_field.Field_name.ToLower() == guid_col.ToLower())
{
if (_field.Field_type == NpgsqlTypes.NpgsqlDbType.Uuid)
{
cmd.Parameters[_field.DB_Col_number].Value = Guid.NewGuid();
}
else
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
else if (_field.Dtst_col != 10000)
{
cmd.Parameters[_field.DB_Col_number].Value = Accessors[_field.Dtst_col].GetValue(row) ?? DBNull.Value;
}
}
cmd.ExecuteNonQuery();
}
}
}
if (query_after != null)
{
foreach (string comm in query_after)
{
using (NpgsqlCommand cmd = new NpgsqlCommand(comm, conO))
{
cmd.ExecuteNonQuery();
}
}
}
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET last_modify=current_timestamp, in_progress=false " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
npgsqlTransaction.Commit();
}
}
return(0);
}
catch (Exception e)
{
using (NpgsqlConnection conO = new NpgsqlConnection(npC))
{
await conO.OpenAsync();
using (NpgsqlCommand cmd = new NpgsqlCommand("" +
"UPDATE public.datatbles " +
"SET in_progress=false,updt_errors=true " +
"WHERE table_name=@table_name", conO))
{
cmd.Parameters.Add("table_name", NpgsqlTypes.NpgsqlDbType.Varchar).Value = name_table;
cmd.Prepare();
cmd.ExecuteNonQuery();
}
}
Loger.Log("Error in update table :" + name_table + " :" + e.StackTrace);
return(1);
}
}
