public static DBInternalMessages DBNonQuery(string conStr, string sqlstr) { OracleConnection _dbconnection = new OracleConnection(conStr); _dbconnection.Open(); try { if (_dbconnection.State == ConnectionState.Open) { OracleCommand _command = new OracleCommand(sqlstr, _dbconnection); int r = _command.ExecuteNonQuery(); _dbconnection.Close(); if (r > 0) { return DBInternalMessages.DB_NonQuerySuccess;// "Success"; } else { return DBInternalMessages.DB_NonQueryFailed; } } else { return DBInternalMessages.DB_NOT_OPEN; //db not open } } catch (Exception ex) { _dbconnection.Close(); LoggingHelper.WriteLog("AkkaTestServer", "DBNonQuery Ex:" + ex.Message); return DBInternalMessages.DB_Exception; //ex } }
public static List<string> retEscalatedIssues(string custId) { using (OracleConnection connection = new OracleConnection(connection_string)) { connection.Open(); string query = "select report_name from reportrepair where customer_id LIKE :cust and status='escalated'"; OracleCommand command = new OracleCommand(query, connection); command.Parameters.Add(":cust", OracleDbType.NVarchar2).Value = custId; List<string> listobj = new List<string>(); OracleDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { listobj.Add(reader.GetValue(0).ToString()); } connection.Close(); return listobj; } catch (Exception ex) { List<string> listobj1 = new List<string>(); listobj1.Add("error"); return listobj1; } } }
public void AddNewBroker(Broker brokerToAdd) { // disconnected version string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)"; IDbConnection connection = new OracleConnection(connectionString); OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection); command.BindByName = true; IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25); param.Value = brokerToAdd.firstName; command.Parameters.Add(param); param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25); param.Value = brokerToAdd.lastName; command.Parameters.Add(param); param = new OracleParameter(":broker_id", OracleDbType.Int16, 50); param.Value = brokerToAdd.id; command.Parameters.Add(param); IDbDataAdapter da = new OracleDataAdapter(); da.InsertCommand = command; DataRow newRow = dataSet.Tables[0].NewRow(); newRow["broker_id"] = brokerToAdd.id; newRow["first_name"] = brokerToAdd.firstName; newRow["last_name"] = brokerToAdd.lastName; dataSet.Tables[0].Rows.Add(newRow); // it's not clear from this code but we could make many changes to // the dataset before submitting back to the database. connection.Open(); da.Update(dataSet); connection.Close(); }
/// <summary> /// 执行数据库查询操作,返回受影响的行数 /// </summary> /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param> /// <param name="commandParameters">命令参数集合</param> /// <returns>当前查询操作影响的数据行数</returns> public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters) { OracleCommand command = new OracleCommand(); OracleConnection connection = new OracleConnection(connstr); int result = 0; try { PrepareCommand(command, connection, null, CommandType.Text, cmdText, commandParameters); result = command.ExecuteNonQuery(); command.Parameters.Clear(); } catch { throw; } finally { command.Dispose(); connection.Close(); connection.Dispose(); } return result; }
public List<DepartamentoDTO> getDepartamento(int idPais = -1) { List<DepartamentoDTO> lstDepar = new List<DepartamentoDTO>(); Parametros p = new Parametros(); DepartamentoDTO itemDep; using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString())) { con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = (idPais != -1) ? "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento where IDPAIS = " + idPais.ToString() : "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento"; using (OracleDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { itemDep = new DepartamentoDTO(); itemDep.idDepartamento = reader.GetInt32(0); itemDep.idPais = reader.GetInt32(1); itemDep.nombreDepartamento = reader.GetValue(2).ToString(); lstDepar.Add(itemDep); } } con.Close(); con.Dispose(); } return lstDepar; }
// GET: Album // [HttpPost] public List<AlbumModels> Album() { OracleConnection conn = new OracleConnection(oradb); conn.Open(); string selectStr = /* "insert into album(albumID,albumname,username) values('111','testalbum','KING')";*/ "select * " + "from album "; // " +(Session["username"] as string)+" OracleCommand cmd = new OracleCommand(selectStr, conn); OracleDataReader dr = cmd.ExecuteReader(); var Models = new List<AlbumModels>(); while (dr.Read()) { AlbumModels model = new AlbumModels(); model.AlbumName = dr["albumname"].ToString(); model.AlbumId = dr["albumid"].ToString(); //model.Username = Session["username"] as string ; string selectStr1 = "select * from photo where albumid = " + model.AlbumId; OracleCommand cmd1 = new OracleCommand(selectStr1, conn); OracleDataReader dr1 = cmd1.ExecuteReader(); dr1.Read(); model.Firstphoto = dr1["photoaddress"].ToString(); Models.Add(model); //Session.Add("currentAlbumName", albumName); //Session.Add("currentAlbumId", albumId); //点击时保存到session } dr.Close(); int insertedLines = cmd.ExecuteNonQuery(); conn.Close(); conn.Dispose(); return Models; }
protected void Button1_Click(object sender, EventArgs e) { try { string strConn = "Data Source=orcl;User Id=chatroom;Password=chatroom;Integrated Security=no;"; OracleConnection oleDbConnection = new OracleConnection(strConn); oleDbConnection.Open(); string strSql; if (this.name.Text.ToString() == "") { strSql = "select * from scott.ChatRoomLog order by id desc"; } else { strSql = "select * from scott.chatroomlog where name='" + this.name.Text.ToString() + "' order by id"; } System.Data.OracleClient.OracleCommand oleDbCommand = new System.Data.OracleClient.OracleCommand(); oleDbCommand.CommandText = strSql; oleDbCommand.Connection = oleDbConnection; System.Data.OracleClient.OracleDataAdapter oleDbDataAdapter = new System.Data.OracleClient.OracleDataAdapter(oleDbCommand); DataTable table = new DataTable(); oleDbDataAdapter.Fill(table); oleDbConnection.Close(); this.GridView1.DataSource = table.DefaultView; this.GridView1.DataBind(); } catch { } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Global.CurrentPerson == null) Response.Redirect("Index.aspx"); DataTable seasonsTable = new DataTable(); List<Season> seasons = new List<Season>(); using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { // Set up the seasons command var seasonsCommand = new OracleCommand("TICKETS_QUERIES.getSeasonsForPurchase", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; seasonsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); seasonsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input); try { // Execute the queries and auto map the results to models objConn.Open(); var seasonsAdapter = new OracleDataAdapter(seasonsCommand); seasonsAdapter.Fill(seasonsTable); seasons = Mapper.DynamicMap<IDataReader, List<Season>>(seasonsTable.CreateDataReader()); } catch (Exception) { Response.Redirect("Index.aspx"); } objConn.Close(); } // Fill list dropdowns with data from the database if (seasons.Count > 0) { var seasonsWithEvents = seasons.GroupBy(s => s.season_id).Select(season => new Season() { season_id = season.First().season_id, name = season.First().name, price = season.First().price, ticket_count = season.First().ticket_count, event_names = seasons.Where(ev => ev.season_id == season.First().season_id).Select(en => en.event_name).ToList() }).ToList(); SeasonDropDown.DataTextField = "name"; SeasonDropDown.DataValueField = "season_id"; SeasonDropDown.DataSource = seasonsWithEvents; SeasonDropDown.DataBind(); SeasonListView.DataSource = seasonsWithEvents; SeasonListView.DataBind(); } if (Request.QueryString["Success"] != null) { Error.Text = "Successfully purchased season ticket!"; Error.Visible = true; } } }
public Aluno_Cadastrar(int mat_instrut) { InitializeComponent(); matricula_instrutor = mat_instrut; try { OracleConnection conn = new OracleConnection(oradb); conn.Open(); OracleCommand oda = new OracleCommand("SELECT COUNT(matricula) FROM pessoa", conn); OracleDataReader reader = oda.ExecuteReader(); if (reader.HasRows) { reader.Read(); int tot_matr = reader.GetInt32(0) + 1; tbx_matricula.Text = Convert.ToString(tot_matr); } conn.Close(); } catch (Exception ex) // detecta todos os erros { MessageBox.Show(ex.Message.ToString()); } }
/// <summary> /// Selects and retrieves values from the database /// </summary> /// <param name="query">The selection statement</param> /// <returns>A DataTable with the retrieved values></returns> public static DataTable RetrieveQuery(string query) { if (Regex.IsMatch(query, @"-{2,}")) { throw new SQLInjectionException(); } using (OracleConnection c = new OracleConnection(@connectionstring)) { try { c.Open(); OracleCommand cmd = new OracleCommand(@query); cmd.Connection = c; try { OracleDataReader r = cmd.ExecuteReader(); DataTable result = new DataTable(); result.Load(r); c.Close(); return result; } catch (OracleException e) { Console.Write(e.Message); throw; } } catch (OracleException e) { Console.Write(e.Message); return new DataTable(); } } }
public static string addbindCompanyHardware(string companyId, string hardwareId) { OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString); OracleCommand cmd = new OracleCommand(); string query = "INSERT INTO IzdavaHardver(koid,hardid) values (:koid,:hardid)"; cmd.Connection = oracleConnection; cmd.CommandText = query; cmd.Parameters.Add(new OracleParameter("koid", companyId)); cmd.Parameters.Add(new OracleParameter("hardid", hardwareId)); try { oracleConnection.Open(); int effect = cmd.ExecuteNonQuery(); if (effect > 0) { return "Врската е успешно додадена"; } } catch (Exception e) { return e.Message; } finally { oracleConnection.Close(); } return "проблем со базата"; }
public override Entity Get(long id, Type entityType) { foreach (var es in eStates) { if (es.Entity.ID == id) { return(es.Entity); } } OracleConnection connection = GetConnection(); try { connection.Open(); IEntityBroker broker = GetBroker(entityType); Entity entity = broker.Get(id, connection); eStates.Add(new EntityState(entity, State.UNCHANGED)); return(entity); } finally { connection?.Close(); } }
public static void reportIssue(string cust_id, string rep_name, string rep_desc) { try { using(OracleConnection connection=new OracleConnection(connection_string)) { rep_id++; repIdCopy = rep_id; string raised_date = DateTime.Now.ToString("dd-MMM-yy"); string provision_date = DateTime.Now.AddDays(7).ToString("dd-MMM-yy"); connection.Open(); string query = "insert into reportrepair values('" + cust_id + "'," + rep_id + ", '" + rep_name + "', '" + rep_desc + "','" + raised_date + "' , 'pending', '" + provision_date + "')"; OracleCommand command = new OracleCommand(query, connection); int count = command.ExecuteNonQuery(); if (count > 0) { Console.WriteLine("Inserted"); } else { Console.WriteLine("Not Inserted"); } connection.Close(); } } catch (Exception ex) { } }
protected void AcceptButton_Click(object sender, EventArgs e) { bool failed = false; SeriesId = int.Parse(SeriesIdField.Value); using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { // Set up the accepting group command var acceptCommand = new OracleCommand("TICKETS_API.acceptRequest", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; acceptCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input); acceptCommand.Parameters.Add("p_GroupId", OracleDbType.Int64, long.Parse(((Button)sender).CommandArgument), ParameterDirection.Input); try { // Execute the command objConn.Open(); acceptCommand.ExecuteNonQuery(); } catch (Exception) { failed = true; Error.Text = "Could not accept the group. Try again later"; Error.Visible = true; } objConn.Close(); } if (!failed) Response.Redirect("AcceptedGroup.aspx?Series=" + SeriesId); }
private void LoginRandomEmployee() { DataTable employeeTable = new DataTable(); Person employee = null; using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { OracleCommand objCmd = new OracleCommand("TICKETS_QUERIES.getRandomEmployee", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; objCmd.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); try { objConn.Open(); OracleDataAdapter adapter = new OracleDataAdapter(objCmd); adapter.Fill(employeeTable); employee = Mapper.DynamicMap<IDataReader, List<Person>>(employeeTable.CreateDataReader()).FirstOrDefault(); } catch (Exception) { Response.Redirect("../Default.aspx"); } objConn.Close(); } if (employee != null) { Global.CurrentPerson = employee; Global.CurrentPerson.accountType = Account.Faculty; } else Response.Redirect("../Default.aspx"); }
public List<PaisDTO> getPais() { List<PaisDTO> lstPais = new List<PaisDTO>(); Parametros p = new Parametros(); PaisDTO itemPais; using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString())) { con.Open(); OracleCommand cmd = con.CreateCommand(); cmd.CommandText = "select IDPAIS, NOMBREPAIS from pais"; using (OracleDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { itemPais = new PaisDTO(); itemPais.idPais = reader.GetInt32(0); itemPais.nombrePais = reader.GetString(1); lstPais.Add(itemPais); } } con.Close(); con.Dispose(); } return lstPais; }
public async Task <ApplicationUser> getUserAsync(string UserName) { string sql = "SELECT * FROM \"AspNetUsers\" " + " where \"UserName\" = :UserName "; string constr = Startup.ConnectionString; using (var con = new OracleConnection(constr)) { try { con.Open(); var result = await con.QueryAsync <ApplicationUser>(sql, new { UserName = UserName }); foreach (var d in result) { return(d); } return(null); } catch (OracleException ex) { Console.WriteLine(ex); throw; } finally { con?.Close(); } } }
public List<Licence> GetAllLicence(string status, string licenceName) { var items = new List<Licence>(); string commandText = "SELECT DISTINCT L.* FROM PUB_ACS.PAS_LICENCE L"; if((!string.IsNullOrEmpty(status)) || (!string.IsNullOrEmpty(licenceName))) { commandText += " WHERE"; } if (status.Equals("active")) { commandText += " L.END_DATE IS NULL"; if (!string.IsNullOrEmpty(licenceName)) commandText += " AND"; } if (!string.IsNullOrEmpty(licenceName)) { commandText += " UPPER(L.LICENCE_NAME) LIKE '%" + licenceName.ToUpper().Trim() + "%'"; } using (OracleConnection con = new OracleConnection(MdallDBConnection)) { OracleCommand cmd = new OracleCommand(commandText, con); try { con.Open(); using (OracleDataReader dr = cmd.ExecuteReader()) { if (dr.HasRows) { while (dr.Read()) { var item = new Licence(); item.original_licence_no = dr["ORIGINAL_LICENCE_NO"] == DBNull.Value ? 0 : Convert.ToInt32(dr["ORIGINAL_LICENCE_NO"]); item.licence_status = dr["LICENCE_STATUS"] == DBNull.Value ? string.Empty : dr["LICENCE_STATUS"].ToString().Trim(); item.application_id = dr["APPLICATION_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPLICATION_ID"]); item.appl_risk_class = dr["APPL_RISK_CLASS"] == DBNull.Value ? 0 : Convert.ToInt32(dr["APPL_RISK_CLASS"]); item.licence_name = dr["LICENCE_NAME"] == DBNull.Value ? string.Empty : dr["LICENCE_NAME"].ToString().Trim(); item.first_licence_status_dt = dr["FIRST_LICENCE_STATUS_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["FIRST_LICENCE_STATUS_DT"]); item.last_refresh_dt = dr["LAST_REFRESH_DT"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["LAST_REFRESH_DT"]); item.end_date = dr["END_DATE"] == DBNull.Value ? (DateTime?)null : Convert.ToDateTime(dr["END_DATE"]); item.licence_type_cd = dr["LICENCE_TYPE_CD"] == DBNull.Value ? string.Empty : dr["LICENCE_TYPE_CD"].ToString().Trim(); item.company_id = dr["COMPANY_ID"] == DBNull.Value ? 0 : Convert.ToInt32(dr["COMPANY_ID"]); items.Add(item); } } } } catch (Exception ex) { string errorMessages = string.Format("DbConnection.cs - GetAllLicence()"); ExceptionHelper.LogException(ex, errorMessages); } finally { if (con.State == ConnectionState.Open) con.Close(); } } return items; }
public ActionResult Index() { OracleConnection myConnection = new OracleConnection(); myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString; myConnection.Open(); myConnection.Close(); return View(); }
protected void Page_Load(object sender, EventArgs e) { if (Global.CurrentPerson == null || string.IsNullOrEmpty(Request.QueryString["Series"])) Response.Redirect("Index.aspx"); SeriesId = long.Parse(Request.QueryString["Series"]); DataTable eventSeatsTable = new DataTable(); List<Models.EventSeats> eventSeatsModel = new List<Models.EventSeats>(); using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { // Set up the getEventSeats command var eventSeatsCommand = new OracleCommand("TICKETS_QUERIES.getEventSeats", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; eventSeatsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); eventSeatsCommand.Parameters.Add("p_SeriesId", OracleDbType.Int64, SeriesId, ParameterDirection.Input); eventSeatsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input); try { // Execute the queries and auto map the results to models objConn.Open(); var eventSeatsAdapter = new OracleDataAdapter(eventSeatsCommand); eventSeatsAdapter.Fill(eventSeatsTable); eventSeatsModel = Mapper.DynamicMap<IDataReader, List<Models.EventSeats>>(eventSeatsTable.CreateDataReader()); } catch (Exception) { Response.Redirect("EventSignup.aspx?Series=" + SeriesId); } objConn.Close(); // If the person already has tickets, redirect them to the page where they can review it if (eventSeatsModel.Any()) { Event.Text = eventSeatsModel.FirstOrDefault().name; Date.InnerText = eventSeatsModel.FirstOrDefault().event_datetime.ToString("dddd, MMMM d - h:mm tt"); Section.InnerText = eventSeatsModel.FirstOrDefault().description; string location = string.Format("Row {0}, ", eventSeatsModel.FirstOrDefault().seat_row); if (eventSeatsModel.Count > 1) { location += string.Format("Seats {0}-{1}", eventSeatsModel.Min(t => t.seat_number), eventSeatsModel.Max(t => t.seat_number)); } else { location += "Seat " + eventSeatsModel.FirstOrDefault().seat_number.ToString(); } Location.InnerText = location; Door.InnerText = "Enter By Door " + eventSeatsModel.FirstOrDefault().door; } else Response.Redirect("EventSignup.aspx?Series=" + SeriesId); } }
public IEnumerable<PerformanceDetail> GetPerformanceDetails(int rmsId, int nbrOfDays, DateTime startDateTime) { OracleConnection connection = new OracleConnection(db.ConnectionString); connection.Open(); try { OracleCommand cmd = new OracleCommand("RMS.GET_DXI_PERFORMANCE_DETAILS", connection); cmd.CommandType = CommandType.StoredProcedure; OracleParameter in1_rmsId = new OracleParameter(); in1_rmsId.OracleDbType = OracleDbType.Int32; in1_rmsId.Direction = ParameterDirection.Input; in1_rmsId.Value = rmsId; cmd.Parameters.Add(in1_rmsId); OracleParameter in2_nbDays = new OracleParameter(); in2_nbDays.OracleDbType = OracleDbType.Int32; in2_nbDays.Direction = ParameterDirection.Input; in2_nbDays.Value = nbrOfDays; cmd.Parameters.Add(in2_nbDays); OracleParameter in3_timestamp = new OracleParameter(); in3_timestamp.OracleDbType = OracleDbType.TimeStamp; in3_timestamp.Direction = ParameterDirection.Input; in3_timestamp.Value = startDateTime; cmd.Parameters.Add(in3_timestamp); OracleParameter out1_PerformanceData = new OracleParameter(); out1_PerformanceData.OracleDbType = OracleDbType.RefCursor; out1_PerformanceData.Direction = ParameterDirection.Output; out1_PerformanceData.Size = 1000; cmd.Parameters.Add(out1_PerformanceData); DataSet dataset = new DataSet(); OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { PerformanceDetail currentDetail = new PerformanceDetail(); currentDetail.RMD_Id = rmsId; currentDetail.Start_Time = reader.GetDateTime(1); currentDetail.End_Time = reader.GetDateTime(2); OracleDecimal oracleAvailability = reader.GetOracleDecimal(3); currentDetail.Availability = OracleDecimal.Round(oracleAvailability, 25).Value; decimal nbDays = reader.GetDecimal(4); currentDetail.Day_Number = (int)nbDays; yield return currentDetail; } } finally { connection.Close(); } }
//根据查询SQL,产生提供给报表生成需要的 XML 数据,采用 Oracle 数据引擎,字段值为空也产生数据 public static void FullGenNodeXmlData(System.Web.UI.Page DataPage, string QuerySQL, bool ToCompress) { OracleConnection myConn = new OracleConnection(OracleConnStr); OracleCommand myCommand = new OracleCommand(QuerySQL, myConn); myConn.Open(); OracleDataReader myReader = myCommand.ExecuteReader(); XMLReportData.GenNodeXmlDataFromReader(DataPage, myReader, ToCompress ? ResponseDataType.ZipBinary : ResponseDataType.PlainText); myReader.Close(); myConn.Close(); }
public AddAnEmployee() { InitializeComponent(); ex_empid.Hide(); ex_ename.Hide(); ex_inss.Hide(); ex_payroll.Hide(); ex_sal.Hide(); msg_bno.Show(); msg_bno.Text = "Please Select A Branch"; msg_mgr.Show(); msg_mgr.Text = "Please Select A Manager"; // string _sql; try { DataSet _ds; OracleConnection _connObj = new OracleConnection(_connstring); _ds = new DataSet(); _connObj.Open(); OracleCommand ShowBNO = new OracleCommand(); ShowBNO.Connection = _connObj; ShowBNO.CommandText = "select bno from branch"; ShowBNO.CommandType = CommandType.Text; OracleDataReader DataRead = ShowBNO.ExecuteReader(); DataRead.Read(); while (DataRead.Read()) { bno.Items.Add(DataRead.GetString(1)); } ShowBNO.Dispose(); //_sql = "SELECT * FROM branch"; // OracleDataAdapter _adapterObj = new OracleDataAdapter(_sql, _connObj); // _adapterObj.Fill(_ds); _connObj.Close(); _connObj.Dispose(); _connObj = null; bno.DataSource = _ds.Tables[0] ; dataGridView1.DataSource = _ds.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
public static void Main (string[] args) { OracleConnection con = new OracleConnection(); con.ConnectionString = connectionString; con.Open(); CLOBTest (con); ReadClob (con); con.Close(); con = null; }
protected void Page_Load(object sender, EventArgs e) { string connectionString = "DATA SOURCE=192.168.36.135:1521/DEV; PASSWORD=pw;PERSIST SECURITY INFO=True;USER ID=MDM"; OracleConnection _connection = new OracleConnection(); _connection.ConnectionString = connectionString; DataTable dataTable = new DataTable(); _connection.Open(); string sql = "select * from test_sa"; OracleCommand command = new OracleCommand(sql, _connection); GridView1.DataSource = command.ExecuteReader(); GridView1.DataBind(); _connection.Close(); }
private void DataUpdateWorker_DoWork(object sender, DoWorkEventArgs e) { DataUpdateForm form = e.Argument as DataUpdateForm; OracleConnection connection = new OracleConnection(Program.MainForm.current.Parameters["ConnectionString"].StringValue); connection.Open(); OracleProc.Execute(connection, "DataUpdatePKG.UpdateAllPlantsData", //new string[] { "pDate1", "pDate2" }, new object[] { form.Date1.Value, form.Date2.Value }); connection.Close(); //Program.MainForm.StatusLabel.Text = "Готово"; MessageBox.Show("Обновление данных с региональных предприятий завершено. Для обновления отчета нажмите кнопку 'Обновить' на понели инструментов", "Обновление данных", MessageBoxButtons.OK, MessageBoxIcon.Information); }
protected void Page_Load(object sender, EventArgs e) { baglanti = new OracleConnection(constring); baglanti.Open(); komut = new OracleCommand(); komut.Connection = baglanti; komut.CommandText = "select mus_id,kul_ad,isim,dogum_tarih,adres,mail,cep_tel,kayit_tarih from t_musteri order by mus_id"; rd = komut.ExecuteReader(); Musteri_Liste.DataSource = rd; Musteri_Liste.DataBind(); baglanti.Close(); mus_sayisi.Text = " Müşteri Sayısı : "+Musteri_Liste.Rows.Count.ToString(); }
// Public public void Query() { using (OracleConnection connection = new OracleConnection( String.Format("User Id={0}; Password={1}; Data Source={2}", Common.SysProp.oracleLogin, Common.SysProp.oraclePassword, Common.SysProp.oracleAliase))) { connection.Open(); OracleDataAdapter da = new OracleDataAdapter(this.sql, connection); dataTable.Clear(); da.Fill(dataTable); connection.Close(); } }
public DataTable SelectData(string commandString, OracleParameter[] parameters) { DataTable result = new DataTable(); try { if (commandString == string.Empty) { throw new ArgumentNullException("SQL command is empty."); } connection = new OracleConnection(connectionString); using (connection) { command = new OracleCommand(commandString, connection); if (parameters != null) { command.Parameters.AddRange(parameters); } dataAdapter = new OracleDataAdapter(command); connection.Open(); dataAdapter.Fill(result); connection.Close(); } } catch (ArgumentNullException argumentEx) { ClearResources(); throw argumentEx; } catch (OracleException oracleEx) { ClearResources(); throw oracleEx; } catch (Exception ex) { ClearResources(); throw ex; } ClearResources(); return result; }
//public ActionResult Add() //{ // return RedirectToAction("Search","Account"); //} //添加好友 public ActionResult Add(string friendname) { string loginuser = Session["username"] as string; OracleConnection conn = new OracleConnection(oradb); conn.Open(); string addStr = "insert into friend(username, friendname, homeauthority, galleryauthority)values('" + loginuser + "', '" + friendname + "', 1, 1)"; string addStr2 = "insert into friend(username, friendname, homeauthority, galleryauthority)values('" + friendname + "', '" + loginuser + "', 1, 1)"; OracleCommand cmd = new OracleCommand(addStr, conn); OracleCommand cmd2 = new OracleCommand(addStr2, conn); int insertedLines = cmd.ExecuteNonQuery(); int insertedLines2 = cmd2.ExecuteNonQuery(); conn.Close(); return RedirectToAction("Search", "Account"); }
//根据查询 SQL,产生提供给报表生成需要的 XML 或 JSON 数据,采用 Oracle 数据引擎, 这里只产生报表参数数据 //当报表没有明细时,调用本方法生成数据,查询 SQL 应该只能查询出一条记录 protected static void DoGenParameterData(System.Web.UI.Page DataPage, string ParameterQuerySQL, bool IsJSON) { OracleConnection myConn = new OracleConnection(OracleConnStr); OracleCommand myCommand = new OracleCommand(ParameterQuerySQL, myConn); myConn.Open(); OracleDataReader myReader = myCommand.ExecuteReader(); if (IsJSON) JSONReportData.GenParameterData(DataPage, myReader); else XMLReportData.GenParameterData(DataPage, myReader); myReader.Close(); myConn.Close(); }
public string UpdateSponsorTransferRequest(SponsorTransferRequest request, long userIdNumber) { var returnValue = string.Empty; var connection = new OracleConnection(_connectionString); OracleCommand command = new OracleCommand { CommandType = CommandType.StoredProcedure, CommandText = $@"{_schema}.srv_trf_appl$web_upd_order", Connection = connection }; command.Parameters.Add("P_LAB_OFF", request.LaborOfficeId); command.Parameters.Add("P_SER_YY", request.Year); command.Parameters.Add("P_SER_NO", request.SeqeunceNumber); command.Parameters.Add("P_ID_NO", request.IdNumber); command.Parameters.Add("P_TRS_STUS", (int)request.Status); command.Parameters.Add("o_lab_off_cmpy", request.OldEstablishmentLaborOfficeId); command.Parameters.Add("o_cmpy_no", request.OldEstablishmentSequenceNumber); command.Parameters.Add("p_user_IdNo", userIdNumber); command.Parameters.Add("RetVal", OracleDbType.Varchar2, 200, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Current, string.Empty); try { connection.Open(); command.ExecuteNonQuery(); returnValue = command.Parameters["RetVal"].Value.ToString(); if (string.IsNullOrEmpty(returnValue) || returnValue == "-1" || returnValue == "-2" || returnValue == "-3" || returnValue == "-4" || returnValue == "-5") { throw new Exception($"Oracle transaction failed during ST Update, oracle statement return {returnValue}"); } } catch (Exception ex) { throw; } finally { connection?.Close(); } return(returnValue.Trim()); }
private bool disposedValue = false; // To detect redundant calls void Dispose(bool disposing) { if (!disposedValue) { if (disposing) { // TODO: dispose managed state (managed objects). _connection?.Dispose(); _connection?.Close(); } // TODO: free unmanaged resources (unmanaged objects) and override a finalizer below. // TODO: set large fields to null. disposedValue = true; } }
/// <summary> Check connection string for correctness. </summary> /// <returns> Returns true if the connection string is valid. </returns> public bool TestConnectionString(string connectionString) { bool isConnectionOpen = false; OracleConnection connection = null; try { connection = new OracleConnection(connectionString); connection.Open(); isConnectionOpen = true; } catch { connection?.Close(); } return(isConnectionOpen); }
public override void CloseTransaction(bool isAllOk) { if (_oracleTransaction != null) { if (IsAllOkTransaction) { _oracleTransaction.Commit(); } else { _oracleTransaction.Rollback(); } } _transactionActive = false; _oracleConnection?.Close(); IsAllOkTransaction = false; }
public override void Save() { OracleConnection connection = GetConnection(); OracleTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); foreach (var entity in eStates) { if (entity.EState == State.NEW) { InsertOne(entity.Entity, connection, transaction); } if (entity.EState == State.DELETED) { DeleteOne(entity.Entity, connection, transaction); } if (entity.EState == State.CHANGED) { UpdateOne(entity.Entity, connection, transaction); } } transaction.Commit(); } catch (Exception ex) { transaction?.Rollback(); Console.WriteLine(ex.Message); } finally { connection?.Close(); } }
private bool LoginUser(string dataSource, string username, string password) { IDbConnection con = null; try { con = new OracleConnection(); con.ConnectionString = String.Format(config.GetConnectionString("UserConnection"), dataSource, username, password); con.Open(); return(true); } catch (Exception e) { ModelState.AddModelError(string.Empty, "Invalid login attempt."); return(false); } finally { con?.Close(); con?.Dispose(); } }
public override void Save(IEnumerable <Entity> entitiesForInsert, IEnumerable <Entity> entitiesForUpdate, IEnumerable <Entity> entitiesForDelete) { OracleConnection connection = GetConnection(); OracleTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); if ((entitiesForInsert != null) && entitiesForInsert.Count() > 0) { InsertImpl(entitiesForInsert, connection, transaction); } if ((entitiesForUpdate != null) && entitiesForUpdate.Count() > 0) { UpdateImpl(entitiesForUpdate, connection, transaction); } if ((entitiesForDelete != null) && entitiesForDelete.Count() > 0) { DeleteImpl(entitiesForDelete, connection, transaction); } transaction.Commit(); } catch (Exception ex) { transaction?.Rollback(); Console.WriteLine(ex.Message); } finally { connection?.Close(); } }
public static string GetApplicationMessages() { OracleConnection oraConn; OracleCommand oraComm; oraComm = new OracleCommand(); string connectionString = ConfigurationManager.ConnectionStrings["LIVE"].ConnectionString; oraConn = new OracleConnection(connectionString); oraConn.Open(); oraComm.Connection = oraConn; oraComm.CommandType = CommandType.StoredProcedure; oraComm.CommandText = "uhs_epr.pkg_epr_dashboard.application_messages_active"; OracleParameter p2 = oraComm.Parameters.Add("p_cursor", OracleDbType.RefCursor); p2.Direction = ParameterDirection.Output; string html; try { oraComm.ExecuteNonQuery(); OracleDataReader reader = ((OracleRefCursor)p2.Value).GetDataReader(); if (reader.HasRows) { html = "<table class=GridViewStyle>"; html += "<thead>"; html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<th class=databasecathead>" + Regex.Replace(reader.GetName(i), "_", " ") + "</th>"; } html += "</tr>"; html += "</thead>"; html += "<tbody>"; while (reader.Read()) { html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<td class=databasecat>" + Regex.Replace(reader.GetValue(i).ToString(), "00.00.00", "") + "</td>"; } html += "</tr>"; } html += "</tbody>"; html += "</table>"; return(html); } else { string html2 = "No data found"; return(html2); } reader.Close(); } catch (Exception e) { return(e.Message); } finally { oraConn.Close(); } }
public static void ExportToCSV(string query, string fileName) { //Add Response header HttpResponse Response = HttpContext.Current.Response; Response.Clear(); Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", fileName)); Response.Charset = ""; Response.ContentType = "application/vnd.xls"; //GET Data From Database OracleConnection cn = WebTools.GetIpmsConnection(); OracleCommand cmd = new OracleCommand(query, cn); cmd.CommandTimeout = 999999; cmd.CommandType = CommandType.Text; try { //cn.Open(); OracleDataReader dr = cmd.ExecuteReader(); StringBuilder sb = new StringBuilder(); //CSV Header for (int count = 0; count < dr.FieldCount; count++) { if (dr.GetName(count) != null) { sb.Append(dr.GetName(count)); } if (count < dr.FieldCount - 1) { sb.Append(","); } } Response.Write(sb.ToString() + "\n"); Response.Flush(); //CSV Body while (dr.Read()) { sb = new StringBuilder(); for (int col = 0; col < dr.FieldCount - 1; col++) { if (!dr.IsDBNull(col)) { if (dr.GetDataTypeName(col).ToUpper() == "DATE") { sb.Append(DateTime.Parse(dr.GetValue(col).ToString()).ToString("dd-MMM-yyyy")); } else { sb.Append(dr.GetValue(col).ToString().Replace(",", " ")); } } sb.Append(","); } if (!dr.IsDBNull(dr.FieldCount - 1)) { if (!dr.IsDBNull(dr.FieldCount - 1)) { if (dr.GetDataTypeName(dr.FieldCount - 1).ToUpper() == "DATE") { sb.Append(DateTime.Parse(dr.GetValue(dr.FieldCount - 1).ToString()).ToString("dd-MMM-yyyy")); } else { sb.Append(dr.GetValue(dr.FieldCount - 1).ToString().Replace(",", " ")); } } } Response.Write(sb.ToString() + "\n"); Response.Flush(); } dr.Dispose(); } catch (Exception ex) { Response.Write(ex.Message); } finally { cmd.Connection.Close(); cn.Close(); } Response.End(); }
protected void DropTeam_SelectedIndexChanged(object sender, EventArgs e) { TeamDetailsDiv.Visible = true; TeamNameLabel.Text = DropTeam.SelectedItem.Text; string team = DropTeam.SelectedItem.Text; Dictionary <string, string> teamDetailsQueryMap = QueryBuilderClass.TeamDetailsQueryBuilder(team); string oracleConnectionString = ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString; OracleConnection con = new OracleConnection(oracleConnectionString); try { con.Open(); foreach (string query in teamDetailsQueryMap.Values) { OracleCommand cmd = new OracleCommand(query, con); OracleDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows) { if (query == teamDetailsQueryMap["winPercentage"]) { while (rdr.Read()) { winNumberLabel.Text = rdr.GetInt32(1).ToString(); loseNumberLabel.Text = rdr.GetInt32(2).ToString(); winPercentageLabel.Text = rdr.GetFloat(3).ToString(); } } if (query == teamDetailsQueryMap["winPercentageChasingMatch"]) { while (rdr.Read()) { successfulChasesLabel.Text = rdr.GetInt32(1).ToString(); failedChasesLabel.Text = rdr.GetInt32(2).ToString(); winPercentageAfterTossWin.Text = rdr.GetFloat(3).ToString(); } } if (query == teamDetailsQueryMap["totalNumberOfAllOuts"]) { while (rdr.Read()) { totalNumberOfAllOutsLabel.Text = rdr.GetInt32(1).ToString(); } } if (query == teamDetailsQueryMap["homeGroundQuery"]) { while (rdr.Read()) { homeWinsLabel.Text = rdr.GetInt32(1).ToString(); homeLoseLabel.Text = rdr.GetInt32(2).ToString(); homeWinPercentageLabels.Text = rdr.GetFloat(3).ToString(); } } if (query == teamDetailsQueryMap["maxBoundariesByATeam"]) { while (rdr.Read()) { maxBoundariesInningsLabel.Text = rdr.GetInt32(4).ToString(); } } if (query == teamDetailsQueryMap["highestRunsScored"]) { while (rdr.Read()) { highestRunsScoredLabel.Text = rdr.GetInt32(4).ToString(); SeasonLabel.Text = rdr.GetString(0); } } } } } catch (OracleException ex) { Response.Write("<br>/" + "<br>/" + "<br>/" + "<br>/" + "<br>/" + ex); } finally { con.Close(); } }
private void visitorFunction_FormClosing(object sender, FormClosingEventArgs e) { con.Close(); }
private void button1_Click(object sender, EventArgs e) { conn.Close(); this.Close(); }
public static string getImportEDA() { OracleConnection oraConn; OracleCommand oraComm; OracleDataReader reader; string html; string connectionString = ConfigurationManager.ConnectionStrings["live"].ConnectionString; oraConn = new OracleConnection(connectionString); oraComm = new OracleCommand( "select " + "trunc(date_creation) created_date " + ",count(case when doc_type='MIU' then 1 end) Lymington " + ",count(case when doc_type='EDA' then 1 end) UHS " + "from EPR.PATIENT_IMPORTED_EDOCUMENT " + "where DATE_CREATION > sysdate - 7 " + "and EXTERNAL_SYSTEM_ID='SYMP' " + "and PATIENT_IMPORTED_EDOCUMENT.VERSION=1 " + "group by trunc(date_creation) " + "order by 1 desc " , oraConn); try { oraConn.Open(); reader = oraComm.ExecuteReader(); if (reader.HasRows) { html = "<table class=GridViewStyle>"; html += "<thead>"; html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<th class=databasecathead>" + Regex.Replace(reader.GetName(i), "_", " ") + "</th>"; } html += "</tr>"; html += "</thead>"; html += "<tbody>"; while (reader.Read()) { html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<td class=databasecat>" + Regex.Replace(reader.GetValue(i).ToString(), "00.00.00", "") + "</td>"; } html += "</tr>"; } html += "</tbody>"; html += "</table>"; return(html); } else { string html2 = "No data found"; return(html2); } reader.Close(); } finally { oraConn.Close(); } }
public static string getImportdocs() { OracleConnection oraConn; OracleCommand oraComm; OracleDataReader reader; string html; string connectionString = ConfigurationManager.ConnectionStrings["live"].ConnectionString; oraConn = new OracleConnection(connectionString); oraComm = new OracleCommand( "select " + "TRUNC (RECIEVED_DATE) Received_date " + ",count(case when external_system_id='SUME' then 1 end) Somerset " + ",count(case when external_system_id='MEDI' then 1 end) Medisoft " + "from EPR.PATIENT_IMPORTED_EDOCUMENT " + "WHERE RECIEVED_DATE > TRUNC(SYSDATE-7) " + "group by TRUNC (RECIEVED_DATE) " + "order by 1 desc " , oraConn); try { oraConn.Open(); reader = oraComm.ExecuteReader(); if (reader.HasRows) { html = "<table class=GridViewStyle>"; html += "<thead>"; html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<th class=databasecathead >" + Regex.Replace(reader.GetName(i), "_", " ") + "</th>"; } html += "</tr>"; html += "</thead>"; html += "<tbody>"; while (reader.Read()) { html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<td class=databasecat>" + Regex.Replace(reader.GetValue(i).ToString(), "00.00.00", "") + "</td>"; } html += "</tr>"; } html += "</tbody>"; html += "</table>"; return(html); } else { string html2 = "No data found"; return(html2); } reader.Close(); } finally { oraConn.Close(); } }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; Process process = new Process(); process.StartInfo.FileName = "/bin/bash"; process.StartInfo.Arguments = "-c 'cat /tmp/tainted.txt'"; process.StartInfo.UseShellExecute = false; process.StartInfo.RedirectStandardOutput = true; process.Start(); using (StreamReader reader = process.StandardOutput) { tainted_2 = reader.ReadToEnd(); process.WaitForExit(); process.Close(); } tainted_3 = tainted_2; while ((4 + 2 <= 42)) { string pattern = @"/^[0-9]*$/"; Regex r = new Regex(pattern); Match m = r.Match(tainted_2); if (!m.Success) { tainted_3 = ""; } else { tainted_3 = tainted_2; } break; } string query = "SELECT * FROM Articles WHERE id=" + tainted_3; string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password"; OracleConnection dbConnection = null; try{ dbConnection = new OracleConnection(connectionString); dbConnection.Open(); OracleCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = query; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.ToString()); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
public static string getDWLmismatch() { OracleConnection oraConn; OracleCommand oraComm; OracleDataReader reader; string html; string connectionString = ConfigurationManager.ConnectionStrings["live"].ConnectionString; oraConn = new OracleConnection(connectionString); oraComm = new OracleCommand( "select " + "adt.patient_no " + ",adt.ADMISSION_NO " + ",adt.WARD_CODE " + ",pt.WARD " + "from EPR.PATIENT_ADT adt " + "inner join ( " + "select " + "adt.admission_no HICSS_ADMISSION " + "/*This will return the last row in a group as ADT multi row*/ " + ",max( abs(adt.adt_sequence)) max_seq " + "from epr.patient_adt adt " + "where adt.cancelled_flag='N' " + "and not exists (select pt.patient_no from epr.patient_test_patient pt where " + "adt.patient_no=pt.patient_no) " + "and adt.admission_no not like '1%' " + "group by adt.admission_no " + ") curr " + "on curr.hicss_admission=adt.admission_no " + "and curr.max_seq=abs(adt.adt_sequence) " + "inner join patient_transfer pt " + "on pt.admission_no=adt.admission_no and pt.CURRENT_LOCATION_FLAG='Y'and pt.MANAGED_CONSULT_FLAG='N' " + "inner join EPR.PATIENT_TRANSFER_WORKLIST ptw " + "on ptw.TRANSFER_UID=pt.UNIQUE_ID and ptw.WORKLIST_MODE='DOCTORS' and ptw.REMOVED_FROM_LIST_FLAG='N' " + "group by " + "adt.patient_no " + ",adt.ADMISSION_NO " + ",adt.WARD_CODE " + ",pt.WARD " + "having count(case when adt.ward_code != pt.ward then 1 end) > 0 order by 2" , oraConn); try { oraConn.Open(); reader = oraComm.ExecuteReader(); if (reader.HasRows) { html = "<table class=GridViewStyle>"; html += "<thead>"; html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<th class=databasecathead>" + Regex.Replace(reader.GetName(i), "_", " ") + "</th>"; } html += "</tr>"; html += "</thead>"; html += "<tbody>"; while (reader.Read()) { html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<td class=databasecat>" + Regex.Replace(reader.GetValue(i).ToString(), "00.00.00", "") + "</td>"; } html += "</tr>"; } html += "</tbody>"; html += "</table>"; return(html); } else { string html2 = "No data found"; return(html2); } reader.Close(); } finally { oraConn.Close(); } }
/// <summary> /// 保存修改单或升版图纸的修改原因 /// </summary> private void SaveModifyInfoReason(int modifyid) { string sqlstr = "delete from MF_MODIFYINFOREASON_TAB where MODI_ID = '" + modifyid + "'"; ExcuteSql(sqlstr, DataAccess.OIDSConnStr); int count = 0; foreach (Control control in this.tableLayoutPanel1.Controls) { if (control.Name == "ModifyDrawingControl") { count += 1; } } int rowcount = count + 2; for (int i = 2; i < rowcount; i++) { Control cntrl = tableLayoutPanel1.GetControlFromPosition(0, i); object reason = ((ComboBox)cntrl.Controls["groupBox1"].Controls["reason_cb"]).SelectedValue; string commentcode = ((TextBox)cntrl.Controls["groupBox1"].Controls["comcode_tb"]).Text.ToString(); object responser = ((ComboBox)cntrl.Controls["groupBox1"].Controls["responsercomb"]).SelectedValue; object status = ((ComboBox)cntrl.Controls["groupBox1"].Controls["status_cb"]).SelectedValue; object type = ((ComboBox)cntrl.Controls["groupBox1"].Controls["typecob"]).SelectedValue; string materialcost = ((TextBox)cntrl.Controls["groupBox1"].Controls["materialcost_tb"]).Text.ToString(); decimal techhrcost = Convert.ToDecimal(((TextBox)cntrl.Controls["groupBox1"].Controls["techcost_tb"]).Text.ToString()); OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); OracleCommand cmd = conn.CreateCommand(); OracleTransaction trans = conn.BeginTransaction(); if (status.ToString() == "B") { cmd.CommandText = @"insert into MF_MODIFYINFOREASON_TAB (PROJECT_ID, DRAWING_ID, REASONTYPE_ID, DIS_SUPER, BA_FLAG, TECHHOUR_COST, MODI_ID, CREATER, COMMENT_NO) values (:projecid, :drawid, :reasontypeid, :supervisor, :BAflag, :techcost, :moid, :creater, :comcode)"; cmd.Parameters.Add("projecid", OracleType.Number).Value = User.projectid; cmd.Parameters.Add("drawid", OracleType.Number).Value = drawingid; cmd.Parameters.Add("reasontypeid", OracleType.Number).Value = Convert.ToInt16(reason); cmd.Parameters.Add("comcode", OracleType.NVarChar).Value = commentcode; cmd.Parameters.Add("supervisor", OracleType.NVarChar).Value = responser.ToString(); cmd.Parameters.Add("BAflag", OracleType.NVarChar).Value = status.ToString(); cmd.Parameters.Add("techcost", OracleType.Number).Value = techhrcost; cmd.Parameters.Add("moid", OracleType.VarChar).Value = modifyid; cmd.Parameters.Add("creater", OracleType.NVarChar).Value = User.cur_user; } else { cmd.CommandText = @"insert into MF_MODIFYINFOREASON_TAB (PROJECT_ID, DRAWING_ID, REASONTYPE_ID, DIS_SUPER, BA_FLAG, MATERAL_TYPE_ID, MATERAL_COST, TECHHOUR_COST, MODI_ID, CREATER,COMMENT_NO) values (:projecid, :drawid, :reasontypeid, :supervisor, :BAflag, :mattypeid, :matcost, :techcost, :moid, :creater, :comcode)"; cmd.Parameters.Add("projecid", OracleType.Number).Value = User.projectid; cmd.Parameters.Add("drawid", OracleType.Number).Value = drawingid; cmd.Parameters.Add("reasontypeid", OracleType.Number).Value = Convert.ToInt16(reason); cmd.Parameters.Add("comcode", OracleType.NVarChar).Value = commentcode; cmd.Parameters.Add("supervisor", OracleType.NVarChar).Value = responser.ToString(); cmd.Parameters.Add("BAflag", OracleType.NVarChar).Value = status.ToString(); cmd.Parameters.Add("mattypeid", OracleType.Number).Value = Convert.ToInt16(type); cmd.Parameters.Add("matcost", OracleType.Number).Value = Convert.ToDecimal(materialcost); cmd.Parameters.Add("techcost", OracleType.Number).Value = techhrcost; cmd.Parameters.Add("moid", OracleType.VarChar).Value = modifyid; cmd.Parameters.Add("creater", OracleType.NVarChar).Value = User.cur_user; } cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } } }
public bool checkStatus(OracleConnection ConnectionTemp, string type, string implementer, string urNo) { bool isInProcess = false; OracleCommand cmd = null; try { string query = "SELECT * FROM TRUE9_BPT_RESERVE_ID WHERE TYPE_NAME = '" + type + "' AND COMPLETE_FLAG = 'N'"; cmd = new OracleCommand(query, ConnectionTemp); OracleDataReader reader = cmd.ExecuteReader(); reader.Read(); if (reader.HasRows) { string user = reader["USERNAME"].ToString(); string typeName = reader["TYPE_NAME"].ToString(); if (user == implementer && type == typeName) { string qryDel = "DELETE FROM TRUE9_BPT_RESERVE_ID WHERE TYPE_NAME = '" + type + "' AND COMPLETE_FLAG = 'N'"; OracleCommand command = new OracleCommand(qryDel, ConnectionTemp); command.ExecuteNonQuery(); isInProcess = false; cmd = ConnectionTemp.CreateCommand(); using (OracleTransaction transaction = ConnectionTemp.BeginTransaction(IsolationLevel.ReadCommitted)) { cmd.Transaction = transaction; try { cmd.CommandText = "INSERT INTO TRUE9_BPT_RESERVE_ID VALUES('" + type + "', 'N', '0', '0', '" + urNo + "', '" + implementer + "', sysdate)"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw new Exception(ex.Message); } } } else { isInProcess = true; MessageBox.Show("UserName : "******" is in the process of inserting." + "\r\n" + "Please try again later"); } } else { isInProcess = false; cmd = ConnectionTemp.CreateCommand(); using (OracleTransaction transaction = ConnectionTemp.BeginTransaction(IsolationLevel.ReadCommitted)) { cmd.Transaction = transaction; try { cmd.CommandText = "INSERT INTO TRUE9_BPT_RESERVE_ID VALUES('" + type + "', 'N', '0', '0', '" + urNo + "', '" + implementer + "', sysdate)"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); throw new Exception(ex.Message); } } } } catch (Exception ex) { MessageBox.Show("Cannot reserve UR into table[TRUE9_BPT_RESERVE_ID] " + "\r\n" + "Error Detail : " + ex.Message); ConnectionTemp.Close(); Environment.Exit(0); } return(isInProcess); }
private void GetReportData() { try { DataTable FreeTable = new DataTable(); FreeTable.Columns.Add("ITEM_ID", typeof(string)); FreeTable.Columns.Add("FREE_CTN", typeof(double)); FreeTable.Columns.Add("FREE_PCS", typeof(double)); string dm = Request.QueryString["dm"]; string sr = Request.QueryString["sr"]; string orderdate = Request.QueryString["orderdate"]; string connectionString = ConfigurationManager.ConnectionStrings["OracleDBMain"].ConnectionString; OracleConnection conn = new OracleConnection(connectionString); // C# conn.Open(); string damageQuery = @"SELECT T3.FACTOR,T3.TP,T3.DP,T2.OUTLET_NAME,T2.OUTLET_ADDRESS,T2.PROPRITOR_NAME,T2.MOBILE_NUMBER,T5.SR_NAME,T4.COMPANY_FULL_NAME COMPANY_NAME, T4.COMPANY_ID,T4.GST_NUMBER,T4.ADDRESS COMAPNY_ADDRESS,T1.* FROM T_DAMAGE T1 INNER JOIN T_OUTLET T2 ON T1.OUTLET_ID=T2.OUTLET_ID INNER JOIN T_ITEM T3 ON T1.ITEM_CODE=T3.ITEM_ID INNER JOIN T_COMPANY T4 ON T3.OWN_COMPANY=T4.COMPANY_ID INNER JOIN T_SR_INFO T5 ON T1.SR_ID=T5.SR_ID WHERE T1.SR_ID='" + sr.Trim() + "' AND T1.ENTRY_DATE=TO_DATE('" + orderdate.Trim() + "','DD/MM/YYYY')"; OracleCommand damageCommand = new OracleCommand(damageQuery, conn); OracleDataAdapter damageadapter = new OracleDataAdapter(damageCommand); DataSet dsDamage = new DataSet(); damageadapter.Fill(dsDamage, "DataTable1"); int damageCount = dsDamage.Tables[0].Rows.Count; if (damageCount > 0 && dsDamage.Tables[0].Rows[0]["SR_ID"].ToString() != "") { DataColumn dc = new DataColumn("DM_ID"); dc.DataType = typeof(string); dsDamage.Tables[0].Columns.Add(dc); DataColumn dc1 = new DataColumn("DM_NAME"); dc1.DataType = typeof(string); dsDamage.Tables[0].Columns.Add(dc1); DataColumn dc2 = new DataColumn("DM_MOBILE"); dc2.DataType = typeof(string); dsDamage.Tables[0].Columns.Add(dc2); DataColumn dc3 = new DataColumn("TRAN_ID"); dc3.DataType = typeof(string); dsDamage.Tables[0].Columns.Add(dc3); DataColumn dc4 = new DataColumn("DAMAGE"); dc4.DataType = typeof(string); dc4.DefaultValue = 0; dsDamage.Tables[0].Columns.Add(dc4); DataColumn dc6 = new DataColumn("ORDER_DATE"); dc6.DataType = typeof(string); dsDamage.Tables[0].Columns.Add(dc6); string DM_ID = dm; string DM_NAME = ""; string DM_MOBILE = ""; string qrDm = @"SELECT * FROM T_DM WHERE DM_ID='" + dm.Trim() + "'"; OracleCommand cmDm = new OracleCommand(qrDm, conn); OracleDataAdapter daDm = new OracleDataAdapter(cmDm); DataSet dsDm = new DataSet(); daDm.Fill(dsDm); int count = dsDm.Tables[0].Rows.Count; if (count > 0 && dsDm.Tables[0].Rows[0]["DM_ID"].ToString() != "") { DM_NAME = dsDm.Tables[0].Rows[0]["DM_NAME"].ToString(); DM_MOBILE = dsDm.Tables[0].Rows[0]["MOBILE_NO"].ToString(); } for (int d = 0; d < damageCount; d++) { double damageAmount = 0; string srID = Convert.ToString(dsDamage.Tables[0].Rows[d]["SR_ID"]); string damageItemId = Convert.ToString(dsDamage.Tables[0].Rows[d]["ITEM_CODE"]); string damageItemName = Convert.ToString(dsDamage.Tables[0].Rows[d]["ITEM_NAME"]); string damageCtn = Convert.ToString(dsDamage.Tables[0].Rows[d]["CARTON"]); string damagePcs = Convert.ToString(dsDamage.Tables[0].Rows[d]["PCS"]); string OUTLET_ID = Convert.ToString(dsDamage.Tables[0].Rows[d]["OUTLET_ID"]); string UTLET_NAME = Convert.ToString(dsDamage.Tables[0].Rows[d]["OUTLET_NAME"]); string UTLET_ADDRESS = Convert.ToString(dsDamage.Tables[0].Rows[d]["OUTLET_ADDRESS"]); double fACTOR = Convert.ToDouble(dsDamage.Tables[0].Rows[d]["FACTOR"]); double tp = Convert.ToDouble(dsDamage.Tables[0].Rows[d]["tp"]); damageAmount = (Convert.ToDouble(damageCtn) * fACTOR + Convert.ToDouble(damagePcs)) * tp; DataRow drD = dsDamage.Tables[0].Rows[d]; drD[21] = DM_ID; DataRow drDD = dsDamage.Tables[0].Rows[d]; drDD[22] = DM_NAME; DataRow drFD = dsDamage.Tables[0].Rows[d]; drFD[23] = DM_MOBILE; string TRAN_ID = ""; string qrPO = @"SELECT TRAN_ID FROM T_ORDER_HEADER WHERE OUTLET_ID='" + OUTLET_ID.Trim() + "' AND SR_ID='" + srID.Trim() + "' AND ENTRY_DATE=TO_DATE('" + orderdate.Trim() + "','DD/MM/YYYY')"; OracleCommand cmdT = new OracleCommand(qrPO, conn); OracleDataAdapter daT = new OracleDataAdapter(cmdT); DataSet dsT = new DataSet(); daT.Fill(dsT); int t = dsT.Tables[0].Rows.Count; if (t > 0 && dsT.Tables[0].Rows[0]["TRAN_ID"].ToString() != "") { TRAN_ID = dsT.Tables[0].Rows[0]["TRAN_ID"].ToString(); DataRow dr = dsDamage.Tables[0].Rows[d]; dr[24] = TRAN_ID; } DataRow drDamage = dsDamage.Tables[0].Rows[d]; drDamage[25] = damageAmount; } } conn.Close(); int rowcount = dsDamage.Tables[0].Rows.Count; if (rowcount > 0 && dsDamage.Tables[0].Rows[0]["ITEM_CODE"].ToString() != "") { for (int i = 0; i < rowcount; i++) { dsDamage.Tables[0].Rows[i]["ORDER_DATE"] = orderdate; } } var crReport = new ReportDocument(); crReport.Load(Server.MapPath("RptOutletwiseDamageMemo.rpt")); Session["ReportDocument"] = crReport; crReport.SetDataSource(dsDamage); // Binding the crystalReportViewer with our report object. CrystalReportViewer1.ReportSource = crReport; CrystalReportViewer1.ToolPanelView = ToolPanelViewType.None; } catch (Exception ex) { Console.WriteLine(ex.ToString()); } }
/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList) { using (OracleConnection conn = new OracleConnection(conStr)) { conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; OracleTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { foreach (CommandInfo c in cmdList) { if (!String.IsNullOrEmpty(c.CommandText)) { PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters); if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine) { if (c.CommandText.ToLower().IndexOf("count(") == -1) { tx.Rollback(); throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式"); } object obj = cmd.ExecuteScalar(); bool isHave = false; if (obj == null && obj == DBNull.Value) { isHave = false; } isHave = Convert.ToInt32(obj) > 0; if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave) { tx.Rollback(); throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0"); } if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave) { tx.Rollback(); throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0"); } continue; } int res = cmd.ExecuteNonQuery(); if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0) { tx.Rollback(); throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行"); } } } tx.Commit(); return(true); } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); throw E; } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } } }
static void Main(string[] args) { //OPEN CONNECTION string oradb = "User Id=system; Password=123; Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))"; OracleConnection conn = new OracleConnection(oradb); conn.Open(); /* * //------------------------------------------------------- * //CREATE TABLE STATEMENT * OracleCommand commCreate = new OracleCommand(); * commCreate.Connection = conn; * commCreate.CommandType = CommandType.Text; * * commCreate.CommandText = " CREATE TABLE TestTable " + * " ( " + * " ID NUMBER not null primary key, " + * " Name varchar2(50) not null, " + * " Category varchar2(50) " + * " ) "; * * commCreate.ExecuteNonQuery(); * * //------------------------------------------------------- * //INSERT STATEMENT * OracleCommand commInsert = new OracleCommand(); * commInsert.Connection = conn; * commInsert.CommandType = CommandType.Text; * * commInsert.Parameters.Add(new OracleParameter("Name", "varchar2")).Value = "NAME_VALUE"; * commInsert.Parameters.Add(new OracleParameter("Category", "varchar2")).Value = "CATEGORY"; * * commInsert.CommandText = "INSERT INTO TestTable (ID, Name, Category) VALUES (1,:Name, :Category)"; * commInsert.ExecuteNonQuery(); * * //------------------------------------------------------ * //UPDATE STATEMENT * OracleCommand commUpdate = new OracleCommand(); * commUpdate.Connection = conn; * commUpdate.CommandType = CommandType.Text; * commUpdate.Parameters.Add(new OracleParameter("NewName", "varchar2")).Value = "NEW_VALUE"; * commUpdate.Parameters.Add(new OracleParameter("Name", "varchar2")).Value = "OLD_VALUE"; * * commUpdate.CommandText = "UPDATE TestTable SET TestTable.Name = :NewName WHERE TestTable.Name = :Name"; * commUpdate.ExecuteNonQuery(); * * //------------------------------------------------------ * //SELECT STATEMENT * OracleCommand commSelect = new OracleCommand(); * commSelect.Connection = conn; * commSelect.CommandType = CommandType.Text; * commSelect.Parameters.Add(new OracleParameter("Category", "varchar2")).Value = "SEARCH_VALUE"; * commSelect.CommandText = "SELECT Name FROM TestTable WHERE Category = :Category"; * * OracleDataReader reader = commSelect.ExecuteReader(); * while (reader.Read()) * { * Console.WriteLine(reader["Name"]); * } * reader.Close(); * * //----------------------------------------------------- * //DELETE STATEMENT * OracleCommand commDelete = new OracleCommand(); * commDelete.Connection = conn; * commDelete.CommandType = CommandType.Text; * commDelete.Parameters.Add(new OracleParameter("Category", "varchar2")).Value = "DELETE_VALUE"; * * commDelete.CommandText = "DELETE FROM TestTable WHERE Category = :Category"; * commDelete.ExecuteNonQuery(); * * //----------------------------------------------------- * //EXTERMINATUS STATEMENT * OracleCommand commDrop = new OracleCommand(); * commDrop.Connection = conn; * commDrop.CommandType = CommandType.Text; * * commDrop.CommandText = "DROP TABLE TestTable"; * commDrop.ExecuteNonQuery(); * */ //CLOSE CONNECTION conn.Close(); Console.WriteLine("The End"); Console.Read(); }
protected void Page_Init(object sender, EventArgs e) { DataSetAssetClass imageDataSet = new DataSetAssetClass(); string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; OracleConnection con = new OracleConnection(connStr); string StartDate = Request.QueryString["StartDate"].ToString(); string EndDate = Request.QueryString["EndDate"].ToString(); DateTime StartDateNew = DateTime.ParseExact(StartDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string StartDateQuery = StartDateNew.ToString("yyyy/mm/dd"); DateTime EndDateNew = DateTime.ParseExact(EndDate, "dd/mm/yyyy", CultureInfo.InvariantCulture); string EndDateQuery = EndDateNew.ToString("yyyy/mm/dd"); string StartDateTemp = Request.QueryString["StartDate"].ToString(); string[] StartDateTempSplit = StartDateTemp.Split('-'); String StartDateFormTemp = StartDateTempSplit[0].Replace("/", "-"); DateTime StartDateFormTempNew = DateTime.ParseExact(StartDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string StartDateForm = StartDateFormTempNew.ToString("dd-MMMM-yyyy"); string EndDateTemp = Request.QueryString["EndDate"].ToString(); string[] EndDateTempSplit = EndDateTemp.Split('-'); String EndDateFormTemp = EndDateTempSplit[0].Replace("/", "-"); DateTime EndDateFormTempNew = DateTime.ParseExact(EndDateFormTemp, "dd-MM-yyyy", CultureInfo.InvariantCulture); string EndDateForm = EndDateFormTempNew.ToString("dd-MMMM-yyyy"); string CollectionForID = Request.QueryString["CollectionForID"].ToString(); string DropDownItem = Request.QueryString["DropDownItemID"].ToString(); string DropDownItemID = DropDownItem.Remove(DropDownItem.Length - 1, 1); string[] ItemID = DropDownItemID.Split('-'); string sqlString = ""; if (ItemID[0] == "0") { sqlString = " SELECT PP.PARTY_ID AS SUPPLIER_ID, PP.PARTY_NAME AS SUPPLIER_NAME, WCF.COLLECTION_FOR_NAME AS SALESMAN_NAME, PI.ITEM_NAME, PI.ITEM_CODE, TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') AS ENTRY_DATE, WPM.SLIP_NO, WPM.ITEM_WEIGHT, WPM.ITEM_RATE, WPM.ITEM_AMOUNT, WPM.VAT_PERCENT, WPM.VAT_AMOUNT, WPM.TOTAL_AMOUNT FROM WP_PARTY PP LEFT JOIN WP_PURCHASE_MASTER WPM ON WPM.PARTY_ID = PP.PARTY_ID LEFT JOIN WP_COLLECTION_FOR WCF ON WCF.COLLECTION_FOR_ID = WPM.COLLECTION_FOR_ID LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = WPM.ITEM_ID WHERE WPM.COLLECTION_FOR_ID = '" + CollectionForID + "' AND TO_CHAR(WPM.ENTRY_DATE, 'yyyy/mm/dd') between '" + StartDateQuery + "' AND '" + EndDateQuery + "' ORDER BY TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') ASC, WPM.SLIP_NO ASC "; } else { sqlString = " SELECT PP.PARTY_ID AS SUPPLIER_ID, PP.PARTY_NAME AS SUPPLIER_NAME, WCF.COLLECTION_FOR_NAME AS SALESMAN_NAME, PI.ITEM_NAME, PI.ITEM_CODE, TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') AS ENTRY_DATE, WPM.SLIP_NO, WPM.ITEM_WEIGHT, WPM.ITEM_RATE, WPM.ITEM_AMOUNT, WPM.VAT_PERCENT, WPM.VAT_AMOUNT, WPM.TOTAL_AMOUNT FROM WP_PARTY PP LEFT JOIN WP_PURCHASE_MASTER WPM ON WPM.PARTY_ID = PP.PARTY_ID LEFT JOIN WP_COLLECTION_FOR WCF ON WCF.COLLECTION_FOR_ID = WPM.COLLECTION_FOR_ID LEFT JOIN WP_ITEM PI ON PI.ITEM_ID = WPM.ITEM_ID WHERE WPM.COLLECTION_FOR_ID = '" + CollectionForID + "' AND TO_CHAR(WPM.ENTRY_DATE, 'yyyy/mm/dd') between '" + StartDateQuery + "' AND '" + EndDateQuery + "' AND ( "; for (int i = 0; i < ItemID.Length; i++) { sqlString += " WPM.ITEM_ID = '" + ItemID[i] + "' OR"; } sqlString = sqlString.Remove(sqlString.Length - 2, 2) + " ) ORDER BY TO_CHAR(TO_DATE(WPM.ENTRY_DATE),'dd/mm/yyyy') ASC, WPM.SLIP_NO ASC "; } // pnlReport.GroupingText = sqlString; OracleCommand cmd = new OracleCommand(sqlString, con); cmd.CommandType = CommandType.Text; cmd.CommandText = sqlString; OracleDataAdapter dt = new OracleDataAdapter(cmd); con.Open(); dt.Fill(imageDataSet.Tables["reportTable"]); con.Close(); crystalReport.Load(Server.MapPath("~/WP/WP_Reports/Wp_Purchase_Collection_For_Wise_Report.rpt")); crystalReport.SetDataSource(imageDataSet.Tables["reportTable"]); crystalReport.SetParameterValue("StartDate", StartDateForm); crystalReport.SetParameterValue("EndDate", EndDateForm); string datetime = DateTime.Now.ToString("dd-MM-yyyy"); CrystalReportViewer1.ID = "Wp_Purchase_Collection_For_Wise_Report_(As_On_Date)_Waste_Paper_" + datetime + ""; CrystalReportViewer1.ReportSource = crystalReport; }
public int reserveID(OracleConnection ConnectionTemp, OracleConnection ConnectionProd, string type, string implementer, string urNo) { OracleCommand cmd = null; int minID = 0; int max = 0; string prefixID = ""; string col = ""; string table = ""; if (type == "Hispeed") { prefixID = "20"; col = "P_ID"; table = "HISPEED_PROMOTION"; } else if (type == "Disc") { prefixID = "DC"; col = "DC_ID"; table = "DISCOUNT_CRITERIA_MAPPING"; } else { prefixID = "VAS"; col = "DC_ID"; table = "DISCOUNT_CRITERIA_MAPPING"; } string queryMax = "SELECT MAX(" + col + ") FROM " + table + " WHERE " + col + " LIKE '" + prefixID + "%'"; string queryMax_reserve = "SELECT MAX(MAX_ID) FROM TRUE9_BPT_RESERVE_ID WHERE TYPE_NAME = '" + type + "'"; cmd = new OracleCommand(queryMax, ConnectionProd); OracleDataReader readerMax = cmd.ExecuteReader(); readerMax.Read(); OracleCommand cmd1 = new OracleCommand(queryMax_reserve, ConnectionTemp); OracleDataReader dataReader = cmd.ExecuteReader(); dataReader.Read(); if (type == "Hispeed") { minID = Convert.ToInt32(readerMax[0]) + 1; max = Convert.ToInt32(dataReader[0]); } else { string minid = Convert.ToString(readerMax[0]).Substring(prefixID.Length); string maxid = Convert.ToString(dataReader[0]).Substring(prefixID.Length); minID = Convert.ToInt32(minid) + 1; max = Convert.ToInt32(maxid); } if (minID <= max) { MessageBox.Show("There is a conflict ID between production and reserve table[TRUE9_BPT_RESERVE_ID]" + "\r\n" + "Please review and confirm the information"); string qryDel = "DELETE FROM TRUE9_BPT_RESERVE_ID WHERE TYPE_NAME = '" + type + "' AND COMPLETE_FLAG = 'N'"; OracleCommand command = new OracleCommand(qryDel, ConnectionTemp); command.ExecuteNonQuery(); ConnectionProd.Close(); ConnectionTemp.Close(); Environment.Exit(0); } return(minID); }
public static void Main(string[] args) { string tainted_2 = null; string tainted_3 = null; tainted_2 = args[1]; tainted_3 = tainted_2; if ((Math.Pow(4, 2) >= 42)) { {} } else if (!(Math.Pow(4, 2) >= 42)) { StringBuilder escape = new StringBuilder(); for (int i = 0; i < tainted_2.Length; ++i) { char current = tainted_2[i]; switch (current) { case '\\': escape.Append(@"\5c"); break; case '*': escape.Append(@"\2a"); break; case '(': escape.Append(@"\28"); break; case ')': escape.Append(@"\29"); break; case '\u0000': escape.Append(@"\00"); break; case '/': escape.Append(@"\2f"); break; default: escape.Append(current); break; } } tainted_3 = escape.ToString(); } //flaw string query = "SELECT * FROM '" + tainted_3 + "'"; string connectionString = "Data Source=localhost;User ID=oracle_user;Password=oracle_password"; OracleConnection dbConnection = null; try{ dbConnection = new OracleConnection(connectionString); dbConnection.Open(); OracleCommand cmd = dbConnection.CreateCommand(); cmd.CommandText = query; OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.ToString()); } dbConnection.Close(); }catch (Exception e) { Console.WriteLine(e.ToString()); } }
public static string getAdmitcounts() { OracleConnection oraConn; OracleCommand oraComm; OracleDataReader reader; string html; string connectionString = ConfigurationManager.ConnectionStrings["live"].ConnectionString; oraConn = new OracleConnection(connectionString); oraComm = new OracleCommand( "select " + "wd.DIRECTORATE_CODE " + ",COUNT(wd.directorate_code) admitted " + "from EPR.PATIENT_ADT adt " + "inner join ( " + "select " + "adt.admission_no HICSS_ADMISSION " + ",max( abs(adt.adt_sequence)) max_seq " + "from epr.patient_adt adt " + "where adt.cancelled_flag='N' " + "and not exists (select pt.patient_no from epr.patient_test_patient pt where " + "adt.patient_no=pt.patient_no) " + "group by adt.admission_no " + ") curr " + "on curr.hicss_admission=adt.admission_no " + "and curr.max_seq=abs(adt.adt_sequence) " + "inner join epr.ward wd " + "on wd.CODE=adt.WARD_CODE " + "and adt.PAS_DISCHARGE_DATE is null " + "and adt.adt_sequence != 0 " + "and adt.IS_CURRENT_FLAG='Y' " + "and adt.LEGACY_ADMISSION_METHOD is null " + "and wd.ORGANISATION_CODE='RHM' " + "and wd.DIRECTORATE_CODE is not null " + "group by rollup (wd.DIRECTORATE_CODE) " + "order by 1 " , oraConn); try { oraConn.Open(); reader = oraComm.ExecuteReader(); if (reader.HasRows) { html = "<table class=GridViewStyle>"; html += "<thead>"; html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<th class=databasecathead>" + Regex.Replace(reader.GetName(i), "_", " ") + "</th>"; } html += "</tr>"; html += "</thead>"; html += "<tbody>"; while (reader.Read()) { html += "<tr>"; for (int i = 0; i < reader.FieldCount; i++) { html += "<td class=databasecat>" + Regex.Replace(reader.GetValue(i).ToString(), "00.00.00", "") + "</td>"; } html += "</tr>"; } html += "</tbody>"; html += "</table>"; return(html); } else { string html2 = "No data found"; return(html2); } reader.Close(); } finally { oraConn.Close(); } }
public SponsorTransferRequest GetSponsorTransferRequestByRequestNumber(int laborOfficeId, int year, long sequenceNumber) { var connection = new OracleConnection(_connectionString); OracleCommand command = new OracleCommand { CommandType = CommandType.Text, CommandText = $@"SELECT application.lab_off , application.ser_yy , application.ser_no , application_laborer.id_no , application.lab_off_cmpy , application.cmpy_no , new_establishment.CMPYNAME , application.olab_off_cmpy , application.ocmpy_no , application.OCMPYNAME , application.trs_stus , application.MOL_INS_TIMESTAMP FROM srv_trf_appl application , srv_trf_labors application_laborer , wfr11 laborer , wfr2 new_establishment WHERE application_laborer.lab_off = application.lab_off AND application_laborer.ser_yy = application.ser_yy AND application_laborer.ser_no = application.ser_no AND application_laborer.id_no = laborer.id_no AND application.lab_off_cmpy = new_establishment.lab_off_cmpy AND application.cmpy_no = new_establishment.cmpy_no AND application.lab_off= :labor_office_id AND application.ser_yy= :year AND application.ser_no= :ser_no AND rownum = 1", Connection = connection }; command.Parameters.Add("labor_office_id", laborOfficeId); command.Parameters.Add("year", year); command.Parameters.Add("ser_no", sequenceNumber); IDataReader reader = null; var dtRequests = new DataTable(); try { connection.Open(); reader = command.ExecuteReader(); dtRequests.Load(reader); } catch (Exception ex) { throw; } finally { reader?.Close(); connection?.Close(); } if (dtRequests.Rows == null && dtRequests.Rows.Count == 0) { return(null); } return(FillSponsorTransferRequest(dtRequests.Rows[0])); }
public IEnumerable <SponsorTransferRequest> GetSponsorTransferRequestByIdNumber(long idNumber) { var connection = new OracleConnection(_connectionString); OracleCommand command = new OracleCommand { CommandType = CommandType.Text, CommandText = $@"SELECT application.lab_off , application.ser_yy , application.ser_no , application_laborer.id_no , application.lab_off_cmpy , application.cmpy_no , new_establishment.CMPYNAME , application.olab_off_cmpy , application.ocmpy_no , application.OCMPYNAME , application.trs_stus , application.MOL_INS_TIMESTAMP FROM srv_trf_appl application , srv_trf_labors application_laborer , wfr11 laborer , wfr2 new_establishment WHERE application_laborer.lab_off = application.lab_off AND application_laborer.ser_yy = application.ser_yy AND application_laborer.ser_no = application.ser_no AND application_laborer.id_no = laborer.id_no AND application.lab_off_cmpy = new_establishment.lab_off_cmpy AND application.cmpy_no = new_establishment.cmpy_no AND laborer.id_no= :id_number", Connection = connection }; command.Parameters.Add("id_number", idNumber); IDataReader reader = null; var dtRequests = new DataTable(); try { connection.Open(); reader = command.ExecuteReader(); dtRequests.Load(reader); } catch (Exception ex) { throw; } finally { reader?.Close(); connection?.Close(); } var requests = new List <SponsorTransferRequest>(); foreach (DataRow row in dtRequests.Rows) { requests.Add(FillSponsorTransferRequest(row)); } return(requests); }
public string InsertDtToOrcl(string orclConStr, DataTable dt, string tbName) { int i; string returnMsg = ""; int rows = dt.Rows.Count; int cols = dt.Columns.Count; StringBuilder sb = new StringBuilder(); string colName = string.Empty; string colNames = string.Empty; string colNamePramas = string.Empty; string colType = string.Empty; for (i = 0; i < cols; i++) { colName = dt.Columns[i].ColumnName.ToString(); colType = dt.Columns[i].DataType.ToString(); colType = this.NetDataTypeToOracleType(colType); if (i == 0) { colNames = colNames + colName; colNamePramas = colNamePramas + ":" + colName; } else { colNames = colNames + "," + colName; colNamePramas = colNamePramas + ",:" + colName; } } sb.Append(")"); if (colNames == string.Empty) { return("数据集的列数必须大于0"); } using (OracleConnection objConn = new OracleConnection(orclConStr)) { OracleCommand objCmd = new OracleCommand(); objCmd.Connection = objConn; sb.Remove(0, sb.Length); sb.Append(" insert into " + tbName + " (" + colNames + ") values(" + colNamePramas + " )"); objCmd.CommandText = sb.ToString(); OracleParameterCollection param = objCmd.Parameters; i = 0; while (i < cols) { colType = dt.Columns[i].DataType.ToString(); colName = dt.Columns[i].ColumnName.ToString(); if (colType == "System.String") { param.Add(new OracleParameter(":" + colName, OracleType.VarChar)); } else if (colType == "System.DateTime") { param.Add(new OracleParameter(":" + colName, OracleType.DateTime)); } else if (colType == "System.Boolean") { param.Add(new OracleParameter(":" + colName, OracleType.Byte)); } else if (colType == "System.Decimal") { param.Add(new OracleParameter(":" + colName, OracleType.Number)); } else if (colType == "System.Double") { param.Add(new OracleParameter(":" + colName, OracleType.Double)); } else if (colType == "System.Single") { param.Add(new OracleParameter(":" + colName, OracleType.Float)); } else if (colType == "System.Single") { param.Add(new OracleParameter(":" + colName, OracleType.Float)); } else { param.Add(new OracleParameter(":" + colName, OracleType.Int32)); } i++; } foreach (DataRow row in dt.Rows) { for (i = 0; i < param.Count; i++) { param[i].Value = row[i]; } try { objConn.Open(); objCmd.ExecuteNonQuery(); objConn.Close(); } catch (Exception wron) { string rec = row[1].ToString(); string str = returnMsg; returnMsg = str + "数据:[" + rec + "] " + this.GetExp(wron.Message); objConn.Close(); continue; } } } if (returnMsg == "") { returnMsg = returnMsg + "成功"; } return(returnMsg); }
public string[] GetSearchItems(string prefix) { List<string> items = new List<string>(); using (OracleConnection conn = new OracleConnection()) { conn.ConnectionString = ConfigurationManager .ConnectionStrings["Bazi"].ConnectionString; using (OracleCommand cmd = new OracleCommand()) { cmd.CommandText = "select ime,igraid from Igra where upper(ime) like upper(:SearchText)||'%'"; cmd.Parameters.Add(new OracleParameter("SearchText", prefix)); cmd.Connection = conn; conn.Open(); using (OracleDataReader sdr = cmd.ExecuteReader()) { while (sdr.Read()) { items.Add(string.Format("{0}`{1} Game", sdr.GetString(0), sdr.GetInt64(1))); } } cmd.CommandText = "select IMEMODEL,HARDID from HARDVERPROIZVOD where upper(IMEMODEL) like upper(:SearchText)||'%'"; using (OracleDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { items.Add(string.Format("{0}`{1} Hardware", rdr.GetString(0), rdr.GetInt64(1))); } } conn.Close(); } return items.ToArray(); } }