public Ward getWardInformation(int ward, OracleConnection connection) { Ward wardObj = null; string strSQL = " SELECT ward_number,employee_number,replace(replace(REPLACE(REPLACE(employee_name,',','_'),' ','_'),'\"',''),'*','') employee_name,ward_ward_status,"; strSQL += "replace(replace(REPLACE(REPLACE(WARD_NAME,',','_'),' ','_'),'\"',''),'*','') WARD_NAME ,NVL(WARD_COURT_FILE_NO,'99-999') WARD_COURT_FILE_NO "; strSQL += "FROM ward w,employee e WHERE WARD_NUMBER =:p_ward and "; strSQL += "e.EMPLOYEE_NUMBER = w.WARD_RESPONSIBLE_EMPLOYEE"; OracleCommand cmd = null; cmd = new OracleCommand(); cmd.CommandText = strSQL; cmd.Connection = connection; OracleDataReader rs = null; try { OracleParameter p_ward = new OracleParameter(); p_ward.Value = Convert.ToDecimal(ward); cmd.Parameters.Add(p_ward); rs = cmd.ExecuteReader(); // execute the query decimal ward_number; string ward_name; decimal employee_number; string employee_name; string file_number; string ward_status; if (rs.HasRows) { while (rs.Read()) { ward_number = rs.GetDecimal(0); employee_number = rs.GetDecimal(1); employee_name = rs.GetString(2); ward_name = rs.GetString(4); file_number = rs.GetString(5); ward_status = rs.GetString(3); wardObj = new Ward(ward_number, ward_name, employee_number, employee_name, file_number, ward_status); } } } catch (OracleException e) { Console.WriteLine("Exception Caught {0}", e.ToString()); rs.Dispose(); cmd.Dispose(); Environment.Exit(-1); } rs.Dispose(); cmd.Dispose(); return(wardObj); }
/// <summary> /// 取用户的岗位列表 /// </summary> /// <param name="yhid"></param> /// <returns></returns> public static List <SinoPost> Get_PostsByYHID(string yhid) { List <SinoPost> _ret = new List <SinoPost>(); OracleParameter[] _param = { new OracleParameter(":YHID", OracleDbType.Decimal), new OracleParameter(":SSDWID", OracleDbType.Decimal) }; _param[0].Value = decimal.Parse(yhid); _param[1].Value = decimal.Parse(ConfigFile.SystemID); OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, SQL_Get_PostsByYHID, _param); while (dr.Read()) { SinoPost _sp = new SinoPost(dr.GetString(0), dr.IsDBNull(1) ? "" : dr.GetDecimal(1).ToString(), dr.IsDBNull(2) ? "" : dr.GetDecimal(2).ToString(), dr.IsDBNull(3) ? "" : dr.GetString(3), dr.IsDBNull(4) ? "" : dr.GetString(4), dr.IsDBNull(5) ? "" : dr.GetString(5), dr.IsDBNull(7) ? (int)0 : Convert.ToInt32(dr.GetDecimal(7)), dr.IsDBNull(6) ? false : (((decimal)dr.GetOracleDecimal(6).Value == 1) ? true : false)); _sp.Roles = Get_RolesOfGW(_sp.PostID); _ret.Add(_sp); } dr.Close(); return(_ret); }
private void FillData() { OracleCommand command = new OracleCommand(); command.Connection = obj_lib.Module.Connection; command.CommandText = "select f_level, f_name from sepo_std_folders where f_owner = :f_owner"; OracleParameter p_owner = command.Parameters.Add("f_owner", null); p_owner.Value = 0; Dictionary <decimal, string> dict = new Dictionary <decimal, string>(); dict.Add(-1, null); using (OracleDataReader reader = command.ExecuteReader()) { while (reader.Read()) { dict.Add(reader.GetDecimal(0), reader.GetDecimal(0) + " - " + reader.GetString(1)); } } lvlBox.ValueMember = "Key"; lvlBox.DisplayMember = "Value"; lvlBox.DataSource = new BindingSource(dict, null); }
public Invoice MapInvoice(OracleDataReader oracleDataReader) { Client client = new Client(); Invoice invoice = new Invoice(); invoice.SaleDate = oracleDataReader.GetDateTime(0); invoice.DueData = oracleDataReader.GetDateTime(1); invoice.InvoiceType = oracleDataReader.GetString(2); invoice.Invoice_ID = oracleDataReader.GetInt32(3); invoice.Subtotal = oracleDataReader.GetDecimal(4); invoice.TotalIva = oracleDataReader.GetDecimal(5); invoice.Total = oracleDataReader.GetDecimal(6); client.Cedula = oracleDataReader.GetString(7); client.FirstName = oracleDataReader.GetString(8); client.SecondName = oracleDataReader.GetString(9); client.LastName = oracleDataReader.GetString(10); client.SecondLastName = oracleDataReader.GetString(11); client.Sex = oracleDataReader.GetString(12); client.DateOfBorn = oracleDataReader.GetDateTime(13); client.Age = oracleDataReader.GetInt32(14); client.Telephone = oracleDataReader.GetString(15); client.E_mail = oracleDataReader.GetString(16); client.AddressHome = oracleDataReader.GetString(17); client.Neighborhood = oracleDataReader.GetString(18); invoice.Client = client; return(invoice); }
public List <WC_DataInfo> GetDataInfo(int Year) { List <WC_DataInfo> _ret = new List <WC_DataInfo>(); OracleParameter[] _param = { new OracleParameter(":STARTDATE", OracleDbType.Varchar2, 8), new OracleParameter(":ENDDATE", OracleDbType.Varchar2, 8) }; _param[0].Value = (new DateTime(Year - 1, 12, 1)).ToString("yyyyMMdd"); _param[1].Value = (new DateTime(Year + 1, 1, 31)).ToString("yyyyMMdd"); OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, SQL_GetDataInfo, _param); while (dr.Read()) { string _datestr = dr.IsDBNull(0) ? "" : dr.GetString(0); if (_datestr != "" && _datestr.Length == 8) { WC_DataInfo _di = new WC_DataInfo(); _di.GZ_Date = new DateTime(int.Parse(_datestr.Substring(0, 4)), int.Parse(_datestr.Substring(4, 2)), int.Parse(_datestr.Substring(6, 2))); _di.Meta = dr.IsDBNull(1) ? "" : dr.GetString(1); _di.Year = int.Parse(dr.IsDBNull(2) ? "1" : dr.GetString(2)); _di.Month = int.Parse(dr.IsDBNull(3) ? "1" : dr.GetString(3)); _di.Day = int.Parse(dr.IsDBNull(4) ? "1" : dr.GetString(4)); _di.IsTJSBR = ((dr.IsDBNull(5) ? 0 : Convert.ToInt16(dr.GetDecimal(5))) > 0); _di.IsWorkDay = ((dr.IsDBNull(6) ? 0 : Convert.ToInt16(dr.GetDecimal(6))) < 1); _di.IsFXSBR = ((dr.IsDBNull(7) ? 0 : Convert.ToInt16(dr.GetDecimal(7))) > 0); _ret.Add(_di); } } dr.Close(); return(_ret); }
private bool TypesFromCursor(OracleRefCursor cursor, ref List <sCaptureTypes> types) { // Types may be none for test photos //if (cursor is System.DBNull) //{ // _error = "No vehicle types given."; // return false; //} if (cursor is System.DBNull) { return(true); } OracleDataReader rd = cursor.GetDataReader(); sCaptureTypes typ; while (rd.Read()) { typ.mID = (int)rd.GetDecimal(0); typ.mCode = (string)rd.GetString(1); typ.mType = rd.GetString(2); typ.mAmount = rd.GetDecimal(3); typ.mDescription = rd.GetString(4); typ.mBeskrywing = rd.GetString(5); types.Add(typ); } rd.Close(); rd.Dispose(); //return (types.Count > 0); Types may be none for test photos return(true); }
public static Quota LoadQuota(OracleDataReader odr) { string tablespaceName = odr.GetString(odr.GetOrdinal("tablespace_name")); string userName = odr.GetString(odr.GetOrdinal("username")); decimal?bytes = null, maxBytes = null, maxBlocks = null; decimal blocks = odr.GetDecimal(odr.GetOrdinal("blocks")); if (!odr.IsDBNull(odr.GetOrdinal("bytes"))) { bytes = odr.GetDecimal(odr.GetOrdinal("bytes")); } if (!odr.IsDBNull(odr.GetOrdinal("max_bytes"))) { maxBytes = odr.GetDecimal(odr.GetOrdinal("max_bytes")); } if (!odr.IsDBNull(odr.GetOrdinal("max_blocks"))) { maxBlocks = odr.GetDecimal(odr.GetOrdinal("max_blocks")); } return(new Quota(tablespaceName, userName, blocks, maxBlocks, bytes, maxBytes)); }
/// <summary> /// 取指定岗位下的所有角色 /// </summary> /// <param name="_postid"></param> /// <returns></returns> public static List <SinoRole> Get_RolesOfGW(string _postid) { string _sql = string.Format(" select a.jsid,a.jsmc,a.jssm,a.ssdwid from qx2_gwjsgxb t,qx_jsdyb a "); _sql += string.Format("where a.jsid = t.jsid and t.gwid = :GWID"); OracleParameter[] _param = { new OracleParameter(":GWID", OracleDbType.Decimal), }; _param[0].Value = decimal.Parse(_postid); List <SinoRole> roles = new List <SinoRole>(); OracleDataReader dr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringProfile, CommandType.Text, _sql, _param); while (dr.Read()) { SinoRole _sr = RightFunctions.CreateRoleItem(dr.GetDecimal(0).ToString(), dr.IsDBNull(1) ? "" : dr.GetString(1), dr.IsDBNull(2) ? "" : dr.GetString(2), dr.IsDBNull(3) ? "" : dr.GetDecimal(3).ToString() ); roles.Add(_sr); } dr.Close(); return(roles); }
public void chercher_ListValidation_conge(String critere, out string erreur, out List <Validation_Conge_Class> val, out Boolean exist) { erreur = null; Validation_Conge_Class Validation = new Validation_Conge_Class(); val = new List <Validation_Conge_Class>(); exist = false; rsql = "select * from VALIDATION_CONGE" + critere; cnx.select(rsql, out erreur, out dr); if (erreur == null) { if (dr.HasRows == true) { exist = true; while (dr.Read()) { Validation = new Validation_Conge_Class(); Validation.Id_validation = dr.GetString(0); Validation.Date_Debut_Valider = dr.GetString(1); Validation.Date_fin_valider = dr.GetString(2); Validation.Heur_Debut_Valider = dr.GetString(3); Validation.Heur_Fin_Valider = dr.GetString(4); Validation.ID_CONGER_DEMANDER = dr.GetString(5); Validation.NombreJourValider = dr.GetDecimal(6); val.Add(Validation); } } dr.Close(); } }
private void load_bed(decimal ward_id)//comboBox { string query = "SELECT * FROM BED WHERE W_ID = :ward_id ORDER BY num"; command = new OracleCommand(query, connection); connection.Open(); OracleParameter p = command.Parameters.Add(new OracleParameter("ward_id", OracleType.Number)); p.Direction = ParameterDirection.Input; p.Value = ward_id; reader = command.ExecuteReader(); comboBox5.Items.Clear(); listView2.Items.Clear(); while (reader.Read()) { string[] i = new string[2]; i[0] = reader.GetDecimal(0).ToString(); if (reader.GetString(1).Equals("A")) { comboBox5.Items.Add(reader.GetDecimal(0)); i[1] = "Available"; } else if (reader.GetString(1).Equals("U")) { i[1] = "Unavailable"; } ListViewItem item = new ListViewItem(i); listView2.Items.Add(item); } connection.Close(); }
public bool RiLoadData(OracleConnection cn, DateTime Rq) { bool find = false; OracleCommand selectCmd = new OracleCommand(); selectCmd.Connection = cn; selectCmd.CommandText = "SELECT 自产湿焦,落地湿焦 FROM 全厂日消耗 WHERE P01日期=:RQ"; selectCmd.Parameters.Add(":RQ", OracleType.DateTime).Value = Rq; OracleDataReader dr = selectCmd.ExecuteReader(); if (dr.Read()) { if (dr.IsDBNull(0)) { this.QuanChangZiChanShiJiao = null; } else { this.QuanChangZiChanShiJiao = dr.GetDecimal(0); } if (dr.IsDBNull(1)) { this.QuanChangLuoDiShiJiao = null; } else { this.QuanChangLuoDiShiJiao = dr.GetDecimal(1); } find = true; } dr.Close(); return(find); }
private List <MD_ReportGuideLineItem> GetChildGuideLine(MD_ReportGuideLineItem _fitem, OracleConnection cn) { List <MD_ReportGuideLineItem> _ret = new List <MD_ReportGuideLineItem>(); string _sql = "select ID,FID,ZBMC,ZBZTMC from tj_zbcxdyb where fid =:FID and zbztmc =:ZTMC order by id "; OracleCommand _cmd = new OracleCommand(_sql, cn); _cmd.Parameters.Add(":FID", decimal.Parse(_fitem.ID)); _cmd.Parameters.Add(":ZTMC", _fitem.ZTName); OracleDataReader _dr = _cmd.ExecuteReader(); while (_dr.Read()) { MD_ReportGuideLineItem _item = new MD_ReportGuideLineItem( _dr.IsDBNull(0) ? "" : _dr.GetDecimal(0).ToString(), _dr.IsDBNull(1) ? "" : _dr.GetDecimal(1).ToString(), _dr.IsDBNull(2) ? "" : _dr.GetString(2), _dr.IsDBNull(3) ? "" : _dr.GetString(3), Enum_ReportGuideLineItemType.GuideLine ); _item.Children = GetChildGuideLine(_item, cn); _ret.Add(_item); } _dr.Close(); return(_ret); }
private bool VehicleFromCursor(OracleRefCursor cursor, ref sCaseInfo vehicle) { if (cursor is System.DBNull) { _error = "No vehicle info given."; return(false); } OracleDataReader rd = cursor.GetDataReader(); vehicle.mOffencePlace = vehicle.mOffenceDate = string.Empty; if (rd.Read()) { vehicle.mOffenceSpeed = (int)rd.GetDecimal(0); vehicle.mOffenceZone = (int)rd.GetDecimal(1); vehicle.mOffenceDate = rd.GetString(2); vehicle.mOffencePlace = rd.GetString(3); vehicle.mPrevRejectID = (int)rd.GetDecimal(4); vehicle.mVehicleRegNo = rd.IsDBNull(5) ? string.Empty : rd.GetString(5); } rd.Close(); rd.Dispose(); if ((vehicle.mOffencePlace.Length <= 0) || (vehicle.mOffenceDate.Length <= 0)) { _error = "No vehicle data loaded, contact helpdesk."; return(false); } return(true); }
public MD_ReportGuideLineDefine GetReportGuideLineDefine(string _id) { MD_ReportGuideLineDefine _ret = null; using (OracleConnection cn = OracleHelper.OpenConnection()) { OracleParameter[] _param = { new OracleParameter(":ID", OracleDbType.Decimal) }; _param[0].Value = decimal.Parse(_id); OracleDataReader _dr = OracleHelper.ExecuteReader(cn, CommandType.Text, SQL_GetReportGuideLineDefine, _param); while (_dr.Read()) { _ret = new MD_ReportGuideLineDefine( _dr.IsDBNull(0) ? "" : _dr.GetDecimal(0).ToString(), _dr.IsDBNull(1) ? "" : _dr.GetDecimal(1).ToString(), _dr.IsDBNull(2) ? "" : _dr.GetString(2), _dr.IsDBNull(3) ? "" : _dr.GetString(3), _dr.IsDBNull(4) ? "" : _dr.GetString(4), _dr.IsDBNull(5) ? "" : _dr.GetString(5) ); } _dr.Close(); cn.Close(); } return(_ret); }
void listele(int a) { string connstring = "User Id = system;Password = 1234;Data Source = 192.168.1.32:1521/xe;"; OracleConnection conn = new OracleConnection(connstring); conn.Open(); OracleCommand cmd = new OracleCommand(); cmd = conn.CreateCommand(); cmd.CommandText = "select urunadi,urunalisfiyati, urunkdv, b.adet, b.uruntutar from urunler a INNER JOIN(select faturaid, urunbarkod, adet, uruntutar from satistablosu ) b ON a.urunbarkodno = b.urunbarkod and b.faturaid = :pa"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(":pa", OracleDbType.Int32, a, ParameterDirection.Input); OracleDataReader reader = null; reader = cmd.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { dataGridView1.Rows.Add(reader.GetString(reader.GetOrdinal("urunadi")), reader.GetInt32(reader.GetOrdinal("adet")), reader.GetDecimal(reader.GetOrdinal("urunalisfiyati")), reader.GetDecimal(reader.GetOrdinal("uruntutar")) / reader.GetInt32(reader.GetOrdinal("adet")), reader.GetInt32(reader.GetOrdinal("adet")) * ((reader.GetDecimal(reader.GetOrdinal("uruntutar")) / reader.GetInt32(reader.GetOrdinal("adet"))) - reader.GetDecimal(reader.GetOrdinal("urunalisfiyati")))); } } cmd.ExecuteNonQuery(); conn.Close(); }
private bool OfficersFromCursor(OracleRefCursor cursor, ref List <sOfficerInfo> officers) { if (cursor is System.DBNull) { _error = "No officers given."; return(false); } OracleDataReader rd = cursor.GetDataReader(); sOfficerInfo off; while (rd.Read()) { off.mID = (int)rd.GetDecimal(0); off.mExternID = rd.GetString(1); off.mName = rd.GetString(2); off.mSurname = rd.GetString(3); off.mCredentialID = (long)rd.GetDecimal(4); officers.Add(off); } rd.Close(); rd.Dispose(); if (officers.Count <= 0) { _error = "No officers loaded, contact helpdesk."; return(false); } return(true); }
//retrieves all data about an item of stock in relation to stock id public void getStock(int StockID) { //connect to DB OracleConnection conn = new OracleConnection(DBConnect.oradb); conn.Open(); //Define SQL Query String strSQL = "SELECT * FROM Stock WHERE stock_id = " + StockID; //execute query using Data Reader OracleCommand cmd = new OracleCommand(strSQL, conn); OracleDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { setStock_id(dr.GetInt32(0)); setStock_name(dr.GetString(1)); setQty(dr.GetInt32(2)); setCost_p(dr.GetDecimal(3)); setSale_p(dr.GetDecimal(4)); setStatus(dr.GetString(5)); setDepartment_id(dr.GetInt32(6)); } //close db conn.Close(); }
private List <object> Check_Login(string username, string password) { try { List <object> data = new List <object>(); OracleCommand cmd1 = conn.CreateCommand(); cmd1.CommandText = "CHECK_LOGIN"; cmd1.CommandType = CommandType.StoredProcedure; cmd1.BindByName = true; cmd1.Parameters.Add("ONAME", OracleDbType.Varchar2, username, ParameterDirection.Input); cmd1.Parameters.Add("OPWD", OracleDbType.Varchar2, password, ParameterDirection.Input); cmd1.Parameters.Add("DATAUSER", OracleDbType.RefCursor, ParameterDirection.Output); cmd1.ExecuteNonQuery(); OracleDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); data.Add(dr1.GetDecimal(0)); data.Add(dr1.GetString(1)); data.Add(dr1.GetString(2)); data.Add(dr1.GetDecimal(4)); data.Add(dr1.GetString(9)); return(data); } catch (Exception e) { Console.WriteLine(e.ToString()); return(new List <object>()); } }
public static ObservableCollection <KontrolnaTacka> VratiSveKontrolneTacke(int id_zadatka) { using (OracleCommand cmd = new OracleCommand("P_ZADATAK.Vrati_Sve_Checkpointove", DBConnection.con)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new OracleParameter("checkpointovi", OracleDbType.RefCursor, ParameterDirection.ReturnValue)); cmd.Parameters.Add(new OracleParameter("p_zad", OracleDbType.Decimal, ParameterDirection.Input)); cmd.Parameters["p_zad"].Value = id_zadatka; OracleDataReader dr = cmd.ExecuteReader(); ObservableCollection <KontrolnaTacka> kTacke = new ObservableCollection <KontrolnaTacka>(); while (dr.Read()) { int id = (int)dr.GetDecimal(0); string ime = dr.GetString(1); bool status = (int)dr.GetDecimal(4) == 1 ? true : false; KontrolnaTacka kt = new KontrolnaTacka() { Id = id, Naziv = ime, Vrednost = status }; kTacke.Add(kt); } dr.Close(); return(kTacke); } }
public void getStock(int Sno) { //Conenct to the DB OracleConnection myConn = new OracleConnection(DBConnect.oradb); myConn.Open(); //Define SQL query to get MAX stock_No used String strSQL = "SELECT * FROM Stock WHERE StockNo = " + Sno; OracleCommand cmd = new OracleCommand(strSQL, myConn); //execute the SQL query OracleDataReader dr = cmd.ExecuteReader(); //If first stockNo, assign value 1, otherwise add 1 to MAX value if (dr.Read()) { setStockNo(dr.GetInt32(0)); setDescription(dr.GetString(1)); setCostPrice(dr.GetDecimal(2)); setSalePrice(dr.GetDecimal(3)); setQty(dr.GetInt32(4)); //setStatus(dr.GetValue(5)); } //close DB connection myConn.Close(); }
public override bool GetBoolean(int ordinal) { //OracleNumber number = source.GetOracleNumber(ordinal); //return Convert.ToBoolean(number.Value); var result = source.GetDecimal(ordinal); return(Convert.ToBoolean(result)); }
public List <HvdcLineCktForeign> ExtractHvdcLineCktForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, LINE_CIRCUIT_NAME, HVDC_LINE_ID, CIRCUIT_NO, FROM_BUS_ID, TO_BUS_ID, LENGTH_KM, DATETIME_OF_TRAIL_OPERATION, DATETIME_OF_FIRST_CHARGING, DATETIME_OF_COMMISSIONING, DATETIME_OF_COD, DATETIME_OF_DECOMMISSIONING, THERMAL_LIMIT_MVA, NO_OF_CONDUCTORS_PER_CKT from REPORTING_WEB_UI_UAT.HVDC_LINE_CIRCUIT where :id=1 and HVDC_LINE_ID IS NOT NULL and ID IS NOT NULL"; // Assign id parameter OracleParameter id = new OracleParameter("id", 1); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); List <HvdcLineCktForeign> hvdcLineCktsForeign = new List <HvdcLineCktForeign>(); while (reader.Read()) { HvdcLineCktForeign hvdcLineCktForeign = new HvdcLineCktForeign(); hvdcLineCktForeign.WebUatId = reader.GetInt32(0); hvdcLineCktForeign.Name = reader.GetString(1); hvdcLineCktForeign.HvdcLineWebUatId = reader.GetInt32(2); hvdcLineCktForeign.CktNumber = reader.GetInt32(3); hvdcLineCktForeign.FromBusWebUatId = reader.GetInt32(4); hvdcLineCktForeign.ToBusWebUatId = reader.GetInt32(5); hvdcLineCktForeign.Length = reader.GetDecimal(6); hvdcLineCktForeign.TrialOperationDate = reader.GetDateTime(7); hvdcLineCktForeign.FtcDate = reader.GetDateTime(8); hvdcLineCktForeign.CommDate = reader.GetDateTime(9); hvdcLineCktForeign.CodDate = reader.GetDateTime(10); hvdcLineCktForeign.DeCommDate = reader.GetDateTime(11); hvdcLineCktForeign.ThermalLimitMVA = reader.GetDecimal(12); hvdcLineCktForeign.NumConductorsPerCkt = reader.GetInt32(13); hvdcLineCktsForeign.Add(hvdcLineCktForeign); } reader.Dispose(); return(hvdcLineCktsForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }
public void getItemInfoFromKey(string key, string sbs_no, Hashtable items, List <ErrorField> errorList, string errorPath) { string[] keyArr = key.Split(UploadSalesControl.ALU_DELIMITER); string alu = keyArr[0]; string upc = keyArr[1]; if (string.IsNullOrEmpty(alu) && string.IsNullOrEmpty(upc)) { errorList.Add(new ErrorField(-1, "ALU", key)); return; } string connectionString = getConnectionString(); using (OracleConnection cn = new OracleConnection(connectionString)) { cn.Open(); OracleDataReader reader = null; try { if (!string.IsNullOrEmpty(alu)) { OracleCommand cmd = getCommandForALU(alu, sbs_no, cn); reader = cmd.ExecuteReader(); } if (reader == null || !reader.HasRows) { if (!string.IsNullOrEmpty(upc)) { OracleCommand cmd = getCommandForUPC(upc, sbs_no, cn); reader = cmd.ExecuteReader(); } } if (reader == null || !reader.HasRows) { errorList.Add(new ErrorField(-1, "ALU/UPC not found:", key)); } else { reader.Read(); decimal item_sid = reader.GetDecimal(0); decimal price = reader.GetDecimal(2); items.Add(key, new ItemInfo(item_sid, price)); } } catch (Exception e) { LogWriter.writeErrorLog(e, errorPath); errorList.Add(new ErrorField(-1, "Invalid ALU/UPC", key)); } finally { cn.Close(); } } }
public List <AcTransmissionLineCircuitForeign> ExtractAcTransLineCktForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, LINE_CIRCUIT_NAME, LINE_ID, CIRCUIT_NUMBER, LENGTH, FIRST_CHARGING_DATETIME, TRAIL_OPERATION_DATETIME, COMMISSIONING_DATETIME, COD_DATETIME, DECOMMISSIONING_DATETIME, THERMAL_LIMIT_MVA, SIL_MW from AC_TRANSMISSION_LINE_CIRCUIT where :id=1 and LINE_ID IS NOT NULL and ID IS NOT NULL and VOLTAGE_LEVEL IS NOT NULL and CIRCUIT_NUMBER IS NOT NULL"; // Assign id parameter OracleParameter id = new OracleParameter("id", 1); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); List <AcTransmissionLineCircuitForeign> acTranLineCktsForeign = new List <AcTransmissionLineCircuitForeign>(); while (reader.Read()) { AcTransmissionLineCircuitForeign acTransLineCktForeign = new AcTransmissionLineCircuitForeign(); acTransLineCktForeign.WebUatId = reader.GetInt32(0); acTransLineCktForeign.Name = reader.GetString(1); acTransLineCktForeign.AcTransLineWebUatId = reader.GetInt32(2); acTransLineCktForeign.CktNumber = reader.GetInt32(3); acTransLineCktForeign.Length = reader.GetDecimal(4); acTransLineCktForeign.FtcDate = reader.GetDateTime(5); acTransLineCktForeign.TrialOperationDate = reader.GetDateTime(6); acTransLineCktForeign.CommDate = reader.GetDateTime(7); acTransLineCktForeign.CODDate = reader.GetDateTime(8); acTransLineCktForeign.DeCommDate = reader.GetDateTime(9); acTransLineCktForeign.ThermalLimitMVA = reader.GetDecimal(10); acTransLineCktForeign.SIL = reader.GetDecimal(11); acTranLineCktsForeign.Add(acTransLineCktForeign); } reader.Dispose(); return(acTranLineCktsForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }
public void Check_Users(int userid, string username, string user_rank, string station_name, string stationzone) { OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "TJPD.SEARCH_USER"; cmd.CommandType = CommandType.StoredProcedure; OracleParameter id = new OracleParameter(); id.OracleDbType = OracleDbType.Decimal; id.Direction = ParameterDirection.Input; id.Value = userid; cmd.Parameters.Add(id); OracleParameter name = new OracleParameter(); name.OracleDbType = OracleDbType.Varchar2; name.Direction = ParameterDirection.Input; name.Value = username+"%"; cmd.Parameters.Add(name); OracleParameter rank = new OracleParameter(); rank.OracleDbType = OracleDbType.Varchar2; rank.Direction = ParameterDirection.Input; rank.Value = user_rank+"%"; cmd.Parameters.Add(rank); OracleParameter station = new OracleParameter(); station.OracleDbType = OracleDbType.Varchar2; station.Direction = ParameterDirection.Input; station.Value = station_name+"%"; cmd.Parameters.Add(station); OracleParameter zone = new OracleParameter(); zone.OracleDbType = OracleDbType.Varchar2; zone.Direction = ParameterDirection.Input; zone.Value = stationzone+"%"; cmd.Parameters.Add(zone); OracleParameter dnt_cur = new OracleParameter(); dnt_cur.OracleDbType = OracleDbType.RefCursor; dnt_cur.Direction = ParameterDirection.Output; cmd.Parameters.Add(dnt_cur); OracleDataReader dr = cmd.ExecuteReader(); Userlist.Items.Clear(); while (dr.Read()) { Console.WriteLine(dr.GetDecimal(0) + " " + dr.GetString(1) + " " + dr.GetString(2) + " " + dr.GetString(3) + " " + dr.GetString(4)); ListViewItem lvi = new ListViewItem(Convert.ToString(dr.GetDecimal(0))); lvi.SubItems.Add(dr.GetString(1)); lvi.SubItems.Add(dr.GetString(2)); lvi.SubItems.Add(dr.GetString(3)); lvi.SubItems.Add(dr.GetString(4)); Userlist.Items.Add(lvi); } }
public List <HvdcPoleForeign> ExtractHvdcPoleForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, POLE_NAME, POLE_ID, FK_SUBSTATION, LOCATION_ID, POLE_TYPE, VOLTAGE_LEVEL, MAX_FIRING_ANGLE_DEG, MIN_FIRING_ANGLE_DEG, DATETIME_OF_COMMISSIONING, DATETIME_OF_COD, DATETIME_OF_DECOMMISSIONING from REPORTING_WEB_UI_UAT.HVDC_POLE where :id=1 and POLE_NAME IS NOT NULL and ID IS NOT NULL"; // Assign id parameter OracleParameter id = new OracleParameter("id", 1); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); List <HvdcPoleForeign> hvdcPolesForeign = new List <HvdcPoleForeign>(); while (reader.Read()) { HvdcPoleForeign hvdcPoleForeign = new HvdcPoleForeign(); hvdcPoleForeign.WebUatId = reader.GetInt32(0); hvdcPoleForeign.Name = reader.GetString(1); hvdcPoleForeign.PoleNumber = reader.GetInt32(2); hvdcPoleForeign.SubstationWebUatId = reader.GetInt32(3); hvdcPoleForeign.StateWebUatId = reader.GetInt32(4); hvdcPoleForeign.PoleType = reader.GetString(5); hvdcPoleForeign.VoltLevelWebUatId = reader.GetInt32(6); hvdcPoleForeign.MaxFiringAngleDegrees = reader.GetDecimal(7); hvdcPoleForeign.MinFiringAngleDegrees = reader.GetDecimal(8); hvdcPoleForeign.CommDate = reader.GetDateTime(9); hvdcPoleForeign.CodDate = reader.GetDateTime(10); hvdcPoleForeign.DeCommDate = reader.GetDateTime(11); hvdcPolesForeign.Add(hvdcPoleForeign); } reader.Dispose(); return(hvdcPolesForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }
public List <GeneratorUnitForeign> ExtractGeneratorUnitsForeign(string oracleConnString) { using (OracleConnection con = new OracleConnection(oracleConnString)) { using (OracleCommand cmd = con.CreateCommand()) { try { con.Open(); cmd.BindByName = true; cmd.CommandText = @"select ID, UNIT_NAME, UNIT_NUMBER, FK_GENERATING_STATION, FK_STAGE, GENERATING_VOLTAGE_KV, GENERATION_HIGH_VOLTAGE_KV, MVA_CAPACITY, INSTALLED_CAPACITY, DATETIME_OF_COMMISSIONING, DATETIME_OF_COD, DATETIME_OF_DECOMMISSIONING from GENERATING_UNIT where :id=1 and UNIT_NAME IS NOT NULL and ID IS NOT NULL and FK_GENERATING_STATION IS NOT NULL and FK_STAGE IS NOT NULL"; // Assign id parameter OracleParameter id = new OracleParameter("id", 1); cmd.Parameters.Add(id); //Execute the command and use DataReader to display the data OracleDataReader reader = cmd.ExecuteReader(); List <GeneratorUnitForeign> genUnitsForeign = new List <GeneratorUnitForeign>(); while (reader.Read()) { GeneratorUnitForeign genUnitForeign = new GeneratorUnitForeign(); genUnitForeign.WebUatId = reader.GetInt32(0); genUnitForeign.Name = reader.GetString(1); genUnitForeign.UnitNumber = reader.GetInt32(2); genUnitForeign.GeneratingStationWebUatId = reader.GetInt32(3); genUnitForeign.GeneratorStageWebUatId = reader.GetInt32(4); genUnitForeign.GenVoltageKV = reader.GetDecimal(5); genUnitForeign.GenHighVoltageKV = reader.GetDecimal(6); genUnitForeign.MvaCapacity = reader.GetDecimal(7); genUnitForeign.InstalledCapacity = reader.GetDecimal(8); genUnitForeign.CommDateTime = reader.GetDateTime(9); genUnitForeign.CodDateTime = reader.GetDateTime(10); genUnitForeign.DeCommDateTime = reader.GetDateTime(11); genUnitsForeign.Add(genUnitForeign); } reader.Dispose(); return(genUnitsForeign); } catch (Exception ex) { Console.WriteLine(ex.Message); return(null); } } } }
public List <BrnKpi> GetMultipleBrnKPI(DateTime trandate, string[] BranchCodes) { // decimal openingBal = 0; string connString = ConfigurationManager.ConnectionStrings["AppDbContext"].ToString(); List <BrnKpi> allKpis = new List <BrnKpi>(); #region New using (OracleConnection connection = new OracleConnection(connString)) { string allBranches = ""; foreach (string branch in BranchCodes) { if (branch == BranchCodes.Last()) { allBranches += "\'" + branch + "\'"; } else { allBranches += "\'" + branch + "\',"; } } //BrnKpi kpirow = new BrnKpi(); connection.Open(); string sqlSelect = "SELECT SUM(BRN_CUST_COUNT) AS BRN_CUST_COUNT, ROUND(AVG(BRN_DQI),2) AS BRN_DQI, SUM(BRN_OPEN_EXCEPTIONS) AS BRN_OPEN_EXCEPTIONS, " + "ROUND(AVG(BRN_PCT_CONTRIB),2) AS BRN_PCT_CONTRIB, SUM(BRN_RECURRING_ERRORS) AS BRN_RECURRING_ERRORS, SUM(BRN_RESOLVED_ERRORS) AS BRN_RESOLVED_ERRORS, " + "SUM(BANK_CUST_COUNT) AS BANK_CUST_COUNT, TIER " + "FROM cmdm_common_kpi WHERE BRANCH_CODE IN (" + allBranches + ") " + "GROUP BY TIER ORDER BY TIER ASC"; OracleCommand command = new OracleCommand(sqlSelect, connection); OracleDataReader rdr = command.ExecuteReader(); if (rdr.HasRows) { while (rdr.Read()) { BrnKpi kpirow = new BrnKpi { BANK_CUST_COUNT = rdr.GetInt32(rdr.GetOrdinal("BANK_CUST_COUNT")), BRN_CUST_COUNT = rdr.GetInt32(rdr.GetOrdinal("BRN_CUST_COUNT")), BRN_DQI = rdr.GetDecimal(rdr.GetOrdinal("BRN_DQI")), BRN_OPEN_EXCEPTIONS = rdr.GetInt32(rdr.GetOrdinal("BRN_OPEN_EXCEPTIONS")), BRN_PCT_CONTRIB = rdr.GetDecimal(rdr.GetOrdinal("BRN_PCT_CONTRIB")), BRN_RECURRING_ERRORS = rdr.GetInt32(rdr.GetOrdinal("BRN_RECURRING_ERRORS")), BRN_RESOLVED_ERRORS = rdr.GetInt32(rdr.GetOrdinal("BRN_RESOLVED_ERRORS")) }; allKpis.Add(kpirow); } } return(allKpis); } #endregion }
public void SetOrders(OracleDataReader orderReader, List <order> ord) // Заполнение полей заказа из данных выборки { while (orderReader.Read()) { string[] str = new string[orderReader.VisibleFieldCount]; decimal[] dec = new decimal[2]; int[] ss = new int[3]; for (int i = 0; i < orderReader.VisibleFieldCount; i++) { switch (i) { case 1: ss[0] = orderReader.GetInt32(i); str[i] = ss[0].ToString(); break; case 9: dec[0] = orderReader.GetDecimal(i); str[i] = dec[0].ToString(); break; case 10: dec[1] = orderReader.GetDecimal(i); str[i] = dec[1].ToString(); break; case 13: ss[1] = orderReader.GetInt32(i); str[i] = ss[1].ToString(); break; case 14: ss[2] = orderReader.GetInt32(i); str[i] = ss[2].ToString(); break; default: str[i] = orderReader.GetValue(i).ToString(); break; } if (str[i].Length == 0) { str[i] = ""; } } ord.Add(new order(str, dec, ss)); } }
// GET: Proveedores public ActionResult Index() { if (Session["usuario"] != null) { List <Proveedor> provs = new List <Proveedor>(); String _connstring = "DATA SOURCE=localhost:1521/xe;USER ID=YUYOS;Password=cipres;"; try { OracleConnection _connObj = new OracleConnection(_connstring); _connObj.Open(); OracleCommand _comObj = _connObj.CreateCommand(); _comObj.CommandText = "PKG_PROVEEDORES.SP_S_PROVEEDORES"; _comObj.CommandType = System.Data.CommandType.StoredProcedure; OracleParameter _RefParam = new OracleParameter(); _RefParam.ParameterName = "provCur"; _RefParam.OracleDbType = OracleDbType.RefCursor; _RefParam.Direction = System.Data.ParameterDirection.Output; _comObj.Parameters.Add(_RefParam); OracleDataReader _rdrObj = _comObj.ExecuteReader(); if (_rdrObj.HasRows) { while (_rdrObj.Read()) { provs.Add(new Proveedor { IdProveedor = (int)_rdrObj.GetDecimal(_rdrObj.GetOrdinal("ID_PROVEEDOR")), RutProveedor = _rdrObj.GetString(_rdrObj.GetOrdinal("RUT_PROVEEDOR")), Email = _rdrObj.GetString(_rdrObj.GetOrdinal("EMAIL")), Fono = (long)_rdrObj.GetDecimal(_rdrObj.GetOrdinal("FONO")), Giro = _rdrObj.GetString(_rdrObj.GetOrdinal("GIRO")), RazonSocial = _rdrObj.GetString(_rdrObj.GetOrdinal("RAZON_SOCIAL")), Estado = _rdrObj.GetDecimal(_rdrObj.GetOrdinal("ESTADO")) }); } } _connObj.Close(); _connObj.Dispose(); _connObj = null; } catch (Exception ex) { throw new Exception(ex.Message); } return(View(provs)); } return(Redirect("~/Login/")); }
/// Safely reads decimal values from the database if they are null ///<returns>0 if null otherwise value from DB</returns> decimal SafeReadDecimal(OracleDataReader odr, int colindex) { if (!odr.IsDBNull(colindex)) return odr.GetDecimal(colindex); else return 0; }
internal override DtoBase PopulateDto(OracleDataReader reader) { var turno = new TurneroDto(); // if (!reader.IsDBNull(_ordTurId)) { turno.TurId = reader.GetInt32(_ordTurId); } // if (!reader.IsDBNull(_ordTurTitulo)) { turno.TurTitulo = reader.GetString(_ordTurTitulo); } // if (!reader.IsDBNull(_ordTurDescripcion)) { turno.TurDescripcion = reader.GetString(_ordTurDescripcion); } // if (!reader.IsDBNull(_ordTurFechaIni)) { turno.TurFechaIni = reader.GetDateTime(_ordTurFechaIni); } // if (!reader.IsDBNull(_ordTurFechaFin)) { turno.TurFechaFin = reader.GetDateTime(_ordTurFechaFin); } // if (!reader.IsDBNull(_ordTurTodoDia)) { turno.TurTodoDia = reader.GetString(_ordTurTodoDia); } // if (!reader.IsDBNull(_ordTurPae)) { turno.TurPae = reader.GetInt32(_ordTurPae); } // if (!reader.IsDBNull(_ordTurPro)) { turno.TurPro= reader.GetInt32(_ordTurPro); } // if (!reader.IsDBNull(_ordTurMonto)) { turno.TurMonto = reader.GetDecimal(_ordTurMonto); } // if (!reader.IsDBNull(_ordTurOspId)) { turno.TurOspId = reader.GetInt32(_ordTurOspId); } // IsNew turno.IsNew = false; return turno; }
static decimal SafeReadDecimal(OracleDataReader odr, int ColIndex) { { if (!odr.IsDBNull(ColIndex)) return odr.GetDecimal(ColIndex); else return 0; } }
internal static int? GetIntNullable(OracleDataReader reader, int index, int? defaultValue) { if (reader.IsDBNull(index)) { return null; } int returnValue; if (int.TryParse(reader.GetDecimal(index).ToString(), out returnValue) == true) { return returnValue; } return defaultValue; }
EventDto MapReaderToEventDto(OracleDataReader dataReader) { return new EventDto() { Id = dataReader.GetInt64(ColumnPositions.Id), CommandContext = ConvertToGuid(dataReader.GetOracleBinary(ColumnPositions.CommandContext)), Name = dataReader.GetString(ColumnPositions.Name), LogicalName = dataReader.GetString(ColumnPositions.LogicalName), EventSourceId = ConvertToGuid(dataReader.GetOracleBinary(ColumnPositions.EventSourceId)), EventSource = dataReader.GetString(ColumnPositions.EventSource), Generation = dataReader.GetInt32(ColumnPositions.Generation), Data = dataReader.GetOracleClob(ColumnPositions.Data).Value, CausedBy = dataReader.GetString(ColumnPositions.CausedBy), Origin = dataReader.GetString(ColumnPositions.Origin), Occurred = dataReader.GetDateTime(ColumnPositions.Occured), Version = Convert.ToDouble(dataReader.GetDecimal(ColumnPositions.Version)) }; }
private static LPHSSession ReadSession(OracleDataReader reader) { LPHSSession session = new LPHSSession(); session.ID = (int)reader.GetDecimal(0); session.Name = OracleDatabase.GetString(reader, 1, string.Empty); session.StartDate = OracleDatabase.GetDateTime(reader, 2, DateTime.Now); session.EndDate = OracleDatabase.GetDateTime(reader, 3, DateTime.Now); session.UserSid = OracleDatabase.GetString(reader, 4, string.Empty); return session; }
User loadUser(OracleDataReader odr, bool isCurrentUser) { DateTime? created = null, expiryDate = null; if (!odr.IsDBNull(4)) created = (DateTime)odr.GetDateTime(odr.GetOrdinal("created")); if (!odr.IsDBNull(5)) expiryDate = (DateTime)odr.GetDateTime(odr.GetOrdinal("expiry_date")); if (isCurrentUser) return new CurrentUser( odr.GetDecimal(odr.GetOrdinal("user_id")), odr.GetString(odr.GetOrdinal("username")), odr.GetString(odr.GetOrdinal("default_tablespace")), odr.GetString(odr.GetOrdinal("temporary_tablespace")), created, expiryDate, session); else return new User( odr.GetDecimal(odr.GetOrdinal("user_id")), odr.GetString(odr.GetOrdinal("username")), odr.GetString(odr.GetOrdinal("default_tablespace")), odr.GetString(odr.GetOrdinal("temporary_tablespace")), created, expiryDate, session); }
public static Quota LoadQuota(OracleDataReader odr) { string tablespaceName = odr.GetString(odr.GetOrdinal("tablespace_name")); string userName = odr.GetString(odr.GetOrdinal("username")); decimal? bytes = null, maxBytes = null, maxBlocks = null; decimal blocks = odr.GetDecimal(odr.GetOrdinal("blocks")); if (!odr.IsDBNull(odr.GetOrdinal("bytes"))) bytes = odr.GetDecimal(odr.GetOrdinal("bytes")); if (!odr.IsDBNull(odr.GetOrdinal("max_bytes"))) maxBytes = odr.GetDecimal(odr.GetOrdinal("max_bytes")); if (!odr.IsDBNull(odr.GetOrdinal("max_blocks"))) maxBlocks = odr.GetDecimal(odr.GetOrdinal("max_blocks")); return new Quota(tablespaceName, userName, blocks, maxBlocks, bytes, maxBytes); }
private static LPHSSchool ReadSchool(OracleDataReader reader) { LPHSSchool school = new LPHSSchool(); school.ID = (int)reader.GetDecimal(0); school.Name = OracleDatabase.GetString(reader, 1, null); // reader.IsDBNull(1) ? null : reader.GetString(1), school.Address1 = OracleDatabase.GetString(reader, 2, null); // reader.IsDBNull(2) ? null : reader.GetString(2), school.Address2 = OracleDatabase.GetString(reader, 3, null); // reader.IsDBNull(3) ? null : reader.GetString(3), school.Address3 = OracleDatabase.GetString(reader, 4, null); //reader.IsDBNull(4) ? null : reader.GetString(4), school.City = OracleDatabase.GetString(reader, 5, null); // reader.IsDBNull(5) ? null : reader.GetString(5), school.State = OracleDatabase.GetString(reader, 6, null); // reader.IsDBNull(6) ? null : reader.GetString(6), school.Zip = OracleDatabase.GetString(reader, 7, null); // reader.IsDBNull(7) ? null : reader.GetString(7), school.CoordinatorID = OracleDatabase.GetIntNullable(reader, 8, null); school.Active = OracleDatabase.GetBooleanFromString(reader, 9); school.RegionID = OracleDatabase.GetInt(reader, 10, -1); school.SessionID = OracleDatabase.GetInt(reader, 11, -1); return school; }
internal static decimal GetDecimal(OracleDataReader reader, int index, decimal defaultValue) { return (reader.IsDBNull(index) || !(reader.GetValue(index) is Decimal)) ? defaultValue : (decimal)reader.GetDecimal(index); }