/// <summary>
/// 填充combox
/// </summary>
/// <param name="cb"></param>
/// <param name="sql"></param>
private void FillComb(ComboBox cb,string sql)
{
try
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
cb.Items.Clear();
cb.Items.Add("");
while (dr.Read())
{
cb.Items.Add(dr[0].ToString());
}
conn.Close();
dr.Close();
}
}
catch (OracleException ox)
{
MessageBox.Show(ox.Message.ToString());
return;
}
}
public static string GetFeature(string p_key, int p_size, int p_page)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_SEARCH_STATION_TOTAL, p_size, p_key);
object total = comm.ExecuteScalar();
comm.CommandText = string.Format(SQL_SEARCH_STATION, p_key, p_page * p_size, (p_page - 1) * p_size);
OracleDataReader rdr = comm.ExecuteReader();
string str = Reader2JSON.ToJSON(rdr);
sb.Append("{\"total\":");
sb.Append(total);
sb.Append(",\"page\":");
sb.Append(p_page);
sb.Append(",\"content\":");
sb.Append(str);
sb.Append("}");
conn.Close();
}
return sb.ToString();
}
public static bool EditFeature(string labelid,string title,string content)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":LABELID",OracleType.VarChar),
new OracleParameter(":TITLE",OracleType.VarChar),
new OracleParameter(":CONTENT",OracleType.VarChar)
};
parms[0].Value = labelid;
parms[1].Value = title;
parms[2].Value = content;
comm.CommandText = SQL_UPDATE_LABEL;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Orders", con);
OracleDataReader rdr = cmd.ExecuteReader();
//change a connection's state without closing the datareader (should fail
try
{
BeginCase("InvalidOperationException");
try
{
((IDbConnection)con).ChangeDatabase("msdb");
ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName);
}
catch (InvalidOperationException ex)
{
ExpectedExceptionCaught(ex);
}
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
private void button1_Click(object sender, EventArgs e)
{
#region connection
try
{
con = new OracleConnection();
con.ConnectionString = "Data Source=orcl.iacsd.org;User ID=user101;Password=user101;Unicode=True";
cmd = new OracleCommand();
cmd.CommandType = CommandType.Text;
//Provide the name of stored procedu re
cmd.CommandText = "select * from Emp";
cmd.Connection = con;
con.Open();
MessageBox.Show(" Number of Rows =" + cmd.ExecuteScalar().ToString());
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
#endregion
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tblFlujo.DataSource = dt;
tblFlujo.DataBind();
ora2.Close();
}
catch (Exception ex)
{
lblError.Text = ex.ToString();
}
}
}
public void createMember(string firstname, string lastname, string address, string city, string username,
string password, string email, string picture) //insert member
{
String oradb = "Data Source= cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw1;Password=CampBand4;";
OracleConnection conn = new OracleConnection(oradb);
try
{
String strSQL = "INSERT INTO MEMBERS VALUES(Members_seq.nextval, '" + firstname + "', '" + lastname + "', '" + address + "', '" + city + "', '" + username + "', '" + password + "', '" + picture + "', '" + email + "', '" + DateTime.Now.ToString("dd-MMM-yyyy") + "' )";
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Welcome to BandCamp!");
registered = true;
}
catch (OracleException ex)
{
registered = false;
MessageBox.Show("Something went wrong when accessing the database.");
MessageBox.Show("Registration was unsuccessful");
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
}
private void createOracleConnection()
{
try
{
if (dbType == DbType.Oracle)
{
if (conOra == null)
{
conOra = new System.Data.OracleClient.OracleConnection(url);
}
if (cmdOra == null)
{
cmdOra = conOra.CreateCommand();
}
if (conOra.State != ConnectionState.Open)
{
conOra.Open();
}
}
}
catch
{
try
{
conOra.Close();
}
catch { }
throw new StaConnectException();
}
}
private DataTable ConnectAndQuery(string devicename)
{
devicename = string.IsNullOrEmpty(devicename) ? "V-0516" : devicename;
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
OracleCommand command = connection.CreateCommand();
string sql = "SELECT * FROM v_conduit_test where separator_id='" + devicename + "' and conduit_name like '%" + ddlConduit.SelectedValue + "%' and rownum<100";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
public DataSet ExecuteDataSet(System.Data.IDbConnection connection, System.Data.CommandType commandType,string CommandText,System.Data.DataSet ds,string tablename)
{
if (connection.State != System.Data.ConnectionState.Open)
connection.Open();
if (!this.needTransaction)
tran = null;
OracleCommand cmd = new OracleCommand();
PrepareCommand( cmd , connection,tran,CommandType.Text,CommandText);
OracleDataAdapter da = new OracleDataAdapter(cmd);
try
{
da.Fill(ds,tablename);
da.Dispose();
}
catch (System.Data.OracleClient.OracleException ex)
{
throw ex;
}
finally
{
if (mustCloseConnection)
{
if(!needTransaction)
{
Connection.Close();
}
}
}
return ds;
}
public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
OracleDataAdapter MyAdapter = new OracleDataAdapter();
MyAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
MyAdapter.Fill(ds);
cmd.Parameters.Clear();
DataTable table = ds.Tables[0];
ds.Dispose();
connection.Close();
return table;
}
catch
{
connection.Close();
throw;
}
}
}
public void CargarQuerys()
{
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas2");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_y_flujo");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoEncargado_2.Text;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablaTareas.DataSource = dt;
tablaTareas.DataBind();
if (tablaTareas.Rows.Count == 0)
{
Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
}
private DataTable GetConduitDeviceData(string devicename, string wellname, string startDate, string endDate)
{
if (string.IsNullOrEmpty(devicename))
{
devicename = "%";
}
if (string.IsNullOrEmpty(wellname))
{
wellname = "F315";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sql = "select count(*) total, extract(month from start_Date) AS monthname,extract(year from start_Date)as yearname from v_Conduit_test where separator_id like '" + devicename + "' and conduit_name = '" + wellname + "' and validity_Status = 'valid test' ";
/*if (!includeAbondoned)
* {
* sql += "and vc.actual_status not like '%ABAN_%' ";
* }*/
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and start_date >= '" + startDate + "' and end_date <= '" + endDate + "' ";
}
sql += " group by extract(month from start_date),extract(year from start_Date) order by 3";
//sql += "order by 2";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
private DataTable GetWellTestData(string devicename, string wellname, string startDate, string endDate)
{
if (string.IsNullOrEmpty(devicename))
{
devicename = "%";
}
if (string.IsNullOrEmpty(wellname))
{
wellname = "F315";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sql = "select vct.separator_id,NVL(vct.water,0) as notNullWater,NVL(vct.oil,0) as notNullOil,NVL(vct.bsw,0) as notNullBSW,NVL(vct.gas_out,0) as notNullGasOut,vct.* from v_conduit_test vct inner join v_conduit vc on VCT.CONDUIT_NAME=VC.CONDUIT_NAME where VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id like '" + devicename + "' and vct.conduit_name = '" + wellname + "' and vct.validity_status='valid test' ";
//if (!includeAbondoned)
//{
// sql += "and vc.actual_status not like '%ABAN_%' ";
//}
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' ";
}
sql += " order by start_date";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Customers", con);
OracleDataReader rdr = cmd.ExecuteReader();
try
{
BeginCase("column REGION ordinal");
Compare(rdr.GetOrdinal("REGION"),6 );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("column not exists");
try
{
int i = rdr.GetOrdinal("blabla");
}
catch (Exception ex) {exp=ex;}
Compare(exp.GetType().FullName,typeof(IndexOutOfRangeException).FullName);
exp=null;
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
protected DataSet cmis_search(string ma_khang, string so_cto, out string txt_err)
{
DataSet ds = new DataSet();
try
{
string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", "THAINGUYEN", "CMIS01PC1");
OracleConnection ora_con = new OracleConnection(txt_cmd);
txt_cmd = @"select TEN_KHANG, DCHI_KHANG, DCHI_TTOAN, KY, THANG, NAM, NGAY_DKY, NGAY_CKY, LOAI_HDON, TONG_TIEN, DIEN_TTHU, NGAY_PHANH
from cmis01.hdn_hdon
where MA_KHANG=:ma_khang or SO_CTO=:so_cto
order by NGAY_CKY desc";
OracleParameter[] pa = new OracleParameter[2];
pa[0] = new OracleParameter("ma_khang", ma_khang.ToUpper());
pa[1] = new OracleParameter("so_cto", so_cto);
OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
ora_da.SelectCommand.CommandType = CommandType.Text;
ora_da.SelectCommand.Parameters.Add(pa[0]);
ora_da.SelectCommand.Parameters.Add(pa[1]);
ora_con.Open();
ora_da.Fill(ds);
ora_con.Close();
txt_err = "";
}
catch (Exception ex) { txt_err = ex.Message; }
return ds;
}
public static void AddTrayORClass(string queryString, string project, string drawing, string spool,string trayorclass)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool;
cmd.Parameters.Add("trayorclass_in", OracleType.VarChar).Value = trayorclass;
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 static bool TestConnect(string user, string pwd, string serviceName, out string msg)
{
//Data Source=lan_189;User ID=user_ahdy_shi;Password=user_ahdy_shi
string conn = string.Format("Data Source={0};User ID={1};Password={2}", serviceName.Trim(), user, pwd);
try
{
using (OracleConnection con = new OracleConnection(conn))
{
OracleConnection.ClearPool(con);
System.Threading.Thread.Sleep(100);
con.Open();
con.Close();
con.Dispose();
}
msg = "连接" + serviceName.ToUpper() + "成功";
return true;
}
catch (Exception ex)
{
msg = ex.Message;
return false;
}
}
public void run()
{
OracleConnection con = null;
OracleTransaction txn;
Exception exp = null;
try
{
BeginCase("OracleTransaction Rollback");
//
//prepare data
base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
string Result = "";
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
txn = con.BeginTransaction();
OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn);
cmd.ExecuteNonQuery();
txn.Rollback();
//
//
cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con);
Result = cmd.ExecuteScalar().ToString();
Compare(Result,"Last100" );
this.Log(Result);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static string GetFeature(string p_key, int p_size, int p_page, double p_left, double p_bottom, double p_right,double p_top )
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD_TOTAL, p_size,p_left,p_right,p_bottom,p_top,p_key);
object total = comm.ExecuteScalar();
comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD, p_key,p_left, p_right,p_bottom,p_top,p_page * p_size,(p_page-1)*p_size);
OracleDataReader rdr = comm.ExecuteReader();
string str = Reader2JSON.ToJSON(rdr);
sb.Append("{total:");
sb.Append(total);
sb.Append(",page:");
sb.Append(p_page);
sb.Append(",content:");
sb.Append(str);
sb.Append("}");
conn.Close();
}
return sb.ToString();
}
public static bool AddFeature(string usrid,string usrname,string password,int privilege)
{
StringBuilder sb = new StringBuilder();
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
OracleParameter[] parms = new OracleParameter[]{
new OracleParameter(":USRID",OracleType.VarChar),
new OracleParameter(":USRNAME",OracleType.VarChar),
new OracleParameter(":PASSWORD",OracleType.VarChar),
new OracleParameter(":PRIVILEGE",OracleType.UInt32)
};
parms[0].Value = usrid;
parms[1].Value = usrname;
parms[2].Value = password;
parms[3].Value = privilege;
comm.CommandText = SQL_INSERT_USER;
comm.CommandType = CommandType.Text;
foreach (OracleParameter parm in parms)
{
comm.Parameters.Add(parm);
}
comm.ExecuteNonQuery();
conn.Close();
return true;
}
}
public ActionResult Create(CreateChart Chart)
{
if (ModelState.IsValid)
{
string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;";
string CHART_ID ;
using (OracleConnection connection = new OracleConnection(connectionString))
{
string number = null;
number = "";
OracleCommand cmd1 = new OracleCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select nvl(max(substr(CHART_ID,7,3)),0) from KMIINTRANET_MASTER_CHART where substr(CHART_ID,1,6)='" + Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + "'";
cmd1.Connection = connection;
connection.Open();
objDataReader = cmd1.ExecuteReader();
if (objDataReader.HasRows)
{
objDataReader.Read();
number = Convert.ToString(Convert.ToInt32(objDataReader[0]) + 1);
if (number.Length == 1)
{
number = "00" + number;
}
else if (number.Length == 2)
{
number = "0" + number;
}
else if (number.Length == 3)
{
number = number;
}
}
cmd1 = null;
connection.Close();
OracleCommand cmd2 = new OracleCommand();
CHART_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number;
byte[] tempFile = new byte[Chart.ChartFile.InputStream.Length];
Chart.ChartFile.InputStream.Read(tempFile, 0, tempFile.Length);
cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_CHART(CHART_ID,CHART_TITLE,CHART_FILE,CREATE_BY,CREATE_DATE) VALUES (:chartid,:title,:chartfile,'" + Session["USER"] + "',sysdate)";
cmd2.CommandType = CommandType.Text;
cmd2.Connection = connection;
connection.Open();
cmd2.Parameters.Add(":chartfile", OracleType.Blob).Value = tempFile;
cmd2.Parameters.Add(":title", OracleType.VarChar).Value = Chart.title.ToString();
cmd2.Parameters.Add(":chartid", OracleType.VarChar).Value = CHART_ID;
cmd2.ExecuteNonQuery();
cmd2 = null;
connection.Close();
return RedirectToAction("Index");
}
}
return View();
}
public static void GetFlowStatus(ComboBox cbox, string sql)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
try
{
//OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader datareader = cmd.ExecuteReader();
while (datareader.Read())
{
cbox.Items.Add(datareader[0].ToString());
}
datareader.Close();
//conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
//return;
}
finally { conn.Close(); }
}
protected void ddlAmphur_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandText = "select * from TB_DISTRICT where AMPHUR_ID=:DISTRICT_ID";
sqlCmd.Parameters.Add(":DISTRICT_ID", ddlAddressAmphurCondition.SelectedValue);
sqlCmd.Connection = sqlConn;
sqlConn.Open();
OracleDataAdapter da = new OracleDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
ddlAddressDistrictCondition.DataSource = dt;
ddlAddressDistrictCondition.DataValueField = "DISTRICT_ID";
ddlAddressDistrictCondition.DataTextField = "DISTRICT_TH";
ddlAddressDistrictCondition.DataBind();
sqlConn.Close();
ddlAddressDistrictCondition.Items.Insert(0, new ListItem("--กรุณาเลือก ตำบล--", "0"));
}
}
}
catch { }
}
public static void MarkDeletedSpoolRecord(string projectid, string spname, string drawing, string username)
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接
conn.Open();
string queryString = "SP_MarkDeleteSpoolRecord";
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand(queryString, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid;
cmd.Parameters.Add("spname_in", OracleType.VarChar).Value = spname;
cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing;
cmd.Parameters.Add("username_in", OracleType.VarChar).Value = username;
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();
}
}
/// <summary>
/// 释放当前连接
/// </summary>
/// <param name="conn">oracle连接通道</param>
/// <param name="connStr">oracle连接字符串</param>
public static void freeConnectionToPool(OracleConnection conn,string connStr)
{
//如果当前连接字符串不在连接管理器内，首先在管理器中创建
if (queueManager.ContainsKey(connStr) == false)
queueManager.Add(connStr, new Queue<OracleConnection>());
lock (queueManager[connStr])
{
if (queueManager[connStr].Count < maxOpen)
{
queueManager[connStr].Enqueue(conn);
}
else
{
try
{
conn.Close();
conn = null;
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
}
}
/// <summary>
/// ִ�в�ѯ��䣬����DataSet
/// </summary>
/// <param name="SQLString">��ѯ���</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
if (SQLString != null && SQLString.Trim() != "")
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.OracleClient.OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return ds;
}
}
else
{
return null;
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
OracleTransaction txn = null;
try
{
BeginCase("BeginTransaction - connection close");
try
{
txn = con.BeginTransaction();
}
catch (Exception ex) {exp = ex;}
Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName );
exp=null;
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
con.Open();
try
{
BeginCase("BeginTransaction - connection close");
txn = con.BeginTransaction();
Compare(txn == null,false );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
public static void FillTreeView(TreeNode node, string sql)
{
try
{
OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OracleDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
node.Nodes.Add(dr[0].ToString());
}
conn.Close();
dr.Close();
}
}
catch (OracleException ox)
{
MessageBox.Show(ox.Message.ToString());
return;
}
}
protected void ddlCampus_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING))
{
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandText = "select * from TB_FACULTY where CAMPUS_ID = " + ddlCampus.SelectedValue;
sqlCmd.Connection = sqlConn;
sqlConn.Open();
OracleDataAdapter da = new OracleDataAdapter(sqlCmd);
DataTable dt = new DataTable();
da.Fill(dt);
ddlFaculty.DataSource = dt;
ddlFaculty.DataValueField = "FACULTY_ID";
ddlFaculty.DataTextField = "FACULTY_NAME";
ddlFaculty.DataBind();
sqlConn.Close();
ddlFaculty.Items.Insert(0, new ListItem("--กรุณาเลือกสำนัก / สถาบัน / คณะ--", "0"));
ddlDivision.Items.Clear();
ddlDivision.Items.Insert(0, new ListItem("--กรุณาเลือกกอง / สำนักงานเลขา / ภาควิชา--", "0"));
ddlWorkDivision.Items.Clear();
ddlWorkDivision.Items.Insert(0, new ListItem("--กรุณาเลือกงาน / ฝ่าย--", "0"));
}
}
}
catch { }
}
/// <summary>
/// execute a query£¬return DataSet
/// </summary>
/// <param name="SQLString"></param>
/// <returns>DataSet</returns>
public static DataSet Query(string connectionString, string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
OracleDataAdapter command = new OracleDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (OracleException ex)
{
throw new Exception(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
return ds;
}
}
public void run()
{
Exception exp = null;
OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OracleCommand cmd = new OracleCommand("Select * From Customers", con);
OracleDataReader rdr = cmd.ExecuteReader();
try
{
BeginCase("Before execute");
Compare(rdr.Depth , 0);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("After execute");
rdr.Read();
Compare(rdr.Depth , 0);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
if (con.State == ConnectionState.Open) con.Close();
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="name"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static bool ExecuteProduce(string name, IList<DbParameter> paramList)
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
OracleCommand dbCommand = new OracleCommand();
dbCommand.CommandType = CommandType.StoredProcedure;
dbCommand.CommandText = name;
try
{
foreach (DbParameter param in paramList)
{
dbCommand.Parameters.Add(param);
}
dbCommand.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
conn.Close();
return false;
}
finally
{
conn.Close();
}
}
protected void showDonVi()
{
try
{
string user = "******";
string dataSource = showSiteName();
if (dataSource.ToUpper() == "BACGIANG")
user = "******";
string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", dataSource, user);
if (dataSource.ToUpper() == "NGHEAN")
txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "PC1CMIS01");
if (dataSource.ToUpper() == "NINHBINH")
txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "pc1cmis01");
OracleConnection ora_con = new OracleConnection(txt_cmd);
txt_cmd = "select MA_DVIQLY, TEN_DVIQLY from cmis01.d_dvi_qly where CAP_DVI=3 order by MA_DVIQLY";
OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con);
DataSet ds = new DataSet();
ora_con.Open();
ora_da.Fill(ds);
ora_con.Close();
ddDonViQL.DataSource = ds;
ddDonViQL.DataBind();
}
catch (Exception ex) { lbError.Text = ex.Message; }
}
/// <summary>
/// 模块定义
/// </summary>
/// <param name="con">OracleConnection</param>
/// <param name="ModelCode">模块代码</param>
/// <returns></returns>
public static List<FLOW_MODELDEFINE_T> GetModelDefineByCode(OracleConnection con,string ModelCode)
{
if (string.IsNullOrEmpty(con.ConnectionString))
{
con.ConnectionString = ADOHelper.ConnectionString;
}
List<FLOW_MODELDEFINE_T> list = new List<FLOW_MODELDEFINE_T>();
OracleDataReader dr = null;
try
{
#region
//OracleCommand cmd = con.CreateCommand();
//cmd.CommandText = "select * from FLOW_MODELDEFINE_T where MODELCODE='" + ModelCode + "'";
//dr = cmd.ExecuteReader();
string sql = "select * from FLOW_MODELDEFINE_T where MODELCODE='" + ModelCode + "'";
dr= MsOracle.ExecuteReaderByTransaction(con, sql, null);
while (dr.Read())
{
#region define
FLOW_MODELDEFINE_T define = new FLOW_MODELDEFINE_T();
define.CREATECOMPANYID = dr["CREATECOMPANYID"] == DBNull.Value ? null : dr["CREATECOMPANYID"].ToString();
define.CREATEDATE = (DateTime)dr["CREATEDATE"];
define.CREATEDEPARTMENTID = dr["CREATEDEPARTMENTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEPOSTID = dr["CREATEPOSTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString();
define.CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? null : dr["CREATEUSERID"].ToString();
define.CREATEUSERNAME = dr["CREATEUSERNAME"] == DBNull.Value ? null : dr["CREATEUSERNAME"].ToString();
define.DESCRIPTION = dr["DESCRIPTION"] == DBNull.Value ? null : dr["DESCRIPTION"].ToString();
define.EDITDATE = dr["EDITDATE"] == DBNull.Value ? null : (DateTime?)dr["EDITDATE"];
define.EDITUSERID = dr["EDITUSERID"] == DBNull.Value ? null : dr["EDITUSERID"].ToString();
define.EDITUSERNAME = dr["EDITUSERNAME"] == DBNull.Value ? null : dr["EDITUSERNAME"].ToString();
define.MODELCODE = dr["MODELCODE"] == DBNull.Value ? null : dr["MODELCODE"].ToString();
define.MODELDEFINEID = dr["MODELDEFINEID"] == DBNull.Value ? null : dr["MODELDEFINEID"].ToString();
define.PARENTMODELCODE = dr["PARENTMODELCODE"] == DBNull.Value ? null : dr["PARENTMODELCODE"].ToString();
define.SYSTEMCODE = dr["SYSTEMCODE"] == DBNull.Value ? null : dr["SYSTEMCODE"].ToString();
list.Add(define);
#endregion
}
dr.Close();
#endregion
return list;
}
catch (Exception ex)
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
if (con.State == ConnectionState.Open)
{
con.Close();
}
throw new Exception("GetModelDefineByCode-->" + ex.Message);
}
}
private void EditStudentFrm_Load(object sender, EventArgs e)
{
//Author: Niall Stack - t00174406
string CloudDB = "Data Source=cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw4;Password=sw4;";
try
{
OracleConnection conn = new OracleConnection(CloudDB);
OracleCommand cmd = new OracleCommand("SELECT * FROM Students", conn);
cmd.CommandType = CommandType.Text;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ss");
studGrd.DataSource = ds.Tables["ss"];
conn.Close();
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
}
public string MianProcess(string Company_code, string ConfID, string FielName, string TransType, string sp_name)
{
string retval = "";
try
{
System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString);
System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
cmd.Parameters.Clear();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp_name;
cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code;
cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID;
cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName;
cmd.Parameters.Add("P_trans_type", OracleType.VarChar, 2000).Value = TransType;
cmd.Parameters.Add("v_retval", OracleType.VarChar, 2000).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
retval = cmd.Parameters["v_retval"].Value.ToString();
}
catch (Exception ex)
{
throw new Exception("Data Base Error: " + ex.Message);
}
#region
//try
//{
// System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString);
// con.Open();
// System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand();
// cmd.CommandText = "sp_raw_DATALOAD1";
// cmd.CommandType = CommandType.StoredProcedure;
// cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code;
// cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID;
// cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName;
// int result = cmd.ExecuteNonQuery();
// con.Close();
// retval = "Data successfully Processed.";
//}
//catch (Exception ex)
//{
// retval = ex.Message;
//}
#endregion
return(retval);
}
/// <summary>
/// 添加Oracle移动终端资讯内容表
/// </summary>
/// <returns></returns>
public long AddTB_PUSH_INFO(PopupMsgPlan popupMsgPlan)
{
long res = 0;
OracleConnection Con = new System.Data.OracleClient.OracleConnection(SqlConnectFactory.BaiduPush);
Con.Open();
string cmdText = "insert into tb_push_info " +
"(fid, infoid, title, infoabstract, author, createdtime, infotype, infocontent,OperateDate,PlanCount,RealCount,ClickCount,EDITOR,PLATFORM) " +
"values " +
"(seq_push_info.nextval, 'ds" + popupMsgPlan.PlanId + "', '" + popupMsgPlan.Title + "', '" + popupMsgPlan.Content + "', '系统推送', to_date（'" + popupMsgPlan.CreatedTime + "','yyyy-mm-dd hh24:mi:ss'), '" + popupMsgPlan.PushColumn + "', :infocontent,sysdate,0,0,0,'" + popupMsgPlan.Editor + "','" + popupMsgPlan.PushPlatform + "')";
OracleCommand cmd = new OracleCommand(cmdText, Con);
OracleParameter op = new OracleParameter("infocontent", OracleType.Clob);
op.Value = StringHelper.RetentionHTML(popupMsgPlan.Content);
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
Con.Close();
return(res);
}
public bool TestConnection()
{
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
return(true);
}
catch (SqlException ex)
{
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
return(false);
}
}
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
{
int idx = DataList1.SelectedIndex;
Label lbl = (Label)DataList1.Items[idx].FindControl("Label2");
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString());
System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_images WHERE ID = '" + lbl.Text + "'", conn);
conn.Open();
System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default);
try
{
while (myReader.Read())
{
System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE"));
if (!myLob.IsNull)
{
string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME"));
//Use buffer to transfer data
byte[] b = new byte[myLob.Length];
//Read data from database
myLob.Read(b, 0, (int)myLob.Length);
Response.AddHeader("content-disposition", "attachment;filename=" + FN);
Response.ContentType = "application/octectstream";
Response.BinaryWrite(b);
Response.End();
}
}
}
finally
{
myReader.Close();
conn.Close();
}
}
public int ExcuteDataTable(DataTable srcTable, string commandText, System.Data.CommandType commandType)
{
if (this.Connection.State != System.Data.ConnectionState.Open)
{
this.Connection.Open();
}
if (!this.needTransaction)
{
tran = null;
}
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, this.Connection, tran, CommandType.Text, commandText);
OracleDataAdapter da = new OracleDataAdapter(cmd);
try
{
da.Fill(srcTable);
da.Dispose();
}
catch (System.Data.OracleClient.OracleException ex)
{
return(-1);
}
finally
{
if (mustCloseConnection)
{
if (!needTransaction)
{
Connection.Close();
}
}
}
return(1);
}
/// <summary>
/// 添加Oracle移动终端资讯内容表
/// </summary>
/// <returns></returns>
public long AddTB_PUSH_INFO(News news, string infoType, PopupMsgPlan popupMsgPlan)
{
long res = 0;
//var dbFactory = new OrmLiteConnectionFactory(SqlConnectFactory.BaiduPush, OracleDialect.Provider);
//TB_PUSH_INFO pushInfo = new TB_PUSH_INFO();
//pushInfo.INFOID = news.NewsId;
//pushInfo.TITLE = news.Title;
//pushInfo.INFOABSTRACT = news.NewsAbstract;
//pushInfo.AUTHOR = news.Author;
//pushInfo.CREATEDTIME = news.CreatedTime;
//pushInfo.INFOTYPE = infoType;
//pushInfo.INFOCONTENT = news.Content;
//using (var db = dbFactory.OpenDbConnection())
//{
// res = db.InsertParam<TB_PUSH_INFO>(pushInfo);
//}
//return res;
OracleConnection Con = new System.Data.OracleClient.OracleConnection(SqlConnectFactory.BaiduPush);
Con.Open();
string cmdText = "insert into tb_push_info " +
"(fid, infoid, title, infoabstract, author, createdtime, infotype, infocontent,OperateDate,PlanCount,RealCount,ClickCount,EDITOR,PLATFORM) " +
"values " +
"(seq_push_info.nextval, '" + news.NewsId + "', '" + news.Title + "', '" + news.NewsAbstract + "', '" + news.Author + "', to_date（'" + news.CreatedTime + "','yyyy-mm-dd hh24:mi:ss'), '" + infoType + "', :infocontent,sysdate,0,0,0,'" + popupMsgPlan.Editor + "','" + popupMsgPlan.PushPlatform + "')";
OracleCommand cmd = new OracleCommand(cmdText, Con);
OracleParameter op = new OracleParameter("infocontent", OracleType.Clob);
op.Value = StringHelper.RetentionHTML(news.Content);
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
Con.Close();
return(res);
}
// This routine writes a User Event audit entry
// into the SYS_USE_ADUIT table
public void WriteUserEvent(string AppName, string UserID, UserEvent uEvent)
{
System.Data.OracleClient.OracleConnection ConnOracle;
System.Data.OracleClient.OracleCommand CmdOracle;
//Parameters for the Oracle Package
OracleParameter return_val = new OracleParameter("v_success", OracleType.VarChar, 1);
return_val.Direction = ParameterDirection.ReturnValue;
OracleParameter p_external_user_id = new OracleParameter("p_external_user_id", OracleType.VarChar, 250);
OracleParameter p_system_use = new OracleParameter("p_system_use", OracleType.VarChar, 250);
OracleParameter p_system_activity = new OracleParameter("p_system_activity", OracleType.VarChar, 250);
ConnOracle = new System.Data.OracleClient.OracleConnection();
CmdOracle = new System.Data.OracleClient.OracleCommand();
try
{
p_external_user_id.Value = UserID;
switch (uEvent)
{
case UserEvent.LogonSuccessful:
p_system_activity.Value = "Logon successful";
break;
case UserEvent.LogonFailed:
p_system_activity.Value = "Logon failed";
break;
case UserEvent.Logoff:
p_system_activity.Value = "Logoff";
break;
case UserEvent.SessionTimeout:
p_system_activity.Value = "Session timeout";
break;
}
p_system_use.Value = AppName;
//Use Oracle Package
ConnOracle.ConnectionString = GSA.R7BD.Utility.DataAccess.ConnStrBDFApps();
CmdOracle.Connection = ConnOracle;
CmdOracle.CommandText = "bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC";
CmdOracle.CommandType = CommandType.StoredProcedure;
CmdOracle.Parameters.Clear();
CmdOracle.Parameters.Add(p_external_user_id);
CmdOracle.Parameters.Add(p_system_activity);
CmdOracle.Parameters.Add(p_system_use);
CmdOracle.Parameters.Add(return_val);
if (ConnOracle.State != ConnectionState.Open)
{
ConnOracle.Open();
}
CmdOracle.ExecuteScalar();
//Check the return value
if (CmdOracle.Parameters["v_success"].Value.ToString().Trim() == "F")
{
GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", "Call to bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC failed.");
}
}
catch (Exception ex)
{
// Write the exception to the error log
GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", ex.Message);
}
finally
{
if (ConnOracle.State == ConnectionState.Open)
{
ConnOracle.Close();
}
}
}
private DataTable ConnectAndQuery(string devicename, bool includeAbondoned, string startDate, string endDate)
{
if (String.IsNullOrEmpty(devicename))
{
devicename = "V-0516";
}
string connectionString = GetOracleConnectionString();
using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection())
{
try
{
connection.ConnectionString = connectionString;
try { connection.Close(); }
catch (Exception ex)
{ }
connection.Open();
//Console.WriteLine("State: {0}", connection.State);
//Console.WriteLine("ConnectionString: {0}",
// connection.ConnectionString);
OracleCommand command = connection.CreateCommand();
string sqlDateFilter = (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) ? " and t.start_date >= '" + startDate + "' and t.end_date <= '" + endDate + "'" : "";
string sql = "select " +
"vct.separator_id, vct.conduit_name," +
"count(*) TotalTests, " +
"CASE vc.actual_status " +
"WHEN 'ABAN_01' THEN 0 WHEN 'ABAN_02' THEN 0 WHEN 'ABAN_03' THEN 0 WHEN 'ABAN_04' THEN 0 ELSE 1 END iconduitstatus," +
"sum(case when VALIDITY_STATUS='valid test' then 1 else 0 end) Valid, " +
"sum(case when VALIDITY_STATUS='invalid test' then 1 else 0 end) InValid," +
"sum(case when VALIDITY_STATUS='valid test' then 0 when VALIDITY_STATUS= 'invalid test' then 0 else 1 end) NotValidated, " +
"(select NVL(min(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minwater," +
"(select NVL(max(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxwater," +
"(select NVL(min(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minoil," +
"(select NVL(max(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxoil," +
"(select NVL(min(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minbsw," +
"(select NVL(max(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxbsw," +
"(select NVL(min(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") mingasout," +
"(select NVL(max(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxgasout " +
"FROM V_CONDUIT_TEST VCT INNER JOIN V_CONDUIT VC ON VCT.CONDUIT_NAME=VC.CONDUIT_NAME " +
"WHERE VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id='" + devicename + "' ";
if (!includeAbondoned)
{
sql += "and vc.actual_status not like '%ABAN_%' ";
}
if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"]))
{
sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' ";
}
sql += "group by VCT.conduit_name, vct.separator_id, vc.actual_status order by 2";
command.CommandText = sql;
DataSet ds = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter(command);
adapter.Fill(ds);
connection.Close();
return(ds.Tables[0]);
}
catch (Exception ex)
{
connection.Close();
}
return(null);
}
}
public override void Close()
{
con.Close();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
lblidddl.Visible = false;
txtCorreo.Text = Session["Value"].ToString();
Array enumList1 = Enum.GetValues(typeof(tiposTarea));
foreach (tiposTarea getTipoTarea in enumList1)
{
ddlTipoTarea.Items.Add(new ListItem(getTipoTarea.ToString(), ((int)getTipoTarea).ToString()));
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
adaptador.SelectCommand = comando3;
DataTable dt = new DataTable();
adaptador.Fill(dt);
tablasTareasDevueltas.DataSource = dt;
tablasTareasDevueltas.DataBind();
if (tablasTareasDevueltas.Rows.Count == 0)
{
// Response.Write("<script>('No hay tareas pendientes')</script>");
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>");
}
try
{
ora2.Open();
System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado");
comando.Connection = ora2;
comando.CommandType = System.Data.CommandType.StoredProcedure;
comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output;
System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter();
System.Data.OracleClient.OracleDataReader rdr = comando.ExecuteReader();
while (rdr.Read())
{
ddlFlujo.Items.Add(rdr.GetString(0).ToString());
}
ora2.Close();
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
}
public string AggiornaMPWrUSER_N(int wr_id, string p_data, string p_data1,
int p_stato, string p_motivo, int p_addetto_id//, string p_materiali
)
{
//-----inserire nome del file nuovo
string numrdl = "";
string STORENAME = "PACK_MAN_PROG.AggiornaWRMPUSER_NOTE_N";
conn.Open();
System.Data.OracleClient.OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = STORENAME;
OracleParameter par1 = new OracleParameter();
par1.ParameterName = "p_wr_id";
par1.OracleType = System.Data.OracleClient.OracleType.Number;
par1.Direction = ParameterDirection.Input;
par1.Value = wr_id;
par1.Size = 10;
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter();
par2.ParameterName = "p_data";
par2.OracleType = System.Data.OracleClient.OracleType.VarChar;
par2.Direction = ParameterDirection.Input;
par2.Size = 50;
par2.Value = p_data;
cmd.Parameters.Add(par2);
OracleParameter par3 = new OracleParameter();
par3.ParameterName = "p_data1";
par3.OracleType = System.Data.OracleClient.OracleType.VarChar;
par3.Direction = ParameterDirection.Input;
par3.Size = 50;
par3.Value = p_data1;
cmd.Parameters.Add(par3);
OracleParameter par4 = new OracleParameter();
par4.ParameterName = "p_stato";
par4.OracleType = System.Data.OracleClient.OracleType.Number;
par4.Direction = ParameterDirection.Input;
par4.Value = p_stato;
par4.Size = 10;
cmd.Parameters.Add(par4);
OracleParameter par5 = new OracleParameter();
par5.ParameterName = "p_motivo";
par5.OracleType = System.Data.OracleClient.OracleType.VarChar;
par5.Direction = ParameterDirection.Input;
par5.Size = 255;
par5.Value = p_motivo;
cmd.Parameters.Add(par5);
OracleParameter par6 = new OracleParameter();
par6.ParameterName = "p_addetto_id";
par6.OracleType = System.Data.OracleClient.OracleType.Number;
par6.Direction = ParameterDirection.Input;
par6.Size = 10;
par6.Value = p_addetto_id;
cmd.Parameters.Add(par6);
// OracleParameter par7 = new OracleParameter();
// par7.ParameterName="p_materiali";
// par7.OracleType = System.Data.OracleClient.OracleType.VarChar;
// par7.Direction = ParameterDirection.Input;
// par7.Size=255;
// par7.Value=p_materiali;
// cmd.Parameters.Add(par7);
OracleParameter par8 = new OracleParameter();
par8.ParameterName = "p_username";
par8.OracleType = System.Data.OracleClient.OracleType.VarChar;
par8.Direction = ParameterDirection.Input;
par8.Size = 255;
par8.Value = System.Web.HttpContext.Current.User.Identity.Name;
cmd.Parameters.Add(par8);
OracleParameter par9 = new OracleParameter();
par9.ParameterName = "p_IdOut";
par9.OracleType = System.Data.OracleClient.OracleType.VarChar;
par9.Direction = ParameterDirection.Output;
par9.Size = 255;
par9.Value = "";
cmd.Parameters.Add(par9);
cmd.ExecuteNonQuery();
numrdl = par9.Value.ToString();
//numrdl = Int32.Parse(cmd.Parameters["p_IdOut"].Value.ToString());
conn.Close();
cmd.Dispose();
return(numrdl);
}
public string GENERAPDFSGA(int wr_id, string sga, string username)
{
System.Web.UI.Page pg = new Page();
string pathRptSource;
ReportDocument crReportDocument = new ReportDocument();
Classi.ManCorrettiva.MANCORRETTIVASFOGLIARDL _RichiestaIntervento = new Classi.ManCorrettiva.MANCORRETTIVASFOGLIARDL();
DataSet Ds = _RichiestaIntervento.ReportSGA(wr_id);
dsRapportino dsP = new dsRapportino();
int i = 0;
for (i = 0; i <= Ds.Tables[0].Rows.Count - 1; i++)
{
dsP.Tables["sga"].ImportRow(Ds.Tables[0].Rows[i]);
}
pathRptSource = pg.Server.MapPath("../Report/RptSGA1.rpt");
crReportDocument.Load(pathRptSource);
crReportDocument.SetDataSource(dsP);
//string Fname = pg.Server.MapPath("../Report/" + pg.Session.SessionID.ToString() + ".pdf");//
ExportOptions optExp;
DiskFileDestinationOptions optDsk = new DiskFileDestinationOptions();
PdfRtfWordFormatOptions optPdfRtf = new PdfRtfWordFormatOptions();
optExp = crReportDocument.ExportOptions;
optExp.ExportFormatType = ExportFormatType.PortableDocFormat;
optExp.FormatOptions = optPdfRtf;
optExp.ExportDestinationType = ExportDestinationType.DiskFile;
//-----inserire nome del file nuovo
string STORENAME = "PACK_TRACCIA_DOC.SP_INS_SGA";
conn.Open();
System.Data.OracleClient.OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = STORENAME;
OracleParameter par1 = new OracleParameter();
par1.ParameterName = "p_id_wr";
par1.OracleType = System.Data.OracleClient.OracleType.Number;
par1.Direction = ParameterDirection.Input;
par1.Value = wr_id;
par1.Size = 100;
cmd.Parameters.Add(par1);
OracleParameter par2 = new OracleParameter();
par2.ParameterName = "p_user";
par2.OracleType = System.Data.OracleClient.OracleType.VarChar;
par2.Direction = ParameterDirection.Input;
par2.Size = 50;
par2.Value = username;
cmd.Parameters.Add(par2);
OracleParameter par3 = new OracleParameter();
par3.ParameterName = "p_sga";
par3.OracleType = System.Data.OracleClient.OracleType.VarChar;
par3.Direction = ParameterDirection.Input;
par3.Size = 100;
par3.Value = sga;
cmd.Parameters.Add(par3);
OracleParameter par4 = new OracleParameter();
par4.ParameterName = "p_codice";
par4.OracleType = System.Data.OracleClient.OracleType.VarChar;
par4.Direction = ParameterDirection.Output;
par4.Size = 50;
par4.Value = "";
cmd.Parameters.Add(par4);
cmd.ExecuteNonQuery();
string NOMESGA = cmd.Parameters["p_codice"].Value.ToString();
// OracleDataReader rdr = cmd.ExecuteReader();
// string pr=rdr.GetString();
//string pr=cmd.ExecuteScalar();
conn.Close();
cmd.Dispose();
//@"C:\Inetpub\wwwroot\RL\Doc_DB\"
//optDsk.DiskFileName = pathRptSource+sga+".pdf";
optDsk.DiskFileName = @System.Configuration.ConfigurationSettings.AppSettings["DIRSGA"] + NOMESGA;
//Fname;
optExp.DestinationOptions = optDsk;
crReportDocument.Export();
string FileZip = Path.GetDirectoryName(optDsk.DiskFileName) + @"\" + Path.GetFileNameWithoutExtension(optDsk.DiskFileName) + ".zip";
if (File.Exists(FileZip))
{
File.Delete(FileZip);
}
ZipOutputStream s = new ZipOutputStream(File.Create(FileZip));
s.SetLevel(5); // 0 - store only to 9 -
FileStream fs = File.OpenRead(optDsk.DiskFileName);
byte[] buffer = new byte[fs.Length];
fs.Read(buffer, 0, buffer.Length);
ZipEntry entry = new ZipEntry(Path.GetFileName(optDsk.DiskFileName));
s.PutNextEntry(entry);
s.Write(buffer, 0, buffer.Length);
s.Finish();
s.Close();
fs.Close();
TheSite.Classi.MailSend mail = new TheSite.Classi.MailSend();
//mail.SendMail(optDsk.DiskFileName,wr_id,DocType.SGA);
mail.SendMail(FileZip, wr_id, DocType.SGA);
return(optDsk.DiskFileName);
}
public bool Open(string connectionString, bool testIt)
{
int pos = connectionString.IndexOf(":");
if (pos == -1)
{
return(false);
}
try
{
Close();
string type = connectionString.Substring(0, pos);
string connStr = connectionString.Substring(pos + 1, connectionString.Length - pos - 1);
switch (type.ToLower())
{
case "oledb":
oledbConnection = new OleDbConnection(connStr);
if (testIt)
{
oledbConnection.Open();
oledbConnection.Close();
}
break;
case "odbc":
odbcConnection = new OdbcConnection(connStr);
if (testIt)
{
odbcConnection.Open();
odbcConnection.Close();
}
break;
case "oracleclient":
case "oracle":
oracleConnection = new OracleConnection(connStr);
if (testIt)
{
oracleConnection.Open();
oracleConnection.Close();
}
break;
case "sqlclient":
case "sql":
sqlConnection = new SqlConnection(connStr);
if (testIt)
{
sqlConnection.Open();
sqlConnection.Close();
}
break;
case "npgsql":
try
{
dbFactory = DataProvider.PostgresProvider;
dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = connStr;
if (testIt)
{
dbConnection.Open();
dbConnection.Close();
}
break;
}
catch (Exception ex)
{
_errMsg = ex.Message;
return(false);
}
break;
default:
_errMsg = "Test Connection is not supported for '" + type + "'";
return(false);
}
return(true);
}
catch (Exception ex)
{
_errMsg = ex.Message;
return(false);
}
}
public static int ExecuteSql(string SQLString)
{
int num2;
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand command = new OracleCommand(SQLString, connection);
try
{
connection.Open();
num2 = command.ExecuteNonQuery();
}
catch (OracleException exception)
{
connection.Close();
throw new Exception(exception.Message);
}
finally
{
if (command != null)
{
command.Dispose();
}
}
}
return num2;
}