C# MySqlConnection.Open方法代码示例

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

        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 void addgenres(string genre)
        {
            bool HasRows;
            string MyConString = "SERVER=192.168.5.106;" +
                "DATABASE=jukebox;" +
                "UID=jukebox;" +
                "PASSWORD=;";
            MySqlConnection connection = new MySqlConnection(MyConString);

            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            MySqlDataReader Reader;

            command.CommandText = "SELECT uid FROM genres WHERE genre='" + MySqlEscape(genre.Trim()) + "'";
            Reader = command.ExecuteReader();
            HasRows = Reader.HasRows;
            Reader.Close();
            connection.Close();
            if (!HasRows)
            {
                connection.Open();
                string sql = "INSERT INTO genres (genre) VALUES ('" + MySqlEscape(genre.Trim()) + "')";
                command = new MySqlCommand(sql, connection);
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            string str = DB.dataConnect.connectStr;
            MySqlConnection connect = new MySqlConnection(str);
            connect.Open();

            MySqlCommand com = new MySqlCommand("SELECT * from news", connect);
            MySqlDataReader read = com.ExecuteReader();

            while (read.Read())
            {

                string result = Label1.Text + "" + read.GetString(1) + "<a href='../Pages/shownews.aspx?title=" + read.GetString(1) + "'</a>&nbsp;&nbsp;&nbsp;อ่านต่อ...</a>";
                Label1.Text = result+"<br/>";
            }

            com = new MySqlCommand("SELECT * from marquee", connect);
            connect.Close();
            connect.Open();
            MySqlDataReader rd = com.ExecuteReader();
            while (rd.Read())
            {

                string result = lblMarquee.Text + "" + rd.GetString(1) + "<a href='../Pages/showDetail.aspx?title=" + rd.GetString(1) + "'</a>&nbsp;&nbsp;&nbsp;อ่านต่อ...&nbsp;&nbsp;&nbsp;</a>";

            }
        }

        public void AddColumn(string tableName, string columnName, string columnDescription)
        {
            using (Connection = new MySqlConnection(GetConnectionString()))
            {
                var q1 = string.Format(CheckColumnQuery, tableName, columnName);
                using (CheckColumnCommand = new MySqlCommand(q1, Connection))
                {
                    Connection.Open();
                    var res = int.Parse(Convert.ToString(CheckColumnCommand.ExecuteScalar()));
                    Connection.Close();

                    if (res > 0)
                    {
                        return;
                    }
                }

                var q3 = string.Format(Query, tableName, columnName, columnDescription);
                using (Command = new MySqlCommand(q3, Connection))
                {
                    Command.CommandTimeout = 12 * 3600;
                    Connection.Open();
                    Command.ExecuteNonQuery();
                    Connection.Close();
                }

                var q2 = string.Format(IndexQuery, "Index" + columnName, tableName, columnName);
                using (IndexCommand = new MySqlCommand(q2, Connection))
                {
                    Connection.Open();
                    IndexCommand.ExecuteNonQuery();
                    Connection.Close();
                }
            }
        }

        public void alterUser(TextBox textBoxGammelPassord, TextBox textBoxEpost, TextBox textBoxNyPassord, TextBox textBoxAdresse, TextBox textBoxTelefon, TextBox textBoxID, String bondeID)
        {
            String dbconnect = myconnectionstring;
            MySqlConnection dbconn = new MySqlConnection(dbconnect);

            if (textBoxGammelPassord.Text == gammeltpassordLocal)
            {
                MySqlCommand cmd = dbconn.CreateCommand();
                cmd.CommandText = "UPDATE login SET epost='" + textBoxEpost.Text + "', passord= '" + textBoxNyPassord.Text + "'WHERE bondeID= '" + bondeID + "'";
                dbconn.Open();
                cmd.ExecuteNonQuery();
                dbconn.Close();

                MySqlCommand cmd2 = dbconn.CreateCommand();
                cmd2.CommandText = "UPDATE Kontakt SET adresse= '" + textBoxAdresse.Text + "', telefonnr= '" + textBoxTelefon.Text + "' WHERE bondeID= '" + bondeID + "'";
                dbconn.Open();
                cmd2.ExecuteNonQuery();
                dbconn.Close();

                MessageBox.Show(textBoxTelefon.Text);
                getinfobruker(textBoxGammelPassord,  textBoxEpost,  textBoxNyPassord,  textBoxAdresse,  textBoxTelefon ,textBoxID, bondeID);
            }
            else
            {
                MessageBox.Show("Feil passord");
            }
        }

    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {

            conn = new MySql.Data.MySqlClient.MySqlConnection();
            conn.ConnectionString = myConnectionString;
            conn.Open();
            MySqlCommand cmd3 = new MySqlCommand("INSERT INTO pokoje (id_pokoj, Nr_pokoj, Ilosc_lozek, Ilosc_osob, Typ_pokoj, Cena_pokoj) VALUES     (@id_pokoj,@nrpokoj,@ilosclozek,@iloscosob,@typpokoj,@cenapokoj)", conn);
            cmd3.CommandType = CommandType.Text;
            cmd3.Parameters.AddWithValue("@nrpokoj", TextBox_up_Nr0.Text);
            cmd3.Parameters.AddWithValue("@ilosclozek", TextBox_up_IL0.Text);
            cmd3.Parameters.AddWithValue("@iloscosob", TextBox_up_IO0.Text);
            cmd3.Parameters.AddWithValue("@typpokoj", DropDownList_up_Typ0.Text);
            cmd3.Parameters.AddWithValue("@cenapokoj", TextBox_up_Cena0.Text);
            cmd3.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
            cmd3.ExecuteNonQuery();
            conn.Close();

            //INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, 7);

            MySqlCommand cmd4 = new MySqlCommand("INSERT INTO pozycja_rezerwacji(Rezerwacja_id_rezerwacja, Pokoje_id_pokoj) VALUES (8, @id_pokoj)", conn);
            cmd4.CommandType = CommandType.Text;

            conn.Open();
            cmd4.Parameters.AddWithValue("@id_pokoj", TextBox_up_Id0.Text);
            cmd4.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            Blad.Visible = true;

        }
    }

    public void InvalidCast()
    {
      MySqlConnection con = rootConn;
      string sql = @"drop function if exists MyTwice; create function MyTwice( val int ) returns int begin return val * 2; end;";
      MySqlCommand cmd = new MySqlCommand(sql, con);
      cmd.ExecuteNonQuery();
      cmd.CommandText = "drop procedure if exists spMyTwice; create procedure spMyTwice( out result int, val int ) begin set result = val * 2; end;";
      cmd.ExecuteNonQuery();
      try
      {
        cmd.CommandText = "drop user 'tester2'@'localhost'";
        cmd.ExecuteNonQuery();
      }
      catch (Exception)
      {
      }
      cmd.CommandText = "CREATE USER 'tester2'@'localhost' IDENTIFIED BY '123';";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "grant execute on function `MyTwice` to 'tester2'@'localhost';";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "grant execute on procedure `spMyTwice` to 'tester2'@'localhost'";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "grant select on table mysql.proc to 'tester2'@'localhost'";
      cmd.ExecuteNonQuery();
      cmd.CommandText = "flush privileges";
      cmd.ExecuteNonQuery();
      MySqlConnection con2 = new MySqlConnection(
        rootConn.ConnectionString);
      con2.Settings.UserID = "tester2";
      con2.Settings.Password = "123";

      // Invoke the function
      cmd.Connection = con2;
      con2.Open();
      cmd.CommandText = "MyTwice";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add(new MySqlParameter("val", System.DBNull.Value));
      cmd.Parameters.Add("@p", MySqlDbType.Int32);
      cmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
      cmd.Parameters[0].Value = 20;
      cmd.ExecuteNonQuery();
      con2.Close();
      Assert.AreEqual(cmd.Parameters[1].Value, 40);

      con2.Open();
      cmd.CommandText = "spMyTwice";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Clear();
      cmd.Parameters.Add(new MySqlParameter("result", System.DBNull.Value));
      cmd.Parameters.Add("val", MySqlDbType.Int32);
      cmd.Parameters[0].Direction = ParameterDirection.Output;
      cmd.Parameters[1].Value = 20;
      cmd.ExecuteNonQuery();
      con2.Close();
      Assert.AreEqual(cmd.Parameters[0].Value, 40);
    }

        protected void submitMethod(object sender, EventArgs e)
        {
            try {
                string constr = "Data Source=localhost; port=3306; Initial Catalog=ci2454_eb04539;User Id=eb04539;password=eb04539";// ConfigurationManager.ConnectionStrings["ci2454_eb04539ConnectionString"].ConnectionString;

                    MySqlConnection con = new MySqlConnection(constr);

                    con.Open();

                    string[] cantidad = Request.Form.GetValues("cantidad");
                    string[] medida = Request.Form.GetValues("medida");
                    string[] ingrediente = Request.Form.GetValues("ingrediente");

                    MySqlCommand cmd = new MySqlCommand("INSERT INTO recetas (nombre, instrucciones, creador, imagen, tips, categoria) " + "VALUES('"+ nombreReceta.Value + "','"+ instrucciones.Value + "','"+ "1" + "','" + imagen.Value + "','"+ tips.Value +"','"+ categorias.Value + "')", con);

                    cmd.ExecuteReader();

                    con.Close();

                    con.Open();

                    MySqlCommand cmd2 = new MySqlCommand("SELECT * FROM recetas Where imagen='"+imagen.Value+"'", con);
                    cmd2.Connection = con;

                    String idReceta = Convert.ToString(cmd2.ExecuteScalar());
                    System.Diagnostics.Debug.WriteLine(idReceta);

                    con.Close();

                    con.Open();

                    for (int i = 0; i < ingrediente.Length;++i) {
                        MySqlCommand cmd3 = new MySqlCommand("INSERT INTO ingredientes (nombre) VALUES('" + ingrediente[i] + "')");
                        cmd3.Connection = con;
                        cmd3.ExecuteReader();
                    }
                //    con.Open();

               //     for (int i = 0; i < ingrediente.Length;++i) {

               //         MySqlCommand cmd3 = new MySqlCommand("INSERT INTO rec_ing (receta, ingrediente, cantidad, unidadDeMedida) " + "VALUES('" + idReceta + "','" + ingrediente[i] + "','" + cantidad[i] + "','" + medida[i] + "')", con);

               //         cmd3.ExecuteReader();
              //     }

              //      con.Close();

            } catch (NullReferenceException ex) {
                System.Diagnostics.Debug.WriteLine("ERROR");
            }
        }

        private void btnActualizar_Click(object sender, EventArgs e)
        {
            try
            {
                string S_Cconn = "Server=192.168.1.100;UID= root; Database= bd_turnos; Password=GrupoSGT014;";

                MySqlConnection SQL_conexion = new MySqlConnection();
                SQL_conexion.ConnectionString = S_Cconn;

                SQL_conexion.Open();
                MySqlDataAdapter SQL_da = new MySqlDataAdapter("Select * from tabm_usuario", SQL_conexion);
                DataTable DT_dat = new DataTable();
                SQL_da.Fill(DT_dat);
                this.dgvUsuarios.DataSource = DT_dat;
                SQL_conexion.Close();

                //Numero de usuarios en el sistema
                SQL_conexion.Open();
                MySqlCommand SQL_cmd = new MySqlCommand("select count(id_usuario)as Cuenta_users from tabm_usuario ", SQL_conexion);
                MySqlDataReader dataReader = SQL_cmd.ExecuteReader();
                while (dataReader.Read()) { lblTotal.Text = dataReader["Cuenta_users"] + ""; }
                SQL_conexion.Close();

                //Numero Usuarios Administrador
                SQL_conexion.Open();
                MySqlCommand SQL_Permiso1 = new MySqlCommand("select count(usur_permiso)as Permiso_user from tabm_usuario where usur_Permiso= 1", SQL_conexion);
                MySqlDataReader dRPermiso = SQL_Permiso1.ExecuteReader();
                while (dRPermiso.Read()) { lblAdmin.Text = dRPermiso["Permiso_user"] + ""; }
                SQL_conexion.Close();

                //numero Usuarios Ventanilla
                SQL_conexion.Open();
                MySqlCommand SQL_Permiso2 = new MySqlCommand("select count(usur_permiso)as Permiso_user from tabm_usuario where usur_Permiso= 2", SQL_conexion);
                MySqlDataReader dRPermiso2 = SQL_Permiso2.ExecuteReader();
                while (dRPermiso2.Read()) { lblNormales.Text = dRPermiso2["Permiso_user"] + ""; }
                SQL_conexion.Close();

                //Numero Usuarios Activos
                SQL_conexion.Open();
                MySqlCommand SQL_Estado = new MySqlCommand("select count(usur_estado)as usur_estado from tabm_usuario where usur_estado= 'Activo'" , SQL_conexion);
                MySqlDataReader dREstado = SQL_Estado.ExecuteReader();
                while (dREstado.Read()) { lblActivos.Text = dREstado["usur_estado"] + ""; }
                SQL_conexion.Close();

            }
            catch (Exception ei)
            {
                MessageBox.Show("Ocurrio un error" + ei.Message);
            }
        }

    /// <summary>
    /// Constructor that sets up and runs queries from the database. 
    /// The database connection is setup in the web.config file.
    /// </summary>
    /// <param name="queryString">This should be an actual sql query like "select * from table where id = 1"</param>
    /// <param name="type">This is the type of query being executed (select/update/insert/delete)</param>
    public DatabaseQuery(String queryString, Type type)
    {
        System.Configuration.Configuration config =
            System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/Study Participant Portal");
        System.Configuration.ConnectionStringSettings connString;
        connString = config.ConnectionStrings.ConnectionStrings["ApplicationServices"];
        System.Configuration.KeyValueConfigurationElement setting = config.AppSettings.Settings["userName"];

        MySqlConnection connection = new MySqlConnection(connString.ToString());
        MySqlCommand command = connection.CreateCommand();
        MySqlDataReader Reader;
        command.CommandText = queryString;
        connection.Open();

        switch (type) {
            //The select statement populates a list of a list of strings.
            case Type.Select:
                Reader = command.ExecuteReader();
                while (Reader.Read()) {
                    record = new List<string>();
                    for (int i = 0; i < Reader.FieldCount; i++) {
                        item = Reader.GetValue(i).ToString();
                        record.Add(item);
                    }
                    results.Add(record);
                }
                break;
            //for an insert, we set the auto increment ID that was inserted into the database
            // as the lastInsertID field so we have access to it in the code.
            case Type.Insert:
                command.ExecuteNonQuery();
                connection.Close();
                connection.Open();
                command.CommandText = "select Last_Insert_ID()";
                MySqlDataReader Reader2 = command.ExecuteReader();
                if (Reader2.Read()) {
                    lastInsertID = Convert.ToInt32(Reader2.GetValue(0));
                }
                break;
            case Type.Update:
                command.ExecuteNonQuery();
                break;
            case Type.Delete:
                command.ExecuteNonQuery();
                break;
        }
        connection.Close();
    }

        private void button1_Click(object sender, EventArgs e)
        {
            string MyConnectionString = "Server=localhost;Database=EMS;Uid=root;Pwd='';";
            MySqlConnection connection = new MySqlConnection(MyConnectionString);

            connection.Open();
            MySqlCommand cmd = connection.CreateCommand();
            MySqlCommand cmd2 = connection.CreateCommand();
            MySqlCommand cmd3 = connection.CreateCommand();
            cmd.CommandText = "INSERT INTO sensor(sensor_id,sensor_type,contract_id,refresh_time,sensor_status) VALUES(@sensor,@type,@cont,@time,@status)";
            cmd.Parameters.AddWithValue("@sensor", id.Text);
            cmd.Parameters.AddWithValue("@type", type.SelectedItem.ToString());
            cmd.Parameters.AddWithValue("@cont",contract.Text.ToString());
            DateTime time = Convert.ToDateTime(refreshtime.Text);
            cmd.Parameters.AddWithValue("@time", time);
            if (contract.Text != null)
            {
                cmd.Parameters.AddWithValue("@status", true);
            }else{
                cmd.Parameters.AddWithValue("@status", true);
            }
            cmd.ExecuteNonQuery();
            connection.Close();
            if (contract.Text != null)
            {

                cmd2.CommandText = "INSERT INTO contract(contract_id,sensor_id,established_date,expire_date,agent_id,Service_provider) VALUES(@contract_id,@sensor_id,@established_date,@expire_date,@agent_id,@Service_provider)";
                cmd2.Parameters.AddWithValue("@contract_id", contract.Text.ToString());
                cmd2.Parameters.AddWithValue("@sensor_id", id.Text.ToString());
                string date1 = establised.Text;
                string date2 = establised.Text;

                DateTime dt1 = Convert.ToDateTime(date1);
                DateTime dt2 = Convert.ToDateTime(date2);

                cmd2.Parameters.AddWithValue("@established_date", dt1.ToString());
                cmd2.Parameters.AddWithValue("@expire_date", dt2.ToString());
                cmd2.Parameters.AddWithValue("@agent_id", agent.SelectedItem.ToString());
                cmd2.Parameters.AddWithValue("@Service_provider", service.SelectedItem.ToString());
                connection.Open();
                cmd2.ExecuteNonQuery();
                connection.Close();

            }

            Login.warning ww = new Login.warning("Sensor Successfully Added!", this);
            ww.Show();
        }

        //test connection for server
        public static bool TestConnection()
        {
            try
            {
                //set database connection
                using (MySqlConnection con = new MySqlConnection(Big.Config.GetConnectionString()))
                {
                    //open connection
                    con.Open();

                    return true;
                }
            }
            catch (MySqlException ex)
            {
                //revert settings
                Properties.Settings.Default.HOST = String.Empty;
                Properties.Settings.Default.DATABASE = String.Empty;
                Properties.Settings.Default.USERNAME = String.Empty;
                Properties.Settings.Default.PASSWORD = String.Empty;
                Properties.Settings.Default.DSN = String.Empty;

                //save settings
                Properties.Settings.Default.Save();

                //error configuration
                MessageBox.Show("Error: Cannot connect to server" + ex.Message.ToString(), "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error);

                return false;
            }
        }

        public List<Categoria> ObterCategoriasCadastradas(string idioma)
        {
            List<Categoria> lista = new List<Categoria>();

            MySqlConnection conn = new MySqlConnection(connectionString);
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT id, nome, urlImagem FROM tb_categorias order by nome";

            conn.Open();
            MySqlDataReader dr = cmd.ExecuteReader();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    lista.Add(new Categoria
                    {
                        id = (int)dr["id"],
                        nome = Tradutor.Traduzir(dr["nome"].ToString(), idioma),
                        urlImagem = dr["urlImagem"].ToString()
                    });
                }
            }
            conn.Close();

            return lista;
        }