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; }
public AseDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) { AseConnection connection = new AseConnection(this.connectionString); connection.Open(); AseCommand command1 = this.BuildQueryCommand(connection, storedProcName, parameters); command1.CommandType = CommandType.StoredProcedure; return(command1.ExecuteReader(CommandBehavior.CloseConnection)); }
public Account getAccountStatus(string AcctNo) { // BVN = "2014225"; Account account = new Account(); //string result = ""; LogWriter logWriter = new LogWriter(); AseCommand cmd = null; AseConnection conn = null; string sqlquery = null; string cnstring = ConfigurationManager.ConnectionStrings["phoenixConnectionString"].ConnectionString; List <Account> accountDetailList = new List <Account>(); try { conn = new AseConnection(cnstring); sqlquery = " SELECT status as status from phoenix..dp_acct WHERE acct_no = '" + AcctNo + "'"; cmd = new AseCommand(sqlquery, conn); cmd.CommandTimeout = 0; conn.Open(); AseDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); if (reader == null) { logWriter.WriteErrorLog(string.Format("Reader empty !", "reader is empty")); account.AccountStatus = ""; } while (reader.Read()) { account.AccountStatus = reader["status"].ToString(); } } catch (Exception ex) { logWriter.WriteErrorLog(string.Format("Exception Message! / {0}", ex.Message)); } finally { //cmd.Dispose(); if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); conn.Dispose(); } } return(account); }
public IHttpActionResult GetAccountDetails(string accountNumber) { List <AccountDetailsModel> accountDetails = new List <AccountDetailsModel>(); string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString(); string query = "select c.title_1 as acct_name, " + "b.gsm_no as Mobile_No, " + "c.class_code as class_code, " + "a.home_address as home_address " + "from zenbase..zib_ecustomer b ," + "zenbase..zib_kyc_corporate_signatories a, " + "phoenix..dp_acct c " + "where a.acct_no = b.acct_no " + "and a.acct_no = c.acct_no " + "and a.acct_no = '" + accountNumber + "'"; try { logwriter.WriteTolog("In try"); using (AseConnection connection = new AseConnection(connectionString)) { AseCommand command = new AseCommand(query, connection); connection.Open(); AseDataReader reader = command.ExecuteReader(); while (reader.Read()) { var accountDt = new AccountDetailsModel() { mobile_no = reader["Mobile_No"].ToString().Trim(), home_address = reader["home_address"].ToString().Trim(), class_code = reader["class_code"].ToString().Trim(), acct_name = reader["acct_name"].ToString().Trim() }; accountDetails.Add(accountDt); } reader.Close(); } } catch (Exception ex) { logwriter.WriteTolog("Error in inner exception: " + ex.InnerException); logwriter.WriteTolog("Error in message: " + ex.Message); } return(Ok(accountDetails)); }
public Tables Get(Database database) { Tables tables = new Tables(database); double tableCount = GetTablesCount(); double tableIndex = 0; int stats = 0; using (AseConnection conn = new AseConnection(connectioString)) { using (AseCommand command = new AseCommand("select O.sysstat2, O.id, O.name, U.name as Owner FROM sysobjects O INNER JOIN sysusers U ON U.uid = O.uid where type = 'U' ORDER BY O.name", conn)) { conn.Open(); using (AseDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Table table = new Table(database); table.Id = (int)reader["id"]; table.Name = reader["name"].ToString(); table.Owner = reader["Owner"].ToString(); stats = (int)reader["sysstat2"]; if ((stats & 8192) == 8192) { table.LockType = Table.LockTypeEnum.LockAllPages; } if ((stats & 16384) == 16384) { table.LockType = Table.LockTypeEnum.LockDataPages; } if ((stats & 32768) == 32768) { table.LockType = Table.LockTypeEnum.LockDataRows; } //table.Constraints = (new GenerateConstraint(connectioString,tableFilter)).Get(table); /*Primero debe ir las constraints*/ //table.Columns = GetColumns(table); //table.Indexes = (new GenerateIndex(connectioString,tableFilter)).Get(table); tables.Add(table); tableIndex++; OnTableProgress(this, new ProgressEventArgs((tableIndex / tableCount) * 100)); } } } } if (objectFilter.OptionFilter.FilterTrigger) { SetTriggers(tables); } SetColumnsDatabase(tables); //tables.Sort(); tables.ToSQL(); return(tables); }
/// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ /// <summary> GET: Student/Delete/5 </summary> /// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ public ClaimsSubmission GetAllClaims(ClaimsSubmission claimSub, string tableName) { ClaimDataAccessLayer.TableName = tableName; using (var conn = new AseConnection(HomeController.SybaseConnString)) { int rowsRead = 0; try { conn.Open(); AseCommand cmd = new AseCommand("SP_XC_CLAIM_REJECTION_READ", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new AseParameter("@TABLE_NAME", TableName)); rowsRead = cmd.ExecuteNonQuery(); if (rowsRead > 0) { AseDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string claimid = reader["CLCL_ID"].ToString(); string createdByUser = reader["CREATED_BY_USER"].ToString(); string createdByService = reader["CREATED_BY_SERVICE"].ToString(); string createdDate = reader["CREATED_DATE"].ToString(); string prprNpi = reader["PRPR_NPI"].ToString(); Claim newClaim = new Claim() { ClaimId = claimid, CreatedByUser = createdByUser, CreatedByService = createdByService, CreatedDate = createdDate, PrprNpi = prprNpi }; claimSub.ClaimList.Add(newClaim); } } } catch (Exception e) { System.Console.WriteLine("**ERROR READING CLAIMS** : {0}", e.ToString()); } return(claimSub); } }
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); }
/// <summary> /// 读取KEY VALUE值 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public override KeyValueCollection ExecuteKVCollection(string sql, KdtParameterCollection parameters) { try { KeyValueCollection entity = new KeyValueCollection(); // 执行SQL命令 using (AseCommand cmd = new AseCommand(ReplaceSqlText(sql, parameters), _sybaseCn)) { InitCommand(cmd); // 初始化 // 赋值参数 var hasConvertParams = ConvertToSqlParameter(parameters); foreach (var item in hasConvertParams) { cmd.Parameters.Add(item.Value); } // 执行填充数据 using (AseDataReader reader = cmd.ExecuteReader()) { entity = GetEntity(reader); // 反射参数值 ReflectParamValue(parameters, hasConvertParams); reader.Close(); reader.Dispose(); } cmd.Cancel(); cmd.Dispose(); } return(entity); } catch (AseException me) { KdtLoger.Instance.Error(me); throw new DataException(me.Message); } catch (Exception ex) { KdtLoger.Instance.Error(ex); throw new DataException(string.Format("执行SQL查询,返回数据集合错误,原因为:{0}", ex.Message)); } }
public AseDataReader ExecuteReader(string SQLString, params AseParameter[] cmdParms) { AseDataReader reader; AseConnection conn = new AseConnection(this.connectionString); AseCommand cmd = new AseCommand(); try { this.PrepareCommand(cmd, conn, null, SQLString, cmdParms); cmd.Parameters.Clear(); reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (OracleException exception1) { throw new Exception(exception1.Message); } return(reader); }
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); }
public void SetTriggers(Tables tables) { string text = ""; using (AseConnection conn = new AseConnection(connectioString)) { conn.Open(); using (AseCommand command = new AseCommand(GetSQLTrigger(), conn)) { using (AseDataReader reader = command.ExecuteReader()) { while (reader.Read()) { TableTrigger trigger = new TableTrigger(tables[reader["TableName"].ToString()]); trigger.Id = (int)reader["id"]; trigger.Name = reader["name"].ToString(); trigger.Owner = reader["owner"].ToString(); tables[reader["TableName"].ToString()].Triggers.Add(trigger); } } } foreach (Table table in tables) { for (int index = 0; index < table.Triggers.Count; index++) { using (AseCommand command = new AseCommand("sp_helptext '" + table.Triggers[index].Name + "'", conn)) { using (AseDataReader reader = command.ExecuteReader()) { text = ""; reader.NextResult(); while (reader.Read()) { text += reader["text"].ToString(); } table.Triggers[index].Text = text; } } } } } }
private void SetColumnsDatabase(Tables tables) { using (AseConnection conn = new AseConnection(connectioString)) { using (AseCommand command = new AseCommand(GetSQLColumns(), conn)) { conn.Open(); using (AseDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Table table = tables[reader["TableName"].ToString()]; Column col = new Column(table); col.Id = (short)reader["colid"]; col.Identity = ((int)reader["IsIdentity"]) == 1; col.Name = reader["Name"].ToString(); col.Nullable = ((int)reader["IsNullable"]) == 1; col.Precision = (byte)reader["Precision"]; col.Scale = (byte)reader["Scale"]; if (!String.IsNullOrEmpty(reader["Size"].ToString())) { col.Size = (int)reader["Size"]; } col.Type = reader["Type"].ToString(); col.HasIndexDependencies = false; if (objectFilter.OptionFilter.FilterColumnPosition) { col.Position = table.Columns.Count + 1; } else { col.Position = 0; } table.Columns.Add(col); } } } } }
public async static Task <Tuple <int, string, string> > getAccountType(string account_number) { string acct_type = string.Empty; string acct_name = string.Empty; string query = " SELECT a.acct_type AS acct_type, " + " a.title_1 AS acct_name " + " FROM phoenix..dp_acct a, " + " phoenix..rm_acct c " + " WHERE a.acct_no = '" + account_number + "'" + " AND a.rim_no = c.rim_no "; using (AseConnection conn = new AseConnection(ConfigurationManager.ConnectionStrings["sybaseconnection"].ConnectionString)) { using (AseCommand cmd = new AseCommand(query, conn)) { try { conn.Open(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; cmd.CommandTimeout = 0; AseDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { acct_name = reader["acct_name"].ToString(); acct_type = reader["acct_type"].ToString(); } reader.Close(); return(await Task.FromResult(Tuple.Create(0, acct_name, acct_type))); } catch (Exception ex) { return(Tuple.Create(-1, string.Empty, ex.Message)); } }; }; }
public static async Task <Tuple <int, string, string, string> > getStaffPhoenixDetails(int employee_id) { string staffStatus = string.Empty; string employee_number = string.Empty; string fullname = string.Empty; string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString(); string queryString = " SELECT a.staff_id AS employee_number, r.status AS status, a.fullname AS fullname " + " FROM phoenix..ad_gb_rsm r, " + " ZENBASE..zib_applications_users a " + " WHERE r.user_name = a.user_id " + " AND r.employee_id = " + employee_id; try { using (AseConnection connection = new AseConnection(connectionString)) { using (AseCommand command = new AseCommand(queryString, connection)) { command.CommandTimeout = 0; connection.Open(); AseDataReader reader = command.ExecuteReader(); while (reader.Read()) { staffStatus = reader["status"].ToString().Trim(); employee_number = reader["employee_number"].ToString().Trim(); fullname = reader["fullname"].ToString().Trim(); } reader.Close(); return(await Task.FromResult(Tuple.Create(0, staffStatus, employee_number, fullname))); } } } catch (Exception ex) { return(Tuple.Create(-1, ex.Message, employee_number, fullname)); } }
/// <summary> /// Consulta de CorreoAplicacion, 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.clsCorreoAplicacion> CorreoAplicacionConsultar(DTO.clsFiltro filtro, out DTO.clsResultado resultado) { string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString; List <DTO.clsCorreoAplicacion> datos = new List <DTO.clsCorreoAplicacion>(); resultado = new DTO.clsResultado(); try { using (AseConnection con = new AseConnection(strConexion)) { con.Open(); using (AseCommand cmd = new AseCommand("anda_reporte..CORREOAPLICACION_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.clsCorreoAplicacion CorreoAplicacion = new DTO.clsCorreoAplicacion(); CorreoAplicacion.Codigo = dr["CA_CODIGO"] as Int32?; CorreoAplicacion.CodigoAplicacion = dr["CA_CODIGO_APLICACION"] as String; CorreoAplicacion.UserOrigen = dr["CA_USER_ORIGEN"] as String; CorreoAplicacion.ClaveOrigen = dr["CA_CLAVE_ORIGEN"] as String; CorreoAplicacion.SmtpServer = dr["CA_SMTP_SERVER"] as String; CorreoAplicacion.SmtpServerPort = dr["CA_SMTP_SERVER_PORT"] as Int32?; CorreoAplicacion.SmtpServerSsl = dr["CA_SMTP_SERVER_SSL"] as String; CorreoAplicacion.PlantillaHtml = dr["CA_PLANTILLAHTML"] as String; CorreoAplicacion.Logotipo = dr["CA_LOGOTIPO"] as String; CorreoAplicacion.Firma = dr["CA_FIRMA"] as String; CorreoAplicacion.Destino = dr["CA_DESTINO"] as String; datos.Add(CorreoAplicacion); } } resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value); resultado.Mensaje = cmd.Parameters["@O_RETMSG"].Value.ToString(); resultado.TotalPaginas = (cmd.Parameters["@O_PAGES"].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 CorreoAplicacionConsultar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0); } resultado.Resultado = -10; resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"]; } return(datos); }
/// <summary> /// Actualización de CorreoAplicacion por ID /// </summary> /// <param name="CorreoAplicacion">CorreoAplicacion que se desea actualizar</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 DTO.clsCorreoAplicacion CorreoAplicacionActualizar(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_UPD", 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)); } if (CorreoAplicacion.CodigoAplicacion != null) { cmd.Parameters.Add(new AseParameter("@I_CodigoAplicacion", AseDbType.VarChar, CorreoAplicacion.CodigoAplicacion.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.CodigoAplicacion)); } if (CorreoAplicacion.UserOrigen != null) { cmd.Parameters.Add(new AseParameter("@I_UserOrigen", AseDbType.VarChar, CorreoAplicacion.UserOrigen.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.UserOrigen)); } if (CorreoAplicacion.ClaveOrigen != null) { cmd.Parameters.Add(new AseParameter("@I_ClaveOrigen", AseDbType.VarChar, CorreoAplicacion.ClaveOrigen.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.ClaveOrigen)); } if (CorreoAplicacion.SmtpServer != null) { cmd.Parameters.Add(new AseParameter("@I_SmtpServer", AseDbType.VarChar, CorreoAplicacion.SmtpServer.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServer)); } if (CorreoAplicacion.SmtpServerPort != null) { cmd.Parameters.Add(new AseParameter("@I_SmtpServerPort", AseDbType.Integer, -1, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServerPort)); } if (CorreoAplicacion.SmtpServerSsl != null) { cmd.Parameters.Add(new AseParameter("@I_SmtpServerSsl", AseDbType.Char, CorreoAplicacion.SmtpServerSsl.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, CorreoAplicacion.SmtpServerSsl)); } 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()) { if (dr.Read()) { CorreoAplicacion.Codigo = dr["CA_CODIGO"] as Int32?; CorreoAplicacion.CodigoAplicacion = dr["CA_CODIGO_APLICACION"] as String; CorreoAplicacion.UserOrigen = dr["CA_USER_ORIGEN"] as String; CorreoAplicacion.ClaveOrigen = dr["CA_CLAVE_ORIGEN"] as String; CorreoAplicacion.SmtpServer = dr["CA_SMTP_SERVER"] as String; CorreoAplicacion.SmtpServerPort = dr["CA_SMTP_SERVER_PORT"] as Int32?; CorreoAplicacion.SmtpServerSsl = dr["CA_SMTP_SERVER_SSL"] as String; } } 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 CorreoAplicacionActualizar... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 0); } resultado.Resultado = -10; resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"]; } return(CorreoAplicacion); }
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); }
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); }
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); }
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"); } } }
/// <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); }
/// <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); }
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); }
/// <summary> /// Resumen de funcionalidad /// </summary> /// <param name="Aplicacion">El nuevo requerimiento a ser creado</param> /// <param name="resultado">Los datos del resultado obtenidos después de su inserción</param> /// <returns>El resultado de la operación, contiene el código de error generado por la operación de base de datos. Si retorna 0 es OK</returns> public DTO.clsAplicacion AplicacionCrear(DTO.clsAplicacion Aplicacion, out DTO.clsResultado resultado) { string strConexion = ConfigurationManager.ConnectionStrings["CADENA"].ConnectionString; string strMsg = "OK"; resultado = new DTO.clsResultado(); try { using (AseConnection con = new AseConnection(strConexion)) { con.Open(); using (AseCommand cmd = new AseCommand("Aplicacion_ADD", con)) { cmd.CommandType = CommandType.StoredProcedure; if (Aplicacion.Codigo != null) { cmd.Parameters.Add(new AseParameter("@I_Codigo", AseDbType.VarChar, Aplicacion.Codigo.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Codigo)); } if (Aplicacion.Nombre != null) { cmd.Parameters.Add(new AseParameter("@I_Nombre", AseDbType.VarChar, Aplicacion.Nombre.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Nombre)); } if (Aplicacion.Descripcion != null) { cmd.Parameters.Add(new AseParameter("@I_Descripcion", AseDbType.VarChar, Aplicacion.Descripcion.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Descripcion)); } if (Aplicacion.Estado != null) { cmd.Parameters.Add(new AseParameter("@I_Estado", AseDbType.Char, Aplicacion.Estado.Length, ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Default, Aplicacion.Estado)); } 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()) { if (dr.Read()) { Aplicacion.Codigo = dr["AP_CODIGO"] as String; Aplicacion.Nombre = dr["AP_NOMBRE"] as String; Aplicacion.Descripcion = dr["AP_DESCRIPCION"] as String; Aplicacion.Estado = dr["AP_ESTADO"] as String; } } resultado.Resultado = Convert.ToInt32(cmd.Parameters["@O_RETVAL"].Value); resultado.Mensaje = strMsg = 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 AplicacionCrear... " + " Descripción= " + error.Message + " Stack: " + error.StackTrace, EventLogEntryType.Error, 16); } resultado.Resultado = -10; resultado.Mensaje = ConfigurationManager.AppSettings["ErrorInternoMensaje"]; } return(Aplicacion); }
void BtnTestConnClick(object sender, EventArgs e) { string connectionString = txtConnx.Text; // OleDbConnection conn = new OleDbConnection(connectionString); AseConnection conn = new AseConnection(connectionString); try { conn.Open(); AseCommand command = new AseCommand("select max(ID) from QA_PAC_TIMING where TEST_PACKAGE='PAR.TPK.0000949'",conn); command.CommandTimeout = 99999; object scale = command.ExecuteScalar(); AseDataReader data= command.ExecuteReader(); string result = data.GetString(0); } catch (Exception ex) { MessageBox.Show(ex.Message+"\r\n\r\n"+ex.StackTrace); } finally { conn.Close(); } }
public IHttpActionResult GetStaffPhoenixDetails(string employeeID) { List <EmployeeInformation> employees = new List <EmployeeInformation>(); string connectionString = ConfigurationManager.ConnectionStrings["sybaseconnection"].ToString(); string queryString = "select b.user_id as user_id," + "b.status as status," + "convert(varchar(4), a.branch_no) as branch_no," + "a.employee_id as employee_id," + "c.name_1 as name_1," + "a.empl_class_code as empl_class_code," + "convert(varchar,a.last_logon_dt) as last_logon_dt," + "a.state as state," + "a.user_name as user_name," + "a.name as name," + "b.acct_no as acct_no," + "b.email_address as email_address " + "FROM phoenix..ad_gb_rsm a ," + "zenbase..zib_applications_users b ," + "phoenix..ad_gb_branch c " + " WHERE b.user_id = a.user_name " + " AND A.branch_no = c.branch_no " + " AND b.staff_id = '" + employeeID + "'"; try { logwriter.WriteTolog("In try"); using (AseConnection connection = new AseConnection(connectionString)) { AseCommand command = new AseCommand(queryString, connection); connection.Open(); AseDataReader reader = command.ExecuteReader(); while (reader.Read()) { var employeeinfo = new EmployeeInformation() { user_id = reader["user_id"].ToString(), status = reader["status"].ToString().Trim(), branch_no = reader["branch_no"].ToString(), employee_id = reader["employee_id"].ToString(), name_1 = reader["name_1"].ToString(), empl_class_code = reader["empl_class_code"].ToString(), last_logon_dt = reader["last_logon_dt"].ToString(), state = reader["state"].ToString(), user_name = reader["user_name"].ToString(), name = reader["name"].ToString(), acct_no = reader["acct_no"].ToString(), email_address = reader["email_address"].ToString() }; employees.Add(employeeinfo); } reader.Close(); } } catch (Exception ex) { logwriter.WriteTolog("Error in inner exception: " + ex.InnerException); logwriter.WriteTolog("Error in message: " + ex.Message); } return(Ok(employees)); }
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); }
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 async Task <string> PullNostroVostroData() { Library library = new Library(); try { LogManager.SaveLog("Task Start in NostroVostroLibrary"); var ReconType = await repoReconTypeRepository.GetAsync(c => c.WSReconName == "NostroVostro"); int ReconTypeId = ReconType.ReconTypeId; var AccountSources = await repoadmSourceAccountRepository.GetManyAsync(c => c.ReconTypeId == ReconTypeId); var listofAccountsource1 = AccountSources.Where(c => c.SourceName == "Source 1" && c.AcctNo != null); string acctlistSource1 = string.Empty; string acctlistSource2 = string.Empty; int dt1 = 0; // int dt2 = 0; foreach (var li in listofAccountsource1) { acctlistSource1 += "'" + li.AcctNo + "'" + ", "; dt1 = (int)li.DataSourceId; } int index = acctlistSource1.LastIndexOf(','); if (!string.IsNullOrEmpty(acctlistSource1)) { acctlistSource1 = (acctlistSource1.Remove(index, 1)); } var dtSouceCon1 = await repoadmReconDataSourcesRepository.GetAsync(c => c.ReconDSId == dt1); #region Source 1 #region // Sybase Server below if (dtSouceCon1.DatabaseType == "SYBASE") { try { LogManager.SaveLog("PullDataNostro for Sybase Start in NostroVostroLibrary"); var value1 = string.Empty; value1 = string.Empty; var value2 = string.Empty; value2 = string.Empty; int scriptExecTtype = 0; string FromDateParam = string.Empty; string ToDateParam = string.Empty; string LastRecordId = string.Empty; string CBSRecordCount = string.Empty; string MaxTransDate = string.Empty; string dateTest = string.Empty; string ReconDate = string.Empty; var controlTable = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "CBSNostroTrans"); if (controlTable != null) { FromDateParam = controlTable.FromDateParam == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.FromDateParam)); FromDateParam = "'" + FromDateParam + "'"; ToDateParam = controlTable.ToDateParam == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.ToDateParam)); ToDateParam = "'" + ToDateParam + "'"; ReconDate = controlTable.LastTransDate == null?DateTime.Now.ToString() : string.Format("{0:yyyyMMdd}", Convert.ToDateTime(controlTable.LastTransDate)); ReconDate = "'" + ReconDate + "'"; LastRecordId = controlTable.LastRecordId; } else { LastRecordId = "0"; FromDateParam = "'20170901'"; ToDateParam = "'20170923'"; ReconDate = "'20170923'"; } string pdt = "";// parametername.Split(' ')[1]; List <AseParameter> parameterPasses = new List <AseParameter>() { new AseParameter() { ParameterName = pdt, AseDbType = AseDbType.VarChar, Value = value1 }, new AseParameter() { ParameterName = pdt, AseDbType = AseDbType.VarChar, Value = value2 } }; string connstring = System.Configuration.ConfigurationManager.AppSettings["sybconnection"].ToString(); connstring = connstring.Replace("{{Data Source}}", dtSouceCon1.ipAddress); connstring = connstring.Replace("{{port}}", dtSouceCon1.PortNumber); connstring = connstring.Replace("{{database}}", dtSouceCon1.DatabaseName); connstring = connstring.Replace("{{uid}}", dtSouceCon1.UserName); connstring = connstring.Replace("{{pwd}}", library.Decrypt(dtSouceCon1.Password)); DataSet dsGetData = new DataSet(); using (AseConnection theCons = new AseConnection(connstring)) { DataSet ds = new DataSet(); try { theCons.Open(); if (theCons.State.ToString() == "Open") { LogManager.SaveLog("Sybase Connection open for Ip " + dtSouceCon1.ipAddress); } else { LogManager.SaveLog("Sybase Connection not Open for Ip " + dtSouceCon1.ipAddress); } } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured NostroVostroLibrary Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); } try { var CurrencyList = AccountSources.Where(c => c.SourceName == "Source 1" && c.Currency != null && c.Currency != string.Empty); int gocount = 0; foreach (var cur in CurrencyList) { //87572420 string currency = "'" + cur.Currency + "'";; string Account = "'" + cur.AcctNo + "'";; string SqlString = ReconType.Source1Script.Replace("{Account}", Account) .Replace("{Currency}", currency) .Replace("{LastRecordId}", LastRecordId) .Replace("{ReconDate}", ReconDate); AseCommand cmd = new AseCommand(SqlString, theCons); cmd.Connection = theCons; cmd.CommandText = SqlString; cmd.CommandTimeout = 0; //i.e check if the parameters are not null, if the are null, that means scriptExecTtype = 1, // would be using CommandText not store procdure if (!string.IsNullOrWhiteSpace(value1) && !string.IsNullOrWhiteSpace(value1)) { if (parameterPasses != null) { cmd.Parameters.AddRange(parameterPasses.ToArray()); var gggg = (parameterPasses.ToArray()); } } else { scriptExecTtype = 1; } cmd.CommandType = scriptExecTtype == 0 ? CommandType.StoredProcedure : CommandType.Text; AseDataReader reader = cmd.ExecuteReader(); ds.EnforceConstraints = false; ds.Load(reader, LoadOption.OverwriteChanges, "Results"); var returndata = ds.Tables["Results"]; LogManager.SaveLog("PullDataCBSNostroTrans for Sybase returndata.Rows.Count " + returndata.Rows.Count); if (returndata.Rows.Count > 0) { var CBSNostroTransTBL = new CBSNostroTran(); var CBSNostroTransTBLErr = new CBSNostroTransError(); int count = 0; int countTrans = 0; for (int col = 0; col < returndata.Rows.Count; col++) { try { CBSNostroTransTBL.AcctNo = returndata.Rows[col][0] == null ? null : returndata.Rows[col][0].ToString(); CBSNostroTransTBL.AcctType = returndata.Rows[col][1] == null ? null : returndata.Rows[col][1].ToString(); CBSNostroTransTBL.TransDate = returndata.Rows[col][2] == null ? (DateTime?)null : Convert.ToDateTime(returndata.Rows[col][2]);; dateTest = CBSNostroTransTBL.TransDate.ToString(); if (countTrans > 1) { MaxTransDate = CBSNostroTransTBL.TransDate.ToString(); if (Convert.ToDateTime(dateTest) > Convert.ToDateTime(MaxTransDate)) { MaxTransDate = dateTest; } else { MaxTransDate = MaxTransDate; } } CBSNostroTransTBL.Amount = returndata.Rows[col][3] == null ? 0 : Math.Round(Convert.ToDecimal(returndata.Rows[col][3]), 2); CBSNostroTransTBL.Description = returndata.Rows[col][4] == null ? null : returndata.Rows[col][4].ToString(); CBSNostroTransTBL.Reference = returndata.Rows[col][5] == null ? null : returndata.Rows[col][5].ToString(); CBSNostroTransTBL.OrigRefNo = CBSNostroTransTBL.Reference; CBSNostroTransTBL.DebitCredit = returndata.Rows[col][6] == null ? null : returndata.Rows[col][6].ToString(); CBSNostroTransTBL.OriginatingBranch = returndata.Rows[col][7] == null ? null : returndata.Rows[col][7].ToString(); CBSNostroTransTBL.PostedBy = returndata.Rows[col][8] == null ? null : returndata.Rows[col][8].ToString(); CBSNostroTransTBL.Currency = returndata.Rows[col][9] == null ? null : returndata.Rows[col][9].ToString(); CBSNostroTransTBL.PtId = returndata.Rows[col][10] == null ? null : returndata.Rows[col][10].ToString(); int revcode; CBSNostroTransTBL.ReversalCode = returndata.Rows[col][11] == null ? (int?)null : int.TryParse(returndata.Rows[col][11].ToString(), out revcode) ? revcode : (int?)null; LastRecordId = CBSNostroTransTBL.PtId; CBSNostroTransTBL.MatchingStatus = "N"; CBSNostroTransTBL.PullDate = DateTime.Now; CBSNostroTransTBL.UserId = 1; countTrans += 1; if (!string.IsNullOrWhiteSpace(CBSNostroTransTBL.Reference)) { var exist = await repoCBSNostroVostroTransRepository.GetAsync(c => (c.Reference == CBSNostroTransTBL.Reference && c.Amount == CBSNostroTransTBL.Amount)); var existHis = await repoCBSNostroVostroTransHistoryRepository.GetAsync(c => c.Reference == CBSNostroTransTBL.Reference && c.Amount == CBSNostroTransTBL.Amount); if (exist != null || existHis != null) { CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo; CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType; CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate; CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount; CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description; CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit; CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch; CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy; CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency; CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId; LastRecordId = CBSNostroTransTBLErr.PtId; CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus; CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate; CBSNostroTransTBLErr.ReversalCode = CBSNostroTransTBL.ReversalCode; CBSNostroTransTBLErr.UserId = 1; CBSNostroTransTBLErr.ErrorMsg = "Duplicate transaction record"; repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr); var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret1) { continue; } continue; } else { repoCBSNostroVostroTransRepository.Add(CBSNostroTransTBL); var ret = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret) { count += 1; CBSRecordCount = count.ToString(); continue; } } } else { CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo; CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType; CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate; CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount; CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description; CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit; CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch; CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy; CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency; CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId; LastRecordId = CBSNostroTransTBLErr.PtId; CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus; CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate; CBSNostroTransTBLErr.ReversalCode = CBSNostroTransTBL.ReversalCode; CBSNostroTransTBLErr.UserId = 1; CBSNostroTransTBLErr.ErrorMsg = "No ref No."; repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr); var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret1) { continue; } } } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); try { CBSNostroTransTBLErr.AcctNo = CBSNostroTransTBL.AcctNo; CBSNostroTransTBLErr.AcctType = CBSNostroTransTBL.AcctType; CBSNostroTransTBLErr.TransDate = CBSNostroTransTBL.TransDate; CBSNostroTransTBLErr.Amount = CBSNostroTransTBL.Amount; CBSNostroTransTBLErr.Description = CBSNostroTransTBL.Description; CBSNostroTransTBLErr.Reference = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.OrigRefNo = CBSNostroTransTBL.Reference; CBSNostroTransTBLErr.DebitCredit = CBSNostroTransTBL.DebitCredit; CBSNostroTransTBLErr.OriginatingBranch = CBSNostroTransTBL.OriginatingBranch; CBSNostroTransTBLErr.PostedBy = CBSNostroTransTBL.PostedBy; CBSNostroTransTBLErr.Currency = CBSNostroTransTBL.Currency; CBSNostroTransTBLErr.PtId = CBSNostroTransTBL.PtId; LastRecordId = CBSNostroTransTBLErr.PtId; CBSNostroTransTBLErr.MatchingStatus = CBSNostroTransTBL.MatchingStatus; CBSNostroTransTBLErr.PullDate = CBSNostroTransTBL.PullDate; CBSNostroTransTBLErr.UserId = 1; CBSNostroTransTBLErr.ErrorMsg = ex == null ? ex.InnerException.Message : ex.Message; repoCBSNostroVostroTransErrorRepository.Add(CBSNostroTransTBLErr); var ret1 = unitOfWork.CommitNonAsync(0, null) > 0 ? true : false; if (ret1) { continue; } } catch (Exception ex1) { var exErr2 = ex1 == null ? ex.InnerException.Message : ex.Message; var stackTrace2 = new StackTrace(ex); var thisas = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisas).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); continue; } continue; } } if (controlTable != null) { //Update with below string updatScript = "update CBSNostroTrans set VostroAcctNo = b.VostroAcctNo " + " from CBSNostroTrans a,admSourceAccount b " + " where a.AcctNo = b.AcctNo " + " and b.ReconTypeId = " + ReconType.ReconTypeId; string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; SqlConnection con = new SqlConnection(connectionstring); con.Open(); SqlCommand commd = new SqlCommand(updatScript, con); commd.CommandType = CommandType.Text; commd.ExecuteNonQuery(); con.Close(); controlTable.LastRecordId = LastRecordId; controlTable.LastRecordTimeStamp = DateTime.Now; DateTime dt = new DateTime(); controlTable.LastTransDate = MaxTransDate == null ? DateTime.Now : DateTime.TryParse(MaxTransDate, out dt) ? dt : DateTime.Now; controlTable.PullDate = DateTime.Now; int recTrack; controlTable.RecordsCount = CBSRecordCount == null ? (int?)null : int.TryParse(CBSRecordCount, out recTrack) ? recTrack : (int?)null; repoadmDataPoollingControlRepository.Update(controlTable); var ret = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret) { } } else { string updatScript = "update CBSNostroTrans set VostroAcctNo = b.VostroAcctNo " + " from CBSNostroTrans a,admSourceAccount b " + " where a.AcctNo = b.AcctNo " + " and b.ReconTypeId = " + ReconType.ReconTypeId; string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString; SqlConnection con = new SqlConnection(connectionstring); con.Open(); SqlCommand commd = new SqlCommand(updatScript, con); commd.CommandType = CommandType.Text; commd.ExecuteNonQuery(); con.Close(); var admDataPoollingControlTBL = new admDataPullingControl(); admDataPoollingControlTBL.ReconTypeId = ReconType.ReconTypeId; admDataPoollingControlTBL.FileType = "Table"; admDataPoollingControlTBL.TableName = "CBSNostroTrans"; admDataPoollingControlTBL.DateCreated = DateTime.Now; admDataPoollingControlTBL.UserId = 1; admDataPoollingControlTBL.LastRecordTimeStamp = DateTime.Now; admDataPoollingControlTBL.LastRecordId = LastRecordId; admDataPoollingControlTBL.ReconLevel = 1; DateTime dt = new DateTime(); admDataPoollingControlTBL.LastTransDate = MaxTransDate == null ? DateTime.Now : DateTime.TryParse(MaxTransDate, out dt) ? dt : DateTime.Now; admDataPoollingControlTBL.PullDate = DateTime.Now; int recTrack; admDataPoollingControlTBL.RecordsCount = CBSRecordCount == null ? (int?)null : int.TryParse(CBSRecordCount, out recTrack) ? recTrack : (int?)null; repoadmDataPoollingControlRepository.Add(admDataPoollingControlTBL); var ret = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret) { } } } var lstRec = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "CBSNostroTrans"); LastRecordId = lstRec.LastRecordId; } // reader.Close(); theCons.Close(); } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured NostroVostroLibrary in Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); } } } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured in CBSNostroVostroTrans Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); } } #endregion #endregion #region Source 2 var dtSouceCon2 = await repoadmSourceAccountRepository.GetAsync(c => c.ReconTypeId == ReconTypeId && c.SourceName == "Source 2"); #region //File Directory Below if (!string.IsNullOrWhiteSpace(dtSouceCon2.FileDirectory)) { try { string LastRecordId = string.Empty; var controlTable = await repoadmDataPoollingControlRepository.GetAsync(c => c.ReconTypeId == ReconType.ReconTypeId && c.TableName == "VostroMT940950Trans"); if (controlTable != null) { LastRecordId = controlTable.LastRecordId; } else { LastRecordId = "0"; } string FileDirectory = dtSouceCon2.FileDirectory; DataTable dTable = new DataTable(); DirectoryInfo d = new DirectoryInfo(FileDirectory); List <FileInfo> DLIST = null; DLIST = d.GetFiles("*" + ".out").ToList(); List <FileInfo> ggg = null; if (controlTable != null) { ggg = DLIST;//.Where(c => c.LastWriteTime > controlTable.LastRecordTimeStamp).ToList(); } else { ggg = DLIST; } string fileNamenAndType = string.Empty; string FileLastTime = string.Empty; int count = 0; string returnValue = string.Empty; foreach (var kl in ggg) { fileNamenAndType = (from f in DLIST orderby f.LastWriteTime ascending select kl).First().Name; FileLastTime = (from f in DLIST orderby f.LastAccessTimeUtc descending select kl).First().LastWriteTime.ToString(); int read = await readMT940.Generate940950Message(dtSouceCon2.FileDirectory + "\\" + fileNamenAndType, ReconTypeId, dtSouceCon2.FileDirectory, fileNamenAndType, FileLastTime); returnValue = read.ToString(); if (!string.IsNullOrWhiteSpace(returnValue)) { // Move here var proceTBL = new FileProcessControl(); proceTBL.ReconTypeId = ReconTypeId; proceTBL.FileDirectory = FileDirectory; proceTBL.FileName = fileNamenAndType; proceTBL.NameConvention = ReconType.FileNamingConvention; proceTBL.DateProcessed = DateTime.Now; proceTBL.Status = "Processed"; proceTBL.DateCreated = DateTime.Now; proceTBL.UserId = 1; repoadmFileProcessedRepository.Add(proceTBL); var ret1 = await unitOfWork.Commit(0, null) > 0 ? true : false; if (ret1) { LogManager.SaveLog("Move File Start Nostro Vostro"); string MvFile = library.MoveFile(FileDirectory + "\\" + fileNamenAndType, ReconType.ProcessedFileDirectory, fileNamenAndType); LogManager.SaveLog("Move File in Nostro Vostro status: " + MvFile); } } } } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured in NostroVostroLibrary Line : " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); } } #endregion #endregion } catch (Exception ex) { var exErr = ex == null ? ex.InnerException.Message : ex.Message; var stackTrace = new StackTrace(ex); var thisasm = Assembly.GetExecutingAssembly(); _methodname = stackTrace.GetFrames().Select(f => f.GetMethod()).First(m => m.Module.Assembly == thisasm).Name; _lineErrorNumber = ex.StackTrace.Substring(ex.StackTrace.Length - 7, 7); LogManager.SaveLog("An error occured in NostroVostroLogManager Line: " + _lineErrorNumber + " CLASSNAME: " + _classname + " METHOD NAME: [" + _methodname + "] ERROR: " + exErr); throw; } return(string.Empty); }