public static string CheckDPISConnect()
{
string ret = "false";
string connStr = DPISConnectionString;
OracleConnection conn = new OracleConnection(connStr);
try
{
conn.Open();
string ServerName = conn.DataSource;
string DbName = conn.Database;
ret = "true|ServerName = " + ServerName + "&DatabaseName=" + DbName;
}
catch (Exception ex)
{
ret = "false|Exception " + ex.Message + Environment.NewLine + ex.StackTrace + Environment.NewLine;
ret += "ConnectionString = " + connStr;
}
finally
{
conn.Close();
conn.Dispose();
}
return ret;
}
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();
}
protected void Page_Load(object sender, EventArgs e)
{
string guid = string.Empty;
guid = Request["q"];
int ww = Convert.ToInt32(Request["w"]);
int wh = Convert.ToInt32(Request["h"]);
if (guid == null || guid == string.Empty)
{
this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>输入参数无效</h1>";
Response.End();
}
else
{
using (OracleConnection conn = new OracleConnection(OraHelper.str))
{
conn.Open();
OracleCommand comm = conn.CreateCommand();
comm.CommandText = string.Format(SQL_SELECT, guid);
OracleDataReader dr = comm.ExecuteReader();
if (dr.Read())
{
string innerHTML = dr["DATA"].ToString();
this.map.InnerHtml = innerHTML;
this.map.Style.Value = "display:block;width:" + ww + "px;height:" + wh + "px;";
}
else
{
this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>该分享不存在</h1>";
}
}
}
}
/// <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;
}
}
protected void lbuBack_Click(object sender, EventArgs e)
{
PersonnelSystem ps = PersonnelSystem.GetPersonnelSystem(this);
Person loginPerson = ps.LoginPerson;
int count = 0;
using (OracleConnection con = new OracleConnection(DatabaseManager.CONNECTION_STRING)) {
con.Open();
using (OracleCommand com = new OracleCommand("SELECT COUNT(LEAVE_ID) FROM LEV_LEAVE WHERE CMD_HIGH_ID = '" + loginPerson.CitizenID + "' AND LEV_LEAVE.STATE_ID = 3", con)) {
using (OracleDataReader reader = com.ExecuteReader()) {
while (reader.Read()) {
count = int.Parse(reader.GetValue(0).ToString());
}
}
}
}
if (count == 0) {
error_area.InnerHtml = "ไม่มีรายการที่ท่านต้องอนุมัติ";
} else {
error_area.InnerHtml = "กรุณาเลือกรายการที่ต้องอนุมัติ";
}
//error_area.Attributes["class"] = "alert alert_info";
error_area.Attributes["class"] = null;
error_area.InnerHtml = "";
MultiView1.ActiveViewIndex = 0;
}
/// <summary></summary>
/// 对于 UPDATE、INSERT 和 DELETE 语句，返回值为该命令所影响的行数。对于其他所有类型的语句，返回值为 -1
///
/// UPDATE、INSERT 和 DELETE 语句
public int ExecuteNonQuery(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(-1);
}
int rv = -1;
OracleTransaction oracleTransaction = null;
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
oracleTransaction = oracleConnection.BeginTransaction();
oracleCommand.Transaction = oracleTransaction;
rv = oracleCommand.ExecuteNonQuery();
oracleTransaction.Commit();
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
oracleTransaction.Rollback();
rv = -1;
}
return(rv);
}
}
/// <summary></summary>
/// 执行查询，并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。
///
/// SELECT 语句
/// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列；如果结果集为空或结果为 REF CURSOR，则为空引用
public object ExecuteScalar(string sql)
{
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
try
{
if (oracleConnection.State == System.Data.ConnectionState.Closed)
{
oracleConnection.Open();
}
oracleCommand = new OracleCommand(sql, oracleConnection);
return(oracleCommand.ExecuteScalar());
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
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();
}
}
} //end of this method
// Madan Saini 03/29/2004 -- Clear Text Passwords for PMT
// This method validate an Internet user's membership of a web application
public static bool IsDBUserValid(string loginID, string password, string appName, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
//string encodedPassword = SharedPassword.Encode(password);
loginID = loginID.ToUpper();
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = Queries.getValidUser(loginID, password, appName);
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
if (rdrReader.Read())
{
valid = true;
}
else
{
valid = false;
}
cmdVITAP.Dispose();
return(valid);
} //end of this method
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);
}
}
private void bt_okview_Click(object sender, EventArgs e)
{
if (txt_nomview.Text == "_VIEW")
{
MessageBox.Show("Debe Ingresar Un Nombre al View", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
query = txt_query2.Text;
string comando = "create or replace view \"" + txt_nomview.Text + "\" as " + query;
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
OracleCommand cmd = new OracleCommand(comando, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Se ha creado view " + "\"" + txt_nomview.Text + "\"", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
Ver_Views();
Borrar_Views_Combo();
Carga_Views();
show_viewtbx.Text = txt_nomview.Text;
tabControl1.SelectedTab = this.tabPage4;
}
catch (Exception ex)
{
MessageBox.Show("Debe Ingresar una Sentencia SQL o tiene el Siguiente Error: \n" + ex.Message.ToString(), "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// Form1.conn.Close();
System.Drawing.Color clr;
clr = System.Drawing.Color.Red;
label4.ForeColor = clr;
}
}
}
int MSOracleClient()
{
System.Data.OracleClient.OracleConnectionStringBuilder connBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder();
connBuilder.DataSource = txtDataSource.Text.Trim();
connBuilder.UserID = txtUserId.Text.Trim();
connBuilder.Password = txtPwd.Text.Trim();
connBuilder.LoadBalanceTimeout = 60;
connBuilder.MinPoolSize = 0;
connBuilder.MaxPoolSize = 50;
int rows = 0;
using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString))
{
//System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString);
System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = txtSql.Text.Trim();
cmd.CommandTimeout = 300;
//cmd.ResetCommandTimeout();
conn.Open();
using (System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
object[] objs = new object[500];
dr.GetValues(objs);
rows++;
}
}
return(rows);
}
}
protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
using (OracleConnection conn = new OracleConnection(DBHelper.ConnectionString))
{
string reason_id = GV.DataKeys[e.RowIndex].Values[0].ToString();
string reason_desc = ((TextBox)GV.Rows[e.RowIndex].FindControl("TxtDesc")).Text;
string active = ((CheckBox)(GV.Rows[e.RowIndex].FindControl("ChkActive"))).Checked == true ? "1" : "0";
string sqlupdate = "update jp_lack_reason set reason_desc = '" + reason_desc + "',is_valid='" + active + "' where reason_id = '" + reason_id + "' ";
OracleCommand updatecomm = new OracleCommand(sqlupdate, conn);
try
{
conn.Open();
updatecomm.ExecuteNonQuery();
GV.EditIndex = -1;
GVDataBind();
}
catch (Exception ex)
{
conn.Close();
Response.Write("<script language=javascript>alert('" + ex.Message + "')</script>");
}
finally
{
updatecomm.Dispose();
conn.Dispose();
conn.Close();
}
}
}
// returns a Open connection
public override void GetConnection ()
{
string connectionString = null;
try {
connectionString = ConfigClass.GetElement (configDoc, "database", "connectionString");
} catch (XPathException e) {
Console.WriteLine ("Error reading the config file !!");
Console.WriteLine (e.Message);
return;
}
con = new OracleConnection (connectionString);
try {
con.Open ();
} catch (OracleException e) {
Console.WriteLine ("Cannot establish connection with the database");
Console.WriteLine ("Probably the database is down");
con = null;
} catch (InvalidOperationException e) {
Console.WriteLine ("Cannot open connection");
Console.WriteLine ("Probably the connection is already open");
con = null;
} catch (Exception e) {
Console.WriteLine ("Cannot open connection");
con = null;
}
}
private void bt_edit_Click(object sender, EventArgs e)
{
query = txb_sql1.Text;
string comando = "Create or replace view \"" + cmb_view1.GetItemText(cmb_view1.SelectedItem) + "\" as " + txb_sql1.Text;
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
OracleCommand cmd = new OracleCommand(comando, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Se ha Editado el View " + "\"" + txt_nomview.Text + "\"", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
Ver_Views();
show_viewtbx.Text = txt_nomview.Text;
tabControl1.SelectedTab = this.tabPage4;
}
catch (Exception ex)
{
MessageBox.Show("Error en la Sentencia o tiene el Siguiente Error: \n" + ex.Message.ToString(), "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// Form1.conn.Close();
System.Drawing.Color clr;
clr = System.Drawing.Color.Red;
label12.ForeColor = clr;
}
}
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;
}
private void button1_Click(object sender, EventArgs e)
{
//||
if (nomseq == "")
{
MessageBox.Show("NOMBRE DE SECUENCIA INVALIDO" + nomseq, "PROYECTO TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
string query = "create sequence\"" + nomseq + "\"" + " start with " + empieza_con + " maxvalue " + valor_max + " minvalue " + valor_min +"\n"+ cache +"\n"+ cycle +"\n"+ order;
// string query = nomseq +" "+ empieza_con +" "+ valor_max +" "+ valor_min +" "+ aumenta_por +" "+ cache +" "+ cycle+" " + order;
MessageBox.Show(query);
OracleConnection con = new OracleConnection("Data Source= XE; User Id=" + Form1.user + "; Password="******";");
con.Open();
OracleCommand cmd = new OracleCommand(query, con);
try
{
cmd.ExecuteNonQuery();
MessageBox.Show("Se ha Creado la Secuencia "+nomseq,"PROYECTO TDB1",MessageBoxButtons.OK,MessageBoxIcon.Information);
cmb_nomsec.Items.Clear();
cmb_nomsec2.Items.Clear();
CARGAR_SECUENCIAS();
}catch(Exception err){
MessageBox.Show("ERROR: " + err.Message.ToString(), "PROYECTO TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
/// <summary>
/// 将生成的材料信息更新到数据库中
/// </summary>
/// <param name="sql"></param>
/// <param name="excelinfo"></param>
public static void UpdateExcelInfo(string sql,byte[] excelinfo)
{
try
{
byte[] file = excelinfo;
using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
OracleParameter op = new OracleParameter("dfd", OracleType.Blob);
op.Value = file;
if (file.Length == 0)
{
MessageBox.Show("插入信息表不能为空！", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
return;
}
else
{
cmd.Parameters.Add(op);
cmd.ExecuteNonQuery();
}
}
conn.Close();
}
}
catch (IOException ee)
{
MessageBox.Show(ee.Message.ToString());
return;
}
}
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; }
}
public A()
{
oracon = new OracleConnection("server = 127.0.0.1/orcx; user id = qzdata; password = xie51");
oracon2 = new OracleConnection("server = 10.5.67.11/pdbqz; user id = qzdata; password = qz9401tw");
wordapp = new word.Application();
worddoc = new word.Document();
worddoc = wordapp.Documents.Add();
worddoc.SpellingChecked = false;
worddoc.ShowSpellingErrors = false;
// wordapp.Visible = true;
ta.wordapp = wordapp;
ta.worddoc = worddoc;
if (IS_YEAR)
{
datestr = dsf.GetDateStr(the_year_begin_int, the_month_begin_int, the_year_end_int, the_month_end_int);
}
else
{
datestr = dsf.GetDateStr(the_date);
}
// datestr_abid = "(" + datestr + "and a.ab_id >=1 and a.ab_id <= 7)";
datestr_abid = "(" + datestr + "and" + abidstr + ")";
oracon2.Open();
orahlper = new OraHelper(oracon2);
orahlper.feedback = true;
the_month_begin = new DateTime(the_date.Year, the_date.Month, 1, 0, 0, 0);
the_month_end = the_month_begin.AddMonths(1).AddSeconds(-1);
}
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 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;
}
}
/// <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;
}
}
/// <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;
}
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OracleCommand to be prepared</param>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
command.CommandTimeout = 200000;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
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 LoanList GetList(int applicantId)
{
LoanList loans = new LoanList();
using(OracleConnection oraDbConn =
new OracleConnection(
ConnStringFactory.getConnString(
ConnStringFactory.ConnStringType.Oracle))){
oraDbConn.Open();
using(OracleCommand getLoansByAppIdCommand = new OracleCommand()){
getLoansByAppIdCommand.CommandType = CommandType.StoredProcedure;
getLoansByAppIdCommand.CommandText = "LoansPKG.getLoansByAppId";
getLoansByAppIdCommand.Connection = oraDbConn;
getLoansByAppIdCommand.Parameters.AddWithValue("AppId", applicantId);
OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
outputCursor.Direction = ParameterDirection.Output;
getLoansByAppIdCommand.Parameters.Add(outputCursor);
using (OracleDataReader loanListReader =
getLoansByAppIdCommand.ExecuteReader()) {
while(loanListReader.Read()){
loans.Add(FillDataRecord(loanListReader));
}
}
}
}
return loans;
}
public ArrayList getDataORA2(String str)
{
try
{
using (System.Data.OracleClient.OracleConnection con_ora = new System.Data.OracleClient.OracleConnection(strConnectionORA))
{
con_ora.Open();
using (System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(str, con_ora))
{
using (OracleDataReader reader = command.ExecuteReader())
{
ArrayList list = new ArrayList();
while (reader.Read())
{
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
list.Add(values);
}
return(list);
}
}
}
}
catch (Exception e)
{
// strError = e.Message.ToString();
// MessageBox.Show(strError);
return(null);
}
}
private void bt_edit_Click(object sender, EventArgs e)
{
query = txb_sql1.Text;
string comando = "Create or replace view \"" + cmb_view1.GetItemText(cmb_view1.SelectedItem) + "\" as " + txb_sql1.Text;
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
OracleCommand cmd = new OracleCommand(comando, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Se ha Editado el View " + "\"" + txt_nomview.Text + "\"", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
Ver_Views();
show_viewtbx.Text = txt_nomview.Text;
tabControl1.SelectedTab = this.tabPage4;
}
catch (Exception ex)
{
MessageBox.Show("Error en la Sentencia o tiene el Siguiente Error: \n" + ex.Message.ToString(), "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// Form1.conn.Close();
System.Drawing.Color clr;
clr = System.Drawing.Color.Red;
label12.ForeColor = clr;
}
}
/// <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();
}
}
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();
}
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 { }
}
private void aceptar_login_Click(object sender, EventArgs e)
{
try
{
user = username_login.Text;
pass = password_login.Text;
string connectionString = "Data Source=XE;User Id=" + user + ";Password="******";";
conn = new OracleConnection(connectionString);
conn.Open();
cone = true;
MessageBox.Show("Conexion Establecida con Exito", "Proyecto TDB1", MessageBoxButtons.OK,MessageBoxIcon.Asterisk);
if (cone = true)
{
Form2 Forma = new Form2();
this.Hide();
Forma.ShowDialog();
}
else {
}
}
catch (Exception err)
{
MessageBox.Show("Conexion No Establecida || Usuario o Contraseña Denegada" + err.Message, "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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();
}
}
public int ExecSql(string[] sqls)
{
using (OracleConnection conn = new OracleConnection(ConnString))
{
OracleTransaction st;
conn.Open();
st = conn.BeginTransaction();
try
{
foreach (string sql in sqls)
{
OracleCommand scc = new OracleCommand(sql, conn);
scc.Transaction = st;
scc.ExecuteNonQuery();
}
st.Commit();
return 0;
}
catch
{
st.Rollback();
return -1;
}
}
}
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);
}
}
public static OracleConnection Connection()
{
//string BMSdb = "Data Source=192.168.2.11:1521/XE; User ID=BMS;Password=BMS;";
//string BMSdb = "Data Source=192.168.2.11:1521/XE; User ID=BMS_N;Password=ccl123;";
//string BMSdb = "Data Source=192.168.2.6:1521/XE; User ID=BMS_N;Password=ccl123;";
string BMSdb = System.Configuration.ConfigurationManager.ConnectionStrings["BMSDbContext1"].ConnectionString;
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(BMSdb);
conn.Open();
return(conn);
}
//This rountine writes log entry to web_log table in vitap database
public static void WriteWebLog(string appName, string allProcess, string clientIP, string userID, System.Data.OracleClient.OracleConnection conVITAP)
{
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = Queries.newWebLog(appName, allProcess, clientIP, userID);
cmdVITAP.ExecuteNonQuery();
}
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 static void Connect()
{
string oradb = "Data Source=192.168.2.8:1522/XE; User ID=BMS;Password=BMS;";
System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(oradb); // C#
try
{
conn.Open();
}
catch
{
}
}
} //end of this method
public static bool IsPassowrdExpired(string loginID, System.Data.OracleClient.OracleConnection conVITAP)
{
bool valid = false;
System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand();
if (conVITAP.State != ConnectionState.Open)
{
conVITAP.Open();
}
cmdVITAP.Connection = conVITAP;
cmdVITAP.CommandText = "";
System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader();
return(valid);
}
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);
}
private void btnConnect_Click(object sender, System.EventArgs e)
{
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception Caught");
}
}
}
private static void prepareCommand(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText)
{
try
{
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = commandType;
}
catch (Exception ex)
{
// Handle the exception in UI level only
throw ex;
}
}
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);
}
}
private void button1_Click(object sender, EventArgs e)
{
MessageBox.Show(cmb_elim.GetItemText(cmb_elim.SelectedItem));
string comando = "drop VIEW \"" + cmb_elim.GetItemText(cmb_elim.SelectedItem) + "\"";
OracleConnection conn = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
/*drop VIEW "BOAT_VIEW"*/
OracleCommand cmd = new OracleCommand(comando, conn);
try{
conn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("El View " + cmb_elim.GetItemText(cmb_elim.SelectedItem) + " ha sido Eliminado", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
Carga_Views();
}catch (Exception er) {
MessageBox.Show(er.Message);
}
}
/// <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);
}
private void button6_Click(object sender, EventArgs e)
{
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
string query = "drop INDEX \"" + cmb_indice.GetItemText(cmb_indice.SelectedItem) + "\"";
MessageBox.Show(query);
OracleCommand cmd = new OracleCommand(query, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("El Indice " + cmb_indice.GetItemText(cmb_indice.SelectedItem) + " ha sido Eliminado!", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
txtsquery.Text = query;
}
catch (Exception err)
{
MessageBox.Show("ERROR: \n" + err.Message.ToString(), "Proyecto TBD1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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();
}
}
/// <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);
}
/// <summary>
/// 得到与数据库的连接
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public OracleConnection ConnectOracle()
{
string str = GetSetting();
wait.lblTip.Text = "正在连接数据库.....";
System.Windows.Forms.Application.DoEvents();
wait.Refresh();
// str = Neusoft.HisDecrypt.Decrypt(str);
OracleConnection con = null;
if (str != "")
{
try
{
con = new System.Data.OracleClient.OracleConnection(str);
con.Open();
}
catch (Exception ee)
{
try
{
wait.lblTip.Text = "正在进行第二次连接数据库.....";
System.Windows.Forms.Application.DoEvents();
wait.Refresh();
con = new OracleConnection(str);
con.Open();
}
catch (Exception f)
{
this.Err = "连接数据库失败 " + f.Message;
con = null;
}
}
}
return(con);
}
public override void Open()
{
con.Open();
}
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 void CargarQuery()
{
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 = txtCorreo.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);
tablaCarga.DataSource = dt;
tablaCarga.DataBind();
if (tablaCarga.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_asignandose");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreo.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);
tablaPendientes.DataSource = dt;
tablaPendientes.DataBind();
if (tablaPendientes.Rows.Count == 0)
{
lblMensaje.Visible = true;
// Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('No hay tareas pendientes');</script>");
}
else
{
lblMensaje.Visible = false;
}
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_sub_asignandose");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreo.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);
tablaPendientes2.DataSource = dt;
tablaPendientes2.DataBind();
if (tablaPendientes2.Rows.Count == 0)
{
lblMensaje_2.Visible = true;
// Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('No hay tareas pendientes');</script>");
}
else
{
lblMensaje_2.Visible = false;
}
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_sub_y_flujo");
comando3.Connection = ora2;
comando3.CommandType = System.Data.CommandType.StoredProcedure;
comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreo.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);
tablaCarga2.DataSource = dt;
tablaCarga2.DataBind();
if (tablaCarga2.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 void INDEX(string aut_pos)
{
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
if (aut_pos == "0")
{
MessageBox.Show("ENTRO CON NOT UNIQUE");
parametro = "create index \"" + txt_nomindex.Text + "\" on \"" + nom_tabla + "\"(";
if (col1 != null)
{
parametro += "\"" + col1 + "\"";
}
if (col2 != null)
{
parametro += "," + "\"" + col2 + "\"";
}
if (col3 != null)
{
parametro += "," + "\"" + col3 + "\"";
}
if (col4 != null)
{
parametro += "," + "\"" + col4 + "\"";
}
parametro += ")";
MessageBox.Show(parametro);
}
if (aut_pos == "1")
{
MessageBox.Show("ENTRO CON UNIQUE");
parametro = "create UNIQUE index \"" + txt_nomindex.Text + "\" on \"" + nom_tabla + "\"(";
if (col1 != null)
{
parametro += "\"" + col1 + "\"";
}
if (col2 != null)
{
parametro += "," + "\"" + col2 + "\"";
}
if (col3 != null)
{
parametro += "," + "\"" + col3 + "\"";
}
if (col4 != null)
{
parametro += "," + "\"" + col4 + "\"";
}
parametro += ")";
MessageBox.Show(parametro);
}
OracleCommand cmd = new OracleCommand(parametro, con);
try {
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("El Indice " + txt_nomindex.Text + " ha sido Creado!", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
}catch (Exception err) {
MessageBox.Show(err.Message.ToString(), "Proyecto TBD1", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
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;
}
public void SetUp ()
{
if (connection_string == null)
return;
connection = new OracleConnection (connection_string);
connection.Open ();
using (command = connection.CreateCommand ()) {
// create the tables
command.CommandText =
"create table oratest (id number(10), text varchar2(64),"
+ " text2 varchar2(64) )";
command.ExecuteNonQuery ();
command.CommandText =
"create table culture_test (id number(10), value1 float,"
+ " value2 number(20,10), value3 number (20,10))";
command.ExecuteNonQuery ();
command.CommandText =
"create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100),"
+ " value2 DATE)";
command.ExecuteNonQuery ();
command.CommandText =
"create or replace procedure params_pos_test (param1 in number,"
+ "param2 in number,param3 in number,result out number) as"
+ " begin result:=param3; end;";
command.ExecuteNonQuery ();
}
}
