/// <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;
}
}
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;
}
}
}
/// <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;
}
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();
}
}
}
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();
}
}
//2/09/2005 - Madan - web_users table in VITAP.world
//Input parameter UserName = First Name Initial, LastName
public static string IsValidWebUser(string UserName, string Password, System.Data.OracleClient.OracleConnection App_Connection)
{
string Message = "";
try {
System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand();
CmdLotusMail.CommandText = "SELECT password from web_users WHERE (externalservice ='T' or app_dfas_fl ='T') and upper(login_id) = '" + Utilities.SqlEncode(UserName.ToUpper().Trim()) + "'";
CmdLotusMail.Connection = App_Connection; //ConnLotusMail;
if (App_Connection.State != System.Data.ConnectionState.Open)
{
CmdLotusMail.Connection.Open();
}
System.Data.OracleClient.OracleDataReader myReader = CmdLotusMail.ExecuteReader();
if (myReader.Read())
{
Message = myReader["password"].ToString().Trim();
}
myReader.Close();
CmdLotusMail.Connection.Close();
}
catch (System.Exception ex) { // if exception occurs, make an entry in the server event log
EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message);
}
return(Message.Trim());
} //end of this method
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);
}
}
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;
}
}
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>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.OracleClient.OracleException E)
{
connection.Close();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
}
/// <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 DataAccess()
{
m_Connection = new OracleConnection();
m_Command = new OracleCommand();
//read connection string from xml file
//string str1 = "";
//XmlTextReader xmlR = new XmlTextReader(MapPath("mycompany.xml") "connString.xml");
//str1 = xmlR.GetAttribute("connstring");
//str1 = System.Configuration.ConfigurationManager.AppSettings.Get("connString");
// XmlDocument doc = new XmlDocument();
//doc.Load("~/App_Data/sample.xml");
//XmlNode root = doc.DocumentElement;
//str1 = root.SelectSingleNode("author").ChildNodes[0].Value;
//string str1 = BDGovPayroll.Properties.Settings.Default.connString;
string str1 = @"Data Source=(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL=TCP)(HOST=soft-server)(PORT=1521)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= orcl)));
User Id=librarry;Password=softech;";
//////// string str1 = @" Data Source = (DESCRIPTION =
//////// (ADDRESS_LIST =
//////// (ADDRESS =
//////// (PROTOCOL = TCP)(HOST = soft1)(PORT = 1521)))(CONNECT_DATA =
//////// (SERVER = DEDICATED)(SERVICE_NAME = XE)));
//////// User Id = payroll_db; Password = payroll;";
//m_Connection.ConnectionString = @"Data Source=soft1;User Id=payroll_db;Password=payroll;";
m_Connection.ConnectionString = str1;
m_Command.Connection = m_Connection;
}
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, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
DataSet ds = new DataSet();
//try
//{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
//}
//catch (System.Data.OracleClient.OracleException ex)
//{
// throw new Exception(ex.Message);
//}
//finally
//{
// if (connection.State != ConnectionState.Closed)
// {
// connection.Close();
// }
//}
return ds;
}
}
}
/// <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 button5_Click(object sender, EventArgs e)
{
MessageBox.Show("OTRO BOTON");
/*create index "BOAT_CTX1"
* on "BOAT" ("BOAT_NAME")
* indextype is ctxsys.context*/
OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";");
string query = "create index \"" + txt_nomindex.Text + "\" on \"" + txt_nomtabla.Text + "\"(\"" + col1 + "\")" + "indextype is ctxsys.context";
MessageBox.Show(query);
OracleCommand cmd = new OracleCommand(query, con);
try
{
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("El Indice " + txt_nomindex.Text + " ha sido Creado!", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information);
txt_sql.Text = query;
}
catch (Exception err)
{
MessageBox.Show("ERROR: \n" + err.Message.ToString(), "Proyecto TBD1", 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();
}
}
internal OracleTransaction (OracleConnection connection, IsolationLevel isolevel, OciTransactionHandle transaction)
{
this.connection = connection;
this.isolationLevel = isolevel;
this.transaction = transaction;
isOpen = true;
}
/// <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);
}
}
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);
}
}
/// <summary></summary>
/// 执行Sql数组语句查询，并将查询返回的结果作为一个数据读取器返回
///
///
/// <returns></returns>OracleDataReader
public OracleDataReader RetriveDataReader(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return(null);
}
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
using (oracleCommand = new OracleCommand(sql, oracleConnection))
{
try
{
OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
return(oracleDataReader);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
return(null);
}
}
}
}
/// <summary></summary>
/// 执行单Sql语句查询，并将查询返回的结果作为一个数据集返回
///
/// SELECT 语句
/// <returns></returns>数据集 DataSet
public DataSet RetriveDataSet(string sql)
{
if (sql == null || sql == string.Empty)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine("sql 为空");
#endif
return(null);
}
using (oracleConnection = this.GetOracleConnection())
{
if (oracleConnection == null)
{
return(null);
}
using (OracleDataAdapter da = new OracleDataAdapter(sql, oracleConnection))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds);
}
catch (Exception ex)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(ex.ToString());
#endif
}
return(ds);
}
}
}
/// <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);
}
}
}
/// <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>
/// 执行存储过程
/// </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();
}
}
// 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;
}
}
/// <summary>
/// Simplify the creation of a Oracle command object by allowing
/// a stored procedure and optional parameters to be provided
/// </summary>
/// <remarks>
/// e.g.:
/// OracleCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName");
/// </remarks>
/// <param name="connection">A valid OracleConnection object</param>
/// <param name="spName">The name of the stored procedure</param>
/// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param>
/// <returns>A valid OracleCommand object</returns>
public static OracleCommand CreateCommand(OracleConnection connection, string spName, params string[] sourceColumns)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );
// Create a OracleCommand
OracleCommand cmd = new OracleCommand( spName, connection );
cmd.CommandType = CommandType.StoredProcedure;
// If we receive parameter values, we need to figure out where they go
if ((sourceColumns != null) && (sourceColumns.Length > 0))
{
// Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection, spName);
// Assign the provided source columns to these parameters based on parameter order
for (int index=0; index < sourceColumns.Length; index++)
commandParameters[index].SourceColumn = sourceColumns[index];
// Attach the discovered parameters to the OracleCommand object
AttachParameters (cmd, commandParameters);
}
return cmd;
}
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 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;
}
}
}
/// <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);
}
}
}
}
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;
}
}
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 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);
}
}
public void run()
{
Exception exp = null;
OracleConnection con = null;
try
{
BeginCase("OracleConnection ctor");
con = new OracleConnection();
Compare(con != null ,true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("OracleConnection ctor connection string");
con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
Compare(con != null ,true);
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
try
{
BeginCase("OracleConnection check connection string");
Compare(con.ConnectionString ,MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString );
}
catch(Exception ex){exp = ex;}
finally{EndCase(exp); exp = null;}
}
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 DataSet ReadDataToDataSet(OracleConnection conn, String SQL, string diff)
{
OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);
DataSet testDataSet = new DataSet();
adapter.Fill(testDataSet);
return testDataSet;
}
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 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 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 Oracle(string connectid)
{
try
{
this.Connection = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_" + connectid]);
}
catch (OracleException ex)
{
throw ex;
}
}
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);
}
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 OracleConnection() : base()
{
_connection = new System.Data.OracleClient.OracleConnection();
_command = new OracleCommand();
_command.Connection = _connection;
// Attaching events.
_connection.StateChange += _StateChange;
// Configure default parameter for oracle.
setParam("usetnsnames", "True");
setParam("useconnectionpooling", "False");
setParam("privilege", "Normal");
setParam("serviceid", "");
}
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
{
}
}
public Oracle(Boolean Unicode)
{
try
{
string connstr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_Oracle"];
connstr = connstr.Replace("Unicode=True", "Unicode=False");
this.Connection = new OracleConnection(connstr);
}
catch (OracleException ex)
{
throw ex;
}
}
} //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 CryReport()
{
//
// TODO: 在此处添加构造函数逻辑
//
try
{
this.Connection = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_Report"]);
}
catch (OracleException ex)
{
throw ex;
}
}
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);
}
public DBMSSqlHelper(string ConnectStr)
{
mConn = new Connection(ConnectStr);
#region 从连接串中解析出来的datasource，作为当前类的关键字
string str = ConnectStr + ";";
string strFlag = "Data Source=";
if (str.IndexOf(strFlag) > -1)
{
str = str.Remove(0, str.IndexOf(strFlag) + strFlag.Length);
str = str.Substring(0, str.IndexOf(";"));
}
this.mDataSource = str;
//this.mDataSource = ConnectStr; //还是直接用连接串吧
#endregion
}
/// <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 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);
}
}
public Oracle(string connectstring__, Boolean if_use)
{
if (if_use == true)
{
connectstring = connectstring__;
}
else
{
connectstring = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString_Oracle"];
}
try
{
this.Connection = new OracleConnection(connectstring);
}
catch (OracleException ex)
{
throw ex;
}
}
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();
}
}
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);
}
}
/// <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 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 ();
}
}