//根据查询SQL,产生提供给报表生成需要的 或 JSON 数据，采用 Oracle 数据引擎, 根据RecordsetQuerySQL获取报表明细数据，根据ParameterQuerySQL获取报表参数数据
    protected static void DoGenEntireData(System.Web.UI.Page DataPage, string RecordsetQuerySQL, string ParameterQuerySQL, ResponseDataType DataType, bool IsJSON)
    {
        OracleConnection myConn = new OracleConnection(OracleConnStr);
        myConn.Open();

        OracleDataAdapter myda = new OracleDataAdapter(RecordsetQuerySQL, myConn);
        DataSet myds = new DataSet();
        myda.Fill(myds);

        OracleCommand mycmd = new OracleCommand(ParameterQuerySQL, myConn);
        OracleDataReader mydr = mycmd.ExecuteReader(CommandBehavior.CloseConnection);

        if (IsJSON)
        {
            string ParameterPart = JSONReportData.GenParameterText(mydr);
            JSONReportData.GenEntireData(DataPage, myds, ref ParameterPart, DataType);
        }
        else
        {
            string ParameterPart = XMLReportData.GenParameterText(mydr);
            XMLReportData.GenEntireData(DataPage, myds, ref ParameterPart, DataType);
        }

        myConn.Close();
    }

        /**
            Permite eliminar un cliente
        */
        public string deleteCustomer(string id)
        {
            string deleteCustomer = "DELETE FROM CUSTOMERS.CUSTOMER WHERE Id = :Id";

            OracleTransaction transaction;
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    deleteCustomerAddress(id, connection, transaction);

                    using (OracleCommand command = new OracleCommand(deleteCustomer, connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", id));

                        command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    throw new PlatformException(e.Message);
                }
            }

            return "OK";
        }

        // GET: Album
        //  [HttpPost]
        public List<AlbumModels> Album()
        {
            OracleConnection conn = new OracleConnection(oradb);

                conn.Open();
                string selectStr =   /* "insert into album(albumID,albumname,username) values('111','testalbum','KING')";*/
                                       "select * "
                                       + "from album ";
                    // " +(Session["username"] as string)+"

                OracleCommand cmd = new OracleCommand(selectStr, conn);
                OracleDataReader dr = cmd.ExecuteReader();
                var Models = new List<AlbumModels>();
                while (dr.Read())
                {

                    AlbumModels model = new AlbumModels();
                    model.AlbumName = dr["albumname"].ToString();
                    model.AlbumId = dr["albumid"].ToString();
                    //model.Username = Session["username"] as string ;
                    string selectStr1 = "select * from photo where albumid = " + model.AlbumId;
                    OracleCommand cmd1 = new OracleCommand(selectStr1, conn);
                    OracleDataReader dr1 = cmd1.ExecuteReader();
                    dr1.Read();
                    model.Firstphoto = dr1["photoaddress"].ToString();
                    Models.Add(model);
                    //Session.Add("currentAlbumName", albumName);
                    //Session.Add("currentAlbumId", albumId);         //点击时保存到session
                }
                dr.Close();
                int insertedLines = cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
                return Models;
        }

 public ReservaController()
 {
     string conn = ConfigurationManager.ConnectionStrings["OracleDbContext"].ConnectionString;
     OracleConnection db = new OracleConnection(conn);
     JaverianaReservasContext dbSql = new JaverianaReservasContext();
     this._repo = new ReservaRepository(db, dbSql);
 }

        protected void Button1_Click(object sender, EventArgs e)
        {
            {

                string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
                OracleConnection conn = new OracleConnection(CS);
                //string U_ID = Session["U_ID"].ToString();
                //string ADD_ID = Session["ADDId"].ToString();
                using (OracleCommand cmd = new OracleCommand("insert into APARTMENT (APP_ID,APP_TYPE,APP_SIZE,APP_DATE,PRICE,NO_OF_ROOMS,FEATURE,USER_ID,APP_ADD_ID) values (U_ID_SEQ.NEXTVAL,'" + TextBoxAType.Text + "','" + TextBoxASize.Text + "','" + TextBoxAdate.Text + "','" + TextBoxAprice.Text + "','" + TextBoxAroom.Text + "','" + TextBoxAfeature.Text + "','" + Session["U_ID"].ToString().Trim() + "','" + Session["ADDId"].ToString().Trim() + "')", conn))
                {

                    conn.Open();
                    //cmd.ExecuteNonQuery();

                    if (Convert.ToBoolean(cmd.ExecuteNonQuery()))
                    {

                        Label1.ForeColor = System.Drawing.Color.Green;
                        Label1.Text = "Data Saved In The Database";
                        Response.Redirect("~/Allapartments.aspx");
                    }
                    else
                    {
                        Label1.ForeColor = System.Drawing.Color.Red;
                        Label1.Text = "Data Cannot Saved In The Database";
                    }

                }

            }
        }

        public static int? SaveBinaryContent(int? id, string processName, string fieldName, byte[] content, OracleConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentNullException("connection");
            }

            using (var command = connection.CreateCommand())
            {
                command.Parameters.Add("p_Content", content == null ? DBNull.Value : (object)content);

                if (id == null || id.Value == 0)
                {
                    command.CommandText = string.Format(CultureInfo.InvariantCulture, @"INSERT INTO """"{0}"""" (""""ProcessName"""", """"FieldName"""", """"Content"""", """"IsRemoved"""") VALUES (:p_ProcessName, :p_FieldName, :p_Content, '0');  RETURNING """"Id"""" INTO :p_Id", OracleNamesTranslator.Translate(Constants.BinaryContentTableName));

                    var idParam = command.Parameters.Add("p_Id", OracleDbType.Int32, ParameterDirection.Output);
                    command.Parameters.Add("p_ProcessName", processName == null ? DBNull.Value : (object)processName); 
                    command.Parameters.Add("p_FieldName", fieldName == null ? DBNull.Value : (object)fieldName); 
                    command.ExecuteNonQuery();
                    id = Convert.ToInt32(idParam.Value, CultureInfo.InvariantCulture);
                }
                else
                {
                    command.Parameters.Add("p_Id", id.Value); 
                    command.CommandText = string.Format(CultureInfo.InvariantCulture, @"UPDATE """"{0}"""" SET """"Content"""" = :p_Content WHERE """"Id"""" = :p_Id", OracleNamesTranslator.Translate(Constants.BinaryContentTableName));
                    command.ExecuteNonQuery();
                }
            }

            return id;
        }

        /// <summary>  
        /// 执行数据库查询操作,返回受影响的行数  
        /// </summary>  
        /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
        /// <param name="commandParameters">命令参数集合</param>  
        /// <returns>当前查询操作影响的数据行数</returns>  
        public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand command = new OracleCommand();
            OracleConnection connection = new OracleConnection(connstr);
            int result = 0;

            try
            {
                PrepareCommand(command, connection, null, CommandType.Text, cmdText, commandParameters);
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch
            {
                throw;
            }
            finally
            {
                command.Dispose();
                connection.Close();
                connection.Dispose();
            }

            return result;
        }

 protected void baglanti_kur()
 {
     baglanti = new OracleConnection(constring);
     baglanti.Open();
     komut = new OracleCommand();
     komut.Connection = baglanti;
 }

 static void ExecuteDropCommand(OracleConnection connection, string statement)
 {
     using (var dropCmd = new OracleCommand(statement, connection))
     {
         dropCmd.ExecuteNonQuery();
     }
 }

 static void Main(string[] args)
 {
     try
     {
         string connstring =
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-002.cafe24.com)(PORT=1521))" +
        "(CONNECT_DATA=(SID=orcl)));User Id=gds;Password=1234;";
         using (OracleConnection conn = new OracleConnection(connstring))
         {
             conn.Open();
             Console.WriteLine("접속 성공");
             string sql = "select test from testdb";
             using (OracleCommand comm = new OracleCommand(sql, conn))
             {
                 using (OracleDataReader rdr = comm.ExecuteReader())
                 {
                     while (rdr.Read())
                     {
                         Console.WriteLine(rdr.GetString(0));
                         Console.ReadLine();
                     }
                 }
             }
         }
     }
     catch
     {
         Console.WriteLine("접속 실패");
     }
 }

        public void CheckPersonWithIdGreaterThan2AndLessThan4()
        {
            List<IPerson> persons = table.GetTable().Where(p => p.Id > 2 && p.Id < 4).ToList();

            using (OracleConnection conn = new OracleConnection(OracleUtility.ConnectionString))
            {
                conn.Open();

                using (IDataReader dr = conn.GetDataReader("select Id,Name from person where Id > :from_id and Id < :to_id",
                    new { from_id = 2, to_id = 4 }))
                {

                    int i = 0;

                    while (dr.Read())
                    {
                        Assert.AreEqual(dr.ConvertToInt32("Id"), persons[i].Id);
                        Assert.AreEqual(dr.ConvertToString("Name"), persons[i].Name);

                        ++i;
                    }

                    Assert.AreEqual(persons.Count, i);

                }
            }
        }

        protected void Submit_Click(object sender, EventArgs e)
        {
            bool failed = false;

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                var insertPersonSeason = new OracleCommand("TICKETS_API.insertPersonSeason", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                insertPersonSeason.Parameters.Add("p_SeasonId", OracleDbType.Int32, int.Parse(SeasonDropDown.SelectedValue), ParameterDirection.Input);
                insertPersonSeason.Parameters.Add("p_PersonId", OracleDbType.Int32, Global.CurrentPerson.person_id, ParameterDirection.Input);

                try
                {
                    objConn.Open();
                    insertPersonSeason.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    failed = true;
                }

                objConn.Close();
            }

            if (!failed)
                Response.Redirect("BuySeasonTickets.aspx?Success=1");
            else
            {
                Error.Text = "Could not buy season ticket, try again later.";
                Error.Visible = true;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Global.CurrentPerson == null)
                    Response.Redirect("Index.aspx");

                DataTable seasonsTable = new DataTable();
                List<Season> seasons = new List<Season>();

                using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
                {
                    // Set up the seasons command
                    var seasonsCommand = new OracleCommand("TICKETS_QUERIES.getSeasonsForPurchase", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                    seasonsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
                    seasonsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);

                    try
                    {
                        // Execute the queries and auto map the results to models
                        objConn.Open();
                        var seasonsAdapter = new OracleDataAdapter(seasonsCommand);
                        seasonsAdapter.Fill(seasonsTable);
                        seasons = Mapper.DynamicMap<IDataReader, List<Season>>(seasonsTable.CreateDataReader());
                    }
                    catch (Exception)
                    {
                        Response.Redirect("Index.aspx");
                    }

                    objConn.Close();
                }

                // Fill list dropdowns with data from the database
                if (seasons.Count > 0)
                {
                    var seasonsWithEvents = seasons.GroupBy(s => s.season_id).Select(season => new Season()
                    {
                        season_id = season.First().season_id,
                        name = season.First().name,
                        price = season.First().price,
                        ticket_count = season.First().ticket_count,
                        event_names = seasons.Where(ev => ev.season_id == season.First().season_id).Select(en => en.event_name).ToList()
                    }).ToList();

                    SeasonDropDown.DataTextField = "name";
                    SeasonDropDown.DataValueField = "season_id";
                    SeasonDropDown.DataSource = seasonsWithEvents;
                    SeasonDropDown.DataBind();

                    SeasonListView.DataSource = seasonsWithEvents;
                    SeasonListView.DataBind();
                }
                if (Request.QueryString["Success"] != null)
                {
                    Error.Text = "Successfully purchased season ticket!";
                    Error.Visible = true;
                }
            }
        }

		private static void PrintDebug(OracleConnection con)
		{
			var debug = new Dictionary<string, string>
			{
				{ "NLS_DATABASE_PARAMETERS",  "SELECT parameter, value FROM NLS_DATABASE_PARAMETERS where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET', 'NLS_RDBMS_VERSION') order by parameter asc"},
				{ "V$VERSION",  "SELECT banner, ' ' FROM V$VERSION order by banner asc"},
				{ "V$PARAMETER", "select name,value from v$parameter where upper(name)=upper('SEC_CASE_SENSITIVE_LOGON')" }
			};
			foreach (var e in debug)
			{
				Console.WriteLine("### {0} ###", e.Key);
				using (var cmd = con.CreateCommand())
				{
					cmd.CommandText = e.Value;
					cmd.CommandType = CommandType.Text;
					using (var reader = cmd.ExecuteReader())
					{
						while (reader.Read())
						{
							Console.WriteLine(@"{0,-24} = {1}", reader.GetString(0), reader.GetString(1));
						}
					}
				}
			}
		}

        private void button1_Click(object sender, EventArgs e)
        {
            string connection = "Data Source=ORCL; User Id=pharmacy; Password=pharmacy";
            OracleConnection InsertData = new OracleConnection(connection);
            InsertData.Open();

            OracleParameter empno = new OracleParameter();
            empno.OracleDbType = OracleDbType.Int16;
            empno.Value = Quantity.Text;

            OracleParameter deptno = new OracleParameter();
            deptno.OracleDbType = OracleDbType.Int16;
            deptno.Value = Price.Text;

            OracleCommand insertEmpno = new OracleCommand();
            insertEmpno.Connection = InsertData;
            insertEmpno.Parameters.Add(empno);
            insertEmpno.CommandText = "select ename from employees where empid = 101";

            insertEmpno.CommandType = CommandType.Text;

            OracleDataReader DataRead = insertEmpno.ExecuteReader();

            DataRead.Read();

            while (DataRead.Read())
            {
                listBox1.Items.Add(DataRead.GetString(1));
            }

            InsertData.Dispose();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            while (!backgroundWorker1.CancellationPending)
            {
                try
                {
                    using (var conn = new OracleConnection(textBox1.Text))
                    {
                        conn.Open();
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = "SELECT 'OK' FROM DUAL";
                            var result = (string) cmd.ExecuteScalar();
                            AppendText(string.Format("{0}: Sql executed OK", DateTime.Now));
                        }
                    }
                }
                catch (Exception ex)
                {
                    //MessageBox.Show(ex.ToString());
                    AppendText(string.Format("{0}: Error:{1}", DateTime.Now, ex.Message));
                }

                for (var i = 0; i < 100; i++)
                {
                    Thread.Sleep(30);
                    if (backgroundWorker1.CancellationPending) return; 
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            // LotNo.Clear();
               // Quantity.Clear();
               // Price.Clear();

            string _connstring = "Data Source=ORCL;User Id = scott;Password=tiger;";
             //           decimal _totalRecords;
            String name;
            try
            {
            OracleConnection _connObj = new OracleConnection(_connstring);
            _connObj.Open();
            OracleCommand _cmdObj = _connObj.CreateCommand();
            //            _cmdObj.CommandText = "SELECT ename from employees where empid = 101";
            _cmdObj.CommandText = "SELECT ename from emp";
             //           _totalRecords = (decimal)_cmdObj.ExecuteScalar();
            name = (String)_cmdObj.ExecuteScalar();
            MessageBox.Show("Total records:" + name);
            textBox1.AppendText("Name: " + name);
            _connObj.Close();
            _connObj.Dispose();
            _connObj = null;
            }
            catch (Exception ex)
            {
            MessageBox.Show(ex.ToString());
            }
        }

 public string[] GetSearchItems(string prefix)
 {
     List<string> items = new List<string>();
     using (OracleConnection conn = new OracleConnection())
     {
         conn.ConnectionString = ConfigurationManager
                 .ConnectionStrings["Bazi"].ConnectionString;
         using (OracleCommand cmd = new OracleCommand())
         {
             cmd.CommandText = "select ime,igraid from Igra where upper(ime) like upper(:SearchText)||'%'";
             cmd.Parameters.Add(new OracleParameter("SearchText", prefix));
             cmd.Connection = conn;
             conn.Open();
             using (OracleDataReader sdr = cmd.ExecuteReader())
             {
                 while (sdr.Read())
                 {
                     items.Add(string.Format("{0}`{1} Game", sdr.GetString(0), sdr.GetInt64(1)));
                 }
             }
             cmd.CommandText = "select IMEMODEL,HARDID from HARDVERPROIZVOD where upper(IMEMODEL) like upper(:SearchText)||'%'";
             using (OracleDataReader rdr = cmd.ExecuteReader())
             {
                 while (rdr.Read())
                 {
                     items.Add(string.Format("{0}`{1} Hardware", rdr.GetString(0), rdr.GetInt64(1)));
                 }
             }
             conn.Close();
         }
         return items.ToArray();
     }
 }

 public Person(
     OracleConnection oracleConn,
     Func<Owned<PersonLotFixMigrator>> personLotFixMigratorFactory)
 {
     this.oracleConn = oracleConn;
     this.personLotFixMigratorFactory = personLotFixMigratorFactory;
 }

        /// <summary>
        /// Handles the specified request.
        /// </summary>
        /// <param name="request">The request.</param>
        public void Handle(DeployRequestDto request)
        {
            //Get the connection string
            var sqlInputs = GetConnectionString(request);

            //get the scripts folder
            var directory = request.IntermediateDirectory;

            //get the files in the folder
            string[] fileEntries = Directory.GetFiles(directory);
            foreach (string fileName in fileEntries)
            {
                try
                {
                    string content = string.Format(File.ReadAllText(fileName));
                    using (var oracleConnection = new OracleConnection(sqlInputs.ConnectionString))
                    {
                        oracleConnection.Open();
                        var command = new OracleCommand();
                        var script = content.Replace("\t", " ");
                        script = script.Replace("\n", Environment.NewLine);
                        command.Connection = oracleConnection;
                        command.CommandText = script;
                        var result = command.ExecuteNonQuery();
                        Debug.Write(result);
                    }
                }
                catch (Exception ex)
                {
                    Debug.Write("Exception message: " + ex.Message);
                }
            }
        }