C# Data.SqlParameterHelper类代码示例

 public static bool Delete(Guid pollGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_Polls_Delete", 1);
     sph.DefineSqlParameter("@PollGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, pollGuid);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > 0);
 }

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

 public static int AddGalleryImage(
     Guid itemGuid,
     Guid moduleGuid,
     int moduleId,
     int displayOrder,
     string caption,
     string description,
     string metaDataXml,
     string imageFile,
     string webImageFile,
     string thumbnailFile,
     DateTime uploadDate,
     string uploadUser,
     Guid userGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_GalleryImages_Insert", 13);
     sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, itemGuid);
     sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
     sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
     sph.DefineSqlParameter("@DisplayOrder", SqlDbType.Int, ParameterDirection.Input, displayOrder);
     sph.DefineSqlParameter("@Caption", SqlDbType.NVarChar, 255, ParameterDirection.Input, caption);
     sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, -1, ParameterDirection.Input, description);
     sph.DefineSqlParameter("@MetaDataXml", SqlDbType.NVarChar, -1, ParameterDirection.Input, metaDataXml);
     sph.DefineSqlParameter("@ImageFile", SqlDbType.NVarChar, 100, ParameterDirection.Input, imageFile);
     sph.DefineSqlParameter("@WebImageFile", SqlDbType.NVarChar, 100, ParameterDirection.Input, webImageFile);
     sph.DefineSqlParameter("@ThumbnailFile", SqlDbType.NVarChar, 100, ParameterDirection.Input, thumbnailFile);
     sph.DefineSqlParameter("@UploadDate", SqlDbType.DateTime, ParameterDirection.Input, uploadDate);
     sph.DefineSqlParameter("@UploadUser", SqlDbType.NVarChar, 100, ParameterDirection.Input, uploadUser);
     sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
     int newID = Convert.ToInt32(sph.ExecuteScalar());
     return newID;
 }

 /// <summary>
 /// Deletes a row from the mp_SystemLog table. Returns true if row deleted.
 /// </summary>
 /// <param name="id"> id </param>
 /// <returns>bool</returns>
 public static bool Delete(int id)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_SystemLog_Delete", 1);
     sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > 0);
 }

 public static bool CreateDefaultModuleSettings(int moduleId)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ModuleSettings_CreateDefaultSettings", 1);
     sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > -1);
 }

 public static int Add(
     Guid guid,
     Guid storeGuid,
     int downloadsAllowed,
     int expireAfterDays,
     bool countAfterDownload,
     DateTime created,
     Guid createdBy,
     string createdFromIP,
     DateTime lastModified,
     Guid lastModifedBy,
     string lastModifedFromIPAddress,
     string name,
     string description)
 {
     SqlParameterHelper sph = new SqlParameterHelper(WebStoreConnectionString.GetWriteConnectionString(), "ws_FullfillDownloadTerms_Insert", 13);
     sph.DefineSqlParameter("@Guid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, guid);
     sph.DefineSqlParameter("@StoreGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, storeGuid);
     sph.DefineSqlParameter("@DownloadsAllowed", SqlDbType.Int, ParameterDirection.Input, downloadsAllowed);
     sph.DefineSqlParameter("@ExpireAfterDays", SqlDbType.Int, ParameterDirection.Input, expireAfterDays);
     sph.DefineSqlParameter("@CountAfterDownload", SqlDbType.Bit, ParameterDirection.Input, countAfterDownload);
     sph.DefineSqlParameter("@Created", SqlDbType.DateTime, ParameterDirection.Input, created);
     sph.DefineSqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, createdBy);
     sph.DefineSqlParameter("@CreatedFromIP", SqlDbType.NVarChar, 255, ParameterDirection.Input, createdFromIP);
     sph.DefineSqlParameter("@LastModified", SqlDbType.DateTime, ParameterDirection.Input, lastModified);
     sph.DefineSqlParameter("@LastModifedBy", SqlDbType.UniqueIdentifier, ParameterDirection.Input, lastModifedBy);
     sph.DefineSqlParameter("@LastModifedFromIPAddress", SqlDbType.NVarChar, 255, ParameterDirection.Input, lastModifedFromIPAddress);
     sph.DefineSqlParameter("@Name", SqlDbType.NVarChar, 255, ParameterDirection.Input, name);
     sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, -1, ParameterDirection.Input, description);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

        public static bool Add(int contractId,
            int projectId,
            int blockId,
            int workId,
            int groupId,
            int itemId,
            int subItemId,
            int initialProgress,
            int currentProgress,
            string user)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("INSERT INTO uAvance ");
            sb.Append("(IdContrato,IdProyecto,IdBloque,IdObra,IdGrupo,IdItem,IdSubItem,AvanceInicial,AvanceActual,Usuario,Fecha)");
            sb.Append(" VALUES ");
            sb.AppendFormat("({0},{1},{2},{3},{4},{5},{6},{7},{8},'{9}',GETDATE())", contractId, projectId, blockId, workId, groupId, itemId, subItemId, initialProgress, currentProgress, user);

            SqlParameterHelper sph = new SqlParameterHelper(DBHelper.Instance.ConnectionString, sb.ToString(), CommandType.Text, 0);
            int rows = 0;

            try
            {
                rows = sph.ExecuteNonQuery();
            }
            catch(Exception)
            {
                rows = 0;
            }

            return rows > 0;
        }

 public static bool DeleteSettingById(int id)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_ModuleDefinitionSettings_DeleteByID", 1);
     sph.DefineSqlParameter("@ID", SqlDbType.Int, ParameterDirection.Input, id);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > -1);
 }

 public static int AddLink(
     Guid itemGuid,
     Guid moduleGuid,
     int moduleId,
     string title,
     string url,
     int viewOrder,
     string description,
     DateTime createdDate,
     int createdBy,
     string target,
     Guid userGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_Links_Insert", 11);
     sph.DefineSqlParameter("@ItemGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, itemGuid);
     sph.DefineSqlParameter("@ModuleGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, moduleGuid);
     sph.DefineSqlParameter("@ModuleID", SqlDbType.Int, ParameterDirection.Input, moduleId);
     sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, 255, ParameterDirection.Input, title);
     sph.DefineSqlParameter("@Url", SqlDbType.NVarChar, -1, ParameterDirection.Input, url);
     sph.DefineSqlParameter("@ViewOrder", SqlDbType.Int, ParameterDirection.Input, viewOrder);
     sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, -1, ParameterDirection.Input, description);
     sph.DefineSqlParameter("@CreatedDate", SqlDbType.DateTime, ParameterDirection.Input, createdDate);
     sph.DefineSqlParameter("@CreatedBy", SqlDbType.Int, ParameterDirection.Input, createdBy);
     sph.DefineSqlParameter("@Target", SqlDbType.NVarChar, 20, ParameterDirection.Input, target);
     sph.DefineSqlParameter("@UserGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, userGuid);
     int newID = Convert.ToInt32(sph.ExecuteScalar());
     return newID;
 }

 /// <summary>
 /// Deletes a row from the ws_OrderOffers table. Returns true if row deleted.
 /// </summary>
 /// <param name="itemGuid"> itemGuid </param>
 /// <returns>bool</returns>
 public static bool DeleteByOrder(Guid orderGuid)
 {
     SqlParameterHelper sph = new SqlParameterHelper(WebStoreConnectionString.GetReadConnectionString(), "ws_OrderOffers_DeleteByOrder", 1);
     sph.DefineSqlParameter("@OrderGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, orderGuid);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > 0);
 }

 public static int Count(int siteId)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetReadConnectionString(), "mp_WebParts_GetCount", 1);
     sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
     int count = Convert.ToInt32(sph.ExecuteScalar());
     return count;
 }

 public static bool DeleteWebPart(Guid webPartId)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_WebParts_Delete", 1);
     sph.DefineSqlParameter("@WebPartID", SqlDbType.Int, ParameterDirection.Input, webPartId);
     int rowsAffected = sph.ExecuteNonQuery();
     return (rowsAffected > -1);
 }

 public static int AddWebPart(
     Guid webPartId,
     Guid siteGuid,
     int siteId,
     string title,
     string description,
     string imageUrl,
     string className,
     string assemblyName,
     bool availableForMyPage,
     bool allowMultipleInstancesOnMyPage,
     bool availableForContentSystem)
 {
     SqlParameterHelper sph = new SqlParameterHelper(ConnectionString.GetWriteConnectionString(), "mp_WebParts_Insert", 11);
     sph.DefineSqlParameter("@WebPartID", SqlDbType.UniqueIdentifier, ParameterDirection.Input, webPartId);
     sph.DefineSqlParameter("@SiteGuid", SqlDbType.UniqueIdentifier, ParameterDirection.Input, siteGuid);
     sph.DefineSqlParameter("@SiteID", SqlDbType.Int, ParameterDirection.Input, siteId);
     sph.DefineSqlParameter("@Title", SqlDbType.NVarChar, ParameterDirection.Input, title);
     sph.DefineSqlParameter("@Description", SqlDbType.NVarChar, ParameterDirection.Input, description);
     sph.DefineSqlParameter("@ImageUrl", SqlDbType.NVarChar, ParameterDirection.Input, imageUrl);
     sph.DefineSqlParameter("@ClassName", SqlDbType.NVarChar, ParameterDirection.Input, className);
     sph.DefineSqlParameter("@AssemblyName", SqlDbType.NVarChar, ParameterDirection.Input, assemblyName);
     sph.DefineSqlParameter("@AvailableForMyPage", SqlDbType.Bit, ParameterDirection.Input, availableForMyPage);
     sph.DefineSqlParameter("@AllowMultipleInstancesOnMyPage", SqlDbType.Bit, ParameterDirection.Input, allowMultipleInstancesOnMyPage);
     sph.DefineSqlParameter("@AvailableForContentSystem", SqlDbType.Bit, ParameterDirection.Input, availableForContentSystem);
     int rowsAffected = sph.ExecuteNonQuery();
     return rowsAffected;
 }

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