C# OleDbDataReader.Read方法代码示例

 protected void Page_Load(object sender, EventArgs e)
 {
     mySelectCommand.CommandType = CommandType.Text;
     mySelectCommand.CommandText = "SELECT Name, FoodTypeID FROM FoodType ORDER BY Name DESC";
     myReader = mySelectCommand.ExecuteReader();
     bool notEoF;
     //read first row from database
     notEoF = myReader.Read();
     //read row by row until the last row
     if (ddlCategory.Items.Count == 0)
     {
         ddlCategory.Items.Add("All Categories");
         while (notEoF)
         {
             ddlCategory.Items.Add(myReader["Name"].ToString());
             ddlCategory.Items[ddlCategory.Items.Count - 1].Value = myReader["FoodTypeID"].ToString();
             notEoF = myReader.Read();
         }
     }
     myReader.Close();
     if(!Page.IsPostBack)
     {
         getDB();
     }
 }

 private void cbLevel_SelectedIndexChanged(object sender, EventArgs e)
 {
     try
     {
         lvRanking.Items.Clear();
         sqlStr = "SELECT TOP 10 * FROM rankinglogs WHERE levelnumber = " + cbLevel.Text.Substring(6).Trim() + "   ORDER BY Point";
         conn.Open();
         cmd = new OleDbCommand(sqlStr,conn);
         reader = cmd.ExecuteReader();
         int i = 1;
         while(reader.Read())
         {
             ListViewItem lvi = new ListViewItem(i+"");
             lvi.SubItems.Add(reader["Nickname"].ToString());
             lvi.SubItems.Add(reader["Point"].ToString());
             lvi.SubItems.Add(reader["TimeSave"].ToString());
             lvRanking.Items.Add(lvi);
             i++;
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     finally
     {
         try
         {
             conn.Close();
         }
         catch
         {
         }
     }
 }

        public void populaLista()
        {
            rsPlay = objBD.ExecutaSQL("select PLI_ID, PLI_TITULO, PLI_URL from PlayList where PLI_ATIVO = 1 order by PLI_DH_CADASTRO desc");
            if (rsPlay == null)
            {
                throw new Exception();
            }
            if (rsPlay.HasRows)
            {
                while (rsPlay.Read())
                {
                    if (aux == 1)
                    {
                        objVideo.InnerHtml += "<iframe width=\"480\" height=\"269\" src=\""+rsPlay["PLI_URL"].ToString()+"\" frameborder=\"0\" allowfullscreen></iframe>";
                    }

                    System.Web.UI.WebControls.ListItem R = new System.Web.UI.WebControls.ListItem();
                    R.Value = rsPlay["PLI_ID"].ToString();
                    R.Text = rsPlay["PLI_TITULO"].ToString();
                    slPlayList.Items.Add(R);
                    aux++;
                }
            }
            rsPlay.Close();
            rsPlay.Dispose();
        }

        public void Initialise()
        {
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=phonebook.accdb";
            OleDbConnection conn = new OleDbConnection(connectionString);

            string sql = "SELECT * FROM task ORDER BY id";

            OleDbCommand cmd = new OleDbCommand(sql, conn);

            try
            {
                conn.Open();

                reader = cmd.ExecuteReader();
                reader.Read();

                lblTask.Text = reader.GetString(1).ToString();
                txtTask.Text = reader.GetString(2).ToString();

                reader.Close();
                conn.Close();
            }
            catch
            {
                //MessageBox.Show(e.Message);
            }
        }

        protected void Button1_Click1(object sender, EventArgs e)
        {
            string strGroupName, strGroupContent;

            DateTime dtmCreateDate;

            if (txtGroupName.Text == "")
            {
                this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('请填写群组名称！')</script>");
                return;
            }

            strGroupName = txtGroupName.Text;
            strGroupContent = txtGroupName.Text;

            string strsql;
            myGroup = AccessDateHelper.ExecuteReader("select * from [Group] where UserID=" + Convert.ToInt16(myUserID) + "");
            if (myGroup.Read())
            {
                strsql = "update [Group] set GroupName='" + strGroupName + "',GroupContent='" + strGroupContent + "' where GroupID=" + Convert.ToInt16(myGroup["GroupID"]) + "";
            }
            else
            {
                strsql = "insert into [Group] (GroupName,GroupContent,UserID) values ('" + strGroupName + "','" + strGroupContent + "',"+Convert.ToInt16(myUserID)+")";
            }

            if (AccessDateHelper.ExecuteNonQuery(strsql) > 0)
            {
                this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('群组更改成功！');try{ window.opener.RefreshForm() }catch(e){};</script>");
            }
            else
            {
                this.RegisterStartupScript("newWindow", "<script language='javascript'>alert('新建试题失败！')</script>");
            }
        }

 private void actualizador()
 {
     Conector();
     comando.CommandText = "select * from t_articulos";
     Lector = comando.ExecuteReader();
     int registros = 0;
              while (Lector.Read() == true)
     {
         DatosCosa[registros]=new Articulo();
         //Cosa = new Articulo();
         DatosCosa[registros].CodigoArti = Convert.ToInt32(Lector["id_articulo"]);
         DatosCosa[registros].NombreArti = Lector["n_articulo"].ToString();
         DatosCosa[registros].Precio = Convert.ToDouble(Lector["precio"]);
        // DatosCosa[registros] = Cosa;
         registros++;
     }
     Lector.Close();
     comando.CommandText = "select * from t_clientes";
     Lector = comando.ExecuteReader();
     registros = 0;
        // DatosFulano = new Clientes[50];
     while (Lector.Read() == true)
     {//Fulano=new Clientes();
         DatosFulano[registros]=new Clientes();
         DatosFulano[registros].Barrio=Convert.ToInt32(Lector["id_barrio"]);
         DatosFulano[registros].Calle=Lector["calle"].ToString();
         DatosFulano[registros].CodigoClle=Convert.ToInt32(Lector["id_cliente"]);
         DatosFulano[registros].NombreClie=Lector["n_cliente"].ToString();
         DatosFulano[registros].NumeroCa=Convert.ToInt32(Lector["calle_nro"]);
         //DatosFulano[registros]=Fulano;
         registros++;
     }
     Lector.Close();
     conexion.Close();
 }

        //Get all pictures from Database and save in a list
        public List<byte[]> photos()
        {
            byte[] aBytes = null;
            List<byte[]> pictures = new List<byte[]> {};

            for (int i = 1; i <= 5000; i++)
            {
                try
                {
                    int cont = 0;
                    string query = "SELECT image FROM Data WHERE counter = " + i + " AND image <> NULL;";
                    command = new OleDbCommand(query, connection);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        aBytes = (byte[])reader.GetValue(cont);
                        pictures.Add(aBytes);
                        cont++;
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    ////The error message is not displayed to continue with the process.
                    //MessageBox.Show("I'm so sorry, we cannot get the images.\n" + e);
                }
            }
            return pictures;
        }

    protected void Button1_Click(object sender, EventArgs e)
    {
        conn = new OleDbConnection("Provider=MSDAORA;Data Source=orcl;Persist Security Info=True;Password=db_mail;User ID=db_mail");
        string u_name, password;
        u_name = TextBox1.Text;
        password = TextBox2.Text;
        rec_mail rr = new rec_mail();
        string comp=rr.un_enc(u_name, password);
        //u_name = raw_u_name + password[2];
        //Response.Write(u_name);
        conn.Open();
        cmd=new OleDbCommand ("select enc_fun(user_id,password) from username where user_id='"+ u_name +"'",conn);
        dr = cmd.ExecuteReader();
        //dr.Read();
        //Response.Write(raw_u_name + dr["password"].ToString()[2]);
        if (dr.HasRows == false)
            Label2.Visible = true;
        else
        {
            dr.Read();

            if (dr[0].ToString() == comp)
            {
                Session["us_name"] = u_name;
                Response.Redirect("Mail_box.aspx");
            }
            else
                Label2.Visible = true;
        }
        dr.Close();
        conn.Close();
    }

        /// <summary>
        /// get all the items from the db
        /// </summary>
        public void ConnectToAccess()
        {
            conn = new OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= nuwaymenu.mdb;";

            try
            {
                conn.Open();

                cmd = new OleDbCommand(selectString, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    string st = reader["Item"].ToString() + "-" + reader["Description"] + "  $" + reader["Price"];
                    items.Add(st);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source");
            }
            finally
            {
                conn.Close();
            }
        }

        private void gen_btn_Click(object sender, EventArgs e)
        {
            string department = comboBox1.Text;
            sqlQuery = "SELECT * FROM EMPLOYEES WHERE DEPARTMENT LIKE '" + department + "' ORDER BY last_name asc";
            cmd.CommandText = sqlQuery;
            cmd.Connection = cn;
            dr = cmd.ExecuteReader();
            using (StreamWriter sw = new StreamWriter("C:/Users/Developer/Desktop/itpl project/itplProject/payrolls/payroll_"+ department +"_"+ DateTime.Now.ToString("mm-dd-yy")+".txt"))
            {

                sw.WriteLine("Report Generated at : " + DateTime.Now);
                sw.WriteLine(" Department, Last Name, First Name, Salary");
                sw.WriteLine("---------------------------------");
                while (dr.Read())
                {
                    sw.Write(dr["department"].ToString());
                    sw.Write(", "+ dr["last_name"].ToString());
                    sw.Write(", " + dr["first_name"].ToString());
                    sw.Write(", " + dr["salary"].ToString());
                    sw.WriteLine();
                }
                sw.WriteLine("---------------------------------");
                sw.Close();
                dr.Close();
                this.Close();
                MessageBox.Show("Check your File Directory!");
            }
        }

 /// <summary>
 ///     Get result of a Query in String form
 /// </summary>
 /// <param name="sqlStatment">The SQL Query</param>
 /// <returns>Result of Query in a String</returns>
 public String GetData(String sqlStatment)
 {
     string outputString = "null";
     try
     {
         OpenConection();
         cmd = new OleDbCommand(sqlStatment, conn);
         reader = cmd.ExecuteReader();
         while (reader.Read())
         {
             if (reader["Deleted"].ToString().Equals("False"))
             {
                 outputString = reader["Lec_Name"].ToString();
                 outputString += "$" + reader["Initials"].ToString();
                 outputString += "$" + reader["Email"].ToString();
                 outputString += "$" + reader["MaxHours"].ToString();
                 outputString += "$" + reader["MaxConsecHours"].ToString();
                 outputString += "$" + reader["MinSlotsPerDay"].ToString();
                 outputString += "$";
             }
             else
             {
                 outputString = "N/A";
             }
         }
         CloseConnection();
     }
     catch
     {
        // MessageBox.Show("Failed to get data from source");
     }
     return outputString;
 }

        public void ExcluirFoto()
        {
            rs = objBD.ExecutaSQL("select USU_ID, USU_FOTO from Usuario where USU_ID = '" + Session["usuID"] + "' ");

            if (rs == null)
            {
                throw new Exception();
            }
            if (rs.HasRows)
            {
                rs.Read();

                if (rs["USU_FOTO"].ToString().Length < 1)
                {
                    Response.Redirect("editar-foto-perfil");
                    Response.End();
                }
                else
                {
                    //Excluir a foto fisicamente
                    //File.Delete(Server.MapPath("~/upload/imagens/usuarios/" + rs["USU_FOTO"].ToString()));
                    //File.Delete(Server.MapPath("~/upload/imagens/usuarios/" + rs["USU_FOTO"].ToString().Replace("" + rs["USU_ID"] + "_", "")));

                    //Excluir a foto do BD;
                    objBD.ExecutaSQL("update Usuario set USU_FOTO = null where USU_ID = '" + Session["usuID"] + "' ");
                }
            }

            //Retornar para a Listagem
            Response.Redirect("editar-foto-perfil");
            Response.End();
        }

    public tbProdutividadeDTO BuscaPorID(tbProdutividadeDTO prodDTO)
    {
        String sSQL = MontaQuery(prodDTO);
        tbProdutividadeDTO prodAux = new tbProdutividadeDTO();
        //tbFabricanteDTO fabri = new tbFabricanteDTO();
        //tbFabricanteBLL fabriBLL = new tbFabricanteBLL();

        try
        {
            conexao = (OleDbConnection)CriaConexaoOleDb();
            drOleDb = cria_DataReader_OleDb(sSQL, conexao);
            while (drOleDb.Read())
            {
                prodAux.idProdutividade = (int)drOleDb["idProdutividade"];
                prodAux.dsLinguagem = (string)drOleDb["dsLinguagem"];
                prodAux.vlAlta = (float)(double)drOleDb["vlAlta"];
                prodAux.vlBaixa = (float)(double)drOleDb["vlBaixa"];
                prodAux.vlMedia = (float)(double)drOleDb["vlMedia"];

                //tbFabricanteDTO fabriAux = new tbFabricanteDTO();
                //fabriAux.idFabricante = (int)drOleDb["idFabricante"];
               //prodAux.fabricante = fabriBLL.BuscaPorID(fabriAux);
            }
        }
        catch (SystemException e)
        {
            erro = e.Message;
        }
        return prodAux;
    }

        public void PopulaTela()
        {
            rsListar = objBD.ExecutaSQL("EXEC site_psCriacaoDocumentadasPorID '" + Request["CDO_ID"] + "' ");

            if (rsListar == null)
            {
                throw new Exception();
            }
            if (rsListar.HasRows)
            {
                rsListar.Read();
                CDO_ID.Attributes.Add("value", Request["CDO_ID"].ToString());
                titu_criacoes.InnerHtml = objUtils.RemoveHTML(rsListar["CDO_TAREFA"].ToString());
                criador.InnerHtml = rsListar["ADM_NOME"].ToString();
                data.InnerHtml = rsListar["CDO_DATA"].ToString();
                box_descritivo.InnerHtml = rsListar["CDO_DESCRITIVO"].ToString();
                video_criacoes.Attributes.Add("src", "https://www.youtube.com/embed/" + rsListar["CDO_VIDEO"].ToString());
               // aRelato.Attributes.Add("href", "/enviar-relato.aspx?CDO_ID=" + Request["CDO_ID"] + "");
                relato_detalhe.InnerHtml = "<strong>" + rsListar["TOTAL_RELATOS"].ToString() + " Relatos Enviados</strong>";

                totalComentarios.InnerHtml = rsListar["TOTAL_COMENTARIOS"].ToString() + " Comentário";

                if (Convert.ToInt16(rsListar["TOTAL_COMENTARIOS"]) > 1)
                {
                    totalComentarios.InnerHtml += "s";
                }
            }
            else
            {
                Response.Redirect("/criacoes-documentadas");
            }

            rsListar.Close();
            rsListar.Dispose();
        }

 /// <summary>
 /// Connect to user meal database and finds record for current user. If current user does not exist, creates a generic record for user in db.
 /// </summary>
 private void ConnectToDb()
 {
     conn = new OleDbConnection();
     conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data source= userMeals.mdb;";
     try
     {
         conn.Open();
         cmd = new OleDbCommand(selectString, conn);
         reader = cmd.ExecuteReader();
         //Reads all records into two lists, one with users, the other with the user meals. Each index corresponds 1 to 1.
         while(reader.Read())
         {
             customMealProfiles.Add(reader["UserName"].ToString());
             customMeals.Add(new CustomMeals(reader["Meal 1"].ToString(), reader["Meal 2"].ToString(), 
                 reader["Meal 3"].ToString(), reader["Meal 4"].ToString(), reader["Meal 5"].ToString(), reader["Meal 6"].ToString(),
                 reader["Meal 7"].ToString(), reader["Meal 8"].ToString(), reader["Meal 9"].ToString(), reader["Meal 10"].ToString()));
         }
         //Is user does not exist, creates generic record for user in db.
         if(!customMealProfiles.Contains(CurrentUser))
         {
             string insertString = "INSERT INTO userMeals ([UserName],[Meal 1],[Meal 2],[Meal 3],[Meal 4],[Meal 5],[Meal 6],[Meal 7],[Meal 8],[Meal 9],[Meal 10]) VALUES (@User, @Meal1, @meal2, @meal3, @meal4, @meal5, @meal6, @meal7, @meal8, @meal9, @meal10)";
             cmd = new OleDbCommand(insertString, conn);
             cmd.Parameters.AddWithValue("@User", CurrentUser);
             cmd.Parameters.AddWithValue("@Meal1", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal2", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal3", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal4", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal5", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal6", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal7", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal8", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal9", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.Parameters.AddWithValue("@Meal10", "Combo #1 $5.29;Spicy Dog $1.99");
             cmd.ExecuteNonQuery();
         }
         else
         {
             int index = customMealProfiles.IndexOf(CurrentUser);
             meals = new List<string>();
             meals.Add(customMeals.ElementAt(index).Meal1);
             meals.Add(customMeals.ElementAt(index).Meal2);
             meals.Add(customMeals.ElementAt(index).Meal3);
             meals.Add(customMeals.ElementAt(index).Meal4);
             meals.Add(customMeals.ElementAt(index).Meal5);
             meals.Add(customMeals.ElementAt(index).Meal6);
             meals.Add(customMeals.ElementAt(index).Meal7);
             meals.Add(customMeals.ElementAt(index).Meal8);
             meals.Add(customMeals.ElementAt(index).Meal9);
             meals.Add(customMeals.ElementAt(index).Meal10);
         }
     }
     catch(Exception ex)
     {
         MessageBox.Show("Error encounters. Error message as follows: " + ex.Message);
     }
     finally
     {
         conn.Close();
     }
 }