C# MySqlConnection.CreateCommand方法代码示例

        //Data uit tabel tonen x
        public static void datashow()
        {
            string MyConnectionString = "Server=localhost;Database=databier;Uid=root;Pwd=;";
            MySqlConnection connection = new MySqlConnection(MyConnectionString);
            connection.Open();
            MySqlCommand cmd = connection.CreateCommand();           

            try
            {
                
                //Data in tabel bekijken
                Console.Write("\n\nKies een tabel die u wilt bekijken: ");
                string input = Console.ReadLine();
                MySqlCommand cmdbierdata = connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM " + input;
                Console.Clear();
                MySqlDataReader readerbierdata = cmd.ExecuteReader();

                while (readerbierdata.Read())
                {
                    Console.WriteLine(readerbierdata.GetString(0) + " | " + readerbierdata.GetString(1) + " | " + readerbierdata.GetString(2));

                }
            }
            catch (MySqlException exception)
            {
                Console.WriteLine("ERROR: \n{0}", exception);
                Console.WriteLine("\n> Terug naar de database: Typ (DATABASE NAAM).");
                Console.WriteLine("> Terug naar het menu: Typ MENU.");
                Console.WriteLine("> Afsluiten: Typ EXIT.");
            }
            connection.Close();
        }

        public static void Main(string[] args)
        {
            string connectionString=
                "Server= Localhost;"+
                "Database = dbprueba;"+
                "User Id=root;"+
                "Password=sistemas";

                MySqlConnection mySqlConnection = new MySqlConnection(connectionString);
            mySqlConnection.Open ();

            MySqlCommand updateMySqlCommand= mySqlConnection.CreateCommand();
            updateMySqlCommand.CommandText="update articulo set nombre=:nombre where id=!";
            updateMySqlCommand.ExecuteNonQuery();

            MySqlCommand mySqlCommand = mySqlConnection.CreateCommand();

            mySqlCommand.CommandText = "select * from articulo";

            MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader ();

            while (mySqlDataReader.Read ()){
                Console.WriteLine("id={0} nombre={1}",mySqlDataReader["id"],mySqlDataReader["nombre"]);
            }

            mySqlDataReader.Close();
            mySqlConnection.Close();
            string hora=DateTime.Now.ToString();
        }

 public IList<Sim> Load()
 {
     using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
     {
         conn.Open();
         var cmd = conn.CreateCommand();
         cmd.CommandText = "set character set 'utf8'";
         cmd.ExecuteNonQuery();
         var cmdText = @"select * from sim";
         cmd = conn.CreateCommand();
         cmd.CommandText = cmdText;
         var reader = cmd.ExecuteReader();
         var cityList = new List<Sim>();
         while (reader.Read())
         {
             var city = new Sim()
             {
                 ID = reader.GetInt32("id"),
                 QuestID = reader.GetInt32("quest_id"),
                 CompareID = reader.GetInt32("compare_id"),
                 Value = reader.GetInt32("value"),
             };
             cityList.Add(city);
         }
         reader.Close();
         return cityList;
     }
 }

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

        //Custom query x 
        public static void custom()
        {

            try
            {
                string MyConnectionString = "Server=localhost;Database=databier;Uid=root;Pwd=;";

                MySqlConnection connection = new MySqlConnection(MyConnectionString);
                connection.Open();

                MySqlCommand cmd = connection.CreateCommand();

                Console.Write("\n\nVoer een query in: ");
                string queryinput = Console.ReadLine();
                MySqlCommand querydata = connection.CreateCommand();
                cmd.CommandText = queryinput;
                Console.Clear();
                MySqlDataReader query = cmd.ExecuteReader();

                while (query.Read())
                {
                    Console.WriteLine(query.GetString(0) + " | " + query.GetString(1) + " | " + query.GetString(2));
                }
                connection.Close();
            }
            catch (Exception optieexception)
            {
                Console.WriteLine("ERROR: \n{0}", optieexception);
                Console.WriteLine("\n> Terug naar de database: Typ (DATABASE NAAM).");
                Console.WriteLine("> Terug naar het menu: Typ MENU.");
                Console.WriteLine("> Afsluiten: Typ EXIT.");
            }
        }

 public void Save(IEnumerable<Sim> simList)
 {
     if (simList.Count() == 0)
         return;
     using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
     {
         conn.Open();
         var cmd = conn.CreateCommand();
         cmd.CommandText = "set character set 'utf8'";
         cmd.ExecuteNonQuery();
         var cmdText = @"select * from quest_sim";
         cmd = conn.CreateCommand();
         cmd.CommandText = cmdText;
         var quest_sim = @"INSERT INTO quest_sim SET quest_id=?quest_id,compare_id=?compare_id,value=?value,start=?start";
         //路径
         simList.All(sim =>
         {
             cmd = conn.CreateCommand();
             cmd.CommandText = quest_sim;
             cmd.Parameters.AddWithValue("?quest_id", sim.QuestID);
             cmd.Parameters.AddWithValue("?compare_id", sim.CompareID);
             cmd.Parameters.AddWithValue("?value", sim.Value);
             cmd.Parameters.AddWithValue("?start", sim.StartCity);
             cmd.ExecuteNonQuery();
             return true;
         });
     }
 }

        public MySQL(Config.Config config)
        {
            this.config = config;
            this.Table = config.Table;

            String connectionString = "server={0};port={1};uid={2};pwd={3};";
            connectionString = String.Format(connectionString,
                config.Host, config.Port, config.User, config.Pass);

            conn = new MySqlConnection();
            conn.ConnectionString = connectionString;
            conn.Open();
            // Create DB
            var cmd = conn.CreateCommand();
            cmd.CommandText = String.Format("CREATE DATABASE IF NOT EXISTS `{0}`;", config.Database);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            cmd = conn.CreateCommand();
            cmd.CommandText = String.Format("USE `{0}`;", config.Database);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            // Create Table
            cmd = conn.CreateCommand();
            cmd.CommandText = String.Format(getTableCreateString(), config.Table);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }

 public IList<City> Load()
 {
     using (MySqlConnection conn = new MySqlConnection("server = localhost; user id = root; password = ; database = test"))
     {
         conn.Open();
         var cmd = conn.CreateCommand();
         cmd.CommandText = "set character set 'utf8'";
         cmd.ExecuteNonQuery();
         var cmdText = @"select * from dol_city";
         cmd = conn.CreateCommand();
         cmd.CommandText = cmdText;
         var reader = cmd.ExecuteReader();
         var cityList = new List<City>();
         while (reader.Read())
         {
             var city = new City()
             {
                 ID = reader.GetInt32("id"),
                 Name = reader.GetString("city_name"),
                 X = reader.GetFloat("x"),
                 Y = reader.GetFloat("y")
             };
             cityList.Add(city);
         }
         reader.Close();
         return cityList;
     }
 }

        public bool writeDonneesXML(List<Station> listStation)
        {
            MySqlConnection connection = new MySqlConnection(myConnectionString);
            MySqlCommand cmd;
            try
            {
                connection.Open();

                int id = 3;

                foreach(Station uneStation in listStation)
                {
                    // Renseignement Station
                    cmd = connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO station(station_id,station_adresse,station_cp,station_ville,station_tel,station_lat, station_long, station_id_enseigne)VALUES(@station_id,@station_adresse,@station_cp,@station_ville,@station_tel,@station_lat,@station_long,@station_id_enseigne);commit;";
                    cmd.Parameters.AddWithValue("@station_id", id);
                    cmd.Parameters.AddWithValue("@station_adresse", uneStation.address);
                    cmd.Parameters.AddWithValue("@station_cp", uneStation.code_postal);
                    cmd.Parameters.AddWithValue("@station_ville", uneStation.city);
                    cmd.Parameters.AddWithValue("@station_tel", DBNull.Value);
                    cmd.Parameters.AddWithValue("@station_lat", uneStation.lattitude);
                    cmd.Parameters.AddWithValue("@station_long", uneStation.longitude);
                    cmd.Parameters.AddWithValue("@station_id_enseigne", getIdEnseigne());
                    cmd.ExecuteNonQuery();

                    // Renseignement Prix
                    foreach (Prix unPrix in uneStation.price_list)
                    {
                        string id_prix = getIdTypeEssence(unPrix);
                        if(Int32.Parse(id_prix) !=-1)
                        {
                            cmd = connection.CreateCommand();
                            cmd.CommandText = "INSERT INTO prix(prix_type_id, prix_station_id, prix_valeur, prix_date)VALUES(@prix_type_id,@prix_station_id,@prix_valeur,@prix_date);commit;";
                            cmd.Parameters.AddWithValue("@prix_type_id", id_prix);
                            cmd.Parameters.AddWithValue("@prix_station_id", id);
                            cmd.Parameters.AddWithValue("@prix_valeur", unPrix.price);
                            cmd.Parameters.AddWithValue("@prix_date", unPrix.dateMiseAjour);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    id++;
                }
            }
            catch (Exception e)
            {
                AffichagePrix.logger.ecrireInfoLogger("ERROR : " + e.StackTrace, true);
                return false;
            }
            finally
            {
                if(connection.State == System.Data.ConnectionState.Open)
                {
                    connection.Close();
                }
            }

            return true;
        }

        public Boolean ItemAdd(String productName, String productDescription, float price, int productId, String office, int officeId)
        {


            try
            {
                DbConnection dbConnection = new DbConnection();
                connection = dbConnection.getConnection();
                connection.Open();
                List<int> storeList = new List<int>();
                storeList = getChildStoreIds(officeId, office);
                //store(storeList);

                if (storeList.Count > 0)
                {
                    if (productId == 0)
                    {
                        sqlQuery = "INSERT INTO product (product_id, product_name, product_description) VALUES (NULL, '" + productName + "', '" + productDescription + "')";
                        MySqlCommand newCommand = connection.CreateCommand();
                        newCommand.CommandText = sqlQuery;

                        newCommand.ExecuteNonQuery();
                    }
                   foreach (int eachStore in storeList)
                    {
                        sqlQuery = "SELECT * FROM store_product WHERE product_id=" + productId + " AND store_id=" + eachStore;
                        MySqlCommand command = new MySqlCommand(sqlQuery, connection);
                        MySqlDataReader sdr = command.ExecuteReader();

                        if (!sdr.HasRows)
                        {
                            sdr.Close();
                            sqlQuery = "INSERT INTO store_product (store_id, product_id, price) VALUES (" + eachStore + ", " + productId + ", " + price + ")";
                            MySqlCommand newCommand = connection.CreateCommand();
                            newCommand.CommandText = sqlQuery;

                            newCommand.ExecuteNonQuery();
                        }
                            
                        sdr.Close();
                        
                    }
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                return false;
                //return new JavaScriptSerializer().Serialize(ex.Message); ;
            }
            finally
            {
                connection.Close();
            }
        }

        public static void Main(string[] args)
        {
            string connectionString =
            "Server=localhost;" +
            "Database=dbrepaso;" +
            "User Id=root;" +
            "Password=sistemas";

            string command = "SELECT * FROM articulo";

            MySqlConnection mySqlConnection = new MySqlConnection(connectionString); //Crear conexión a bd

            mySqlConnection.Open(); //Abrimos la conexión

            MySqlCommand mySqlCommand = mySqlConnection.CreateCommand(); //Creamos comando SQL
            mySqlCommand.CommandText = command; //Cambiamos el texto del comando SQL por command.

            MySqlCommand mySqlCommandChange = mySqlConnection.CreateCommand(); //Creamos comando SQL
            mySqlCommandChange.CommandText = "UPDATE articulo SET [email protected] WHERE id=1"; //Cambiamos el texto del comando SQL por command.
            MySqlParameter mySqlParameter = mySqlCommandChange.CreateParameter();
            mySqlParameter.ParameterName = "nombre";
            mySqlParameter.Value = DateTime.Now.ToString();
            mySqlCommandChange.Parameters.Add(mySqlParameter);

            //MySqlDataReader mySqlDataReader; //Creamos un DataReader
            //mySqlDataReader = mySqlCommand.ExecuteReader(); // Nos devuelve un mySqlDataReader, lector de datos

            MySqlDataReader mySqlDataReaderChange; //Creamos otro DataReader
            //mySqlDataReaderChange = mySqlCommandChange.ExecuteNonQuery();
            // ExecuteNonQuery ejecuta el Update
            mySqlDataReaderChange.Close();

            //while(mySqlDataReader.Read()){ //Leemos todas las filas
            //	Console.WriteLine (mySqlDataReader.GetString (0) + ", " + mySqlDataReader.GetString (1));
            //}

            while(mySqlDataReaderChange.Read()){ //Leemos todas las filas
                Console.WriteLine (mySqlDataReaderChange.GetString (0) + ", " + mySqlDataReaderChange.GetString (1));
            }

            /*			do {
            if (mySqlConnection.State == ConnectionState.Open) {
            Console.WriteLine ("Conexión establecida.");
            } else {
            Console.WriteLine ("...");
            }
            } while (mySqlConnection.State == ConnectionState.Closed);
            */
            mySqlConnection.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();
        }

        // uses it's own connection
        public void checkTable(string tablename)
        {
            bool isTable = false;
            //mysql- check to see if tables already exist
            IDbConnection dbcon = new MySqlConnection (connectionString);
            dbcon.Open ();
            IDbCommand dbcmd = dbcon.CreateCommand ();
            string sql =
             	  		    " SHOW tables LIKE '" + tablename + "'";
            dbcmd.CommandText = sql;
            IDataReader reader = dbcmd.ExecuteReader ();
            while (reader.Read()) {
                string checkresult = (string)reader ["Tables_in_" + mysqldatabase + " (" + tablename + ")"];
                isTable = true;
            }
            ProgramLog.Plugin.Log ("[Mysql] checkTable " + tablename + ": Found:" + isTable);

            // clean up
            reader.Close ();
            reader = null;
            dbcmd.Dispose ();
            dbcmd = null;
            dbcon.Close ();
            dbcon = null;
            //end check

            if (!isTable) {
                //mysql- create table if not found
                string connectionString2 =
               	     		"Server=" + mysqlserver + ";" +
              	   			"Database=" + mysqldatabase + ";" +
              	 			"User ID=" + mysqluser + ";" +
              				"Password=" + mysqlpassword + ";" +
              				"Pooling=false";
                dbcon = new MySqlConnection (connectionString);
                dbcon.Open ();
                IDbCommand dbcmd2 = dbcon.CreateCommand ();
                string sql2 = "";
                if (tablename == "terraria") {
                    sql2 = " CREATE TABLE " + tablename + " ( total INT NOT NULL , timestamp DATETIME NOT NULL , players INT NOT NULL , playernames TEXT NOT NULL , ops INT NOT NULL , opnames TEXT NOT NULL , allnames TEXT NOT NULL ) ";
                }
                if (tablename == "terraria_tiles") {
                    sql2 = " CREATE TABLE " + tablename + " ( timestamp DATETIME NOT NULL , player TEXT NOT NULL , x INT NOT NULL , y INT NOT NULL , action TEXT NOT NULL , tile INT NOT NULL, tiletype TEXT NOT NULL, wall INT NOT NULL, undone BOOLEAN NOT NULL ) ENGINE = MYISAM ;";
                }
                if (tablename == "terraria_iplog") {
                    sql2 = " CREATE TABLE " + tablename + " ( timestamp DATETIME NOT NULL , player TEXT NOT NULL , ip TEXT NOT NULL);";
                }
                dbcmd2.CommandText = sql2;
                IDataReader reader2 = dbcmd2.ExecuteReader ();
                // clean up
                reader2.Close ();
                reader2 = null;
                dbcmd2.Dispose ();
                dbcmd2 = null;
                dbcon.Close ();
                dbcon = null;
                ProgramLog.Plugin.Log ("[Mysql] Table '" + tablename + "' created.");
                //end create
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            string thisProjectID = Request.QueryString["ProjectID"];

            if (!string.IsNullOrEmpty(thisProjectID))
            {

                DBConnection = new MySqlConnection(objUKFilmLocation.DBConnect);
                DBCommand = DBConnection.CreateCommand();

                DBConnection.Open();

                // Update Record
                DBCommand.CommandText = "update ProjectDetails set ShelvedDate = '" + UK_Film_Location_Class.UKFilmLocation.makeSQLDate(DateTime.Now.ToString()) + "' where ProjectID = '" + thisProjectID + "';";

                DBCommand.ExecuteNonQuery();

                DBConnection.Close();

            }

            DBConnection.Dispose();

            Response.Redirect("/Opportunities.aspx");
        }

        //m mayucula en main obligatoriamente.
        public static void Main(string[] args)
        {
            MySqlConnection mysqlconection = new MySqlConnection (
                "Database=dbprueba;Data Source=localhost;User id=root; Password=sistemas");

            mysqlconection.Open ();

            MySqlCommand mysqlcommand = mysqlconection.CreateCommand ();
            mysqlcommand.CommandText = "select * from articulo";
            //				"select a.categoria as articulocategoria, c.nombre as categorianombre, count(*)" +
            //				"from articulo a " +
            //				"left join categoria c on a.categoria= c.id " +
            //				"group by articulocategoria, categorianombre";

            MySqlDataReader mysqldatareader = mysqlcommand.ExecuteReader ();

            //---------------------------------------------------------------
            updateDatabase (mysqlconection);
            showColumnNames (mysqldatareader);
            show (mysqldatareader);

            //---------------------------------------------------------------
            mysqldatareader.Close ();
            mysqlconection.Close ();
        }