/// <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);
}
protected void ingresarTarea_Click(object sender, EventArgs e)
{
try
{
lblidddl.Text = "";
//Para el Usuario
ora.Open();
//Llamar al Stored Procedure
Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora);
comando1.CommandType = System.Data.CommandType.StoredProcedure;
//Asignar variable de cursor
comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreo.Text;
comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output);
comando1.ExecuteNonQuery();
Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader();
int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString());
lblidddl.Text = id.ToString();
ora.Close();
//Para la Tarea
string tipo = ddlTipoTarea.SelectedItem.ToString();
lblidddl.Text = tipo;
int flujo = ddlFlujo.SelectedIndex + 1;
//FECHA
var date = fecha.SelectedDate;
ora2.Open();
System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2);
comando2.CommandType = System.Data.CommandType.StoredProcedure;
comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text;
comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.Text;
comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id;
comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4;
comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo;
comando2.Parameters.Add("P_FLUJO", OracleType.VarChar).Value = flujo;
comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString();
comando2.ExecuteNonQuery();
// ora2.Close();
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>");
txtNombre.Text = "";
txtDescripcion.Text = "";
// txtCorreo.Text = "";
}
catch (Exception ex)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>");
}
}
/// <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);
}
public static Oracle.DataAccess.Client.OracleDataReader ExecuteDataReader(string query)
{
Oracle.DataAccess.Client.OracleCommand cmd = null;
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
cmd = new OracleCommand(query, DbConnection);
cmd.CommandType = System.Data.CommandType.Text;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
return(null);
}
}
/// <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);
}
public static Oracle.DataAccess.Client.OracleDataReader ExecuteDataReader(string sprocName, OracleParameter[] sprocParams)
{
Oracle.DataAccess.Client.OracleCommand cmd = null;
Oracle.DataAccess.Client.OracleDataReader reader = null;
try
{
cmd = new OracleCommand(sprocName, DbConnection);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
if (sprocParams != null && sprocParams.Length > 0)
{
cmd.Parameters.AddRange(sprocParams);
}
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
LogToFileServer("Server", "ERR: " + ex.Message + " TRACE: " + ex.StackTrace);
return(null);
}
}
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();
}