/// <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>
/// Update table with Calendar Days
/// </summary>
/// <returns></returns>
public async Task <int> Update_calendar_table(string calendar_id)
{
try
{
if (calendar_id != "")
{
Update_pstgr_from_Ora <Calendar> rw = new Update_pstgr_from_Ora <Calendar>();
List <Calendar> list_ora = new List <Calendar>();
List <Calendar> list_pstgr = new List <Calendar>();
var dataObject = new ExpandoObject() as IDictionary <string, Object>;
ORA_parameters Command_prepare = new ORA_parameters();
Parallel.Invoke(async() =>
{
list_ora = await rw.Get_Ora("" +
"SELECT calendar_id, counter, to_date(work_day) work_day, day_type, working_time, working_periods, objid, objversion " +
"FROM ifsapp.work_time_counter " +
"WHERE CALENDAR_ID='SITS' ", "Calendar_ORA");
list_ora.Sort();
}, async() => { list_pstgr = await rw.Get_PSTGR("Select * from work_cal WHERE CALENDAR_ID='SITS' order by counter", "Calendar_Pstgr"); list_pstgr.Sort(); });
Changes_List <Calendar> tmp = rw.Changes(list_pstgr, list_ora, new[] { "id" }, "id", "id");
list_ora = null;
list_pstgr = null;
return(await PSTRG_Changes_to_dataTable(tmp, "work_cal", "id", null, null));
}
else
{
throw new Exception("Service Calendar not set in settings.xml file ");
}
}
catch (Exception e)
{
Loger.Log("Błąd importu CRP:" + e);
return(1);
}
}
/// <summary>
/// To Do => 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);
}
}