C# VzfSqlCommand类代码示例

        public static void __CreateUser(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
        {          
            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.ToString()));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_email", email));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordquestion", passwordQuestion));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_passwordanswer", passwordAnswer));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isapproved", isApproved));
                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));
                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newuserkey", Guid.NewGuid()));
                sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_utctimestamp", DateTime.UtcNow));

                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userkey", providerUserKey, ParameterDirection.InputOutput));               

                sc.CommandText.AppendObjectQuery("prov_createuser", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure, true);
                providerUserKey = sc.Parameters["i_userkey"].Value;
            }     
        }

 /// <summary>
 /// Deletes the access mask.
 /// </summary>
 /// <param name="mid">
 /// The module id.
 /// </param>
 /// <param name="accessMaskId">
 /// The access mask id. 
 /// </param>
 /// <returns> 
 /// A <see cref="T:System.Boolean"/> with true if access mask was deleted and false if deletion failed.
 /// </returns>
 public static bool accessmask_delete(int? mid, object accessMaskId)
 {                
     using (var sc = new VzfSqlCommand(mid))
     {   
         sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_AccessMaskID", accessMaskId));
       
         sc.CommandText.AppendObjectQuery("accessmask_delete", mid);
         return Convert.ToBoolean(sc.ExecuteScalar(CommandType.StoredProcedure));
     }
 }

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

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

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

        public void CreateUser(string connectionStringName, string appName, string userName, string password, string passwordSalt, int passwordFormat, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey)
        {
            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.ToString()));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_Email", email));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordQuestion", passwordQuestion));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_PasswordAnswer", passwordAnswer));
                sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_IsApproved", isApproved));
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserKey", providerUserKey, ParameterDirection.InputOutput));
               // sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "I_UTCTIMESTAMP", DateTime.UtcNow));

                sc.CommandText.AppendObjectQuery("prov_createuser", connectionStringName);
                sc.ExecuteNonQuery(CommandType.StoredProcedure, true);
                providerUserKey = sc.Parameters["i_UserKey"].Value;
            }     
        }

    /// <summary>
    /// The unlock user.
    /// </summary>
    /// <param name="appName">
    /// The app name.
    /// </param>
    /// <param name="userName">
    /// The user name.
    /// </param>
    public void UnlockUser(string connectionStringName, [NotNull] string appName, [NotNull] string userName)
    {
      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.CommandText.AppendObjectQuery("prov_unlockuser", connectionStringName);

          sc.ExecuteNonQuery(CommandType.StoredProcedure);
      } 
    }

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

    /// <summary>
    /// Database Action - Add User to Role
    /// </summary>
    /// <param name="appName">
    /// Application Name
    /// </param>
    /// <param name="userName">
    /// User Name
    /// </param>
    /// <param name="roleName">
    /// Role Name
    /// </param>
    /// <returns>
    /// DataTable with user information
    /// </returns>
    public DataTable IsUserInRole(string connectionStringName, object appName, object userName, object roleName)
    {
      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, "@RoleName", roleName));

          sc.CommandText.AppendObjectQuery("prov_role_isuserinrole", connectionStringName);
          return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);
      }
    }

        /*
        public  void ValidateAddColumnInProfile( string columnName, NpgsqlTypes.NpgsqlDbType columnType )
        {
            NpgsqlCommand cmd = new NpgsqlCommand( sprocName );
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add( "@ApplicationName", appName );
            cmd.Parameters.Add( "@Username", username );
            cmd.Parameters.Add( "@IsUserAnonymous", isAnonymous );

            return cmd;
        }
        */

        public static void __SetPropertyValues(
            string connectionStringName,
            SettingsContext context,
            SettingsPropertyValueCollection collection,
            List<SettingsPropertyColumn> settingsColumnsList)
        {
            bool isAuthenticated = (bool)context["IsAuthenticated"];
            string appName = (string)context["ApplicationName"];

            // sc.Add("IsAnonymous", isAnonymous);
            // sc.Add("LastActivityDate", lastActivityDate);
            if (context["UserID"] == null)
            {
                return;
            }

            var userid = (Guid)context["UserID"];
            if (collection.Count < 1)
            {
                return;
            }

            string index = string.Empty;
            string stringData = string.Empty;
            byte[] binaryData = null;
            int count = EncodeProfileData(collection, isAuthenticated, ref index, ref stringData, ref binaryData);
            if (count < 1)
            {
                return;
            }

            // save the encoded profile data to the database

             // using (TransactionScope ts = new TransactionScope())
             // {

             // either create a new user or fetch the existing user id
             Guid userId = SchemaManager.CreateOrFetchUserId(userid, isAuthenticated);
             bool profileExists = false;
    
             string table =
                              SqlDbAccess.GetVzfObjectNameFromConnectionString("prov_profile", connectionStringName);
             using (var sc1 = new VzfSqlCommand(connectionStringName))
             {

                 // cmd.Parameters.Add(new FbParameter("@I_USERID", FbDbType.VarChar)).Value = userID;
                 sc1.CommandText.AppendQuery(String.Format("SELECT COUNT(1) FROM {0} WHERE userid ='{1}';", table, new Guid(userId.ToString())));

                 profileExists = Convert.ToBoolean(sc1.ExecuteScalar(CommandType.Text, false));
                 sc1.CommandText.Clear();
             }

             var mu = Membership.GetUser(userId);

             if (profileExists)
             {
                 using (var sc = new VzfSqlCommand(connectionStringName))
                 {


                     sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_valueindex", index));
                     sc.Parameters.Add(sc.CreateParameter(DbType.String, "I_stringdata", stringData));
                     sc.Parameters.Add(sc.CreateParameter(DbType.Binary, "I_binarydata", binaryData));
                     sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastupdateddate", DateTime.UtcNow));
                     sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_lastactivitydate", mu.LastActivityDate));
                     sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_applicationid", (Guid)GetApplicationIdFromName(connectionStringName,appName)));
                     sc.Parameters.Add(sc.CreateParameter(DbType.Boolean, "i_isanonymous", false));
                     sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_username", mu.UserName));
                     sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userId", userId));
                     sc.CommandText.AppendQuery(string.Format(
                                 @"UPDATE {0} SET valueindex = :i_valueindex,stringdata= :i_stringData,binarydata= :i_binaryData,
                                                  lastupdateddate= :i_lastupdateddate,lastactivitydate= :i_lastactivitydate,
                                                 username= :i_username WHERE userid = :i_userid and applicationid = :i_applicationid;",
                                 table));
                    int res =  sc.ExecuteNonQuery(CommandType.Text, false);
                      if (res == 0)
                     {
                         // Error
                     }
                 }
             }
             else
             {
                   using (var sc = new VzfSqlCommand(connectionStringName))
                 {

                     sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_userId", userId));
//.........这里部分代码省略.........

        public static int __DeleteProfiles(string connectionStringName, object appName, object userNames)
        {
            using (var sc = new VzfSqlCommand(connectionStringName))
            {
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName)); ;
                sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserNames", userNames));
                sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));

                sc.CommandText.AppendObjectQuery("prov_profile_deleteprofiles", connectionStringName);
                return Convert.ToInt32(sc.ExecuteScalar(CommandType.StoredProcedure, false));
            }
        }

        public static DataTable __GetProfiles(string connectionStringName, object appName, object pageIndex, object pageSize, object userNameToMatch, object inactiveSinceDate)
         {
            using (var sc = new VzfSqlCommand(connectionStringName))
             {
                 sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_ApplicationName", appName));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageIndex", pageIndex));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Int32, "i_PageSize", pageSize));
                 sc.Parameters.Add(sc.CreateParameter(DbType.String, "i_UserNameToMatch", userNameToMatch));
                 sc.Parameters.Add(sc.CreateParameter(DbType.DateTime, "i_InactiveSinceDate", inactiveSinceDate));
                 sc.Parameters.Add(sc.CreateParameter(DbType.Guid, "i_newguid", Guid.NewGuid()));

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

                 return sc.ExecuteDataTableFromReader(CommandBehavior.Default, CommandType.StoredProcedure, false);                
             }
        }