C# MySqlClient.MySqlCommand类代码示例

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

        protected void Button_click(object sender, EventArgs e)
        {
            MySqlConnection bazaPovezava = new MySqlConnection(bazaConnString);
            try
            {
                bazaPovezava.Open();
                string SQLcommand = "INSERT INTO User(username, firstname, lastname, password, email, city, country) VALUES(?un, ?fn, ?ln, ?pw, ?em, ?ci, ?co);";
                MySqlCommand bazaUkaz = new MySqlCommand(SQLcommand, bazaPovezava);
                bazaUkaz.Parameters.Add(new MySqlParameter("?un", username.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?fn", firstname.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?ln", surname.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?pw", pass.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?em", email.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?ci", city.Text));
                bazaUkaz.Parameters.Add(new MySqlParameter("?co", country.Text));

                bazaUkaz.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex);
            }
            finally
            {
                bazaPovezava.Close();
            }
        }

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

        public int InserirPedido(int numCliente, List<Item> pedido)
        {
            int NumPedido = 0;
            int idPedido = 0;
            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand();

            cmd.Connection = conn;
            conn.Open();

            cmd.CommandText = "Select Max(numero) + 1 from tb_Pedidos";
            NumPedido = int.Parse(cmd.ExecuteScalar().ToString());

            cmd.CommandText = "Insert into tb_Pedidos (numero, id_cliente, data) Values(" + NumPedido + "," + numCliente + ", sysdate()); select Max(id) from tb_Pedidos;";
            idPedido = int.Parse(cmd.ExecuteScalar().ToString());

            foreach (Item item in pedido)
            {
                cmd.CommandText = "insert into tb_items (nome, descricao, preco, quantidade, id_pedido, urlImagem) Values ('" + item.descricao + "', Null,"+ item.preco.ToString().Replace(",",".") + "," + item.quantidade + "," + idPedido + ", Null);";
                cmd.ExecuteNonQuery();
            }

            conn.Close();

            return NumPedido;
        }

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

        /// <summary> 
        /// 修改数据 
        /// </summary> 
        /// <param name="entity"></param> 
        /// <returns></returns> 
        public int Update(Policy entity)
        {
            string sql = "UPDATE  tb_policy SET [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],";
            sql = sql + " [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] where [email protected] ";

            //string sql = "UPDATE cimuser SET [email protected] WHERE [email protected]";
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
                command.Parameters.AddWithValue("@agentType", entity.agentType);
                command.Parameters.AddWithValue("@sequence", entity.sequence);
                command.Parameters.AddWithValue("@subject", entity.subject);
                command.Parameters.AddWithValue("@content", entity.content);
                command.Parameters.AddWithValue("@sender", entity.sender);
                command.Parameters.AddWithValue("@attachment", entity.attachment);
                command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
                command.Parameters.AddWithValue("@creatTime", entity.creatTime);
                command.Parameters.AddWithValue("@type", entity.type);
                command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
                 command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
                command.Parameters.AddWithValue("@isValidate", entity.isValidate);
                command.Parameters.AddWithValue("@isDelete", entity.isDelete);
                command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
                command.Parameters.AddWithValue("@toAll", entity.toAll);
                int i = command.ExecuteNonQuery();
                mycn.Close();
                mycn.Dispose();
                return i;
            }
        }

        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 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 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 Int16 Insert(ParkingRateDetails Details)
		{
			try 
			{
                Details.CreatedByName = Details.ParkingRateID == 0 ? Details.CreatedByName : Details.LastUpdatedByName;

                Save(Details);

                string SQL = "SELECT LAST_INSERT_ID();";

                MySqlCommand cmd = new MySqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = SQL;

                System.Data.DataTable dt = new System.Data.DataTable("LAST_INSERT_ID");
                base.MySqlDataAdapterFill(cmd, dt);
                

                Int16 iID = 0;
                foreach (System.Data.DataRow dr in dt.Rows)
                {
                    iID = Int16.Parse(dr[0].ToString());
                }

				return iID;
			}

			catch (Exception ex)
			{
				throw base.ThrowException(ex);
			}	
		}

        public const string mysqlConnection = DBConstant.mysqlConnection;//"User Id=root;Host=115.29.229.134;Database=chinaunion;password=c513324665;charset=utf8";
        /// <summary> 
        /// 添加数据 
        /// </summary> 
        /// <returns></returns> 
        public int Add(Policy entity)
        {


            string sql = "INSERT INTO tb_policy (agentType,subject,content,sender,attachment,attachmentName,creatTime,type, validateStartTime,validateEndTime, isValidate, isDelete, deleteTime,toAll) VALUE (@agentType,@subject,@content,@sender,@attachment,@attachmentName,@creatTime,@type, @validateStartTime,@validateEndTime, @isValidate, @isDelete, @deleteTime,@toAll)";
            using (MySqlConnection mycn = new MySqlConnection(mysqlConnection))
            {
                mycn.Open();
                MySqlCommand command = new MySqlCommand(sql, mycn);
                command.Parameters.AddWithValue("@agentType", entity.agentType);
                command.Parameters.AddWithValue("@subject", entity.subject);
                command.Parameters.AddWithValue("@content", entity.content);
                command.Parameters.AddWithValue("@sender", entity.sender);
                command.Parameters.AddWithValue("@attachment", entity.attachment);
                command.Parameters.AddWithValue("@attachmentName", entity.attachmentName);
                command.Parameters.AddWithValue("@creatTime", entity.creatTime);
                command.Parameters.AddWithValue("@type", entity.type);
                command.Parameters.AddWithValue("@validateStartTime", entity.validateStartTime);
                command.Parameters.AddWithValue("@validateEndTime", entity.validateEndTime);
                command.Parameters.AddWithValue("@isValidate", entity.isValidate);
                command.Parameters.AddWithValue("@isDelete", entity.isDelete);
                command.Parameters.AddWithValue("@deleteTime", entity.deleteTime);
                command.Parameters.AddWithValue("@toAll", entity.toAll);


                int i = command.ExecuteNonQuery();
                mycn.Close();
                mycn.Dispose();
                return i;
            }
        }

        private void buttonCadastrar_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = null;

            try
            {
                conn = Conexao.getConexao();
                MySqlCommand cmd = new MySqlCommand("call cadastra_pizza(@nome, @tamanho, @preco)", conn);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@nome", this.txt_nome.Text);
                cmd.Parameters.AddWithValue("@tamanho", this.cmb_tamanho.Text);
                cmd.Parameters.AddWithValue("@preco", this.txt_preco.Text);

                MySqlDataReader rs = cmd.ExecuteReader();
                rs.Read();

                if (!rs.HasRows)
                {
                    conn.Close();
                    new SuccessDialog("Pizza cadastrada com sucesso!");
                    this.Close();
                }
                else
                {
                    String error = rs.GetString(0);
                    conn.Close();
                    new ErrorDialog(error);
                }
            }
            catch (Exception err)
            {
                if (conn != null) conn.Close();
                new ErrorDialog(err.Message);
            }
        }

        protected void Dodaj(object sender, EventArgs e)
        {
            string DruzynaID = txtDruzyna.Text;
            string Imie = txtImie.Text;
            string Nazwisko = txtNazwisko.Text;
            string Data = txtData.Text;
            string Pozycja = txtPozycja.Text;
            string Waga = txtWaga.Text;
            string Wzrost = txtWzrost.Text;
            string Numer = txtNumer.Text;

            string sDate = String.Format("{0:yyyy-mm-dd}", Data);

            MySqlConnection polaczenie = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand("INSERT INTO pilkarze (ID_Druzyny, Imie, Nazwisko, Data_urodz, Pozycja, Waga, Wzrost, Nr_Kosz) VALUES (@IdDruzyny, @imie, @nazwisko, @data, @pozycja, @waga, @wzrost, @numer)", polaczenie);
            MySqlDataAdapter sda = new MySqlDataAdapter();
            cmd.Parameters.AddWithValue("@IdDruzyny", DruzynaID);
            cmd.Parameters.AddWithValue("@imie", Imie);
            cmd.Parameters.AddWithValue("@nazwisko", Nazwisko);
            cmd.Parameters.AddWithValue("@data", sDate);
            cmd.Parameters.AddWithValue("@pozycja", Pozycja);
            cmd.Parameters.AddWithValue("@waga", Waga);
            cmd.Parameters.AddWithValue("@wzrost", Wzrost);
            cmd.Parameters.AddWithValue("@numer", Numer);

            polaczenie.Open();
            cmd.ExecuteNonQuery();
            polaczenie.Close();
            this.WypelnijGridView();
        }

        private void fillgridDef()
        {
            id = -1;
            buttonDel.Visible = false;
            buttonEdit.Visible = false;
            buttonIns.Visible = false;
            buttonCancel.Visible = false;
            DBConnect NewcConnection = new DBConnect();
            NewcConnection.dbConnection();
            MySqlCommand querysql = new MySqlCommand("Select * From deficiencias", DBConnect.db);

            try
            {
                MySqlDataAdapter dados = new MySqlDataAdapter();
                dados.SelectCommand = querysql;
                DataTable tabela = new DataTable();
                dados.Fill(tabela);
                BindingSource fonte = new BindingSource();
                fonte.DataSource = tabela;
                dataGridView1.DataSource = fonte;
                this.dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[1].HeaderText = "Deficiencia";
                dados.Update(tabela);
                dataGridView1.AutoResizeColumns();
                dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            DBConnect.db.Close();
            textBoxDef.Clear();
        }

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