C# OracleConnection.Close方法代码示例

        public static DBInternalMessages DBNonQuery(string conStr, string sqlstr)
        {
            OracleConnection _dbconnection = new OracleConnection(conStr);

            _dbconnection.Open();

            try
            {
                if (_dbconnection.State == ConnectionState.Open)
                {
                    OracleCommand _command = new OracleCommand(sqlstr, _dbconnection);
                    int r = _command.ExecuteNonQuery();
                    _dbconnection.Close();
                    if (r > 0)
                    {
                       return DBInternalMessages.DB_NonQuerySuccess;// "Success";
                    }
                    else
                    {
                        return  DBInternalMessages.DB_NonQueryFailed;
                    }
                }
                else
                {
                    return DBInternalMessages.DB_NOT_OPEN; //db not open
                }

            }
            catch (Exception ex)
            {
                _dbconnection.Close();
                LoggingHelper.WriteLog("AkkaTestServer", "DBNonQuery Ex:" + ex.Message);
                return DBInternalMessages.DB_Exception; //ex
            }
        }

    public static void reportIssue(string cust_id, string rep_name, string rep_desc)
    {
        try
        {
            using(OracleConnection connection=new OracleConnection(connection_string))
            {

                rep_id++;
                repIdCopy = rep_id;
                string raised_date = DateTime.Now.ToString("dd-MMM-yy");
                string provision_date = DateTime.Now.AddDays(7).ToString("dd-MMM-yy");
                connection.Open();
                string query = "insert into reportrepair values('" + cust_id + "'," + rep_id + ", '" + rep_name + "', '" + rep_desc + "','" + raised_date + "' , 'pending', '" + provision_date + "')";
                OracleCommand command = new OracleCommand(query, connection);

                int count = command.ExecuteNonQuery();

                if (count > 0)
                {
                    Console.WriteLine("Inserted");
                }
                else
                {
                    Console.WriteLine("Not Inserted");
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {

        }
    }

 public static string addbindCompanyHardware(string companyId, string hardwareId)
 {
     OracleConnection oracleConnection = new OracleConnection(ConfigurationManager.ConnectionStrings["Bazi"].ConnectionString);
     OracleCommand cmd = new OracleCommand();
     string query = "INSERT INTO IzdavaHardver(koid,hardid) values (:koid,:hardid)";
     cmd.Connection = oracleConnection;
     cmd.CommandText = query;
     cmd.Parameters.Add(new OracleParameter("koid", companyId));
     cmd.Parameters.Add(new OracleParameter("hardid", hardwareId));
     try
     {
         oracleConnection.Open();
         int effect = cmd.ExecuteNonQuery();
         if (effect > 0)
         {
             return "Врската е успешно додадена";
         }
     }
     catch (Exception e)
     {
         return e.Message;
     }
     finally
     {
         oracleConnection.Close();
     }
     return "проблем со базата";
 }

        public List<PaisDTO> getPais() 
        {
            List<PaisDTO> lstPais = new List<PaisDTO>();
            Parametros p = new Parametros();
            PaisDTO itemPais;

            using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
            {
                con.Open();

                OracleCommand cmd = con.CreateCommand();                
                cmd.CommandText = "select IDPAIS, NOMBREPAIS from pais";

                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        itemPais = new PaisDTO();
                        itemPais.idPais = reader.GetInt32(0);
                        itemPais.nombrePais = reader.GetString(1);
                        lstPais.Add(itemPais);
                    }
                }

                con.Close();
                con.Dispose();
            }

            return lstPais;
        }

        public Aluno_Cadastrar(int mat_instrut)
        {
            InitializeComponent();
            matricula_instrutor = mat_instrut;

            try
            {
                OracleConnection conn = new OracleConnection(oradb);
                conn.Open();

                OracleCommand oda = new OracleCommand("SELECT COUNT(matricula) FROM pessoa", conn);
                OracleDataReader reader = oda.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    int tot_matr = reader.GetInt32(0) + 1;
                    tbx_matricula.Text = Convert.ToString(tot_matr);
                }
                conn.Close();
            }
            catch (Exception ex) // detecta todos os erros
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }

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

        public List<DepartamentoDTO> getDepartamento(int idPais = -1)
        {
            List<DepartamentoDTO> lstDepar = new List<DepartamentoDTO>();
            Parametros p = new Parametros();
            DepartamentoDTO itemDep;

            using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
            {
                con.Open();

                OracleCommand cmd = con.CreateCommand();
                
                cmd.CommandText = (idPais != -1) ? "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento where IDPAIS = " + idPais.ToString() : "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento";

                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        itemDep = new DepartamentoDTO();
                        itemDep.idDepartamento = reader.GetInt32(0);
                        itemDep.idPais = reader.GetInt32(1);
                        itemDep.nombreDepartamento = reader.GetValue(2).ToString();
                        
                        lstDepar.Add(itemDep);
                    }
                }

                con.Close();
                con.Dispose();
            }

            return lstDepar;
        }

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

            SeriesId = int.Parse(SeriesIdField.Value);
            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                // Set up the accepting group command
                var acceptCommand = new OracleCommand("TICKETS_API.acceptRequest", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };
                acceptCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input);
                acceptCommand.Parameters.Add("p_GroupId", OracleDbType.Int64, long.Parse(((Button)sender).CommandArgument), ParameterDirection.Input);

                try
                {
                    // Execute the command
                    objConn.Open();
                    acceptCommand.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    failed = true;
                    Error.Text = "Could not accept the group. Try again later";
                    Error.Visible = true;
                }

                objConn.Close();
            }

            if (!failed)
                Response.Redirect("AcceptedGroup.aspx?Series=" + SeriesId);
        }

 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();
     }
 }

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

        private void LoginRandomEmployee()
        {
            DataTable employeeTable = new DataTable();
            Person employee = null;

            using (OracleConnection objConn = new OracleConnection(Global.ConnectionString))
            {
                OracleCommand objCmd = new OracleCommand("TICKETS_QUERIES.getRandomEmployee", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure };

                objCmd.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue);

                try
                {
                    objConn.Open();
                    OracleDataAdapter adapter = new OracleDataAdapter(objCmd);
                    adapter.Fill(employeeTable);
                    employee = Mapper.DynamicMap<IDataReader, List<Person>>(employeeTable.CreateDataReader()).FirstOrDefault();
                }
                catch (Exception)
                {
                    Response.Redirect("../Default.aspx");
                }

                objConn.Close();
            }
            if (employee != null)
            {
                Global.CurrentPerson = employee;
                Global.CurrentPerson.accountType = Account.Faculty;
            }
            else
                Response.Redirect("../Default.aspx");
        }

            /// <summary>
            /// Selects and retrieves values from the database 
            /// </summary>
            /// <param name="query">The selection statement</param>
            /// <returns>A DataTable with the retrieved values></returns>
            public static DataTable RetrieveQuery(string query)
            {
                if (Regex.IsMatch(query, @"-{2,}"))
                {
                    throw new SQLInjectionException();
                }

                using (OracleConnection c = new OracleConnection(@connectionstring))
                {
                    try
                    {
                        c.Open();
                        OracleCommand cmd = new OracleCommand(@query);
                        cmd.Connection = c;
                        try
                        {
                            OracleDataReader r = cmd.ExecuteReader();
                            DataTable result = new DataTable();
                            result.Load(r);
                            c.Close();
                            return result;
                        }
                        catch (OracleException e)
                        {
                            Console.Write(e.Message);
                            throw;
                        }
                    }
                    catch (OracleException e)
                    {
                        Console.Write(e.Message);
                        return new DataTable();
                    }
                }
            }

        public void AddNewBroker(Broker brokerToAdd)
        {
            // disconnected version
            string sqlStatement = "INSERT INTO brokers(broker_id, first_name, last_name) VALUES (:broker_id, :first_name, :last_name)";
            IDbConnection connection = new OracleConnection(connectionString);
            OracleCommand command = new OracleCommand(sqlStatement, (OracleConnection)connection);
            command.BindByName = true;
            IDbDataParameter param = new OracleParameter(":first_name", OracleDbType.Varchar2, 25);
            param.Value = brokerToAdd.firstName;
            command.Parameters.Add(param);
            param = new OracleParameter(":last_name", OracleDbType.Varchar2, 25);
            param.Value = brokerToAdd.lastName;
            command.Parameters.Add(param);
            param = new OracleParameter(":broker_id", OracleDbType.Int16, 50);
            param.Value = brokerToAdd.id;
            command.Parameters.Add(param);

            IDbDataAdapter da = new OracleDataAdapter();
            da.InsertCommand = command;

            DataRow newRow = dataSet.Tables[0].NewRow();
            newRow["broker_id"] = brokerToAdd.id;
            newRow["first_name"] = brokerToAdd.firstName;
            newRow["last_name"] = brokerToAdd.lastName;
            dataSet.Tables[0].Rows.Add(newRow);

            // it's not clear from this code but we could make many changes to
            // the dataset before submitting back to the database.
            connection.Open();
            da.Update(dataSet);
            connection.Close();
        }

    public static List<string> retEscalatedIssues(string custId)
    {
        using (OracleConnection connection = new OracleConnection(connection_string))
        {

            connection.Open();
            string query = "select report_name from reportrepair where customer_id LIKE :cust and status='escalated'";
            OracleCommand command = new OracleCommand(query, connection);
            command.Parameters.Add(":cust", OracleDbType.NVarchar2).Value = custId;
            List<string> listobj = new List<string>();

            OracleDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    listobj.Add(reader.GetValue(0).ToString());

                }
                connection.Close();
                return listobj;
            }

            catch (Exception ex)
            {

                List<string> listobj1 = new List<string>();
                listobj1.Add("error");
                return listobj1;
            }
        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            string strConn = "Data Source=orcl;User Id=chatroom;Password=chatroom;Integrated Security=no;";
            OracleConnection oleDbConnection = new OracleConnection(strConn);
            oleDbConnection.Open();
            string strSql;
            if (this.name.Text.ToString() == "")
            {
                strSql = "select * from scott.ChatRoomLog order by id desc";
            }
            else
            {
                strSql = "select * from scott.chatroomlog where name='" + this.name.Text.ToString() + "' order by id";
            }
            System.Data.OracleClient.OracleCommand oleDbCommand = new System.Data.OracleClient.OracleCommand();
            oleDbCommand.CommandText = strSql;
            oleDbCommand.Connection = oleDbConnection;
            System.Data.OracleClient.OracleDataAdapter oleDbDataAdapter = new System.Data.OracleClient.OracleDataAdapter(oleDbCommand);
            DataTable table = new DataTable();
            oleDbDataAdapter.Fill(table);
            oleDbConnection.Close();
            this.GridView1.DataSource = table.DefaultView;
            this.GridView1.DataBind();

        }
        catch
        {
        }
    }