public bool AddCollections(string getwork_ID, string getUser_ID)
{
using (OracleConnection con = new OracleConnection(ConString.conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "insert into collection values(" +
"'" + getwork_ID + "'" + "," +
"'" + getUser_ID + "'" + ")";
cmd.ExecuteNonQueryAsync();
cmd.CommandText = "update work set collect_num=collect_num+1 where work_id='" + getwork_ID + "'";
cmd.ExecuteNonQueryAsync();
con.Close();
return(true);
}
catch (Exception ex)
{
string e = ex.Message;
return(false);
}
}
}
}
public Task <bool> AddCollections(String getwork_ID, String getUser_ID)
{
string conString = "User Id=C##DBCD;Password=12345678;Data Source=localhost:1521/orcl1";
using (OracleConnection con = new OracleConnection(conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "insert into collection values(" +
"'" + getwork_ID + "'" + "," +
"'" + getUser_ID + "'" + ")";
cmd.ExecuteNonQueryAsync();
cmd.CommandText = "update work set collect_num=collect_num+1 where work_id='" + getwork_ID + "'";
cmd.ExecuteNonQueryAsync();
con.Close();
return(Task.FromResult(true));
}
catch (Exception ex)
{
string e = ex.Message;
return(Task.FromResult(false));
}
}
}
}
public bool AddReport(String getwork_ID, String getuser_ID)
{
string conString = "User Id=C##DBCD;Password=12345678;Data Source=localhost:1521/orcl1";
using (OracleConnection con = new OracleConnection(ConString.conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
cmd.CommandText = "insert into report values(" +
"'" + getwork_ID + "'" + "," +
"'" + getuser_ID + "'" + ",to_date('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS') , 'User Report', " + 0 + ")";
cmd.ExecuteNonQueryAsync();
con.Close();
return(true);
}
catch (Exception ex)
{
string e = ex.Message;
return(false);
}
}
}
}
public static void InsertAdres(int rows)
{
List <string> commandsToTextFile = new List <string>();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Adres (adres_id,kod_poczt,miejsc,ulica,nr_domu,nr_mieszk) values(:adres_id, :kod_poczt, :miejsc, :ulica, :nr_domu, :nr_mieszk)";
using (OracleConnection connection = new OracleConnection(connectionstring))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.Parameters.Add(new OracleParameter("adres_id", GetRowCount("Adres")));
command.Parameters.Add(new OracleParameter("kod_poczt", Generatory.generatorKod_poczt()));
command.Parameters.Add(new OracleParameter("miejsc", Generatory.generatorZnakow(10, false)));
command.Parameters.Add(new OracleParameter("ulica", Generatory.generatorZnakow(10, true)));
command.Parameters.Add(new OracleParameter("nr_domu", Generatory.generatorLiczb(4, 0)));
command.Parameters.Add(new OracleParameter("nr_mieszk", Generatory.generatorLiczb(4, 0)));
commandsToTextFile.Add("insert into Adres (adres_id,kod_poczt,miejsc,ulica,nr_domu,nr_mieszk) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() + ", "
+ command.Parameters[3].Value.ToString() + ", " + command.Parameters[4].Value.ToString() + ", " + command.Parameters[5].Value.ToString() + ")");
command.Connection.Open();
command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
Generatory.zapis_plik("Adres", commandsToTextFile);
}
public static async Task DeleteUnusedAsync(OracleConnection connection)
{
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var transaction = connection.BeginTransaction();
using var cmd = new OracleCommand("DropUnusedWorkflowProcessScheme", connection)
{
CommandType = CommandType.StoredProcedure,
Transaction = transaction
};
var returnParameter = cmd.Parameters.Add("ReturnVal", OracleDbType.Int32, ParameterDirection.ReturnValue);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if ((OracleDecimal)returnParameter.Value != 0)
{
transaction.Rollback();
throw new Exception("Failed to clean up unused WorkflowProcessSchemes ");
}
transaction.Commit();
}
public static void InsertPracownik(int rows)
{
List <string> commandsToTextFile = new List <string>();
List <int> daneId = new List <int>();
List <DateTime> daty = GetDate("data_zatrudnienia", "Pracownik");
DateTime data;
if (daty.Count == 0)
{
data = new DateTime(1995, 1, 1);
}
else
{
data = daty[daty.Count - 1];
}
List <int> foreignKeys = new List <int>();
int liczba_rand = 0;
foreignKeys.Clear();
daneId.Clear();
daneId = GetIds("dane_id", "Dane");
foreignKeys = GetIds("dane_dane_id", "Pracownik");
daneId.RemoveAll(i => foreignKeys.Contains(i));
if (daneId.Count == 0)
{
throw new Exception("brak mozliwych do uzycia wierszy w tabeli dane");
}
Random rnd = new Random();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Pracownik (PRACOWNIK_ID, imie, nazwisko, fax, dane_dane_id, data_zatrudnienia) values(:PRACOWNIK_ID, :imie, :nazwisko, :fax, :dane_dane_id, :data_zatrudnienia)";
using (OracleConnection connection = new OracleConnection(connectionstring))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
int range = (DateTime.Today - data).Days;
data = data.AddDays(rnd.Next(range));
liczba_rand = rnd.Next(daneId.Count - 1);
command.Parameters.Add(new OracleParameter("PRACOWNIK_ID", GetRowCount("Pracownik")));
command.Parameters.Add(new OracleParameter("imie", Generatory.generatorZnakow(9, false)));
command.Parameters.Add(new OracleParameter("nazwisko", Generatory.generatorZnakow(9, true)));
command.Parameters.Add(new OracleParameter("fax", Convert.ToString(Generatory.generatorTelefon())));
command.Parameters.Add(new OracleParameter("dane_dane_id", daneId[liczba_rand]));
command.Parameters.Add(new OracleParameter("data_zatrudnienia", data));
commandsToTextFile.Add("insert into Pracownik (PRACOWNIK_ID, imie, nazwisko, fax, dane_dane_id, data_zatrudnienia) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() + ", "
+ command.Parameters[3].Value.ToString() + ", " + command.Parameters[4].Value.ToString() + ", " + command.Parameters[5].Value.ToString() + ")");
command.Connection.Open();
command.ExecuteNonQueryAsync();
command.Connection.Close();
daneId.Remove((int)command.Parameters[4].Value);
}
}
}
foreignKeys.Clear();
daneId.Clear();
Generatory.zapis_plik("Pracownik", commandsToTextFile);
}
public static void InsertProducent(int rows)
{
List <string> commandsToTextFile = new List <string>();
Random rnd = new Random();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Producent (id_producent,nazwa_producent) values(:id_producent, :nazwa_producent)";
using (OracleConnection connection = new OracleConnection(connectionstring))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.Parameters.Add(new OracleParameter("id_producent", GetRowCount("Producent")));
command.Parameters.Add(new OracleParameter("nazwa_producent", Generatory.generatorZnakow(10, true)));
commandsToTextFile.Add("insert into Producent (id_producent,nazwa_producent) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ")");
command.Connection.Open();
command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
Generatory.zapis_plik("Producent", commandsToTextFile);
}
public async Task <bool> Login(string userId, string Pass)
{
var connection = _conn.ToString();
using (OracleConnection _oracleConnection = new OracleConnection(connection))
{
_oracleConnection.Open();
var cmd = new OracleCommand("ACCOUNT_PKG.GET_ACCOUNT_ID", _oracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
var p_user_name = new OracleParameter("p_user_name", OracleType.Cursor);
var p_pass = new OracleParameter("p_pass", OracleType.Cursor);
var p_reflist = new OracleParameter("p_reflist", OracleType.Cursor);
cmd.Parameters.Add(p_user_name).Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_pass).Direction = ParameterDirection.Output;
cmd.Parameters.Add(p_reflist).Direction = ParameterDirection.Output;
cmd.Transaction = _oracleConnection.BeginTransaction();
try
{
// Assign value here, AFTER starting the TX
var result = await cmd.ExecuteNonQueryAsync();
cmd.Transaction.Commit();
}
catch (OracleException ex)
{
cmd.Transaction.Rollback();
}
}
return(true);
}
public async Task StartImportSchemaAsync(
string fromSchemaName,
string toSchemaName,
string toSchemaPassword,
string dumpFileName,
string logFileName,
string directoryName,
CancellationToken cancellaionToken)
{
var sql =
"begin" + Environment.NewLine +
" DataPumperUtils.StartImportSchema(:FROM_SCHEMA_NAME, :TO_SCHEMA_NAME, :TO_SCHEMA_PASSWORD, :DUMP_FILE_NAME, :LOG_FILE_NAME, :DIRECTORY_NAME);" + Environment.NewLine +
"end;";
using (var command = new OracleCommand(sql, _dbConnection.OracleConnection))
{
command.Parameters.Add(new OracleParameter("FROM_SCHEMA_NAME", fromSchemaName));
command.Parameters.Add(new OracleParameter("TO_SCHEMA_NAME", toSchemaName));
command.Parameters.Add(new OracleParameter("TO_SCHEMA_PASSWORD", toSchemaPassword));
command.Parameters.Add(new OracleParameter("DUMP_FILE_NAME", OraclizeFileName(dumpFileName)));
command.Parameters.Add(new OracleParameter("LOG_FILE_NAME", logFileName));
command.Parameters.Add(new OracleParameter("DIRECTORY_NAME", directoryName));
await command.ExecuteNonQueryAsync(cancellaionToken);
}
}
public static async void InsertGamesToDatabaseAsync(int rows)
{
List <string> commandsToTextFile = new List <string>();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Kolejka (numer_kolejki) values(:numer_kolejki)";
using (OracleConnection connection = new OracleConnection(SqlConnection.connectionString))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.Parameters.Add(new OracleParameter("numer_kolejki", RandomElements.GetRandomNumber(1, 99)));
commandsToTextFile.Add("insert into Kolejka (numer_kolejki) " +
"values(" + command.Parameters[0].Value.ToString() + ")");
command.Connection.Open();
await command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
WriteCommandsToTextFile("Kolejka", commandsToTextFile);
}
public async Task <int> SheduleTaskRunner()
{
try
{
using (connection = new DatabaseConnect().GetOracleConnection())
{
connection.Open();
using (command = new OracleCommand(Sql_File_Command, connection))
{
Console.WriteLine("{0} Начало выполнения задачи {1}", DateTime.Now, Name);
resultCode = await command.ExecuteNonQueryAsync();
IsComplete = true;
Console.WriteLine("{0} Конец выполнения задачи {1}", DateTime.Now, Name, resultCode);
connection.Close();
return(resultCode);
}
}
}
catch (Exception ex)
{
connection.Close();
Console.WriteLine("{0} Error with Task: {1}\nError is {2}\n{3}", DateTime.Now, Name, ex.Message, ex.StackTrace);
return(TASK_ERROR_VALUE);
}
}
/// <summary>
/// Calls the Oracle Executer for the procedure or function specified in the command
/// </summary>
/// <param name="cmd">The OracleCommand that specifies the query to execute</param>
/// <param name="parameters">The parameters to send</param>
/// <returns>A Task indicating the status of the execution</returns>
/// <remarks>Doesn't continue on the same context</remarks>
private async Task ExecuteInnerAsync(OracleCommand cmd, Param[] parameters)
{
var outParameters = new List <OutParam>();
foreach (var param in parameters)
{
var genericMethod = ConverterBase.MakeGenericMethod(param.Type);
var oracleParam = genericMethod.Invoke(null, new[] { param }) as OracleParameter;
cmd.Parameters.Add(oracleParam);
if (oracleParam.Direction == ParameterDirection.Output || oracleParam.Direction == ParameterDirection.InputOutput)
{
var genericOutParam = typeof(OutParam <>).MakeGenericType(param.Type);
outParameters.Add(Activator.CreateInstance(genericOutParam, new object[] { param, oracleParam }) as OutParam);
}
}
try
{
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
catch (Exception ex)
{
throw new Exception($"Error executing {cmd.CommandText}. See inner exception for details.", ex);
}
foreach (var param in outParameters)
{
param.SetParamValue();
}
}
//public void logReadSms(m_LogReq log)
//{
// using(OracleConnection conn = new OracleConnection(Database.conString))
// {
// try
// }
//}
public void logSignup(m_LogReg log)
{
using (OracleConnection conn = new OracleConnection(Database.conString))
{
try
{
conn.Open();
using (var cmd = new OracleCommand(SqlCmd.Log.logRegister, conn)
{
CommandType = CommandType.Text
})
{
cmd.Parameters.Add("cust_no", log.cust_no);
cmd.Parameters.Add("device_id", log.device_id);
cmd.Parameters.Add("tel", log.tel);
cmd.Parameters.Add("ip_addr", log.ip_addr);
cmd.Parameters.Add("status", log.status);
cmd.Parameters.Add("note", log.note);
cmd.Parameters.Add("action", log.action);
cmd.Parameters.Add("serial_sim", log.serial_sim);
cmd.Parameters.Add("brand", log.brand);
cmd.Parameters.Add("model", log.model);
cmd.Parameters.Add("api_version", log.api_version);
cmd.ExecuteNonQueryAsync();
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public void logRequest2(m_LogActivity log)
{
using (OracleConnection conn = new OracleConnection(Database.conString))
{
try
{
conn.Open();
using (var cmd = new OracleCommand(SqlCmd.Log.logReq, conn)
{
CommandType = System.Data.CommandType.Text
})
{
cmd.Parameters.Add(new OracleParameter("note", log.note));
cmd.Parameters.Add(new OracleParameter("cust_no", log.cust_no));
cmd.Parameters.Add(new OracleParameter("device_id", log.device_id));
cmd.Parameters.Add(new OracleParameter("ip_addr", log.ip_addr));
cmd.Parameters.Add(new OracleParameter("url", log.url));
cmd.ExecuteNonQueryAsync();
cmd.Dispose();
}
}
finally
{
conn.Close();
conn.Dispose();
}
}
}
public static async void InsertClubsToDatabaseAsync(int rows)
{
List <string> commandsToTextFile = new List <string>();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Kluby (nazwa_klubu, punkty, zwyciestwa, wspolczynnik_setow, miejsce_w_tabeli) values(:nazwa_klubu, :punkty, :zwyciestwa, :wspolczynnik_setow, :miejsce_w_tabeli)";
using (OracleConnection connection = new OracleConnection(SqlConnection.connectionString))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.Parameters.Add(new OracleParameter("nazwa_klubu", SD.clubs[RandomElements.GetRandomNumber(0, SD.clubs.Count - 1)]));
command.Parameters.Add(new OracleParameter("punkty", RandomElements.GetRandomNumber(0, 99)));
command.Parameters.Add(new OracleParameter("zwyciestwa", RandomElements.GetRandomNumber(0, 27)));
command.Parameters.Add(new OracleParameter("wspolczynnik_setow", RandomElements.GetRandomDouble(0.25, 5.0, 2)));
command.Parameters.Add(new OracleParameter("miejsce_w_tabeli", RandomElements.GetRandomNumber(1, 99)));
commandsToTextFile.Add("insert into Kluby (nazwa_klubu, punkty, zwyciestwa, wspolczynnik_setow, miejsce_w_tabeli) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() +
", " + command.Parameters[3].Value.ToString() + ", " + command.Parameters[4].Value.ToString() + ")");
command.Connection.Open();
await command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
WriteCommandsToTextFile("Kluby", commandsToTextFile);
}
/// <summary>
/// This method will execute a SQL Stored Procedure.
/// </summary>
/// <param name="connectionString">the connection string</param>
/// <param name="sql">the stored procedure name or SQL statement</param>
/// <param name="parameters">array of OracleParameter objects</param>
/// <param name="timeout"></param>
/// <returns>the number of rows affected</returns>
internal protected async Task <int> ExecuteNonQueryAsync(string connectionString, string sql, OracleParameter[] parameters = null, int timeout = QUERY_TIMEOUT)
{
int rowsAffected = 0;
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(sql, connection)
{
CommandType = CommandType.StoredProcedure
})
{
cmd.CommandTimeout = timeout;
cmd.CommandType = sql.Contains(' ') ? CommandType.Text : CommandType.StoredProcedure;
if (parameters != null)
{
foreach (OracleParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
CaptureSqlStatement(sql, parameters);
await connection.OpenAsync();
rowsAffected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
return(rowsAffected);
}
public override async Task PostGenerateExecute()
{
string table_name = "Hashes";
//string database = Conn.ConnectionString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries)
// .ToList().FirstOrDefault(x => x.ToLower().StartsWith("database"))
// .Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries)[1].Trim();
string cmd_text = $@"
DECLARE
nCount NUMBER;
BEGIN
SELECT count(*) INTO nCount FROM all_constraints WHERE constraint_name = '{table_name}_UK_MD5' or constraint_name = '{table_name}_UK_SHA256';
IF(nCount <= 0)
THEN
execute immediate 'ALTER TABLE ""{table_name}"" ADD CONSTRAINT ""{table_name}_UK_MD5"" UNIQUE (""hashMD5"" ) ENABLE
ADD CONSTRAINT ""{table_name}_UK_SHA256"" UNIQUE (""hashSHA256"") ENABLE';
END IF;
END;
";
Console.Write("Creating indexes...");
using (var cmd = new OracleCommand(cmd_text, Conn /*, Tran*/))
{
cmd.Transaction = Tran;
cmd.CommandTimeout = 0;
cmd.CommandType = CommandType.Text;
await cmd.ExecuteNonQueryAsync();
}
Console.WriteLine("done");
}
public static async void InsertSponsorsToDatabaseAsync(int rows)
{
List <string> commandsToTextFile = new List <string>();
List <int> clubIds = new List <int>();
clubIds.Clear();
clubIds = GetIds("id_klubu", "Kluby");
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Sponsorzy (nazwa_sponsora, Kluby_id_klubu, wklad_pieniezny) values(:nazwa_sponsora, :Kluby_id_klubu, :wklad_pieniezny)";
using (OracleConnection connection = new OracleConnection(SqlConnection.connectionString))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
command.Parameters.Add(new OracleParameter("nazwa_sponsora", SD.sponsors[RandomElements.GetRandomNumber(0, SD.sponsors.Count - 1)]));
command.Parameters.Add(new OracleParameter("Kluby_id_klubu", clubIds[RandomElements.GetRandomNumber(0, clubIds.Count - 1)]));
command.Parameters.Add(new OracleParameter("wklad_pieniezny", RandomElements.GetRandomNumber(20000, 900000)));
commandsToTextFile.Add("insert into Sponsorzy (nazwa_sponsora, Kluby_id_klubu, wklad_pieniezny) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() + ")");
command.Connection.Open();
await command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
WriteCommandsToTextFile("Sponsorzy", commandsToTextFile);
clubIds.Clear();
}
public Task <bool> AddComment(String getuser_ID, String getwork_ID, String words)
{
string conString = "User Id=C##DBCD;Password=12345678;Data Source=localhost:1521/orcl1";
using (OracleConnection con = new OracleConnection(conString))
{
using (OracleCommand cmd = con.CreateCommand())
{
try
{
con.Open();
cmd.BindByName = true;
Random random = new Random();
cmd.CommandText = "insert into comments values(" +
"'" + Convert.ToString(random.Next(1, 10000)) + "'" + "," +
"'" + getwork_ID + "'" + "," +
"'" + getuser_ID + "'" + "," +
"'" + words + "'" + "," +
"'" + DateTime.Now + "'"
+ ")";
cmd.ExecuteNonQueryAsync();
con.Close();
return(Task.FromResult(true));
}
catch (Exception ex)
{
string e = ex.Message;
return(Task.FromResult(false));
}
}
}
}
public async Task <bool> Update <T>(T objeto) where T : class, new()
{
String tabla = typeof(T).Name;
var miembros = typeof(T).GetProperties();
bool b = miembros[0].GetValue(objeto) is String;
FormatearComando();
String val = "";
String condicion = "";
SetFieldsForUpdate <T>(out condicion, out val, objeto: objeto, idIsString: b);
try
{
_update = new OracleCommand();
_update.Connection = con;
_update.CommandType = CommandType.StoredProcedure;
_update.CommandText = "SP_ACTUALIZAR";
_update.Parameters.Add("tabla", OracleDbType.Varchar2, tabla, ParameterDirection.Input);
_update.Parameters.Add("valores", OracleDbType.Varchar2, val, ParameterDirection.Input);
_update.Parameters.Add("condicion", OracleDbType.Varchar2, condicion, ParameterDirection.Input);
if (await _update.ExecuteNonQueryAsync() < 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
return(false);
}
}
public async Task RegistrarMatch(PersonaInfo personaIdentificada, short consec, OracleConnection conn)
{
#region RegistrarDatos
string insert = string.Format("INSERT INTO BTS.VALIDA_HUELLA " +
"(ID,CONSEC,ESTADO,MUNICIPIO,CERESO,ANO,FOLIO)" +
" VALUES ({0},{1},{2},{3},'{4}',{5},{6})",
personaIdentificada.id,
consec,
personaIdentificada.estado,
personaIdentificada.municipio,
personaIdentificada.cereso,
personaIdentificada.ano,
personaIdentificada.folio);
try
{
if (conn.State != System.Data.ConnectionState.Open)
{
await conn.OpenAsync();
}
OracleCommand cmdInsert = new OracleCommand(insert, conn);
await cmdInsert.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
throw;
}
#endregion
}
/// <summary>
/// Task for executing non-query commands and stored procedures in Oracle. See documentation at https://github.com/CommunityHiQ/Frends.Community.Oracle.ExecuteCommand
/// </summary>
/// <param name="input">The input data for the task</param>
/// <param name="output">The output of the task</param>
/// <param name="options">The options for the task</param>
/// <returns>object { bool Success, string Message, dynamic Result }</returns>
public async static Task <Output> Execute([PropertyTab] Input input, [PropertyTab] OutputProperties output,
[PropertyTab] Options options)
{
try
{
OracleConnection connection = null;
// Get connection from cache, or create a new one
connection = GetLazyConnection(input.ConnectionString);
if (connection.State != ConnectionState.Open)
{
await connection.OpenAsync();
}
using (OracleCommand command = new OracleCommand(input.CommandOrProcedureName, connection))
{
command.CommandType = (CommandType)input.CommandType;
command.CommandTimeout = input.TimeoutSeconds;
if (input.InputParameters != null)
{
command.Parameters.AddRange(input.InputParameters.Select(x => CreateOracleParam(x))
.ToArray());
}
if (output.OutputParameters != null)
{
command.Parameters.AddRange(output.OutputParameters
.Select(x => CreateOracleParam(x, ParameterDirection.Output)).ToArray());
}
command.BindByName = input.BindParametersByName;
int affectedRows = 0;
// Oracle command executions are not really async https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-wait-properly/29034412#29034412
var runCommand = command.ExecuteNonQueryAsync();
affectedRows = await runCommand;
IEnumerable <OracleParam> outputOracleParams = null;
outputOracleParams = command.Parameters.Cast <OracleParam>()
.Where(p => p.Direction == ParameterDirection.Output);
return(HandleDataset(outputOracleParams, affectedRows, output));
}
}
catch (Exception ex)
{
if (options.ThrowErrorOnFailure)
{
throw ex;
}
return(new Output {
Success = false, Message = ex.Message
});
}
}
public static void InsertFaktura(int rows)
{
List <string> commandsToTextFile = new List <string>();
List <int> klientIds = new List <int>();
List <DateTime> daty = GetDate("data_uslugi", "Faktura");
DateTime data;
if (daty.Count == 0)
{
data = new DateTime(2010, 1, 1);
}
else
{
data = daty[daty.Count - 1];
}
int liczba_rand = 0;
klientIds.Clear();
klientIds = GetIds("id_klient", "Klient");
if (klientIds.Count == 0)
{
throw new Exception("brak wierszy w tabeli Klient");
}
Random rnd = new Random();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Faktura (id_faktura, data_uslugi, wartosc_netto, wartosc_brutto, nr_faktury_sprzedazy, wartosc_vat, forma_platnosci, Klient_id_klient)" +
" values(:id_faktura, :data_uslugi, :wartosc_netto, :wartosc_brutto, :nr_faktury_sprzedazy, :wartosc_vat, :forma_platnosci, :Klient_id_klient)";
using (OracleConnection connection = new OracleConnection(connectionstring))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
int range = (DateTime.Today - data).Days;
data = data.AddDays(rnd.Next(range));
liczba_rand = rnd.Next(klientIds.Count - 1);
command.Parameters.Add(new OracleParameter("id_faktura", GetRowCount("Faktura")));
command.Parameters.Add(new OracleParameter("data_uslugi", data));
command.Parameters.Add(new OracleParameter("wartosc_netto", Generatory.generatorLiczb(6, 2)));
command.Parameters.Add(new OracleParameter("wartosc_brutto", Generatory.generatorLiczb(6, 2)));
command.Parameters.Add(new OracleParameter("nr_faktury_sprzedazy", Generatory.generatorZnakow(2, true) + "/" + Generatory.generatorLiczb(1, 0) + "/" + Generatory.generatorLiczb(4, 0)));
command.Parameters.Add(new OracleParameter("wartosc_vat", "23"));
command.Parameters.Add(new OracleParameter("forma_platnosci", Generatory.generatorZnakow(8, false)));
command.Parameters.Add(new OracleParameter("Klient_id_klient", klientIds[liczba_rand]));
commandsToTextFile.Add("insert into Faktura (id_faktura, data_uslugi, wartosc_netto, wartosc_brutto, nr_faktury_sprzedazy, wartosc_vat, forma_platnosci, Klient_id_klient) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() + ", " + command.Parameters[3].Value.ToString() +
", " + command.Parameters[4].Value.ToString() + ", " + command.Parameters[5].Value.ToString() + ", " + command.Parameters[6].Value.ToString() + command.Parameters[7].Value.ToString() + ")");
command.Connection.Open();
command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
Generatory.zapis_plik("Faktura", commandsToTextFile);
klientIds.Clear();
}
public override async Task Purge()
{
using (var cmd = new OracleCommand("truncate table \"Hashes\"", Conn /*, Tran*/))
{
cmd.Transaction = Tran;
cmd.CommandType = CommandType.Text;
await cmd.ExecuteNonQueryAsync();
}
}
//public async Task SetGUIDFromGIDOracleAsync(long GID, string GUID, string table)
//{
// cmd = new OracleCommand();
// cmd.Connection = conn;
// cmd.CommandText = $"UPDATE {table} SET GUID = '{GUID}' where GID = '{GID}'";
// cmd.CommandType = CommandType.Text;
// await cmd.ExecuteNonQueryAsync();
//}
public async Task SetGUIDFromGIDOracleAsync(long GID, string GUID, string table)
{
cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = $"UPDATE {table} SET GUID = '{GUID}' where GID = '{GID}'";
// cmd.CommandText = $"insert into dummy (GUID, GID)\n Values( '{GUID}', '{GID}') ";
cmd.CommandType = CommandType.Text;
await cmd.ExecuteNonQueryAsync();
}
public async Task ExecuteAsync(CancellationToken cancellationToken)
{
EnsureCommandCreated();
_dataOutParam.Size = ByteCountToRead;
_dataOutParam.Value = null;
await _command.ExecuteNonQueryAsync(cancellationToken);
}
public async Task InserirOnChange(TableDependency.EventArgs.RecordChangedEventArgs <T> e)
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
await Task.Run(() => connection.OpenAsync());
OracleCommand insertCommand = MontaInsertCommand(connection, e);
await Task.Run(() => insertCommand.ExecuteNonQueryAsync());
}
}
public static void InsertPojazd(int rows)
{
List <string> commandsToTextFile = new List <string>();
List <string> vin = new List <string>();
string nr_Vin;
bool wyjscie = true;
vin.Clear();
vin = Getvin("nr_vin", "Pojazd");
Random rnd = new Random();
for (int i = 0; i < rows; i++)
{
var commandText = "insert into Pojazd (nr_vin, marka, model, rok_produkcji, nr_rej, jaki_pojazd) values(:nr_vin, :marka, :model, :rok_produkcji, :nr_rej, :jaki_pojazd)";
using (OracleConnection connection = new OracleConnection(connectionstring))
{
using (OracleCommand command = new OracleCommand(commandText, connection))
{
nr_Vin = Generatory.generatorZnakow(4, true) + Generatory.generatorLiczb(6, 0) + Generatory.generatorLiczb(7, 0);
while (true)
{
for (int j = 0; j < vin.Count; j++)
{
wyjscie = true;
if (vin[j] == nr_Vin)
{
wyjscie = false;
}
}
if (wyjscie)
{
break;
}
nr_Vin = Generatory.generatorZnakow(4, true) + Generatory.generatorLiczb(6, 0) + Generatory.generatorLiczb(7, 0);
}
command.Parameters.Add(new OracleParameter("nr_vin", nr_Vin));
command.Parameters.Add(new OracleParameter("marka", Generatory.generatorZnakow(7, true)));
command.Parameters.Add(new OracleParameter("model", Generatory.generatorZnakow(7, false)));
command.Parameters.Add(new OracleParameter("rok_produkcji", int.Parse("201" + Generatory.generatorLiczb(1, 0))));
command.Parameters.Add(new OracleParameter("nr_rej", Generatory.generatorZnakow(2, true) + " " + Generatory.generatorLiczb(5, 0)));
command.Parameters.Add(new OracleParameter("jaki_pojazd", Generatory.generatorZnakow(6, false)));
commandsToTextFile.Add("insert into Pojazd (nr_vin, marka, model, rok_produkcji, nr_rej, jaki_pojazd) " +
"values(" + command.Parameters[0].Value.ToString() + ", " + command.Parameters[1].Value.ToString() + ", " + command.Parameters[2].Value.ToString() + ", " + command.Parameters[3].Value.ToString() +
", " + command.Parameters[4].Value.ToString() + ", " + command.Parameters[5].Value.ToString() + ", " + ")");
command.Connection.Open();
command.ExecuteNonQueryAsync();
command.Connection.Close();
}
}
}
Generatory.zapis_plik("Pojazd", commandsToTextFile);
}
public async Task <int> ExecuteAsync(UserData userData, string commandText, CommandType commandType = CommandType.Text, List <DatabaseParameter> parameters = null)
{
try
{
var connString =
BuildConnectionString(
Properties.AppSettings.ConnectionString,
userData.Username,
PasswordHelper.Decrypt(userData.EncryptedPassword));
using var conn = new OracleConnection(connString);
try
{
await conn.OpenAsync();
var tx = (OracleTransaction)await conn.BeginTransactionAsync();
commandText = commandText.Replace("&AO", Properties.AppSettings.ApplicationOwner);
using var cmd = new OracleCommand(commandText, conn)
{
Transaction = tx,
CommandType = commandType
};
if (parameters != null && parameters.Count > 0)
{
foreach (var paremeter in parameters)
{
if (commandText.IndexOf(":" + paremeter.Name) > -1)
{
cmd.Parameters.Add(GetParameter(paremeter.Name, paremeter.Value, paremeter.DataType, paremeter.Direction, paremeter.Size));
}
}
}
return(await cmd.ExecuteNonQueryAsync());
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (conn.State == ConnectionState.Open)
{
await conn.CloseAsync();
}
}
}
catch (Exception ex)
{
throw ExceptionHelper.Throw(ex, ClassName, "ExecuteAsync");
}
}
public async Task InserirOnChange(object handle)
{
using (OracleConnection connection = new OracleConnection(ConnectionString))
{
await Task.Run(() => connection.OpenAsync());
string sql = $"INSERT INTO NOTA(codigo, texto) values ({handle}, 'Texto - {handle}')";
OracleCommand insertCommand = new OracleCommand(sql, connection);
await Task.Run(() => insertCommand.ExecuteNonQueryAsync());
}
}