C# MySqlClient.MySqlParameter類代碼示例

 public int Add(Maticsoft.Email.Model.MailConfig model)
 {
     StringBuilder builder = new StringBuilder();
     builder.Append("insert into Accounts_MailConfig(");
     builder.Append("UserID,Mailaddress,Username,Password,SMTPServer,SMTPPort,SMTPSSL,POPServer,POPPort,POPSSL)");
     builder.Append(" values (");
     builder.Append("?UserID,?Mailaddress,?Username,?Password,?SMTPServer,?SMTPPort,?SMTPSSL,?POPServer,?POPPort,?POPSSL)");
     builder.Append(";select ??IDENTITY");
     MySqlParameter[] cmdParms = new MySqlParameter[] { new MySqlParameter("?UserID", MySqlDbType.Int32, 4), new MySqlParameter("?Mailaddress", MySqlDbType.VarChar, 100), new MySqlParameter("?Username", MySqlDbType.VarChar, 50), new MySqlParameter("?Password", MySqlDbType.VarChar, 50), new MySqlParameter("?SMTPServer", MySqlDbType.VarChar, 50), new MySqlParameter("?SMTPPort", MySqlDbType.Int32, 4), new MySqlParameter("?SMTPSSL", MySqlDbType.Int16, 2), new MySqlParameter("?POPServer", MySqlDbType.VarChar, 50), new MySqlParameter("?POPPort", MySqlDbType.Int32, 4), new MySqlParameter("?POPSSL", MySqlDbType.Int16, 2) };
     cmdParms[0].Value = model.UserID;
     cmdParms[1].Value = model.Mailaddress;
     cmdParms[2].Value = model.Username;
     cmdParms[3].Value = model.Password;
     cmdParms[4].Value = model.SMTPServer;
     cmdParms[5].Value = model.SMTPPort;
     cmdParms[6].Value = model.SMTPSSL ? 1 : 0;
     cmdParms[7].Value = model.POPServer;
     cmdParms[8].Value = model.POPPort;
     cmdParms[9].Value = model.POPSSL ? 1 : 0;
     object single = DbHelperMySQL.GetSingle(builder.ToString(), cmdParms);
     if (single == null)
     {
         return 1;
     }
     return Convert.ToInt32(single);
 }

        public static int CountActiveByProduct(Guid siteGuid, Guid productGuid)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT  Count(*) ");
            sqlCommand.Append("FROM	sts_MembershipTicket ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("SiteGuid = ?SiteGuid ");
            sqlCommand.Append("AND ProductGuid = ?ProductGuid ");
            sqlCommand.Append("AND OrderStatusGuid = '70443443-f665-42c9-b69f-48cbf011a14b' "); //fulfillable status
            sqlCommand.Append("AND ?CurrentTimeUtc < TIMESTAMPADD(DAY, GracePeriodDays, EndDateUtc) "); //before end of grace period
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[3];

            arParams[0] = new MySqlParameter("?SiteGuid", MySqlDbType.VarChar, 36);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = siteGuid.ToString();

            arParams[1] = new MySqlParameter("?ProductGuid", MySqlDbType.VarChar, 36);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = productGuid.ToString();

            arParams[2] = new MySqlParameter("?CurrentTimeUtc", MySqlDbType.DateTime);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = DateTime.UtcNow;

            return Convert.ToInt32(MySqlHelper.ExecuteScalar(
                GetReadConnectionString(),
                sqlCommand.ToString(),
                arParams));
        }

        /// <summary>
        /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value
        /// greater than the provided trackOrder.
        /// </summary>
        /// <param name="playerID">The ID of the Player.</param>
        /// <param name="trackOrder">The TrackOrder value.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_MediaTrack ");
            sqlCommand.Append("SET TrackOrder = TrackOrder - 1 ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("PlayerID = ?PlayerID ");
            sqlCommand.Append("AND TrackOrder > ?TrackOrder ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[2];

            arParams[0] = new MySqlParameter("?TrackOrder", MySqlDbType.Int32);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = trackOrder;

            arParams[1] = new MySqlParameter("?PlayerID", MySqlDbType.Int32);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = playerId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return rowsAffected;
        }

        public static bool DeleteByModule(int moduleId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_MediaFile ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("FileID  ");
            sqlCommand.Append("IN (");
            sqlCommand.Append("SELECT FileID FROM mp_MediaFile WHERE TrackID IN (");
            sqlCommand.Append("SELECT TrackID FROM mp_MediaTrack WHERE PlayerID IN (");
            sqlCommand.Append("SELECT PlayerID FROM mp_MediaPlayer WHERE ModuleID = ?ModuleID");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[1];

            arParams[0] = new MySqlParameter("?ModuleID", MySqlDbType.Int32);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = moduleId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return (rowsAffected > 0);
        }

        public static Course fetchCoursebyId(int _courseid)
        {
            dbManager db = new dbManager();
                Course cs = null;

                string sqlCommand;
                MySqlParameter[] param = new MySqlParameter[1];

                param[0] = new MySqlParameter("@courseid", _courseid);

                sqlCommand = "select * from Course where [email protected]";

                DataTable ds = db.fetchRows(sqlCommand, param);

                int i = 0;

                foreach (DataRow dr in ds.Rows)
                {
                    cs = new Course(Convert.ToInt32(dr["courseid"].ToString()),
                                        dr["coursename"].ToString(),
                                        (DateTime)dr["startdate"],
                                        (DateTime)dr["enddate"],
                                        dr["classondays"].ToString(),
                                        (TimeSpan)dr["classstarttime"],
                                        (TimeSpan)dr["classendtime"]);
                    i++;
                }

                return cs;
        }

 public static MySqlParameter CreateParameter(ParameterDirection direction, string paramName, MySqlDbType dbtype, int size, object value)
 {
     MySqlParameter param = new MySqlParameter(paramName, dbtype, size);
     param.Value = value;
     param.Direction = direction;
     return param;
 }

 public GZScenery queryAll(long gzSceneryID)
 {
     conn.Open();
     GZScenery gzScenery = new GZScenery();
     string sql = "select * from gzScenery where gzSceneryID = ?gzSceneryID";
     MySqlCommand cmd = new MySqlCommand(sql, conn);
     MySqlParameter[] parameters = new MySqlParameter[1];
     parameters[0] = new MySqlParameter("?gzSceneryID", MySqlDbType.Int64);
     parameters[0].Value = gzSceneryID;
     cmd.Parameters.AddRange(parameters);
     MySqlDataReader reader = cmd.ExecuteReader();
     reader.Read();
     gzScenery.GZSceneryLinePlay = reader[1].ToString();
     gzScenery.GZSceneryIntroduce = reader[2].ToString();
     gzScenery.GZSceneryName = reader[3].ToString();
     gzScenery.GZSceneryCity = reader[4].ToString();
     gzScenery.GZSceneryDateTime = reader[5].ToString();
     gzScenery.GZSceneryTripDay = reader[6].ToString();
     gzScenery.GZSceneryAdultPrice = reader[7].ToString();
     gzScenery.GZSceneryChildren = reader[8].ToString();
     gzScenery.GZScenerySingleRoom = reader[9].ToString();
     gzScenery.GZSceneryTravelDetails = reader[10].ToString();
     gzScenery.GZSceneryGridInformation = reader[11].ToString();
     gzScenery.GZSceneryCost = reader[12].ToString();
     return gzScenery;
 }

        public DbDataReader GetSiteSettingsExList(int siteId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT  e.* ");

            sqlCommand.Append("FROM	mp_SiteSettingsEx e ");

            sqlCommand.Append("JOIN ");
            sqlCommand.Append("mp_SiteSettingsExDef d ");
            sqlCommand.Append("ON ");
            sqlCommand.Append("e.KeyName = d.KeyName ");
            sqlCommand.Append("AND e.GroupName = d.GroupName ");

            sqlCommand.Append("WHERE ");
            sqlCommand.Append("e.SiteID = ?SiteID ");

            sqlCommand.Append("ORDER BY d.GroupName, d.SortOrder ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[1];

            arParams[0] = new MySqlParameter("?SiteID", MySqlDbType.Int32);
            arParams[0].Value = siteId;

            return AdoHelper.ExecuteReader(
                readConnectionString,
                sqlCommand.ToString(),
                arParams);

        }

 public List<LevelReplies> queryLevelReplies(long postID)
 {
     sqlconn.Open();
       List<LevelReplies> levelRepliesList = new List<LevelReplies>();
       string sql = "select * from levelReplies where postID=?postID";
       MySqlCommand cmd = new MySqlCommand(sql, sqlconn);
       MySqlParameter[] parameters = new MySqlParameter[1];
       parameters[0] = new MySqlParameter("?postID", MySqlDbType.Int64);
       parameters[0].Value = postID;
       cmd.Parameters.AddRange(parameters);
       MySqlDataReader reader = cmd.ExecuteReader();
       while (reader.Read())
       {
           LevelReplies levelReplies = new LevelReplies();
           levelReplies.LevelRepliesID = Convert.ToInt64(reader[0]);
           levelReplies.PostID = Convert.ToInt64(reader[1]);
           levelReplies.FirstRepliesID = Convert.ToInt64(reader[2]);
           levelReplies.UserName = reader[3].ToString();
           levelReplies.ToUserName = reader[4].ToString();
           levelReplies.Content = reader[5].ToString();
           levelReplies.Time = reader[6].ToString();
           levelRepliesList.Add(levelReplies);
       }
       reader.Close();
       sqlconn.Close();
       return levelRepliesList;
 }

        private HassiumObject query(HassiumObject[] args)
        {
            MySqlCommand command = new MySqlCommand(null, Value);
            command.CommandText = "INSERT INTO " + args[0] + "(";
            HassiumObject[] vals = ((HassiumArray)args[1]).Value;
            command.CommandText += vals[0].ToString();
            for (int x = 1; x < vals.Length; x++)
                command.CommandText += ", " + vals[x].ToString();
            command.CommandText += ") VALUES (";
            command.CommandText += "@" + vals[0].ToString();
            for (int x = 1; x < vals.Length; x++)
            command.CommandText += ", @" + vals[x].ToString();
            command.CommandText += ")";

            HassiumObject[] lits = ((HassiumArray)args[2]).Value;
            for (int x = 0; x < lits.Length; x++) {
            MySqlParameter param = new MySqlParameter("@" + vals[x].ToString(), lits[x].ToString());
            param.Value = lits[x].ToString();
            command.Parameters.Add(param);
            }
            // Console.WriteLine(command.CommandText);

            command.Prepare();
            command.ExecuteNonQuery();

            return null;
        }

        public static bool Delete(
            string loginProvider,
            string providerKey,
            string userId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_UserLogins ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("LoginProvider = ?LoginProvider AND ");
            sqlCommand.Append("ProviderKey = ?ProviderKey AND ");
            sqlCommand.Append("UserId = ?UserId ");
            sqlCommand.Append(";");

            MySqlParameter[] arParams = new MySqlParameter[3];

            arParams[0] = new MySqlParameter("?LoginProvider", MySqlDbType.VarChar, 128);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = loginProvider;

            arParams[1] = new MySqlParameter("?ProviderKey", MySqlDbType.VarChar, 128);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = providerKey;

            arParams[2] = new MySqlParameter("?UserId", MySqlDbType.VarChar, 128);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = userId;

            int rowsAffected = MySqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);
            return (rowsAffected > 0);
        }

        public void DeleteCategorie(int categorie_id)
        {
            try
            {
                conn.Open();

                string insertString = @"delete from categorie where [email protected]_id";
                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter sportParam = new MySqlParameter("@categorie_id", MySqlDbType.Int32);

                sportParam.Value = categorie_id;

                cmd.Parameters.Add(sportParam);

                cmd.Prepare();

                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.Write("categorie niet verwijderd: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        ///<summary>
        ///</summary>
        ///<param name="paramName"></param>
        ///<param name="dbType"></param>
        ///<param name="size"></param>
        ///<param name="direction"></param>
        ///<param name="value"></param>
        ///<returns></returns>
        ///<exception cref="ArgumentOutOfRangeException"></exception>
        public static MySqlParameter MakeParam(string paramName, MySqlDbType dbType, int size, ParameterDirection direction, object value)
        {
            MySqlParameter sqlParameter = null;
            try
            {
                paramName = paramName ?? string.Empty;

                //modify reason:驗證花時比較大
                //if (!MathUtils.IsMachVarName(paramName))
                //{
                //    throw new ArgumentOutOfRangeException("paramName", "參數名格式不正確");
                //}
                if (size > 0)
                {
                    sqlParameter = new MySqlParameter(FormatParamName(paramName), dbType, size);
                }
                else
                {
                    sqlParameter = new MySqlParameter(FormatParamName(paramName), dbType);
                }
                sqlParameter.Direction = direction;
                if (direction != ParameterDirection.Output || value != null)
                {
                    sqlParameter.Value = value;
                }
            }
            catch (Exception ex)
            {
                TraceLog.WriteError("{0}", ex);
            }
            return sqlParameter;
        }

        public void DeleteInschrijving(int studentID, int evenementID)
        {
            MySqlTransaction trans = null;
            try
            {
                conn.Open();
                trans = conn.BeginTransaction();
                string insertString = @"delete from inschrijving where [email protected] and [email protected]";

                MySqlCommand cmd = new MySqlCommand(insertString, conn);
                MySqlParameter studentidParam = new MySqlParameter("@studentid", MySqlDbType.Int32);
                MySqlParameter evenementidParam = new MySqlParameter("@evenementid", MySqlDbType.Int32);

                studentidParam.Value = studentID;
                evenementidParam.Value = evenementID;

                cmd.Parameters.Add(studentidParam);
                cmd.Parameters.Add(evenementidParam);
                cmd.Prepare();
                cmd.ExecuteNonQuery();

                trans.Commit();

            }
            catch (Exception e)
            {
                trans.Rollback();
                Console.Write("Inschrijving niet verwijderd: " + e);
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        private static void RunSqlQueryWithReturnId(string queryText, Dictionary<string, object> parametters, CommandType commandType, Action<int> action)
        {
            using (MySqlConnection oConn = new MySqlConnection(dbSetting.Default.MySqlConnectionString))
            {
                using (MySqlCommand oRS = oConn.CreateCommand())
                {
                    oConn.Open();
                    oRS.CommandType = commandType;
                    oRS.CommandText = queryText;
                    if (parametters != null && parametters.Count > 0)
                    {
                        foreach (var parametter in parametters)
                        {
                            oRS.Parameters.AddWithValue(parametter.Key, parametter.Value);
                        }
                    }

                    // output parameters
                    MySqlParameter param = new MySqlParameter("@ID", MySqlDbType.Int16);
                    param.Direction = ParameterDirection.Output;
                    oRS.Parameters.Add(param);

                    oRS.ExecuteNonQuery();

                    if (action != null)
                    {
                        action(int.Parse(oRS.LastInsertedId.ToString()));
                    }
                }
            }
        }