All posts by bryan

Uncategorized

Structure to Stored Procedure

Leave a comment

Everywhere I go from client to client I find that when developers create Stored Procedures that just perform the task in hand, and I so often find that no exception handling, comments or transaction based handling has been put in place.  So with all this in mind I set to write a structured Stored Procedure to be used as a template for future Stored Procedures that I create.

-- =============================================
-- Author: Bryan Avery
-- Create date: 15th July 2008
-- Description: Import the Taxonomy in to WPPs
-- =============================================
ALTER PROCEDURE [dbo].[sp_WaSPs_TaxonomyImport] 
 @Debug bit = 0,
 @ErrorMessage varchar(2000) OUTPUT, 
 @return_code Int OUTPUT

AS
BEGIN
-- ========================================================
-- Declare SProc variables 
-- ========================================================

DECLARE @Error int
 DECLARE @RowCount int

-- ========================================================
-- Initialise SProc variables 
-- ========================================================
 
 SET NOCOUNT ON;

-- ========================================================
-- SProc content 
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
DECLARE @Debug_ProcStartTime datetime 
, @Debug_ProcEndTime datetime 
 
SET @Debug_ProcStartTime = Getdate() 
 
SET NOCOUNT OFF 
 
 PRINT '' 
 
 PRINT '-- ======================================================' 
 PRINT '-- = PROCEDURE sp_WaSPs_TaxonomyImport' 
 PRINT '-- = @Debug = ' + IsNull(Cast(@Debug as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 
 PRINT '' 
 
 
END

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * BEGIN TRANSACTION' 
 PRINT '-- ******************************' 
 
END

-- ========================================================
-- Uncomment the section below if you want to remove all Taxonomy information from the application
-- ========================================================

-- DELETE tbl_WaSPs_GroupArea;
-- DELETE tbl_WaSPs_AreaUserName;
-- DELETE tbl_WaSPs_AreaSite;
-- DELETE tbl_WaSPs_Areas;
-- 
-- DELETE tbl_WaSPs_SiteSite;
-- DELETE tbl_WaSPs_Sites;


BEGIN TRANSACTION


BEGIN TRY

-- ========================================================
-- Update the areas
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Update the Areas' 
 PRINT '-- ******************************' 
 
END

UPDATE tbl_WaSPs_Areas
 SET AreaName = et2.EntityType, EntityTypeCD = et2.EntityTypeCD, myView = 'False'
 FROM WaspFactory.dbo.tblEntityType et2, tbl_WaSPs_Areas a1
 WHERE et2.EntityTypeCD = a1.EntityTypeCD 
 
-- ========================================================
-- Insert the areas
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Insert the Areas' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_Areas(AreaName, EntityTypeCD, myView)
 SELECT et2.EntityType, et2.EntityTypeCD, 'False'
 FROM WaspFactory.dbo.tblEntityType et2
 WHERE et2.EntityTypeCD NOT IN (SELECT EntityTypeCD FROM tbl_WaSPs_Areas)

-- ========================================================
-- Update the sites
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Update the Sites' 
 PRINT '-- ******************************' 
 
END

UPDATE tbl_WaSPs_Sites
 SET title = e1.EntityName, siteid = e1.EntityIdent, entityid = e1.EntityID
 FROM WaspFactory.dbo.tblEntity e1, tbl_WaSPs_Sites s1
 WHERE e1.EntityName IS NOT Null AND
 e1.EntityId = s1.entityid

-- ========================================================
-- Insert the sites
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Insert the Sites' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_Sites(title, siteid, entityid)
 SELECT EntityName, EntityIdent, EntityID
 FROM WaspFactory.dbo.tblEntity
 WHERE EntityName IS NOT Null AND
 EntityId NOT IN(SELECT entityid FROM tbl_WaSPs_Sites)

-- ========================================================
-- Remove all the links first
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Remove all the links first' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_wasps_AreaSite
 FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
 tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
 WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
 Sites.EntityId = Entity.EntityId AND
 Entity.EntityTypeCD = EntityType.EntityTypeCD

-- ========================================================
-- Link the sites to the areas
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the sites to the areas' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_wasps_AreaSite(AreaId, SiteId)
 SELECT AreaId, Id
 FROM tbl_WaSPs_Areas Areas, WaspFactory.dbo.tblEntityType EntityType,
 tbl_WaSPs_Sites Sites, WaspFactory.dbo.tblEntity Entity
 WHERE Areas.EntityTypeCD = EntityType.EntityTypeCD AND
 Sites.EntityId = Entity.EntityId AND
 Entity.EntityTypeCD = EntityType.EntityTypeCD AND
 (AreaId NOT IN (SELECT AreaId FROM tbl_wasps_AreaSite) AND
 Id NOT IN (SELECT SiteId FROM tbl_wasps_AreaSite))
 
-- ========================================================
-- Remove all the site to site links
-- ========================================================
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Remove all the site to site links' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_WaSPs_SiteSite
 WHERE SiteId = fkSiteId

-- ========================================================
-- Link the site to themself
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the site to themself' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
 SELECT ID, ID, 'Neither'
 FROM tbl_WaSPs_Sites

-- ========================================================
-- Delete all the site to site links
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Delete all the site to site links' 
 PRINT '-- ******************************' 
 
END

DELETE tbl_WaSPs_SiteSite
 FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
 WaspFactory.dbo.tblEntityLink el1, 
 WaspFactory.dbo.tblEntity e2, 
 tbl_WaSPs_Sites s2
 WHERE e1.EntityId = s1.EntityId AND
 s1.EntityId = el1.EntityID1 AND
 el1.EntityID2 = e2.EntityId AND
 e2.Entityid = s2.EntityId

-- ========================================================
-- Link the site to other sites
-- ========================================================

IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * Link the site to other sites' 
 PRINT '-- ******************************' 
 
END

INSERT INTO tbl_WaSPs_SiteSite(SiteID, fkSiteId, WaterDirection)
 SELECT s1.ID, s2.ID, 'Neither'
 FROM tbl_WaSPs_Sites s1, WaspFactory.dbo.tblEntity e1,
 WaspFactory.dbo.tblEntityLink el1, 
 WaspFactory.dbo.tblEntity e2, 
 tbl_WaSPs_Sites s2
 WHERE e1.EntityId = s1.EntityId AND
 s1.EntityId = el1.EntityID1 AND
 el1.EntityID2 = e2.EntityId AND
 e2.Entityid = s2.EntityId
 
END TRY 
 
BEGIN CATCH 
 
 SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT ; IF (@Error <> 0 OR @RowCount = 0) GOTO QuitWithRollback -- (check for error) 
 
END CATCH

-- ---------------------------------------------------------------------------------------------------- 
-- Commit Transaction 
-- ---------------------------------------------------------------------------------------------------- 
 
IF (@@TRANCOUNT > 0) 
 
BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * COMMIT TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 COMMIT TRANSACTION 
 
END

GOTO QuitWithSuccess

-- ---------------------------------------------------------------------------------------------------- 
-- Error Handling 
-- ---------------------------------------------------------------------------------------------------- 
QuitWithConcurrencyRollback: 
 
IF (@@TRANCOUNT > 0) 
 
 BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * ROLLBACK TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 ROLLBACK TRANSACTION 
 
 END 
 
GOTO QuitWithConcurrencyError

-------------------------------------------------- 
QuitWithRollback: 
 
IF (@@TRANCOUNT > 0) 
 
 BEGIN 
 
 IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
 BEGIN 
 
 PRINT '-- ******************************' 
 PRINT '-- * ROLLBACK TRANSACTION' 
 PRINT '-- ******************************' 
 
 END 
 
 ROLLBACK TRANSACTION 
 
END 
 
GOTO QuitWithError

-------------------------------------------------- 
QuitWithConcurrencyError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithConcurrencyError' 
 PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @ErrorMessage = 'Concurrency Error' 
 
SET @return_code = 3 
 
RETURN(3)

-------------------------------------------------- 
QuitWithError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithError' 
 PRINT '-- = @Error = ' + IsNull(Cast(@Error as varchar),'<NULL>') 
 PRINT '-- = @return_code = ' + IsNull(Cast(@return_code as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @ErrorMessage = 'SQL Error' 
 
SET @return_code = 2 
 
RETURN(2)

-------------------------------------------------- 
QuitWithParameterError: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithParameterError' 
 PRINT '-- = @ErrorMessage = ' + IsNull(Cast(@ErrorMessage as varchar),'<NULL>') 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
 
SET @return_code = 1 
 
RETURN(1) 
------------------------------------------------ 
QuitWithSuccess: 
 
IF ( IsNull(@Debug,0) = 1 ) -- (debug info) 
 
BEGIN 
 
 SET @Debug_ProcEndTime = Getdate() 
 
 PRINT '' 
 PRINT '-- ======================================================' 
 PRINT '-- = sp_WaSPs_TaxonomyImport' 
 PRINT '-- = RETURN VALUE' 
 PRINT '-- = QuitWithSuccess' 
 PRINT '-- = EXECUTION TIME' 
 PRINT '-- = Start = ' + IsNull(Convert(varchar(24), @Debug_ProcStartTime , 113),'<NULL>') 
 PRINT '-- = End = ' + IsNull(Convert(varchar(24), @Debug_ProcEndTime , 113),'<NULL>') 
 PRINT '-- = Ellapsed Time (ms) = ' + IsNull(Cast(Datediff(millisecond, @Debug_ProcStartTime, @Debug_ProcEndTime) as varchar),'<NULL>') 
 PRINT '-- ======================================================' 
 PRINT '' 
 
END 
 
SET @return_code = 0 
 
RETURN(0) 
-- -------------------------------------------------- 
END

StoredProcedure

Uncategorized

HTML Editor

1 Comment

I’ve been doing a little research on the use of an HTML Editor for development of internal project, using ASP.NET

Aloha Editor (http://www.aloha-editor.org)

This HTML editor is the bees knees, take a look at the website and check it out, it fantastic

alohaeditor-0.23.9.zip (11.30 mb)

FCKeditor (http://www.fckeditor.net/)

The editor is distributed under the GPL, LGPL and MPL open source licenses. This triple licensing model avoids incompatibility with other open source licenses, making it possible to integrate FCKeditor whenever you want.

FreeTextBox (http://freetextbox.com/default/)

FreeTextBox is the most-used HTML editor for ASP.NET. It is compatible with IE on the PC, and Mozilla and Firefox on all platforms. It is used in major Open Source projects such as Community Server and DotNetNuke as well as excellent packages like Smarter Mail.
Buy FreeTextBox
To purchase a FreeTextBox Professional license or Distribution License with Source code, please click on one of the “Purchase” buttons below. Currently, PayPal is our payment provider. Once you click a “buy” you will be redirected to paypal where you can pay using your PayPal account or a credit card.
Professional License – $49.99
The Professional License allows FreeTextBox to be used in all applications you or your company creates on as many servers as you host, but the license does not include rights to re-distribute FreeTextBox 3.0 Professional features in applications you or your company sell. If you sell or distribute products with FreeTextBox 3.0 Professional features, you must purchase a Distribution license (see below). Upon purchase, you will receive a unique FreeTextBox.lic file which will unlock the Professional features of FreeTextBox 3.0. Professional License Agreement

TinyMCE (http://tinymce.moxiecode.com/index.php)

Open Source under LGPL

CuteEditor (http://cutesoft.net/ASP.NET+WYSIWYG+Editor/default/)

http://cutesoft.net/How+to+Buy/Licensing+Info/default/
This can be purchased from
http://cutesoft.net/ASP.NET+WYSIWYG+Editor/Purchase+CuteEditor+for+.NET/default/

RadEditor (http://www.telerik.com/products/aspnet-ajax/controls/editor/overview/)

RadEditor for ASP.NET AJAX is the leading WYSIWYG web editor. It can replace any TextBox with an intuitive Word®-like editor, which enables even non-technical users to visually manage HTML content. Also available for SharePoint 2007 (MOSS) and DotNetNuke.
Cost $999

On my research I could not find a BSD licensed HTML Editor.

The ones I have used before are
    RadEditor, very good, also very good support and backup.
    TinyMCE, very rich in features and easy to use.
    FCKeditor, little harder to use, and now with extra licences to allow for more general use
   

BlogEngine.NET

Using BlogEngine.net as a General Purpose Content Management System

7 Comments

So I keep running into the same problem – I am building a small website for somebody and I need to provide them with a way to update the content of their site so I don’t have to. Basically, I need a lightweight and flexible content management system that is easy to use.

If The Shoe Fits…

When I first thought of a lightweight CMS, I thought of graffiti. It sounds like exactly what I need. So I downloaded the express edition and started evaluating it. It seemed like a nice product and all is not free for commercial use ($399 is the cheapest commercial licence) and I can’t afford that price tag when building small websites.

Enter BlogEngine.net. My favorite blogging platform. There, I said it.  Well, I use it to run my blog and I am constantly tinkering around with the site all of the time because I enjoy using BlogEngine.net so much.

I thought that BlogEngine.net has all of the key pieces I needed for my lightweight CMS:

  1. A WYSIWYG Editor
  2. A Metaweblog interface
  3. Tons of extensibility

Basic Idea

I decided to base my CMS implementation on the concept of pages. Most blog engines have two distinct types of content: pages and posts. Posts are the typical type of content that becomes part of your blogs feed whereas pages are usually static content which can be anything outside of a blog post (for example an ‘About Me’ page). BlogEngine.net already has everything I need to get the content of page created and persisted in a data store (it supports xml and sql server out of the box). I decided to write a web control which I can place on any webpage and include the contents of a given page from the data store.

I made a control called PageViewer which you can place on the page like this:

<blog:PageViewer ID=“view” runat=“server” DisplayTitle=“false” PageId=“167eb7f3-135b-4f90-9756-be25ec10f14c” />

This control basically just looks up the page using the given id (this functionality is all provided by the existing BlogEngine.Core library) and displays its content. Here is the rendering logic

if (PageId != Guid.Empty)	
page = BlogEngine.Core.Page.GetPage(PageId);if (page != null){	ServingEventArgs arg = new ServingEventArgs(page.Content,ServingLocation.SinglePage);BlogEngine.Core.Page.OnServing(page, arg);	if (arg.Cancel)	Page.Response.Redirect("error404/", true);	
if (DisplayTitle)	{		writer.Write("<h1>");		writer.Write(page.Title);		writer.Write("</h1>");	}	
writer.Write("<div>");	writer.Write(arg.Body);	writer.Write("</div>");}

This code is pretty straight forward – all it does is get an instance of the page and then display its title in <h1> a tag and its body in <div> tag. This logic is actually straight from the existing page retrieval code that already exists in BlogEngine.net. This web control is pretty much the only new code I had to write. The rest of the project mostly involves moving files around and removing parts of the BlogEngine.net framework that I don’t need.

Armed with this control, we are ready to start converting the static pages from the old version of the website to be BlogEngine.net pages which can be stored and retrieved using the BlogEngine.Core classes.

Themes

It’s also worth noting that there are many themes available for BlogEngine

themes4blogengine.net