C# SqlParameterHelper.DefineSqlParameter方法代码示例

 public static int Add(
     Guid pollGuid,
     Guid siteGuid,
     String question,
     bool anonymousVoting,
     bool allowViewingResultsBeforeVoting,
     bool showOrderNumbers,
     bool showResultsWhenDeactivated,
     bool active,
     DateTime activeFrom,
     DateTime activeTo)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_Polls_Insert", 10);
     sph.DefineSqlParameter("@PollGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, pollGuid);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@Question", SqlDbType.NVarChar, 255, ParameterDirection.Input, question);
     sph.DefineSqlParameter("@AnonymousVoting", SqlDbType.Bit, ParameterDirection.Input, anonymousVoting);
     sph.DefineSqlParameter("@AllowViewingResultsBeforeVoting", SqlDbType.Bit, ParameterDirection.Input, allowViewingResultsBeforeVoting);
     sph.DefineSqlParameter("@ShowOrderNumbers", SqlDbType.Bit, ParameterDirection.Input, showOrderNumbers);
     sph.DefineSqlParameter("@ShowResultsWhenDeactivated", SqlDbType.Bit, ParameterDirection.Input, showResultsWhenDeactivated);
     sph.DefineSqlParameter("@Active", SqlDbType.Bit, ParameterDirection.Input, active);
     sph.DefineSqlParameter("@ActiveFrom", SqlDbType.DateTime, ParameterDirection.Input, activeFrom);
     sph.DefineSqlParameter("@ActiveTo", SqlDbType.DateTime, ParameterDirection.Input, activeTo);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

        /// <summary>
        /// Inserts a row in the mp_ContentWorkflow table. Returns rows affected count.
        /// </summary>
        /// <param name="guid"> guid </param>
        /// <param name="siteGuid"> siteGuid </param>
        /// <param name="moduleGuid"> moduleGuid </param>
        /// <param name="createdDateUtc"> createdDateUtc </param>
        /// <param name="userGuid"> userGuid </param>
        /// <param name="status"> status </param>
        /// <param name="contentText"> contentText </param>
        /// <param name="customData"> customData </param>
        /// <param name="customReferenceNumber"> customReferenceNumber </param>
        /// <param name="customReferenceGuid"> customReferenceGuid </param>
        /// <returns>int</returns>
        public static int Create(
            Guid guid,
            Guid siteGuid,
            Guid moduleGuid,
            Guid userGuid,
            DateTime createdDateUtc,
            string contentText,
            string customData,
            int customReferenceNumber,
            Guid customReferenceGuid,
            string status)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ContentWorkflow_Insert", 10);
            sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
            sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
            sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
            sph.DefineSqlParameter("@CreatedDateUtc", SqlDbType.DateTime, ParameterDirection.Input, createdDateUtc);
            sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
            sph.DefineSqlParameter("@Status", SqlDbType.NVarChar, 20, ParameterDirection.Input, status);
            sph.DefineSqlParameter("@ContentText", SqlDbType.NVarChar, -1, ParameterDirection.Input, contentText);
            sph.DefineSqlParameter("@CustomData", SqlDbType.NVarChar, -1, ParameterDirection.Input, customData);

            //object customReferenceNumberVal = customReferenceNumber.HasValue ? (object)customReferenceNumber.Value : DBNull.Value;
            sph.DefineSqlParameter("@CustomReferenceNumber", SqlDbType.Int, ParameterDirection.Input, customReferenceNumber);

            //object customReferenceGuidVal = customReferenceGuid.HasValue ? (object)customReferenceGuid.Value : DBNull.Value;
            sph.DefineSqlParameter("@CustomReferenceGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, customReferenceGuid);

            int rowsAffected = sph.ExecuteNonQuery();
            return rowsAffected;
        }

 public static IDataReader GetByName(int siteId, string roleName)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_Roles_SelectOneByName", 2);
     sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
     sph.DefineSqlParameter("@RoleName", SqlDbType.NVarChar, 50, ParameterDirection.Input, roleName);
     return sph.ExecuteReader();
 }

 /// <summary>
 /// Gets an IDataReader with one row from the mp_SurveyQuestionAnswers table.
 /// </summary>
 /// <param name="answerGuid"> answerGuid </param>
 public static IDataReader GetOne(Guid responseGuid, Guid questionGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_SurveyQuestionAnswers_SelectOne", 2);
     sph.DefineSqlParameter("@ResponseGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, responseGuid);
     sph.DefineSqlParameter("@QuestionGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, questionGuid);
     return sph.ExecuteReader();
 }

 ///// <summary>
 ///// Gets the connection string.
 ///// </summary>
 ///// <returns></returns>
 //private static string GetConnectionString()
 //{
 //    return ConfigurationManager.AppSettings["MSSQLConnectionString"];
 //}
 /// <summary>
 /// Inserts a row in the mp_ContentRating table. Returns rows affected count.
 /// </summary>
 /// <param name="rowGuid"> rowGuid </param>
 /// <param name="siteGuid"> siteGuid </param>
 /// <param name="contentGuid"> contentGuid </param>
 /// <param name="userGuid"> userGuid </param>
 /// <param name="emailAddress"> emailAddress </param>
 /// <param name="rating"> rating </param>
 /// <param name="comments"> comments </param>
 /// <param name="ipAddress"> ipAddress </param>
 /// <param name="createdUtc"> createdUtc </param>
 /// <returns>int</returns>
 public static int Create(
     Guid rowGuid,
     Guid siteGuid,
     Guid contentGuid,
     Guid userGuid,
     string emailAddress,
     int rating,
     string comments,
     string ipAddress,
     DateTime createdUtc)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ContentRating_Insert", 10);
     sph.DefineSqlParameter("@RowGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, rowGuid);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@ContentGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, contentGuid);
     sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
     sph.DefineSqlParameter("@EmailAddress", SqlDbType.NVarChar, 100, ParameterDirection.Input, emailAddress);
     sph.DefineSqlParameter("@Rating", SqlDbType.Int, ParameterDirection.Input, rating);
     sph.DefineSqlParameter("@Comments", SqlDbType.NVarChar, -1, ParameterDirection.Input, comments);
     sph.DefineSqlParameter("@IpAddress", SqlDbType.NVarChar, 50, ParameterDirection.Input, ipAddress);
     sph.DefineSqlParameter("@CreatedUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
     sph.DefineSqlParameter("@LastModUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

 public static void AddToModule(Guid surveyGuid, int moduleId)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_Survey_AddToModule", 2);
     sph.DefineSqlParameter("@SurveyGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, surveyGuid);
     sph.DefineSqlParameter("@ModuleId", SqlDbType.Int, ParameterDirection.Input, moduleId);
     sph.ExecuteNonQuery();
 }

 /// <summary>
 /// Gets an IDataReader with all rows in the mp_Currency table.
 /// </summary>
 public static IDataReader GetSpecificSettingAllModulesWithTheDefinition(string srcPath, string settingName)
 {
     SqlParameterHelper sph = new SqlParameterHelper(GetConnectionString(), "mp_ModulePagePath_SelectByControlSrcPathSnippet", 2);
     sph.DefineSqlParameter("@FeaturePath", SqlDbType.UniqueIdentifier, ParameterDirection.Input, srcPath);
     sph.DefineSqlParameter("@SettingName", SqlDbType.NVarChar, ParameterDirection.Input, settingName);
     return sph.ExecuteReader();
 }

 ///// <summary>
 ///// Gets the connection string for read.
 ///// </summary>
 ///// <returns></returns>
 //private static string GetReadConnectionString()
 //{
 //    return ConfigurationManager.AppSettings["MSSQLConnectionString"];
 //}
 ///// <summary>
 ///// Gets the connection string for write.
 ///// </summary>
 ///// <returns></returns>
 //private static string GetWriteConnectionString()
 //{
 //    if (ConfigurationManager.AppSettings["MSSQLWriteConnectionString"] != null)
 //    {
 //        return ConfigurationManager.AppSettings["MSSQLWriteConnectionString"];
 //    }
 //    return ConfigurationManager.AppSettings["MSSQLConnectionString"];
 //}
 /// <summary>
 /// Inserts a row in the mp_ContentMeta table. Returns rows affected count.
 /// </summary>
 /// <param name="guid"> guid </param>
 /// <param name="siteGuid"> siteGuid </param>
 /// <param name="moduleGuid"> moduleGuid </param>
 /// <param name="contentGuid"> contentGuid </param>
 /// <param name="name"> name </param>
 /// <param name="scheme"> scheme </param>
 /// <param name="langCode"> langCode </param>
 /// <param name="dir"> dir </param>
 /// <param name="metaContent"> metaContent </param>
 /// <param name="sortRank"> sortRank </param>
 /// <param name="createdUtc"> createdUtc </param>
 /// <param name="createdBy"> createdBy </param>
 /// <returns>int</returns>
 public static int Create(
     Guid guid,
     Guid siteGuid,
     Guid moduleGuid,
     Guid contentGuid,
     string name,
     string scheme,
     string langCode,
     string dir,
     string metaContent,
     int sortRank,
     DateTime createdUtc,
     Guid createdBy)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ContentMeta_Insert", 14);
     sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
     sph.DefineSqlParameter("@ContentGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, contentGuid);
     sph.DefineSqlParameter("@Name", SqlDbType.NVarChar, 255, ParameterDirection.Input, name);
     sph.DefineSqlParameter("@Scheme", SqlDbType.NVarChar, 255, ParameterDirection.Input, scheme);
     sph.DefineSqlParameter("@LangCode", SqlDbType.NVarChar, 10, ParameterDirection.Input, langCode);
     sph.DefineSqlParameter("@Dir", SqlDbType.NVarChar, 3, ParameterDirection.Input, dir);
     sph.DefineSqlParameter("@MetaContent", SqlDbType.NVarChar, -1, ParameterDirection.Input, metaContent);
     sph.DefineSqlParameter("@SortRank", SqlDbType.Int, ParameterDirection.Input, sortRank);
     sph.DefineSqlParameter("@CreatedUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
     sph.DefineSqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, createdBy);
     sph.DefineSqlParameter("@LastModUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
     sph.DefineSqlParameter("@LastModBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, createdBy);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

 /// <summary>
 /// Gets an IDataReader with one row from the mp_GeoZone table.
 /// </summary>
 /// <param name="guid"> guid </param>
 public static IDataReader GetByCode(Guid countryGuid, string code)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_GeoZone_SelectByCode", 2);
     sph.DefineSqlParameter("@CountryGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, countryGuid);
     sph.DefineSqlParameter("@Code", SqlDbType.NVarChar, 255, ParameterDirection.Input, code);
     return sph.ExecuteReader();
 }

 /// <summary>
 /// Inserts a row in the mp_ContactFormMessage table. Returns rows affected count.
 /// </summary>
 /// <param name="rowGuid"> rowGuid </param>
 /// <param name="siteGuid"> siteGuid </param>
 /// <param name="moduleGuid"> moduleGuid </param>
 /// <param name="email"> email </param>
 /// <param name="url"> url </param>
 /// <param name="subject"> subject </param>
 /// <param name="message"> message </param>
 /// <param name="createdUtc"> createdUtc </param>
 /// <param name="createdFromIpAddress"> createdFromIpAddress </param>
 /// <param name="userGuid"> userGuid </param>
 /// <returns>int</returns>
 public static int Create(
     Guid rowGuid,
     Guid siteGuid,
     Guid moduleGuid,
     string email,
     string url,
     string subject,
     string message,
     DateTime createdUtc,
     string createdFromIpAddress,
     Guid userGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ContactFormMessage_Insert", 10);
     sph.DefineSqlParameter("@RowGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, rowGuid);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
     sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
     sph.DefineSqlParameter("@Url", SqlDbType.NVarChar, 255, ParameterDirection.Input, url);
     sph.DefineSqlParameter("@Subject", SqlDbType.NVarChar, 255, ParameterDirection.Input, subject);
     sph.DefineSqlParameter("@Message", SqlDbType.NVarChar, -1, ParameterDirection.Input, message);
     sph.DefineSqlParameter("@CreatedUtc", SqlDbType.DateTime, ParameterDirection.Input, createdUtc);
     sph.DefineSqlParameter("@CreatedFromIpAddress", SqlDbType.NVarChar, 255, ParameterDirection.Input, createdFromIpAddress);
     sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

 /// <summary>
 /// Inserts a row in the mp_LetterSubscribe table. Returns rows affected count.
 /// </summary>
 /// <param name="guid"> guid </param>
 /// <param name="siteGuid"> siteGuid </param>
 /// <param name="letterInfoGuid"> letterInfoGuid </param>
 /// <param name="userGuid"> userGuid </param>
 /// <param name="email"> email </param>
 /// <param name="isVerified"> isVerified </param>
 /// <param name="verifyGuid"> verifyGuid </param>
 /// <param name="beginUtc"> beginUtc </param>
 /// <param name="useHtml"> useHtml </param>
 /// <returns>int</returns>
 public static int Create(
     Guid guid,
     Guid siteGuid,
     Guid letterInfoGuid,
     Guid userGuid,
     string email,
     bool isVerified,
     Guid verifyGuid,
     DateTime beginUtc,
     bool useHtml,
     string ipAddress)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_LetterSubscribe_Insert", 10);
     sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@LetterInfoGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, letterInfoGuid);
     sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
     sph.DefineSqlParameter("@Email", SqlDbType.NVarChar, 100, ParameterDirection.Input, email);
     sph.DefineSqlParameter("@IsVerified", SqlDbType.Bit, ParameterDirection.Input, isVerified);
     sph.DefineSqlParameter("@VerifyGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, verifyGuid);
     sph.DefineSqlParameter("@BeginUtc", SqlDbType.DateTime, ParameterDirection.Input, beginUtc);
     sph.DefineSqlParameter("@UseHtml", SqlDbType.Bit, ParameterDirection.Input, useHtml);
     sph.DefineSqlParameter("@IpAddress", SqlDbType.NVarChar, 100, ParameterDirection.Input, ipAddress);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

 /// <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)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_MediaTrack_AdjustTrackOrderForDelete", 2);
     sph.DefineSqlParameter("@PlayerID", SqlDbType.Int, ParameterDirection.Input, playerId);
     sph.DefineSqlParameter("@TrackOrder", SqlDbType.Int, ParameterDirection.Input, trackOrder);
     return sph.ExecuteNonQuery();
 }

        public static int AddSharedFile(
            Guid itemGuid,
            Guid moduleGuid,
            Guid userGuid,
            Guid folderGuid,
            int moduleId,
            int uploadUserId,
            string friendlyName,
            string originalFileName,
            string serverFileName,
            int sizeInKB,
            DateTime uploadDate,
            int folderId,
            string description)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_SharedFiles_Insert", 13);

            sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, itemGuid);
            sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
            sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
            sph.DefineSqlParameter("@FolderGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, folderGuid);
            sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
            sph.DefineSqlParameter("@UploadUserID", SqlDbType.Int, ParameterDirection.Input, uploadUserId);
            sph.DefineSqlParameter("@FriendlyName", SqlDbType.NVarChar, 255, ParameterDirection.Input, friendlyName);
            sph.DefineSqlParameter("@OriginalFileName", SqlDbType.NVarChar, 255, ParameterDirection.Input, originalFileName);
            sph.DefineSqlParameter("@ServerFileName", SqlDbType.NVarChar, 255, ParameterDirection.Input, serverFileName);
            sph.DefineSqlParameter("@SizeInKB", SqlDbType.Int, ParameterDirection.Input, sizeInKB);
            sph.DefineSqlParameter("@UploadDate", SqlDbType.DateTime, ParameterDirection.Input, uploadDate);
            sph.DefineSqlParameter("@FolderID", SqlDbType.Int, ParameterDirection.Input, folderId);
            sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, -1, ParameterDirection.Input, description);
            int newID = Convert.ToInt32(sph.ExecuteScalar());
            return newID;
        }

 public static IDataReader Find(string loginProvider, string providerKey)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_UserLogins_Find", 2);
     sph.DefineSqlParameter("@LoginProvider", SqlDbType.NVarChar, 128, ParameterDirection.Input, loginProvider);
     sph.DefineSqlParameter("@ProviderKey", SqlDbType.NVarChar, 128, ParameterDirection.Input, providerKey);
     return sph.ExecuteReader();
 }

        public static bool CreateModuleSetting(
            Guid settingGuid,
            Guid moduleGuid,
            int moduleId,
            string settingName,
            string settingValue,
            string controlType,
            string regexValidationExpression,
            string controlSrc,
            string helpKey,
            int sortOrder)
        {
            SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ModuleSettings_Insert", 10);

            sph.DefineSqlParameter("@SettingGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, settingGuid);
            sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
            sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
            sph.DefineSqlParameter("@SettingName", SqlDbType.NVarChar, 50, ParameterDirection.Input, settingName);
            sph.DefineSqlParameter("@SettingValue", SqlDbType.NVarChar, -1, ParameterDirection.Input, settingValue);
            sph.DefineSqlParameter("@ControlType", SqlDbType.NVarChar, 50, ParameterDirection.Input, controlType);
            sph.DefineSqlParameter("@RegexValidationExpression", SqlDbType.NVarChar, -1, ParameterDirection.Input, regexValidationExpression);
            sph.DefineSqlParameter("@ControlSrc", SqlDbType.NVarChar, 255, ParameterDirection.Input, controlSrc);
            sph.DefineSqlParameter("@HelpKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, helpKey);
            sph.DefineSqlParameter("@SortOrder", SqlDbType.Int, ParameterDirection.Input, sortOrder);

            int rowsAffected = sph.ExecuteNonQuery();
            return (rowsAffected > -1);
        }