/// <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;
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.Number;
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);
}
/// <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;
this.command.CommandType = System.Data.CommandType.Text;
this.command.Parameters.Clear();
this.command.CommandText = strSql + "";
try
{
this.TempReader1 = this.command.ExecuteReader();
this.TempReader1.FetchSize = this.command.RowSize * 10000;
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.Number;
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);
}
/// <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
command.CommandText = strSql + "";
command.CommandType = System.Data.CommandType.Text;
Oracle.DataAccess.Client.OracleDataReader reader;
// 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.Number;
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 { }
reader.Close();
return(byteData);
}
private void updateDataGrid()
{
Oracle.DataAccess.Client.OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT KUPACID,NAZIV,PIB FROM KUPACPRAVNOLICE";
cmd.CommandType = CommandType.Text;
Oracle.DataAccess.Client.OracleDataReader dr = cmd.ExecuteReader();
// DataTable dt = new DataTable();
// dt.Load(dr);
// myDataGrid.ItemsSource = dt.DefaultView;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("KUPACID");
dt.Columns.Add("NAZIV");
dt.Columns.Add("PIB");
ds.Tables.Add(dt);
OracleParameter valueToReturn = null;
while (dr.Read())
{
DataRow red = dt.NewRow();
red["KUPACID"] = dr.GetValue(0);
red["NAZIV"] = dr.GetString(1);
Pib mBroj = (Pib)dr.GetValue(2);
// Pib mm = new Pib();
// mm= (Pib)dr.GetValue(2);
// red["PIB"] = Convert.ToInt64(mm.Pibb);
red["PIB"] = Convert.ToInt64(mBroj.Pibb);
}
dr.Close();
}
