private ObservableCollection<Broker> GetBrokerMapping()
{
lstBrokers = new ObservableCollection<Broker>();
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.SelectBrokerMapping, con) { CommandType = CommandType.Text })
{
con.Open();
try
{
using (AseDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
lstBrokers.Add(new Broker(Convert.ToInt32(reader["id"].ToString()), reader["portia_broker_name"].ToString(),
reader["dtc_broker_name"].ToString(), reader["executing_broker_id"].ToString(),
Convert.ToDateTime(reader["effective_date"].ToString()),
reader["updatedBy"].ToString()
));
}
}
}
catch (Exception ex)
{
}
}
}
return lstBrokers;
}
public object GetSingle(string SQLString)
{
object obj3;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand command = new AseCommand(SQLString, connection);
try
{
connection.Open();
object objA = command.ExecuteScalar();
if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
{
return(null);
}
obj3 = objA;
}
catch (Exception exception1)
{
connection.Close();
throw new Exception(exception1.Message);
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
return(obj3);
}
protected override DbCommand GetDbCommand(DbConnection dbconnection)
{
AseCommand cmd = new AseCommand();
cmd.Connection = dbconnection as AseConnection;
return(cmd);
}
public void RepeatedDisposal_DoesNotThrow()
{
var command = new AseCommand();
command.Dispose();
command.Dispose();
}
public Task <int> ExecuteNonQueryTaskRunnable(AseCommand command, AseTransaction transaction)
{
var rowsAffectedSource = new TaskCompletionSource <int>();
InternalExecuteNonQueryAsync(command, transaction, rowsAffectedSource);
return(rowsAffectedSource.Task);
}
/// <summary>
/// 执行查询,返回查询结果的第一行第一列
/// </summary>
/// <typeparam name="T">返回值类型</typeparam>
/// <param name="sql">SQL查询语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>查询结果的第一行第一列</returns>
public override T ExecuteScalar <T>(string sql, KdtParameterCollection parameters)
{
try
{
T value = default(T);
// 执行SQL命令
using (AseCommand cmd = new AseCommand(ReplaceSqlText(ReplaceSqlText(sql, parameters), parameters), _sybaseCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
value = cmd.ExecuteScalar().Convert <T>();
cmd.Cancel();
cmd.Dispose();
}
return(value);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行查询,返回查询结果的第一行第一列错误,原因为:{0}", ex.Message));
}
}
public object GetSingle(string SQLString, params AseParameter[] cmdParms)
{
object obj3;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand cmd = new AseCommand();
try
{
this.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
object objA = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if (object.Equals(objA, null) || object.Equals(objA, DBNull.Value))
{
return(null);
}
obj3 = objA;
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
return(obj3);
}
private void ExecutarQuerySemRetorno(string query, string strConn, List <AseParameter> parameters = null)
{
_log.TraceMethodStart();
_log.Trace($"Query gerada: {query}");
AseConnection connection = new AseConnection(strConn);
AseCommand cmd = new AseCommand(query, connection);
if (parameters != null)
{
cmd.Parameters.AddRange(parameters.ToArray());
}
try
{
connection.Open();
cmd.ExecuteNonQuery();
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
_log.TraceMethodEnd();
}
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/// <summary> GET: Student/Delete/5 </summary>
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
public int DeleteClaims(List <string> claimIdList)
{
int claimsDeleted = 0;
SybaseConnString = HomeController.SybaseConnString;
using (var conn = new AseConnection(SybaseConnString))
{
conn.Open();
foreach (var claimId in claimIdList)
{
try
{
AseCommand cmd = new AseCommand("SP_XC_CLAIM_REJECTION_DELETE", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new AseParameter("@TABLE_NAME", TableName));
cmd.Parameters.Add(new AseParameter("@CLCL_ID", claimId));
claimsDeleted += cmd.ExecuteNonQuery();
}
catch (Exception e)
{
System.Console.WriteLine("**ERROR INSERTING CLAIMS** : {0}", e.ToString());
throw e;
}
}
}
return(claimsDeleted);
}
private ObservableCollection<Account> GetAccountMapping()
{
lstAccount = new ObservableCollection<Account>();
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.SelectAccountMapping, con) { CommandType = CommandType.Text })
{
con.Open();
try
{
using (AseDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
lstAccount.Add(new Account(Convert.ToInt32(reader["id"].ToString()), reader["dtcAccountNumber"].ToString(),
reader["portiaAccountNumber"].ToString(),
Convert.ToDateTime(reader["modifiedDate"].ToString()),
reader["modifiedBy"].ToString()
));
}
}
}
catch (Exception ex)
{
}
}
}
return lstAccount;
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="commandType"></param>
/// <param name="closeConnection"></param>
/// <returns></returns>
public List <RespObj> ExecuteReader(string query, CommandType commandType = CommandType.Text, bool closeConnection = true)
{
comm.CommandText = query.ToString();
comm.CommandType = commandType;
DataTable resultTable = new DataTable();
try
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
AseDataAdapter Adapter = new AseDataAdapter(comm);
Adapter.Fill(resultTable);
List <RespObj> ReturnObject = _util.convertDataTable <RespObj>(resultTable);
return(ReturnObject);
}
catch (Exception ex)
{
throw;
}
finally
{
comm = new AseCommand();
if (closeConnection)
{
comm.Dispose();
if (_conn.State != ConnectionState.Closed)
{
_conn.Close();
}
}
}
}
public int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
int num;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand command = new AseCommand(strSQL, connection);
AseParameter p = new AseParameter("@fs", AseDbType.Image)
{
Value = fs
};
command.Parameters.Add(p);
try
{
connection.Open();
num = command.ExecuteNonQuery();
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
return(num);
}
private AseCommand BuildIntCommand(AseConnection connection, string storedProcName, IDataParameter[] parameters)
{
AseCommand command1 = this.BuildQueryCommand(connection, storedProcName, parameters);
command1.Parameters.Add(new AseParameter("ReturnValue", AseDbType.Integer, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
return(command1);
}
public int ExecuteSql(string SQLString, string content)
{
int num;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand command = new AseCommand(SQLString, connection);
AseParameter p = new AseParameter("@content", OracleType.NVarChar)
{
Value = content
};
command.Parameters.Add(p);
try
{
connection.Open();
num = command.ExecuteNonQuery();
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
finally
{
command.Dispose();
connection.Close();
}
}
return(num);
}
public int ExecuteSql(string SQLString, params AseParameter[] cmdParms)
{
int num;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand cmd = new AseCommand();
try
{
this.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
cmd.Parameters.Clear();
num = cmd.ExecuteNonQuery();
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
return(num);
}
public int ExecuteSql(string SQLString)
{
int num;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand command = new AseCommand(SQLString, connection);
try
{
connection.Open();
num = command.ExecuteNonQuery();
}
catch (Exception exception1)
{
connection.Close();
throw new Exception(exception1.Message);
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
return(num);
}
private void GetComaprisonReport()
{
using (AseConnection oCon = new AseConnection(sybConnectionString))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam1 = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam1.Value = new DateTime(2014, 03, 26);
oCom.Parameters.Add(oParam1);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
public override DataTable ExecuteQuery(string cmdText, DBHelperParmCollection parameters)
{
DataTable dtRet = new DataTable();
AseCommand _AseCommand = (AseCommand)CreateCommand(cmdText, CommandType.Text);
_AseCommand.Parameters.Clear();
if (parameters != null)
{
foreach (DBHelperParm para in parameters)
{
_AseCommand.Parameters.Add(new AseParameter(para.Key, EncodingHelper.Default2DB(para.Value, _DBEncodeing)));
}
}
AseDataAdapter _OdbcDataAdapter = new AseDataAdapter(_AseCommand);
try
{
ProcessDataTable(dtRet);
_OdbcDataAdapter.Fill(dtRet);
}
catch (Exception ex)
{
throw ex;
}
return(dtRet);
}
public void ExecuteSqlTran(Hashtable SQLStringList)
{
using (AseConnection connection = new AseConnection(this.connectionString))
{
connection.Open();
using (AseTransaction transaction = connection.BeginTransaction())
{
AseCommand cmd = new AseCommand();
try
{
foreach (DictionaryEntry entry in SQLStringList)
{
string cmdText = entry.Key.ToString();
AseParameter[] cmdParms = (AseParameter[])entry.Value;
this.PrepareCommand(cmd, connection, transaction, cmdText, cmdParms);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
transaction.Commit();
}
}
catch
{
transaction.Rollback();
throw;
}
}
}
}
public DataSet Query(string SQLString, params AseParameter[] cmdParms)
{
DataSet set2;
using (AseConnection connection = new AseConnection(this.connectionString))
{
AseCommand cmd = new AseCommand();
this.PrepareCommand(cmd, connection, null, SQLString, cmdParms);
using (AseDataAdapter adapter = new AseDataAdapter(cmd))
{
DataSet dataSet = new DataSet();
try
{
adapter.Fill(dataSet, "ds");
cmd.Parameters.Clear();
}
catch (Exception exception1)
{
throw new Exception(exception1.Message);
}
set2 = dataSet;
}
}
return(set2);
}
/// <summary>
/// 执行非查询SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数集合</param>
/// <returns>受影响的记录数</returns>
public override int ExecuteNoQuery(string sql, KdtParameterCollection parameters)
{
try
{
int effected = 0;
// 执行SQL命令
using (AseCommand cmd = new AseCommand(ReplaceSqlText(sql, parameters), _sybaseCn))
{
InitCommand(cmd); // 初始化
// 赋值参数
var hasConvertParams = ConvertToSqlParameter(parameters);
foreach (var item in hasConvertParams)
{
cmd.Parameters.Add(item.Value);
}
effected = cmd.ExecuteNonQuery();
cmd.Cancel();
cmd.Dispose();
}
return(effected);
}
catch (Exception ex)
{
KdtLoger.Instance.Error(ex);
throw new DataException(string.Format("执行非查询SQL语句错误,原因为:{0}", ex.Message));
}
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="commandType"></param>
/// <param name="closeConnection"></param>
/// <returns></returns>
public int ExecuteNonQuery(string query, CommandType commandType = CommandType.Text, bool closeConnection = true)
{
comm.CommandText = query.ToString();
comm.CommandType = commandType;
try
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
return(comm.ExecuteNonQuery());
}
catch (Exception ex)
{
throw;
}
finally
{
comm = new AseCommand();
if (closeConnection)
{
comm.Dispose();
if (_conn.State != ConnectionState.Closed)
{
_conn.Close();
}
}
}
}
public DataTable GetJobDetail(string sqlCommand, string tableName)
{
DataTable dt = new DataTable(tableName);
var cmd = new AseCommand(sqlCommand, _aseConnection);
var ada = new AseDataAdapter(cmd);
ada.Fill(dt);
return(dt);
}
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (AseConnection connection = new AseConnection(this.connectionString))
{
connection.Open();
AseCommand command = this.BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
return((int)command.Parameters["ReturnValue"].Value);
}
}
public AseDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
AseConnection connection = new AseConnection(this.connectionString);
connection.Open();
AseCommand command1 = this.BuildQueryCommand(connection, storedProcName, parameters);
command1.CommandType = CommandType.StoredProcedure;
return(command1.ExecuteReader(CommandBehavior.CloseConnection));
}
public Account getAccountStatus(string AcctNo)
{
// BVN = "2014225";
Account account = new Account();
//string result = "";
LogWriter logWriter = new LogWriter();
AseCommand cmd = null;
AseConnection conn = null;
string sqlquery = null;
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
List <Account> accountDetailList = new List <Account>();
try
{
conn = new AseConnection(cnstring);
sqlquery = " SELECT status as status from phoenix..dp_acct WHERE acct_no = '" + AcctNo + "'";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
conn.Open();
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (reader == null)
{
logWriter.WriteErrorLog(string.Format("Reader empty !", "reader is empty"));
account.AccountStatus = "";
}
while (reader.Read())
{
account.AccountStatus = reader["status"].ToString();
}
}
catch (Exception ex)
{
logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", ex.Message));
}
finally
{
//cmd.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(account);
}
private int GetTablesCount()
{
using (AseConnection conn = new AseConnection(connectioString))
{
conn.Open();
using (AseCommand command = new AseCommand("select Count(*) from sysobjects WHERE type = 'U'", conn))
{
return((int)command.ExecuteScalar());
}
}
}
private void UpdateSybaseData(DataTable dtPortia)
{
using (AseConnection oCon = new AseConnection("Data Source=mcmqavip;port=4105;Database=MCM;Uid=idsi;Pwd=3idsi3;"))
{
using (AseCommand oCom = new AseCommand(Constants.InsertPortiaToSybase, oCon) { CommandType = CommandType.StoredProcedure, CommandTimeout = 5000 })
{
oCon.Open();
foreach (DataRow row in dtPortia.Rows)
{
oCom.Parameters.Add(new AseParameter("@id", Convert.ToInt32(row["id"])));
oCom.Parameters.Add(new AseParameter("@tran_#", Convert.ToInt32(row["tran_#"])));
oCom.Parameters.Add(new AseParameter("@portfolio_nm", row["portfolio"].ToString()));
oCom.Parameters.Add(new AseParameter("@portfolio", row["portfolio_code"].ToString()));
oCom.Parameters.Add(new AseParameter("@security_nm", row["security"].ToString()));
oCom.Parameters.Add(new AseParameter("@cusip", row["cusip"].ToString()));
oCom.Parameters.Add(new AseParameter("@lot_#", Convert.ToInt32(row["lot_#"])));
oCom.Parameters.Add(new AseParameter("@tran_type_id", Convert.ToInt32(row["tran_type_id"])));
oCom.Parameters.Add(new AseParameter("@tran_type", row["tran_type"].ToString()));
oCom.Parameters.Add(new AseParameter("@trade_date", Convert.ToDateTime(row["trade_date"])));
oCom.Parameters.Add(new AseParameter("@settle_date", Convert.ToDateTime(row["settle_date"])));
oCom.Parameters.Add(new AseParameter("@maturity_date", Convert.ToDateTime(row["maturity_date"])));
oCom.Parameters.Add(new AseParameter("@issue_date", Convert.ToDateTime(row["issue_date"])));
oCom.Parameters.Add(new AseParameter("@coupon_rate", Convert.ToDouble(row["coupon_rate"])));
oCom.Parameters.Add(new AseParameter("@broker", row["broker"].ToString()));
oCom.Parameters.Add(new AseParameter("@broker_number", row["broker_number"].ToString()));
oCom.Parameters.Add(new AseParameter("@quantity", Convert.ToDouble(row["quantity"])));
oCom.Parameters.Add(new AseParameter("@face_value", Convert.ToDouble(row["face_value"])));
oCom.Parameters.Add(new AseParameter("@price", Convert.ToDouble(row["price"])));
oCom.Parameters.Add(new AseParameter("@commission", Convert.ToDouble(row["commission"])));
oCom.Parameters.Add(new AseParameter("@sec_fee", Convert.ToDouble(row["sec_fee"])));
oCom.Parameters.Add(new AseParameter("@total_amount", Convert.ToDouble(row["total_amount"])));
oCom.Parameters.Add(new AseParameter("@entry_status", row["entry_status"].ToString()));
oCom.Parameters.Add(new AseParameter("@post_date", Convert.ToDateTime(row["post_date"])));
oCom.Parameters.Add(new AseParameter("@settlement_country", row["settlement_country"].ToString()));
oCom.Parameters.Add(new AseParameter("@currency_id", row["currency_id"].ToString()));
oCom.Parameters.Add(new AseParameter("@price_symbol", row["price_symbol"].ToString()));
oCom.Parameters.Add(new AseParameter("@interest", Convert.ToDouble(row["interest"])));
oCom.Parameters.Add(new AseParameter("@principal_amount", Convert.ToDouble(row["principal_amount"])));
oCom.Parameters.Add(new AseParameter("@sec_type", row["sec_type"].ToString()));
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
}
public IHttpActionResult GetAccountDetails(string accountNumber)
{
List <AccountDetailsModel> accountDetails = new List <AccountDetailsModel>();
string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString();
string query = "select c.title_1 as acct_name, " +
"b.gsm_no as Mobile_No, " +
"c.class_code as class_code, " +
"a.home_address as home_address " +
"from zenbase..zib_ecustomer b ," +
"zenbase..zib_kyc_corporate_signatories a, " +
"phoenix..dp_acct c " +
"where a.acct_no = b.acct_no " +
"and a.acct_no = c.acct_no " +
"and a.acct_no = '" +
accountNumber + "'";
try
{
logwriter.WriteTolog("In try");
using (AseConnection connection = new AseConnection(connectionString))
{
AseCommand command = new AseCommand(query, connection);
connection.Open();
AseDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var accountDt = new AccountDetailsModel()
{
mobile_no = reader["Mobile_No"].ToString().Trim(),
home_address = reader["home_address"].ToString().Trim(),
class_code = reader["class_code"].ToString().Trim(),
acct_name = reader["acct_name"].ToString().Trim()
};
accountDetails.Add(accountDt);
}
reader.Close();
}
}
catch (Exception ex)
{
logwriter.WriteTolog("Error in inner exception: " + ex.InnerException);
logwriter.WriteTolog("Error in message: " + ex.Message);
}
return(Ok(accountDetails));
}
public object ExecuteScalar(AseCommand command, AseTransaction transaction)
{
using (var reader = (IDataReader)ExecuteReader(CommandBehavior.SingleRow, command, transaction))
{
if (reader.Read())
{
return(reader[0]);
}
}
return(null);
}
public Tables Get(Database database)
{
Tables tables = new Tables(database);
double tableCount = GetTablesCount();
double tableIndex = 0;
int stats = 0;
using (AseConnection conn = new AseConnection(connectioString))
{
using (AseCommand command = new AseCommand("select O.sysstat2, O.id, O.name, U.name as Owner FROM sysobjects O INNER JOIN sysusers U ON U.uid = O.uid where type = 'U' ORDER BY O.name", conn))
{
conn.Open();
using (AseDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Table table = new Table(database);
table.Id = (int)reader["id"];
table.Name = reader["name"].ToString();
table.Owner = reader["Owner"].ToString();
stats = (int)reader["sysstat2"];
if ((stats & 8192) == 8192)
{
table.LockType = Table.LockTypeEnum.LockAllPages;
}
if ((stats & 16384) == 16384)
{
table.LockType = Table.LockTypeEnum.LockDataPages;
}
if ((stats & 32768) == 32768)
{
table.LockType = Table.LockTypeEnum.LockDataRows;
}
//table.Constraints = (new GenerateConstraint(connectioString,tableFilter)).Get(table); /*Primero debe ir las constraints*/
//table.Columns = GetColumns(table);
//table.Indexes = (new GenerateIndex(connectioString,tableFilter)).Get(table);
tables.Add(table);
tableIndex++;
OnTableProgress(this, new ProgressEventArgs((tableIndex / tableCount) * 100));
}
}
}
}
if (objectFilter.OptionFilter.FilterTrigger)
{
SetTriggers(tables);
}
SetColumnsDatabase(tables);
//tables.Sort();
tables.ToSQL();
return(tables);
}
public int CargarData(DTO.clsCargaDatos ParametrosIngreso, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["BDD_ANDALUCIA"].ConnectionString;
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("anda_reporte..sp_cc_carga_datos_diario", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (ParametrosIngreso.Finmes != null)
{
cmd.Parameters.Add(new AseParameter("@i_fin_mes", AseDbType.Char, ParametrosIngreso.Finmes.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, ParametrosIngreso.Finmes));
}
if (ParametrosIngreso.Fechadesde != null)
{
cmd.Parameters.Add(new AseParameter("@i_fecha_desde", AseDbType.DateTime, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, ParametrosIngreso.Fechadesde));
}
if (ParametrosIngreso.FechaHasta != null)
{
cmd.Parameters.Add(new AseParameter("@i_fecha_hasta", AseDbType.DateTime, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, ParametrosIngreso.FechaHasta));
}
cmd.Parameters.Add(new AseParameter("@O_RETVAL", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_RETMSG", AseDbType.NVarChar, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
cmd.ExecuteNonQuery();
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
}
con.Close();
}
}
catch (Exception error)
{
string strSource = ConfigurationManager.AppSettings["NombreLog"];
using (EventLog eventLog = new System.Diagnostics.EventLog("Application", Environment.MachineName, strSource))
{
eventLog.WriteEntry("Error en DAL CuentaEliminar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = error.Message;
}
return(resultado.Resultado);
}
protected override IDbCommand CreateCommand(string cmdText, CommandType commandType)
{
if (_IDbCommand == null)
{
_IDbCommand = new AseCommand();
}
_IDbCommand.Connection = IDbConnection;
_IDbCommand.CommandText = EncodingHelper.Default2DB(cmdText, _DBEncodeing).ToString();
_IDbCommand.CommandType = commandType;
return(_IDbCommand);
}
private AseCommand BuildQueryCommand(AseConnection connection, string storedProcName, IDataParameter[] parameters)
{
AseCommand command = new AseCommand(storedProcName, connection)
{
CommandType = CommandType.StoredProcedure
};
foreach (AseParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
return(command);
}
public DataSet GetData(procedure type)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.FLSH_RPT))
{
oCmd = new AseCommand("FLSH_RPT", oCon);
}
if (type.Equals(procedure.DISTINCT_DTC))
{
oCmd = new AseCommand("DISTINCT_DTC", oCon);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
private void GetComparison(DateTime tradeDate)
{
using (AseConnection oCon = new AseConnection("Data Source=mcmqavip;port=4105;Database=MCM;Uid=idsi;Pwd=3idsi3;"))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam = new AseParameter("@trade_dt", AseDbType.DateTime) { Value = tradeDate };
oCom.Parameters.Add(oParam);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
private List<DtcConfirms> GetConfirmsFromSybase(DateTime tradeDate)
{
DataTable data = new DataTable();
List<DtcConfirms> lstConfirms = new List<DtcConfirms>();
using (AseConnection oCon = new AseConnection("Data Source=mcmqavip;port=4105;Database=MCM;Uid=idsi;Pwd=3idsi3;"))
{
using (AseCommand oCom = new AseCommand(Constants.InsertConfirms, oCon) { CommandType = CommandType.Text })
{
AseParameter paramDate = new AseParameter(Constants.TradeDateParamPortia, AseDbType.DateTime);
paramDate.Value = tradeDate;
oCom.Parameters.Add(paramDate);
oCon.Open();
try
{
AseDataAdapter adapter = new AseDataAdapter(oCom);
adapter.Fill(data);
foreach (DataRow row in data.Rows)
{
var values = row.ItemArray;
lstConfirms.Add(new DtcConfirms(
values[0].ToString(), values[1].ToString(), values[2].ToString(), values[3].ToString(), values[4].ToString(), values[5].ToString(), values[6].ToString(), values[7].ToString(),
values[8].ToString(), values[9].ToString(), values[10].ToString(), values[11].ToString(), values[12].ToString(), values[13].ToString(), values[14].ToString(),
values[15].ToString(), values[16].ToString(), values[17].ToString(), values[18].ToString(), values[19].ToString(), values[20].ToString()
));
}
}
catch (Exception ex)
{
}
oCon.Close();
}
}
return lstConfirms;
}
private object AddBroker(object param)
{
string portiaName = ((Tuple<string, string,string>)param).Item1;
string dtcName = ((Tuple<string, string, string>)param).Item2;
string execBrokerId = ((Tuple<string, string, string>)param).Item3;
if (!string.IsNullOrEmpty(dtcName) && !string.IsNullOrEmpty(portiaName))
{
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.InsertBroker, con) { CommandType = CommandType.Text })
{
AseParameter oParam = new AseParameter("@portiaBrokerName", AseDbType.VarChar);
oParam.Value = portiaName;
com.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@dtcBrokerName", AseDbType.VarChar);
oParam1.Value = dtcName;
com.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@executingBrokerId", AseDbType.VarChar);
oParam2.Value = execBrokerId;
com.Parameters.Add(oParam2);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
DtcBrokerName = string.Empty;
PortiaBrokerName = string.Empty;
ExecBrokerId = string.Empty;
return null;
}
private object AddAccount(object param)
{
string dtcAccount = ((Tuple<string, string>)param).Item1;
string portiaAccount = ((Tuple<string, string>)param).Item2;
if (!string.IsNullOrEmpty(dtcAccount) && !string.IsNullOrEmpty(portiaAccount))
{
using (AseConnection con = new AseConnection(model.Constants.SybaseConnection))
{
using (AseCommand com = new AseCommand(model.Constants.InsertAccount, con) { CommandType = CommandType.Text })
{
AseParameter oParam = new AseParameter("@dtcAccountNumber", AseDbType.VarChar);
oParam.Value = dtcAccount;
com.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@portiaAccountNumber", AseDbType.VarChar);
oParam1.Value = portiaAccount;
com.Parameters.Add(oParam1);
con.Open();
try
{
com.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
//DtcAccountNum = string.Empty;
//PortiaAccountNum = string.Empty;
return null;
}
void BtnTestConnClick(object sender, EventArgs e)
{
string connectionString = txtConnx.Text;
// OleDbConnection conn = new OleDbConnection(connectionString);
AseConnection conn = new AseConnection(connectionString);
try {
conn.Open();
AseCommand command = new AseCommand("select max(ID) from QA_PAC_TIMING where TEST_PACKAGE='PAR.TPK.0000949'",conn);
command.CommandTimeout = 99999;
object scale = command.ExecuteScalar();
AseDataReader data= command.ExecuteReader();
string result = data.GetString(0);
} catch (Exception ex) {
MessageBox.Show(ex.Message+"\r\n\r\n"+ex.StackTrace);
} finally {
conn.Close();
}
}
protected override IDbCommand CreateCommand(string cmdText, CommandType commandType)
{
if (_IDbCommand == null)
{
_IDbCommand = new AseCommand();
}
_IDbCommand.Connection = IDbConnection;
_IDbCommand.CommandText = EncodingHelper.Default2DB(cmdText, _DBEncodeing).ToString();
_IDbCommand.CommandType = commandType;
return _IDbCommand;
}
private void DeleteExistingRecords()
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("Delete from MCM_DTC_MTF", oCon) { CommandType = CommandType.Text })
{
oCon.Open();
oCom.ExecuteNonQuery();
}
using (AseCommand oCom = new AseCommand("Delete from Confirms_DifferenceTable", oCon) { CommandType = CommandType.Text })
{
oCon.Open();
oCom.ExecuteNonQuery();
}
}
}
/// <summary>
/// calling the stored procedure MCM_STAGE..FLSH_DTC_RCN_RPT
/// prepare the comparison report and returns the data with exception and success
/// </summary>
public DataSet dsExceptionReport(string strTradeDate, int PageSize, string strType)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
if (strType == "1")//Equity trades
oCmd = new AseCommand("FLSH_DTC_RCN_RPT", oCon);
else//FI trades
oCmd = new AseCommand("FLSH_DTC_RCN_RPT_FI", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam.Value = Convert.ToDateTime(strTradeDate).ToShortDateString();
oCmd.Parameters.Add(oParam);
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-dsExceptionReport():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Broker Record for that date")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
private System.Data.DataTable GetCompareRecords()
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("select * from Confirms_DifferenceTable", oCon) { CommandType = CommandType.Text })
{
oCon.Open();
AseDataAdapter adapter = new AseDataAdapter(oCom);
adapter.Fill(dtCompare);
}
}
return dtCompare;
}
private void GetComaprisonReport(DateTime tradeDate)
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("portia_load_dtc_comparison", oCon) { CommandType = CommandType.StoredProcedure })
{
AseParameter oParam1 = new AseParameter("@trade_dt", AseDbType.DateTime);
oParam1.Value = tradeDate;
oCom.Parameters.Add(oParam1);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
dtCompare = GetCompareRecords();
ExportDataTableTOExcel(dtCompare,tradeDate);
}
private void UpdateSybase(System.Data.DataTable dtPortiaa)
{
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
using (AseConnection oCon = new AseConnection(strCon))
{
using (AseCommand oCom = new AseCommand("insert_mcm_dtc_recon", oCon) { CommandType = CommandType.StoredProcedure, CommandTimeout = 5000 })
{
oCon.Open();
foreach (DataRow row in dtPortiaa.Rows)
{
//oCom.Parameters.Add(new AseParameter("@id", Convert.ToInt32(row["id"])));
//oCom.Parameters.Add(new AseParameter("@tran_#", Convert.ToInt32(row["tran_#"])));
////oCom.Parameters.Add(new AseParameter("@trade_list_id",row["trade_list_id"]));
//oCom.Parameters.Add(new AseParameter("@portfolio_nm", row["portfolio"].ToString()));
oCom.Parameters.Add(new AseParameter("@portfolio", row["portfolio_code"].ToString()));
oCom.Parameters.Add(new AseParameter("@cusip", row["cusip"].ToString()));
//oCom.Parameters.Add(new AseParameter("@lot_#", Convert.ToInt32(row["lot_#"])));
//oCom.Parameters.Add(new AseParameter("@tran_type_id", Convert.ToInt32(row["tran_type_id"])));
oCom.Parameters.Add(new AseParameter("@tran_type", row["tran_type"].ToString()));
oCom.Parameters.Add(new AseParameter("@trade_date", Convert.ToDateTime(row["trade_date"])));
oCom.Parameters.Add(new AseParameter("@settle_date", Convert.ToDateTime(row["settle_date"])));
oCom.Parameters.Add(new AseParameter("@price", Convert.ToDouble(row["price"])));
oCom.Parameters.Add(new AseParameter("@sec_type", row["sec_type"].ToString()));
oCom.Parameters.Add(new AseParameter("@security_nm", row["security"].ToString()));
//oCom.Parameters.Add(new AseParameter("@maturity_date", Convert.ToDateTime(row["maturity_date"])));
//oCom.Parameters.Add(new AseParameter("@issue_date", Convert.ToDateTime(row["issue_date"])));
//oCom.Parameters.Add(new AseParameter("@coupon_rate", Convert.ToDouble(row["coupon_rate"])));
oCom.Parameters.Add(new AseParameter("@broker", row["broker"].ToString()));
oCom.Parameters.Add(new AseParameter("@broker_number", row["broker_number"].ToString()));
oCom.Parameters.Add(new AseParameter("@face_value", Convert.ToDouble(row["face_value"])));
oCom.Parameters.Add(new AseParameter("@quantity", Convert.ToDouble(row["quantity"])));
//oCom.Parameters.Add(new AseParameter("@commission", Convert.ToDouble(row["commission"])));
//oCom.Parameters.Add(new AseParameter("@sec_fee", Convert.ToDouble(row["sec_fee"])));
oCom.Parameters.Add(new AseParameter("@total_amount", Convert.ToDouble(row["total_amount"])));
oCom.Parameters.Add(new AseParameter("@entry_status", row["entry_status"].ToString()));
//oCom.Parameters.Add(new AseParameter("@raw_id",row["raw_id"]));
//oCom.Parameters.Add(new AseParameter("@post_date", Convert.ToDateTime(row["post_date"])));
//oCom.Parameters.Add(new AseParameter("@settlement_country", row["settlement_country"].ToString()));
//oCom.Parameters.Add(new AseParameter("@currency_id", row["currency_id"].ToString()));
//oCom.Parameters.Add(new AseParameter("@price_symbol", row["price_symbol"].ToString()));
oCom.Parameters.Add(new AseParameter("@interest", Convert.ToDouble(row["interest"])));
oCom.Parameters.Add(new AseParameter("@principal_amount", Convert.ToDouble(row["principal_amount"])));
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
}
}
public void ExecuteData(procedure type, string forFlashValue, string dtcTicket, string flashTicket, string updatedDtcValue)
{
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.UpdateMappingBoth))
{
oCmd = new AseCommand("UpdateMappingForBoth", oCon);
AseParameter oParam3 = new AseParameter("@newFlash_number", AseDbType.VarChar, 25);
oParam3.Value = flashTicket;
oCmd.Parameters.Add(oParam3);
AseParameter oParam4 = new AseParameter("@newDtc_number", AseDbType.VarChar, 25);
oParam4.Value = updatedDtcValue;
oCmd.Parameters.Add(oParam4);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam1.Value = forFlashValue;
oCmd.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcTicket;
oCmd.Parameters.Add(oParam2);
oCon.Open();
oCmd.ExecuteNonQuery();
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-Exeute Data():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
private void btnExecutar_Click(object sender, EventArgs e)
{
using (AseConnection conn = new AseConnection(ConfigurationManager.AppSettings["DBConn"]))
{
String query = MontaQuerySpWho(cbbUsuario.SelectedItem.ToString(), txbNomeMaquina.Text);
AseCommand cmd = new AseCommand(query, conn);
try
{
conn.Open();
AseDataReader dtReader = cmd.ExecuteReader();
grvResultado.DataSource = dtReader;
lblQtdLinhas.Text = String.Format("Qtd. Linhas: {0}", grvResultado.Rows.Count);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
lblDataHoraUltimoRefresh.Text = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss");
}
}
}
public int CheckMappingData(string dtcTicket, string flashTicket)
{
int noOfRowReturned;
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
oCmd = new AseCommand("CheckMapping", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcTicket;
oCmd.Parameters.Add(oParam2);
AseParameter oParam3 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam3.Value = flashTicket;
oCmd.Parameters.Add(oParam3);
oCon.Open();
noOfRowReturned = Convert.ToInt32(oCmd.ExecuteScalar());
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-CekMapping():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
return 0;
}
throw;
}
return noOfRowReturned;
}
public DataSet GetConsolidateReport(string strType)
{
DataSet dsReport = new DataSet();
//string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
if (strType == "1")//Equity trades
oCmd = new AseCommand("FLSH_DTC_RCN_ECEPTION_ALL", oCon);
else//FI trades
oCmd = new AseCommand("RCN_EXCEPTION_DETAILS_FI_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam.Value = strHandler;
oCmd.Parameters.Add(oParam);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetConsolidateReport():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
dsReport = null;
throw;
}
return dsReport;
}
public void UpdateSybase(BlockingCollection<List<AseParameter>> taskQueue)
{
using (AseConnection oCon = new AseConnection(sybConnectionString))
{
using (AseCommand oCom = new AseCommand("insert_mcm_dtc_recon", oCon) { CommandType = CommandType.StoredProcedure, CommandTimeout = 5000 })
{
oCon.Open();
using (Task uploadSybase = Task.Factory.StartNew(() =>
{
try
{
while (true)
{
oCom.Parameters.AddRange(taskQueue.Take());
oCom.ExecuteNonQuery();
}
}
catch (InvalidOperationException ex)
{
Environment.Exit(0);
}
}))
Console.ReadLine();
}
}
}
public void ExecuteData(procedure type, string flashAcctNum, string dtcAcctNum)
{
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd=null;
if (type.Equals(procedure.ADD_FLSH))
{
oCmd = new AseCommand("ADD_FLSH", oCon);
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
}
if (type.Equals(procedure.RemoveMapping))
{
oCmd = new AseCommand("RemoveMapping", oCon);
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam1 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam1.Value = flashAcctNum;
oCmd.Parameters.Add(oParam1);
AseParameter oParam2 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam2.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam2);
oCon.Open();
oCmd.ExecuteNonQuery();
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-ExeuteData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
private void FillList()
{
AseConnection oCon = new AseConnection(strCon);
SuggestionListFlash = "";
SuggestionListDtc = "";
try
{
string strHandler = Convert.ToString(Utility.GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd;
oCmd = new AseCommand("FLSH_RPT_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
AseParameter oParam1 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam1.Value = strHandler;
oCmd.Parameters.Add(oParam1);
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
oCon.Open();
AseDataReader reader = oCmd.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrEmpty(SuggestionListFlash))
{
flashList.Insert(0, reader["flashAccountNumber"].ToString());
SuggestionListFlash += "\"" + reader["flashAccountNumber"].ToString() + "\"";
}
else
{
flashList.Add(reader["flashAccountNumber"].ToString());
SuggestionListFlash += ", \"" + reader["flashAccountNumber"].ToString() + "\"";
}
}
hdnFlash.Value = SuggestionListFlash;
oCmd.CommandTimeout = intTimeout;
oCmd = new AseCommand("DISTINCT_DTC_ALL", oCon);
oCmd.CommandType = CommandType.StoredProcedure;
AseParameter oParam2 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam2.Value = strHandler;
oCmd.Parameters.Add(oParam2);
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
oCon.Open();
reader = oCmd.ExecuteReader();
while (reader.Read())
{
if (string.IsNullOrEmpty(SuggestionListDtc))
{
dtcList.Insert(0, reader["dtcAccountNumber"].ToString());
SuggestionListDtc += "\"" + reader["dtcAccountNumber"].ToString() + "\"";
}
else
{
dtcList.Add(reader["dtcAccountNumber"].ToString());
SuggestionListDtc += ", \"" + reader["dtcAccountNumber"].ToString() + "\"";
}
}
hdnDtc.Value = SuggestionListDtc;
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-while filling autoomplete data:", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
throw;
}
}
public DataSet GetData(procedure type, string flashAcctNum,string dtcAcctNum)
{
DataSet dsReport = new DataSet();
string strOut = string.Empty;
string strCon = ConfigurationSettings.AppSettings["ConnectionSybaseMCM_STAGE"];
AseConnection oCon = new AseConnection(strCon);
try
{
string strHandler = Convert.ToString(GetFromSession("Handler"));
int intTimeout;
AseCommand oCmd = null;
if (type.Equals(procedure.SEARCH_FLSH_SINGLE))//Equity trades
{
oCmd = new AseCommand("SEARCH_FLSH_SINGLE", oCon);
AseParameter oParam2 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam2.Value = flashAcctNum;
oCmd.Parameters.Add(oParam2);
AseParameter oParam1 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam1.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam1);
}
if (type.Equals(procedure.SEARCH_NONACTIVE))//Equity trades
{
oCmd = new AseCommand("SEARCH_NotActive", oCon);
AseParameter oParam2 = new AseParameter("@flash_number", AseDbType.VarChar, 25);
oParam2.Value = flashAcctNum;
oCmd.Parameters.Add(oParam2);
AseParameter oParam1 = new AseParameter("@dtc_number", AseDbType.VarChar, 25);
oParam1.Value = dtcAcctNum;
oCmd.Parameters.Add(oParam1);
}
oCmd.CommandType = CommandType.StoredProcedure;
intTimeout = oCmd.CommandTimeout;
oCmd.CommandTimeout = 0;
AseParameter oParam0 = new AseParameter("@recon_handle", AseDbType.VarChar, 25);
oParam0.Value = strHandler;
oCmd.Parameters.Add(oParam0);
AseDataAdapter oAseAdp = new AseDataAdapter(oCmd);
oAseAdp.Fill(dsReport);
oCmd.CommandTimeout = intTimeout;
oCmd.Dispose();
oCon.Close();
oCon.Dispose();
oCmd = null;
oCon = null;
}
catch (Exception ex)
{
log.Info("Exception occured-GetData():", ex);
oCon.Close();
oCon.Dispose();
oCon = null;
if (ex.Message == "No Flash Record found")
{
dsReport = null;
return dsReport;
}
throw;
}
return dsReport;
}
