C# MySqlCommand.ExecuteReader方法代码示例

        public static List<Trayecto> buscarOrigenes()
        {
            List<Trayecto> trayectos = new List<Trayecto>(); ;

            MySqlConnection con = conexionDB.ObtenerConexion();
            try
            {
                string sql = "select id, origen, destino, precio from trayecto group by origen";
                MySqlCommand cmd = new MySqlCommand(sql, con);
                MySqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    trayectos.Add(new Trayecto(dr.GetInt32(0), CiudadFacade.buscarPorId(dr.GetInt32(1)), CiudadFacade.buscarPorId(dr.GetInt32(2)), dr.GetInt32(3)));
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                con.Close();
            }

            return trayectos;
        }

        public Account LoadAccount(string username)
        {
            string SqlQuery = "SELECT * FROM `accounts` WHERE `username` = ?username";
                MySqlCommand SqlCommand = new MySqlCommand(SqlQuery, AccountDAOConnection);
                SqlCommand.Parameters.AddWithValue("?username", username);
                MySqlDataReader AccountReader = SqlCommand.ExecuteReader();

                Account acc = new Account();
                if (AccountReader.HasRows)
                {
                    while (AccountReader.Read())
                    {
                        acc.AccountId = AccountReader.GetInt32(0);
                        acc.Username = AccountReader.GetString(1);
                        acc.Password = AccountReader.GetString(2);
                        acc.Email = AccountReader.GetString(3);
                        acc.AccessLevel = (byte)AccountReader.GetInt32(4);
                        acc.Membership = (byte)AccountReader.GetInt32(5);
                        acc.isGM = AccountReader.GetBoolean(6);
                        acc.LastOnlineUtc = AccountReader.GetInt64(7);
                        acc.Coins = (int)AccountReader.GetInt32(8);
                        acc.Ip = AccountReader.GetString(9);
                        acc.UiSettings = ByteUtilities.StringToByteArray(AccountReader.GetString(10));

                    }
                }
                AccountReader.Close();
                return (acc.Username == "") ? null : acc;
        }

        public static void LoadItemsSets()
        {
            lock (DatabaseHandler.ConnectionLocker)
            {
                var sqlText = "SELECT * FROM datas_items_sets";
                var sqlCommand = new MySqlCommand(sqlText, DatabaseHandler.Connection);

                var sqlReader = sqlCommand.ExecuteReader();

                while (sqlReader.Read())
                {
                    var set = new Models.Items.SetModel();

                    set.ID = sqlReader.GetInt16("ID");
                    set.ParseBonus(sqlReader.GetString("bonus"));
                    set.ParseItems(sqlReader.GetString("items"));

                    lock(SetsList)
                        SetsList.Add(set);
                }

                sqlReader.Close();
            }

            Utilities.Loggers.StatusLogger.Write(string.Format("Loaded @'{0}' items [email protected] from the database !", SetsList.Count));
        }

        public static Trayecto buscarPorId(int id)
        {
            Trayecto trayecto = null;
            if (id > -1)
            {
                MySqlConnection con = conexionDB.ObtenerConexion();
                try
                {
                    string sql = "select id, origen, destino, precio from trayecto where id = " + id;
                    MySqlCommand cmd = new MySqlCommand(sql, con);
                    MySqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        trayecto = new Trayecto(dr.GetInt32(0), CiudadFacade.buscarPorId(dr.GetInt32(1)), CiudadFacade.buscarPorId(dr.GetInt32(2)), dr.GetInt32(3));
                    }
                    dr.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    trayecto = null;
                }
                finally
                {
                    con.Close();
                }
            }
            return trayecto;
        }

        public static bool validateLogin(UserLog ul)
        {
            DBConnector dbcon = new DBConnector();
            dbcon.openConnection();

            //try {

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "SELECT * FROM user WHERE username='" + ul.getUsername() + "' AND password=MD5('" + ul.getPassword() + "')";
            cmd.Connection = dbcon.connection;

            MySqlDataReader login = cmd.ExecuteReader();

            if (login.Read())
            {
                LoginSession.setSession(login.GetString("iduser"));

                //login.Close();
                dbcon.closeConnection();
                return true;
            }
            else
            {
                //login.Close();
                dbcon.closeConnection();
                return false;
            }
            //}
            //catch (MySqlException e){
            //int errorcode = e.Number;
            //return false;
            //}
        }

        public static Models.AccountsModel LoadAccount(int accountID)
        {
            DatabaseProvider.CheckConnection();

            lock (DatabaseProvider.ConnectionLocker)
            {
                var account = new Models.AccountsModel();

                var sqlText = "SELECT * FROM dyn_accounts WHERE [email protected]";
                var sqlCommand = new MySqlCommand(sqlText, DatabaseProvider.Connection);
                sqlCommand.Parameters.Add(new MySqlParameter("@id", accountID));

                var sqlReader = sqlCommand.ExecuteReader();

                if (sqlReader.Read())
                {
                    account.ID = sqlReader.GetInt16("id");
                    account.Username = sqlReader.GetString("username");
                    account.Password = sqlReader.GetString("password");
                    account.Pseudo = sqlReader.GetString("pseudo");
                    account.Communauty = sqlReader.GetInt16("communauty");
                    account.Level = sqlReader.GetInt16("gmLevel");
                    account.Question = sqlReader.GetString("question");
                    account.Answer = sqlReader.GetString("answer");
                    account.SubscriptionDate = sqlReader.GetDateTime("subscription");
                }

                sqlReader.Close();

                return account;
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            String cbid = TextBox1.Text;
            conn = new MySqlConnection(GetConnectionString());

            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand("Select Stock, Amount, DATE_FORMAT(Valid_From, '%d-%m-%Y'),DATE_FORMAT(Valid_To, '%d-%m-%Y') from Combopack where Combo_ID='" + cbid + "'", conn);
                MySqlDataReader dr = comm.ExecuteReader();
                dr.Read();
                TextBox3.Text = dr.GetValue(0).ToString();
                TextBox2.Text = dr.GetValue(1).ToString();
                TextBox4.Text = dr.GetValue(2).ToString();
                TextBox5.Text = dr.GetValue(3).ToString();
                dr.Close();

                comm.CommandText = "Select p.Product_ID, p.Company_Name, p.Product_Name, c.Quantity from Current_Store_Products p,Combopack_Schemes c where c.Product_ID=p.Product_ID and c.Combo_ID='" + cbid + "'";
                dr = comm.ExecuteReader();
                GridView1.DataSource = dr;
                GridView1.DataBind();
                dr.Close();

                comm.CommandText = "Select Sum(Quantity),Sum(Price) from Combopack_Schemes where Combo_ID='" + cbid + "'";
                dr = comm.ExecuteReader();
                dr.Read();
                Label9.Text = dr.GetValue(0).ToString();
                Label10.Text = dr.GetValue(1).ToString();
                dr.Close();

            }
            catch (Exception ex)
            { Response.Write("In Button2Click"+ex.Message); }
            finally { conn.Close(); }
        }

        public void AggregateTypesTest()
        {
            execSQL("CREATE TABLE foo (abigint bigint, aint int)");
              execSQL("INSERT INTO foo VALUES (1, 2)");
              execSQL("INSERT INTO foo VALUES (2, 3)");
              execSQL("INSERT INTO foo VALUES (3, 4)");
              execSQL("INSERT INTO foo VALUES (3, 5)");

              // Try a normal query
              string NORMAL_QRY = "SELECT abigint, aint FROM foo WHERE abigint = {0}";
              string qry = String.Format(NORMAL_QRY, 3);
              MySqlCommand cmd = new MySqlCommand(qry, conn);
              using (MySqlDataReader reader = cmd.ExecuteReader())
              {
            while (reader.Read())
            {
              reader.GetInt64(0);
              reader.GetInt32(1); // <--- aint... this succeeds
            }
              }

              cmd.CommandText = "SELECT abigint, max(aint) FROM foo GROUP BY abigint";
              using (MySqlDataReader reader = cmd.ExecuteReader())
              {
            while (reader.Read())
            {
              reader.GetInt64(0);
              reader.GetInt64(1); // <--- max(aint)... this fails
            }
              }
        }

 private void GetValues()
 {
     qry = "SELECT In_Stock,Hired_Out FROM books WHERE ISBN = @ISBN";
     using (MySqlConnection Connection = new MySqlConnection(MyConString))
     {
         try
         {
             MySqlCommand cmd = new MySqlCommand(qry, Connection);
             cmd.Parameters.Add(new MySqlParameter("@ISBN",(object)ISBN));
             MySqlDataReader reader;
             Connection.Open();
             reader = cmd.ExecuteReader();
             in_stock = int.Parse(reader["In_Stock"].ToString());
             hired_out = int.Parse(reader["Hired_Out"].ToString());
             reader.Close();
             cmd.Parameters.Clear();
             qry = "SELECT Books_purchased,Books_hired FROM clients WHERE FirstName = @FirstName AND LastName = @LastName";
             cmd.Parameters.Add(new MySqlParameter("@FirstName", (object)firstname));
             cmd.Parameters.Add(new MySqlParameter("@LastName", (object)lastname));
             reader = cmd.ExecuteReader();
             books_purchased = int.Parse(reader["Books_purchased"].ToString());
             books_hired = int.Parse(reader["Books_hired"].ToString());
         }
         catch (Exception ex)
         {
             MessageBox.Show(ex.Message, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
         }
         finally
         {
             Connection.Close();
         }
     }
 }

        public ReportData getPatientReport(int patientID)
        {
            ReportData reportData = new ReportData();

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                conn.Open();

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT * FROM patient where [email protected]";
                cmd.Prepare();
                cmd.Parameters.Add("@pID", MySqlDbType.Int32).Value = patientID;

                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        reportData.PatientData.PatientID = rdr.GetInt32("patientID");
                        reportData.PatientData.FirstName = rdr.GetString("firstName");
                        reportData.PatientData.LastName = rdr.GetString("lastName");
                        reportData.PatientData.DateAdmitted = rdr.GetDateTime("dateAdmitted");
                    }
                }

                //TODO new reach specific, fix later
                cmd.CommandText = "SELECT * FROM reach where [email protected]";
                cmd.Prepare();

                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        ExerciseData exerciseData = new ExerciseData();
                        exerciseData.ExerciseName = "reach";
                        exerciseData.PatientID = rdr.GetInt32("patientID");
                        exerciseData.EmployeeID = rdr.GetInt32("employeeID");
                        exerciseData.SessionID = rdr.GetInt32("sessionID");

                        //specific to reach
                        exerciseData.Hands = rdr.GetString("hands");
                        exerciseData.Angle = rdr.GetDouble("angle");
                        exerciseData.Date = rdr.GetDateTime("exerciseDate");
                        exerciseData.Time = rdr.GetDouble("time");

                        reportData.ExerciseDataList.Add(exerciseData);
                    }
                }

            }

            return reportData;

        }

        public static List<OgrenciDersleri> OgrenciDersGetir(Ogrenci ogrenci)
        {
            List<OgrenciDersleri> ogrenciDersleri = new List<OgrenciDersleri>();
            MySqlConnection connection=null;
            try
            {
                string connectionString="SERVER=localhost;DATABASE=OgrenciYonetimSistemi; UID=root;PASSWORD=hy050491;";
                 connection = new MySqlConnection(connectionString);
                connection.Open();
                string query = "SELECT * FROM OgrenciDersleri";

                MySqlCommand cmd = new MySqlCommand(query, connection);
                MySqlDataReader reader= cmd.ExecuteReader();

                while (reader.Read())
                {
                    OgrenciDersleri ogreciDers = new OgrenciDersleri();
                    ogreciDers.Id = Convert.ToInt32(reader["Id"]);

                    ogreciDers.Ogrenci = new Ogrenci();
                    ogreciDers.Ogrenci.Id = Convert.ToInt32(reader["Ogrenci"]);
                    ogreciDers.Ders = new Ders();

                    ogreciDers.Ders.Id = Convert.ToInt32(reader["Ders"]);

                    string studentQuery = "SELECT * FROM Ogrenci WHERE Id = '" + ogreciDers.Ogrenci.Id + "'";

                    MySqlCommand cmdStudent = new MySqlCommand(query, connection);
                    MySqlDataReader readerStudent = cmd.ExecuteReader();
                    while (readerStudent.Read())
                    {
                        ogreciDers.Ogrenci.Adi = readerStudent["Adi"].ToString();
                        ogreciDers.Ogrenci.Soyadi = readerStudent["Soyadi"].ToString();
                    }

                    ogrenciDersleri.Add(ogreciDers);
                }
                connection.Close();
                    DataTable table = new DataTable();
                    table.Load(reader);
                    for (int i = 0; i < table.Rows.Count; i++)
                {
                    string adi = table.Rows[i]["Id"].ToString();
                }
                //DersBilgileriDoldur(ogrenciDersleri);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                connection.Close();
            }
            return ogrenciDersleri;
        }

    public int AddNewCustomer()
    {
        int newCustomerID = 0;
        int existingCustomerResult = 0; //Used to check if this customer already exists in the database

        MySqlConnection conn = new MySqlConnection("Server=database2.cs.tamu.edu; Database=gcopley-car_dealership; Uid=gcopley ;Pwd=add431;");
        MySqlCommand findExistingCmd = new MySqlCommand("SELECT MAX(id) from customer where f_name ='" + m_txtCrtPendingCustFirstNameVal.Text + "' and l_name ='" + m_txtCrtPendingCustLastNameVal.Text + "'", conn);
        findExistingCmd.Connection.Open();

        //First check to see if the customer already exists in the database, if he does, return his ID, as there is no need to add him
        MySqlDataReader queryReader = findExistingCmd.ExecuteReader();
        while (queryReader.Read())
        {
            if (queryReader.FieldCount != 0)
            {
                existingCustomerResult = (queryReader.GetInt32(0));
            }
        }

        queryReader.Close();

        if (existingCustomerResult != 0)
        {
            queryReader.Dispose();
            return existingCustomerResult;
        }

        //If the customer doesn't exist, add him into the database, and if its a success, extract his id and return it.
        MySqlCommand addNewCustomerCmd = new MySqlCommand("INSERT INTO customer(f_name, l_name, addr_city, addr_state, addr_zip, phone, email) " +
                                               "VALUES('" + m_txtCrtPendingCustFirstNameVal.Text + "','" + m_txtCrtPendingCustLastNameVal.Text + "','" + m_txtCrtPendingCustCityVal.Text + "','" + m_txtCrtPendingCustStateVal.Text + "','" + m_txtCrtPendingCustZipVal.Text + "','" + m_txtCustomerPhoneNumber.Text + "','" + m_txtCustomerEmail.Text + ")", conn);

        addNewCustomerCmd.ExecuteNonQuery();

        //queryReader.();
        queryReader = findExistingCmd.ExecuteReader();
        while (queryReader.Read())
        {
            newCustomerID = (queryReader.GetInt32(0));
        }

        if (newCustomerID != 0)
        {
            queryReader.Close();
            queryReader.Dispose();
            return newCustomerID;
        }

        else
        {
            queryReader.Close();
            queryReader.Dispose();
            return 0;
        }
    }

        private void button1_Click(object sender, EventArgs e)
        {
            string strconn;
            strconn = "SERVER = " + server + "; DATABASE = " + DBtext + "; User ID =" + user + " ; password = " + password + ";Charset=utf8";

            MySqlConnection mys_conn = new MySqlConnection(strconn);
            mys_conn.Open();

            string sql = "select * from " + DBtable + " order by mdl_code asc";

            MySqlCommand mys_com = new MySqlCommand(sql,mys_conn);

            MySqlDataReader mys_read = mys_com.ExecuteReader();
            int listcount = 0;
            while (mys_read.Read())
            {
                if (mys_read.HasRows)
                {
                    listcount++;
                }
            }
            result = new string[listcount][];

            //釋放資源
            mys_read.Dispose();

            //重新載入
            mys_read = mys_com.ExecuteReader();
            int times = 0;
            while (mys_read.Read())
            {
                result[times] = new string[mys_read.FieldCount];
                string str="";
                for (int i = 0; i < mys_read.FieldCount; i++)
                {
                    result[times][i] = mys_read[i].ToString();
                    if (i == 0)
                    {
                        str += mys_read[i].ToString();
                        continue;
                    }
                    str += " , "+ mys_read[i].ToString();
                }
                times++;
                listBox1.Items.Add(str.ToString());
            }

            mys_read.Dispose();
            mys_read.Close();
            mys_com.Dispose();
            mys_conn.Dispose();
            mys_conn.Close();
        }

        //Vrakja lista na knigi
        public List<Book> SelectListBooks(string search,string language, string category)
        {
            List<Book> list = new List<Book>();

            using (MySqlConnection connection = new MySqlConnection())
            {
                connection.ConnectionString = connString;
                connection.Open();

                string query = "SELECT IDBook, Name, ImageSrc, Description, Date FROM Books, Categories, Tags, BelongsTo, Tagged";
                MySqlCommand command = new MySqlCommand(query, connection);
                MySqlDataReader dataReader = command.ExecuteReader();

                Dictionary<string, string> dictionary = new Dictionary<string, string>();
                List<Dictionary<string, string>> books = new List<Dictionary<string, string>>();
                List<Author> authors;

                while (dataReader.Read())
                {
                    dictionary.Add("IDBook", dataReader["IDBook"].ToString());
                    dictionary.Add("Name", dataReader["Name"].ToString());
                    dictionary.Add("ImageSrc", dataReader["ImageSrc"].ToString());
                    dictionary.Add("Description", dataReader["Description"].ToString());
                    dictionary.Add("Date", dataReader["YearPublished"].ToString());
                    books.Add(dictionary);
                }
                dataReader.Close();

                for (int i = 0; i < books.Count; i++)
                {
                    //Lista na avtori za sekoja kniga
                    authors = new List<Author>();
                    query = "SELECT a.Name, a.Surname,a.Country FROM Authors as a, Books as b, Wrote as w WHERE w.IDAuthor = a.IDAuthor AND w.IDBook =" + books[i]["IDBook"];
                    command.CommandText = query;
                    dataReader = command.ExecuteReader();

                    while (dataReader.Read())
                    {
                        Author a = new Author(dataReader["Name"].ToString(), dataReader["Surname"].ToString(), dataReader["Countrey"].ToString());
                        authors.Add(a);
                    }
                    dataReader.Close();

                    //Dodavanje na knigata vo listata
                    Book b = new Book(books[i]["Name"], authors, books[i]["ImageSrc"], books[i]["Description"], books[i]["Date"]);
                    list.Add(b);
                    }

                    connection.Close();
                    return list;
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection(GetConnectionString());
            String[] top = new String[5];
            String[] bottom = new String[5];
            MySqlDataReader dr;
            String query1 = "Select Product_Name,Company_Name,Sp4_Value,Sp5_Value,Sp6_Value,Sp7_Value,Sp8_Value from Master_Products where Product_ID in (Select Product_ID from Transactions where Bill_No in (Select Bill_No from Bills where MONTH(Bill_Date) = MONTH(NOW())) GROUP BY(Product_ID) ORDER BY SUM(Quantity) desc) ORDER BY(Product_ID) desc";
            String query2 = "Select Product_Name,Company_Name,Sp4_Value,Sp5_Value,Sp6_Value,Sp7_Value,Sp8_Value from Master_Products where Product_ID in (Select Product_ID from Transactions where Bill_No in (Select Bill_No from Bills where MONTH(Bill_Date) = MONTH(NOW())) GROUP BY(Product_ID) ORDER BY SUM(Quantity) ORDER BY (Product_ID))";
            try
            {
                conn.Open();
                MySqlCommand comm = new MySqlCommand(query1, conn);
                dr = comm.ExecuteReader();

                for (int i = 0; i < 5; i++)
                {
                    dr.Read();
                    top[i] = dr.GetValue(1).ToString() + " " + dr.GetValue(0).ToString() + " " + dr.GetValue(2).ToString() + " " + dr.GetValue(3).ToString() + " " + dr.GetValue(4).ToString() + " " + dr.GetValue(5).ToString() + " " + dr.GetValue(6).ToString();
                }
                dr.Close();
                comm.CommandText = query2;
                dr = comm.ExecuteReader();

                for (int i = 0; i < 5; i++)
                {
                    dr.Read();
                    bottom[i] = dr.GetValue(1).ToString() + " " + dr.GetValue(0).ToString() + " " + dr.GetValue(2).ToString() + " " + dr.GetValue(3).ToString() + " " + dr.GetValue(4).ToString() + " " + dr.GetValue(5).ToString() + " " + dr.GetValue(6).ToString();
                }
                dr.Close();

            }
            catch (Exception ex)
            { Response.Write(ex.Message); }
            finally
            { conn.Close(); }

            Label3.Text = top[0];
            Label5.Text = top[1];
            Label4.Text = top[2];
            Label6.Text = top[3];
            Label7.Text = top[4];

            Label8.Text = bottom[0];
            Label9.Text = bottom[1];
            Label10.Text = bottom[2];
            Label11.Text = bottom[3];
            Label12.Text = bottom[4];
        }