C# MySqlCommand.CreateParameter方法代碼示例

        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, Dictionary<string, object> cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (var param in cmdParms)
                {
                    var parameter = cmd.CreateParameter();
                    parameter.ParameterName = param.Key;
                    parameter.Value = param.Value;
                    cmd.Parameters.Add(parameter);
                }

            }
        }

        protected override CustomerCommand CreateCommand(DataCommandConfig commandConfig)
        {
            //1.創建 Connection 對象【需要從連接池中獲取連接對象，此處後續優化】
            //string strConn = ConfigurationManager.AppSettings["ConnectionString"];

            var dbConfig = DataManager.DatabaseDictionary[commandConfig.DataSourceID];
            string strConn = dbConfig.ConnectionString;

            MySqlConnection dbConnection = new MySqlConnection(strConn);

            //2.創建 Command 對象
            MySqlCommand command = new MySqlCommand(commandConfig.CommandText, dbConnection);
            command.CommandType = CommandType.Text;

            //3.填充參數列表
            foreach (ParameterConfig param in commandConfig.Parameters)
            {
                MySqlParameter parameter = command.CreateParameter();
                parameter.ParameterName = param.Name;
                parameter.DbType = param.DBType;
                parameter.Size = param.Size == 0 ? 4 : param.Size;
                parameter.Direction = ParameterDirection.Input; //默認是輸入參數
                command.Parameters.Add(parameter);
            }
            CustomerCommand customerCmd = new CustomerCommand(command, DataBaseType.MySQL);
            return customerCmd;
        }

        public void AddingParameterPreviouslyRemoved()
        {
            MySqlCommand cmd = new
              MySqlCommand("Insert into sometable(s1, s2) values(?p1, ?p2)");

              MySqlParameter param1 = cmd.CreateParameter();
              param1.ParameterName = "?p1";
              param1.DbType = DbType.String;
              param1.Value = "Ali Gel";

              cmd.Parameters.Add(param1);
              cmd.Parameters.RemoveAt(0);
              cmd.Parameters.Add(param1);
        }

        /// <summary>
        /// Adds the parameters to a MySQL command
        /// </summary>
        /// <param name="commandText">The MySQL query to execute</param>
        /// <param name="parameters">Parameters to pass to the MySQL query</param>
        private static void AddParameters(MySqlCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                return;
            }

            foreach (var param in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = param.Key;
                parameter.Value = param.Value ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }
        }

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

              execSQL("CREATE FUNCTION fnTest(valin int) RETURNS INT " +
              " LANGUAGE SQL DETERMINISTIC BEGIN return valin * 2; END");
              MySqlCommand cmd = new MySqlCommand("fnTest", conn);
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.AddWithValue("?valin", 22);
              MySqlParameter retVal = cmd.CreateParameter();
              retVal.ParameterName = "?retval";
              retVal.MySqlDbType = MySqlDbType.Int32;
              retVal.Direction = ParameterDirection.ReturnValue;
              cmd.Parameters.Add(retVal);
              if (prepare) cmd.Prepare();
              cmd.ExecuteNonQuery();
              Assert.AreEqual(44, cmd.Parameters[1].Value);
        }

        public bool Save(MySqlConnection dbcon, bool forceFullData)
        {
            bool result = true;
            using (Utils.ProgressBlock fixpr = new ProgressBlock(this, STR_SAVING, STR_SAVINGDATA, 1, 0))
            {
                if (dbcon != null)
                {
                    string[] custAttr = Core.Geocaches.CustomAttributes;
                    List<string> activeAttr = new List<string>();
                    using (MySqlCommand cmd = new MySqlCommand("select field_name from geocache_cfields", dbcon))
                    using (MySqlDataReader dr = cmd.ExecuteReader())
                        while (dr.Read())
                        {
                            activeAttr.Add(string.Format("{0}", dr["field_name"]));
                        }

                    foreach (string s in activeAttr)
                    {
                        if (!custAttr.Contains(s) && ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
                        {
                            //drop column not supported!
                        }
                    }
                    //geocache_cfields
                    using (MySqlCommand cmd = new MySqlCommand("delete from geocache_cfields", dbcon))
                        cmd.ExecuteNonQuery();
                    foreach (string s in custAttr)
                    {
                        if (!activeAttr.Contains(s))
                        {
                            using (MySqlCommand cmd = new MySqlCommand(string.Format("insert into geocache_cfields (field_name) values ('{0}')", s), dbcon))
                                cmd.ExecuteNonQuery();
                        }
                        if (!ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
                        {
                            using (MySqlCommand cmd = new MySqlCommand(string.Format("alter table geocache add _{0} text)", s), dbcon))
                                cmd.ExecuteNonQuery();
                        }
                    }

                    //delete geoacches that are not in the list anymore.
                    string[] c = (from string a in _geocachesInDB.Keys select a).ToArray();
                    using (MySqlCommand cmd = dbcon.CreateCommand())
                        for (int i = 0; i < c.Length; i++)
                        {
                            if (Utils.DataAccess.GetGeocache(Core.Geocaches, c[i]) == null)
                            {
                                cmd.CommandText = string.Format("delete from geocache where code='{0}'", c[i]);
                                cmd.ExecuteNonQuery();
                                _geocachesInDB.Remove(c[i]);
                            }
                        }

                    //reset selection
                    using (MySqlCommand cmd = new MySqlCommand("update geocache set selected=0", dbcon))
                        cmd.ExecuteNonQuery();

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.Geocache> gclist = (from Framework.Data.Geocache wp in Core.Geocaches
                                                            where wp.Selected || !wp.Saved
                                                            select wp).ToList();
                    if (gclist.Count > 0)
                    {
                        using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, 0))
                        {
                            string updateSqlFull = "update geocache set [email protected], [email protected], [email protected], lat=[email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlFull = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, shortdescr, shortdescrhtml, longdescr, longdescrhtml, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @shortdescr, @shortdescrhtml, @longdescr, @longdescrhtml, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";

                            string updateSqlShort = "update geocache set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlShort = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";

                            using (MySqlCommand cmd = dbcon.CreateCommand())
                            {
                                cmd.CommandType = CommandType.Text;
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@code";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@name";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@datafromdate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lat";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lon";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@disttocent";
//.........這裏部分代碼省略.........

 /// <summary>
 /// SQLインジェクション対策。Paramに入力された不正な値をエスケープする。
 /// </summary>
 /// <param name="com">Mysqlコマンド文</param>
 /// <param name="ParameterName">プレースホルダ</param>
 /// <param name="type">データの型</param>
 /// <param name="value">エスケープしたい対象</param>
 public void AddMysqlParameter(
     MySqlCommand com,string ParameterName,MySqlDbType type,Object value)
 {
     MySqlParameter param = com.CreateParameter();
     param.ParameterName = ParameterName;
     param.MySqlDbType = type;
     param.Direction = ParameterDirection.Input;
     param.Value = value;
     com.Parameters.Add(param);
 }

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

            execSQL("CREATE TABLE  Test (id int(10) unsigned NOT NULL default '0', " +
                 "val int(10) unsigned default NULL, PRIMARY KEY (id)) " +
                 "ENGINE=InnoDB DEFAULT CHARSET=utf8");
            execSQL("CREATE PROCEDURE spTest (IN pp INTEGER) " +
                      "select * from Test where id > pp ");

            MySqlCommand c = new MySqlCommand("spTest", conn);
            c.CommandType = CommandType.StoredProcedure;
            IDataParameter p = c.CreateParameter();
            p.ParameterName = "?pp";
            p.Value = 10;
            c.Parameters.Add(p);
            c.Prepare();
            using (MySqlDataReader reader = c.ExecuteReader())
            {
                while (reader.Read())
                {

                }
            }
        }

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

            execSQL(@"CREATE TABLE Test(f1 bigint(20) unsigned NOT NULL,
                      PRIMARY KEY(f1)) ENGINE=InnoDB DEFAULT CHARSET=utf8");

            execSQL(@"CREATE PROCEDURE spTest(in _val bigint unsigned)
                      BEGIN insert into  Test set f1=_val; END");

            DbCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "spTest";
            DbParameter param = cmd.CreateParameter();
            param.DbType = DbType.UInt64;
            param.Direction = ParameterDirection.Input;
            param.ParameterName = "?_val";
            ulong bigval = long.MaxValue;
            bigval += 1000;
            param.Value = bigval;
            cmd.Parameters.Add(param);
            cmd.ExecuteNonQuery();
        }

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

            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE  Test (id int(10) unsigned NOT NULL default '0', " +
                 "val int(10) unsigned default NULL, PRIMARY KEY (id)) " +
                 "ENGINE=InnoDB DEFAULT CHARSET=utf8");
            execSQL("CREATE PROCEDURE spTest (IN pp INTEGER) " +
                      "select * from Test where id > pp ");

            MySqlCommand c = new MySqlCommand("spTest", conn);
            c.CommandType = CommandType.StoredProcedure;
            IDataParameter p = c.CreateParameter();
            p.ParameterName = "?pp";
            p.Value = 10;
            c.Parameters.Add(p);
            c.Prepare();
            MySqlDataReader reader = null;
            try
            {
                reader = c.ExecuteReader();
                while (reader.Read())
                {

                }
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
            finally
            {
                if (reader != null)
                    reader.Close();
            }
        }

    public void UnTypedParameterBeingReused()
    {
      MySqlCommand cmd = new MySqlCommand("INSERT INTO Test (id, dt) VALUES (?id, ?dt)", conn);
      cmd.Parameters.AddWithValue("?id", 1);
      MySqlParameter p = cmd.CreateParameter();
      p.ParameterName = "?dt";
      p.Value = DBNull.Value;
      cmd.Parameters.Add(p);
      cmd.ExecuteNonQuery();

      cmd.Parameters[0].Value = 2;
      p.Value = DateTime.Now;
      cmd.ExecuteNonQuery();

      cmd.CommandText = "SELECT * FROM Test";
      cmd.Parameters.Clear();
      using (MySqlDataReader reader = cmd.ExecuteReader())
      {
        reader.Read();
        Assert.IsTrue(reader.IsDBNull(2));
        reader.Read();
        Assert.IsFalse(reader.IsDBNull(2));
        Assert.IsFalse(reader.Read());
      }
    }

        private static void AdicionarParamatros(MySqlCommand cmdComando, Dictionary<string, object> parametros)
        {
            if (parametros == null)
                return;

            foreach (var item in parametros)
            {
                var parametro = cmdComando.CreateParameter();
                parametro.ParameterName = item.Key;
                parametro.Value = item.Value ?? DBNull.Value;
                cmdComando.Parameters.Add(parametro);
            }
        }