/// <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
}