C# VzfSqlCommand.ExecuteNonQuery方法代码示例

        /// <summary>
        /// The add user to role.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        /// <param name="roleName">
        /// The role name.
        /// </param>
        public void AddUserToRole(string connectionStringName, object appName, object userName, object roleName)
        {
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_RoleName", roleName));

                sc.CommandText.AppendObjectQuery("prov_role_addusertorole", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// The upgrade membership.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="I_PREVIOUSVERSION">
        /// The previous version.
        /// </param>
        /// <param name="I_NEWVERSION">
        /// The new version.
        /// </param>
        public void UpgradeMembership(string connectionStringName, int I_PREVIOUSVERSION, int I_NEWVERSION)
        {
            // connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                //  sc.DataSource.ProviderName
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_PREVIOUSVERSION", I_PREVIOUSVERSION));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_NEWVERSION", I_NEWVERSION));

                sc.CommandText.AppendObjectQuery("p_upgrade", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }            
        }

        /// <summary>
        /// Database Action - Create Role
        /// </summary>
        /// <param name="appName">Application Name</param>
        /// <param name="roleName">Role Name</param>
        /// <returns></returns>
        public static void __CreateRole(string connectionStringName, object appName, object roleName)
        {
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_applicationname", appName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_rolename", roleName));
                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newroleguid", Guid.NewGuid()));

                sc.CommandText.AppendObjectQuery("prov_role_createrole", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }
        }

        public void ChangePasswordQuestionAndAnswer(string connectionStringName, string appName, string userName, string passwordQuestion, string passwordAnswer)
        {
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
                //  sc.DataSource.ProviderName
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordQuestion", passwordQuestion));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", passwordAnswer));

                sc.CommandText.AppendObjectQuery("prov_changepasswordquestionandanswer", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }     
        }

        public void ChangePassword(string connectionStringName, string appName, string userName, string newPassword, string newSalt, int passwordFormat, string newPasswordAnswer)
        {
            using (var sc = new VzfSqlCommand(connectionStringName))
            { 
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
                //  sc.DataSource.ProviderName
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserName", userName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_Password", newPassword));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordSalt", newSalt));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordFormat", passwordFormat));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", newPasswordAnswer));

                sc.CommandText.AppendObjectQuery("prov_changepassword", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }             
        }

    /// <summary>
    /// Database Action - Delete Role
    /// </summary>
    /// <param name="appName">
    /// Application Name
    /// </param>
    /// <param name="roleName">
    /// Role Name
    /// </param>
    /// <param name="deleteOnlyIfRoleIsEmpty">
    /// The delete Only If Role Is Empty.
    /// </param>
    /// <returns>
    /// Status as integer
    /// </returns>
    public int DeleteRole(string connectionStringName, object appName, object roleName, object deleteOnlyIfRoleIsEmpty)
    {   
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@RoleName", roleName));
          sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@DeleteOnlyIfRoleIsEmpty", deleteOnlyIfRoleIsEmpty));
          sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@ReturnValue", null, ParameterDirection.ReturnValue));

          sc.CommandText.AppendObjectQuery("prov_role_deleterole", connectionStringName);
          sc.ExecuteNonQuery(CommandType.StoredProcedure);
          return Convert.ToInt32(sc.Parameters["@ReturnValue"].Value);         
      }        
    }

        public static void __AddProfileColumn(string connectionStringName, string name, string type, int size)
        {
            // get column type...
            type = DataTypeMappers.typeToDbValueMap(name, type, size);
          

            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                string sql = String.Format("ALTER TABLE {0} ADD  {1}  {2} ", SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_profile", connectionStringName), name, type);
                sc.CommandText.AppendQuery(sql);
                sc.ExecuteNonQuery(CommandType.Text, false);
            }
        }

    /// <summary>
    /// The update user.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="user">
    /// The user.
    /// </param>
    /// <param name="requiresUniqueEmail">
    /// The requires unique email.
    /// </param>
    /// <returns>
    /// The update user.
    /// </returns>
    public int UpdateUser(string connectionStringName, [NotNull] object appName, [NotNull] MembershipUser user, bool requiresUniqueEmail)
    {
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserKey", user.ProviderUserKey));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", user.UserName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Email", user.Email));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Comment", user.Comment));
          sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@IsApproved", user.IsApproved));
          sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@LastLogin", user.LastLoginDate));
          sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@LastActivity", user.LastActivityDate));
          sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@UniqueEmail", requiresUniqueEmail));
          var p = sc.CreateParameter(DbType.Boolean, "@ReturnValue", null, ParameterDirection.ReturnValue);       
       
          sc.Parameters.Add(p);

          sc.CommandText.AppendObjectQuery("prov_updateuser", connectionStringName);
          sc.ExecuteNonQuery(CommandType.StoredProcedure);
          return Convert.ToInt32(p.Value);
      }        
    }

    /// <summary>
    /// The reset password.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="userName">
    /// The user name.
    /// </param>
    /// <param name="password">
    /// The password.
    /// </param>
    /// <param name="passwordSalt">
    /// The password salt.
    /// </param>
    /// <param name="passwordFormat">
    /// The password format.
    /// </param>
    /// <param name="maxInvalidPasswordAttempts">
    /// The max invalid password attempts.
    /// </param>
    /// <param name="passwordAttemptWindow">
    /// The password attempt window.
    /// </param>
    public void ResetPassword(string connectionStringName, [NotNull] string appName, [NotNull] string userName, [NotNull] string password, [NotNull] string passwordSalt, 
      int passwordFormat, 
      int maxInvalidPasswordAttempts, 
      int passwordAttemptWindow)
    {
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));

          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@Password", password));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordSalt", passwordSalt));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@PasswordFormat", passwordFormat));
          sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@MaxInvalidAttempts", maxInvalidPasswordAttempts));
          sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "@PasswordAttemptWindow", passwordAttemptWindow));
          sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "@CurrentTimeUtc", DateTime.UtcNow));

          sc.CommandText.AppendObjectQuery("prov_resetpassword", connectionStringName);

          sc.ExecuteNonQuery(CommandType.StoredProcedure);
      }
    }

    /// <summary>
    /// The delete user.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="username">
    /// The username.
    /// </param>
    /// <param name="deleteAllRelatedData">
    /// The delete all related data.
    /// </param>
    public void DeleteUser(string connectionStringName, [NotNull] string appName, [NotNull] string userName, bool deleteAllRelatedData)
    {
      using (var sc = new VzfSqlCommand(connectionStringName))
      {
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@ApplicationName", appName));
          sc.Parameters.Add(sc.CreateParameter(DbType.String, "@UserName", userName));
          sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "@DeleteAllRelated", deleteAllRelatedData));

          sc.CommandText.AppendObjectQuery("prov_deleteuser", connectionStringName);

          sc.ExecuteNonQuery(CommandType.StoredProcedure);
      } 
    }

//.........这里部分代码省略.........
                    case SqlDbAccess.MsSql:
                        chunk = MsProfile.GetDbTypeAndSizeFromString(chunk);
                        break;
                    case SqlDbAccess.Npgsql:
                        chunk = PgProfile.GetDbTypeAndSizeFromString(chunk);
                        break;
                    case SqlDbAccess.MySql:
                        chunk = MySqlProfile.GetDbTypeAndSizeFromString(chunk);
                        break;
                    case SqlDbAccess.Firebird:
                        chunk = FbProfile.GetDbTypeAndSizeFromString(chunk);
                        break;
                    default:
                        throw new ArgumentOutOfRangeException(dataEngine);
                }

                // get the datatype and ignore case...
                DbType dbType = (DbType)Enum.Parse(typeof(DbType), chunk[1], true);

                if (chunk.Length > 2)
                {
                    // handle size...
                    if (!int.TryParse(chunk[2], out size))
                    {
                        throw new ArgumentException("Unable to parse as integer: " + chunk[2]);
                    }
                }


                // default the size to 256 if no size is specified
                if (dbType == DbType.String && size == -1)
                {
                    size = 256;
                }

                settingsColumnsList.Add(new SettingsPropertyColumn(value.Property, dbType, size));
            }

            // getting current profile table colimns...
            DataTable structure = CommonDb.GetProfileStructure(SqlDbAccess.GetConnectionStringName(mid, string.Empty), Constants.SpecialObjectNames.UserProfileMirrorTable);

            // verify all the columns are there...
            foreach (var column in settingsColumnsList)
            {
                // see if this column exists and add it if required
                if (structure.Columns.Contains(column.Settings.Name)) continue;

                string dataTypeName = column.DataType.ToString();

                // if not, create it...
                // parse custom provider data...
                string sqlAddColumn;
                switch (dataEngine)
                {
                    case SqlDbAccess.MsSql:
                        sqlAddColumn = MsProfile.AddProfileColumn(
                            column.Settings.Name,
                            dataTypeName,
                            column.Size,
                            SqlDbAccess.GetVzfObjectName(tableName, mid));
                        break;
                    case SqlDbAccess.Npgsql:
                        sqlAddColumn = PgProfile.AddProfileColumn(
                            column.Settings.Name,
                            dataTypeName,
                            column.Size,
                            SqlDbAccess.GetVzfObjectName(tableName, mid));
                        break;
                    case SqlDbAccess.MySql:
                        sqlAddColumn = MySqlProfile.AddProfileColumn(
                            column.Settings.Name,
                            dataTypeName,
                            column.Size,
                            SqlDbAccess.GetVzfObjectName(tableName, mid));
                        break;
                    case SqlDbAccess.Firebird:
                        sqlAddColumn = FbProfile.AddProfileColumn(
                            column.Settings.Name,
                            dataTypeName,
                            column.Size,
                            SqlDbAccess.GetVzfObjectName(tableName, mid));
                        break;
                    default:
                        throw new ArgumentOutOfRangeException(dataEngine);
                }

                if (sqlAddColumn.IsNotSet())
                {
                    continue;
                }

                using (var sc = new VzfSqlCommand(mid))
                {
                    sc.CommandText.AppendQuery(sqlAddColumn);
                    sc.ExecuteNonQuery(CommandType.Text, true);
                }
            }

            return settingsColumnsList;
        }

//.........这里部分代码省略.........
                    {
                        if (!collection[column.Settings.Name].IsDirty) return;

                        var nameParam = collection[column.Settings.Name].Name;
                        var valParam = collection[column.Settings.Name].PropertyValue;

                        nameParam = "@i_" + nameParam;

                        paramData.Add(new Tuple<DbType, string, object>(column.DataType, nameParam, valParam));

                        valueStr.Append(nameParam);
                        valueStr.Append(",");

                        columnStr.Append(column.Settings.Name);
                        columnStr.Append(",");

                        setStr.Append(column.Settings.Name);
                        setStr.Append("=");
                        setStr.Append(nameParam);
                        setStr.Append(",");
                    });

            // first predefined parameter without separator
            columnStr.Append("LastUpdatedDate ");
            valueStr.Append("@i_LastUpdatedDate");
            setStr.Append("[email protected]_LastUpdatedDate");

            paramData.Add(new Tuple<DbType, string, object>(DbType.DateTime, "@i_LastUpdatedDate", DateTime.UtcNow));

            columnStr.Append(",LastActivity ");
            valueStr.Append(",@i_LastActivity");
            setStr.Append(",[email protected]_LastActivity");

            paramData.Add(new Tuple<DbType, string, object>(DbType.DateTime, "@i_LastActivity", DateTime.UtcNow));

            columnStr.Append(",ApplicationName ");
            valueStr.Append(",@i_ApplicationName");
            setStr.Append(",[email protected]_ApplicationName");

            columnStr.Append(",IsAnonymous ");
            valueStr.Append(",@i_IsAnonymous");
            setStr.Append(",[email protected]_IsAnonymous");

            paramData.Add(new Tuple<DbType, string, object>(DbType.Boolean, "@i_IsAnonymous", false));

            columnStr.Append(",UserName ");
            valueStr.Append(",@i_UserName");
            setStr.Append(",[email protected]_UserName");

            paramData.Add(new Tuple<DbType, string, object>(DbType.String, "@i_UserName", userName));

            paramData.Add(new Tuple<DbType, string, object>(DbType.Int32, "@i_UserID", userId));

            paramData.Add(new Tuple<DbType, string, object>(DbType.String, "@i_ApplicationName", appname));

            // start saving...             
            switch (dataEngine)
            {
                case SqlDbAccess.MsSql:
                    sql = MsProfile.SetProfileProperties(
                        setStr.ToString(),
                        columnStr.ToString(),
                        valueStr.ToString(),
                        profileExists);
                    break;
                case SqlDbAccess.Npgsql:
                    sql = PgProfile.SetProfileProperties(
                        setStr.ToString(),
                        columnStr.ToString(),
                        valueStr.ToString(),
                        profileExists);
                    break;
                case SqlDbAccess.MySql:
                    sql = MySqlProfile.SetProfileProperties(
                        setStr.ToString(),
                        columnStr.ToString(),
                        valueStr.ToString(),
                        profileExists);
                    break;
                case SqlDbAccess.Firebird:
                    sql = FbProfile.SetProfileProperties(
                        setStr.ToString(),
                        columnStr.ToString(),
                        valueStr.ToString(),
                        profileExists);
                    break;
                default:
                    throw new ArgumentOutOfRangeException(dataEngine);
            }

            using (var sc = new VzfSqlCommand(mid))
            {
                // add parameters in a loop
                paramData.ForEach(
                    parameter => sc.Parameters.Add(sc.CreateParameter(parameter.Item1, parameter.Item2, parameter.Item3)));

                sc.CommandText.AppendQuery(sql.FormatWith(SqlDbAccess.GetVzfObjectName(tableName, mid)));
                sc.ExecuteNonQuery(CommandType.Text, true);
            }
        }

        /// <summary>
        /// The replace_words_save.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="boardId">
        /// The board id.
        /// </param>
        /// <param name="id">
        /// The id.
        /// </param>
        /// <param name="badword">
        /// The badword.
        /// </param>
        /// <param name="goodword">
        /// The goodword.
        /// </param>
        public static void replace_words_save(int? mid, object boardId, object id, object badword, object goodword)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_BoardID", boardId));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_ID", id));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_BadWord", badword));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_GoodWord", goodword));

                sc.CommandText.AppendObjectQuery("replace_words_save", mid);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// The replace_words_delete.
        /// </summary>
        /// <param name="mid">
        /// The mid.
        /// </param>
        /// <param name="id">
        /// The id.
        /// </param>
        public static void replace_words_delete(int? mid, object id)
        {
            using (var sc = new VzfSqlCommand(mid))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_ID", id));

                sc.CommandText.AppendObjectQuery("replace_words_delete", mid);
                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            }
        }

        /// <summary>
        /// The reset password.
        /// </summary>
        /// <param name="connectionString">
        /// The connection string.
        /// </param>
        /// <param name="appName">
        /// The app name.
        /// </param>
        /// <param name="userName">
        /// The user name.
        /// </param>
        /// <param name="password">
        /// The password.
        /// </param>
        /// <param name="passwordSalt">
        /// The password salt.
        /// </param>
        /// <param name="passwordFormat">
        /// The password format.
        /// </param>
        /// <param name="maxInvalidPasswordAttempts">
        /// The max invalid password attempts.
        /// </param>
        /// <param name="passwordAttemptWindow">
        /// The password attempt window.
        /// </param>
        public void ResetPassword(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, int maxInvalidPasswordAttempts, int passwordAttemptWindow)
        {
            // connectionStringName = SqlDbAccess.GetConnectionStringNameFromConnectionString(connectionStringName);
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_APPLICATIONNAME", appName));

                sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_USERNAME", userName));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_password", password));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordsalt", passwordSalt));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordformat", passwordFormat));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_maxinvalidattempts", maxInvalidPasswordAttempts));
                sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_passwordattemptwindow", passwordAttemptWindow));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_currenttimeutc", DateTime.UtcNow));

                sc.CommandText.AppendObjectQuery("P_resetpassword", connectionStringName);

                sc.ExecuteNonQuery(CommandType.StoredProcedure);
            } 
        }