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
}
}
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);
}
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());
}
}
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 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)
{
}
}
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();
}
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 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;
}
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 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();
}
}
// 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;
}
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();
}
}
}
/// <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<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;
}
/// <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();
}
}
}
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;
}
}
}
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
{
}
}
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 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;
}
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();
}
}
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();
}
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();
}
}
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();
}
}
/// <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();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
conn.Close();
this.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();
}
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();
}
}
private void visitorFunction_FormClosing(object sender, FormClosingEventArgs e)
{
con.Close();
}
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();
}
}
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 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);
}
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 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);
}
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());
}
}
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;
}
/// <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();
}
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 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 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 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 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 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);
}
