C# SqlParameterHelper.ExecuteScalarAsync方法代码示例

        public async Task<int> RoleCreate(
            Guid roleGuid,
            Guid siteGuid,
            int siteId,
            string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                writeConnectionString, 
                "mp_Roles_Insert", 
                4);

            sph.DefineSqlParameter("@RoleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, roleGuid);
            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            object result = await sph.ExecuteScalarAsync();
            int newID = Convert.ToInt32(result);
            return newID;
        }

        public async Task<int> Create(
            int siteId,
            string userId,
            string claimType,
            string claimValue)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                writeConnectionString, 
                "mp_UserClaims_Insert", 
                4);

            sph.DefineSqlParameter("@UserId", SqlDbType.NVarChar, 128, ParameterDirection.Input, userId);
            sph.DefineSqlParameter("@ClaimType", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimType);
            sph.DefineSqlParameter("@ClaimValue", SqlDbType.NVarChar, -1, ParameterDirection.Input, claimValue);
            sph.DefineSqlParameter("@SiteId", SqlDbType.Int, ParameterDirection.Input, siteId);

            object result = await sph.ExecuteScalarAsync();
            int newID = Convert.ToInt32(result);
            return newID;
        }

        public async Task<int> GetSiteIdByFolder(string folderName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteFolders_SelectSiteIdByFolder", 
                1);

            sph.DefineSqlParameter("@FolderName", SqlDbType.NVarChar, 255, ParameterDirection.Input, folderName);
            object result = await sph.ExecuteScalarAsync();
            return Convert.ToInt32(result);

        }

        public async Task<int> CountOtherSites(int currentSiteId)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Sites_CountOtherSites", 
                1);

            sph.DefineSqlParameter("@CurrentSiteID", SqlDbType.Int, ParameterDirection.Input, currentSiteId);
            object result = await sph.ExecuteScalarAsync();
            return Convert.ToInt32(result);

        }

//.........这里部分代码省略.........
            bool allowNewRegistration,
            bool allowUserSkins,
            bool allowPageSkins,
            bool allowHideMenuOnPages,
            bool useSecureRegistration,
            bool useSslOnAllPages,
            String defaultPageKeywords,
            String defaultPageDescription,
            String defaultPageEncoding,
            String defaultAdditionalMetaTags,
            bool isServerAdminSite,
            bool useLdapAuth,
            bool autoCreateLdapUserOnFirstLogin,
            String ldapServer,
            int ldapPort,
            String ldapDomain,
            String ldapRootDN,
            String ldapUserDNKey,
            bool allowUserFullNameChange,
            bool useEmailForLogin,
            bool reallyDeleteUsers,
            String editorSkin,
            String defaultFriendlyUrlPattern,
            bool enableMyPageFeature,
            string editorProvider,
            string datePickerProvider,
            string captchaProvider,
            string recaptchaPrivateKey,
            string recaptchaPublicKey,
            string wordpressApiKey,
            string windowsLiveAppId,
            string windowsLiveKey,
            bool allowOpenIdAuth,
            bool allowWindowsLiveAuth,
            string gmapApiKey,
            string apiKeyExtra1,
            string apiKeyExtra2,
            string apiKeyExtra3,
            string apiKeyExtra4,
            string apiKeyExtra5,
            bool disableDbAuth)
        {

            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                writeConnectionString, 
                "mp_Sites_Insert", 
                46);

            sph.DefineSqlParameter("@SiteName", SqlDbType.NVarChar, 128, ParameterDirection.Input, siteName);
            sph.DefineSqlParameter("@Skin", SqlDbType.NVarChar, 100, ParameterDirection.Input, skin);
            sph.DefineSqlParameter("@Logo", SqlDbType.NVarChar, 50, ParameterDirection.Input, logo);
            sph.DefineSqlParameter("@Icon", SqlDbType.NVarChar, 50, ParameterDirection.Input, icon);
            sph.DefineSqlParameter("@AllowUserSkins", SqlDbType.Bit, ParameterDirection.Input, allowUserSkins);
            sph.DefineSqlParameter("@AllowNewRegistration", SqlDbType.Bit, ParameterDirection.Input, allowNewRegistration);
            sph.DefineSqlParameter("@UseSecureRegistration", SqlDbType.Bit, ParameterDirection.Input, useSecureRegistration);
            sph.DefineSqlParameter("@UseSSLOnAllPages", SqlDbType.Bit, ParameterDirection.Input, useSslOnAllPages);
            sph.DefineSqlParameter("@DefaultPageKeywords", SqlDbType.NVarChar, 255, ParameterDirection.Input, defaultPageKeywords);
            sph.DefineSqlParameter("@DefaultPageDescription", SqlDbType.NVarChar, 255, ParameterDirection.Input, defaultPageDescription);
            sph.DefineSqlParameter("@DefaultPageEncoding", SqlDbType.NVarChar, 255, ParameterDirection.Input, defaultPageEncoding);
            sph.DefineSqlParameter("@DefaultAdditionalMetaTags", SqlDbType.NVarChar, 255, ParameterDirection.Input, defaultAdditionalMetaTags);
            sph.DefineSqlParameter("@IsServerAdminSite", SqlDbType.Bit, ParameterDirection.Input, isServerAdminSite);
            sph.DefineSqlParameter("@AllowPageSkins", SqlDbType.Bit, ParameterDirection.Input, allowPageSkins);
            sph.DefineSqlParameter("@AllowHideMenuOnPages", SqlDbType.Bit, ParameterDirection.Input, allowHideMenuOnPages);
            sph.DefineSqlParameter("@UseLdapAuth", SqlDbType.Bit, ParameterDirection.Input, useLdapAuth);
            sph.DefineSqlParameter("@AutoCreateLDAPUserOnFirstLogin", SqlDbType.Bit, ParameterDirection.Input, autoCreateLdapUserOnFirstLogin);
            sph.DefineSqlParameter("@LdapServer", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapServer);
            sph.DefineSqlParameter("@LdapPort", SqlDbType.Int, ParameterDirection.Input, ldapPort);
            sph.DefineSqlParameter("@LdapDomain", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapDomain);
            sph.DefineSqlParameter("@LdapRootDN", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapRootDN);
            sph.DefineSqlParameter("@LdapUserDNKey", SqlDbType.NVarChar, 10, ParameterDirection.Input, ldapUserDNKey);
            sph.DefineSqlParameter("@AllowUserFullNameChange", SqlDbType.Bit, ParameterDirection.Input, allowUserFullNameChange);
            sph.DefineSqlParameter("@UseEmailForLogin", SqlDbType.Bit, ParameterDirection.Input, useEmailForLogin);
            sph.DefineSqlParameter("@ReallyDeleteUsers", SqlDbType.Bit, ParameterDirection.Input, reallyDeleteUsers);
            sph.DefineSqlParameter("@EditorSkin", SqlDbType.NVarChar, 50, ParameterDirection.Input, editorSkin);
            sph.DefineSqlParameter("@DefaultFriendlyUrlPatternEnum", SqlDbType.NVarChar, 50, ParameterDirection.Input, defaultFriendlyUrlPattern);
            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@EnableMyPageFeature", SqlDbType.Bit, ParameterDirection.Input, enableMyPageFeature);
            sph.DefineSqlParameter("@EditorProvider", SqlDbType.NVarChar, 255, ParameterDirection.Input, editorProvider);
            sph.DefineSqlParameter("@DatePickerProvider", SqlDbType.NVarChar, 255, ParameterDirection.Input, datePickerProvider);
            sph.DefineSqlParameter("@CaptchaProvider", SqlDbType.NVarChar, 255, ParameterDirection.Input, captchaProvider);
            sph.DefineSqlParameter("@RecaptchaPrivateKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, recaptchaPrivateKey);
            sph.DefineSqlParameter("@RecaptchaPublicKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, recaptchaPublicKey);
            sph.DefineSqlParameter("@WordpressAPIKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, wordpressApiKey);
            sph.DefineSqlParameter("@WindowsLiveAppID", SqlDbType.NVarChar, 255, ParameterDirection.Input, windowsLiveAppId);
            sph.DefineSqlParameter("@WindowsLiveKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, windowsLiveKey);
            sph.DefineSqlParameter("@AllowOpenIDAuth", SqlDbType.Bit, ParameterDirection.Input, allowOpenIdAuth);
            sph.DefineSqlParameter("@AllowWindowsLiveAuth", SqlDbType.Bit, ParameterDirection.Input, allowWindowsLiveAuth);

            sph.DefineSqlParameter("@GmapApiKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, gmapApiKey);
            sph.DefineSqlParameter("@ApiKeyExtra1", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra1);
            sph.DefineSqlParameter("@ApiKeyExtra2", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra2);
            sph.DefineSqlParameter("@ApiKeyExtra3", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra3);
            sph.DefineSqlParameter("@ApiKeyExtra4", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra4);
            sph.DefineSqlParameter("@ApiKeyExtra5", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra5);
            sph.DefineSqlParameter("@DisableDbAuth", SqlDbType.Bit, ParameterDirection.Input, disableDbAuth);
            object result = await sph.ExecuteScalarAsync();
            int newID = Convert.ToInt32(result);
            return newID;
        }

        /// <summary>
        /// Gets a count of rows in the mp_GeoZone table.
        /// </summary>
        public async Task<int> GetCount(Guid countryGuid)
        {

            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_GeoZone_GetCountByCountry", 
                1);

            sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
            object result = await sph.ExecuteScalarAsync();
            return Convert.ToInt32(result);


        }

//.........这里部分代码省略.........
            string loginInfoBottom,
            string registrationAgreement,
            string registrationPreamble,
            string smtpServer,
            int smtpPort,
            string smtpUser,
            string smtpPassword,
            string smtpPreferredEncoding,
            bool smtpRequiresAuth,
            bool smtpUseSsl,
            bool requireApprovalBeforeLogin
            )
        {

            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                writeConnectionString, 
                "mp_Sites_Insert", 
                74);

            sph.DefineSqlParameter("@SiteName", SqlDbType.NVarChar, 128, ParameterDirection.Input, siteName);
            sph.DefineSqlParameter("@Skin", SqlDbType.NVarChar, 100, ParameterDirection.Input, skin); 
            sph.DefineSqlParameter("@AllowNewRegistration", SqlDbType.Bit, ParameterDirection.Input, allowNewRegistration);
            sph.DefineSqlParameter("@UseSecureRegistration", SqlDbType.Bit, ParameterDirection.Input, useSecureRegistration);
            sph.DefineSqlParameter("@UseSSLOnAllPages", SqlDbType.Bit, ParameterDirection.Input, useSslOnAllPages); 
            sph.DefineSqlParameter("@IsServerAdminSite", SqlDbType.Bit, ParameterDirection.Input, isServerAdminSite);
            sph.DefineSqlParameter("@UseLdapAuth", SqlDbType.Bit, ParameterDirection.Input, useLdapAuth);
            sph.DefineSqlParameter("@AutoCreateLDAPUserOnFirstLogin", SqlDbType.Bit, ParameterDirection.Input, autoCreateLdapUserOnFirstLogin);
            sph.DefineSqlParameter("@LdapServer", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapServer);
            sph.DefineSqlParameter("@LdapPort", SqlDbType.Int, ParameterDirection.Input, ldapPort);
            sph.DefineSqlParameter("@LdapDomain", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapDomain);
            sph.DefineSqlParameter("@LdapRootDN", SqlDbType.NVarChar, 255, ParameterDirection.Input, ldapRootDN);
            sph.DefineSqlParameter("@LdapUserDNKey", SqlDbType.NVarChar, 10, ParameterDirection.Input, ldapUserDNKey);
            sph.DefineSqlParameter("@AllowUserFullNameChange", SqlDbType.Bit, ParameterDirection.Input, allowUserFullNameChange);
            sph.DefineSqlParameter("@UseEmailForLogin", SqlDbType.Bit, ParameterDirection.Input, useEmailForLogin);
            sph.DefineSqlParameter("@ReallyDeleteUsers", SqlDbType.Bit, ParameterDirection.Input, reallyDeleteUsers);
            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@RecaptchaPrivateKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, recaptchaPrivateKey);
            sph.DefineSqlParameter("@RecaptchaPublicKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, recaptchaPublicKey);
            sph.DefineSqlParameter("@ApiKeyExtra1", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra1);
            sph.DefineSqlParameter("@ApiKeyExtra2", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra2);
            sph.DefineSqlParameter("@ApiKeyExtra3", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra3);
            sph.DefineSqlParameter("@ApiKeyExtra4", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra4);
            sph.DefineSqlParameter("@ApiKeyExtra5", SqlDbType.NVarChar, 255, ParameterDirection.Input, apiKeyExtra5);
            sph.DefineSqlParameter("@DisableDbAuth", SqlDbType.Bit, ParameterDirection.Input, disableDbAuth);
            sph.DefineSqlParameter("@RequiresQuestionAndAnswer", SqlDbType.Bit, ParameterDirection.Input, requiresQuestionAndAnswer);
            sph.DefineSqlParameter("@MaxInvalidPasswordAttempts", SqlDbType.Int, ParameterDirection.Input, maxInvalidPasswordAttempts);
            sph.DefineSqlParameter("@PasswordAttemptWindowMinutes", SqlDbType.Int, ParameterDirection.Input, passwordAttemptWindowMinutes);
            sph.DefineSqlParameter("@MinRequiredPasswordLength", SqlDbType.Int, ParameterDirection.Input, minRequiredPasswordLength);
            sph.DefineSqlParameter("@MinReqNonAlphaChars", SqlDbType.Int, ParameterDirection.Input, minReqNonAlphaChars);
            sph.DefineSqlParameter("@DefaultEmailFromAddress", SqlDbType.NVarChar, 100, ParameterDirection.Input, defaultEmailFromAddress);
            sph.DefineSqlParameter("@AllowDbFallbackWithLdap", SqlDbType.Bit, ParameterDirection.Input, allowDbFallbackWithLdap);
            sph.DefineSqlParameter("@EmailLdapDbFallback", SqlDbType.Bit, ParameterDirection.Input, emailLdapDbFallback);
            sph.DefineSqlParameter("@AllowPersistentLogin", SqlDbType.Bit, ParameterDirection.Input, allowPersistentLogin);
            sph.DefineSqlParameter("@CaptchaOnLogin", SqlDbType.Bit, ParameterDirection.Input, captchaOnLogin);
            sph.DefineSqlParameter("@CaptchaOnRegistration", SqlDbType.Bit, ParameterDirection.Input, captchaOnRegistration);
            sph.DefineSqlParameter("@SiteIsClosed", SqlDbType.Bit, ParameterDirection.Input, siteIsClosed);
            sph.DefineSqlParameter("@SiteIsClosedMessage", SqlDbType.NVarChar, -1, ParameterDirection.Input, siteIsClosedMessage);
            sph.DefineSqlParameter("@PrivacyPolicy", SqlDbType.NVarChar, -1, ParameterDirection.Input, privacyPolicy);
            sph.DefineSqlParameter("@TimeZoneId", SqlDbType.NVarChar, 50, ParameterDirection.Input, timeZoneId);
            sph.DefineSqlParameter("@GoogleAnalyticsProfileId", SqlDbType.NVarChar, 25, ParameterDirection.Input, googleAnalyticsProfileId);
            sph.DefineSqlParameter("@CompanyName", SqlDbType.NVarChar, 255, ParameterDirection.Input, companyName);
            sph.DefineSqlParameter("@CompanyStreetAddress", SqlDbType.NVarChar, 250, ParameterDirection.Input, companyStreetAddress);
            sph.DefineSqlParameter("@CompanyStreetAddress2", SqlDbType.NVarChar, 250, ParameterDirection.Input, companyStreetAddress2);
            sph.DefineSqlParameter("@CompanyRegion", SqlDbType.NVarChar, 200, ParameterDirection.Input, companyRegion);
            sph.DefineSqlParameter("@CompanyLocality", SqlDbType.NVarChar, 200, ParameterDirection.Input, companyLocality);
            sph.DefineSqlParameter("@CompanyCountry", SqlDbType.NVarChar, 10, ParameterDirection.Input, companyCountry);
            sph.DefineSqlParameter("@CompanyPostalCode", SqlDbType.NVarChar, 20, ParameterDirection.Input, companyPostalCode);
            sph.DefineSqlParameter("@CompanyPublicEmail", SqlDbType.NVarChar, 100, ParameterDirection.Input, companyPublicEmail);
            sph.DefineSqlParameter("@CompanyPhone", SqlDbType.NVarChar, 20, ParameterDirection.Input, companyPhone);
            sph.DefineSqlParameter("@CompanyFax", SqlDbType.NVarChar, 20, ParameterDirection.Input, companyFax);
            sph.DefineSqlParameter("@FacebookAppId", SqlDbType.NVarChar, 100, ParameterDirection.Input, facebookAppId);
            sph.DefineSqlParameter("@FacebookAppSecret", SqlDbType.NVarChar, 100, ParameterDirection.Input, facebookAppSecret);
            sph.DefineSqlParameter("@GoogleClientId", SqlDbType.NVarChar, 100, ParameterDirection.Input, googleClientId);
            sph.DefineSqlParameter("@GoogleClientSecret", SqlDbType.NVarChar, 100, ParameterDirection.Input, googleClientSecret);
            sph.DefineSqlParameter("@TwitterConsumerKey", SqlDbType.NVarChar, 100, ParameterDirection.Input, twitterConsumerKey);
            sph.DefineSqlParameter("@TwitterConsumerSecret", SqlDbType.NVarChar, 100, ParameterDirection.Input, twitterConsumerSecret);
            sph.DefineSqlParameter("@MicrosoftClientId", SqlDbType.NVarChar, 100, ParameterDirection.Input, microsoftClientId);
            sph.DefineSqlParameter("@MicrosoftClientSecret", SqlDbType.NVarChar, 100, ParameterDirection.Input, microsoftClientSecret);
            sph.DefineSqlParameter("@PreferredHostName", SqlDbType.NVarChar, 250, ParameterDirection.Input, preferredHostName);
            sph.DefineSqlParameter("@SiteFolderName", SqlDbType.NVarChar, 50, ParameterDirection.Input, siteFolderName);
            sph.DefineSqlParameter("@AddThisDotComUsername", SqlDbType.NVarChar, 50, ParameterDirection.Input, addThisDotComUsername);
            sph.DefineSqlParameter("@LoginInfoTop", SqlDbType.NVarChar, -1, ParameterDirection.Input, loginInfoTop);
            sph.DefineSqlParameter("@LoginInfoBottom", SqlDbType.NVarChar, -1, ParameterDirection.Input, loginInfoBottom);
            sph.DefineSqlParameter("@RegistrationAgreement", SqlDbType.NVarChar, -1, ParameterDirection.Input, registrationAgreement);
            sph.DefineSqlParameter("@RegistrationPreamble", SqlDbType.NVarChar, -1, ParameterDirection.Input, registrationPreamble);
            sph.DefineSqlParameter("@SmtpServer", SqlDbType.NVarChar, 200, ParameterDirection.Input, smtpServer);
            sph.DefineSqlParameter("@SmtpPort", SqlDbType.Int, ParameterDirection.Input, smtpPort);
            sph.DefineSqlParameter("@SmtpUser", SqlDbType.NVarChar, 500, ParameterDirection.Input, smtpUser);
            sph.DefineSqlParameter("@SmtpPassword", SqlDbType.NVarChar, 500, ParameterDirection.Input, smtpPassword);
            sph.DefineSqlParameter("@SmtpPreferredEncoding", SqlDbType.NVarChar, 20, ParameterDirection.Input, smtpPreferredEncoding);
            sph.DefineSqlParameter("@SmtpRequiresAuth", SqlDbType.Bit, ParameterDirection.Input, smtpRequiresAuth);
            sph.DefineSqlParameter("@SmtpUseSsl", SqlDbType.Bit, ParameterDirection.Input, smtpUseSsl);
            sph.DefineSqlParameter("@RequireApprovalBeforeLogin", SqlDbType.Bit, ParameterDirection.Input, requireApprovalBeforeLogin);


            object result = await sph.ExecuteScalarAsync();
            int newID = Convert.ToInt32(result);
            return newID;
        }

        public async Task<Guid> GetSiteGuid(string folderName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteFolders_SelectSiteGuidByFolder", 
                1);

            sph.DefineSqlParameter("@FolderName", SqlDbType.NVarChar, 255, ParameterDirection.Input, folderName);
            object result = await sph.ExecuteScalarAsync();
            string strGuid = result.ToString();
            if (strGuid.Length == 36)
            {
                return new Guid(strGuid);
            }
            return Guid.Empty;
        }

//.........这里部分代码省略.........

        //    }

        //    return dataTable;

        //}

        public async Task<int> AddUser(
            Guid siteGuid,
            int siteId,
            string fullName,
            string loginName,
            string email,
            Guid userGuid,
            DateTime dateCreated,
            bool mustChangePwd,
            string firstName,
            string lastName,
            string timeZoneId,
            DateTime dateOfBirth,
            bool emailConfirmed,
            string passwordHash,
            string securityStamp,
            string phoneNumber,
            bool phoneNumberConfirmed,
            bool twoFactorEnabled,
            DateTime? lockoutEndDateUtc,
            
            bool accountApproved,
            bool isLockedOut,
            bool displayInMemberList,
            string webSiteUrl,
            string country,
            string state,
            string avatarUrl,
            string signature,
            string authorBio,
            string comment
            )
        {

            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                writeConnectionString, 
                "mp_Users_Insert", 
                30);

            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@Name", SqlDbType.NVarChar, 100, ParameterDirection.Input, fullName);
            sph.DefineSqlParameter("@LoginName", SqlDbType.NVarChar, 50, ParameterDirection.Input, loginName);
            sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
            sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
            sph.DefineSqlParameter("@DateCreated", SqlDbType.DateTime, ParameterDirection.Input, dateCreated);
            sph.DefineSqlParameter("@MustChangePwd", SqlDbType.Bit, ParameterDirection.Input, mustChangePwd);
            sph.DefineSqlParameter("@FirstName", SqlDbType.NVarChar, 100, ParameterDirection.Input, firstName);
            sph.DefineSqlParameter("@LastName", SqlDbType.NVarChar, 100, ParameterDirection.Input, lastName);
            sph.DefineSqlParameter("@TimeZoneId", SqlDbType.NVarChar, 32, ParameterDirection.Input, timeZoneId);
            sph.DefineSqlParameter("@EmailChangeGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, Guid.Empty);

            if (dateOfBirth == DateTime.MinValue)
            {
                sph.DefineSqlParameter("@DateOfBirth", SqlDbType.DateTime, ParameterDirection.Input, DBNull.Value);
            }
            else
            {
                sph.DefineSqlParameter("@DateOfBirth", SqlDbType.DateTime, ParameterDirection.Input, dateOfBirth);
            }

            sph.DefineSqlParameter("@EmailConfirmed", SqlDbType.Bit, ParameterDirection.Input, emailConfirmed);
            sph.DefineSqlParameter("@PasswordHash", SqlDbType.NVarChar, -1, ParameterDirection.Input, passwordHash);
            sph.DefineSqlParameter("@SecurityStamp", SqlDbType.NVarChar, -1, ParameterDirection.Input, securityStamp);
            sph.DefineSqlParameter("@PhoneNumber", SqlDbType.NVarChar, 50, ParameterDirection.Input, phoneNumber);
            sph.DefineSqlParameter("@PhoneNumberConfirmed", SqlDbType.Bit, ParameterDirection.Input, phoneNumberConfirmed);
            sph.DefineSqlParameter("@TwoFactorEnabled", SqlDbType.Bit, ParameterDirection.Input, twoFactorEnabled);
            if (lockoutEndDateUtc == null)
            {
                sph.DefineSqlParameter("@LockoutEndDateUtc", SqlDbType.DateTime, ParameterDirection.Input, DBNull.Value);
            }
            else
            {
                sph.DefineSqlParameter("@LockoutEndDateUtc", SqlDbType.DateTime, ParameterDirection.Input, lockoutEndDateUtc);
            }

            sph.DefineSqlParameter("@AccountApproved", SqlDbType.Bit, ParameterDirection.Input, accountApproved);
            sph.DefineSqlParameter("@IsLockedOut", SqlDbType.Bit, ParameterDirection.Input, isLockedOut);
            sph.DefineSqlParameter("@DisplayInMemberList", SqlDbType.Bit, ParameterDirection.Input, displayInMemberList);
            sph.DefineSqlParameter("@WebSiteURL", SqlDbType.NVarChar, 100, ParameterDirection.Input, webSiteUrl);
            sph.DefineSqlParameter("@Country", SqlDbType.NVarChar, 100, ParameterDirection.Input, country);
            sph.DefineSqlParameter("@State", SqlDbType.NVarChar, 100, ParameterDirection.Input, state);
            sph.DefineSqlParameter("@AvatarUrl", SqlDbType.NVarChar, 250, ParameterDirection.Input, avatarUrl);
            sph.DefineSqlParameter("@Signature", SqlDbType.NVarChar, -1, ParameterDirection.Input, signature);
            sph.DefineSqlParameter("@AuthorBio", SqlDbType.NVarChar, -1, ParameterDirection.Input, authorBio);
            sph.DefineSqlParameter("@Comment", SqlDbType.NVarChar, -1, ParameterDirection.Input, comment);

            object result = await sph.ExecuteScalarAsync();

            int newID = Convert.ToInt32(result);
            return newID;
        }

        public async Task<int> GetCountOfSiteRoles(int siteId, string searchInput)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Roles_CountBySearch", 
                2);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@SearchInput", SqlDbType.NVarChar, 50, ParameterDirection.Input, searchInput);
            object result = await sph.ExecuteScalarAsync();
            return Convert.ToInt32(result);
        }

        public async Task<bool> Exists(int siteId, string roleName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_Roles_RoleExists", 
                2);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
            object result = await sph.ExecuteScalarAsync();
            int count = Convert.ToInt32(result);
            return (count > 0);

        }

        public async Task<int> CountFutureLockoutDate(
            int siteId,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString,
                "mp_Users_CountFutureLockoutDate",
                2);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            sph.DefineSqlParameter("@CurrentUtc", SqlDbType.DateTime, ParameterDirection.Input, DateTime.UtcNow);

            object result = await sph.ExecuteScalarAsync(cancellationToken);
            int count = Convert.ToInt32(result);
            return count;
        }

        public async Task<int> CountPhoneUnconfirmed(
            int siteId,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString,
                "mp_Users_CountPhoneUnconfirmed",
                1);

            sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
            object result = await sph.ExecuteScalarAsync(cancellationToken);
            int count = Convert.ToInt32(result);
            return count;
        }

        public async Task<int> GetSiteIdByHostName(
            string hostName,
            CancellationToken cancellationToken)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteHosts_SelectSiteIdByHost", 
                1);

            sph.DefineSqlParameter("@HostName", SqlDbType.NVarChar, 255, ParameterDirection.Input, hostName);
            object result = await sph.ExecuteScalarAsync(cancellationToken);
            return Convert.ToInt32(result);

        }

        public async Task<bool> Exists(string folderName)
        {
            SqlParameterHelper sph = new SqlParameterHelper(
                logFactory,
                readConnectionString, 
                "mp_SiteFolder_Exists", 
                1);

            sph.DefineSqlParameter("@FolderName", SqlDbType.NVarChar, 255, ParameterDirection.Input, folderName);
            object result = await sph.ExecuteScalarAsync();
            int count = Convert.ToInt32(result);
            return (count > 0);
        }