/// <summary> /// 输出blob /// </summary> /// <param name="strSql"></param> /// <returns></returns> public byte[] OutputBlob(string strSql) { //string block="SELECT image from test_image WHERE id = 100";//INTO :b // Set command to create Anonymous PL/SQL Block this.command.Connection = this.con as OracleConnection; if (this.command.Transaction == null) //没有事务,用全局事务 { this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction; } command.CommandText = strSql + ""; command.CommandType = System.Data.CommandType.Text; System.Data.OracleClient.OracleDataReader reader; command.Parameters.Clear(); // The anonymous PL/SQL block retrieves the images as an output parameter try { //cmd.ExecuteNonQuery(); reader = command.ExecuteReader(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(null); } catch (Exception ex) { this.Err = ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.WriteErr(); return(null); } reader.Read(); // Create a byte array byte[] byteData = new byte[0]; // fetch the value of Oracle parameter into the byte array //byteData = (byte[])(cmd.Parameters[0].Value); try { byteData = (byte[])(reader[0]); } catch (Exception ex) { this.Err = ex.Message; reader.Close(); return(null); } reader.Close(); return(byteData); }
//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
/// <summary> /// 执行sql /// </summary> /// <param name="strSql"></param> /// <param name="strDataSet"></param> /// <returns></returns> public int ExecQuery(string strSql, ref string strDataSet, string strXSLFileName) { this.command.Connection = this.con as OracleConnection; if (this.command.Transaction == null) //没有事务,用全局事务 { this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction; } this.command.CommandType = System.Data.CommandType.Text; this.command.Parameters.Clear(); this.command.CommandText = strSql + ""; try { this.TempReader1 = this.command.ExecuteReader(); XmlDocument doc = new XmlDocument(); XmlNode root; XmlElement node, row; doc.AppendChild(doc.CreateXmlDeclaration("1.0", "GB2312", "")); if (strXSLFileName != null && strXSLFileName != "") { string PI = "type='text/xsl' href='" + strXSLFileName + "'"; System.Xml.XmlProcessingInstruction xmlProcessingInstruction = doc.CreateProcessingInstruction("xml-stylesheet", PI); doc.AppendChild(xmlProcessingInstruction); } root = doc.CreateElement("Table"); doc.AppendChild(root); while (this.TempReader1.Read()) { row = doc.CreateElement("Row"); for (int i = 0; i < this.TempReader1.FieldCount; i++) { node = doc.CreateElement("Column"); node.SetAttribute("Name", this.TempReader1.GetName(i).ToString()); node.InnerText = this.TempReader1[i].ToString() + ""; row.AppendChild(node); } root.AppendChild(row); } strDataSet = doc.OuterXml; TempReader1.Close(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = "执行语句产生错误!" + ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.ErrCode = strSql; this.WriteErr(); return(-1); } WriteDebug("执行查询sql语句!" + strSql); return(0); }
}//end of method public static string AuthenticateLotusNotesUser(string firstName, string lastName, System.Data.OracleClient.OracleConnection App_Connection) { string Message = ""; string UserName1 = firstName.Trim().ToUpper() + " " + lastName.Trim().ToUpper(); string UserName2 = lastName.Trim().ToUpper() + ", " + firstName.Trim().ToUpper(); try { System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand(); CmdLotusMail.CommandText = "SELECT Notesname from maildir WHERE Notesname = '" + Utilities.SqlEncode(UserName1.ToUpper().Trim()) + "' or upper(Name) ='" + Utilities.SqlEncode(UserName2) + "'"; CmdLotusMail.Connection = App_Connection;// ConnLotusMail; if (App_Connection.State != System.Data.ConnectionState.Open) { CmdLotusMail.Connection.Open(); } System.Data.OracleClient.OracleDataReader NotesReader = CmdLotusMail.ExecuteReader(); if (NotesReader.Read()) { Message = NotesReader["Notesname"].ToString().Trim(); } NotesReader.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); }//end of method
/// <summary> /// 执行sql语句 重载 /// </summary> /// <param name="strSql"></param> /// <param name="strDataSet">返回DataSet xml</param> /// <returns></returns> public int ExecQuery(string strSql, ref string strDataSet) { this.command.Connection = this.con as OracleConnection; if (this.command.Transaction == null) //没有事务,用全局事务 { this.command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as OracleTransaction; } this.command.CommandType = System.Data.CommandType.Text; this.command.Parameters.Clear(); this.command.CommandText = strSql + ""; try { this.TempReader1 = this.command.ExecuteReader(); XmlDocument doc = new XmlDocument(); XmlNode root; XmlNode node, table; root = doc.CreateElement("DataSet"); doc.AppendChild(root); while (this.TempReader1.Read()) { table = doc.CreateElement("Table"); for (int i = 0; i < this.TempReader1.FieldCount; i++) { node = doc.CreateElement(this.TempReader1.GetName(i).ToString()); node.InnerText = this.TempReader1[i].ToString() + ""; table.AppendChild(node); } root.AppendChild(table); } strDataSet = doc.OuterXml; TempReader1.Close(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = "执行语句产生错误!" + ex.Message; this.ErrorException = ex.InnerException + "+ " + ex.Source; this.ErrCode = strSql; this.WriteErr(); return(-1); } WriteDebug("执行查询sql语句!" + strSql); return(0); }
public DataTable Consulta(string query) { DataTable dt = new DataTable(); if(conexao.State == ConnectionState.Open) { command = conexao.CreateCommand(); command.CommandText = query; reader = command.ExecuteReader(); dt.Load(reader); reader.Close(); return dt; } else { MessageBox.Show("Erro ao conectar-se"); return null; } }
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e) { int idx = DataList1.SelectedIndex; Label lbl = (Label)DataList1.Items[idx].FindControl("Label2"); System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString()); System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_images WHERE ID = '" + lbl.Text + "'", conn); conn.Open(); System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default); try { while (myReader.Read()) { System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE")); if (!myLob.IsNull) { string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME")); //Use buffer to transfer data byte[] b = new byte[myLob.Length]; //Read data from database myLob.Read(b, 0, (int)myLob.Length); Response.AddHeader("content-disposition", "attachment;filename=" + FN); Response.ContentType = "application/octectstream"; Response.BinaryWrite(b); Response.End(); } } } finally { myReader.Close(); conn.Close(); } }
public override void Close() { rdr.Close(); }
public void SimpleValue() { try { BeginCase("check simple value"); //prepare data base.PrepareDataForTesting(connectionString); con = new OracleConnection (connectionString); con.Open(); cmd = new OracleCommand("Select BirthDate From Employees where EmployeeID = 100", con); rdr = cmd.ExecuteReader(); rdr.Read(); DateTime dt = rdr.GetDateTime(0); //will be 1988-May-31 15:33:44 Compare(dt,new DateTime(1988,5,31,15,33,44,00)); } catch(Exception ex) { exp = ex; } finally { EndCase(exp); if (rdr != null && !rdr.IsClosed) rdr.Close(); exp = null; } }
public void MinDate() { BeginCase("Test Min date."); exp = null; string[] dateColumns; DateTime[] expectedValues; InitMinDates(out dateColumns, out expectedValues); try { con = new OracleConnection(ConnectedDataProvider.ConnectionString); cmd = new OracleCommand(); cmd.Connection = con; cmd.CommandText = BuildMinDateTimeSelectSql(dateColumns); con.Open(); rdr = cmd.ExecuteReader(); Compare(true, rdr.HasRows); bool b = rdr.Read(); for (int i=0; i<dateColumns.Length && i<expectedValues.Length; i++) { int j=-1; j = rdr.GetOrdinal(dateColumns[i]); //DateTime result = rdr.GetDateTime(j); object result = rdr.GetValue(j); Compare(result, expectedValues[i]); } } catch (Exception ex) { exp = ex; } finally { EndCase(exp); if (rdr != null && !rdr.IsClosed) { rdr.Close(); } if (con != null && con.State != ConnectionState.Closed) { con.Close(); } } }
public void Teste(string query) { Console.WriteLine("Iniciar teste..."); DataTable dt = new DataTable(); command = conexao.CreateCommand(); command.CommandText = query; reader = command.ExecuteReader(); dt.Load(reader); reader.Close(); foreach(DataRow row in dt.Rows) { foreach (DataColumn col in dt.Columns) { Console.WriteLine(col.ColumnName + " : " + row[col.ColumnName]); } Console.WriteLine(" "); } }
// pega usuarios do bd public void GetUsuarios() { command = conexao.CreateCommand(); // command.CommandText = "SELECT * FROM all_users"; // Usuarios command.CommandText = "SELECT * FROM all_users"; try { reader = command.ExecuteReader(); while (reader.Read()) { Console.Write(reader[0].ToString() + " | "); } reader.Close(); } catch(Exception e) { Console.WriteLine(e.Message); } }
public List<string> GetTabelas() { List<string> lista = new List<string>(); command = conexao.CreateCommand(); command.CommandText = "SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'"; reader = command.ExecuteReader(); Console.WriteLine(" \n Pegando Tabelas do banco..."); while (reader.Read()) { lista.Add(reader[0].ToString()); Console.Write(reader[0].ToString()+" | "); } Console.Write("\n"); reader.Close(); return lista; }
public void run() { Exception exp = null; OracleCommand cmd = new OracleCommand(); try { BeginCase("CommandType - default"); Assert.AreEqual(cmd.CommandType , CommandType.Text ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #if NotWorking try { BeginCase("CommandType - TableDirect"); cmd.CommandType = CommandType.TableDirect; Assert.AreEqual(cmd.CommandType , CommandType.TableDirect); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #endif try { BeginCase("CommandType - Text"); cmd.CommandType = CommandType.Text ; Assert.AreEqual(cmd.CommandType , CommandType.Text); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} #region ---- CommandType.Text using Parameters.Add ---- try { BeginCase("CommandType.Text using Parameters.Add"); cmd = new OracleCommand(); cmd.Connection = con; cmd.Transaction = tr; cmd.CommandType = CommandType.Text; cmd.CommandText = "call GH_REFCURSOR3(:IN_LASTNAME, :RCT_Employees)"; OracleParameter param1 = cmd.Parameters.Add("IN_LASTNAME", OracleType.VarChar,20); param1.Direction = ParameterDirection.Input; param1.Value = "Yavine"; cmd.Parameters.Add("RCT_Employees", OracleType.Cursor).Direction = ParameterDirection.Output; #if DAAB #if !JAVA if ((dbServerType == DataBaseServer.PostgreSQL)) { dr = PostgresOracleHelper.OLEDB4ODBCExecuteReader(cmd,true); } else #endif #endif { dr = cmd.ExecuteReader(); } if (dr.HasRows) { dr.Read(); Assert.AreEqual(dr.GetValue(0).ToString(),"1"); Assert.AreEqual(dr.GetString(1),"Yavine"); } else Assert.AreEqual("error","HasRows=0"); } catch(Exception ex) { exp = ex; } finally { if (dr != null)dr.Close(); if (con != null) {if (con.State == ConnectionState.Open) con.Close();} EndCase(exp); exp = null; } #endregion CommandTypeSP_Manual_InOutParameters(); #region ---- ORACLE CommandType.StoredProcedure using DeriveParameters ---- if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.Oracle) { try { BeginCase("ORACLE CommandType.StoredProcedure using DeriveParameters"); con.Open(); cmd = new OracleCommand(); cmd.Connection = con; cmd.Transaction = tr; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GH_REFCURSOR3"; OracleCommandBuilder.DeriveParameters(cmd); cmd.Parameters[0].Value = "Yavine"; //cmd.Parameters.RemoveAt(1); // the ORACLE DAAB trick is to remove the out parameter dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); Assert.AreEqual(dr.GetValue(0).ToString(),"1"); Assert.AreEqual(dr.GetString(1),"Yavine"); } else Assert.AreEqual("error","HasRows=0"); } catch(Exception ex) { exp = ex; } finally { if (dr != null)dr.Close(); if (con != null) {if (con.State == ConnectionState.Open) con.Close();} EndCase(exp); exp = null; } } #endregion #region CommandType.StoredProcedure in order to repreduce bug 4003 if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer) { exp = null; try { if (con.State == ConnectionState.Closed) con.Open(); BeginCase("Bug 4003"); OracleCommand cmd4003 = new OracleCommand("[mainsoft].[GH_DUMMY]",con); cmd4003.CommandType = CommandType.StoredProcedure; cmd4003.Parameters.Add("@EmployeeIDPrm","1"); cmd4003.ExecuteReader(); } catch (Exception ex) { exp=ex; } finally { if (con.State == ConnectionState.Open) con.Close(); EndCase(exp); } } #endregion }