/// <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);
}
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 DataTable RetornarDataTableQuery(string query, string strConn, List <AseParameter> parameters = null)
{
_log.TraceMethodStart();
_log.Trace($"Query gerada: {query}");
DataTable data = new DataTable();
AseConnection connection = new AseConnection(strConn);
AseDataAdapter da = new AseDataAdapter(query, connection);
if (parameters != null)
{
da.SelectCommand.Parameters.AddRange(parameters.ToArray());
}
try
{
connection.Open();
da.Fill(data);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
_log.TraceMethodEnd();
return(data);
}
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 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();
}
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 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);
}
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);
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (AseConnection connection = new AseConnection(this.connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
new AseDataAdapter {
SelectCommand = this.BuildQueryCommand(connection, storedProcName, parameters)
}.Fill(dataSet, tableName);
connection.Close();
return(dataSet);
}
}
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);
}
//Cerrar la conexion
public bool CerrarConexion()
{
try
{
conn.Close();
conn = null;
return(true);
}
catch (AseException ex)
{
conn = null;
return(false);
throw new Exception(ex.Message);
}
}
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 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);
}
}
/// <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);
}
//[TestCleanup]
public void CleanUp()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
RunSetupCommand(connection, "drop table [dbo].[records_tests]");
RunSetupCommand(connection, "drop procedure dbo.Test_Select");
RunSetupCommand(connection, "drop procedure dbo.Test_Insert");
RunSetupCommand(connection, "drop procedure dbo.Test_Delete");
RunSetupCommand(connection, "drop procedure dbo.Test_Update");
RunSetupCommand(connection, "drop procedure dbo.Test_Select_Insert");
RunSetupCommand(connection, "drop procedure dbo.Test_Insert_Select");
RunSetupCommand(connection, "drop procedure dbo.Test_Insert_Delete");
connection.Close();
}
}
public List <Branch> GetBranchList()
{
List <Branch> brList = new List <Branch>();
AseCommand cmd = null;
AseConnection conn = null;
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
try
{
conn = new AseConnection(cnstring);
string sqlquery = "select branch_no,name_1 from phoenix..ad_gb_branch where status = 'Active' order by name_1";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
conn.Open();
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
Branch br = new Branch();
br.BranchNo = Convert.ToInt16(reader["branch_no"].ToString());
br.BranchName = reader["name_1"].ToString();
brList.Add(br);
}
reader.Close();
}
catch (Exception ex)
{
throw;
}
finally
{
cmd.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(brList);
}
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;
}
public DataTable GetReaderInfo(DataModel.M_Config config)
{
AseConnection conn = new AseConnection(config.SybaseSourceSetting.ToConnectionString());
string cmdstr = config.SQLString;
DataSet ds = new DataSet();
try
{
AseDataAdapter adapter = new AseDataAdapter(cmdstr, conn);
adapter.Fill(ds);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public int InsertDataUsingStoredProcedure(string storedProcedureName, DatabaseEnum dbName, List<AseParameter> parameters)
{
var connFactory = new ConnectionFactory();
int _errorCode = 0;
string connectionString = connFactory.GetConnectionString(dbName);
using (conn = connFactory.GetAseConnectionString(connectionString))
{
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
using (cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;
if (parameters != null )
{
foreach (AseParameter param in parameters)
{
cmd.Parameters.Add(param);
}
}
_errorCode = cmd.ExecuteNonQuery();
}
}
}
catch (AseException ex)
{
_logger.ErrorFormat("Error Inserting Data into Database {0}", ex);
throw;
}
finally
{
conn.Close();
}
}
return _errorCode;
}
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, 02, 26);
oCom.Parameters.Add(oParam1);
oCon.Open();
try
{
oCom.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
oCon.Close();
}
}
//[TestInitialize]
public void Initialise()
{
using (var connection = new AseConnection(ConnectionStrings.Pooled))
{
connection.Open();
RunSetupCommand(connection, CleanUpSql);
RunSetupCommand(connection, "create table [dbo].[records_tests] (data_field int null)");
RunSetupCommand(connection, "insert into records_tests (data_field) values (1)");
RunSetupCommand(connection, "insert into records_tests (data_field) values (2)");
RunSetupCommand(connection, "insert into records_tests (data_field) values (3)");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Select as {select}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Insert as {insert}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Delete as {delete}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Update as {update}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Select_Insert as {select} {insert}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Insert_Select as {insert} {select}");
RunSetupCommand(connection, $"create or replace procedure dbo.Test_Insert_Delete as {insert} {delete}");
//RunSetupCommand(connection, "create or replace procedure dbo.test as select top 1 * from ov_task select top 2 * from ov_task");
connection.Close();
}
}
private void btTest_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(this.tbDataBase.Text))
{
MessageBox.Show(this, "请输入数据库DataBase");
}
else if (string.IsNullOrEmpty(this.tbUser.Text))
{
MessageBox.Show(this, "请输入数据库User");
}
else if (string.IsNullOrEmpty(this.tbPassword.Text))
{
MessageBox.Show(this, "请输入数据库Password");
}
else if (string.IsNullOrEmpty(this.tbDataSource.Text))
{
MessageBox.Show(this, "请输入数据库DataSource");
}
else if (string.IsNullOrEmpty(this.tbservername.Text))
{
MessageBox.Show(this, "请输入数据库servername");
}
else
{
try
{
AseConnection connection1 = new AseConnection(DESEncrypt.Decrypt(this.Connection.ConnectionString));
connection1.Open();
connection1.Close();
MessageBox.Show(this, "链接数据库成功");
}
catch (Exception exception)
{
MessageBox.Show(this, "测试链接失败，错误" + exception.Message);
}
}
}
static int Main(string[] args)
{
int
Result = -1,
tmpInt;
decimal
tmpDecimal;
object
tmpObject;
StreamWriter
fstr_out = null;
string
tmpString,
OutputFileName = "log.log";
#if WITH_TRACE
string
TraceFileName = "trace.log";
#endif
AseConnection
conn = null;
AseCommand
cmd = null;
AseTransaction
Transaction = null;
AseDataReader
reader = null;
AseParameter
Parameter = null;
AseDataAdapter
da = null;
DataTable
tmpDataTable = null;
try
{
try
{
fstr_out = new StreamWriter(OutputFileName, false, System.Text.Encoding.GetEncoding(1251));
fstr_out.AutoFlush = true;
#if WITH_REFLECTION
Assembly[]
asms = AppDomain.CurrentDomain.GetAssemblies();
Assembly
asm = null;
foreach (Assembly a in asms)
{
if (a.FullName.ToLower().IndexOf("sybase.data.aseclient") > -1)
{
asm = a;
}
fstr_out.WriteLine("Assembly.CodeBase: " + a.CodeBase);
fstr_out.WriteLine("Assembly.EscapedCodeBase: " + a.EscapedCodeBase);
fstr_out.WriteLine("Assembly.FullName: " + a.FullName);
fstr_out.WriteLine("Assembly.GlobalAssemblyCache: " + a.GlobalAssemblyCache.ToString().ToLower());
fstr_out.WriteLine("Assembly.ImageRuntimeVersion: " + a.ImageRuntimeVersion);
fstr_out.WriteLine("Assembly.Location: " + a.Location);
fstr_out.WriteLine();
}
if (asm != null)
{
Type[]
alltypes = asm.GetTypes();
for (tmpInt = 0; tmpInt < alltypes.Length; ++tmpInt)
{
fstr_out.WriteLine("Обнаружено: " + alltypes[tmpInt].Name);
if (alltypes[tmpInt].Name.CompareTo("AseConnection") == 0)
{
Type
t = alltypes[tmpInt];
FieldInfo[]
fi = t.GetFields(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Field, определенных в " + t.Name);
foreach (FieldInfo f in fi)
{
fstr_out.Write(" " + f.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
PropertyInfo[]
//pi_=t.GetProperties(BindingFlags.Instance|BindingFlags.NonPublic|BindingFlags.Static); // AseConnection.Language
pi_ = t.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
fstr_out.WriteLine("Анализ Property, определенных в " + t.Name);
foreach (PropertyInfo p_ in pi_)
{
fstr_out.Write(" " + p_.Name);
fstr_out.WriteLine();
}
fstr_out.WriteLine();
fstr_out.WriteLine("Поддерживаемые методы:");
ParameterInfo[]
pi;
MethodInfo[]
mi;
//mi=t.GetMethods();
mi = t.GetMethods(BindingFlags.DeclaredOnly
| BindingFlags.Instance
| BindingFlags.Public);
foreach (MethodInfo m in mi)
{
fstr_out.Write(" " + m.ReturnType.Name + " " + m.Name + "(");
pi = m.GetParameters();
for (int i = 0; i < pi.Length; ++i)
{
fstr_out.Write(pi[i].ParameterType.Name + " " + pi[i].Name);
if (i + 1 < pi.Length)
{
fstr_out.Write(", ");
}
}
fstr_out.WriteLine(")");
}
fstr_out.WriteLine();
}
}
fstr_out.WriteLine();
}
#endif
#if WITH_TRACE
_strmWriter = new StreamWriter(TraceFileName, false, System.Text.Encoding.GetEncoding(1251));
_strmWriter.AutoFlush = true;
#endif
if ((tmpString = ConfigurationSettings.AppSettings["connectionString"]) == null ||
tmpString == string.Empty)
{
fstr_out.WriteLine("ConfigurationSettings.AppSettings[\"connectionString\"] is empty!!!");
return(Result);
}
conn = new AseConnection(tmpString);
conn.InfoMessage += new AseInfoMessageEventHandler(conn_InfoMessage);
conn.StateChange += new System.Data.StateChangeEventHandler(conn_StateChange);
#if WITH_TRACE
conn.TraceEnter += new TraceEnterEventHandler(conn_TraceEnter);
conn.TraceExit += new TraceExitEventHandler(conn_TraceExit);
#endif
conn.Open();
fstr_out.WriteLine("AseConnection.ConnectionString: " + conn.ConnectionString);
fstr_out.WriteLine("AseConnection.ConnectionTimeout: " + conn.ConnectionTimeout);
fstr_out.WriteLine("AseConnection.Database: " + conn.Database);
fstr_out.WriteLine("AseConnection.NamedParameters: " + conn.NamedParameters.ToString().ToLower());
fstr_out.WriteLine("AseConnection.State: " + conn.State);
fstr_out.WriteLine("AseConnection.DriverVersion: " + AseConnection.DriverVersion);
//fstr_out.WriteLine("AseConnection.Language: "+AseConnection.Language);
fstr_out.WriteLine();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select @@spid";
if ((tmpObject = cmd.ExecuteScalar()) != null)
{
tmpString = Convert.ToString(tmpObject);
}
#if TEST_CHARSET
cmd.CommandText = "select cast(val as date) from T4 where GroupId=4 and Id=1";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable == null)
{
tmpDataTable = new DataTable();
}
else
{
tmpDataTable.Reset();
}
da.Fill(tmpDataTable);
#endif
#if TEST_EXECUTE_NON_QUERY
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.NamedParameters = false;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
cmd.CommandText = "update Victim set Val = ? where Id = ?";
cmd.Parameters.Add("Val", AseDbType.Integer);
cmd.Parameters.Add("Id", AseDbType.Integer);
for (int Id = 1; Id <= 5; Id += 2)
{
cmd.Parameters["Val"].Value = Id;
cmd.Parameters["Id"].Value = Id;
tmpInt = cmd.ExecuteNonQuery();
}
cmd.NamedParameters = true;
#endif
#if TEST_BLOB
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
FileStream
fs;
byte[]
Blob;
#if TEST_BLOB_SAVE
tmpString = "@FImage";
cmd.CommandText = "update TestTypes set FImage = " + tmpString;
cmd.Parameters.Clear();
cmd.Parameters.Add(tmpString, AseDbType.Image);
fs = new FileStream("welcome.bmp", FileMode.Open, FileAccess.Read);
Blob = new byte[fs.Length];
fs.Read(Blob, 0, Blob.Length);
cmd.Parameters[tmpString].Value = Blob;
tmpInt = cmd.ExecuteNonQuery();
#endif
cmd.Parameters.Clear();
cmd.CommandText = "select * from TestTypes";
reader = cmd.ExecuteReader();
do
{
if (reader.HasRows)
{
for (int i = 0; i < reader.FieldCount; ++i)
{
fstr_out.WriteLine(reader.GetName(i) + " GetDataTypeName(): \"" + reader.GetDataTypeName(i) + "\" GetFieldType(): \"" + reader.GetFieldType(i) + "\"");
}
tmpInt = reader.GetOrdinal("FImage");
while (reader.Read())
{
tmpString = "FromBlob.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = (byte[])reader["FImage"];
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
tmpString = "FromBlob_1.bmp";
if (File.Exists(tmpString))
{
File.Delete(tmpString);
}
Blob = new byte[reader.GetBytes(tmpInt, 0, null, 0, int.MaxValue)];
reader.GetBytes(tmpInt, 0, Blob, 0, Blob.Length);
fs = new FileStream(tmpString, FileMode.Create);
fs.Write(Blob, 0, Blob.Length);
fs.Close();
}
}
}while(reader.NextResult());
reader.Close();
#endif
#if TEST_SMTH
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from TestDate order by FDate";
cmd.Parameters.Clear();
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.Fill(tmpDataTable);
tmpString = "";
for (int i = 0; i < tmpDataTable.Rows.Count; ++i)
{
if (tmpString != string.Empty)
{
tmpString += " ";
}
tmpString += Convert.ToDateTime(tmpDataTable.Rows[i]["FDate"]).ToString("yyyy-MM-dd");
}
cmd.NamedParameters = false;
cmd.CommandText = "update TestTypes set FDatetime = ?";
cmd.Parameters.Clear();
cmd.Parameters.Add("FDate", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
cmd.NamedParameters = true;
cmd.CommandText = "update TestTypes set FDatetime = @FDatetime";
cmd.Parameters.Clear();
cmd.Parameters.Add("@FDatetime", AseDbType.DateTime).Value = DateTime.Now;
tmpInt = cmd.ExecuteNonQuery();
#endif
#if TEST_DATA_ADAPTER_FILL_SCHEMA
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select * from Staff";
if (da == null)
{
da = new AseDataAdapter(cmd);
}
else
{
da.SelectCommand = cmd;
}
if (tmpDataTable != null)
{
tmpDataTable.Reset();
}
else
{
tmpDataTable = new DataTable();
}
da.FillSchema(tmpDataTable, SchemaType.Source);
#endif
#if TEST_STORED_PROCEDURES
if (cmd == null)
{
cmd = conn.CreateCommand();
}
object[]
tmpObjects = new object[] { 5, 6 };
cmd.CommandType = CommandType.StoredProcedure;
cmd.NamedParameters = false;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "mathtutor";
for (int i = 0; i < tmpObjects.Length; ++i)
{
Parameter = new AseParameter();
Parameter.ParameterName = "Param" + i;
switch (Type.GetTypeCode(tmpObjects[i].GetType()))
{
case System.TypeCode.Int32:
{
Parameter.DbType = DbType.Int32;
break;
}
}
Parameter.Value = tmpObjects[i];
cmd.Parameters.Add(Parameter);
}
Parameter = new AseParameter();
Parameter.ParameterName = "Param3";
Parameter.DbType = DbType.Int32;
Parameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpInt = !Convert.IsDBNull(cmd.Parameters["Param3"].Value) ? Convert.ToInt32(cmd.Parameters["Param3"].Value) : Int32.MinValue;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_TestTypes_Decimal_10_6";
AseCommandBuilder.DeriveParameters(cmd);
cmd.Parameters["@FDecimal_10_6"].Value = 123.45m;
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{? = call sp_TestTypes_Decimal_10_6(? ,?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@RETURN_VALUE", AseDbType.Integer);
Parameter.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6", AseDbType.Decimal); //AseDbType.Numeric
//Parameter=new AseParameter();
//Parameter.ParameterName="@FDecimal_10_6";
//Parameter.DbType=DbType.Decimal;
//Parameter.AseDbType=AseDbType.Decimal; //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Input;
//Parameter.Precision=10;
//Parameter.Scale=6;
Parameter.Value = 123.45m;
cmd.Parameters.Add(Parameter);
Parameter = new AseParameter("@FDecimal_10_6_out", AseDbType.Decimal); //AseDbType.Numeric
Parameter.Direction = ParameterDirection.Output;
Parameter.Precision = 10;
Parameter.Scale = 6;
cmd.Parameters.Add(Parameter);
cmd.ExecuteNonQuery();
tmpDecimal = !Convert.IsDBNull(cmd.Parameters["@FDecimal_10_6_out"].Value) ? Convert.ToDecimal(cmd.Parameters["@FDecimal_10_6_out"].Value) : decimal.MinValue;
tmpDecimal = 2;
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "{call UpdateTestTypes(?)}";
cmd.NamedParameters = false;
Parameter = new AseParameter("@rate", AseDbType.Decimal);
Parameter.Direction = ParameterDirection.Input;
Parameter.Value = tmpDecimal;
cmd.Parameters.Add(Parameter);
/*
* if(cmd==null)
* cmd=conn.CreateCommand();
*
* cmd.CommandType=CommandType.StoredProcedure;
* cmd.CommandText="UpdateTestTypes";
* AseCommandBuilder.DeriveParameters(cmd);
* cmd.Parameters["@Decimal_18_4"].Value=tmpDecimal;
*/
tmpInt = cmd.ExecuteNonQuery();
cmd.CommandType = CommandType.StoredProcedure;
for (int size = 255; size <= 258; ++size)
{
cmd.CommandText = "sp_ReturnAndOutputVarChar" + size;
AseCommandBuilder.DeriveParameters(cmd);
cmd.ExecuteNonQuery();
tmpString = !Convert.IsDBNull(cmd.Parameters["@OutParam"].Value) ? Convert.ToString(cmd.Parameters["@OutParam"].Value) : "NULL";
}
conn.ChangeDatabase("master");
cmd.CommandText = "testdb..sp_ReturnAndOutput";
AseCommandBuilder.DeriveParameters(cmd);
conn.ChangeDatabase("testdb");
#endif
Transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
if (cmd == null)
{
cmd = conn.CreateCommand();
}
cmd.Transaction = Transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Staff_Save";
//cmd.CommandText="sp_TestTypes";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
conn.ChangeDatabase("veksel");
cmd.CommandText = "sp_CONTRACT_SAVE";
fstr_out.WriteLine("AseCommandBuilder.DeriveParameters(\"" + cmd.CommandText + "\")");
AseCommandBuilder.DeriveParameters(cmd);
foreach (AseParameter parameter in cmd.Parameters)
{
fstr_out.WriteLine("\tParameterIndex: " + parameter.ParameterIndex + Environment.NewLine +
"\tParameterName: " + parameter.ParameterName + Environment.NewLine +
"\tDirection: " + parameter.Direction + Environment.NewLine +
"\tDbType: " + parameter.DbType + Environment.NewLine +
"\tAseDbType: " + parameter.AseDbType + Environment.NewLine +
"\tSize: " + parameter.Size + Environment.NewLine +
"\tPrecision: " + parameter.Precision + Environment.NewLine +
"\tScale: " + parameter.Scale + Environment.NewLine +
"\tIsNullable: " + parameter.IsNullable.ToString().ToLower() + Environment.NewLine +
"\tSourceColumn: " + parameter.SourceColumn + Environment.NewLine +
"\tSourceVersion: " + parameter.SourceVersion + Environment.NewLine);
}
fstr_out.WriteLine();
Transaction.Rollback();
Transaction = null;
Result = 0;
}
catch (AseException eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Errors:" + Environment.NewLine + ErrorsToString(eException.Errors) + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine);
}
catch (Exception eException)
{
Console.WriteLine(eException.GetType().FullName + Environment.NewLine +
"Message: " + eException.Message + Environment.NewLine +
"Source: " + eException.Source + Environment.NewLine +
"StackTrace:" + Environment.NewLine + eException.StackTrace + Environment.NewLine +
"TargetSite: " + eException.TargetSite + Environment.NewLine +
"InnerException:" + Environment.NewLine + eException.InnerException.GetType().FullName + Environment.NewLine +
"InnerException.Message: " + eException.InnerException.Message + Environment.NewLine +
"InnerException.Source: " + eException.InnerException.Source + Environment.NewLine +
"InnerException.StackTrace:" + Environment.NewLine + eException.InnerException.StackTrace + Environment.NewLine +
"InnerException.TargetSite: " + eException.InnerException.TargetSite);
}
}
finally
{
if (Transaction != null)
{
try
{
Transaction.Rollback();
}
catch
{
;
}
}
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null && conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
if (fstr_out != null)
{
fstr_out.Close();
}
#if WITH_TRACE
if (_strmWriter != null)
{
_strmWriter.Close();
}
#endif
}
return(Result);
}
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;
}
public List <Account> getAccounts(string BVN)
{
// BVN = "2014225";
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 acct_no as AcctNo, Acct_name as AcctName from zenbase..zib_kyc_cust_information where bvn = '" + BVN + "' union select acct_no as AcctNo , Acct_name as AcctName from zenbase..zib_kyc_cust_information_wk where bvn = '" + BVN + "'";
sqlquery = "select acct_no as AcctNo, Acct_name as AcctName from zenbase..zib_kyc_cust_information where bvn = '" + BVN + "'";
cmd = new AseCommand(sqlquery, conn);
cmd.CommandTimeout = 0;
logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", "before conn open"));
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
logWriter.WriteErrorLog(string.Format("Conection section / {0}", "connection open"));
}
else
{
logWriter.WriteErrorLog(string.Format("Conection section / {0}", "connection closed"));
}
logWriter.WriteErrorLog(string.Format("execute Section / {0}", "Before execute"));
AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
logWriter.WriteErrorLog(string.Format("execute Section / {0}", "after execute"));
if (reader == null)
{
logWriter.WriteErrorLog(string.Format("reader section / {0}", "Reader is null"));
}
else
{
logWriter.WriteErrorLog(string.Format("reader section / {0}", "Reader is not null"));
}
while (reader.Read())
{
Account account = new Account();
// account.AccountNo = AcctNo;
account.AccountNumber = reader["AcctNo"].ToString();
account.AccountName = reader["AcctName"].ToString();
if (account.AccountNumber != "" || account.AccountNumber != null)
{
account.AccountStatus = getAccountStatus(account.AccountNumber).AccountStatus;
}
else
{
account.AccountStatus = "";
}
accountDetailList.Add(account);
}
}
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(accountDetailList);
}
public Account getCorporateAccountInfo(string acct_no, string initiator_number)
{
List <Loan> loans = new List <Loan>();
Account acct = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_cust_information";
//string sqltext = "select title_1,acct_no,acct_type,a.rsm_id as 'AccountRSMID'";
//sqltext += ",(select name from phoenix..ad_gb_rsm where employee_id = a.rsm_id) as 'AccountRSMName'";
//sqltext += ",b.rsm_id as 'RIMRSMID'";
//sqltext += ",(select name from phoenix..ad_gb_rsm where employee_id = b.rsm_id) as 'RIMRSMName'";
//sqltext += ",(select user_name from phoenix..ad_gb_rsm where employee_id = b.rsm_id) as 'RIMUSerName'";
//sqltext += ",(select d.staff_id from phoenix..ad_gb_rsm c, zib_applications_users d where c.user_name = d.user_id and ";
//sqltext += "employee_id = b.rsm_id) as 'StaffNo' ";
//sqltext += "from phoenix..dp_acct a, phoenix..rm_acct b ";
//sqltext += "where a.acct_no = @corperateacct_no ";// -----'1020041488'
//sqltext += "and a.rim_no = b.rim_no ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@psCorperateacct_no", acct_no);
command.Parameters.AddWithValue("@initiator_staffnumber", initiator_number);
reader = command.ExecuteReader();
while (reader.Read())
{
acct = new Account();
acct.account_name = reader["title_1"].ToString();
acct.acct_no = reader["acct_no"].ToString();
acct.acct_type = reader["acct_type"].ToString();
acct.domicile_branch_no = reader["BranchNo"].ToString();
acct.domicile_branch_name = reader["BranchName"].ToString();
acct.rim_no = reader["RIMNO"].ToString();
acct.rsm_id = reader["AccountRSMID"].ToString();
acct.rsm_name = reader["AccountRSMName"].ToString();
acct.rimrsm_id = reader["RIMRSMID"].ToString();
acct.rimrsm_name = reader["RIMRSMName"].ToString();
acct.rimrsm_username = reader["RIMUSerName"].ToString();
acct.rsm_staffno = reader["StaffNo"].ToString();
//New properties to be returned//
acct.sex = reader["sex"].ToString();
//acct.marital_status = reader["familystatus"].ToString();
acct.dob = reader["date_of_birth"].ToString();
acct.home_address = reader["home_address"].ToString();
acct.state_of_origin = reader["state_of_origin"].ToString();
acct.phonenumber = reader["phone_number"].ToString();
acct.email = reader["email_address"].ToString();
acct.next_of_kin = reader["next_of_kin"].ToString();
//New property to be returned - bvn - 03 NOV 2015//
acct.bvn = reader["bvn"].ToString();
acct.acct_type_desc = reader["acct_type_desc"].ToString();
//New property to be returned - bvn - 25 OCT 2016//
acct.initiator_branch = reader["initiator_branch"].ToString();
acct.initiator_branchcode = reader["initiator_branchcode"].ToString();
acct.initiator_phoenix_username = reader["initiator_phoenix_username"].ToString();
acct.initiator_phoenix_employee_id = reader["initiator_phoenix_employee_id"].ToString();
acct.initiator_phoenix_status = reader["initiator_phoenix_status"].ToString();
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(acct);
}
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 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 List <Loan> getApplicantLoans(string acct_no)
{
List <Loan> loans = new List <Loan>();
Loan loan = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_cust_exist_loans";
//string sqltext = "select (select description from phoenix..ad_ln_cls where class_code = a.class_code) as 'FacilityType',a.acct_no";
//sqltext += ",a.amt as 'FacilityAmount'";
//sqltext += ",a.period as 'RepaymentFreq'";
//sqltext += ",a.last_pmt_amt as 'LastRepaymentAmount'";
//sqltext += ",a.nxt_pmt_amt as 'NextRepaymentAmount'";
//sqltext += ",a.col_bal as 'CurrentBalance'";
//sqltext += ",a.mat_dt as 'FacilityMaturityDate'";
//sqltext += " from phoenix..ln_display a, phoenix..dp_acct b ";
//sqltext += "where b.acct_no = @applicantacct_no ";// -----'1020041488'
//sqltext += "and a.rim_no = b.rim_no ";
//sqltext += "and a.class_code != 585 ";
//sqltext += "and a.col_bal != 0 ";
//sqltext += "and a.status != 'Closed' ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
//command.Parameters.AddWithValue("@applicantacct_no", acct_no);
//AseParameter param = new AseParameter();
//param.ParameterName = "@applicantacct_no";
//param.Value = acct_no;
AseParameter param = new AseParameter();
param.ParameterName = "@psApplicantacct_no";
param.Value = acct_no;
command.Parameters.Add(param);
reader = command.ExecuteReader();
while (reader.Read())
{
loan = new Loan();
loan.FacilityType = reader["FacilityType"].ToString();
loan.acct_no = reader["acct_no"].ToString();
loan.FacilityAmount = reader["FacilityAmount"].ToString();
loan.RepaymentFreq = reader["RepaymentFreq"].ToString();
loan.LastRepaymentAmount = reader["LastRepaymentAmount"].ToString();
loan.NextRepaymentAmount = reader["NextRepaymentAmount"].ToString();
loan.FacilityMaturityDate = reader["FacilityMaturityDate"].ToString();
loan.CurrentBalance = reader["CurrentBalance"].ToString();
loans.Add(loan);
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(loans);
}
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();
}
}
/// <summary>
/// consulta la fecha de saldo diario y la fecha de cierre
/// </summary>
/// <param name="ParametrosIngreso"></param>
/// <param name="resultado"></param>
/// <returns></returns>
public List <DTO.clsDiasFeriados> CargarDataFechas(DTO.clsFiltro filtro, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["BDD_ANDALUCIA"].ConnectionString;
resultado = new DTO.clsResultado();
List <DTO.clsDiasFeriados> datos = new List <DTO.clsDiasFeriados>();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("anda_reporte..sp_cc_carga_datos_fechas", con))
{
cmd.CommandType = CommandType.StoredProcedure;
String strTimeoutComando = ConfigurationManager.AppSettings["TimeoutComandos"];
if (strTimeoutComando != null && strTimeoutComando.Length > 0)
{
cmd.CommandTimeout = int.Parse(strTimeoutComando);
}
cmd.Parameters.Add(new AseParameter("I_MODO", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Modo));
if (filtro.Filtro.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_FILTRO", AseDbType.VarChar, filtro.Filtro.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filtro));
}
cmd.Parameters.Add(new AseParameter("@o_fecha_sd", AseDbType.DateTime, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@o_fecha_cierre", AseDbType.DateTime, 128, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, ""));
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, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DTO.clsDiasFeriados DiasFeriados = new DTO.clsDiasFeriados();
DiasFeriados.Codigo = dr["ID_REGISTRO"] as Int32?;
DiasFeriados.Ciudad = dr["df_ciudad"] as Int32?;
DiasFeriados.Fecha = dr["df_fecha"] as DateTime?;
datos.Add(DiasFeriados);
}
}
// cmd.ExecuteNonQuery();
resultado.FechaSd = Convert.ToDateTime(cmd.Parameters["@o_fecha_sd"].Value.ToString() == ""?null: cmd.Parameters["@o_fecha_sd"].Value.ToString());
resultado.FechaCierre = Convert.ToDateTime(cmd.Parameters["@o_fecha_cierre"].Value.ToString() == ""?null: cmd.Parameters["@o_fecha_cierre"].Value.ToString());
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(datos);
}
public static void Main(string[] args)
{
var ConnectionSybase = "Data Source=10.1.1.36;Port=5000;Database=cafardb;Uid=sa;Pwd=cafsyb;Charset=cp850;";
var ConnectionMaster = "Data Source=10.1.4.10;Port=5000;Database=master;Uid=sa;Pwd=cafsybweb;Charset=cp850;";
var logger = NLog.Web.NLogBuilder.ConfigureNLog("nlog.config").GetCurrentClassLogger();
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
logger.Info("Prueba - Iniciando Backup and Restore diario");
Console.WriteLine("Prueba - Iniciando Backup and Restore diario");
using (Process p = Process.GetCurrentProcess())
p.PriorityClass = ProcessPriorityClass.High;
try
{
using (AseConnection _connectionSybase = new AseConnection(ConnectionSybase))
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
try
{
_connectionSybase.Open();
}
catch (Exception ex)
{
logger.Error("Error al conectarse con RDC-W2K12");
Console.WriteLine("Error al conectarse con RDC-W2K12");
logger.Error(ex.Message);
Console.WriteLine(ex.Message);
}
if (_connectionSybase.State == System.Data.ConnectionState.Open)
{
var backupDiario = "cafardb" + DateTime.Now.Date.ToString("yyyyMMdd");
var pathBackupProd = "C:\\Users\\Administrator\\Desktop\\COMPARTIDO RDC-W2K12\\" + backupDiario;
logger.Info("Conexión establecida");
Console.WriteLine("Conexión establecida");
if (File.Exists(pathBackupProd))
{
logger.Error("Ya existe " + pathBackupProd);
Console.WriteLine("Ya existe " + pathBackupProd);
}
else
{
var sqlBackup = "dump database cafardb to " + '"' + pathBackupProd + '"' + " with notify = client";
var command = _connectionSybase.CreateCommand();
command.CommandText = sqlBackup;
logger.Info("Iniciando dump");
Console.WriteLine("Iniciando dump");
try
{
void captureResult(object sender, AseInfoMessageEventArgs aseInfoMessageEventArgs)
{
Console.WriteLine(aseInfoMessageEventArgs.Message);
}
_connectionSybase.InfoMessage += captureResult;
command.ExecuteNonQuery();
logger.Info("Dump finalizado");
Console.WriteLine("Dump finalizado");
var pathBackupWeb = "\\\\10.1.2.10\\compartidosybase15\\" + backupDiario;
if (File.Exists(pathBackupWeb))
{
logger.Error("Ya existe " + pathBackupWeb);
Console.WriteLine("Ya existe " + pathBackupWeb);
}
else
{
logger.Info("Copiando backup en SYBASE15");
Console.WriteLine("Copiando backup en SYBASE15");
File.Copy(pathBackupProd, pathBackupWeb);
logger.Info("Copia de backup finalizada");
Console.WriteLine("Copia de backup finalizada");
try
{
using (AseConnection connectionMaster = new AseConnection(ConnectionMaster))
{
logger.Info("Conectando a Sybase15");
Console.WriteLine("Conectando a Sybase15");
var sqlRestore = "load database CafarWeb from " + '"' + pathBackupWeb + '"';
connectionMaster.Open();
logger.Info("Conexion Establecida con Sybase15");
Console.WriteLine("Conexion Establecida con Sybase15");
var procesos = connectionMaster.Query <Procces>("sp_who sa").ToList();
var procesoSaCafarNov = procesos.Where(x => x.dbname == "CafarNov" || x.dbname == "CafarWeb");
if (procesoSaCafarNov.Any())
{
logger.Info("Limpiando procesos sa en SYBASE15");
Console.WriteLine("Limpiando procesos sa en SYBASE15");
}
foreach (Procces proceso in procesoSaCafarNov)
{
try
{
connectionMaster.Execute("kill " + proceso.spid);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
logger.Error(ex.Message);
}
}
//_connectionWeb.Open();
logger.Info("Iniciando Restore");
Console.WriteLine("Iniciando Restore");
connectionMaster.Execute(sqlRestore);
logger.Info("Restore Finalizado en Web");
Console.WriteLine("Restore Finalizado en Web");
connectionMaster.Execute("Online database CafarWeb");
logger.Info("Bases de Datos puesta OnLine");
Console.WriteLine("Bases de Datos puesta OnLine");
connectionMaster.Close();
connectionMaster.Dispose();
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
Console.WriteLine(ex.Message);
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
Console.WriteLine(ex.Message);
}
}
_connectionSybase.Close();
}
}
}
catch (Exception ex)
{
logger.Error(ex.Message);
logger.Error("Error");
Console.WriteLine(ex.Message);
}
}
/// <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;
}
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 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 GlobalLimit getRimInfo(string schemerimno, string schemeclasscode)
{
GlobalLimit gLimit = null;
AseConnection conn = null;
AseCommand command = null;
AseDataReader reader = null;
StringBuilder s = new StringBuilder();
StringBuilder output = new StringBuilder();
string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString;
string sqltext = "zsp_loans_GlobalLimit";
//string sqltext = "select sum(amt) as 'GlobalLimit'";
//sqltext += ",sum(undisbursed) as 'GlobalBalance'";
//sqltext += ",rsm_id as 'RIMRSMID' ";
//sqltext += "from phoenix..ln_umb ";
//sqltext += "where rim_no = @SchemeRimNo ";
//sqltext += "and class_code = @SchemeClassCode ";
//sqltext += "and status = 'Active' ";
try
{
using (conn = new AseConnection(cnstring))
{
conn.Open();
using (command = new AseCommand(sqltext, conn))
{
//command.Parameters.AddWithValue("@SchemeRimNo", int.Parse(schemerimno));
//command.Parameters.AddWithValue("@SchemeClassCode", int.Parse(schemeclasscode));
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@pnSchemeRimNo", int.Parse(schemerimno));
command.Parameters.AddWithValue("@pnSchemeClassCode", int.Parse(schemeclasscode));
reader = command.ExecuteReader();
while (reader.Read())
{
gLimit = new GlobalLimit();
gLimit.Limit = reader["GlobalLimit"].ToString();
gLimit.GlobalBalance = reader["GlobalBalance"].ToString();
}
}
}
}
catch (Exception ex)
{
throw;
}
finally
{
command.Dispose();
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
return(gLimit);
}
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;
}
/// <summary>
/// Consulta de Aplicacion, puede ser individual (por ID) o con filtros y órdenes.
/// </summary>
/// <param name="filtro">El filtro indica el modo, página, condiciones y orden de la consulta. Si viene el ID y modo=0, se consulta el registro específico</param>
/// <param name="resultado">Contiene el Código, Mensaje y Número de páginas obtenidos como resultados de la consulta</param>
/// <returns>Retorna la lista , utilizando una lista o arreglo</returns>
public List <DTO.clsDiasFeriados> DiasHabilesConsultar(DTO.clsFiltro filtro, out DTO.clsResultado resultado)
{
string strConexion = ConfigurationManager.ConnectionStrings["BDD_ANDALUCIA"].ConnectionString;
List <DTO.clsDiasFeriados> datos = new List <DTO.clsDiasFeriados>();
resultado = new DTO.clsResultado();
try
{
using (AseConnection con = new AseConnection(strConexion))
{
con.Open();
using (AseCommand cmd = new AseCommand("Aplicacion_QRY", con))
{
cmd.CommandType = CommandType.StoredProcedure;
if (filtro.Id != null || filtro.Id > 0)
{
cmd.Parameters.Add(new AseParameter("I_ID", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Id));
}
cmd.Parameters.Add(new AseParameter("I_MODO", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Modo));
cmd.Parameters.Add(new AseParameter("I_FILAS", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filas));
if (filtro.Filtro.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_FILTRO", AseDbType.VarChar, filtro.Filtro.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Filtro));
}
if (filtro.Orden.Length > 0)
{
cmd.Parameters.Add(new AseParameter("I_ORDEN", AseDbType.VarChar, filtro.Orden.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Orden));
}
cmd.Parameters.Add(new AseParameter("I_IR_A_PAGINA", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, filtro.Pagina));
cmd.Parameters.Add(new AseParameter("@O_ROWS", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
cmd.Parameters.Add(new AseParameter("@O_PAGES", AseDbType.Integer, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Default, 0));
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, ""));
using (AseDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
DTO.clsDiasFeriados DiasFeriados = new DTO.clsDiasFeriados();
DiasFeriados.Codigo = dr["ID_REGISTRO"] as Int32?;
DiasFeriados.Ciudad = dr["df_ciudad"] as Int32?;
DiasFeriados.Fecha = dr["df_fecha"] as DateTime?;
datos.Add(DiasFeriados);
}
}
resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value);
resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString();
resultado.TotalPaginas = (cmd.Parameters["@o_fecha_sd"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_PAGES"].Value) : 0;
resultado.TotalRegistros = (cmd.Parameters["@O_ROWS"].Value != DBNull.Value) ? Convert.ToInt32(cmd.Parameters["@O_ROWS"].Value) : 0;
}
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 AplicacionConsultar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0);
}
resultado.Resultado = -10;
resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"];
}
return(datos);
}
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;
}