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;
}
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;
}
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 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 void AseAdapter_WithAseCommandBuilder_CanInsertUpdateAndDelete()
{
using (var connnection = new AseConnection(ConnectionStrings.Default))
{
connnection.Open();
using (var adapter = new AseDataAdapter("SELECT ColumnId, ColumnDescription, ColumnNullable, COALESCE(ColumnNullable, 'Foo') AS ColumnCalculated FROM AseDataAdapterTests_Table1", connnection))
{
using (new AseCommandBuilder(adapter))
{
var original = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(original, SchemaType.Mapped);
adapter.Fill(original);
Assert.AreEqual(5, original.Rows.Count); // SELECT
var updateRow = original.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
updateRow["ColumnDescription"] = "an updated value"; // UPDATE
var deleteRow = original.Rows.Find(3);
Assert.IsNotNull(deleteRow, "Did not find a row in AseDataAdapterTests_Table1 for delete with ColumnId=3");
deleteRow.Delete(); // DELETE
original.Rows.Add(-1, "an inserted value"); // INSERT
// Commit the changes to the database.
adapter.Update(original);
original.AcceptChanges();
var fresh = new DataTable("AseDataAdapterTests_Table1");
adapter.FillSchema(fresh, SchemaType.Mapped);
adapter.Fill(fresh);
Assert.AreEqual(5, fresh.Rows.Count); // SELECT
updateRow = fresh.Rows.Find(1);
Assert.IsNotNull(updateRow, "Did not find a row in AseDataAdapterTests_Table1 for update with ColumnId=1");
Assert.AreEqual(updateRow["ColumnDescription"], "an updated value");
deleteRow = fresh.Rows.Find(3);
Assert.IsNull(deleteRow);
var insertRow = fresh.Rows.Find(6); // Next identity value.
Assert.IsNotNull(insertRow);
}
}
}
}
public void CreateCommand_Connection_IsSet()
{
// Arrange
var mockConnectionPoolManager = InitMockConnectionPoolManager();
var connection = new AseConnection("Data Source=myASEserver;Port=5000;Database=foo;Uid=myUsername;Pwd=myPassword;", mockConnectionPoolManager);
// Act
connection.Open();
var command = connection.CreateCommand();
// Assert
Assert.IsNotNull(command);
Assert.IsNotNull(command.Connection);
}
public void ExecuteScalarAsync_NoCancel_Succeeds()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "waitfor delay '00:00:01' select 1";
command.CommandType = CommandType.Text;
var task = command.ExecuteScalarAsync(new CancellationToken());
task.Wait();
Assert.AreEqual(1, task.Result);
}
}
}
public void GetFieldType_ReturnsNonNullableType(string query, Type expected)
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = query;
var reader = command.ExecuteReader();
reader.Read();
Assert.AreEqual(expected, reader.GetFieldType(0));
Assert.IsTrue(reader.IsDBNull(0));
}
}
}
public void GetConnectionPool_NewConnectionWithUnpooledConnectionString_ReturnsPoolWithSizeZero()
{
var unpooledConnectionString = ConnectionStrings.DefaultUnique;
using (var connection = new AseConnection(unpooledConnectionString))
{
connection.Open();
Assert.AreEqual(0, AseConnectionPoolManager.GetConnectionPool(unpooledConnectionString).Size);
connection.Close();
}
Assert.AreEqual(0, AseConnectionPoolManager.GetConnectionPool(unpooledConnectionString).Size);
}
public void GetConnectionPool_NewConnectionWithPooledConnectionString_ReturnsPoolWithAvailable()
{
var unpooledConnectionString = ConnectionStrings.PooledUnique;
using (var connection = new AseConnection(unpooledConnectionString))
{
connection.Open();
Assert.AreEqual(0, AseConnectionPoolManager.GetConnectionPool(unpooledConnectionString).Available);
connection.Close();
}
Assert.AreEqual(1, AseConnectionPoolManager.GetConnectionPool(unpooledConnectionString).Available);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="connect"></param>
public DataAccessSybase(string connection, bool connect = false)
{
_conn = new AseConnection(connection);
comm = new AseCommand();
comm.CommandTimeout = 0;
comm.Connection = _conn;
if (connect)
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
}
_util = new SharedUtil();
}
public void ExecuteReader_WithMissingStoredProc_ThrowsAseException()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "missing_stored_proc";
command.CommandType = CommandType.StoredProcedure;
var exception = Assert.Throws <AseException>(() => command.ExecuteReader());
Assert.AreEqual(1, exception.Errors.Count);
Assert.AreEqual(2812, exception.Errors[0].MessageNumber);
}
}
}
public void OpenConnection_WithValidConnectionString_TimeoutValueHasDefault()
{
// Arrange
var mockConnectionPoolManager = InitMockConnectionPoolManager();
var connection =
new AseConnection(
"Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;",
mockConnectionPoolManager);
// Act
connection.Open();
// Assert
Assert.AreEqual(15, connection.ConnectionTimeout);
}
public void NumberOfOpenConnections_NewConnectionWithUnpooledConnectionString_ReturnsZero()
{
var unpooledConnectionString = ConnectionStrings.DefaultUnique;
var originalNumberOfOpenConnections = AseConnectionPoolManager.NumberOfOpenConnections;
using (var connection = new AseConnection(unpooledConnectionString))
{
connection.Open();
Assert.AreEqual(originalNumberOfOpenConnections, AseConnectionPoolManager.NumberOfOpenConnections);
connection.Close();
}
Assert.AreEqual(originalNumberOfOpenConnections, AseConnectionPoolManager.NumberOfOpenConnections);
}
public void UseInputParameterWithASQLQuery()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText =
"SELECT TOP 1 FirstName FROM Customer WHERE LastName = @lastName";
command.Parameters.AddWithValue("@lastName", "Rubble"); // Input parameter.
var result = command.ExecuteScalar();
}
}
}
public void Open_StateChange_IsInvoked()
{
// Arrange
var mockConnectionPoolManager = InitMockConnectionPoolManager();
var eventCount = 0;
var connection = new AseConnection("Data Source=myASEserver;Port=5000;Database=foo;Uid=myUsername;Pwd=myPassword;", mockConnectionPoolManager);
connection.StateChange += (sender, args) => eventCount++;
// Act
connection.Open();
// Assert
Assert.AreEqual(2, eventCount); // 2 state changes occur while Opening a connection.
}
//Funciones Propias
public AseConnection ConexionBdd(string conexionString)
{
ConexionInfo conInf = new ConexionInfo(conexionString);
try
{
conn = new AseConnection(conInf.ConectionStrin);
conn.Open();
}
catch (AseException ex)
{
conn = null;
throw new System.InvalidCastException("No se pudo establecer la conexion a la base de datos.", ex);
}
return(conn);
}
public Boolean TestConnection()
{
try
{
AseConnection connection = new AseConnection();
connection.ConnectionString = this.ConnectionString;
connection.Open();
connection.Close();
return(true);
}
catch (Exception ex)
{
errorConnection = ex.Message;
return(false);
}
}
public void ExecuteQuickNonQueryAsync_DelayedCancel_DoesNothing()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "select 1";
command.CommandType = CommandType.Text;
var cts = new CancellationTokenSource(100);
var task = command.ExecuteNonQueryAsync(cts.Token);
task.Wait();
Assert.IsTrue(task.IsCompleted);
}
}
}
public void ExecuteNonQueryAsync_AlreadyCanceled_Cancels()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "waitfor delay '00:00:10'";
command.CommandType = CommandType.Text;
var task = command.ExecuteNonQueryAsync(new CancellationToken(true));
var ex = Assert.Throws <AggregateException>(() => task.Wait());
Assert.IsTrue(ex.InnerException is TaskCanceledException);
Assert.IsTrue(task.IsCanceled);
}
}
}
public void TearDown()
{
// Use SqlCommandBuilder.
using (var connnection = new AseConnection(ConnectionStrings.Default))
{
connnection.Open();
using (var command = connnection.CreateCommand())
{
command.CommandText =
@"IF OBJECT_ID('Customer') IS NOT NULL
BEGIN
DROP TABLE Customer
END";
command.ExecuteNonQuery();
command.CommandText =
@"IF OBJECT_ID('GetCustomer') IS NOT NULL
BEGIN
DROP PROCEDURE GetCustomer
END";
command.ExecuteNonQuery();
command.CommandText =
@"IF OBJECT_ID('CreateCustomer') IS NOT NULL
BEGIN
DROP PROCEDURE CreateCustomer
END";
command.ExecuteNonQuery();
command.CommandText =
@"IF OBJECT_ID('CountCustomer') IS NOT NULL
BEGIN
DROP PROCEDURE CountCustomer
END";
command.ExecuteNonQuery();
command.CommandText =
@"IF OBJECT_ID('GetCustomerFirstName') IS NOT NULL
BEGIN
DROP PROCEDURE GetCustomerFirstName
END";
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 测试连接
/// </summary>
/// <returns>返回是否连接上</returns>
public bool TestConnection(out string msg)
{
using (var conn = new AseConnection(GetConnectionString()))
{
try
{
conn.Open();
}
catch (Exception ex)
{
msg = ex.Message;
return(false);
}
}
msg = "";
return(true);
}
/// <summary>
/// Eliminación de CorreoAplicacion por ID
/// </summary>
/// <param name="CorreoAplicacion">Información de CorreoAplicacion a ser eliminado</param>
/// <param name="resultado">Obtiene el código y el mensaje de resultado</param>
/// <returns>Retorna el código de error de la transacción. Si retorna 0 es OK</returns>
public int CorreoAplicacionEliminar(DTO.clsCorreoAplicacion CorreoAplicacion, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString;
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("Correo_Aplicacion_DEL", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (CorreoAplicacion.Codigo != null)
{
cmd.Parameters.Add(new AseParameter("@I_Codigo", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.Codigo));
}
cmd.Parameters.Add(new AseParameter("@I_Version", AseDbType.DateTime, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.Version));
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 CorreoAplicacionEliminar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(resultado.Resultado);
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="connect"></param>
public DataAccessSybase(string connection, bool connect = false)
{
_conn = new AseConnection(connection);
comm = new AseCommand();
comm.CommandTimeout = 0;
comm.Connection = _conn;
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
if (connect)
{
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
}
_util = new SharedUtil();
}
public DataSet QueryPage(string SQLString, int pageindex, int pagesize)
{
using (AseConnection connection = new AseConnection(this.connectionString))
{
DataSet dataSet = new DataSet();
try
{
connection.Open();
new AseDataAdapter(SQLString, connection).Fill(dataSet, pagesize * pageindex, pagesize, "ds");
}
catch (SqlException exception1)
{
throw new Exception(exception1.Message);
}
return(dataSet);
}
}
public AseDataReader ExecuteReader(string strSQL)
{
AseDataReader reader;
AseConnection connection = new AseConnection(this.connectionString);
AseCommand command = new AseCommand(strSQL, connection);
try
{
connection.Open();
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (OracleException exception1)
{
throw new Exception(exception1.Message);
}
return(reader);
}
public void ExecuteAStoredProcedureThatReturnsAScalarValue()
{
var connectionString = "Data Source=myASEserver;Port=5000;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "CountCustomer";
command.CommandType = CommandType.StoredProcedure;
var result = command.ExecuteScalar();
}
}
}
public void ExecuteScalarAsync_DelayedCancel_Cancels()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "waitfor delay '00:00:10' select 1";
command.CommandType = CommandType.Text;
var cts = new CancellationTokenSource(100);
var task = command.ExecuteScalarAsync(cts.Token);
var ex = Assert.Throws <AggregateException>(() => task.Wait());
Assert.IsTrue(ex.InnerException is TaskCanceledException);
Assert.IsTrue(task.IsCanceled);
}
}
}
public void CreateCommand_WithNamedParameters_Propogates(bool namedParameters)
{
// Arrange
var mockConnectionPoolManager = InitMockConnectionPoolManager();
var connection = new AseConnection("Data Source=myASEserver;Port=5000;Database=foo;Uid=myUsername;Pwd=myPassword;", mockConnectionPoolManager);
connection.NamedParameters = namedParameters;
// Act
connection.Open();
var command = connection.CreateCommand();
// Assert
Assert.IsNotNull(command);
Assert.AreEqual(namedParameters, ((AseCommand)command).NamedParameters);
}
public void ExecuteReader_WithoutSelects_RetainsServerOrder()
{
var results = new List <string>();
var messageEventHandler = new AseInfoMessageEventHandler((sender, eventArgs) =>
{
foreach (AseError error in eventArgs.Errors)
{
results.Add(error.Message);
}
});
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
try
{
connection.InfoMessage += messageEventHandler;
using (var command = connection.CreateCommand())
{
command.CommandText = "[dbo].[sp_test_message_order]";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@runSelect", 'N');
command.Parameters.Add(new AseParameter("@status", AseDbType.VarChar)
{
Direction = ParameterDirection.Output
});
// ReSharper disable once UnusedVariable
using (var reader = command.ExecuteReader())
{
// Do not attempt to read results for this test
// server output should still be sent
}
Assert.AreEqual("OK", command.Parameters["@status"].Value.ToString());
}
}
finally
{
connection.InfoMessage -= messageEventHandler;
}
}
CollectionAssert.AreEqual(_expectedResultsWithoutSelect, results.ToArray());
}
public void ExecuteProcedure_WithNamedParametersTrue_ReturnsParameterValue()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
{
connection.Open();
Assert.IsTrue(connection.NamedParameters);
using (var command = connection.CreateCommand())
{
Assert.IsTrue(command.NamedParameters);
command.CommandType = CommandType.Text;
command.CommandText =
@"IF OBJECT_ID('EchoParameter') IS NOT NULL
BEGIN
DROP PROCEDURE EchoParameter
END";
command.ExecuteNonQuery();
command.CommandType = CommandType.Text;
command.CommandText =
@"CREATE PROCEDURE [EchoParameter]
(
@parameter1 VARCHAR(256),
@parameter2 VARCHAR(256)
)
AS
BEGIN
SELECT @parameter1 + ' ' + @parameter2 AS Value
END";
command.ExecuteNonQuery();
command.CommandType = CommandType.StoredProcedure;
command.AseParameters.Add("@parameter1", "General Kenobi?");
command.AseParameters.Add("@parameter2", "Hello there!");
command.CommandText = "EchoParameter";
var result = command.ExecuteScalar();
Assert.AreEqual("General Kenobi? Hello there!", result);
}
}
}
public void Confirm_Disabled_UnicodeNormalization_Configuration()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "exec sp_configure 'enable unicode normalization'";
using (var result = command.ExecuteReader())
{
result.Read();
var cConfigValue = result.GetOrdinal("Config Value");
var configValue = result.GetInt32(cConfigValue);
Assert.AreEqual(0, configValue, "The database should be configured to disable unicode normalization. Run the following: `exec sp_configure 'enable unicode normalization', 0`");
}
}
}
public void HealthCheckBefore()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "exec sp_configure 'number of user connections'";
using (var result = command.ExecuteReader())
{
result.Read();
var cConfigValue = result.GetOrdinal("Config Value");
var configValue = result.GetValue(cConfigValue).ToString().Trim();
Assert.GreaterOrEqual(Convert.ToInt32(configValue), 100, "The database server should be configured to allow for more connections. Run the following: `exec sp_configure 'number of user connections', 100`");
}
}
}
public void Adequate_UserConnections_Configuration()
{
using (var connection = new AseConnection(ConnectionStrings.Default))
using (var command = connection.CreateCommand())
{
connection.Open();
command.CommandText = "exec sp_configure 'number of user connections'";
using (var result = command.ExecuteReader())
{
result.Read();
var cConfigValue = result.GetOrdinal("Config Value");
var configValue = result.GetInt32(cConfigValue);
Assert.GreaterOrEqual(100, configValue, "The database should be configured to allow for more connections. Run the following: `exec sp_configure 'number of user connections', 100`");
}
}
}
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;
}
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();
}
}
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 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 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 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 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 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 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");
}
}
}
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 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 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 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();
}
}
}
