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);
}
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);
}
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);
}
public void Simple_Procedure_ShouldExecute()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Execute("sp_test_simple", commandType: CommandType.StoredProcedure);
}
}
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);
}
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;
}
public void OpenConnection_WithCharsetCp850_NoEncodingProvider_Throws()
{
using (var connection = new AseConnection(ConnectionStrings.Cp850))
{
Assert.Throws <AseException>(() => connection.Open());
}
}
public void TearDown()
{
// Use SqlCommandBuilder.
using (var connnection = new AseConnection(ConnectionStrings.Default))
{
connnection.Open();
using (var command = connnection.CreateCommand())
{
command.CommandText =
@"IF OBJECT_ID('AseDataAdapterTests_Table1') IS NOT NULL
BEGIN
DROP TABLE AseDataAdapterTests_Table1
END";
command.ExecuteNonQuery();
command.CommandText =
@"IF OBJECT_ID('AseDataAdapterTests_Proc1') IS NOT NULL
BEGIN
DROP PROCEDURE AseDataAdapterTests_Proc1
END";
command.ExecuteNonQuery();
}
}
}
public void Login_Blitz(int size, int threads, string cs)
{
//need to add some counters so we can see what's going on
Logger.Disable();
var parallelism = size * 2;
ParallelLoopResult result;
try
{
result = Parallel.ForEach(
Enumerable.Repeat(1, threads),
new ParallelOptions
{
MaxDegreeOfParallelism = parallelism
},
(_, __) =>
{
using (var connection = new AseConnection(cs))
{
connection.Open();
}
});
}
catch (AggregateException ae)
{
ExceptionDispatchInfo.Capture(ae.InnerException).Throw();
throw;
}
Assert.IsTrue(result.IsCompleted);
}
public void EchoChar_Procedure_ShouldExecute(object input, object expected)
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "sp_test_echo_char";
command.CommandType = CommandType.StoredProcedure;
var p = command.CreateParameter();
p.ParameterName = "@input";
p.Value = input ?? DBNull.Value;
p.DbType = DbType.AnsiStringFixedLength;
command.Parameters.Add(p);
var pOut = command.CreateParameter();
pOut.ParameterName = "@output";
pOut.Value = DBNull.Value;
pOut.DbType = DbType.AnsiStringFixedLength;
pOut.Direction = ParameterDirection.Output;
pOut.Size = 1;
command.Parameters.Add(pOut);
command.ExecuteNonQuery();
Assert.AreEqual(expected ?? DBNull.Value, pOut.Value);
}
}
}
public void EchoBinary_Procedure_ShouldExecute()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "sp_test_echo_binary";
command.CommandType = CommandType.StoredProcedure;
var expected = Enumerable.Repeat(new byte[] { 0xde, 0xad, 0xbe, 0xef }, 64).SelectMany(x => x).Take(255).ToArray();
var p = command.CreateParameter();
p.ParameterName = "@input";
p.Value = expected;
p.DbType = DbType.Binary;
command.Parameters.Add(p);
var pOut = command.CreateParameter();
pOut.ParameterName = "@output";
pOut.Value = DBNull.Value;
pOut.DbType = DbType.Binary;
pOut.Direction = ParameterDirection.Output;
command.Parameters.Add(pOut);
command.ExecuteNonQuery();
Assert.AreEqual(expected, pOut.Value);
}
}
}
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 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 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 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;
}
}
}
}
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);
}
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);
}
private void ExecuteProcedure(string connectionString)
{
using (var connection = new AseConnection(connectionString))
{
var expected = Guid.NewGuid().GetHashCode();
var parameters = new DynamicParameters();
parameters.Add("@RETURN_VALUE", -1, DbType.Int32, ParameterDirection.ReturnValue);
parameters.Add("@nEchoValueReturn", -1, DbType.Int32, ParameterDirection.Output);
parameters.Add("@nEchoValue", expected, DbType.Int32, ParameterDirection.Input);
using (var multi = connection.QueryMultiple("sp_test_echo", parameters, commandType: CommandType.StoredProcedure))
{
var t1Echo = multi.Read <int>().FirstOrDefault();
var t2Echo = multi.Read <int>().FirstOrDefault();
Assert.AreEqual(expected, t1Echo);
Assert.AreEqual(expected + 1, t2Echo);
var rReturn = parameters.Get <int>("@RETURN_VALUE");
var rOutput = parameters.Get <int>("@nEchoValueReturn");
Assert.AreEqual(expected, rReturn);
Assert.AreEqual(expected, rOutput);
}
}
}
public override void SetUpFixture()
{
SybaseAseInsightDbProvider.RegisterProvider();
_connectionStringBuilder = new AseConnectionStringBuilder();
_connectionStringBuilder.ConnectionString = "Data Source=testserver;Port=5000;User ID=sa;Password=Password1";
_connection = new AseConnection(_connectionStringBuilder.ConnectionString);
_connection.Open();
}
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 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 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");
}
}
}
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();
}
}
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;
}
/// <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)
{
}
}
}
}
}
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();
}
}
}
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 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;
}
}
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 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;
}
}
public SybaseHelper(AseConnection myConnection)
: base(myConnection)
{
_DBEncodeing = DBConfig.GetDBConfig().Sybase.DBCoding;
}
public SybaseHelper(AseConnection myConnection,string DBEncoding)
: base(myConnection)
{
_DBEncodeing = DBConfig.GetDBEncoding(DBEncoding);
}
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;
}
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 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();
}
}
}
