public async Task <DataTable> QueryAsync(UserData userData, string sql, 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(); DataTable result = new DataTable("DATA"); sql = sql.Replace("&AO", Properties.AppSettings.ApplicationOwner); using var cmd = new OracleCommand(sql, conn); cmd.Transaction = tx; if (parameters != null && parameters.Count > 0) { foreach (var paremeter in parameters) { if (sql.IndexOf(":" + paremeter.Name) > -1) { cmd.Parameters.Add(GetParameter(paremeter.Name, paremeter.Value, paremeter.DataType, paremeter.Direction, paremeter.Size)); } } } var reader = await cmd.ExecuteReaderAsync(); result.Load(reader); return(result); } catch (Exception ex) { throw ex; } finally { if (conn.State == ConnectionState.Open) { await conn.CloseAsync(); } } } catch (Exception ex) { throw ExceptionHelper.Throw(ex, ClassName, "QueryAsync"); } }
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"); } }
ExecuteProcAsync( string procName, OracleParameter[] cmdParams, OracleConnection conn, OracleTransaction transaction = null, bool commit = true ) { // OracleTransaction trans = transaction ?? conn.BeginTransaction(); OracleTransaction trans = transaction ?? (await conn.BeginTransactionAsync()) as OracleTransaction; using (OracleCommand cmd = new OracleCommand()) { try { await PrepareCommandAsync(procName, cmdParams, cmd, CommandType.StoredProcedure, conn, trans); // int affectedCounts = cmd.ExecuteNonQuery(); int affectedCounts = await cmd.ExecuteNonQueryAsync(); if (transaction == null || commit) { trans.Commit(); } cmd.Parameters.Clear(); return(0); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { cmd.Dispose(); if (transaction == null) { trans.Dispose(); } } } }
ExecuteSqlAsync( string zsgSql, OracleParameter[] cmdParams, OracleConnection conn, OracleTransaction transaction = null, bool commit = true ) { OracleTransaction trans = transaction ?? (await conn.BeginTransactionAsync()) as OracleTransaction; using (OracleCommand cmd = new OracleCommand()) { try { await PrepareCommandAsync(zsgSql, cmdParams, cmd, CommandType.Text, conn, trans); int affectedCounts = await cmd.ExecuteNonQueryAsync(); if (transaction == null || commit) { trans.Commit(); } cmd.Parameters.Clear(); return(affectedCounts); } catch (Exception ex) { trans.Rollback(); throw ex; } finally { cmd.Dispose(); if (transaction == null) { trans.Dispose(); } } } }