/// <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; }