C# MySqlCommand.Prepare方法代码示例

        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 ArrayList buscarPendentes(Emprestimo emprestimo)
        {
            ArrayList emprestimos = new ArrayList();

            MySqlCommand cmd;

            string sql = "SELECT * FROM " + TABELA
                    + " WHERE entregue = false;";

                // Associação do comando à conexão.
                cmd = new MySqlCommand(sql,
                    BancoDados.recuperarConexao());

                cmd.Prepare();

                // Execução da sentença SQL, com dados de retorno
                // associados a um objeto para posterior leitura.
                MySqlDataReader leitor = cmd.ExecuteReader();

            while (leitor.Read())
            {

                emprestimos.Add(
                    new Emprestimo(int.Parse(leitor["id"].ToString()), DateTime.Parse(leitor["dataemprestimo"].ToString()),
                        bool.Parse(leitor["entregue"].ToString()),int.Parse(leitor["fk_destinatario"].ToString()), int.Parse(leitor["fk_item"].ToString())));
            }

            leitor.Close();

            return emprestimos;
        }

        public void create()
        {
            db = new Database();

            if (officeVerification())
            {
                try
                {
                    officeQuery = new MySqlCommand();

                    officeQuery.Connection = db.Connection();

                    officeQuery.CommandText = "INSERT INTO office(officedescription) VALUES(@Description)";

                    officeQuery.Prepare();

                    officeQuery.Parameters.AddWithValue("@Description", officedescription);

                    officeQuery.ExecuteNonQuery();

                    MessageBox.Show("A Descrição Cargo Assinatura " + officedescription + " foi criada com sucesso.");
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Ocurreu um erro");
                    Console.WriteLine("Error: {0}", ex.ToString());
                }
                finally
                {
                    db.Close();
                }
            }
        }

        public void ProcessRequest(HttpContext context)
        {
            int count = context.Request.GetQueryStringInteger("queries", 1).Clamp(1, 500);
            World[] worlds = new World[count];
            Random random = new Random();

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

                using (MySqlCommand command = new MySqlCommand(DB_QUERY, connection))
                {
                    command.Prepare();
                    command.Parameters.Add("@id", MySqlDbType.Int32);

                    for (int i = 0; i < count; i++)
                    {
                        int id = random.Next(1, DB_ROWS);
                        command.Parameters["@id"].Value = id;

                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                worlds[i] = new World(id, reader.GetInt32("randomNumber"));
                            }
                        }
                    }
                }
            }

            JsonHelpers.WriteJson(worlds, context);
        }

        public int retrieveAlbumId()
        {
            DBConnect connection = new DBConnect();

            string query = "SELECT albumId FROM Album WHERE albumName LIKE @albumName;";

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, connection.OpenConnection());
                cmd.CommandText = query;
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@albumName", this.albumName);

                albumId = int.Parse(cmd.ExecuteScalar() + "");

                cmd.ExecuteNonQuery();

                connection.CloseConnection();

                return albumId;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static bool addResignationRecord()
        {
            DBConnector dbcon = new DBConnector();

            //try
            //{
            if (dbcon.openConnection())
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "INSERT INTO resignation (letter_submitted_date, resignation_date, employee_idemployee) VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd") + "', '" + DateTime.Now.ToString("yyyy-MM-dd") + "', " + Employee.employee_id + ")";
                cmd.Connection = dbcon.connection;
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                dbcon.closeConnection();
                return true;
            }
            else
            {
                dbcon.closeConnection();
                return false;
            }

            /*}
            catch (MySqlException e)
            {
                int errorcode = e.Number;
                dbcon.closeConnection();
                return false;
            }*/
        }

        public void BinaryAndVarBinaryParameters()
        {
            if (Version < new Version(5, 0)) return;

              execSQL("CREATE PROCEDURE spTest(OUT out1 BINARY(20), OUT out2 VARBINARY(20)) " +
              "BEGIN SET out1 = 'out1'; SET out2='out2'; END");

              MySqlCommand cmd = new MySqlCommand("spTest", conn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add("out1", MySqlDbType.Binary);
              cmd.Parameters[0].Direction = ParameterDirection.Output;
              cmd.Parameters.Add("out2", MySqlDbType.VarBinary);
              cmd.Parameters[1].Direction = ParameterDirection.Output;
              if (prepare) cmd.Prepare();
              cmd.ExecuteNonQuery();

              byte[] out1 = (byte[])cmd.Parameters[0].Value;
              Assert.AreEqual('o', out1[0]);
              Assert.AreEqual('u', out1[1]);
              Assert.AreEqual('t', out1[2]);
              Assert.AreEqual('1', out1[3]);

              out1 = (byte[])cmd.Parameters[1].Value;
              Assert.AreEqual('o', out1[0]);
              Assert.AreEqual('u', out1[1]);
              Assert.AreEqual('t', out1[2]);
              Assert.AreEqual('2', out1[3]);
        }

        private void btnEnvoyer_Click(object sender, EventArgs e)
        {
            MySqlCommand cmd;

            //Global.Connection.Open();
            try
            {
                if (comboBox1.Text == "Sélectionnez la nature de votre requête")
                {
                    MessageBox.Show("Veuillez renseigner la nature de votre requête", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }else if(rtbContenu.Text == "")
                {
                    MessageBox.Show("Veuillez écrire votre requête", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                } else {
                    cmd = new MySqlCommand("INSERT INTO requetecontact(idUtilisateur, contenu, dateContact, dateTraitement) VALUES(@idUtilisateur, @contenu, now(), '1000-01-01 00:00:00')", Global.Connection);
                    MySqlParameter pIdUtilisateur = new MySqlParameter("@idUtilisateur", MySqlDbType.Int16);
                    MySqlParameter pContenu = new MySqlParameter("@contenu", MySqlDbType.Text);
                    pIdUtilisateur.Value = Global.userId;
                    pContenu.Value = comboBox1.Text + " : " + rtbContenu.Text;
                    cmd.Parameters.Add(pIdUtilisateur);
                    cmd.Parameters.Add(pContenu);
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("Votre reqûete a bien été prise en compte, nos équipes sont au travail ! ", "Succès", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    rtbContenu.Text = "";
                    comboBox1.SelectedItem = "Sélectionnez la nature de votre requête";
                }
            } catch (MySqlException) {
                MessageBox.Show("Une erreur est survenue. Impossible de contiuer.", "Erreur", MessageBoxButtons.OK, MessageBoxIcon.Error);
                rd.Close();
                Global.Connection.Close();
            }
        }

        public void create()
        {
            db = new Database();

            if (vendorVerification())
            {
                try
                {
                    vendorQuery = new MySqlCommand();

                    vendorQuery.Connection = db.Connection();

                    vendorQuery.CommandText = "INSERT INTO vendor(vendorname) VALUES(@Name)";
                    vendorQuery.Prepare();

                    vendorQuery.Parameters.AddWithValue("@Name", vendorname);

                    vendorQuery.ExecuteNonQuery();

                    MessageBox.Show("O fornecedor " + vendorname + " foi criado com sucesso.");
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Ocurreu um erro");
                    Console.WriteLine("Error: {0}", ex.ToString());
                }
                finally
                {
                    db.Close();
                }
            }
        }

        public static void setSession(string userid)
        {
            LoginInfo.UserID = userid;
            LoginInfo.inTime = DateTime.Now;
            LoginInfo.computer_name = Environment.MachineName;
            LoginInfo.ipAddress = Dns.GetHostAddresses(Environment.MachineName)[0].ToString();

            DBConnector dbcon = new DBConnector();
            dbcon.openConnection();

            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "INSERT INTO login_session (in_time, computer_name, ip_address, user_iduser) VALUES ('" + LoginInfo.inTime.ToString("yyyy-MM-dd HH:mm:ss") + "', N'" + LoginInfo.computer_name + "', '" + LoginInfo.ipAddress + "', "+int.Parse(LoginInfo.UserID)+")";
            cmd.Connection = dbcon.connection;
            cmd.Prepare();
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM login_session ORDER BY idlogin_session DESC LIMIT 1";
            cmd.Connection = dbcon.connection;

            MySqlDataReader reader = cmd.ExecuteReader();

            if (reader.Read())
            {
                LoginInfo.sessionID = int.Parse(reader.GetString(0));
                //Console.Write(LoginInfo.sessionID);
            }

            dbcon.closeConnection();
        }

        /// <summary>
        /// Ajoute une salle à base de donneés.
        /// </summary>
        /// <param name="connectionString">Les paramètres de connexion à la base de données.</param>
        /// <param name="salleDTO">Représente la salle qui sera ajouté.</param>
        public void Add(string connectionString, SalleDTO salleDTO)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new DAOException("Les paramètres de connexion n'ont pas été initialisé.");
            }

            try
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    connection.Open();
                    MySqlCommand addPreparedStatement = new MySqlCommand(SalleDAO.ADD_REQUEST, connection);
                    addPreparedStatement.Parameters.AddWithValue("@numero", salleDTO.Numero);
                    addPreparedStatement.Parameters.AddWithValue("@etage", salleDTO.Etage);
                    addPreparedStatement.Parameters.AddWithValue("@bloc", salleDTO.Bloc);
                    addPreparedStatement.Prepare();
                    addPreparedStatement.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlException)
            {
                throw new DAOException(sqlException.Message, sqlException);
            }
        }

        public static bool Delete(int id)
        {
            try
            {
                String delete_sql          = "DELETE FROM " + TABLE_NAME + " WHERE [email protected]";
                //Sql command
                sql_command                = new MySqlCommand();
                sql_command.Connection     = (MySqlConnection)database.OpenConnection();
                sql_command.CommandText    = delete_sql;
                sql_command.Parameters.AddWithValue("@id", id);
                sql_command.Prepare();

                //execute command
                database.Update(sql_command);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                CloseDatabaseConnection();
            }
        }

        public MailSession()
        {
            string cs = @"server=localhost;userid=timetracker;password=DdCyzpALrxndc6BY;database=timetracker";

            MySqlConnection connect = null;
            MySqlDataReader reader = null;

            try {
                connect = new MySqlConnection (cs);
                connect.Open ();

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandText = "SELECT * FROM `email`";
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    //Console.WriteLine(reader.GetInt32(0) + ": " + reader.GetString(1));
                }

            } catch (Exception ex) {
            } finally {
                if (connect != null)
                {
                    connect.Close();
                }
            }
        }

 /// <summary>
 /// Ajoute un entraineur à la base de données.
 /// </summary>
 /// <param name="connectionString">Les paramètres de connexion à la base de données.</param>
 /// <param name="entraineurDTO">Représente l'entraineur qui sera ajouté.</param>
 public void Add(string connectionString, EntraineurDTO entraineurDTO)
 {
     if (string.IsNullOrEmpty(connectionString))
     {
         throw new DAOException("Les paramètres de connexion n'ont pas été initialisé.");
     }
     try
     {
         using (MySqlConnection connection = new MySqlConnection(connectionString))
         {
             connection.Open();
             MySqlCommand addPreparedStatement = new MySqlCommand(EntraineurDAO.ADD_REQUEST, connection);
             addPreparedStatement.Parameters.AddWithValue("@nom", entraineurDTO.Nom);
             addPreparedStatement.Parameters.AddWithValue("@prenom", entraineurDTO.Prenom);
             addPreparedStatement.Parameters.AddWithValue("@adresse", entraineurDTO.Adresse);
             addPreparedStatement.Parameters.AddWithValue("@ville", entraineurDTO.Ville);
             addPreparedStatement.Parameters.AddWithValue("@telephone", entraineurDTO.Telephone);
             addPreparedStatement.Parameters.AddWithValue("@email", entraineurDTO.Email);
             addPreparedStatement.Parameters.AddWithValue("@sexe", entraineurDTO.Sexe);
             addPreparedStatement.Parameters.AddWithValue("@dateNaissance", entraineurDTO.DateNaissance);
             addPreparedStatement.Prepare();
             addPreparedStatement.ExecuteNonQuery();
         }
     }
     catch (SqlException sqlException)
     {
         throw new DAOException(sqlException.Message, sqlException);
     }
 }

        public void create()
        {
            db = new Database();

            space = new Space();

            try
            {
                pathQuery = new MySqlCommand();

                pathQuery.Connection = db.Connection();

                pathQuery.CommandText = "INSERT INTO path(path) VALUES(@Path)";

                pathQuery.Prepare();

                pathQuery.Parameters.AddWithValue("@Path", path);

                pathQuery.ExecuteNonQuery();

                MessageBox.Show("O Caminho " + path + " foi criado com sucesso.");
            }
            catch (MySqlException ex)
            {
                MessageBox.Show("Ocurreu um erro");

                Console.WriteLine("Error: {0}", ex.ToString());
            }
            finally
            {
                db.Close();
            }
        }