/// <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);
        }
示例#2
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);
            }
        }
示例#7
0
        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();
        }